Zobacz jak wstawić wykres kaskadowy w programie Excelu, dostosować go do swoich potrzeb i kiedy go wykorzystywać. To jeden z trudniejszych wykresów i z budową bardziej skomplikowaną od podstawowych wykresów. Mimo to warto się z nim oswoić, bo prezentacja danych za jego pomocą często niesie wiele korzyści.

.Jeśli zajmujemy się analizami stanów magazynowych czy innych podobnych danych, co jakiś czas może ktoś nam zadać pytanie „Co się zadziało, że mamy teraz aż lub tylko X sztuk produktu Y”. Odpowiedź na to pytanie często nie będzie prosta ani szybka, jeśli nie mamy odpowiednio przygotowanych narzędzi analitycznych. Najskuteczniejszym narzędziem jest prezentacja poszczególnych przyczyn zmian na wykresie kaskadowym, inaczej zwanym wodospadowym czy waterfall. W obecnych wersjach Excela jest to jeden z gotowych do wyboru rodzajów wykresów. Wcześniej takie wykresy wymagały trochę wysiłku w odpowiednim przygotowaniu danych.

Wykres kaskadowy – przygotowanie danych

W naszym przykładzie mamy dane prezentujące dla poszczególnych produktów – warzyw – stan początkowy, stan końcowy oraz przyczyny zmian stanów czyli dostawa, zniszczenie podczas transportu czy na magazynie, przeterminowanie produktów oraz sprzedaż produktów.

Wstawianie tabeli przestawnej i formuł pomocniczych do wykresu kaskadowego (waterfall)

Budując dashboard aż prosi się żeby całość oprzeć o tabele przestawne i wykresy przestawne. Obecnie nie możesz w wstawić wykresu kaskadowego w Excelu bezpośrednio na tabeli przestawnej, dlatego musimy posiłkować się zwykłymi formułami pomocniczymi. Zaczynamy od przygotowania pierwszej tabeli przestawnej, która będzie prezentował zmiany stanów dla wybranego produktu. Obok dokładamy formuły, które odnoszą się bezpośrednio do tabeli przestawnej, będzie to nasze źródło danych do wykresu.

Wstawianie fragmentatora do szybkiej zmiany filtru

Aby wygodnie przestawiać wykres na inne produkty dokładamy fragmentator (karta Analiza > Wstaw fragmentator).

Schemat jak wstawić wykres kaskadowy w Excelu

Kolejnym krokiem jest wstawienie naszego wykresu kaskadowego. Zaznaczamy dane od D3 do E9 i na karcie Wstawianie w grupie Wykresy wybieramy wykres kaskadowy.

Poniżej standardowy układ, który proponuje nam Excel. Warto zmienić wygląd tego wykresu aby był bardziej czytelny. Bardzo ważna rzecz na tym etapie to kliknięcie prawym przyciskiem myszy na ostatnim słupku i wybranie opcji „Ustaw jako sumę”.

Dostosowanie wyglądu wykresu kaskadowego

Zmiany jakie wprowadzamy:

– tytuł wykresu – zmieniamy na Zmiany stanów magazynowych

– usuwamy legendę i linie siatki

– zmieniamy kolory wzrostów i spadków na bardziej rozpoznawalne czyli zielone wzrosty i czerwone spadki.

Wykres kaskadowy w Excelu niestety ma swoje ograniczenia w edycji niektórych elementów. Jak zaznaczymy całą serie danych to przestawimy kolor dla wszystkich punktów danych. Jeśli zmienimy kolory pojedynczych punktów to nie będą się one zmieniać w zależności od znaku wartości (spadki/wzrosty).

Kolory możemy zmieniać na karcie Projektowanie > Zmień kolory ale żaden z gotowych układów nie daje nam pożądanego efektu.

Drugim sposobem jest zmiana zestawu kolorów na karcie Układ strony > Kolory. Niestety w tym miejscu też żaden zestaw nie jest odpowiedni. Ale to właśnie tu jest rozwiązanie naszego problemu.

Wybieramy opcję Dostosuj kolory… i zmieniamy kolory przy elementach Akcent 1 i Akcent 2. Zapisujemy i otrzymujemy wykres zgodny z naszymi oczekiwaniami.

Dodanie wykresu kolumnowego do naszego dashboardu

Budujemy drugą tabele pomocniczą do prezentacji na których produktach najbardziej zmieniły się stany magazynowe. W wiersze wrzucamy nasze produkty, do kolumny pozycje ale zafiltrowane tylko na Stan na początek okresu i Stan na koniec okresu. Obok robimy najpierw wprost odwołania do tabeli a następnie dwie kolumny pomocnicze, które pokażą te same wartości ale tylko dla produktu który wybraliśmy we fragmentatorze. Formułę którą stosujemy w komórce R5 to „=JEŻELI($L5=$B$1;M5;””)”.

Zaznaczamy zakres danych od O5 do S8 i wstawiamy wykres kolumnowy lub inny z podstawowych wykresów, nie jest to kluczowe ponieważ i tak musimy zrobić z niego wykres kombi. Wszystkie serie ustawiamy jako kolumnowy grupowany, natomiast nasze kolumny pomocnicze dodatkowo zaznaczamy jako pokazywane na osi pomocniczej.

Następnie zmieniamy kolory poszczególnych serii np. według tego schematu:

• początkowy stan na jasnoszary
• końcowy stan na ciemnoszary
• początkowy stan pomocniczy na jasnozielony
• końcowy stan pomocniczy na ciemnozielony

Dodatkowo zmieniamy tytuł wykresu, usuwamy linie siatki, legendę oraz najważniejsze USUWAMY WIDOK OSI POMOCNICZEJ. Robimy to po to aby serie danych przypisanych do osi pomocniczej były pokazywane zgodnie ze skalą osi głównej. W przeciwnym razie słupki zielone będą zmieniały rozmiar według słupków szarych co będzie wizualnym przekłamaniem relacji wielkości poszczególnych słupków. Dla szarych słupków dodajemy etykiety danych. W ten sposób uzyskujemy wykres, który pokazuje wartości dla wszystkich produktów, ale dodatkowo wyszczególnia wybrany na fragmentatorze produkt.

Połączenie elementów w mini dashboard

Na koniec dodajemy kształt prostokąta z białym tłem, na którym umieścimy nasze wykresy oraz fragmentator, aby uporządkować i uatrakcyjnić wygląd naszego małego dashboardu. W kolejnej części pokażę jak rozbudować ten dashboard o analizy czasowe i zastosowanie modelu danych w Power Pivot, aby automatycznie liczyć stany początkowe i końcowe dla dowolnego zakresu czasowego.

Plik z przykładem jak wstawić wykres kaskadowy w Excelu

Jeśli dalej masz problemy z tym jak wstawić wykres kaskadowy w Excelu, to możesz pobrać gotowy plik Pobierz mini dashboard.