fbpx

Mediana w tabeli przestawnej?

07.04.2017 | ECP2, HR, Księgowość, Tabele przestawne, Triki, Wynagrodzenie

Ostatnio jedna z uczestniczek prowadzonego przeze mnie szkolenia Excel w dziale HR zadała mi pytanie, którego jeszcze nikt wcześniej mi nie zadał. Miałam więc bardzo dużą motywację, aby szybko jej odpowiedzieć. 😉 Pytanie brzmiało: Jak obliczyć medianę w tabeli przestawnej? Chodziło konkretnie o ustalenie przeciętnego wynagrodzenia na danym stanowisku w danym regionie firmy w Polsce.

Tabela przestawna oferuje nam wiele funkcji agregujących, taki jak oczywiście suma czy średnia, ale też maksimum czy odchylenie standardowe. Jest nawet wariancja, natomiast nie ma mediany. Szkoda – to by załatwiło sprawę 😉 Pola obliczeniowe też nie na wiele się zdadzą, ponieważ operują na zagregowanych danych, a my chcemy na pojedynczych wynagrodzeniach. Pozostaje więc tylko zabawa z danymi źródłowymi. Tak też zrobiłam.

Czyli z takich danych:

Mediana w tabeli przestawnej - dane źródłowe

Dane źródłowe (fragment)

Chcę takie:

Mediana w tabeli przestawnej - wynik

Wynik

Czyli w danych mam dostępne następujące informacje: lokalizacja, stanowisko i wynagrodzenie. Chcę zbadać medianę wynagrodzeń, czyli przeciętne wynagrodzenie, per lokalizacja i stanowisko. Mediana to wartość środkowa posortowanych rosnąco wartości. Na szczęście tym sortowaniem nie musimy się przejmować, bo istnieje wbudowana funkcja Excela – MEDIANA. A ona potrzebuje od nas tylko zakresu wartości, z których ma liczyć medianę. Sama je sobie sortuje, w celu obliczenia wyniku. Oczywiście sortuje „w pamięci”, czyli nie wpływa fizycznie na kolejność danych w naszym arkuszu.

Ustalanie zakresu mediany – sortowanie

No właśnie – czyli wystarczy jej wskazać odpowiedni zakres do wyliczeń. Hehe, w tym będzie największa trudność 🙂 Aby wszystko się udało, zakres ten powinien zawierać stanowisko danego rodzaju w danej lokalizacji. Czyli coś takiego:

Mediana w tabeli przestawnej - zakresy mediany

Zakresy mediany

Zauważcie, że np. wszyscy Telemarketerzy ze Wschodu są obok siebie. Aby posortować dane – ustaw się w dowolnej komórce zakresu tabeli, użyj kreatora sortowania (Dane/Sortuj) i wybierz takie kryteria:

Mediana w tabeli przestawnej - kryteria sortowania

Kryteria sortowania

No, to teraz wystarczy tylko wyświetlić wartość mediany dla wskazanych zakresów wynagrodzeń dla pierwszego wystąpienia danej kombinacji lokalizacji i stanowiska, a dla pozostałych – zero. Pice of cake 😉 Dlaczego tylko dla pierwszego? Chodzi tutaj o tabelę przestawną, która tradycyjnie zsumuje dane (nie chcę używać w niej podsumowania np. min czy max). Kiedyś już poruszałam podobny problem – dotyczył on liczby osób w danych, w którym osoby się powtarzały. Wpis ten możesz przeczytać tutaj.  Rety, pisałam o tym 4 lata temu 🙂

Kolumna pomocnicza

Aby dokonać powyższego, potrzebujemy kolumny roboczej – połączenia stanowiska z lokalizacją (lub odwrotnie – kolejność nie ma znaczenia). Kolumnę tę dodam na końcu tabeli, i docelowo będzie ona wyglądała tak:

Mediana w tabeli przestawnej - kolumna pomocnicza

Tabela z kolumną pomocniczą

A formułą w E3 jest następująca:

=A3&B3

Formuła licząca medianę

Teraz już wystarczy napisać formułę główną. Medianę jest bardzo łatwo policzyć – należy użyć wbudowanej funkcji Excela: MEDIANA. Problem jest jedynie wskazanie jej odpowiedniego zakresu, z którego tę medianę ma liczyć. Zakres ten na pewno będzie w kolumnie C z wartością wynagrodzenia. Dodatkowo, zacznie się on wtedy, gdy komórka w kolumnie rob, w wierszu, w którym obecnie jest formuła, jest różna od komórki poprzedniej (PRZESUNIĘCIE). Zakres ten będzie miał tyle komórek, ile wystąpień kombinacji lokalizacja-stanowisko. Po to nam właśnie jest ta kolumna robocza (LICZ.JEŻELI).

Formuła jest taka:

=JEŻELI(E3<>E2;MEDIANA(PRZESUNIĘCIE(C3;0;0;LICZ.JEŻELI($E$3:$E$34;E3)));0)

Nawet nie taka straszna, prawda?

Ewentualnie jej wersja korzystająca z narzędzia tabela:

=JEŻELI(E3<>E2;MEDIANA(PRZESUNIĘCIE(C3;0;0;LICZ.JEŻELI([rob];E3)));0)

Wynik dostajemy taki:

Mediana w tabeli przestawnej - wynik formuły

Wynik formuły

Bez kolumny pomocniczej

Można to zrobić również bez kolumny pomocniczej – jak wolicie. Formuła jednak będzie nieco dłuższa i wykorzystująca LICZ.WARUNKI  zamiast LICZ.JEŻELI:

=JEŻELI(A3&B3<>A2&B2;MEDIANA(PRZESUNIĘCIE(C3;0;0;LICZ.WARUNKI($A$3:$A$34;A3;$B$3:$B$34;B3)));0)

Tabela przestawna

Teraz już tylko wystarczy zrobić z tego tabelkę przestawną (Wstaw/Tabela przestawna), i ustawić ją w preferowany sposób. Ja lubię tak (lista pól):

Mediana w tabeli przestawnej - układ tabeli przestawnej

Układ tabeli przestawnej

A wynik otrzymujemy taki:

Mediana w tabeli przestawnej - wynik

Wynik

I to tyle. Mam nadzieję, że wam się przyda. Pamiętajcie, że często mediana jest lepszą miarą niż popularna średnia. Niestety – często niedocenianą…

Dajcie znać w komentarzach jak może wam się przydać opisana wyżej metoda. Może niekoniecznie do mediany? Może do czegoś innego?

I wersja wideo:

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

2 komentarze

  1. Wartości w tabeli przestawnej dla poszczególnych kategorii wyliczają się poprawnie, ale pole, które zwykle nazywa się „Suma końcowa” w tabeli przestawnej wylicza się błędnie, tzn. jest to suma median z poszczególnych kategorii, zamiast jak by się oczekiwało mediana z całego zbioru. Ta uwaga powinna zostać dodana w opisie.

    Reply
    • Kamila, tak, tak to działa w tym rozwiązaniu.

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest