Ako vytvoriť vodopádový graf v programe Excel

Tento tutoriál vám ukáže, ako vytvoriť vodopádový graf vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.

Vodopádový graf (nazývaný tiež premosťovací diagram, diagram lietajúcich tehál, kaskádový diagram alebo Mario diagram) je graf, ktorý vizuálne člení kumulatívny efekt, podľa ktorého séria postupných kladných alebo záporných hodnôt prispela k konečnému výsledku.

Prvý a posledný stĺpec obvykle predstavujú počiatočné a konečné hodnoty oddelené prechodnými úpravami, ktoré znázorňujú postupnosť krokov, ktoré bolo potrebné dosiahnuť z bodu A do bodu B.

Tento typ grafu profesionáli vo všetkých hlavných priemyselných odvetviach široko používajú na vykonávanie kvantitatívnych analýz, od dekódovania finančných výkazov po monitorovanie zásob v obchode.

Mostové grafy boli predstavené ako vstavaný typ grafu iba v programe Excel 2016, čo znamená, že používateľom starších verzií programu Excel neostáva nič iné, ako vytvoriť graf od základov.

Z tohto dôvodu sa v tomto návode naučíte, ako vytvoriť tento Mario diagram tak ručne, ako od začiatku, tak aj tak, že sa obrátite na typ natívneho grafu, ktorý za vás vykoná všetku špinavú prácu.

Začíname

Hneď za bránou musíme do obrazu vhodiť niekoľko nespracovaných údajov. Na ilustračné účely predpokladajme, že ste sa pustili do analýzy štvrťročných čistých peňažných tokov nejakej fiktívnej spoločnosti.

Vzhľadom na to všetko vezmite do úvahy nasledujúce údaje:

Aby sme vám pomohli prispôsobiť graf svojim vlastným potrebám, porozprávajme sa o tabuľke podrobnejšie:

• Obdobie: Tento stĺpec predstavuje postupnosť kladných a záporných hodnôt, ktoré sa majú vykresliť do grafu.
• Čistý peňažný tok: Tento stĺpec pozostáva zo zodpovedajúcich hodnôt. Prvý (B2) a posledný (B13Bunky) obsahujú počiatočné a konečné hodnoty, zatiaľ čo zostávajúce čísla medzi nimi predstavujú kvantitatívne zmeny v priebehu času.

Teraz, keď poznáte základy grafu, môžeme prejsť na drobnozrnné.

Ako vytvoriť vodopádový graf v programe Excel 2016+

Najprv vám ukážem riešenie pre tých, ktorí hľadajú cestu najmenšieho odporu.

Použitím vstavanej, pripravenej šablóny, ktorú dodávate, používate Excel 2016 alebo 2022-nastavenie vodopádového grafu by vám nemalo trvať viac ako niekoľko jednoduchých krokov.

Všetko však súvisí s cenou obmedzených možností prispôsobenia a potenciálnymi problémami s kompatibilitou; staršie verzie programu Excel jednoducho nedokážu spracovať novšie typy natívnych grafov.

Zrátané a podčiarknuté: Ak sa chystáte poslať graf okolo, vždy je bezpečnejšie stavať ho ručne.

Krok č. 1: Vytvorte graf vodopádu.

Na začiatku vytvorte predvolený vodopádový graf na základe vašich skutočných údajov. Krása tejto metódy spočíva v tom, že nemusíte skákať cez žiadne obruče:

1. Zvýraznite všetky údaje (A1: B13).
2. Choďte do Vložiť tab.
3. Vyberte „Vložte vodopádový, lievikový, skladový, povrchový alebo radarový graf”Tlačidlo.
4. Vyber si “Vodopád.”

Graf vašich lietajúcich tehál sa okamžite objaví:

Krok č. 2: Nastavte stĺpce medzisúčtu a súčtu.

Našim ďalším krokom je oddeliť počiatočné a konečné hodnoty od „lietajúcich tehál“.

Dvakrát kliknite a potom kliknite pravým tlačidlom myši na stĺpce predstavujúce údajové body „Prenosový zostatok“A„Aktuálny zostatok“A zvoľte„Nastaviť ako celkom.”

Technicky možno budete chcieť zastaviť tam a nechať graf tak, ako je. Tento kaskádový graf nám však nehovorí nič o údajoch, ktoré sú na ňom vykreslené.

Prečo sa uspokojiť s menej? Namiesto toho môžete svoj novovytvorený graf Mario výrazne zlepšiť tým, že sem-tam zmeníte niekoľko vecí.

Krok č. 3: Zmeňte farebnú schému.

Jeden účinný spôsob, ako vylepšiť graf, je nechať za vás pracovať farebnú schému, nie naopak. Prefarbite dátové rady spôsobom, ktorý vám pomôže sprostredkovať, o čom graf je.

1. Dvakrát kliknite a potom pravým tlačidlom kliknite na položku legendy, ktorá predstavuje Séria „Znížiť“.
2. Vyberte „Vyplniť”Tlačidlo.
3. Vyzdvihnúť červená z palety farieb.

Rovnakým spôsobom prefarbiť Séria „Zvýšiť“ na zelenú a Séria „Celkove“ do modra. Kaskádový graf by mal vyzerať takto:

Krok č. 4: Prispôsobte rozsahy vertikálnych osí svojim skutočným údajom.

Ak chcete priblížiť plávajúce stĺpce a získať ďalšie podrobnosti, upravte mierku zvislej osi. Kliknite pravým tlačidlom myši na primárnu vertikálnu os a kliknite na „Formát osi.”

V Formát osi na paneli úloh postupujte podľa týchto jednoduchých krokov:

1. Prepnúť na Možnosti osi tab.
2. Nastaviť Minimálne hranice na “50,000.”
3. Nastaviť Maximálne hranice na “110,000.”

Krok č. 5: Dolaďte detaily.

Je logické, že čím viac prvkov graf obsahuje, tým chaotickejšie to vyzerá. Poďme sa teda zbaviť nepotrebných prvkov odstránením legendy a mriežky.

Pravým tlačidlom myši kliknite na legendu grafu a zvoľte „Vymazať”Z ponuky, ktorá sa objaví. Opakujte rovnaký postup pre mriežky.

Nakoniec zmeňte názov grafu a môžete ho nazvať dňom!

Ako vytvoriť vodopádový graf v Exceli 2007, 2010 a 2013

Tento návod by sa skončil práve tu, ak by vyššie uvedená metóda bola kompatibilná so všetkými verziami programu Excel. Žiaľ, nie je tomu tak.

Z tohto dôvodu môžete zvážiť manuálne vykreslenie grafu od začiatku, aj keď používate Excel 2016 alebo 2022.

Je zrejmé, že zostavenie grafu týmto spôsobom vyžaduje viac času, ale výplata stojí za to.

Na konci tejto časti sa naučíte, ako vytvoriť tento prispôsobiteľný, dynamický mostový graf kompatibilný so všetkými verziami programu Excel:

Bez ďalších okolkov rozbehneme loptu.

Krok č. 1: Pripravte údaje grafu.

Úprimne povedané, tento krok je najťažší.

Jednoducho povedané, náš vlastný vodopádový graf bude vytvorený na základe skladaného stĺpcového grafu. Aby sme to však zvládli, musíme do rovnice pridať niekoľko ďalších pomocných údajov.

Na záver sa rýchlo pozrite, ako by mali byť údaje v grafe organizované do konca prípravnej fázy.

Pre tých, ktorí robia prvé krôčiky na ceste k majstrovstvu Excelu, sa táto tabuľka môže zdať úplne skľučujúca, ale v skutočnosti túto úlohu zvládne ktokoľvek podľa podrobných pokynov uvedených nižšie.

Začnime pridaním troch pomocných stĺpcov s názvom „Neviditeľný” (stĺpec B), “Zvýšiť” (stĺpec C.) a “Znížiť” (stĺpec D), ktoré budú obsahovať fiktívne hodnoty pre umiestnenie vašich skutočných údajov do budúceho grafu.

A keď stĺpce Zvýšiť a Znížiť sú dosť vysvetľujúce, o čo ide stĺpček Neviditeľný? V zásade budú tieto hodnoty tvoriť neviditeľnú základňu budúceho skladaného stĺpcového grafu, aby sa skutočné hodnoty navrhli až do príslušnej výšky.

Poďme sa vysporiadať stĺpec Zvýšiť prvý: Do bunky zadajte nasledujúci vzorec C2 a potiahnite ho nadol pomocou rukoväte výplne, aby sa vykonal pre zostávajúce bunky (C3: C13):

1	= IF (E2> 0, E2, 0)

Teraz zadajte zrkadlový vzorec do prvej bunky zníženie stĺpca (stĺpec D) a skopírujte ho tiež:

1	= IF (E2 <= 0, -E2, 0)

Dvaja dole, jeden preč. Keď ste našli potrebné údaje o grafe, môžete ich teraz nastaviť stĺpček Neviditeľný na presné mapovanie fluktuácií peňažných tokov zadaním nasledujúceho vzorca do druhej bunky stĺpca (B3) a skopírujte ho do priečinka B12:

1	= B2+C2-D3

POZNÁMKA: Je úplne nevyhnutné, aby ste opustili prvú a poslednú bunku (B2 a B13) stĺpca prázdny!

Nezabudnite naformátovať všetky údaje grafu ako menu (Domov> Skupina čísel> Mena).

V tomto mieste by mala vaša rozbalená tabuľka vyzerať takto:

Ako jednu z posledných úprav nastavte ďalší stĺpec s názvom „Štítky”, Ktorý bude obsahovať efektné štítky vlastných údajov.

Akonáhle budete mať tento stĺpec nastavený, zadajte tento vzorec do druhej bunky súboru stĺpcové štítky (F3) skopírujte ho a ponechajte prvú bunku (F2) a posledná bunka (F13) prázdne:

1	= IF (E3 <= 0, TEXT (-E3, "$#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)"), TEXT (E3, „$#, ##“) & CHAR (10) & TEXT (E3/SUM (B3: D3), „(#% ↑)“))

Tí, ktorí pracujú s inými formátmi čísel, by mali namiesto toho použiť tento vzorec:

1	= IF (E3 <= 0, TEXT (-E3, "#, ##") & CHAR (10) & TEXT (-E3/SUM (B3: D3), "(#% ↓)"), TEXT (E3, " #, ## ") & CHAR (10) & TEXT (E3/SUM (B3: D3)," (#% ↑) "))

Krok č. 2: Vytvorte skladaný stĺpcový graf.

Zvládli sme to cez pahorok, takže konečne prišiel čas na zostavenie našej schémy vodopádov.

1. Vyberte všetky údaje v stĺpcoch Obdobie, neviditeľné, zvýšenie, a Znížiť (A1: D13).
2. Choďte do Vložiť tab.
3. Kliknite na „Vložiť stĺpcový alebo stĺpcový graf.”
4. Vyberte „Skladaný stĺp.”

Excel zostaví tento jednoduchý graf, ktorý sa nakoniec zmení na ohromujúci vodopádový graf:

Krok č. 3: Skryte sériu „Neviditeľný“.

Predtým, ako prejdeme k zvyšku grafu, skryte podkladové rady údajov a posuňte plávajúce stĺpce na začiatok. Kliknite pravým tlačidlom myši na ktorýkoľvek z modrých stĺpcov, ktoré predstavujú Séria „Neviditeľný“ a otvorte Formátovať rad údajov panel úloh.

Akonáhle ste tam, urobte pomocné stĺpce neviditeľnými:

1. Choďte do Fill & Line tab-the Vyplniť kartu v starších verziách Excelu.
2. V časti „Vyplniť,“Vyberte„Bez výplne.”
3. V časti „Hranica,“Kliknite na„Žiadna čiara.”

Krok č. 4: Upravte farebnú schému.

V rovnakom Formátovať rad údajov na paneli úloh znova zafarbite každú sériu údajov podľa rovnakých vyššie uvedených krokov (Možnosti série > Vyplniť > Pevná výplň > Farba), aby to bolo možné intuitívne pochopiť na prvý pohľad:

1. Pre Séria „Znížiť“ vyzdvihnúť červená.
2. Pre Séria „Zvýšiť“ vyzdvihnúť zelená.
3. Okrem toho dvakrát kliknite na stĺpce „Zostatok pri prenose“ a "Aktuálny zostatok" a prefarbiť ich Modrá.

V tomto bode by ste mali skončiť:

Krok č. 5: Zmeňte šírku medzery na „20%“.

Teraz zväčšte šírku stĺpcov pre lepšie vizuálne vnímanie.

1. Otvor Formátovať rad údajov podokno úloh (kliknite pravým tlačidlom myši na jednu z radov údajov a zvoľte Formátovať rad údajov) a prepnite na Možnosti série tab.
2. Zmeniť „Šírka medzery“Až„20%.”

Krok č. 6: Upravte rozsahy vertikálnych osí.

Znovu by mala byť upravená mierka osi, aby sa údaje podrobnejšie priblížili. Ak chcete rýchlo zrekapitulovať postup pre tých, ktorí preskočili prvú časť tohto tutoriálu, postupujte takto:

1. Pravým tlačidlom myši kliknite na zvislú os a zvoľte „Formát osi.”
2. Na paneli úloh otvorte súbor Možnosti osi tab.
3. V časti „Hranice, „Zmeniť“Minimálne“Až„50,000“A„Maximálne“Až„110,000.”

Krok č. 7: Pridajte a umiestnite štítky vlastných údajov.

Na tieto vlastné štítky s údajmi sa čakalo príliš dlho, takže ich konečne uvedieme do používania. Kliknite pravým tlačidlom myši na ľubovoľný stĺpec a zvoľte „Pridajte štítky s údajmi.”

Do grafu budú okamžite pridané predvolené štítky údajov viazané na pomocné hodnoty:

Nie je to však presne to, čo hľadáme. Ak chcete problém vyriešiť, manuálne nahraďte predvolené štítky vlastnými hodnotami, ktoré ste vopred pripravili.

1. Dvakrát kliknite na štítok údajov, ktorý chcete zmeniť.
2. Zadajte „=" do Formula bar.
3. Zvýraznite zodpovedajúcu bunku v stĺpcové štítky.

Nahraďte zostávajúce štítky s údajmi opakovaním rovnakého postupu pre každý plávajúci stĺpec.

Keď ste tam, budete musieť štítky pretiahnuť na ich príslušné pozície: všetky kladné hodnoty by mali byť umiestnené nad príslušnými zelenými stĺpcami, zatiaľ čo záporné hodnoty je potrebné presunúť pod červené stĺpce.

Potom, čo sa úspešne popasujete so štítkami, by sa váš graf Mario mal zmeniť na niečo také:

Krok č. 8: Vyčistite oblasť grafu.

Nakoniec odstráňte legendu grafu a mriežky, ktoré do tabuľky nič neprinášajú (Kliknite pravým tlačidlom myši > Vymazať). Zmeňte názov grafu a tam máte vodopádový graf!