Jak utworzyć wykres wodospadu w programie Excel

Ten samouczek pokaże, jak utworzyć wykres kaskadowy we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.

Wykres kaskadowy (nazywany również wykresem pomostowym, wykresem latających cegieł, wykresem kaskadowym lub wykresem Mario) to wykres, który wizualnie przedstawia skumulowany efekt, który seria kolejnych dodatnich lub ujemnych wartości przyczyniła się do końcowego wyniku.

Konwencjonalnie, pierwsza i ostatnia kolumna reprezentują wartości początkowe i końcowe oddzielone korektami pośrednimi, które przedstawiają kolejność kroków, jakie trzeba było wykonać, aby przejść z punktu A do punktu B.

Ten typ wykresu jest szeroko stosowany przez profesjonalistów we wszystkich głównych branżach do przeprowadzania analiz ilościowych - od dekodowania sprawozdań finansowych po monitorowanie zapasów w sklepie.

Wykresy pomostowe zostały wprowadzone jako wbudowany typ wykresu tylko w programie Excel 2016, co oznacza, że ​​użytkownicy wcześniejszych wersji programu Excel nie mają innego wyjścia, jak tylko utworzyć wykres od podstaw.

Z tego powodu w tym samouczku nauczysz się tworzyć ten wykres Mario zarówno ręcznie od podstaw, jak i przechodząc do natywnego typu wykresu, który wykonuje całą brudną robotę za Ciebie.

Pierwsze kroki

Zaraz za bramą musimy wrzucić do obrazu trochę surowych danych. Załóżmy więc, dla celów ilustracyjnych, że postanowiłeś przeanalizować kwartalne przepływy pieniężne netto jakiejś fikcyjnej firmy.

Mając to wszystko na uwadze, rozważ następujące dane:

Aby ułatwić Ci dopasowanie wykresu do własnych potrzeb, porozmawiajmy bardziej szczegółowo o tabeli:

• Okres: Ta kolumna przedstawia sekwencję wartości dodatnich i ujemnych do wykreślenia na wykresie.
• Przepływ gotówki netto: Ta kolumna składa się z odpowiednich wartości. Pierwszy (B2) i ostatni (B13) komórki zawierają wartości początkowe i końcowe, natomiast pozostałe liczby pomiędzy nimi oznaczają ilościowe zmiany w czasie.

Teraz, gdy znasz już podstawy wykresu, możemy przejść do sedna sprawy.

Jak utworzyć wykres wodospadu w programie Excel 2016+

Po pierwsze, pozwól, że pokażę ci rozwiązanie dla tych, którzy szukają drogi najmniejszego oporu.

Korzystając z wbudowanego, gotowego szablonu - pod warunkiem, że korzystasz z Excela 2016 lub 2022 - konfiguracja wykresu kaskadowego nie powinna zająć więcej niż kilka prostych kroków.

Jednak wszystko to wiąże się z ceną ograniczonych opcji dostosowywania i potencjalnych problemów ze zgodnością; starsze wersje programu Excel po prostu nie mogą przetwarzać nowszych typów wykresów natywnych.

Konkluzja: za każdym razem, gdy zamierzasz rozesłać wykres, zawsze bezpieczniej jest zbudować go ręcznie.

Krok 1: Sporządź wykres kaskadowy.

Na początek utwórz domyślny wykres kaskadowy na podstawie rzeczywistych danych. Piękno tej metody polega na tym, że nie musisz w ogóle przeskakiwać przez żadne obręcze:

1. Zaznacz wszystkie dane (A1:B13).
2. Przejdź do Wstawić patka.
3. Wybierz "Wstaw wykres wodospadowy, lejkowy, giełdowy, powierzchniowy lub radarowy" przycisk.
4. Wybierać "Wodospad."

Natychmiast pojawi się wykres z latającymi klockami:

Krok #2: Ustaw kolumny sum częściowych i sum.

Naszym następnym krokiem jest oddzielenie wartości początkowej i końcowej od „latających cegieł”.

Kliknij dwukrotnie, a następnie kliknij prawym przyciskiem myszy kolumny reprezentujące punkty danych „Saldo przeniesienia" oraz "Aktualne saldo” i wybierz „Ustaw jako sumę.”

Technicznie rzecz biorąc, możesz chcieć zatrzymać się w tym miejscu i pozostawić wykres bez zmian. Ale ten wykres kaskadowy nie mówi nam nic o danych na nim wykreślonych.

Dlaczego zadowalać się mniej? Zamiast tego możesz znacznie ulepszyć swój nowo utworzony wykres Mario, zmieniając kilka rzeczy tu i tam.

Krok 3: Zmień schemat kolorów.

Jednym ze skutecznych sposobów na ulepszenie wykresu jest pozwolenie, aby schemat kolorów działał dla Ciebie, a nie na odwrót. Zmień kolor serii danych w sposób, który pomoże przekazać, o co chodzi na wykresie.

1. Kliknij dwukrotnie, a następnie kliknij prawym przyciskiem myszy element legendy reprezentujący Seria „Spadek”.
2. Wybierz "Napełnić" przycisk.
3. Wybierać czerwony z palety kolorów.

W ten sam sposób pokoloruj ponownie Seria „Wzrost” na zielono i Seria „Suma” na niebieski. Tam twój wykres kaskadowy powinien wyglądać tak:

Krok 4: Dostosuj zakresy osi pionowej do rzeczywistych danych.

Aby powiększyć ruchome kolumny w celu uzyskania bardziej szczegółowych informacji, zmodyfikuj skalę osi pionowej. Kliknij prawym przyciskiem myszy główną oś pionową i kliknij „Oś formatuj.”

w Formatuj oś okienku zadań, wykonaj następujące proste czynności:

1. Przełącz na Opcje osi patka.
2. Ustaw Minimalne granice do "50,000.”
3. Ustaw Maksymalne granice do "110,000.”

Krok 5: Dostosuj szczegóły.

To zdrowy rozsądek, że im więcej elementów składa się na wykres, tym bardziej bałagan wygląda. Pozbądźmy się więc niepotrzebnych elementów, usuwając legendę i linie siatki.

Kliknij prawym przyciskiem myszy legendę wykresu i wybierz „Kasować” z menu, które się pojawi. Powtórz ten sam proces dla linii siatki.

Na koniec zmień tytuł wykresu i możesz nazwać to dniem!

Jak utworzyć wykres wodospadu w programie Excel 2007, 2010 i 2013?

Ten samouczek zakończyłby się właśnie tutaj, gdyby metoda pokazana powyżej była zgodna ze wszystkimi wersjami programu Excel. Niestety tak nie jest.

Z tego powodu warto rozważyć ręczne wykreślenie wykresu od podstaw, nawet jeśli używasz programu Excel 2016 lub 2022.

Oczywiście złożenie wykresu w ten sposób zajmuje więcej czasu, ale wypłata jest warta wysiłku.

Pod koniec tej sekcji dowiesz się, jak utworzyć ten dostosowywalny, dynamiczny wykres pomostowy zgodny ze wszystkimi wersjami programu Excel:

Bez zbędnych ceregieli, rzućmy piłkę.

Krok 1: Przygotuj dane wykresu.

Szczerze mówiąc, ten krok jest najtrudniejszy.

Mówiąc najprościej, nasz niestandardowy wykres kaskadowy zostanie utworzony na podstawie skumulowanego wykresu kolumnowego. Ale żeby to zrobić, musimy dodać do równania dodatkowe dane pomocnicze.

Dla Twojej wygody, pod koniec fazy przygotowawczej rzuć okiem na to, jak powinny być zorganizowane dane na wykresie.

Dla tych, którzy stawiają pierwsze kroki na drodze do mistrzostwa w Excelu, ta tabela może wydawać się wręcz zniechęcająca, ale w rzeczywistości każdy może poradzić sobie z tym zadaniem, postępując zgodnie ze szczegółowymi instrukcjami przedstawionymi poniżej.

Zacznijmy od dodania trzech kolumn pomocniczych o nazwie „Niewidzialny” (kolumna B), “Zwiększać” (kolumna C), oraz "Zmniejszać” (kolumna D), które będą zawierać wartości pozorne do pozycjonowania rzeczywistych danych na przyszłym wykresie.

I jeśli kolumny Zwiększ oraz Zmniejszać są raczej oczywiste, o co chodzi kolumna Niewidoczne? Zasadniczo wartości te będą stanowić niewidoczną podstawę przyszłego skumulowanego wykresu kolumnowego, aby podeprzeć rzeczywiste wartości do odpowiedniej wysokości.

Zajmijmy się kolumna Zwiększ po pierwsze: wprowadź następującą formułę do komórki C2 i przeciągnij go w dół za pomocą uchwytu wypełniania, aby wykonać go dla pozostałych komórek (C3:C13):

1	=JEŻELI(E2>0, E2, 0)

Teraz wpisz wzór lustrzany w pierwszej komórce kolumna Zmniejszenie (kolumna D) i skopiuj go również:

1	=JEŻELI(E2<=0, -E2, 0)

Dwóch w dół, jeden do wyjścia. Po znalezieniu niezbędnych danych wykresu możesz teraz skonfigurować kolumna Niewidoczne aby dokładnie wykreślić fluktuacje przepływów pieniężnych, wpisując następujący wzór do drugiej komórki kolumny (B3) i skopiować do B12:

1

=B2+C2-D3

UWAGA: Absolutnie konieczne jest pozostawienie pierwszej i ostatniej komórki (B2 oraz B13) kolumny puste!

Nie zapomnij sformatować wszystkich danych wykresu jako waluty (Dom > Grupa numerów > Waluta).

Oto jak powinna wyglądać Twoja rozszerzona tabela:

Jako ostatnią korektę skonfiguruj kolejną kolumnę o nazwie „Etykiety”, który będzie zawierał fantazyjne niestandardowe etykiety danych.

Po skonfigurowaniu tej kolumny wprowadź tę formułę w drugiej komórce kolumny Etykiety (F3) i skopiuj go, pozostawiając pierwszą komórkę (F2) i ostatnia komórka (F13) pusty:

1	=JEŻELI(E3<=0, TEKST(-E3, "$#,##")&ZNAK(10)&TEKST(-E3/SUMA(B3:D3); "(#% ↓)")), TEKST(E3, "$#,##")&ZNAK(10)&TEKST(E3/SUMA(B3:D3), "(#% ↑)"))

Osoby pracujące z innymi formatami liczb powinny zamiast tego zastosować tę formułę:

1	=JEŻELI(E3<=0, TEKST(-E3, "#,##")&ZNAK(10)&TEKST(-E3/SUMA(B3:D3), "(#% ↓)"), TEKST(E3, " #,##")&ZNAK(10)&TEKST(E3/SUMA(B3:D3), "(#% ↑)"))

Krok 2: Zbuduj skumulowany wykres kolumnowy.

Przeszliśmy przez garb, więc w końcu nadszedł czas na zbudowanie naszego wykresu kaskadowego.

1. Wybierz wszystkie dane w kolumnach Okres, niewidoczny, wzrost, oraz Zmniejszać (A1:D13).
2. Przejdź do Wstawić patka.
3. Kliknij "Wstaw wykres kolumnowy lub słupkowy.”
4. Wybierać "Ułożona kolumna.”

Excel skompiluje ten prosty wykres, który ostatecznie zostanie przekształcony w oszałamiający wykres kaskadowy:

Krok 3: Ukryj serię „Niewidoczny”.

Zanim przejdziemy do reszty wykresu, ukryj podstawowe serie danych, przesuwając pływające kolumny na górę. Kliknij prawym przyciskiem myszy dowolną z niebieskich kolumn reprezentujących: Seria „Niewidzialne” i otwórz Formatuj serię danych Okno zadań.

Gdy już tam będziesz, spraw, aby kolumny pomocnicze były niewidoczne:

1. Przejdź do Wypełnij i linia tab-the Napełnić w starszych wersjach programu Excel.
2. Pod "Napełnić," Wybierz "Nie wypełniać.”
3. Pod "Granica," Kliknij "Brak linii.”

Krok 4: Dostosuj schemat kolorów.

W tym samym Formatuj serię danych w okienku zadań, ponownie pokoloruj każdą serię danych, wykonując identyczne kroki przedstawione powyżej (Opcje serii > Napełnić > Wypełnienie stałe > Kolor), aby na pierwszy rzut oka można było intuicyjnie zrozumieć:

1. Do Seria „Spadek”, wybierać czerwony.
2. Do Seria „Wzrost”, wybierać Zielony.
3. Dodatkowo kliknij dwukrotnie kolumny „Saldo przeniesienia” oraz „Bieżące saldo” i przekoloruj je niebieski.

Oto, co powinieneś mieć w tym momencie:

Krok 5: Zmień szerokość odstępu na „20%”.

Teraz zwiększ szerokość kolumn, aby uzyskać lepszą percepcję wizualną.

1. Otworzyć Formatuj serię danych okienko zadań (kliknij prawym przyciskiem myszy jedną z serii danych i wybierz Formatuj serię danych) i przejdź do Opcje serii patka.
2. Reszta "Szerokość szczeliny" do "20%.”

Krok #6: Dostosuj zakresy osi pionowej.

Ponownie, skala osi powinna zostać zmodyfikowana, aby dokładniej przybliżyć dane. Aby szybko podsumować proces dla tych, którzy pominęli pierwszą część tego samouczka, oto jak to zrobić:

1. Kliknij prawym przyciskiem myszy oś pionową i wybierz „Oś formatuj.”
2. W okienku zadań otwórz Opcje osi patka.
3. Pod "Miedza," reszta "Minimum" do "50,000" oraz "Maksymalny" do "110,000.”

Krok 7: Dodaj i umieść niestandardowe etykiety danych.

Te niestandardowe etykiety danych czekały zbyt długo, więc w końcu wykorzystajmy je. Kliknij prawym przyciskiem myszy dowolną kolumnę i wybierz „Dodaj etykiety danych.”

Natychmiast do wykresu zostaną dodane domyślne etykiety danych powiązane z wartościami pomocniczymi:

Ale to nie jest dokładnie to, czego szukamy. Aby obejść ten problem, ręcznie zastąp etykiety domyślne wcześniej przygotowanymi wartościami niestandardowymi.

1. Kliknij dwukrotnie etykietę danych, którą chcesz zmienić.
2. Wejść "=” do Pasek formuły.
3. Zaznacz odpowiednią komórkę w kolumny Etykiety.

Zastąp pozostałe etykiety danych, powtarzając ten sam proces dla każdej kolumny zmiennoprzecinkowej.

Tam będziesz musiał przeciągnąć etykiety na ich odpowiednie pozycje: wszystkie wartości dodatnie należy umieścić nad odpowiednimi zielonymi kolumnami, a ujemne należy przesunąć pod kolumny czerwone.

Po pomyślnym rozprawieniu się z etykietami Twój wykres Mario powinien przekształcić się w coś takiego:

Krok #8: Oczyść obszar wykresu.

Na koniec usuń legendę wykresu i linie siatki, które nie wnoszą nic do tabeli (Kliknij prawym przyciskiem myszy > Kasować). Zmień tytuł wykresu, a tam masz wykres kaskadowy!