Minuty czytania: 4

Mediana w tabeli przestawnej na 3 sposoby

Tabela przestawna to jedno z najcenniejszych narzędzi Excela. Jednak, jak prawie wszystko podlega pewnym ograniczeniom. Jednym z nich jest możliwość prostego policzenia niektórych miar statystycznych, takich jak: mediana (wartość środkowa) czy dominanta (wartość występująca najczęściej).

Mediana w tabeli przestawnej to było jedno z wyzwań jakie miałem analizując zawodowo wynagrodzenia. Jak wiadomo, zazwyczaj mediana jest dużo lepszym miernikiem do analizy wynagrodzeń, niż średnia. Stąd możliwość jej wykorzystania w tak elastycznym narzędziu jak tabela przestawna, było dla mnie więcej niż pożądane.

W tym artykule pokażę Ci 3 sposoby, na połączenie elastyczności tabeli przestawnej z medianą.

Mediana w tabeli przestawnej, a w zasadzie obok tabeli

Oczywiście jak zawsze, zachęcam Cię do jednoczesnych ćwiczeń, wraz z lekturą artykułu. Pobierz plik i działaj ze mną. Gotowe rozwiązania otrzymują subskrybenci naszego newslettera.

Na początku wykonajmy standardowe kroki, aby stworzyć tabelę przestawną. Następnie ułożymy dane, tak aby analizować wynagrodzenia z perspektywy działów i kategorii zaszeregowania (grade’ów). Gdybyśmy, chcieli wyciągnąć średnie wynagrodzenie dla takiego układu, to nasza tabela mogłaby wyglądać tak:

Średnia w tabeli przestawnej

Oczywiście nam zależy na medianie. Najprostszym ale mało eleganckim i mało elastycznym sposobem, będzie wyliczenie mediany w kolumnie obok pivota. A zatem, troszeczkę tutaj oszukujemy, bo mediana nie jest częścią tabeli przestawnej, a jedynie „korzysta” z danych w niej zawartych. Zmieniając dane w tabeli, automatycznie wylicza się mediana i pozornie (przy odpowiednim formatowaniu) całość wygląda ok.

W tym przypadku, aby dokonać tych wyliczeń skorzystamy z formuł tablicowych. Odpowieni zapis może wyglądać tak jak niżej (dane źródłowe mają postać tabeli strukturalnej):

{=JEŻELI.BŁĄD(MEDIANA(JEŻELI(Dane[[#Wszystko];[Dział]]=$A4;JEŻELI(Dane[[#Wszystko];[Grade]]=$B4;Dane[[#Wszystko];[Wynagrodzenie]])));"")}

Taki sposób podlega licznym ograniczeniom. Oczywistym jest, że jeżeli zmienisz układ tabeli, np. dodasz kolejną kategorię danych, wg których mediana ma być wyliczana – całość się posypie. A zatem ta metoda jest ok, ale tylko jeżeli „na szybko” chcesz pokazać medianę w statycznym układzie danych.

Mediana w tabeli przestawnej – sposób (nieco) lepszy

Korzystając z formuły tablicowej możemy policzyć medianę (i inne statystyki) dla wielu warunków. Zamiast pokazywać ją obok tabeli przestawnej – dodajmy kolejną kolumnę w tabeli z danymi źródłowymi. Następnie z całości stworzymy tabelę przestawną. Kolejne kroki będą wyglądąły tak:

  1. Liczymy medianę w kolumnie (obok danych źródłowych) dla podanych warunków, czyli dla danego działu i grade’u. Formuła może wyglądać następująco:
{=JEŻELI.BŁĄD(MEDIANA(JEŻELI(Dane[[#Wszystko];[Dział]]=[@Dział];JEŻELI(Dane[[#Wszystko];[Grade]]=[@Grade];Dane[[#Wszystko];[Wynagrodzenie]])));"")}
  1. Z całości danych tworzymy tabelę przestawną.
  2. Układamy dane w pożądany przez nas sposób, dodając kolumnę z medianą do pola wartości.
  3. W oknie dialogowym USTAWIENIA POLA WARTOŚCI wybierz ŚREDNIĄ w PODSUMUJ POLE WARTOŚCI WEDŁUG.
  4. Zmień nazwę pola np. na „Mediana wynagrodzeń”. Nazwa musi być różna od nazwy kolumny w tabeli z danymi źródłowymi.
Mediana w tabeli przestawnej (jako  średnia)

Efekt jest teraz taki, że mediana jest częścią naszej tabeli przestawnej. Jednak i ten sposób podlega pewnym istotnym ograniczeniom. Tak naprawdę mamy bowiem do czynienia ze średnią z mediany! Jeżeli używasz tego sposobu świadomie to ok. Ale oczywiście wystarczy jedna zmiana w układzie tabeli, aby wpłynąć na wyliczenia i błędnie zinterpretować uzyskane informacje.

Jeżeli chcesz liczyć medianę efektywnie i bezbłędnie – takie podejście również nie jest wystarczające. Przejdźmy zatem do najlepszego rozwiązania.

Mediana w tabeli przestawnej – profesjonalnie!

Aby osiągnąć nasz cel wykorzystamy Power Pivot. Stworzymy tabelę przestawną opartą o model danych, a medianę wyliczymy jako tzw. miarę. Jeżeli dopiero teraz poczułeś niepewność, to spokojnie… nie będziemy robili niczego szczególnie skomplikowanego 🙂

Więcej na temat Power Pivot i modelu danych przeczytasz w cyklu artykułów:

Aby włączyć dodatek Power Pivot, idziemy na wstążkę i wybieramy PLIK > OPCJE > DODATKI > DODATKI COM. Klikamy przycisk przejdź, a następnie zaznaczamy Microsoft Power Pivot. Na wstążce zobaczysz nową kartę.

Ponownie utwórz tabelę przestawną ale tym razem, w menu tworzenia tabeli zaznacz opcję DODAJ TE DANE DO MODELU DANYCH.

Dodawanie danych do modelu danych w tabeli przestawnej

Ułóż dane w taki sposób jak wcześniej, tzn. umieszczając działy i grade’y w polach wierszy. Teraz przejdź na kartę POWER PIVOT i wybierz polecenie ZARZĄDZAJ.

Power Pivot - zarządzaj modelem danych

To właśnie tutaj utworzymy medianę w postaci miary. Kliknij w dowolne pole pod tabelą z danymi i wpisz nazwę nowej miary.

Nazywanie nowej miary w modelu danych

Miary są tworzone z wykorzystaniem języka DAX. Medianę możemy policzyć w taki sposób:

Mediana w tabeli przestawnej - miara DAX
Mediana:=MEDIAN([Wynagrodzenie])

To wszystko co należało zrobić w Power Pivocie. Przechodzimy z powrotem do Excela. W tabeli przestawnej automatycznie pojawi się nowa miara, którą teraz przeniesiemy do pola wartości.

Mediana w tabeli przestawnej - dodawanie miary

W ten sposób pozbyliśmy się wszystkich wad poprzednich sposobów i mamy wszelkie zalety tabeli przestawnej. Możemy zmieniać, dodawać, usuwać kolejne kategorie, a mediana będzie na bieżąco wyliczana.

Mediana wynagrodzeń w tabeli przestawnej

Oczywiście tabele przestawne, miary i model danych są powszechnie wykorzystywane w interaktywnych dashboardach. Jeżeli chcesz takie tworzyć, a nie masz doświadczenia zacznij od naszego bezpłatnego ebooka i gotowego szablonu.

Podsumowanie

Opisałem 3 sposoby na medianę w tabeli przestawnej. Oczywiście możesz użyć innych miar statystycznych, które nie są standardowo dostępne w tabeli przestawnej. Model danych i możliwość korzystania z miar jest fantastycznym sposobem na zaawansowane kalkulacje i analizy danych.

Starałem się, aby artykuł okazał się pomocny. Jeżeli też tak uważasz – proszę podziel się nim w swoich mediach społecznościowych, albo pokaż naszego bloga kolegom w pracy 🙂

Jeżeli natomiast znasz inne sposoby na wyliczenie mediany w tabeli przestawnej – koniecznie podziel się z nami w komentarzu.

Łukasz Pietrzak


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.


6 Comments

Karol Wolski · 23 listopada 2021 at 11:37

Łukasz, świetny tekst 🙂 Bardzo ważny i przydatny

    Łukasz Pietrzak · 23 listopada 2021 at 15:00

    Dzięki Karol 🙂

Joanna · 25 listopada 2021 at 18:03

Super, zrobiłam i działa 🙂 to rzeczywiście wpłynie na sposób wykonywania mojej pracy, dziękuję!

    Łukasz Pietrzak · 25 listopada 2021 at 18:15

    Super 🙂 Cieszę, się że mogliśmy pomóc.

Piotr · 1 grudnia 2021 at 21:12

Wspaniałe przedstawienie tematu, dla mnie niesamowite uproszczenie i standaryzacja wyliczenia mediany, dziękuję!

    Łukasz Pietrzak · 2 grudnia 2021 at 06:52

    Dzięki Piotr 🙂

Dodaj komentarz

Avatar placeholder

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