Wie man eindeutige Elemente (count distinct) in einer Excel-Pivot-Tabelle zählt. Drei Methoden, für verschiedene Versionen von Excel. Holen Sie sich die kostenlose Arbeitsmappe, um mitzumachen
Danke an Roger Govier, der diese Anleitung und Arbeitsmappe erstellt hat.
Einzelstückzählung
Wenn Sie eine Pivot-Tabelle verwenden, um Ihre Daten zusammenzufassen, haben Sie vielleicht Fragen wie:
- Wie viele Einzelstücke (Einzelstücke) haben in jeder Region gekauft?
- Wie viele eindeutige Produkte wurden in jeder Filiale verkauft?
- Wie viele eindeutige Verkäufer haben jedes Produkt pro Region verkauft?
Es gibt keine eingebaute Funktion „Eindeutige Zählung“ in einer normalen Pivot-Tabelle, aber in diesem Tutorial sehen Sie, wie Sie eine eindeutige Zählung von Artikeln in einer Pivot-Tabelle erhalten.
- In Excel 2013 und späteren Versionen erstellen Sie ein Datenmodell
- In Excel 2010 und späteren Versionen verwenden Sie eine Technik, die „Pivot-Tabelle“.
- Oder, in älteren Versionen, fügen Sie eine neue Spalte zu den Quelldaten hinzu und verwenden Sie CountIf.
- Wenn das Power Pivot Add-In installiert ist, verwenden Sie es, um eine eindeutige Zählung anzuzeigen
Dieses Video zeigt die Schritte, um eine eindeutige Zählung mit dem Datenmodell anzuzeigen.
Einführung
Oft haben wir große Datensätze, die wir mit Hilfe einer Pivot-Tabelle analysieren wollen, aber wir wollen nur die eindeutigen Vorkommen einiger Datenelemente zählen. Leider hatten Pivot-Tabellen noch nie die Möglichkeit, eindeutige Werte zu zählen.
In diesem Beispiel hat die Beispieldatei 4999 Datensätze, die Produktverkäufe mit der Region und dem Namen des Verkäufers zeigen. Die ersten paar Datensätze sind im folgenden Screenshot zu sehen. Sie können die Beispieldatei über den unten stehenden Link herunterladen.
Wenn wir aus den Daten eine Pivot-Tabelle erstellen, erhalten wir etwas wie das Folgende, wenn wir im Bereich „Werte“ der Pivot-Tabelle „Person“ hinzufügen und mit „Anzahl“ zusammenfassen.
Dies liefert uns jedoch eine Zählung aller Transaktionen, nicht die Zählung der eindeutigen Anzahl von Personen, die diese Transaktionen innerhalb jeder Region durchgeführt haben.
Eine eindeutige Zählung erhalten
Es gibt Umgehungen, die Sie verwenden können, um eine eindeutige Zählung zu erhalten:
- Methode 1: In Excel 2013 und höher fügen Sie die Quelldaten der Pivot-Tabelle zum Datenmodell hinzu, und eine eindeutige Zählung kann einfach durchgeführt werden.
- Methode 2: In Excel 2010 und höher verwenden Sie die „Pivot a Pivot“-Technik.
- Methode 3: In älteren Excel-Versionen fügen Sie eine Spalte zu den Quelldaten hinzu, um in Zellen einer Zeile, in denen der Wert eindeutig ist, eine 1 und in allen anderen Zellen eine 0 zu setzen. Die Summe dieser zusätzlichen Spalte ergibt dann den eindeutigen Wert.
- Methode 4: Verwenden Sie PowerPivot, um die Pivot-Tabelle zu erstellen, und verwenden Sie deren Funktionen, um eine eindeutige Zählung zu erstellen. Siehe die Details unten.
Alle vier Methoden werden im Folgenden beschrieben, zusammen mit einem Leistungsvergleich zwischen den Methoden 2 und 3.
1. Zum Datenmodell hinzufügen – Excel 2013 und später
In Excel 2013 ist es einfach, eine eindeutige Zählung zu erstellen, wenn Sie die Quelldaten einer Pivot-Tabelle zum Datenmodell der Arbeitsmappe hinzufügen.
Hinweis: Mit dieser Technik wird eine OLAP-basierte Pivot-Tabelle erstellt, die einige Einschränkungen hat, z. B. keine Gruppierung und keine berechneten Felder oder berechneten Elemente. Wenn Sie diese eingeschränkten Funktionen benötigen, probieren Sie stattdessen die Methode „Pivot a Pivot“ aus.
Eine eindeutige Zählung erstellen (Distinct Count)
Um die Pivot-Tabelle zu erstellen, gehen Sie folgendermaßen vor:
- Wählen Sie eine Zelle in der Quelldatentabelle aus.
- Unten im Dialogfeld „Pivot-Tabelle erstellen“ ein Häkchen bei „Diese Daten zum Datenmodell hinzufügen“
- Klicken Sie auf OK
Um das Layout der Pivot-Tabelle einzurichten, gehen Sie folgendermaßen vor:
- Fügen Sie in der Pivot-Tabelle dem Bereich „Zeile“ die Region hinzu.
- Fügen Sie diese 3 Felder zum Bereich „Werte“ hinzu: „Person“, „Einheiten“, „Wert“
- Das Feld „Person“ enthält Text, daher ist es standardmäßig auf „Anzahl der Personen“ eingestellt. Die Zählung zeigt die Gesamtzahl der Transaktionen in jeder Region an, nicht eine eindeutige Zählung der Personen
Um eine eindeutige Zählung der Personen in jeder Region zu erhalten, führen Sie folgende Schritte aus:
- Klicken Sie mit der rechten Maustaste auf einen der Werte im Feld „Person“
- Klicken Sie auf „Wertfeldeinstellungen“
- In der Liste „Wertfeld zusammenfassen nach“ blättern Sie nach unten, und klicken Sie auf „Eindeutige Zählung“ und dann auf „OK“
Das Feld „Person“ ändert sich und zeigt statt der Gesamtzahl der Transaktionen eine eindeutige Zählung der Namen der Verkäufer an.
2. Pivotieren Sie den Pivot – Excel 2010
Um eine eindeutige Zählung der Verkäufer pro Region in Excel 2010 zu erhalten, können wir einen Pivot-Bericht erstellen und dann „Pivotieren Sie den Pivot“, um unser Endergebnis zu erhalten.
Erstellen Sie die erste Pivot-Tabelle
- Erstellen Sie eine Pivot-Tabelle aus diesen Daten, mit Region und Person im Bereich Zeilen
- Fügen Sie Einheiten und Wert im Bereich Werte hinzu. Da es sich bei Person um ein Textfeld handelt, wird es in der Pivot-Tabelle automatisch als „Anzahl von“ angezeigt.
- Formatieren Sie die Pivot-Tabelle mit dem Layout „Tabellarischer Bericht“
- Stellen Sie alle Elementbeschriftungen so ein, dass sie sich in jeder Zeile wiederholen.
- Ändern Sie die Beschriftungen, um die „Summe von“ zu entfernen
TIP: Geben Sie den ursprünglichen Feldnamen ein, mit einem Leerzeichen am Ende, z. B. „Einheiten“
Dies sieht nun wie eine normale Datentabelle aus, ist aber eine Zusammenfassung der ursprünglichen vollständigen Datentabelle mit 4.999 Zeilen.
Erstellen Sie einen benannten Bereich für die Startzelle
Als Nächstes benennen Sie die erste Zelle in der Pivot-Tabelle.
- Markieren Sie die Zelle B2, die die Überschrift in der Region-Spalte der Pivot-Tabelle ist
- Klicken Sie in der Formelleiste und geben Sie einen Namen für die Zelle ein – pvtStart
- Drücken Sie die Eingabetaste, um den Namen zu vervollständigen
Erstellen Sie einen benannten Bereich für die Endzeile
Der nächste Schritt besteht darin, die Position der letzten Zeile in der Pivot-Tabelle zu berechnen. Wir verwenden die MATCH-Funktion mit einer sehr großen Zahl, um die letzte Zahl in Spalte D zu finden und ihre Zeilennummer zu erhalten. Diese Formel funktioniert nur in einer Spalte mit Zahlen.
- Geben Sie im Blatt „Daten“ in Zelle M2 diese Formel ein, um die letzte Zeile in der Spalte „Einheiten“ der Pivot-Tabelle zu berechnen:
=MATCH(9.9E+307,Methode_1!D:D,1)
- Um diese Zelle zu benennen, markieren Sie die Zelle M2, und geben Sie in das Feld „Name“ den Wert „PvtEnd“ ein, und drücken Sie die Eingabetaste.
Erstellen Sie einen dynamischen benannten Bereich
Als Nächstes erstellen wir einen dynamischen benannten Bereich, der sich auf diese neue Tabelle bezieht. Dieser Bereich beginnt in der Zelle mit dem Namen „pvtstart“ (die Zelle mit der Überschrift „Region“) und endet in der letzten Datenzeile in Spalte E (der benannte Bereich, „PvtEnd“).
- Klicken Sie auf der Registerkarte „Formeln“ der Multifunktionsleiste auf „Name definieren“
- Geben Sie im Dialogfeld „Neuer Name“ den Namen „myData“ ein
- Wählen Sie in der Dropdown-Liste „Bereich“ die Option „Arbeitsmappe“ aus
- Geben Sie in das Feld „Bezieht sich auf“ die folgende Formel ein:
=pvtStart:INDEX(Methode_1!$E:$E, PvtEnd) - Klicken Sie auf OK, um den benannten Bereich zu erstellen
Zweite Pivot-Tabelle erstellen
Schließlich erstellen Sie eine zweite Pivot-Tabelle, basierend auf dem dynamischen Bereich myData.
Geben Sie Region in den Zeilenbereich und Person, Einheiten und Wert in den Wertebereich ein
Dies zeigt die Anzahl der eindeutigen Verkäufer pro Region wie unten dargestellt. In der ersten Pivot-Tabelle zeigte die Region Ost beispielsweise 8 eindeutige Namen in der Spalte „Person“, und das ist die Anzahl in dieser Pivot-Tabelle.
Auffrischen der Pivot-Tabellen
Natürlich müssen beide Pivot-Tabellen aufgefrischt werden, da in dieser Lösung 2 Pivot-Tabellen beteiligt sind, nachdem Daten in der Quelltabelle hinzugefügt oder geändert wurden. Es ist wichtig, dass die erste Pivot-Tabelle zuerst aktualisiert wird, gefolgt von einem Refresh der finalen Pivot-Tabelle.
Sie können die Pivot-Tabellen manuell aktualisieren, oder ein Makro verwenden. Um ein Makro zu verwenden, fügen Sie eine der folgenden Prozeduren in das Blatt mit der endgültigen Pivot-Tabelle ein.
A) Wenn sich beide Pivot-Tabellen auf demselben Blatt befinden, verwenden Sie diesen Code:
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub
B) Wenn sich die Pivot-Tabellen auf verschiedenen Blättern befinden, verwenden Sie diesen Code (ersetzen Sie „IhrBlattname“ durch den Namen Ihres aktuellen Blatts):
Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub
(Optional) Benennen Sie den Pivot-Tabellen-Datenbereich
Wenn Sie Makros zum Aktualisieren der Pivot-Tabellen verwenden, können Sie auch ein Makro verwenden, um den benannten Bereich „myData“ nach jeder Aktualisierung zurückzusetzen. Fügen Sie die folgende Prozedur zu Ihrer Arbeitsmappe hinzu und rufen Sie diese Prozedur SetmyData in den Prozeduren Worksheet_Activate auf.
Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub
3. COUNTIF verwenden — Excel 2007 und früher
In früheren Versionen von Excel haben Pivot-Tabellen nicht die Möglichkeit, Zeilenbeschriftungen zu wiederholen. Um eine eindeutige Zählung der Verkäufer pro Region zu erhalten, können Sie eine neue Spalte in den Quelldaten hinzufügen und eine COUNTIF-Formel eingeben
Die Beispieldatei können Sie über den unten stehenden Link herunterladen.
Fügen Sie die COUNTIF-Formel hinzu
Befolgen Sie diese Schritte, um die Formel hinzuzufügen:
Fügen Sie in den Quelldaten eine neue Spaltenüberschrift – „Einzigartig“ – in Zelle J1 hinzu
Geben Sie in Zelle J2 diese Formel ein und kopieren Sie sie in die letzte Datenzeile:
=IF(COUNTIF($E$5:E5,E5)>1,0,1)
Die erste Referenz im Bereich ($E$5) ist absolut, die zweite relativ (E5), so dass sich der Bereich beim Kopieren der Formel nach unten von $E$5:E5 auf $E$5:E6, dann $E$5:E7 und so weiter ausdehnt.
In jeder Zeile prüfen wir, wie oft der Wert in Spalte E (Person) bis zur aktuellen Zeile aufgetreten ist. Wenn er größer als einmal ist, ist das Ergebnis Null, so dass diese Person nicht mehr gezählt wird. So können wir sehen, wie oft der Name einer Person im Datensatz vorkommt.
Mit der zusätzlichen Spalte würden die Daten wie folgt aussehen. Die zweite Instanz von „Harry“ liefert eine Null in der Spalte „Eindeutig“.
Hinweis: Wenn es Personen mit demselben Namen gibt, die in verschiedenen Regionen verkauft werden:
- In Excel 2007 oder höher: Verwenden Sie COUNTIFS, um mehrere Kriterien zu prüfen. Zum Beispiel: =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
- In Excel 2003 oder früher: Verketten Sie die Namen von Region und Person in einer neuen Spalte und verwenden Sie dann die COUNTIF-Formel, um eindeutige Werte in dieser verketteten Spalte zu finden.
Erstellen einer Pivot-Tabelle
Um die Anzahl der eindeutigen Werte in einer Pivot-Tabelle zu sehen:
- Erstellen Sie eine Pivot-Tabelle aus diesen Daten, mit Region im Bereich Zeilen
- Fügen Sie Eindeutig, Einheiten und Wert im Bereich Werte hinzu.
- Ändern Sie die Überschrift „Einzigartig“ in „Anzahl der Personen“ oder „Person“
Wir können sehen, dass es 30 Personen gibt, die den Verkauf gemacht haben, und die Anzahl von jeder, die in jeder Region sind, und wenn wir eine Region erweitern würden, würden wir die individuellen Summen für jede Person sehen.
Leistungsvergleich
Wenn möglich, verwenden Sie die „Pivot a Pivot“-Methode, die viel schneller ist.
COUNTIF-Methode
Die Verwendung einer COUNTIF-Formel in den Quelldaten funktioniert und liefert uns das gewünschte Ergebnis, und bei einem relativ kleinen Datensatz ist die Methode akzeptabel. Aber sie ist sehr kostspielig in Bezug auf die Verarbeitungszeit und kann bei sehr großen Datenmengen extrem langsam sein.
Wenn Sie Charles Williams‘ „FastExcel“ verwenden, um die Zeit zu berechnen, die die Arbeitsmappe benötigt, erhalten Sie das unten gezeigte Ergebnis, bei dem die Zeit für die Neuberechnung des Datenblatts 224 Millisekunden beträgt.
Methode „Pivot a Pivot“
Die Methode „Pivot a Pivot“ liefert ebenfalls das korrekte Ergebnis, mit einer eindeutigen Zählung der Person, wie gefordert. Darüber hinaus benötigt sie weder zusätzliche Spalten in den Quelldaten noch die Eingabe neuer Formeln.
Die resultierende Arbeitsmappe ist kleiner, und auch hier beträgt die Zeit für die Berechnung des Datenblatts unter Verwendung von Charles Williams‘ „FastExcel“ unglaubliche 1.2 Millisekunden
Bei kleinen Datensätzen mag das Timing der Neuberechnung unkritisch sein, aber wenn man es mit größeren Datensätzen von 300.000 bis 400.000 Zeilen zu tun hat, dann werden diese Unterschiede sehr wesentlich.
Power Pivot
Wenn Sie das PowerPivot-Add-In installiert haben, können Sie es verwenden, um eine eindeutige Zählung für ein Feld anzuzeigen. Dieses Video zeigt die Schritte zum Erstellen einer Power Pivot-Pivot-Tabelle und zum Hinzufügen eines Feldes mit der eindeutigen Zählung.
Für eine schriftliche Anleitung siehe die Schritte für Excel 2013 in meinem Pivot-Tabellen-Blog. Es gibt auch eine Anleitung für Excel 2010.
Beispieldatei: Um dem Video zu folgen, laden Sie die Beispieldatei herunter, die für dieses Video verwendet wurde
Holen Sie sich die Beispieldateien
- Datenmodell: Laden Sie die Beispiel-Arbeitsmappe „Datenmodell“ herunter, die das Beispiel für Methode 1 (Datenmodell) enthält. Die Datei ist gezippt und liegt im xlsx-Format vor. Die Datei enthält keine Makros.
- Kein Datenmodell: Laden Sie die Beispielarbeitsmappe ohne Datenmodell herunter, die Beispiele für Methode 2 (Pivot ein Pivot) und Methode 3 (Spalte in Quelldaten) enthält. Die Datei ist gezippt und liegt im xlsb-Format vor. Die Datei enthält keine Makros.
- Power Pivot Video: Zum Nachvollziehen des Power Pivot-Videos in Methode 4, laden Sie die Beispieldatei herunter, die für das Video verwendet wurde
Verwandte Tutorials
Bereiche benennen
Einzigartig in Power Pivot zählen
Beispieldateien – Roger Govier
Über den Entwickler
Roger Govier ist ein Excel MVP mit Sitz in Großbritannien, der Aufträge in Excel und VBA für Kunden weltweit übernimmt. Während er die intellektuelle Herausforderung, Probleme mit Arbeitsblattfunktionen zu lösen, genießt, behauptet Roger, von Natur aus faul zu sein, und sucht daher immer nach einem schnellen und einfachen Weg, um solide praktikable Lösungen zu liefern.
Mehr von Rogers Tutorials und Beispieldateien finden Sie hier: Beispieldateien – Roger Govier
Sie können Roger kontaktieren unter: [email protected]
Roger Govier
