Wykres bridge\waterfall

Wykres bridge\waterfall

Pierwszym wpisem na moim blogu chciałbym przybliżyć sposób wykonania wykresu typu bridge\waterfall. Wykres ten sprawdza się znakomicie gdy chcemy przedstawić zmienność np. w wyniku firmy pomiędzy miesiącami. Dzięki niemu w obrazowy sposób możemy pokazać wpływ poszczególnych składowych na sumaryczną wartość pomiędzy porównywanymi okresami. 

Przygotowanie danych

Jako przykład przedstawię wyniki firmy sprzedażowej X, której konsultanci pracują na teranie całej Polski a ich wyniki w ciągu ostatnich dwóch miesięcy przedstawiały się następująco:

W pierwszym etapie musimy odpowiednio przygotować dane do wykresu dodając kolumnę z informacją jak wartość sprzedaży za październik zmieniła się w porównaniu do wartości z poprzedniego okresu. Posłuży ona do identyfikacji wzrostów i spadków sprzedaży dla każdego z regionów pomiędzy miesiącami.

Kolejnym krokiem jest dodatnie kolumny technicznej, która będzie potrzebna przy określeniu wielkości słupków bazowych na wykresie. W pierwszej kolejności w komórce H2 tworzymy powiązanie z komórką E8, gdzie znajduje się podsumowanie wyników sprzedaży za wrzesień.

Następnie w komórce H3 wpisujmy funkcję która do sumy sprzedaży września doda wartość z kolumny określającej wielkość zmiany pomiędzy miesiącami dla każdego z regionów (obliczyliśmy ją w poprzednim punkcie). Tak przygotowaną formułę przeciągamy dla wszystkich komórek.

Teraz pozostaje nam stworzenie dwóch kolumn (posłużą one do zbudowania wykresu),w których określimy wysokość słupków zmian (wzrostów\spadków) pomiędzy okresami oraz słupków stanowiących dla nich podstawę.

Kolejna kolumna będzie definiowała wielkość słupka określające zmianę w porównaniu do poprzedniego okresu. Wykorzystamy do tego funkcję ABS zwracający wartość bezwzględną z liczby. W pierwszej kolejności komórkę I2 linkujemy do komórki H2 gdzie znajduje się suma sprzedaży za wrzesień.

W kolejnej komórce (I3) wpisujemy formułę ABS, która obliczy wartość bezwzględną ze zmiany pomiędzy wartością z miesiąca aktualnego i poprzedniego:

Następny etap to połączenie komórki I8 z komorką H8 w której znajduje się wartość sprzedaży za październik.

W ostatniej kolumnie znajdą się wartości określającej wielkość słupków bazowych. Zostaną one skalkulowane na podstawie formuły = IF(G3<0;H3;H2).

Budowa wykresu

Ostatni etap to stworzenie oraz odpowiednie sformatowanie samego wykresu. Rozpoczynamy od wybrania typu wykresu - kolumnowy skumulowany.

Po pojawieniu się obszaru wykresu klikamy na niego prawym przyciskiem myszki oraz z menu kontekstowego wybieramy  menu w którym określimy zakres danych wejściowych - 'Select Data'.

Dodajemy pierwszą serię słupków określających zmianę poziomu sprzedaży dla każdego z terenów pomiędzy miesiącami. W tym celu wybieramy zakres w którym kalkulowaliśmy wartości absolutne oraz zatwierdzamy.

Po wykonaniu tej operacji powstaje nam wykres jak poniżej.

W kolejnym kroku dodajemy słupki bazowe, które będą stanowiły poziom odniesienia dla wartości z poprzedniego kroku. Dodajemy kolejną serię danych i zaznaczamy zakres jak na screenie poniżej (z pustymi wartościami dla sumy września i października).

W ten sposób stworzyliśmy wykres zmian.

Dalej przechodzimy do odpowiedniego sformatowanie wykresu. W pierwszym kroku zmieniamy kolejność serii tak aby seria wartości określająca wielkość słupków zmian była na pierwszej pozycji, co realizujemy za pomocą odpowiedniej strzałki w menu kontekstowym.

Po wprowadzeniu tej zmiany wykres powinien wyglądać jak poniżej.

Następnie tworzymy odpowiednie opisy do poziomej osi głównej.

Aby dodać ją do wykresu wybieramy przycisk 'Edit' w sekcji etykiet osi poziomej.

Zaznaczamy zakres z danymi i zatwierdzamy.

Po wykonaniu tych kroków nasz wykres powinien wyglądać jak poniżej.

Teraz zajmiemy się formatowaniem samych słupków. W pierwszym korku wyłączymy pokazywanie słupków bazowych poprzez kliknięcie prawym przyciskiem myszy serii danych słupków bazowych (pomarańczowych) i wybranie z menu kontekstowego 'Format Data Series'.

W menu, które się pojawi przechodzimy na pierwszą zakładkę w której definiujemy wypełnienie i wybieramy 'No fill' (brak wypełnienia).

Dzięki wybraniu tych opcji pozbyliśmy się naszych pomarańczowych słupków bazowych.

Następnie przechodzimy na zakładkę w której zmieniamy ustawienia dotyczące wyświetlania serii danych, gdzie zmieniamy ustawienia dla osi podstawowej ustawiając wartość parametru 'Series Overlap' na 100% oraz 'Gap Width' na 0%.

Dzięki temu nasz wykres wygląda tak.

Dalej przechodzimy do pokolorowania słupków z wielkością zmiany dla poszczególnych regionów pomiędzy miesiącami tak, aby już po pierwszym spojrzeniu na wykres było widoczne gdzie zyskaliśmy, a gdzie straciliśmy sprzedaż w badanym okresie. Wzrosty zaznaczamy zielonym kolorem, natomiast spadki kolorem czerwonym. Wykorzystamy do tego drugi sposób na zmianę koloru serii wykresu.

W tym celu zaznaczmy słupek dla Regionu Północnego, klikając pierwszy raz na ten słupek zaznaczy nam się cała seria danych, dopiero ponowne kliknięcie wybierze nam pojedynczy słupek (punkt 1 na screenie poniżej).

 Spowoduje to pojawienie się nowego menu 'Chart Tools' (punkt 2), z którego wybieramy zakładkę 'Format' (punkt 3) następnie ikonę wypełnienia 'Shape Fill' (punkt 4) oraz finalnie kolor wypełnienia (w naszym przypadku 'Region Północny' zwiększył sprzedaż miesiąc do miesiąca więc wybieramy kolor zielony). Podobną procedurę powtarzamy do wszystkich słupków, aż do uzyskania poniższego efektu.

Możemy zatytułować nasz wykres klikając na pole 'Chart Title' i wpisując odpowiednią nazwę. Aby dodatkowo podkreślić wielkość zmian i lepiej zobrazować zmienność okres do okresu, dobrym pomysłem jest zmiana skali osi pionowej tak aby nie zaczynała się od zera, a np. od wartości 1000k PLN.

W tym celu klikamy prawym przyciskiem myszy na główną oś pionową oraz wybieramy 'Format Axis'.

Oraz w menu które się pojawi na zakładce 'Axis Options' wpisujemy wartość minimum na 1000.

Dzięki temu wykres będzie wyglądał tak.

Warto również pokazać liczbowo wielkość spadków oraz wzrostów. W tym celu klikamy prawym przyciskiem myszy na serię danych, a następnie wybieramy 'Add Data Labels' -> 'Add Data Labels'.

Ostatnim krokiem będzie dodanie opisu osi pionowej. Tą czynność oraz inne modyfikacja jak etykiety wartości, pole legendy, tabele danych wykresu możemy dodać z menu 'Chart Tools' -> 'Design' -> 'Add Chart Element'. W naszym przykładzie wybieramy z menu  'Axis Titles' -> 'Primary Vertical'.

Wykres który udało nam się stworzyć prezentuje się tak:

Wykres tego typu sprawdza się doskonale np. przy porównaniu wyników przedsiębiorstwa pomiędzy okresami, gdzie możemy bardzo obrazowo przedstawić które składowe i w jakim stopniu wpłynęły na sumaryczny rezultat (np. czy spadek w zysku firmy to wynik rosnących kosztów czy może spadających przychodów itp.). Mam nadzieję, że dzięki temu opisowi stworzenie takiego wykresu nie będzie stanowiło dla nikogo żadnego problemu.