Vízesés grafikon útmutató (Waterfall chart 1. rész)

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:

waterfall01a

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:

waterfall02

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.

waterfall03

Így néz ki formulákkal:

waterfall04

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):

waterfall05

Szúrj be egy vonal diagramot jelölőkkel: Beszúrás menü / Diagramok: Vonal (Insert / Charts: 2-D line).

waterfall06

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:

waterfall07

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:

waterfall08

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.

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

Your email address will not be published.

css.php