Den här frågan kommer upp en gång per årtionde: Kan du göra en median i ett pivottabell. Traditionellt var svaret Nej. Jag minns redan 2000 när jag anställde Excel MVP Juan Pablo Gonzalez för att skriva ett fantastiskt makro som skapade rapporter som såg ut som pivottabeller men hade medianer.
Så när Alex i mitt Houston Power Excel-seminarium frågade om att skapa medianer, var jag snabb att säga "Nej". Men då … Jag undrade om DAX hade en medianfunktion. En snabb Google-sökning och Ja! Det finns en DAX-funktion för Median.
Vad krävs för att använda DAX i en pivottabell? Du behöver Windows-versionen av Excel som kör Excel 2013 eller senare.
Här är min mycket enkla datamängd som jag kommer att använda för att testa hur pivottabellmedianer beräknas. Du kan se medianen för Chicago ska vara 5000 och medianen för Cleveland bör vara 17000. I fallet Chicago finns det fem värden, så medianen blir det tredje högsta värdet. Men för hela datamängden finns det 12 värden. Det betyder att medianen är någonstans mellan 11000 och 13000. Excel genomsnittar de två värdena för att returnera 12000.
För att börja markera en cell i dina data och tryck på Ctrl + T för att formatera data som en tabell.
Välj sedan Infoga, pivottabell. I rutan Infoga pivottabell väljer du rutan för Lägg till dessa data i datamodellen.
I Region Pivot Table Fields väljer du Region och District. Men välj inte Försäljning. Högerklicka istället på rubriken Tabell och välj Nytt mått. "Mått" är ett snyggt namn för ett beräknat fält. Mått är kraftfullare än beräknade fält i vanliga pivottabeller.
I dialogrutan Definiera mått fyller du i de fyra posterna som visas nedan:
- Måttnamn: försäljningsmedian
- Formel
=MEDIAN((Sales)) - Nummerformat: Antal
- Decimaler: 0
När du har skapat måttet läggs det till i fältlistan, men du måste välja posten för att lägga till den i området Värden i pivottabellen. Som du kan se nedan beräknar pivottabellen korrekt medianerna.
Kolla på video
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2197: Median i en pivottabell.
Jag måste berätta för dig, jag är enormt upphetsad över det här. när jag var i Houston och gjorde ett Power Excel-seminarium där, och Alex räckte upp handen, sa "Hej, finns det något sätt att göra en median i ett pivottabell?" Nej, du kan inte göra en median i ett pivottabell. Jag minns för 25 år sedan, min goda vän, Juan Pablo Gonzales, tog faktiskt med ett makro för att göra motsvarande median, utan att använda ett pivottabell - det är bara inte möjligt.
Men jag hade en gnista. Jag sa "Vänta en sekund" och jag öppnar en webbläsare och jag söker efter "DAX-median", och det finns nog en MEDIAN-funktion i DAX, vilket innebär att vi kan lösa detta problem.
Okej, så för att kunna använda DAX måste du vara i Excel 2013 eller Excel 2016 eller i Excel 2010 och ha Power Pivot-tillägget; du behöver inte ha fliken Power Pivot, vi måste bara ha datamodellen. OK? Så jag ska välja dessa data, jag kommer att göra det till en tabell med Ctrl + T, och de ger den ett fantasilöst namn på "Tabell 4". I ditt fall kan det vara "Tabell 1". Och vi infogar en pivottabell, lägger till dessa data i datamodellen, klickar på OK och vi väljer Regional på vänster sida, men inte Sales. Istället vill jag skapa en ny beräknad mätning. Så jag kommer upp till bordet och högerklickar och säger, Lägg till mått. Och denna åtgärd kommer att kallas "Median of Sales", och formeln kommer att vara: = MEDIAN. Tryck på fliken där och välj Försäljning,stängande parentes. Jag kan välja detta som ett tal med noll decimaler, använda tusen avgränsare och klicka på OK. Och, låt oss se, vårt nya fält läggs till här, vi måste markera det för att lägga till det, och det står att medianen för Midwest är 12.000.
Låt oss nu kontrollera det. Så här, hela Midwest, medianen för hela datamängden mellan 11 000 och 13 000. Så det är i genomsnitt 11 och 13, vilket är exakt vad medianen skulle göra i vanlig Excel. Låt oss nu lägga till distrikt och det står att medianen för Chicagos 5 000, medianen för Cleveland är 17 000; Mellanvästern och totalt 12 000. Allt detta är korrekt. Hur fantastiskt är det? Slutligen, median i en pivottabell, tack vare ett beräknat fält eller mått, som det heter, och datamodellen.
Nu, många av mina favorittips - tipsen för mitt live Power Excel-seminarium - i den här boken LIVe, The 54 Greatest Tips of All Time. Klicka på "jag" i det övre högra hörnet för att läsa mer om boken.
OK. Avslutning: Kan du göra en median i ett pivottabell? Åh nej, ja, ja du kan, tack vare datamodellen. Du kan skapa en median; Ctrl + T för att göra dina data till en tabell; Infoga pivottabell; och kryssa i den rutan, Lägg till dessa data i datamodellen; högerklicka på tabellnamnet och välj nytt mått, och måttet blir = MEDIAN ((Försäljning)). Det är jättehäftigt.
Tack till Alex för att du dykt upp i mitt seminarium och ställde den frågan, tack till dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner excel-filen: pivot-table-median.xlsx
