«

»

jul 08

Buborék diagram kombinálása vonallal

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:

Bubble-line-1

A buborékokhoz az alábbi adat táblát használtuk:

Bubble-line-2

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.

Bubble-line-3

 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:

Bubble-line-4

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!

Bubble-line-5

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!

 

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöljük.

Az alábbi HTML kódok használhatóak: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

css.php