Deze vraag komt eens in de tien jaar naar voren: kun je een mediaan in een draaitabel doen? Traditioneel was het antwoord nee. Ik herinner me dat ik in 2000 Excel MVP Juan Pablo Gonzalez inhuurde om een geweldige macro te schrijven die rapporten maakte die eruit zagen als draaitabellen maar met medianen.
Dus toen Alex in mijn Houston Power Excel-seminar vroeg naar het maken van medianen, zei ik snel "Nee". Maar toen… Ik vroeg me af of DAX een mediaanfunctie had. Een snelle Google-zoekopdracht en ja! Er is een DAX-functie voor Mediaan.
Wat is er nodig om DAX in een draaitabel te gebruiken? U hebt de Windows-versie van Excel nodig met Excel 2013 of nieuwer.
Hier is mijn zeer eenvoudige gegevensset die ik zal gebruiken om te testen hoe de medianen van de draaitabel worden berekend. U kunt zien dat de mediaan voor Chicago 5000 moet zijn en de mediaan voor Cleveland 17000. In het geval van Chicago zijn er vijf waarden, dus de mediaan is de op twee na hoogste waarde. Maar voor de hele dataset zijn er 12 waarden. Dat betekent dat de mediaan ergens tussen 11000 en 13000 ligt. Excel neemt het gemiddelde van deze twee waarden om 12000 te retourneren.
Selecteer om te beginnen een cel in uw gegevens en druk op Ctrl + T om de gegevens als een tabel op te maken.
Kies vervolgens Invoegen, Draaitabel. Kies in het dialoogvenster Draaitabel invoegen het vak voor Deze gegevens aan het gegevensmodel toevoegen.
Kies Regio en District in de draaitabelvelden. Maar kies niet voor Sales. Klik in plaats daarvan met de rechtermuisknop op de kop Tabel en kies Nieuwe meting. "Meten" is een mooie naam voor een berekend veld. Metingen zijn krachtiger dan berekende velden in normale draaitabellen.
Vul in het dialoogvenster Maat definiëren de vier onderstaande gegevens in:
- Maatregelnaam: Mediaan van verkoop
- Formule
=MEDIAN((Sales)) - Getalnotatie: Getal
- Decimale plaatsen: 0
Nadat de meting is gemaakt, wordt deze toegevoegd aan de lijst met velden, maar u moet het item kiezen om het toe te voegen aan het gebied Waarden van de draaitabel. Zoals u hieronder kunt zien, berekent de draaitabel de medianen correct.
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2197: mediaan in een draaitabel.
Ik moet je zeggen, ik ben hier enorm opgewonden over. toen ik daar in Houston een Power Excel-seminar deed, en Alex zijn hand opstak en zei: "Hé, is er een manier om een mediaan in een draaitabel te doen?" Nee, u kunt geen mediaan in een draaitabel doen. Ik herinner me 25 jaar geleden dat mijn goede vriend, Juan Pablo Gonzales, eigenlijk een macro bracht om het equivalent van mediaan te doen, zonder een draaitabel te gebruiken - het is gewoon niet mogelijk.
Maar ik had een vonk. Ik zei: "Wacht even" en ik open een browser, en ik zoek naar "DAX median", en inderdaad, er is een MEDIAN-functie in DAX, wat betekent dat we dit probleem kunnen oplossen.
Oké, dus om DAX te gebruiken, moet u zich in Excel 2013 of Excel 2016 of in Excel 2010 bevinden en de Power Pivot-invoegtoepassing hebben; u hoeft niet het Power Pivot-tabblad te hebben, we hebben alleen het datamodel nodig. Oké? Dus ik ga deze gegevens kiezen, ik ga er een tabel van maken met Ctrl + T, en ze geven het een fantasieloze naam van "Tabel 4". In uw geval kan het "Tabel 1" zijn. En we voegen een draaitabel in, voegen deze gegevens toe aan het gegevensmodel, klikken op OK en we kiezen aan de linkerkant Regionaal, maar niet Verkoop. In plaats daarvan wil ik een nieuwe berekende meting maken. Dus ik kom hier naar de tafel en ik klik met de rechtermuisknop en zeg: Maat toevoegen. En deze maat zal "Mediaan van Verkoop" worden genoemd, en de formule zal zijn: = MEDIAAN. Druk daar op de tab en kies Verkoop,haakje sluiten. Ik kan dit kiezen als een getal met nul decimalen, een duizendtal scheidingsteken gebruiken en op OK klikken. En laten we eens kijken, ons nieuwe veld is hier toegevoegd, we moeten het aanvinken om het toe te voegen, en er staat dat de mediaan voor Midwest 12.000 is.
Laten we dat nu eens kijken. Dus hier, heel Midwest, de mediaan van alle gegevenssets tussen 11.000 en 13.000. Het is dus gemiddeld 11 en 13, wat precies is wat de mediaan zou doen in normaal Excel. Laten we nu district toevoegen en er staat dat de mediaan van Chicago's 5.000 is, de mediaan van Cleveland is 17.000; Midwest totaal en eindtotaal 12.000. Dat klopt allemaal. Hoe gaaf is dat? Eindelijk mediaan in een draaitabel, dankzij een berekend veld, of meting, zoals het wordt genoemd, en het gegevensmodel.
Nu, veel van mijn favoriete tips - de tips voor mijn live Power Excel-seminar - in dit boek LIVe, The 54 Greatest Tips of All Time. Klik op die "I" in de rechterbovenhoek om meer over het boek te lezen.
Oké. Afronding: kunt u een mediaan in een draaitabel doen? Oh nee, ja, ja dat kan, dankzij het datamodel. U kunt een mediaan maken; Ctrl + T om uw gegevens in een tabel te veranderen; Draaitabel invoegen; en vink dat vakje aan, Voeg deze gegevens toe aan het gegevensmodel; klik met de rechtermuisknop op de tabelnaam en kies een nieuwe maat, en de maat is = MEDIAAN ((Verkoop)). Het is geweldig.
Dank aan Alex voor het verschijnen op mijn seminar en het stellen van die vraag, dank aan jou voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: pivot-table-median.xlsx
