Jak stworzyć wykres wodospadowy zysków i strat w Excelu?
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.
Bezpośredni odnośnik do filmu na youtube — Wykres wodospadowy waterfall zysków i strat — porada #91
