Czyli uproszczony wykres pudełkowy w Excelu

Ostatnio prowadziłam szkolenie Wizualizacja danych HR w MS Excel. W sumie jedno z pierwszych pytań, które padło brzmiało: „a będziemy robić wykres rozpiętości wynagrodzeń?”. Pomyślałam: No nie, ze wszystkich wykresów, które zaplanowałam, tego jednego nie mam. I akurat o to jest pytanie :). W tym wpisie nadrobię to i pokażę jak zrobić właśnie taki wykres – będzie na nim widoczna rozpiętość wynagrodzeń w działach naszej firmy. Dorzucimy do tego jeszcze medianę, czyli środkowe wynagrodzenie w danym dziale. Wszystko wygląda tak:

I wbrew pozorom w kilku prostych krokach można takie cudo zrobić. Tym się teraz zajmiemy.

Logika wykresu

Wykres jest kombinacją 2 typów wykresów:

1. kolumnowego skumulowanego,
2. liniowego ze znacznikami.

Wykres kolumnowy skumulowany to nasza rozpiętość (różowe kolumny), a liniowy to mediana.

Aby umiejscowić rozpiętość w odpowiednim miejscu, aby wydawała się „pływająca”, trochę oszukamy Excela i ustawimy kolor serii wynagrodzeń minimalnych na przeźroczysty. Kolumna pokazująca rozpiętość będzie to techniczna seria danych, której wartość/wysokość będzie wyliczana na podstawie minimalnego i maksymalnego wynagrodzenia. Obie te kolumny są po prostu ustawione na sobie, stąd potrzebny jest wykres skumulowany.

Mediana to wykres liniowy ze znacznikami, tylko bez widocznej linii.

Całą kuchnię rozwiązania przedstawiam na poniższym rysunku:

Wykres od kuchni

Ok – wprowadźmy to teraz w życie.

Dane do wykresu (formatka)

Aby przedstawić rozpiętość wynagrodzeń w działach (i medianę) – potrzebujemy takich danych:

• minimalne wynagrodzenie
• maksymalne wynagrodzenie
• środkowe wynagrodzenie (medianę)

Zakładam, że mamy takie dane (ich zdobycie to temat na oddzielny artykuł).

Natomiast powyższych danych nie możemy tak po prostu przedstawić na wykresie skumulowanym, ponieważ Excel stworzy kolumnę dla minimalnego wynagrodzenia; później na niej ustawi maksymalne wynagrodzenie. My tak nie chcemy, ponieważ wyszłyby nam zbyt wysokie słupki i do tego źle umiejscowione. Dlatego potrzebujemy technicznej serii danych, która będzie stała na kolumnie minimalnych wynagrodzeń, na której ustawimy kolumnę rozpiętości. Ta druga seria będzie kolumną techniczną, wyliczaną na podstawie wynagrodzeń. Będzie ona po prostu różnicą między wynagrodzeniem maksymalnym i minimalnym.

Nasza formatka będzie więc wyglądała tak:

Formatka

Widać na niej od razu formułę, którą należy wpisać do roboczej kolumny (komórka F6):

=D6-C6

Tworzenie wykresu

Teraz już ta łatwiejsza część – zrobienie wykresu z danych.

1. Zaznaczmy dane, które mają być widoczne na wykresie, czyli kolumny Dział, MIN, Mediana i MAX (robocza). Do zaznaczenia użyj klawisza Ctrl.
2. Menu Wstawianie/Wykres kolumnowy skumulowany.
3. Pojawi się taki wykres, który oczywiście jest tylko pewnym etapem w tworzeniu docelowego wykresu:
   

   Tworzenie wykresu – kolumnowy skumulowany

4. Ustaw wypełnienie serii danych MIN (ciemny różowy) na Brak wypełnienia (Narzędzia wykresów/ Formatowanie/ Style kształtów/ Wypełnienie kształtu/ Brak wypełnienia). Efekt tego wygląda tak:
   

   Brak wypełnienia serii MIN

5. Zmień typ wykresu dla serii danych Mediana na liniowy ze znacznikami (zaznacz na wykresie serię Mediana/ Narzędzia wykresów/ Projektowanie/ Zmień typ wykresu). Efekt widać od razu na podglądzie (w wersjach Excela >= 2013):
   

   Wykres liniowy ze znacznikami

6. Ustaw brak linii dla wykresu liniowego, dzięki czemu będą widoczne same znaczniki (zaznacz wykres liniowy i Ctrl + 1):
   

   Likwidowanie linii z wykresu liniowego

7. W tym samym okienku, zmień znacznik na długą poziomą kreskę, rozmiar np. 10:
   

   Ustawienie znacznika

8. Ustaw tytuł wykresu i legendę według uznania. Jeśli chcesz uzyskać dynamiczny tytuł wykresu, czyli oparty na wybranej komórce – tutaj przeczytasz jak to zrobić.

Wykres na koniec wygląda tak (nie zmieniałam domyślnego koloru):

Wynik

Moim zdaniem cacuszko. Można te dane przedstawić też w poziomie, czyli na wykresie słupkowym, i wyglądałoby to następująco:

Poziomo – wykres słupkowy

Też fajnie. Nawet nie wiem, który mi się bardziej podoba. Oba są czytelne. Można byłoby pomyśleć nad posortowaniem danych? Zależy już, od osoby analizującej :).

 A tutaj plik z rozwiązaniem do pobrania:

MalinowyExcel Wykres rozpiętości wynagrodzeń dw.xlsx

I film na YouToube’ie: