Jak pokazać dynamikę zmian na wykresie kolumnowym?

Wykres kolumnowy jest jednym z najczęściej używanych. Jest popularny, bo możesz go użyć w wielu przypadkach. Często wykorzystujemy go, aby porównać ze sobą jakieś kategorie. Na przykład porównujemy wyniki sprzedaży rok do roku. I na takim wykresie warto pokazać jaka zmiana nastąpiła. Z tego artykułu dowiesz się jak pokazać dynamikę zmian – wartościowo lub procentowo.

Na początek – ściągnij ten plik. Dzięki temu, będziesz mógł czytając artykuł przejść krok po kroku przez kolejne etapy tworzenia wykresu.

Przygotuj dane do wykresu

Jak zawsze przed stworzeniem wykresu, musimy przygotować dane. Idea jest taka, że wykorzystamy właściwości wykresu kolumnowego (grupowanego) Dodamy kolejne kolumny danych do pokazania linii dynamiki, pomiędzy bieżącym i poprzednim rokiem sprzedaży. Żeby było ciekawiej (i trudniej) wzrost ma być obrazowany zieloną, a spadek czerwoną linią.

Wprowadź kolumny techniczne i odpowiednie formuły

Do naszych danych sprzedażowych z lat 2017/2018 wprowadzimy 4 dodatkowe kolumny techniczne:

1. Kolumna „Techniczne +”, gdzie ma się pojawić wyższa z wartości sprzedaży (2018 vs. 2017) ale powiększona o powiedzmy 5%. Przy czym wartość ta ma się pojawić tylko wtedy, gdy sprzedaż w 2018 była wyższa, bądź równa sprzedaży z roku 2017;
2. Kolumna „Techniczne -„, analogicznie jak powyżej ale wartość ma się pojawić, gdy sprzedaż 2018 będzie niższa niż w 2017;
3. W kolumnie „Zmiana % +”, wprowadzimy formułę, która da procentowy wynik przyrostu sprzedaży 2018 vs. 2017;
4. „Zmiana % -„, to z kolei procentowy wynik spadku sprzedaży.

Całość powinna wyglądać tak:

A formuły w kolejnych kolumnach to:

=JEŻELI(D8>=C8;MAX($C8:$D8)*1,05;0)

=JEŻELI(D8<C8;MAX($C8:$D8)*1,05;0)

=JEŻELI(D8>=C8;D8/C8-100%;””)

=JEŻELI(D8<C8;D8/C8-100%;””)

Tworzenie wykresu z dynamiką zmian

Wstaw wykres kolumnowy

Rysujemy podstawowy wykres kolumnowy, który pokaże sprzedaż w latach.

Modyfikujemy wykres:

1. Dodajemy etykiety danych i zmieniamy nazwy serii.
2. Formatujemy liczby na osi pionowej na walutowe z separatorami tysięcy.
3. Zmieniamy kolor serii na wybrany przez nas.
4. Formatujemy obszar wykresu, usuwając wypełnienie i obramowanie.
5. Formatujemy serię danych, tak aby NAKŁADANIE SERII miało wartość 0%.

W efekcie powinniśmy otrzymać efekt podobny do tego poniżej.

Dodaj kolumny techniczne na wykresie

Teraz zasadnicza część. Wykorzystamy wcześniej stworzone kolumny techniczne, aby „narysować” linię zmiany i dodać odpowiednie etykiety. Dodajmy kolejne dwie serie danych z kolumnami technicznymi.

Dostosuj kolumny techniczne

Oczywiście nie taki efekt chcieliśmy osiągnąć! W kolejnych krokach przekształcimy serię z kolumnami technicznymi do pożądanej przez nas formy:

• Zaznacz serię danych, która reprezentuje spadki sprzedaży („Techniczne -„)
• Zmień formatowanie tej kolumny jak niżej.

• Zrób to samo dla serii danych, która reprezentuje wzrosty („Techniczne +”)
• Tym razem zmień formatowanie tak, aby linia była obramowania była zielona.

W efekcie otrzymasz taki wykres:

Jesteśmy prawie w domu 🙂 Jak widzisz, jeżeli uda nam się „schować” kolumny wzrostu/spadku za kolumny z danymi sprzedażowymi – osiągniemy to, czego chcemy. Aby to zrobić, wystarczą już tylko trzy kroki:

1. Kliknij prawym przyciskiem myszy w dowolną serię danych i wybierz ZMIEŃ TYP WYKRESU SERYJNEGO
2. Dla serii 2017/2018 zmień oś na POMOCNICZĄ
3. Wybierz formatowanie dowolnej z serii technicznych i zmień NAKŁADANIE SERII na 100%.

Na koniec usuwamy etykiety osi pomocniczej (po prawej stronie) – zaznacz i DELETE.

Dodaj etykiety danych

Pozostało nam dodać etykiety danych z procentami, które reprezentują wzrost/spadek sprzedaży. Oczywiście dla wzrostu –  chcemy mieć kolor zielony, a dla spadku – czerwony.

1. Zaznacz kolumnę reprezentującą spadki.
2. Dodaj etykiety danych.
3. Przejdź do opcji etykiet danych i wybierz WARTOŚĆ Z KOMÓREK. Pozostałe opcje odznacz.
4. Sformatuj czcionkę etykiety: kolor czerwony, pogrubienie.
5. Analogicznie, zrób dla kolumny reprezentującej wzrosty. Przy czym tu oczywiście sformatujesz czcionkę na zieloną.

Finalny efekt znajdziesz tu.

Podsumowanie

Oczywiście, jeżeli chcesz zamiast % pokazać zmianę w liczbach – nic nie stoi na przeszkodzie. Jedyne co musisz zrobić, to zmienić formuły w kolumnach technicznych „Zmiana %…”

Tego typu technikę możesz wykorzystać za każdym razem, gdy musisz pokazać dynamikę zmian pomiędzy wartościami w czasie. To czy to jest sprzedaż, wielkość produkcji czy zatrudnienia – nie ma oczywiście, żadnego znaczenia. Znasz inne sposoby na pokazywanie dynami zmian? Podziel się z nami koniecznie w komentarzu.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.