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.