Nagyon népszerű ez a diagram típus nálunk, sokféle prezentációban használjuk. Bemutathatjuk vele egy üzleti mutatószám változását egyik időszakról a másikra, valamint azt, hogy a változás milyen hatásokból ered.
Például: a bal oldalon kékkel mutatjuk a 2014-es év profitját. Ezután zölddel és pirossal mutatjuk, hogy az egyes hatások mennyivel növelték illetve csökkentették a profitot, így érkezünk el a jobb oldali kék oszlophoz, a 2015-ös profithoz.
Íme:
Ezt a diagram típust sajnos az Excel nem támogatja (bár úgy hírlik, a 2016-os Excelbe már be lesz építve!), ezért egy kis többlet munkával tudjuk csak felépíteni. Általában a halmozott oszlop (stacked column) diagram típust szokták használni ehhez az ábrához, ami egyszerűen felépíthető, de ha az adataink átlógnak a negatív tartományba, már problémába ütközik ez a módszer, és bonyolultabb kalkulációt kell mögé raknunk. Én jobban szeretem a picit macerásabb, de biztos megoldást használni, amiben pozitív-negatív eltérés sávokból (Up/down bar) építkezünk.
Előkészítés
Íme a kiinduló adattábla:
A számítások egyszerűek lesznek: 3 adatsorra van szükségünk:
- oszlop: amiben csak két érték szerepel, ezek a kiinduló és a befejező kék oszlopok.
- induló pont: ez lesz az egyes hatások kiinduló pontja, ami egyenlő az előző hatás befejező pontjának értékével. (Más szavakkal: ennyi az érték a hatás „előtt”.)
- befejező pont: az egyes hatások „után” ez lesz az érték.
Így néz ki formulákkal:
Grafikon elkészítése
Jelöld ki az adat táblát – először a megnevezéseket, utána a három számított oszlopot, fejléccel együtt (nem összefüggő tartományt Ctrl lenyomásával tudsz kijelölni):
Szúrj be egy vonal diagramot jelölőkkel: Beszúrás menü / Diagramok: Vonal (Insert / Charts: 2-D line).
Pici elmélet haladóknak: mit látunk itt? A kék oszlopok helyett két kék pontot – ezt az adatsort át fogjuk változtatni oszloppá. A zöld és piros kis oszlopok helyett pedig két vonalat látunk – ezt majd el fogjuk tüntetni. Ami most érdekes, az az egymás fölött elhelyezkedő bordó és zöld pontok. A két pont közti távolság egyenlő a hatás értékével, az irány pedig a pontok helyzetéből adódik: ha az induló pont van alul, akkor a hatás pozitív, ez lesz majd a zöld oszlop, ellenkező esetben negatív a hatás, ez lesz a piros oszlop. Ezeket az oszlopokat egyszerűen a két pont közé fogjuk betenni: a pozitív-negatív eltérés (Up/down bar) egy különleges grafikon-elem, amit épp erre használhatunk.
Grafikon átalakítása
Változtasd az „oszlop” adatsort diagram típusát oszlopra!
Egérrel: jobb klikk az adatsoron (a kis kék ponton), majd Sorozat diagram típus módosítása (Change series chart type)
Menüből: Először az adatsort kell kijelölni. Excel 2010: Diagrameszközök / Elrendezés alatt a bal oldali legördülő menüben, Excel 2013-ban ugyanez a Chart tools / Format alatt van. Ha ki van jelölve az adatsor, a 2010-es Excelben: Tervezés / Más diagramtípus, 2013-ban: Design / Change chart type.
Add hozzá a vonalhoz a pozitív-negatív eltéréseket (up/down bar)!
Jelöld ki az egyik vonal adatsort. 2010-es Excelben az Elrendezés alatt találod a Pozitív-negatív eltérés menüpontot, 2013-ban ez a Design / Add chart element / Up/down bar alatt van.
Alakul már valami:
A pozitív-negatív eltérések fehér és fekete színnel látszanak már, ezeket majd átszínezzük.
A vonalak feleslegesek, láthatatlanná kell őket formázni: jelöld ki az „induló pont” adatsort, és az adatsor formázása alatt állítsd be: vonal színe: „nincs vonal”-ra, a jelölő beállításai: nincs. (Line: No line, Marker options: No marker). Ugyanezt kell tenned a „befejező pont” adatsorral.
A jelmagyarázatra sincs szükség, ezt is törölheted.
Formázás
A pozitív-negatív eltérések nagy előnye, hogy külön lehet formázni a kétféle értéket. Egyszerűen jelöld ki a fehér oszlopot, és állítsd a színét zöldre, a fekete oszlopét pedig pirosra.
Az oszlopok szélességét is állíthatod, ennek helye kicsit trükkös, ugyanis a vonal adatsor formázási beállításai között találod. A vonal most nem látszik a grafikonon, ezért csak a menüből tudod kijelölni, ahogy fentebb is írtam. (Sajnos a 2007-es Excelben ez a beállítási lehetőség nincsen benne, ott csak VBA-ból lehet ezt megoldani – lásd a bejegyzés végén idézett cikkben.)
Ha minden sikerült, ilyesmi a végeredmény:
Innen letölthetitek a példa fájlt, amit új sorok beszúrásával egyszerűen kibővíthettek.
A bejegyzés Jon Peltier kiváló cikke alapján készült, melyben részletesen bemutatja a waterfall chart készítés különböző technikáit. Akit mélyebben érdekel a téma, mindenképpen érdemes elolvasnia.
A folytatásban: adatfeliratokat teszünk erre a grafikonra.
2 pings