Diese Frage wird einmal alle zehn Jahre gestellt: Können Sie einen Median in einer Pivot-Tabelle erstellen? Traditionell war die Antwort Nein. Ich erinnere mich an das Jahr 2000, als ich Excel MVP Juan Pablo Gonzalez beauftragte, ein fantastisches Makro zu schreiben, das Berichte erstellte, die wie Pivot-Tabellen aussahen, aber Mediane hatten.
Als Alex in meinem Houston Power Excel-Seminar nach dem Erstellen von Medianen fragte, sagte ich schnell "Nein". Aber dann … fragte ich mich, ob DAX eine Medianfunktion hatte. Eine schnelle Google-Suche und Ja! Es gibt eine DAX-Funktion für Median.
Was braucht es, um DAX in einer Pivot-Tabelle zu verwenden? Sie benötigen die Windows-Version von Excel, auf der Excel 2013 oder höher ausgeführt wird.
Hier ist mein sehr einfacher Datensatz, mit dem ich testen werde, wie Pivot-Tabellenmediane berechnet werden. Sie können sehen, dass der Median für Chicago 5000 und der Median für Cleveland 17000 betragen sollte. Im Fall von Chicago gibt es fünf Werte, sodass der Median der dritthöchste Wert ist. Für den gesamten Datensatz gibt es jedoch 12 Werte. Das bedeutet, dass der Median irgendwo zwischen 11000 und 13000 liegt. Excel mittelt diese beiden Werte, um 12000 zurückzugeben.
Wählen Sie zunächst eine Zelle in Ihren Daten aus und drücken Sie Strg + T, um die Daten als Tabelle zu formatieren.
Wählen Sie dann Einfügen, Pivot-Tabelle. Aktivieren Sie im Dialogfeld PivotTable einfügen das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen.
Wählen Sie in den Pivot-Tabellenfeldern Region und Distrikt aus. Wählen Sie jedoch nicht Vertrieb. Klicken Sie stattdessen mit der rechten Maustaste auf die Überschrift Tabelle und wählen Sie Neue Kennzahl. "Measure" ist ein ausgefallener Name für ein berechnetes Feld. Kennzahlen sind leistungsfähiger als berechnete Felder in regulären Pivot-Tabellen.
Füllen Sie im Dialogfeld "Maßnahme definieren" die folgenden vier Einträge aus:
- Kennzahlname: Median des Umsatzes
- Formel
=MEDIAN((Sales)) - Zahlenformat: Nummer
- Dezimalstellen: 0
Nach dem Erstellen der Kennzahl wird sie der Liste der Felder hinzugefügt. Sie müssen jedoch den Eintrag auswählen, um sie dem Bereich Werte der Pivot-Tabelle hinzuzufügen. Wie Sie unten sehen können, berechnet die Pivot-Tabelle die Mediane korrekt.
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2197: Median in einer Pivot-Tabelle.
Ich muss dir sagen, ich bin sehr aufgeregt darüber. Als ich in Houston war und dort ein Power Excel-Seminar absolvierte, hob Alex die Hand und sagte: "Hey, gibt es eine Möglichkeit, einen Median in einer Pivot-Tabelle zu erstellen?" Nein, Sie können keinen Median in einer Pivot-Tabelle erstellen. Ich erinnere mich, dass mein guter Freund Juan Pablo Gonzales vor 25 Jahren tatsächlich ein Makro mitgebracht hat, um das Äquivalent zum Median zu erreichen, ohne eine Pivot-Tabelle zu verwenden - es ist einfach nicht möglich.
Aber ich hatte einen Funken. Ich sagte: "Warte eine Sekunde", öffne einen Browser und suche nach "DAX-Median". Natürlich gibt es in DAX eine MEDIAN-Funktion, mit der wir dieses Problem lösen können.
Okay, um DAX verwenden zu können, müssen Sie sich in Excel 2013 oder Excel 2016 oder in Excel 2010 befinden und über das Power Pivot-Add-In verfügen. Sie müssen nicht die Registerkarte Power Pivot haben, sondern nur das Datenmodell. In Ordung? Also werde ich diese Daten auswählen, sie mit Strg + T in eine Tabelle umwandeln und sie geben ihnen den einfallslosen Namen "Tabelle 4". In Ihrem Fall könnte es sich um "Tabelle 1" handeln. Wir werden eine Pivot-Tabelle einfügen, diese Daten zum Datenmodell hinzufügen, auf OK klicken und auf der linken Seite Regional auswählen, nicht jedoch Vertrieb. Stattdessen möchte ich eine neue berechnete Messung erstellen. Also komme ich hier an den Tisch und klicke mit der rechten Maustaste und sage: Maßnahme hinzufügen. Und diese Kennzahl wird "Median of Sales" genannt, und die Formel lautet: = MEDIAN. Drücken Sie dort die Tabulatortaste und wählen Sie Vertrieb,schließende Klammer. Ich kann dies als Zahl mit null Dezimalstellen wählen, ein Tausendertrennzeichen verwenden und auf OK klicken. Und mal sehen, unser neues Feld wird hier hinzugefügt, wir müssen es ankreuzen, um es hinzuzufügen, und es besagt, dass der Median für den Mittleren Westen 12.000 beträgt.
Lassen Sie uns das jetzt überprüfen. Hier im gesamten Mittleren Westen liegt der Median der gesamten Datensätze zwischen 11.000 und 13.000. Es sind also durchschnittlich 11 und 13, genau das würde der Median in normalem Excel tun. Fügen wir nun einen Distrikt hinzu und es heißt, der Median von Chicago 5.000, der Median von Cleveland ist 17.000; Mittlerer Westen insgesamt und Gesamtsumme 12.000. Das alles ist richtig. Wie großartig ist das? Schließlich Median in einer Pivot-Tabelle dank eines berechneten Felds oder Maßes, wie es genannt wird, und des Datenmodells.
Nun, viele meiner Lieblingstipps - die Tipps für mein Live-Power-Excel-Seminar - in diesem Buch LIVe, Die 54 besten Tipps aller Zeiten. Klicken Sie auf das "I" in der oberen rechten Ecke, um mehr über das Buch zu erfahren.
In Ordung. Zusammenfassung: Können Sie einen Median in einer Pivot-Tabelle erstellen? Oh nein, ja, ja, dank des Datenmodells können Sie. Sie können einen Median erstellen. Strg + T, um Ihre Daten in eine Tabelle zu verwandeln; Pivot-Tabelle einfügen; und aktivieren Sie das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen. Klicken Sie mit der rechten Maustaste auf den Tabellennamen und wählen Sie eine neue Kennzahl aus. Die Kennzahl lautet = MEDIAN ((Sales)). Es ist toll.
Vielen Dank an Alex, dass er in meinem Seminar aufgetaucht ist und diese Frage gestellt hat. Vielen Dank, dass Sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: pivot-table-median.xlsx
