Hogyan készítsünk vízesés diagramot

Egyre gyakrabban találkozom a különböző cégek beszámolóiban, és hallom a gyakornokok kérését, hogy magyarázzák el, hogyan épül fel az eltérések kaszkáddiagramja – ez is „vízesés”, „vízesés”, „híd” is. ”, ez is egy „híd” stb. Valahogy így néz ki:

Távolról valóban úgy néz ki, mint egy vízesés zuhatag egy hegyi folyón vagy egy függőhíd – ki mit lát?

Egy ilyen diagram sajátossága, hogy:

• Jól látjuk a paraméter kezdeti és végső értékét (az első és az utolsó oszlop).
• A pozitív változások (növekedés) egy színben jelennek meg (általában zöld), a negatívakat pedig (hanyatlás) másoknak (általában). piros).
  
• Néha a diagram részösszeg oszlopokat is tartalmazhat (szürkeleszállt az x tengely oszlopaira).
  

A mindennapi életben az ilyen diagramokat általában a következő esetekben használják:

• Vizuális dinamika kijelző bármilyen folyamat időben: cash flow (cash-flow), befektetések (projektbe fektetünk és abból profitot kapunk).
• Megjelenítés terv megvalósítását (a diagram bal szélső oszlopa tény, a jobb szélső oszlop egy terv, a teljes diagram a kívánt eredmény felé vezető folyamatunkat tükrözi)
• Amikor vizuálisra van szüksége tényezőket mutatniamelyek befolyásolják paraméterünket (a profit faktoriális elemzése – miből áll).
  

Számos módja van egy ilyen diagram elkészítésének – minden a Microsoft Excel verziójától függ.

1. módszer: A legegyszerűbb: Beépített típus az Excel 2016 és újabb verziókban

Ha Excel 2016, 2019 vagy újabb verziója van (vagy Office 365), akkor egy ilyen diagram elkészítése nem nehéz – ezekben az Excel verziókban már alapértelmezés szerint be van építve ez a típus. Csak ki kell választani egy táblázatot az adatokkal, és kiválasztani a lapon betétlap (Beszúrás) parancs lépcsőzetes (Vízesés):

Ennek eredményeként egy majdnem kész diagramot kapunk:

Azonnal beállíthatja a kívánt kitöltési színt a pozitív és negatív oszlopokhoz. Ennek legegyszerűbb módja a megfelelő sorok kiválasztása Növelje и csökkenés közvetlenül a jelmagyarázatban, és jobb gombbal rájuk kattintva válassza ki a parancsot Tölt (Tölt):

Ha részösszegekkel vagy végső oszlopösszeggel rendelkező oszlopokat kell hozzáadnia a diagramhoz, akkor ezt a legkényelmesebb a függvényekkel megtenni. RÉSZÖSSZEGEK (RÉSZÖSSZEG) or EGYSÉG (ÖSSZESÍTETT). A táblázat elejétől számítják ki a felhalmozott összeget, miközben kizárják belőle a fenti hasonló összegeket:

Ebben az esetben az első argumentum (9) a matematikai összegzési művelet kódja, a második (0) pedig azt eredményezi, hogy a függvény figyelmen kívül hagyja az eredményekben az előző negyedévekre már kiszámított összegeket.

Összesítő sorok hozzáadása után marad a diagramon megjelenő összesített oszlopok kijelölése (kétszer egymás után egyszeri kattintás az oszlopra), majd jobb egérgombbal kattintson a parancsra. Állítsa be teljesnek (Összesen beállítva):

A kiválasztott oszlop az x tengelyen landol, és színe automatikusan szürkére változik.

Valójában ez minden – a vízesés diagram készen áll:

2. módszer. Univerzális: láthatatlan oszlopok

Ha Excel 2013 vagy régebbi verziója van (2010, 2007 stb.), akkor a fent leírt módszer nem fog működni az Ön számára. Körbe kell mennie, és ki kell vágnia a hiányzó vízesés-diagramot egy szokásos halmozott hisztogramból (az oszlopokat egymás tetején összegezve).

Itt az a trükk, hogy átlátszó prop oszlopokkal emeljük a piros és zöld adatsorainkat a megfelelő magasságba:

Egy ilyen diagram elkészítéséhez hozzá kell adnunk néhány további képletekkel ellátott segédoszlopot a forrásadatokhoz:

• Először is fel kell osztanunk az eredeti oszlopunkat úgy, hogy a pozitív és negatív értékeket külön oszlopokra választjuk a függvény segítségével IF (HA).  
• Másodszor, hozzá kell adnia egy oszlopot az oszlopok elé cumi, ahol az első érték 0 lesz, és a második cellától kezdve a képlet kiszámítja azoknak a nagyon átlátszó tartóoszlopoknak a magasságát.
  

Ezt követően marad a teljes táblázat kijelölése, kivéve az eredeti oszlopot Folyik és hozzon létre egy szabályos halmozott hisztogramot keresztben Betét – hisztogram (Beszúrás — Oszlopdiagram):

Ha most kiválasztja a kék oszlopokat és láthatatlanná teszi őket (jobb gombbal kattintson rájuk – Sorformátum – Kitöltés – Nincs kitöltés), akkor csak azt kapjuk, amire szükségünk van. 

Ennek a módszernek az előnye az egyszerűség. A mínuszok – a segédoszlopok megszámlálásának szükségessége.

3. módszer. Ha mínuszba megyünk, minden nehezebb

Sajnos az előző módszer csak pozitív értékek esetén működik megfelelően. Ha legalább egy területen a vízesésünk negatív területre kerül, akkor a feladat összetettsége jelentősen megnő. Ebben az esetben minden sort (ál, zöld és piros) külön kell kiszámítani a negatív és a pozitív részekhez képletekkel:

Annak érdekében, hogy ne szenvedjen sokat, és ne találja fel újra a kereket, a cikk címében letölthető egy kész sablon egy ilyen esetre.

4. módszer. Egzotikus: fel-le sávok

Ez a módszer a lapos diagramok (hisztogramok és grafikonok) egy speciális, kevéssé ismert elemének felhasználásán alapul. Fel-le szalagok (Fel-le sávok). Ezek a sávok két grafikon pontjait kötik össze páronként, hogy egyértelműen megmutassák, melyik a magasabb vagy alacsonyabb pont, amelyet aktívan használnak a terv-tény megjelenítésénél:

Könnyen kitalálható, hogy ha eltávolítjuk a diagramok vonalait, és csak a fel-le sávokat hagyjuk a diagramon, akkor ugyanazt a „vízesést” kapjuk.

Egy ilyen konstrukcióhoz további két további oszlopot kell hozzáadnunk a táblázatunkhoz egyszerű képletekkel, amelyek kiszámítják a két szükséges láthatatlan grafikon pozícióját:

Egy „vízesés” létrehozásához ki kell választania egy oszlopot hónapokkal (az X tengely mentén lévő aláírásokhoz) és két további oszlopot Az 1 ütemezése и Az 2 ütemezése és készítsen egy szabályos grafikont a kezdők számára Beszúrás – Grafikon (Beszúrás — Chart vonal):

Most adjunk fel-le sávokat a diagramunkhoz:

• Az Excel 2013-as és újabb verzióiban ezt ki kell választani a lapon építész parancs Add hozzá a diagram elemét — Növekedés-csökkenés sávjai (Tervezés – Diagramelem hozzáadása – Fel-le sávok)
• Az Excel 2007-2010-ben – lépjen a lapra Elrendezés – Előre-csökkentő sávok (Elrendezés – Fel-le sávok)

A diagram ezután valahogy így fog kinézni:

Marad a grafikonok kijelölése és átlátszóvá tétele a jobb egérgombbal sorra kattintva és a parancs kiválasztásával Adatsor formátum (Sorozat formátuma). Hasonlóképpen módosíthatja a szokásos, meglehetősen kopottasnak tűnő fekete-fehér csíkszíneket zöldre és pirosra, hogy szebb képet kapjon a végén:

A Microsoft Excel legújabb verzióiban a sávok szélessége módosítható úgy, hogy az egyik átlátszó grafikonra (nem a sávokra!) kattintunk jobb egérgombbal, és kiválasztjuk a parancsot. Adatsor formátum – Oldalsó távolság (Sorozat formátuma – résszélesség).

Az Excel régebbi verzióiban a Visual Basic parancsot kellett használnia a hiba javításához:

1. Jelölje ki az épített diagramot
2. Nyomja meg a billentyűparancsot más+F11hogy bejusson a Visual Basic Editorba
3. Nyomja meg a billentyűparancsot Ctrl+Ga közvetlen parancsbeviteli és hibakereső panel megnyitásához azonnali (általában alul található).

4. Másolja ki és illessze be a következő parancsot: ActiveChart.ChartGroups(1).GapWidth = 30 és nyomja meg az belép:

Természetesen, ha akarja, játszhat a paraméter értékével. GapWidtha kívánt távolság eléréséhez:

• Hogyan készítsünk listás diagramot az Excelben a KPI megjelenítéséhez  
• Az Excel 2013 diagramjainak újdonságai
• Hogyan készítsünk interaktív „élő” diagramot Excelben