Jak zbudować wykres kaskadowy

Coraz częściej spotykam się w reportażach różnych firm i słyszę prośby od stażystów o wyjaśnienie, jak budowany jest kaskadowy diagram odchyleń – to też „wodospad”, to też „wodospad”, to też „most ”, to także „most” itp. . Wygląda to mniej więcej tak:

Z daleka wygląda jak kaskada wodospadów na górskiej rzece lub wiszący most – kto widzi co 🙂

Osobliwością takiego diagramu jest to, że:

• Wyraźnie widzimy początkową i końcową wartość parametru (pierwsza i ostatnia kolumna).
• Pozytywne zmiany (wzrost) są wyświetlane w jednym kolorze (zwykle Zielony) i negatywne (odrzuć) innym (zwykle czerwony).
  
• Czasami wykres może również zawierać kolumny sum częściowych (szarywylądował na kolumnach osi X).
  

W życiu codziennym takie diagramy są zwykle używane w następujących przypadkach:

• Wizualny wyświetlacz dynamiki dowolny proces w czasie: cash flow (cash-flow), inwestycje (inwestujemy w projekt i czerpiemy z niego zysk).
• Wizualizacja realizacja planu (skrajna lewa kolumna na diagramie to fakt, skrajna prawa kolumna to plan, cały diagram odzwierciedla nasz proces dochodzenia do pożądanego rezultatu)
• Kiedy potrzebujesz wizualizacji pokaż czynnikiktóre wpływają na nasz parametr (analiza czynnikowa zysku – z czego się składa).
  

Istnieje kilka sposobów na zbudowanie takiego wykresu – wszystko zależy od Twojej wersji programu Microsoft Excel.

Metoda 1: Najłatwiejsza: Wbudowany typ w Excel 2016 i nowszych

Jeśli masz Excel 2016, 2019 lub nowszy (lub Office 365), to zbudowanie takiego wykresu nie jest trudne – te wersje Excela mają już ten typ wbudowany domyślnie. Wystarczy wybrać tabelę z danymi i wybrać na karcie wstawka (Wstawić) Command kaskadowy (Wodospad):

W rezultacie otrzymamy prawie gotowy schemat:

Możesz natychmiast ustawić żądane kolory wypełnienia dla kolumn dodatnich i ujemnych. Najłatwiej to zrobić, wybierając odpowiednie wiersze Zwiększać и spadek bezpośrednio w legendzie i klikając je prawym przyciskiem myszy, wybierz polecenie Wypełniać (Wypełnić):

Jeśli chcesz dodać kolumny z sumami częściowymi lub końcową sumą kolumn do wykresu, najwygodniej jest to zrobić za pomocą funkcji SUMY CZĘŚCIOWE (SUMA CZĘŚCIOWA) or UNIT (AGREGAT). Obliczą kwotę skumulowaną od początku tabeli, wyłączając z niej podobne sumy znajdujące się powyżej:

W tym przypadku pierwszy argument (9) jest kodem operacji sumowania matematycznego, a drugi (0) powoduje, że funkcja ignoruje w wynikach już obliczone sumy za poprzednie kwartały.

Po dodaniu wierszy z sumami pozostaje wybrać kolumny sum, które pojawiły się na diagramie (wykonać dwa kolejne pojedyncze kliknięcia na kolumnie) i klikając prawym przyciskiem myszy wybrać polecenie Ustaw jako sumę (Ustaw jako sumę):

Wybrana kolumna wyląduje na osi X i automatycznie zmieni kolor na szary.

To właściwie wszystko – diagram kaskadowy jest gotowy:

Metoda 2. Uniwersalna: niewidoczne kolumny

Jeśli masz Excel 2013 lub starsze wersje (2010, 2007 itd.), to opisana powyżej metoda nie zadziała. Będziesz musiał obejść się i wyciąć brakujący wykres kaskadowy ze zwykłego histogramu skumulowanego (sumując słupki jeden na drugim).

Sztuczka polega na tym, aby użyć przezroczystych kolumn rekwizytów, aby podnieść nasze czerwone i zielone wiersze danych do prawidłowej wysokości:

Aby zbudować taki wykres, musimy dodać do danych źródłowych kilka dodatkowych kolumn pomocniczych z formułami:

• Najpierw musimy podzielić naszą oryginalną kolumnę, oddzielając wartości dodatnie i ujemne na osobne kolumny za pomocą funkcji IF (JEŚLI).  
• Po drugie, musisz dodać kolumnę przed kolumnami smoczki, gdzie pierwsza wartość będzie wynosić 0, a począwszy od drugiej komórki, formuła obliczy wysokość tych bardzo przezroczystych kolumn wspierających.
  

Następnie pozostaje wybrać całą tabelę z wyjątkiem oryginalnej kolumny Przepływ i utwórz regularny histogram skumulowany w poprzek Wstawka — histogram (Wstaw — wykres kolumnowy):

Jeśli teraz wybierzesz niebieskie kolumny i uczynisz je niewidocznymi (kliknij na nie prawym przyciskiem myszy – Format wiersza – wypełnienie – brak wypełnienia), wtedy po prostu dostajemy to, czego potrzebujemy. 

Zaletą tej metody jest prostota. W minusach – konieczność liczenia kolumn pomocniczych.

Metoda 3. Jeśli pójdziemy na czerwień, wszystko będzie trudniejsze

Niestety poprzednia metoda działa adekwatnie tylko dla wartości dodatnich. Jeśli chociaż w jakimś obszarze nasz wodospad przejdzie w obszar ujemny, to znacznie wzrośnie złożoność zadania. W takim przypadku konieczne będzie obliczenie każdego wiersza (atrapa, zielonego i czerwonego) osobno dla części ujemnej i dodatniej za pomocą wzorów:

Aby nie cierpieć wiele i nie wymyślać koła na nowo, gotowy szablon na taki przypadek można pobrać w tytule tego artykułu.

Metoda 4. Egzotyczne: pasma góra-dół

Metoda ta opiera się na wykorzystaniu specjalnego mało znanego elementu płaskich wykresów (histogramów i wykresów) – Pasma góra-dół (paski góra-dół). Te pasma łączą punkty dwóch wykresów parami, aby wyraźnie pokazać, który z dwóch punktów jest wyższy lub niższy, co jest aktywnie wykorzystywane podczas wizualizacji planu-faktu:

Łatwo się domyślić, że jeśli usuniemy linie z wykresów i zostawimy na wykresie tylko wstęgi góra-dół, to otrzymamy ten sam „wodospad”.

Dla takiej konstrukcji musimy dodać dwie dodatkowe kolumny do naszej tabeli z prostymi formułami, które obliczą położenie dwóch wymaganych niewidocznych wykresów:

Aby utworzyć „wodospad”, musisz wybrać kolumnę z miesiącami (dla podpisów wzdłuż osi X) i dwie dodatkowe kolumny Zaplanuj 1 и Zaplanuj 2 i zbuduj regularny wykres na początek, używając Wstaw – wykres (Wstaw — Wykres linii):

Teraz dodajmy do naszego wykresu wstęgi góra-dół:

• W programie Excel 2013 i nowszych należy to zaznaczyć na karcie Konstruktor Command Dodaj element wykresu — Pasma wzrostu-spadku (Projekt — Dodaj element wykresu — Paski góra-dół)
• W Excel 2007-2010 – przejdź do zakładki Układ — słupki Advance-Decrement (Układ — paski góra-dół)

Wykres będzie wtedy wyglądał mniej więcej tak:

Pozostaje wybrać wykresy i uczynić je przezroczystymi, klikając je kolejno prawym przyciskiem myszy i wybierając polecenie Format serii danych (Seria formatu). Podobnie możesz zmienić standardowe, raczej nędznie wyglądające czarno-białe kolory pasków na zielono-czerwone, aby na końcu uzyskać ładniejszy obraz:

W najnowszych wersjach Microsoft Excel szerokość słupków można zmienić klikając na jeden z przezroczystych wykresów (nie słupki!) prawym przyciskiem myszy i wybierając polecenie Format serii danych – prześwit boczny (Seria formatu — Szerokość odstępu).

W starszych wersjach programu Excel trzeba było użyć polecenia Visual Basic, aby to naprawić:

1. Zaznacz zbudowany schemat
2. Naciśnij skrót klawiaturowy inny+F11dostać się do Edytora Visual Basic
3. Naciśnij skrót klawiaturowy Ctrl+Gaby otworzyć bezpośrednie wprowadzanie poleceń i panel debugowania Natychmiastowy (zwykle znajduje się na dole).

4. Skopiuj i wklej tam następujące polecenie: ActiveChart.ChartGroups(1).GapWidth = 30 i naciśnij Wchodzę:

Możesz oczywiście pobawić się wartością parametru, jeśli chcesz. Szerokość odstępuaby osiągnąć pożądany prześwit:

• Jak zbudować wykres pociskowy w programie Excel, aby zwizualizować KPI  
• Co nowego w wykresach w programie Excel 2013
• Jak stworzyć interaktywny wykres „na żywo” w Excelu