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

Pokud používáte Excel poměrně často, víte z první ruky výhody grafů. Grafické znázornění vašich dat je velmi užitečné, když chcete provést srovnání nebo určit trend na první pohled.

Microsoft Excel má spoustu předdefinovaných typů grafů, včetně sloupců, řádků, koláčů, pruhů, radar atd. V tomto článku pojedeme nad rámec vytváření základních grafů a podíváme se blíže na speciální typ grafu – vodopádový graf v aplikaci Excel. Zjistíte, co je vodopádová tabulka a jak může být užitečná. Znáte tajemství vytváření vodopádového grafu v aplikaci Excel 2010 – 2016 a dozvíte se o různých nástrojích, které to zvládnou za minutu.

Takže pojďme začít zdokonalovat své znalosti aplikace Excel!: )

• Co je vodopádový graf v aplikaci Excel?
• Jak vytvořit mostový graf Excel
• Doplňky pro vytváření vodopádových grafů

Co je vodopádový graf v aplikaci Excel?

Nejprve se podívejme, jak by měl jednoduchý vodopádový graf vypadat a kdy se může hodit.

Vodopádový graf je ve skutečnosti speciální typ sloupcového grafu aplikace Excel. Obvykle se používá k demonstraci toho, jak se počáteční pozice zvyšuje nebo snižuje řadou změn.

První a poslední sloupce v typickém vodopádovém grafu představují celkové hodnoty. Zdá se, že mezilehlé sloupce se pohybují a ukazují pozitivní nebo negativní změnu z jednoho období do druhého a končí v konečné celkové hodnotě. Tyto sloupce jsou zpravidla barevně odlišeny pro rozlišení kladných a záporných hodnot. O kousek dále v tomto článku budete znát trik, jak zajistit, aby mezilehlé sloupce plavaly.

Vodopádový graf je také známý jako graf mostu Excel, protože plovoucí sloupce vytvářejí takzvaný most spojující koncové body.

Tyto grafy jsou velmi užitečné pro analytické účely. Pokud potřebujete vyhodnotit zisk společnosti nebo výnosy produktu, provést inventarizaci nebo analýzu prodeje nebo jen ukázat, jak se během toho roku změnil počet vašich přátel na Facebooku , vodopádový graf v aplikaci Excel je právě to, co potřebujete.

Jak vytvořit mostový graf aplikace Excel

Neztrácejte čas hledáním typu vodopádového grafu v aplikaci Excel, vyhráli jste „Tady to nenajdeme. Problém je v tom, že Excel nemá předdefinovanou šablonu vodopádového grafu.“ Svou vlastní verzi však můžete snadno vytvořit pečlivým uspořádáním dat a použitím standardního typu grafu skládaného sloupce aplikace Excel.

• Změna uspořádání datové tabulky
• Vložení vzorců
• Vytvořit standardní skládaný sloupcový graf
• Transformovat sloupcový graf na vodopádový graf
• Formátovat graf mostu Excel

Pojďme vytvořte jednoduchou ukázkovou tabulku s kladnými a zápornými hodnotami, abyste věděli věci lépe. Jako příklad si vezmu částku prodeje. Pokud se podíváte na níže uvedenou tabulku, uvidíte, že prodeje během několika měsíců rostou a během jiných klesají ve srovnání s výchozí pozicí.

Mostový graf aplikace Excel bude dokonalým způsobem, jak vizualizovat tok prodeje za dvanáct měsíců. Pokud však nyní použijete šablonu grafu skládaného sloupce na tyto konkrétní hodnoty, nedostanete nic podobného jako vodopádový graf. První věcí, kterou byste měli udělat, je pečlivě změnit uspořádání dat.

Krok 1: Změna uspořádání datové tabulky

Začnete vkládáním dalších tří sloupců do tabulky aplikace Excel. Pojďme je nazvat Base, Fall and Rise. Sloupec Base bude vypočítaná částka, která se použije jako výchozí bod pro sérii Fall and Rise v grafu. Všechna záporná čísla ze sloupce Flow Flow budou umístěna do Sloupec Pád a všechna kladná čísla budou ve sloupci Vzestup.

Také jsem přidal řádek Konec v dolní části měsíce seznamu pro výpočet částky prodeje za celý rok. Nyní přejděte k dalšímu kroku a vyplňte tyto sloupce potřebnými hodnotami.

Krok 2. Vložte vzorce

Nejlepší způsob, jak tabulku dokončit, je zadávání speciálních vzorců do prvních buněk v odpovídajících sloupcích a poté je pomocí úchytu výplně zkopírujte do sousedních buněk.

1. Vyberte buňku C4 ve sloupci Pád a zadejte do ní následující vzorec: =IF(E4<=0, -E4,0)

   Vzorec říká, že pokud je hodnota v buňce E4 menší nebo rovna nule, záporné číslo se zobrazí jako kladné a kladné číslo se zobrazí jako nula.

   Poznámka . Pokud chcete, aby všechny hodnoty ve vodopádovém grafu ležely nad nulou, musíte do vzorce zadat znaménko minus (-) před odkazem na druhou buňku (E4). A dvě minusky udělají plus.
2. Zkopírujte vzorec až na konec tabulky.
   
3. Klikněte na buňku D4 a zadejte =IF(E4>0, E4,0).

   Znamená to, že hodnota v buňka E4 je větší než nula, všechna kladná čísla se zobrazí jako kladná a záporná jako nula.

4. Pomocí úchytu zkopírujte tento vzorec dolů do sloupce.
   
5. Vložte poslední vzorec =B4+D4-C5 do buňky B5 a zkopírujte jej dolů; zahrnout koncový řádek.
   

Tento vzorec vypočítá základní hodnoty, které podpoří vzestupy a poklesy do příslušné výšky.

Krok 3. Vytvořte standardní skládaný sloupcový graf

Nyní jsou vaše data dobře organizovaná a jste připraveni sestavit samotný graf.

1. Vyberte vaše data včetně záhlaví sloupců a řádků, vylučte sloupec Tok prodeje.
2. Přejděte do skupiny Grafy na kartě VLOŽIT.
3. Klikněte na ikonu Vložit sloupcový graf a vyberte Skládaný sloupec z rozevíracího seznamu.
   

   Graf se zobrazí v listu, ale jen stěží vypadá jako vodopádový graf. Udělejte další krok a přeměňte skládaný sloupcový graf na přemosťovací graf aplikace Excel.
   

Krok 4. Transformujte sloupcový graf do vodopádového grafu

Nastal čas znát tajemství. Chcete-li získat vodopádový graf ze skládaného sloupce, musíte nastavit neviditelnou základní řadu.

1. Klepnutím na základní řadu je vyberte, klepněte pravým tlačítkem a vyberte možnost Formátovat datovou řadu … z místní nabídka.
   

   Vpravo od listu v aplikaci Excel 2013/2016 se okamžitě zobrazí podokno Formát datové řady.

2. Klikněte na ikonu Vyplnit & Řádek.
3. V části Vyplnit vyberte možnost Bez výplně a v části Orámování žádný řádek.
   

   Když se modré sloupce stanou neviditelnými, stačí odstranit základnu z legendy grafu a zcela skrýt všechny stopy řady Base.
   

Krok 5. Formátování grafu mostu Excel

Pojďme to dokončit s malým formátováním. Nejprve zvýrazním létající cihly a zvýrazním je počáteční a koncové hodnoty v grafu:

1. Vyberte v grafu řadu Fall a přejděte na kartu FORMAT v části CHART TOOL S.
2. Klikněte na Výplň tvaru ve skupině Tvarové styly.
   
3. V rozbalovací nabídce vyberte požadovanou barvu. .

   Zde můžete také experimentovat s obrysy sloupců nebo k nim přidat speciální efekty. K provedení změn stačí použít možnosti Tvarový obrys a Tvarové efekty na kartě FORMÁT.

   Pak byste měli udělat stejný trik se sérií Rise. Pokud jde o sloupce Začátek a Konec, je třeba je barevně kódovat jednotlivě, ale měly by mít stejnou barvu.

   Až budete hotovi, graf by měl vypadat jako ten níže:
   

   Poznámka. Alternativně můžete změnit barvu a obrys sloupců v grafu otevřením podokna Formát datových řad nebo výběrem možností Vyplnit nebo Obrys v nabídce pravého tlačítka.

   Poté můžete odstranit nadbytečné mezery mezi sloupci, aby se staly blíže k sobě navzájem:

4. Poklepáním na jeden ze sloupců grafu zobrazte podokno Format Data Series.
5. Změňte Šířka mezery na něco menšího, například 15%. Zavřete podokno.
   

   Otvory v mostním grafu jsou nyní opraveny.
   

   Když se podíváte na vodopádovou tabulku výše, zdá se, že některé z létajících cihel mají stejnou velikost. Když však odkazujete na tabulku s údaji, uvidíte, že zobrazené hodnoty se liší. Pro přesnější analýzu doporučuji ke sloupcům přidat štítky s údaji.

6. Vyberte série, které chcete označit.
7. Klikněte pravým tlačítkem a v místní nabídce vyberte možnost Přidat datové štítky.
   

   Opakujte postup pro ostatní série. Můžete také upravit polohu štítku, písmo a barvu textu, aby byla čísla čitelnější.

   Poznámka. Pokud existuje zjevný rozdíl ve velikosti sloupce a zvláštnosti nejsou důležité, můžete popisky dat vynechat, ale pro lepší interpretaci dat byste měli přidat osu Y.

Až skončíte s označováním sloupců, zbavte se zbytečných prvků, jako jsou nulové hodnoty a legenda. Můžete také změnit výchozí název grafu na něco popisnějšího. Podívejte se prosím na jeden z mých předchozích blogových příspěvků jak přidat názvy do grafů aplikace Excel.

Můj vodopádový graf je připraven! Vypadá úplně jinak než běžně používané typy grafů a je velmi čitelný, že?

Doplňky pro vytváření vodopádových grafů

Jak vidíte, není vůbec obtížné ručně vytvořit vodopádový graf v aplikaci Excel 2016 – 2010.Pokud si ale nechcete přeorganizovat svá data, můžete použít speciální doplňky, které za vás udělají vše.

Jon Peltier nabízí využití svého časově úsporného nástroje Peltier Tech Chart Utility k automatickému vytváření vodopádových grafů a dalších vlastních grafů ze surových dat. Můžete si vybrat, zda chcete vytvořit standardní vodopádový graf nebo skládaný vodopádový graf. Není nutné zadávat žádné vzorce, stačí vybrat data, kliknout na příkaz Vodopádový graf v Pás karet, nastavte několik možností, klikněte na OK a graf mostu Excel je připraven. Kromě vlastních grafů vám doplněk poskytuje různé nástroje pro grafy, data a obecné nástroje, které vám usnadní práci v aplikaci Excel.

Můžete dokonce vytvořit vodopádový graf online a přijímat jej jako soubor Excel e-mailem. Je to možné díky skvělé online službě vodopádového grafu. Potřebujete jen Chcete-li odeslat své údaje, zadejte svou e-mailovou adresu a počkejte méně než minutu, než se vygeneruje váš graf. Poté zkontrolujte svůj doručená pošta. Vodopádový graf vám bude zaslán v souboru Excel. Poté můžete změnit název, štítky, barvy atd. Jako v jakémkoli jiném grafu aplikace Excel. Nelze však změnit číselná data. V tomto případě budete muset znovu vytvořit graf. Pokud chcete, aby vodopádový graf vypadal podle očekávání, měli byste také dodržovat některá pravidla pro odesílání údajů.

Složitější graf, který chcete build, tím složitější vzorce musíte zadat při změně uspořádání dat. A šance na chybu se zvyšuje. V této situaci vám doplněk Waterfall Chart Creator pro Microsoft Excel pomůže ušetřit čas a úsilí. S tímto doplňkem můžete vytvářet, měnit a aktualizovat více vodopádových grafů najednou. Umožňuje určit barvy, plnou nebo přechodovou výplň, zobrazit hodnoty a umístění a nabízí mnoho dalších možností. Můžete také přizpůsobit výchozí nastavení a barvy pro nové grafy.

Nyní máte v Excelu kolekci vodopádových grafů. Doufám, že pro vás nebude problém ručně vytvořit vlastní verzi. můstkového grafu. Jinak můžete využít doplňky doplňkového grafu aplikace Excel.

Děkujeme za přečtení.

Pokud vás zajímají další způsoby vizualizace dat, mohou být také následující články zajímavé pro vás:

• Jak vytvořit graf v aplikaci Excel a uložit jej jako šablonu grafu
• Jak vytvořit výsečový graf v aplikaci Excel
• Jak k vytvoření histogramu v aplikaci Excel
• Jak vytvořit Ganttův diagram v aplikaci Excel
• Přizpůsobení grafů aplikace Excel: přidání názvu grafu, os, legendy, štítků dat a dalších
• Vytvořte graf v aplikaci Excel ze dvou nebo více různých listů.
• Co jsou jiskrové čáry aplikace Excel a jak je vložit k analýze dat