Jak vytvořit vodopádový graf v aplikaci Excel

Tento tutoriál předvede, jak vytvořit vodopádový graf ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Vodopádový graf (také nazývaný můstkový graf, létající cihlový graf, kaskádový graf nebo Marioův graf) je graf, který vizuálně rozděluje kumulativní efekt, kterým ke konečnému výsledku přispěla řada sekvenčních kladných nebo záporných hodnot.

První a poslední sloupec obvykle představují počáteční a konečné hodnoty oddělené mezilehlými úpravami, které znázorňují posloupnost kroků, které bylo zapotřebí k dosažení bodu A do bodu B.

Tento typ grafu byl široce používán profesionály ve všech hlavních průmyslových odvětvích pro provádění kvantitativní analýzy-od dekódování finančních výkazů po sledování skladových zásob.

Bridge grafy byly představeny jako předdefinovaný typ grafu pouze v Excelu 2016, což znamená, že uživatelé dřívějších verzí Excelu nemají jinou možnost, než vytvořit graf od základů.

Z tohoto důvodu se v tomto tutoriálu naučíte, jak vytvořit tento graf Mario jak ručně od začátku, tak i otočením na typ nativního grafu, který za vás provede veškerou špinavou práci.

Začínáme

Hned z brány potřebujeme vložit do obrázku nějaká nezpracovaná data. Předpokládejme tedy, že jste se rozhodli analyzovat čtvrtletní čistý peněžní tok nějaké fiktivní společnosti.

S ohledem na to všechno zvažte následující data:

Abychom vám pomohli přizpůsobit graf vašim vlastním potřebám, promluvme si o tabulce podrobněji:

• Doba: Tento sloupec představuje posloupnost kladných a záporných hodnot, které se mají vykreslit do grafu.
• Čistý peněžní tok: Tento sloupec se skládá z odpovídajících hodnot. První (B2) a poslední (B13) buňky obsahují počáteční a konečné hodnoty, zatímco zbývající čísla mezi nimi představují kvantitativní změny v čase.

Nyní, když znáte základní informace o grafu, můžeme přejít k drobným.

Jak vytvořit vodopádový graf v aplikaci Excel 2016+

Nejprve vám ukážu řešení pro ty, kteří hledají cestu nejmenšího odporu.

Pomocí vestavěné, připravené šablony, kterou používáte, používáte aplikaci Excel 2016 nebo 2022-nastavení vodopádového grafu by vám nemělo zabrat více než několik jednoduchých kroků.

To vše však přichází s cenou omezených možností přizpůsobení a potenciálních problémů s kompatibilitou; starší verze aplikace Excel jednoduše nemohou zpracovat novější typy nativních grafů.

Sečteno a podtrženo: kdykoli se chystáte poslat graf kolem, vždy je bezpečnější sázka vytvořit jej ručně.

Krok č. 1: Nakreslete vodopádový graf.

Nejprve vytvořte výchozí vodopádový graf na základě vašich skutečných dat. Krása této metody spočívá v tom, že nemusíte skákat přes žádné obruče:

1. Zvýrazněte všechna data (A1: B13).
2. Jděte na Vložit tab.
3. Vybrat "Vložte vodopád, trychtýř, pažbu, povrch nebo radarový graf" knoflík.
4. Vybrat "Vodopád."

Váš graf létajících cihel se okamžitě objeví:

Krok č. 2: Nastavte sloupce mezisoučtu a součtu.

Naším dalším krokem je oddělení počátečních a konečných hodnot od „létajících cihel“.

Poklepejte a poté pravým tlačítkem klikněte na sloupce představující datové body „Přepravní zůstatek" a "Aktuální zůstatek“A vyberte„Nastavit jako celkem.”

Technicky se možná budete chtít zastavit a nechat graf tak, jak je. Tento kaskádový graf nám ale nic neříká o datech, která jsou na něm vykreslena.

Proč se spokojit s méně? Místo toho můžete svůj nově vytvořený graf Mario podstatně zlepšit změnou několika věcí sem a tam.

Krok č. 3: Změňte barevné schéma.

Jedním z efektivních způsobů, jak vylepšit graf, je nechat barevné schéma pracovat pro vás, nikoli naopak. Změňte barvu datových řad způsobem, který pomáhá sdělit, o čem graf je.

1. Poklepejte a potom pravým tlačítkem klikněte na položku legendy představující Série „Snížit“.
2. Vybrat "Vyplnit" knoflík.
3. Výběr Červené z palety barev.

Stejným způsobem přebarvit Série „Zvýšení“ na zelenou a Série „Celkem“ na modrou. Jakmile jste tam, váš kaskádový graf by měl vypadat takto:

Krok č. 4: Přizpůsobte rozsahy svislých os svým aktuálním datům.

Chcete -li přiblížit plovoucí sloupce pro další podrobnosti, upravte měřítko svislé osy. Klikněte pravým tlačítkem na primární svislou osu a klikněte na „Formátovat osu.”

V Formátovat osu podokno úloh, postupujte podle těchto jednoduchých kroků:

1. Přepnout na Možnosti osy tab.
2. Nastav Minimální hranice na “50,000.”
3. Nastav Maximální hranice na “110,000.”

Krok č. 5: Dolaďte detaily.

Je logické, že čím více prvků graf obsahuje, tím chaotičtěji to vypadá. Pojďme se tedy zbavit nepotřebných prvků odstraněním legendy a mřížky.

Klikněte pravým tlačítkem na legendu grafu a vyberte „Vymazat“Z nabídky, která se objeví. Stejný postup opakujte pro mřížky.

Nakonec změňte název grafu a můžete tomu říkat den!

Jak vytvořit vodopádový graf v Excelu 2007, 2010 a 2013

Tento výukový program by skončil zde, pokud by výše uvedená metoda byla kompatibilní se všemi verzemi aplikace Excel. Bohužel tomu tak není.

Z tohoto důvodu možná budete chtít zvážit ruční vykreslení grafu od začátku, i když používáte Excel 2016 nebo 2022.

Je zřejmé, že sestavení grafu tímto způsobem zabere více času, ale výplata stojí za to.

Na konci této části se naučíte, jak vytvořit tento přizpůsobitelný dynamický můstkový graf kompatibilní se všemi verzemi aplikace Excel:

Bez dalších okolků dáme míč do pohybu.

Krok č. 1: Připravte data grafu.

Upřímně řečeno, tento krok je nejtěžší.

Jednoduše řečeno, náš vlastní vodopádový graf bude vytvořen na základě skládaného sloupcového grafu. Abychom to ale zvládli, musíme do rovnice přidat další pomocná data.

Pro pohodlí se rychle podívejte, jak by měla být data vašeho grafu organizována do konce přípravné fáze.

Pro ty, kteří dělají své první krůčky na cestě ke zvládnutí Excelu, se tato tabulka může zdát vyloženě skličující, ale ve skutečnosti tento úkol zvládne kdokoli podle podrobných pokynů uvedených níže.

Začněme přidáním tří pomocných sloupců s názvem „Neviditelný” (sloupec B), “Zvýšit” (sloupec C), a "Pokles” (sloupec D), které budou obsahovat fiktivní hodnoty pro umístění vašich aktuálních dat do budoucího grafu.

A pokud sloupce Zvětšit a Pokles jsou spíše samovysvětlující, o co jde sloupec Neviditelný? V zásadě budou tyto hodnoty tvořit neviditelnou základnu budoucího skládaného sloupcového grafu, aby bylo možné podepřít skutečné hodnoty až do příslušné výšky.

Pojďme se vypořádat sloupec Zvětšit první: Do buňky zadejte následující vzorec C2 a přetáhněte ji dolů pomocí rukojeti výplně pro spuštění pro zbývající buňky (C3: C13):

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

Nyní zadejte zrcadlový vzorec do první buňky snížení sloupce (sloupec D) a také jej zkopírujte:

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

Dva dole, jeden pryč. Když jste našli potřebná data grafu, můžete nyní nastavit sloupec Neviditelný přesně zmapovat fluktuace peněžních toků zadáním následujícího vzorce do druhé buňky sloupce (B3) a zkopírujte jej do B12:

1	= B2+C2-D3

POZNÁMKA: Je naprosto nezbytné, abyste opustili první a poslední buňky (B2 a B13) sloupce prázdný!

Nezapomeňte formátovat všechna data grafu jako měnu (Domů> Skupina čísel> Měna).

V tomto okamžiku by takto měla vypadat rozbalená tabulka:

Jako jednu konečnou úpravu nastavte další sloupec s názvem „Štítky”, Která bude obsahovat efektní štítky vlastních dat.

Jakmile tento sloupec nastavíte, zadejte tento vzorec do druhé buňky sloupce Štítky (F3) a zkopírujte jej a ponechte první buňku (F2) a poslední buňka (F13) prázdný:

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

Ti, kteří pracují s jinými formáty čísel, by místo toho měli použít 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: Vytvořte skládaný sloupcový graf.

Dokázali jsme to přes hrb, takže konečně přišel čas na sestavení naší tabulky vodopádů.

1. Vyberte všechna data ve sloupcích Období, neviditelné, zvýšení, a Pokles (A1: D13).
2. Jděte na Vložit tab.
3. Klikněte na „Vložte sloupcový nebo sloupcový graf.”
4. Vybrat "Skládaný sloup.”

Excel sestaví tento jednoduchý graf, který bude nakonec přeměněn na ohromující vodopádový graf:

Krok č. 3: Skrýt sérii „Neviditelný“.

Než přejdeme ke zbytku grafu, skryjte podkladové datové řady a tlačte plovoucí sloupce nahoru. Klikněte pravým tlačítkem na některý z modrých sloupců, které představují Série „Neviditelný“ a otevřete Formát datové řady podokno úloh.

Jakmile tam budete, udělejte pomocné sloupce neviditelné:

1. Jděte na Fill & Line tab-the Vyplnit kartu ve starších verzích aplikace Excel.
2. Pod „Vyplnit,"Vyberte"Bez výplně.”
3. Pod „Okraj,"Klikněte na"Žádná linka.”

Krok č. 4: Upravte barevné schéma.

Ve stejné Formát datové řady podokno úloh, přebarvěte každou datovou řadu podle stejných kroků uvedených výše (Možnosti řady > Vyplnit > Pevná výplň > Barva), aby jej bylo možné intuitivně pochopit na první pohled:

1. Pro Série „Snížit“ výběr Červené.
2. Pro Série „Zvýšení“ výběr zelená.
3. Navíc poklepejte na sloupce „Zůstatek při přenosu“ a "Aktuální zůstatek" a přebarvit je modrý.

V tomto bodě byste měli skončit s tímto:

Krok č. 5: Změňte šířku mezery na „20%“.

Nyní zvětšete šířku sloupců pro lepší vizuální vnímání.

1. Otevři Formát datové řady podokno úloh (klikněte pravým tlačítkem na některou z datových řad a vyberte Formát datové řady) a přepněte na Možnosti řady tab.
2. Změna "Šířka mezery“Až„20%.”

Krok č. 6: Upravte rozsahy svislé osy.

Měřítko osy by mělo být znovu upraveno tak, aby se údaje přiblížily blíže. Chcete -li rychle zrekapitulovat postup pro ty, kteří přeskočili první část tohoto kurzu, postupujte takto:

1. Klikněte pravým tlačítkem na svislou osu a vyberte „Formátovat osu.”
2. V podokně úloh otevřete Možnosti osy tab.
3. Pod „Meze," změna "Minimální“Až„50,000" a "Maximum“Až„110,000.”

Krok č. 7: Přidejte a umístěte vlastní popisky dat.

Tyto štítky s vlastními údaji na ně čekaly příliš dlouho-pojďme je tedy konečně použít. Klikněte pravým tlačítkem na libovolný sloupec a vyberte „Přidejte datové štítky.”

Do grafu budou okamžitě přidány výchozí popisky dat vázané na pomocné hodnoty:

Ale to není přesně to, co hledáme. Chcete -li problém vyřešit, ručně nahraďte výchozí popisky vlastními hodnotami, které jste si předem připravili.

1. Poklepejte na datový štítek, který chcete změnit.
2. Zadejte „=" do Vzorec bar.
3. Zvýrazněte odpovídající buňku v sloupce Štítky.

Nahraďte zbývající popisky dat opakováním stejného postupu pro každý plovoucí sloupec.

Jakmile jste tam, budete muset přetáhnout popisky na jejich příslušné pozice: všechny kladné hodnoty by měly být umístěny nad příslušnými zelenými sloupci, zatímco záporné hodnoty musí být přesunuty pod červené sloupce.

Poté, co úspěšně vyřešíte popisky, by se váš graf Mario měl změnit na něco takového:

Krok č. 8: Vyčistěte oblast grafu.

Nakonec odeberte legendu grafu a mřížky, které do tabulky nic nepřinesou (Klikněte pravým tlačítkem myši > Vymazat). Změňte název grafu a tam máte svůj vodopádový graf!