Dette spørsmålet kommer opp en gang hvert tiår: Kan du gjøre en median i et pivottabell. Tradisjonelt var svaret nei. Jeg husker tilbake i 2000 da jeg hyret Excel MVP Juan Pablo Gonzalez til å skrive en fantastisk makro som opprettet rapporter som så ut som pivottabeller, men som hadde medianer.
Så da Alex i mitt Houston Power Excel-seminar spurte om å lage medianer, var jeg rask med å si "Nei". Men så … Jeg lurte på om DAX hadde en medianfunksjon. Et raskt Google-søk og Ja! Det er en DAX-funksjon for Median.
Hva skal til for å bruke DAX i en pivottabell? Du trenger Windows-versjonen av Excel som kjører Excel 2013 eller nyere.
Her er det veldig enkle datasettet mitt som jeg vil bruke til å teste hvordan pivottabellmedianer beregnes. Du kan se at medianen for Chicago skal være 5000 og medianen for Cleveland skal være 17000. I tilfelle Chicago er det fem verdier, så medianen vil være den tredje høyeste verdien. Men for hele datasettet er det 12 verdier. Det betyr at medianen er et sted mellom 11000 og 13000. Excel er gjennomsnitt av de to verdiene for å returnere 12000.
For å begynne, velg en celle i dataene dine og trykk Ctrl + T for å formatere dataene som en tabell.
Velg deretter Sett inn, pivottabell. I dialogboksen Sett inn pivottabell velger du boksen for Legg til disse dataene i datamodellen.
Velg Region og distrikt i pivottabellfeltene. Men ikke velg Salg. Høyreklikk i stedet på tabelloverskriften og velg Nytt mål. "Mål" er et fancy navn for et beregnet felt. Tiltak er kraftigere enn beregnede felt i vanlige pivottabeller.
I dialogboksen Definer mål, fyll ut de fire oppføringene vist nedenfor:
- Målnavn: Median of Sales
- Formel
=MEDIAN((Sales)) - Tallformat: Antall
- Desimaler: 0
Etter at du har opprettet tiltaket, legges det til i listen over felt, men du må velge oppføringen for å legge den til Verdier-området i pivottabellen. Som du kan se nedenfor, beregner pivottabellen medianene riktig.
Se på video
Videoutskrift
Lær Excel fra Podcast, Episode 2197: Median in a Pivot Table.
Jeg må fortelle deg at jeg er veldig spent på dette. da jeg var i Houston og holdt et Power Excel-seminar der, og Alex løftet hånden, sa: "Hei, er det noen måte å gjøre en median i et pivottabell?" Nei, du kan ikke gjøre en median i et pivottabell. Jeg husker for 25 år siden, min gode venn, Juan Pablo Gonzales, tok faktisk med en makro for å gjøre tilsvarende median, uten å bruke et pivottabell - det er bare ikke mulig.
Men jeg hadde en gnist. Jeg sa, "Vent et sekund," og jeg åpner en nettleser, og jeg søker etter "DAX median", og det er sikkert en MEDIAN-funksjon i DAX, som betyr at vi kan løse dette problemet.
Greit, så for å kunne bruke DAX, må du være i Excel 2013 eller Excel 2016, eller i Excel 2010, og ha Power Pivot-tillegget; du trenger ikke å ha Power Pivot-fanen, vi må bare ha datamodellen. Ok? Så jeg skal velge disse dataene, jeg skal gjøre det til en tabell med Ctrl + T, og de gir det et fantasiløst navn på "Tabell 4". I ditt tilfelle kan det være "Tabell 1". Og vi setter inn en pivottabell, legger til disse dataene i datamodellen, klikker OK, og vi velger Regional på venstre side, men ikke Salg. I stedet vil jeg lage en ny beregnet måling. Så jeg kommer hit til bordet og høyreklikker og sier, Legg til mål. Og dette tiltaket vil bli kalt "Median of Sales", og formelen vil være: = MEDIAN. Trykk på fanen der og velg Salg,lukker parentes. Jeg kan velge dette som et tall med null desimaler, bruke tusen skilletegn og klikke OK. Og la oss se, det nye feltet vårt er lagt til her, vi må merke av for å legge det til, og det står at medianen for Midtvesten er 12.000.
La oss nå sjekke det. Så her, hele Midtvesten, medianen for hele datasettet setter mellom 11.000 og 13.000. Så det er gjennomsnittlig 11 og 13, som er nøyaktig hva medianen ville gjort i vanlig Excel. La oss nå legge til distrikt, og det står medianen for Chicagos 5.000, medianen for Cleveland er 17.000; Midtvesten totalt og totalt 12.000. Alt dette er riktig. Hvor fantastisk er det? Til slutt, median i en pivottabell, takket være et beregnet felt eller mål, som det heter, og datamodellen.
Nå, mange av mine favorittips - tipsene til mitt live Power Excel-seminar - i denne boken LIVe, The 54 Greatest Tips of All Time. Klikk på "I" øverst til høyre for å lese mer om boka.
Ok. Avslutning: Kan du gjøre en median i et pivottabell? Å nei, ja, ja du kan, takket være datamodellen. Du kan opprette en median; Ctrl + T for å gjøre dataene dine til en tabell; Sett inn pivottabell; og kryss av i ruten, Legg til disse dataene i datamodellen; høyreklikk på tabellnavnet og velg nytt mål, og målet vil være = MEDIAN ((Salg)). Det er kjempebra.
Takk til Alex for at du møtte opp på seminaret mitt og stilte det spørsmålet, takk til deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: pivot-table-median.xlsx
