Különleges bejegyzés következik – az angol nyelvű blogunk legutóbbi postjának magyar változatát olvashatjátok. A napokban Roberto-val (ki ő? lásd: Rólunk) egy nagyon ötletes és egyszerű megoldást találtunk arra, hogyan lehet a gyakorlatban közkedvelt buborék diagramot vonallal kombinálni.
Az Excelben rengeteg hasznos és különleges megoldást lehet felépíteni különböző diagram típusok kombinálásával – ebben az esetben is az az egyszerű megoldás adódna, hogy kombináljuk a buborék diagramot pont-vonal diagrammal. Ez azonban zsákutca, sajnos a buborék diagram nem kombinálható semmi más típussal. Mi ezt a buborék diagram saját eszközeivel valósítjuk meg.
Nagyon hasznos lehet ez a megoldás, ha például egy termék hónapról hónapra változó pozícióját szeretnénk bemutatni egy Profit + Növekedés és Volumen adatokból felépített buborék diagramon. Így nézhet ez ki például:
A buborékokhoz az alábbi adat táblát használtuk:
A vonal beillesztéshez a megoldás alapötletét a trendvonal adta. A trendvonal a diagramon mindig egy adatsorhoz illeszkedik, és az általunk választott statisztikai módszer alapján kiszámítja és megjeleníti az adatokban levő tendenciát. Mi a mozgóátlag trendvonalat fogjuk használni – ez egy egyszerű számítási módszeren alapul (idézet a MS Office oldalról):
A mozgóátlag meghatározott számú adatpont (ez a Szakasz beállítással adható meg) átlagát veszi, ez az átlagérték adja a trendvonal egy pontját. Ha a Szakasz beállítás értéke például 2, akkor az első két adatpont átlaga képezi a trendvonal első adatpontját, a harmadik és a negyedik adatpont átlaga a trendvonal második pontját, és így tovább.
Nos, az ötlet alapján én csináltam egy egyszerű, de nem túl elegáns (sok manuális munkát igénylő) megoldást, amit Roberto továbbfejlesztett – így most egy gyors és dinamikus módszert osztok meg veletek. Az ő ötlete alapján a vonal, amit a fenti képen láttok, egy 2 tagú mozgóátlag trendvonal, ami egy, a grafikonon láthatatlanra állított adatsorhoz tartozik.
Tehát: Fel kell tennünk a grafikonra egy olyan (dummy) adatsort, aminek a trendvonala a buborékokat összekötő vonal lesz. Másképp fogalmazva: a mozgóátlag-értékek az adottak, ezek az eredeti adataink. Ki kell számolnunk, hogy mi lesz az az adatsor, aminek ezek az eredeti adatok a mozgóátlagai. Ez az adatsor eggyel több adatot fog tartalmazni, mint az eredeti adataink, hiszen az első ponthoz tartozó mozgóátlag értékhez is két adatra van szükségünk.
Nem olyan bonyolult, mint amilyennek hangzik – nézzük lépésről lépésre!
1. A dummy adatsor értékeinek kiszámítása:
Egy kis segéd táblát készítünk, ennek oszlopait -help oszlopnak neveztem el. Egy sorral több adatra lesz szükségünk, ezt sárgával jelöltem az alábbi ábrán – ez a sor fix 0 értékeket tartalmaz.
- Growth-help: itt a kis táblázatban 2. sortól az eredeti Growth adatok szerepelnek.
- Profit-help: Az ábrán látjátok a képletet: 2-vel szorozzuk az eredeti profitot, és kivonjuk belőle a felette levő cella értékét. Ezt a képletet kell lefelé másolni. Ha megnézitek a H oszlop egy celláját, például a H6-ot, itt 8% szerepel márciusra. Ez a 8% az eredeti márciusi adat és az eredeti februári adat átlaga (11%+5%)/2 = 8%
- Size-help: itt minden cellába 0-t írunk – így a dummy adatsor buborékainak 0 a mérete, nem fognak megjelenni a grafikonon.
2. Adjuk hozzá a grafikonhoz a dummy adatsort.
Jobb kattintás a grafikonon, Adatok kijelölése / Hozzáadás (Select data / Add)
A Growth-help oszlop adatai lesznek az x értékek, a Profit-help adatok mennek az y értékekhez, a Size-help pedig a buborék mérethez:
OK, OK után a grafikonon nem látszik semmi (csak a jel a jelmagyarázatban, ha fent hagytad) hiszen a buborék mérete 0.
3. Trendvonal hozzáadása a dummy adatsorhoz:
2007/2010-es Excelben: A Diagrameszközök menüben az Elrendezés alatt jelöld ki a „dummy” sorozatot (bal oldalon, az Aktuális kijelölés alatt a legördülő kiválasztó dobozka) (angolban: Chart Tools / Layout)
Ugyanebben a menüben a jobb oldalon találod a Trendvonal gombot, itt válaszd a Kétperiódusú mozgóátlagot.
2013-as Excelben: A Chart tools / Format menü alatt találod a bal oldalon a Current selection-t, itt van a legördülő dobozka, amiben ki tudod választani a dummy adatsort. A Chart tools / Design / Add chart element alatt tudod felrakni a Trendline/Moving average-et.
És ott a vonal!
4. Formázd a trendvonalat igényeknek megfelelően.
A trendvonalon jobb kattintás után, vagy a menüben ugyanott, ahol a vonalat felraktad, megtalálod a formázási lehetőségeket. Én a vonalstílus alatt a kezdő és vég nyíl beállításával tettem fel a pontot és nyilat a vonal végére.
Innen letölthetitek a minta fájlunkat.
+1. A megoldást dinamikussá teheted elnevezett formulák használatával.
Ennek részleteire azonban itt nem térek ki, mert itt a blogon még nem írtam a dinamikus range-ekről. Akit érdekel ez a megoldás, az megtalálja a részleteket az angol nyelvű cikkben.
Az angol nyelvű cikk itt érhető el, és innen letölthető a dinamikus minta fájl.
Kérlek, írjátok meg, hasznosnak tartjátok-e ezt a megoldást, s osszátok meg bárkivel, akiről úgy gondoljátok, hogy neki is hasznos lehet!
Legutóbbi hozzászólások