Narzędzia BI, takie jak np. Tableau, pozwalają na zaawansowaną prezentację danych przy pomocy dosłownie kilku kliknięć. Wykorzystując możliwości Excela mogę zaprezentować dane w równie atrakcyjny sposób, szczególnie mając nowe wykresy w wersji Excel 2016. Tym razem przestawię jednak niestandardowy sposób pokazania dwóch zmiennych na wykresie – jedną w postaci długości słupka danych, drugą w postaci intensywności jego koloru.
Dane do przygotowania wykresu Excel
Przed rozpoczęciem pracy polecam wpis o projektowaniu wykresu krok po kroku.
Do przygotowania wykresu wykorzystam dane sprzedażowe dotyczące mebli, urządzeń i artykułów biurowych Arkusza „dane” . Wykorzystam jedynie kolumny P (Kategoria), Q (Podkategoria), S (Sprzedaż), X (Zysk). Kolumny P oraz Q kopiuję do nowego arkusza i nazywamy go „wykres”. Przygotowuję tu dane do wykresu oraz sam wykres.
Przy zaznaczeniu nowo przeklejonych kolumn (A i B) w arkuszu „wykres” wybieram Dane>Usuń duplikaty > Moje dane mają nagłówki > OK.
Zaznaczając komórki A2:B18 sortuję dane według kategorii produktu od Z do A : Narzędzia Główne> Sortuj i Filtruj > Sortowanie niestandardowe.
W ten sposób tworzę listę etykiet kategorii do wykresu.
Następnie w kolumnie C i D w kolejności umieszczam wartość sprzedaży oraz zysku dla poszczególnych podkategorii produktu używając formuły SUMA.WARUNKÓW i kopiuję do komórek poniżej.
Budowa wykresu słupkowego w programie Excel
Sprzedaż pokażę jako długość kolumny danych, zaś zysk jako jej kolor. Poniżej przygotowuję dane, które będą źródłem danych do wykresu. Tworzę przedziały dla wartości zysku (B21:H22) oraz nazwy kolejnych serii danych (B23:H23). W komórki B24:H40 wpisuję formułę sprawdzającą czy wartość zysku dla danej podkategorii produktu mieści się w danym przedziale (C21:I22), jeżeli formuła jest prawdziwa zwracana jest wartość sprzedaży dla podkategorii, jeżeli fałszywa zwracany jest błąd.
Gdy cały obszar jest wypełniony formułami przechodzę do tworzenia wykresu. Zaznaczam cały zakres danych A23:H40 Wstawianie > Wykresy – wyświetl wszystkie wykresy (mała strzałka u dołu) > Wszystkie wykresy > Kombi > Niestandardowy złożony > Skumulowany słupkowy (dla każdej z serii) > Odznaczam Oś pomocniczą.
Następnie formatuję wykres (tytuł, oś poziomą, legendę itd), może się tu przydać wiedza o tym jak poprawić czytelność mojego wykresu oraz dobrać w odpowiedni sposób kolory.
Plik do pobrania
Pobierz szablon wykresu Excel z drugą zmienną w postaci koloru i wstaw swoje dane.
-1024x269.png)
Ciekawy sposób przedstawiania danych Jednak dla mnie mocno mylący. Znacznie lepiej się czuje gdy wartości ujemne są po drugiej stronie osi liczbowej. Wtedy plusy zaznaczam na zielono a minusy na czerwono. Jeden rzut oka wystarcza by zobaczyć znak i wartość oraz proporcje.
Do tego typu wizualizacji trzeba się z pewnością przyzwyczaić, niemniej przykład pokazuje, że mamy w Excelu możliwość przedstawienia zmiennej na dowolnym wykresie (nie tylko słupkowym) jako kolor.