Podczas prowadzenia sprzedaży dużej liczby produktów, dających się pogrupować w pewne kategorie, można zadać sobie pytanie: na jaki typ produktów wzrasta (lub maleje) popyt?  Pomóc w odpowiedzi może wykres słupkowy przedstawiający, ile procent produktów w danej kategorii zanotowało wzrost lub spadek sprzedaży.

Zdobycie takich informacji pozwoli nam zrozumieć zmieniające się tendencje na rynku oraz zjawisko sezonowości sprzedaży niektórych produktów.

Jak czytać powyższy wykres?

Czerwony kolor to procent produktów z danej kategorii których sprzedaż osłabła, natomiast kolor zielony – procent produktów, których sprzedaż wzrosła. Razem zawsze będą tworzyć sumę 100 %, dlatego też wszystkie słupki mają tę samą długość. Ciemniejszym kolorem zaznaczono produkty, których sprzedaż wzrosła lub spadła o więcej niż 10 %.

Jak łatwo zauważyć, wykres taki nie przedstawia konkretnych wartości, zamiast tego dając ogólne pojęcie o sytuacji na rynku.

Jak stworzyć taki wykres?

Krok 1 – przygotuj i opracuj dane.

Przygotuj dane – powinno to być zestawienie sprzedaży produktów lub usług, które będzie można podzielić w kategorie. Musi ono obejmować dwa okresy sprzedaży – np. dwa miesiące bądź też kwartały. Przykładowe dane znajdziesz w pliku Excela pod tekstem.

Uporządkuj dane w 4 kolumny – nazwę produktu, kategorię do jakiej należy, wartość sprzedanych rzeczy w zeszłym okresie rozliczeniowym oraz w obecnym okresie. Na podstawie 2 ostatnich kolumn oblicza się procent wzrostu lub spadku przy pomocy formuły

= obecny miesiąc / zeszły miesiąc – 1

Należy zmienić format liczby z ogólnego na procentowy.

W ostatecznym rozrachunku dane powinny wyglądać podobnie jak na obrazku poniżej:

Następnie sporządź listę wszystkich kategorii produktów. Używając formuły LICZ.WARUNKI policz liczbę produktów których sprzedaż wzrosła lub spadła o określoną liczbę procent.

Jak działa ta formuła? Zlicza ilość komórek znajdujących się w zakresie funkcji, spełniających dane kryterium. Przy tym można wyznaczyć więcej niż jeden zakres oraz jedno kryterium, a zliczane komórki muszą spełniać wszystkie kryteria. Tym się różni od funkcji LICZ.JEŻELI, gdzie można wprowadzić tylko jedno kryterium.

Używając słownictwa logiki matematycznej, pomiędzy poszczególnymi kryteriami musi występować koniunkcja. Słowny opis tej funkcji jest zagmatwany, aczkolwiek każdy użytkownik Excela w mig zrozumie jej istotę gdy tylko spróbuje jej użyć po raz pierwszy.

Przykładowo: Aby policzyć liczbę produktów z kategorii TURYSTYKA których sprzedaż spadła o więcej niż 10%, trzeba napisać:

=LICZ.WARUNKI(Tabela1[Kategoria produktu];”TURYSTYKA”;Tabela1[Zmiana w %];”<-10%”)

Przy pomocy powyższej funkcji dzielimy przedmioty z jednej kategorii na 4 grupy:

1. Produkty których sprzedaż spadła o więcej niż 10 %;
2. Produkty których sprzedaż spadła, ale nie więcej niż o 10 %
3. Produkty których sprzedaż wzrosła, ale nie więcej niż o 10 %
4. Produkty których sprzedaż wzrosła o więcej niż 10 %

Formuła nie musi wyglądać dokładnie tak samo jak było to przytoczone; wpisywanie warunków można zastąpić adresem komórki, w której znajduje się odpowiedni zapis:

Na wynik funkcji LICZ.WARUNKI nie ma też znaczenia kolejność poszczególnych kryteriów.

Następnie obliczamy, jaką część (w procentach) całości stanowią poszczególne grupy; w tym celu dzielimy liczebność poszczególnych grup przez liczbę wszystkich produktów w danej kategorii. Przykładowo dla grupy produktów zaliczający spadek większy niż 10 % formuła będzie wyglądać:

=-liczebność grupy „<-10%”/Suma

Co robi tutaj znak minusa?

Chcemy, aby część słupka obrazująca spadki, była po lewej stronie osi pionowej, więc wynik musi być ujemny. Dla części wykresu przedstawiającego liczbę produktów, których sprzedaż wzrosła, znak ten pomijamy.

Obliczamy w ten sposób dane dla wszystkich kategorii.

Krok 2 – stwórz wykres

Zaznaczamy komórki opisujące udział procentowy poszczególnych grup w kategoriach > klikamy Wstawianie > Wykres słupkowy > Skumulowany słupkowy:

Wynik, jaki ujrzycie na ekranie, powinien być podobny do poniższego:

Łatwo zapewne zauważycie, że nasze słupki nie są w dobrej kolejności. Aby to naprawić, należy kliknąć prawym klawiszem myszy na słupki > Zaznacz dane > przy pomocy strzałek w górę i w dół ustawić serie w poprawnym porządku.

Krok 3. Popracuj nad wyglądem wykresu

Należy zadbać, aby wykres był czytelny – przy jego formatowaniu warto pamiętać o  siedmiu zasadach skutecznego wykresu. Niepotrzebne linie siatki trzeba usunąć, a słupkom nadać odpowiedni kolor – spadki sprzedaży na kolor czerwony, wzrost – oczywiście na zielony. Słupki oznaczające wzrost/spadek powyżej 10 % oznaczamy kolorami silnie nasyconymi, w przeciwieństwie do tych oznaczających wahania sprzedaży w granicach 0 – 10 % – te oznaczamy barwami bardziej stonowanymi.

Legendę możemy umieścić w dowolnym miejscu – jej obecność jest obowiązkowa, bowiem bez niej odczytanie i interpretacja wykresu będzie bardzo trudne.

Teraz można popracować nad osią pionową – należy nadać jej odpowiednie etykiety (te liczby przecież nic nam nie mówią) oraz usunąć znaczniki. Nazwy kategorii wstawiamy, klikając prawym klawiszem na słupki i wybierając Zaznacz dane. W pojawiającym się oknie dialogowym nad spisem Etykiet osi poziomej wybieramy przycisk Edytuj, po czym wybieramy uprzednio stworzoną przez nas listę kategorii.

Możliwe, że będzie istniała konieczność rozciągnięcia wykresu w pionie, aby zmieściły się wszystkie etykiety.

Aby zmienić ich położenie oraz usunąć znaczniki, włączamy formatowanie poprzez kliknięcie w rozwijającym się menu kontekstowym na opcję Formatuj oś. Ewentualnie: zaznaczamy oś i używamy skrótu klawiszowego CTRL + 1.

W zakładce Opcje osi ustawiamy wartość opcji Typ głównego znacznika na Brak, a opcję Etykiety osi na wartość Nisko.

Ponadto możemy zaznaczyć opcję Kategorie w kolejności odwrotnej. Spowoduje to przeniesienie osi poziomej na górę wykresu oraz zmianę kolejności kategorii – teraz pierwsza etykieta będzie na górze.

 Efekt naszych działań powinien być podobny do poniższego:

Aby poprawić wartość współczynnika Dane/Atrament, można pogrubić szerokość słupków, jednocześnie zmniejszając prześwity pomiędzy nimi. Dokonujemy tego w oknie Formatowanie serii danych (zaznaczamy słupki wykresu, naciskamy CTRL + 1) > ustawiając szerokość przerwy na mniejszą, rozsądną wartość – np. 30 %

 Patrząc na oś poziomą, dostrzeżemy jej niesymetryczność – jej zakres zaczyna się od -80% i biegnie aż do 100 %, pomimo iż żaden słupek nie przekracza 80%, w związku z czym jej długość nie ma żadnego racjonalnego uzasadnienia. Zakres osi zmieniamy, zaznaczając ją, włączając formatowanie osi (ponownie możemy wykorzystać skrót klawiszowy CTRL + 1) i zmienienie wartości Maksimum poprzez zaznaczenie opcji Stała, a następnie określenie jej jako 0,8.

Teraz nie pozostało nic innego, jak tylko dodać tytuł oraz kilka kosmetycznych poprawek – np. zmiana koloru tekstu na szary, aby nie odciągał wzroku od najistotniejszych części wykresu oraz po prostu rozciągnięcie go do odpowiedniego kształtu. Voilà – nasz wykres jest skończony.

Co sądzicie o takim wykresie? Czy uważacie, że sprawdzi się w praktyce? A może coś byście w nim zmienili?

Link do Excela z przykładowymi danymi: Wykres słupkowy konfrontujący wzrosty i spadki