To pytanie pojawia się raz na dekadę: czy potrafisz zrobić medianę w tabeli przestawnej. Tradycyjnie odpowiedź brzmiała: Nie. Pamiętam, jak w 2000 roku zatrudniłem Juana Pablo Gonzaleza, MVP programu Excel, do napisania niesamowitego makra, które tworzyło raporty, które wyglądały jak tabele przestawne, ale miały mediany.
Kiedy więc Alex na moim seminarium Houston Power Excel zapytał o tworzenie median, szybko odpowiedziałem „Nie”. Ale potem… Zastanawiałem się, czy DAX ma funkcję mediany. Szybkie wyszukiwanie w Google i tak! Mediana ma funkcję języka DAX.
Czego potrzeba, aby używać języka DAX w tabeli przestawnej? Potrzebujesz wersji programu Excel dla systemu Windows z programem Excel 2013 lub nowszym.
Oto mój bardzo prosty zestaw danych, którego użyję do sprawdzenia, jak obliczane są mediany tabeli przestawnej. Możesz zobaczyć, że mediana dla Chicago powinna wynosić 5000, a mediana dla Cleveland powinna wynosić 17000. W przypadku Chicago istnieje pięć wartości, więc mediana będzie trzecią najwyższą wartością. Ale dla całego zestawu danych jest 12 wartości. Oznacza to, że mediana jest między 11000 a 13000. Excel uśrednia te dwie wartości, aby zwrócić 12000.
Aby rozpocząć, wybierz jedną komórkę w danych i naciśnij Ctrl + T, aby sformatować dane jako tabelę.
Następnie wybierz Wstaw, Tabela przestawna. W oknie dialogowym Wstawianie tabeli przestawnej wybierz pole Dodaj te dane do modelu danych.
W polach tabeli przestawnej wybierz region i dystrykt. Ale nie wybieraj sprzedaży. Zamiast tego kliknij prawym przyciskiem myszy nagłówek Tabela i wybierz Nowa miara. „Miara” to fantazyjna nazwa pola obliczeniowego. Miary mają większe możliwości niż pola obliczeniowe w zwykłych tabelach przestawnych.
W oknie dialogowym Zdefiniuj miarę wypełnij cztery wpisy pokazane poniżej:
- Nazwa miary: mediana sprzedaży
- Formuła
=MEDIAN((Sales)) - Format liczb: liczba
- Miejsca dziesiętne: 0
Po utworzeniu miary jest ona dodawana do listy pól, ale musisz wybrać wpis, aby dodać go do obszaru Wartości tabeli przestawnej. Jak widać poniżej, tabela przestawna poprawnie oblicza mediany.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2197: Mediana w tabeli przestawnej.
Muszę ci powiedzieć, że jestem ogromnie podekscytowany. kiedy byłem w Houston na seminarium Power Excel i Alex podniósł rękę i powiedział: „Hej, czy jest jakiś sposób na zrobienie mediany w tabeli przestawnej?” Nie, nie możesz zrobić mediany w tabeli przestawnej. Pamiętam, że 25 lat temu mój dobry przyjaciel, Juan Pablo Gonzales, przyniósł makro, aby wykonać odpowiednik mediany, bez użycia tabeli przestawnej - to po prostu niemożliwe.
Ale miałem iskrę. Powiedziałem „Zaczekaj chwilę”, po czym otwieram przeglądarkę i wyszukuję „medianę języka DAX”. Oczywiście w języku DAX jest funkcja MEDIAN, co oznacza, że możemy rozwiązać ten problem.
W porządku, więc aby używać języka DAX, musisz być w programie Excel 2013 lub Excel 2016 albo w programie Excel 2010 i mieć dodatek Power Pivot; nie musisz mieć zakładki Power Pivot, po prostu musimy mieć model danych. W porządku? Więc wybiorę te dane, zrobię z nich tabelę za pomocą Ctrl + T i nadadzą im niewyobrażalną nazwę „Tabela 4”. W Twoim przypadku może to być „Tabela 1”. Wstawimy tabelę przestawną, dodamy te dane do modelu danych, klikniemy OK i wybierzemy Regionalny po lewej stronie, ale nie Sprzedaż. Zamiast tego chcę utworzyć nowy obliczony pomiar. Podchodzę więc do stołu, klikam prawym przyciskiem myszy i mówię „Dodaj miarę”. Miara ta będzie się nazywać „Mediana sprzedaży”, a wzór będzie wyglądał następująco: = MEDIANA. Naciśnij tam zakładkę i wybierz Sprzedaż,nawias zamykający. Mogę wybrać to jako liczbę bez miejsc po przecinku, użyć separatora tysięcy i kliknąć OK. Zobaczmy, nasze nowe pole zostało tutaj dodane, musimy je zaznaczyć, aby je dodać, i mówi, że mediana dla Środkowego Zachodu wynosi 12 000.
Teraz sprawdźmy to. Więc tutaj, na całym Środkowym Zachodzie, mediana wszystkich zbiorów danych mieści się w przedziale od 11 000 do 13 000. Czyli uśrednia 11 i 13, czyli dokładnie to, co zrobiłaby mediana w zwykłym Excelu. Teraz dodajmy dzielnicę i mówi się, że mediana z 5 000 Chicago, mediana Cleveland to 17 000; Łącznie na Środkowym Zachodzie i razem 12.000. Wszystko to jest poprawne. Jakie to niesamowite? Na koniec mediana w tabeli przestawnej dzięki polu obliczeniowemu lub miary, jak to się nazywa, i modelowi danych.
Teraz wiele z moich ulubionych wskazówek - wskazówki do mojego seminarium Power Excel na żywo - w tej książce LIVe, The 54 Greatest Tips of All Time. Kliknij „I” w prawym górnym rogu, aby przeczytać więcej o książce.
W porządku. Podsumowanie: czy możesz obliczyć medianę w tabeli przestawnej? O nie, tak, tak, dzięki modelowi danych. Możesz utworzyć medianę; Ctrl + T, aby przekształcić dane w tabelę; Wstaw tabelę przestawną; i zaznacz to pole, Dodaj te dane do modelu danych; kliknij prawym przyciskiem myszy nazwę tabeli i wybierz nową miarę, a miarą będzie = MEDIANA ((Sales)). To jest zajebiste.
Dziękuję Alexowi za pojawienie się na moim seminarium i zadawanie tego pytania, dziękuję za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: pivot-table-median.xlsx
