Wykresy kaskadowe (Waterfall)
Wykres kaskadowy (waterfall) pozwala w analityce na lepsze zrozumienie skumulowanego wpływu poszczególnych wartości (pozytywnych i negatywnych) na całość. Te wartości mogą być podzielone oczywiście w czasie lub innych wymiarach (np. kategorie). Wykres wodospadowy znany jest także pod nazwą kaskadowego (cascade) oraz mostowego (bridge). Wykres pozwala na łatwą interpretację wpływu poszczególnych czynników na totalną wartość, na przykład ocenę wpływu poszczególnych kategorii na różnicę w wyniku finansowym pomiędzy dwoma okresami, a także iterację dalszego ciągu, np. rozkład całego roku w rozbiciu na miesiące i przejścia pomiędzy kategoriami.
Zazwyczaj prezentowany jest w formie dwóch stałych kolumn (start / end), pomiędzy którymi znajdują się kolumny pływające. Kolumny pływające często określone są dwoma kolorami oznaczającymi wzrost lub spadek wartości.
Jak utworzyć wykres wodospadowy?
Jeśli korzystasz z pakietu Office 2019 i wyżej to wykres wodospadowy jest wbudowany w Excela. Znajdziesz go na wstążce Wstaw (Insert). Wykres wodospadowy można także utworzyć z wykorzystaniem wykresu kolumnowego lub kombinowanego (combo chart).
Poniżej opisuję wszystkie 3 metody:
Wykres kaskadowy (Office 365):
Tworzymy poprzez zaznaczenie danych do wykresu i wybranie wykresu kaskadowego ze wstążki. Excel Automatycznie powinien przygotować za nas wykres (pod warunkiem, że dane są odpowiednio zorganizowane). Jak zorganizować dane? W lewej kolumnie utrzymaj nazwy kategorii, natomiast w prawej wartości. Excel sam wywnioskuje, których danych użyć (przykład poniżej).
Wykres kolumnowy (Excel w dowolnej wersji):
Utwórz tabelę z następującymi kolumnami:
Category, Base, Fall, Rise, Value.
W polu category znajdują się Twoje etykiety osi. Pole value oznacza wartość danej kategorii. Pola Rise i Fall determinują wzrost i spadek. Pole base wykorzystywane jest do pobrania poprzedniej wartości oraz dokonania działań z obecnymi wzrostem lub spadkiem.
Pole Rise będzie korzystać z formuły (zgodnie ze zrzutem ekranu):
=IF(G29>0;G29;0)
Pole Fall determinujące spadek korzysta z formuły:
=IF(G29<0;-G29;0)
Pole Base pobiera wartość poprzednią (w przypadku pierwszej kategorii zostawiam wartość pustą) oraz dodaje do niej wartość Rise i odejmuje wartość Fall.
Po przeciągnięciu formuł do ostatniej kategorii efekt powinien być zgodny z powyższymi zrzutami ekranu. Przejdźmy teraz do budowy wykresu. Zaznacz swoje dane z nagłówkami pomijając kolumnę Value. Następnie wybierz na wstążce Wstaw (Insert) wykres kolumnowy skumulowany. Excel automatycznie utworzy wykres jak poniżej:
Wykonaj następujące kroki:
– Zmień formatowanie serii Base – wybierz brak wypełnienia (dodaj wypełnienie tylko w kolumnie total), oraz pozostałych kolumn (kolor wedle uznania)
– Dodaj etykiety danych tylko dla serii Rise oraz pojedynczą etykietę dla serii Total.
Po tych dwóch prostych krokach mój wykres prezentuje się następująco:
Przejdźmy teraz do trzeciej możliwości.
Wykres kolumnowo-linowy (kombinowany)
Utwórz tabelę z następującymi kolumnami:
Category, End, Before, After, Value, Labels.
Kolumna Category zawierać będzie jak poprzednio etykiety osi. Kolumna End zawiera dane krańcowe, w naszym przypadku niech będzie to pierwsza wartość oraz wartość końcowa. W kolumnie Before znajdować się będzie suma zasięgu od poprzedniego elementu rozciągająca się w miarę przechodzenia dalej (pamiętaj, aby zamrozić początek zakresu).
=SUM($G$51:G51)
Kolumna After również będzie zawierać sumę, jednak w tym przypadku początkowym zakresem będzie element poprzedni (zablokuj go) i aktualny jak na zrzucie ekranu poniżej.
=SUM($G$51:G52)
Przeciągnij formuły w dół, aby uzyskać widok jak na powyższym zrzucie ekranu.
Ostatnia będzie dodatkowa kolumna Labels. Zawierać ona będzie nasze etykiety danych, abyśmy nie musieli ich ręcznie przenosić. Utwórz nową kolumnę i wstaw w niej odpowiednio identyczną wartość dla krańcowych wartości (end), natomiast dla wartości wewnętrznych użyj formuły MAX zaznaczając odpowiadający wiersz w kolumnach Before i After (zrzut ekranu poniżej):
Pozostaje nam teraz utworzyć wykres. Zaznacz kolumny wraz z danymi w zakresie Category : After, a następnie przejdź do wstążki Wstaw (Insert) i wybierz wszystkie wykresy (zgodnie z poniższym zrzutem ekranu), następnie w menu w karcie All charts wybierz wykres Combo i ustaw odpowiednio dla serii End wykres kolumnowy, a dla Before i After wykres liniowy.
Po utworzeniu wykresu zaznacz jedną z serii liniowych i dodaj element Up/Down Bars. Utworzone zostaną kolumny o rozpiętości pomiędzy liniami.
Pozostaje teraz odpowiednio sformatować kolory kolumn wzrostów i spadków, a także wyłączyć wypełnienie etykiet liniowych. Możesz zrobić to wszystko z poziomu formatowania wykresu.
Przejdźmy teraz do dodania serii etykiet danych. W tym celu wejdź w ustawienia wykresu i dodaj nową, liniową serię danych. Dodaj do niej etykiety danych i zmień wypełnienie linii na brak wypełnienia. Następnie dodaj do tej serii etykiety danych, zaznacz opcję Value From Cells (wartości z komórek) i wybierz wartości z kolumny Value. Dzięki temu Twoje etykiety utrzymają odpowiednią pozycję i podadzą właściwe dane dotyczące spadku bądź wzrostu.
To by było na tyle w kwestii tworzenia wykresów wodospadowych. Choć dla początkujących zadanie może wydać się trudne, to przećwiczenie na kilku przykładach pozwoli nabrać wprawę i płynność w tworzeniu tego typu wizualizacji. Oczywiście najlepiej korzystać z wbudowanych rozwiązań, które są najszybsze.