Jak stworzyć wykres wodospadowy zysków i strat w Excelu?


Wykres wodospadowy waterfall zysków i strat — porada #91

Wykres wodospadowy waterfall zysków i strat - porada #91

W tym wideo zajmiemy się stworzeniem wykresu wodospadowego (waterfall) zysków i strat. Na przykładzie oszczędności rodziny. Początkowe dane zawierają kwotę, od której zaczynamy oraz bilans poszczególnych miesięcy — kiedy wydaliśmy więcej niż przewidywał budżet, a kiedy zaoszczędziliśmy.

Jednak ten zbiór danych nie pozwoli stworzyć wykresu wodospadowego. Potrzebujemy stworzyć 4 kolumny, żeby uzyskać pełny zakres danych:

      1) to kolumna Krawędzi, która będzie zawierała wartości brzegowe, czyli kwotę oszczędności z jaką zaczęliśmy (3000zł) oraz kwotę końcową, którą musimy obliczyć.
      2) to kolumna W górę, w której będziemy szukać pozytywnych bilansów, czyli miesięcy, w których zaoszczędziliśmy. Uzyskamy ją dzięki funkcji:

      =MAX(0;F4)

      3) to kolumna W dół, w której będziemy szukać negatywnych bilansów, czyli miesięcy kiedy wydaliśmy więcej niż mieliśmy (przewidywał budżet). Tak naprawdę potrzebujemy odwrotności tej wartości ze znakiem plus, więc przed funkcję postawimy znak minus:

      =-MIN(0;F4)

      4) to kolumna Podstawy. Czyli wartości na której będą opierać się wartości W górę i W dół, by symbolizować, że od tej wartości wartość wzrosła lub do tej wartości spadła.
      Na jej obliczenie składa się pierwsza wartość krawędzi, suma z wcześniejszej podstawy oraz wartości W górę oraz odjęcie wartości W dół.

      =SUMA(B3;D3:E3)-C4

      Przeciągamy tą formułę o 1 niżej niż Grudzień (koniec naszego bilansu), by uzyskać końcową wartość brzegową/krawędzi (musimy ją przeciągnąć do odpowiedniej kolumny.

Teraz trzeba zaznaczyć dane — wszystkie 4 kolumny + miesiące oraz wartości krawędzi przed i za miesiącami. Czyli wartości oszczędności z jakimi rodzina zaczęła rok i z jakimi oszczędnościami skończyła rok.

Wstawiamy wykres kolumnowy skumulowany. Usuwamy zbędne dane np: Legendę, a następnie zaznaczamy serię podstawy by ustawić jej Wypełnienie na Brak wypełnienia, i kolor linii na Brak linii.

Teraz możemy dopasować kolory pozostałych serii malując je np: narzędziem Kolor wypełnienia z karty Narzędzia główne, co przy pełnym kolorze jest szybsze od wchodzenia w okno formatowania serii i opcje wypełnienia serii. 

Zostało jeszcze dopracowanie nazw osi poziomej (kategorii). Bo przy naszym zaznaczeniu Excel pomyślał, że nazwy miesiąca to Seria, dlatego trzeba je usunąć z Serii a dołożyć do Kategorii. Pamiętaj zaznaczyć kategorię o 1 komórkę przed i po nazwach miesiąca, żeby ilość punktów kategorii była równa ilościom punktów w seriach.

Właśnie stworzyliśmy funkcjonalny i ładnie się prezentujący wykres wodospadowy.

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Wykres wodospadowy waterfall zysków i strat — porada #91