Dette spørgsmål kommer op hvert årti: Kan du lave en median i en pivottabel. Traditionelt var svaret Nej. Jeg husker tilbage i 2000, da jeg hyrede Excel MVP Juan Pablo Gonzalez til at skrive en fantastisk makro, der oprettede rapporter, der lignede pivottabeller, men som havde medianer.
Så da Alex i mit Houston Power Excel-seminar spurgte om at skabe medianer, var jeg hurtig til at sige "Nej". Men så … jeg spekulerede på, om DAX havde en medianfunktion. En hurtig Google-søgning og Ja! Der er en DAX-funktion til Median.
Hvad skal der til for at bruge DAX i en pivottabel? Du har brug for Windows-versionen af Excel, der kører Excel 2013 eller nyere.
Her er mit meget enkle datasæt, som jeg vil bruge til at teste, hvordan pivottabelmedianer beregnes. Du kan se medianen for Chicago skal være 5000 og medianen for Cleveland skal være 17000. I tilfælde af Chicago er der fem værdier, så medianen vil være den 3. højeste værdi. Men for hele datasættet er der 12 værdier. Det betyder, at medianen er et sted mellem 11000 og 13000. Excel gennemsnit disse to værdier for at returnere 12000.
For at begynde skal du vælge en celle i dine data og trykke på Ctrl + T for at formatere dataene som en tabel.
Vælg derefter Indsæt, drejetabel. I dialogboksen Indsæt pivottabel skal du vælge afkrydsningsfeltet Føj disse data til datamodellen.
Vælg Region og District i Pivot Table Fields. Men vælg ikke Salg. I stedet skal du højreklikke på tabeloverskriften og vælge nyt mål. "Mål" er et smukt navn for et beregnet felt. Mål er stærkere end beregnede felter i almindelige pivottabeller.
I dialogboksen Definer mål udfyld de fire poster vist nedenfor:
- Mål Navn: Median af salg
- Formel
=MEDIAN((Sales)) - Nummerformat: Antal
- Decimaler: 0
Når målingen er oprettet, føjes den til listen over felter, men du skal vælge posten for at tilføje den til området Værdier i pivottabellen. Som du kan se nedenfor, beregner pivottabellen medianerne korrekt.
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2197: Median i en pivottabel.
Jeg må fortælle dig, at jeg er meget begejstret for dette. da jeg var i Houston og lavede et Power Excel-seminar der, og Alex løftede hånden og sagde: "Hej, er der nogen måde at lave en median i et drejetabel på?" Nej, du kan ikke lave en median i en pivottabel. Jeg husker for 25 år siden, min gode ven, Juan Pablo Gonzales, bragte faktisk en makro til at gøre det svarende til medianen, uden at bruge et drejetabel - det er bare ikke muligt.
Men jeg havde en gnist. Jeg sagde: "Vent et øjeblik," og jeg åbner en browser, og jeg søger efter "DAX-median", og der er helt sikkert en MEDIAN-funktion i DAX, hvilket betyder, at vi kan løse dette problem.
Okay, så for at bruge DAX skal du være i Excel 2013 eller Excel 2016 eller i Excel 2010 og have tilføjelsesprogrammet Power Pivot; du behøver ikke at have Power Pivot-fanen, vi skal bare have datamodellen. I orden? Så jeg skal vælge disse data, jeg vil gøre det til en tabel med Ctrl + T, og de giver det et fantasifuldt navn på "Tabel 4". I dit tilfælde kan det være "tabel 1". Og vi indsætter en pivottabel, tilføjer disse data til datamodellen, klikker på OK, og vi vælger Regional på venstre side, men ikke Salg. I stedet vil jeg oprette en ny beregnet måling. Så jeg kommer her op til bordet, og jeg højreklikker og siger, Tilføj mål. Og denne foranstaltning kaldes "Median of Sales", og formlen vil være: = MEDIAN. Tryk på fanen der, og vælg Salg,lukker parentes. Jeg kan vælge dette som et tal med nul decimaler, bruge tusind separator og klikke på OK. Og lad os se, vores nye felt tilføjes her, vi er nødt til at afkrydse det for at tilføje det, og det står, at medianen for Midtvesten er 12.000.
Lad os nu kontrollere det. Så her, hele Midtvesten, medianen for hele datasættet mellem 11.000 og 13.000. Så det er gennemsnit 11 og 13, hvilket er nøjagtigt hvad medianen ville gøre i almindelig Excel. Lad os nu tilføje distrikt, og der står medianen for Chicagos 5.000, medianen for Cleveland er 17.000; Midtvesten i alt og i alt 12.000. Alt dette er korrekt. Hvor fantastisk er det? Endelig median i en pivottabel takket være et beregnet felt eller mål, som det hedder, og datamodellen.
Nu er mange af mine yndlingstip - tipene til mit live Power Excel-seminar - i denne bog LIVe, The 54 Greatest Tips of All Time. Klik på det "I" øverst til højre for at læse mere om bogen.
I orden. Afslutning: Kan du lave en median i et drejetabel? Åh nej, ja, ja det kan du takket være datamodellen. Du kan oprette en median; Ctrl + T for at gøre dine data til en tabel; Indsæt drejetabel; og afkryds dette felt, tilføj disse data til datamodellen; højreklik på tabelnavnet, og vælg nyt mål, og målet vil være = MEDIAN ((Salg)). Det er fantastisk.
Tak til Alex for at dukke op på mit seminar og stille det spørgsmål, tak til dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: pivot-table-median.xlsx
