Zählen einzelner Werte in der Excel-Pivot-Tabelle (einfache Schritt-für-Schritt-Anleitung)

Excel-Pivot-Tabellen sind erstaunlich (ich weiß, dass ich das jedes Mal erwähne, wenn ich über Pivot-Tabellen schreibe, aber es ist wahr).

Mit einem grundlegenden Verständnis und ein wenig Drag & Drop können Sie in wenigen Sekunden eine Menge Arbeit erledigen.

Während in Pivot-Tabellen viel mit wenigen Klicks erledigt werden kann, gibt es einige Dinge, die ein paar zusätzliche Schritte oder ein wenig Arbeit erfordern.

Eine solche Möglichkeit besteht darin, verschiedene Werte in einer Pivot-Tabelle zu zählen.

In diesem Tutorial zeige ich Ihnen, wie Sie unterschiedliche Werte sowie eindeutige Werte in einer Excel-Pivot-Tabelle zählen.

Bevor ich mich jedoch mit dem Zählen verschiedener Werte befasse, ist es wichtig, den Unterschied zwischen „eindeutiger Zählung“ und „eindeutiger Zählung“ zu verstehen.

Eindeutige Anzahl vs. eindeutige Anzahl

Während diese wie die gleiche Sache erscheinen mögen, es ist nicht.

Unten ist ein Beispiel, in dem ein Datensatz mit Namen vorhanden ist und ich eindeutige und eindeutige Namen separat aufgelistet habe.

Eindeutige Werte/Namen sind solche, die nur einmal vorkommen. Dies bedeutet, dass alle Namen, die sich wiederholen und Duplikate aufweisen, nicht eindeutig sind. Eindeutige Namen sind in Spalte C im obigen Datensatz aufgeführt

Eindeutige Werte/Namen sind diejenigen, die mindestens einmal im Datensatz vorkommen. Wenn ein Name also dreimal vorkommt, wird er immer noch als ein eindeutiger Name gezählt. Dies kann erreicht werden, indem die doppelten Werte/Namen entfernt und alle unterschiedlichen beibehalten werden. Im obigen Datensatz sind in Spalte B eindeutige Namen aufgeführt.

Basierend auf dem, was ich gesehen habe, meinen die meisten Leute, die sagen, dass sie die eindeutige Anzahl in einer Pivot-Tabelle erhalten möchten, tatsächlich eine eindeutige Anzahl, was ich in diesem Tutorial beschreibe.

Zählen Sie unterschiedliche Werte in der Excel-Pivot-Tabelle

Angenommen, Sie haben die folgenden Verkaufsdaten:

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Nehmen wir an, Sie möchten mit dem obigen Datensatz die Antwort auf die folgenden Fragen finden:

1. Wie viele Vertriebsmitarbeiter gibt es in jeder Region (was nichts anderes ist als die unterschiedliche Anzahl von Vertriebsmitarbeitern in jeder Region)?
2. Wie viele Vertriebsmitarbeiter haben den Drucker in den Jahren 2021-2022 verkauft?

Während Pivot-Tabellen die Daten mit wenigen Klicks sofort zusammenfassen können, müssen Sie einige weitere Schritte ausführen, um die Anzahl der unterschiedlichen Werte zu ermitteln.

Wenn Sie verwenden Excel 2013 oder Versionen danach, gibt es eine eingebaute Funktionalität in Pivot Table, die Ihnen schnell die eindeutige Anzahl liefert. Und wenn Sie verwenden Excel 2010 oder Versionen davor, müssen Sie die Quelldaten ändern, indem Sie eine Hilfsspalte hinzufügen.

Die folgenden zwei Methoden werden in diesem Tutorial behandelt:

• Hinzufügen einer Hilfsspalte im ursprünglichen Datensatz, um eindeutige Werte zu zählen (funktioniert in allen Versionen).
• Hinzufügen der Daten zu einem Datenmodell und Verwenden der Option Distinct Count (verfügbar in Excel 2013 und späteren Versionen).

Es gibt eine dritte Methode, die Roger in diesem Artikel zeigt (die er Pivot the Pivot Table Methode nennt).

Lass uns anfangen!

Hinzufügen einer Hilfsspalte im Datensatz

Hinweis: Wenn Sie Excel 2013 und höhere Versionen verwenden, überspringen Sie diese Methode und wechseln Sie zur nächsten (da sie eine integrierte Pivot-Tabellenfunktion verwendet - Eindeutige Anzahl).

Dies ist eine einfache Möglichkeit, unterschiedliche Werte in der Pivot-Tabelle zu zählen, da Sie den Quelldaten nur eine Hilfsspalte hinzufügen müssen. Nachdem Sie eine Hilfsspalte hinzugefügt haben, können Sie diesen neuen Datensatz verwenden, um die eindeutige Anzahl zu berechnen.

Obwohl dies eine einfache Problemumgehung ist, weist diese Methode einige Nachteile auf (die später in diesem Tutorial behandelt werden).

Lassen Sie mich Ihnen zunächst zeigen, wie Sie eine Hilfsspalte hinzufügen und eine eindeutige Anzahl erhalten.

Angenommen, ich habe den Datensatz wie unten gezeigt:

Fügen Sie die folgende Formel in Spalte F hinzu und wenden Sie sie auf alle Zellen an, die Daten in den angrenzenden Spalten enthalten.

=WENN(ZÄHLENWENN($C$2:C2,C2,$B$2:B2,B2)>1,0,1)

Die obige Formel verwendet die Funktion ZÄHLENWENN, um zu zählen, wie oft ein Name in der angegebenen Region vorkommt. Beachten Sie auch, dass der Kriterienbereich $C$2:C2 und $B$2:B2 ist. Dies bedeutet, dass es sich immer weiter ausdehnt, während Sie in der Spalte nach unten gehen.

In Zelle E2 sind die Kriterienbereiche beispielsweise $C$2:C2 und $B$2:B2 und in Zelle E3 werden diese Bereiche auf $C$2:C3 und $B$2:B3 erweitert.

Dadurch wird sichergestellt, dass die Funktion ZÄHLENWENN die erste Instanz eines Namens als 1 zählt, die zweite Instanz des Namens als 2 usw.

Da wir nur die eindeutigen Namen erhalten möchten, wird die IF-Funktion verwendet, die 1 zurückgibt, wenn ein Name für eine Region zum ersten Mal auftaucht, und 0 zurückgibt, wenn er wieder auftaucht. Dadurch wird sichergestellt, dass nur eindeutige Namen gezählt werden und nicht die Wiederholungen.

Unten sehen Sie, wie Ihr Dataset aussehen würde, wenn Sie die Hilfsspalte hinzugefügt haben.

Nachdem wir die Quelldaten geändert haben, können wir damit eine Pivot-Tabelle erstellen und die Hilfsspalte verwenden, um die eindeutige Anzahl der Vertriebsmitarbeiter in jeder Region zu ermitteln.

Im Folgenden sind die Schritte dazu aufgeführt:

1. Wählen Sie eine beliebige Zelle im Dataset aus.
2. Klicken Sie auf die Registerkarte Einfügen.
3. Klicken Sie auf Pivot-Tabelle (oder verwenden Sie die Tastenkombination - ALT + N + V)
4. Stellen Sie im Dialogfeld Pivot-Tabelle erstellen sicher, dass die Tabelle/der Bereich richtig ist (und die Hilfsspalte enthält) und "Neues Arbeitsblatt" ausgewählt ist.
5. OK klicken.

Die obigen Schritte würden ein neues Blatt einfügen, das die Pivot-Tabelle enthält.

Ziehen Sie das Feld „Region“ in den Zeilenbereich und das Feld „D Count“ in den Wertebereich.

Sie erhalten eine Pivot-Tabelle wie unten gezeigt:

Jetzt können Sie die Spaltenüberschrift von „Summe of D count“ in „Sales Rep“ ändern.

Nachteile der Verwendung einer Hilfsspalte:

Obwohl diese Methode ziemlich einfach ist, muss ich einige Nachteile hervorheben, die mit der Änderung der Quelldaten in einer Pivot-Tabelle einhergehen:

• Die Datenquelle mit der Hilfsspalte ist nicht so dynamisch wie eine Pivot-Tabelle. Während Sie mit einer Pivot-Tabelle die Daten nach Belieben schneiden und würfeln können, verlieren Sie einen Teil dieser Fähigkeit, wenn Sie eine Hilfsspalte verwenden. Angenommen, Sie fügen eine Hilfsspalte hinzu, um die Anzahl eines bestimmten Vertriebsmitarbeiters in jeder Region zu ermitteln. Was ist nun, wenn Sie auch die eindeutige Anzahl der Vertriebsmitarbeiter erhalten möchten, die Drucker verkaufen. Sie müssen zu den Quelldaten zurückkehren und die Hilfsspaltenformel ändern (oder eine neue Hilfsspalte hinzufügen).
• Da Sie der Pivot-Tabellenquelle (die auch dem Pivot-Cache hinzugefügt wird) mehr Daten hinzufügen, kann dies zu einer größeren Excel-Datei führen.
• Da wir eine Excel-Formel verwenden, kann dies Ihre Excel-Arbeitsmappe verlangsamen, falls Sie Tausende von Datenzeilen haben.

Daten zum Datenmodell hinzufügen und mit eindeutiger Anzahl zusammenfassen

Pivot-Tabelle hat in Excel 2013 eine neue Funktionalität hinzugefügt, mit der Sie die eindeutige Anzahl beim Zusammenfassen des Datensatzes abrufen können.

Falls Sie eine frühere Version verwenden, können Sie diese Methode nicht verwenden (wie Sie versuchen sollten, die Hilfsspalte hinzuzufügen, wie in der Methode oben gezeigt).

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Anzahl der einzelnen Vertriebsmitarbeiter in jeder Region ermitteln.

Im Folgenden sind die Schritte aufgeführt, um einen eindeutigen Zählwert in der Pivot-Tabelle zu erhalten:

1. Wählen Sie eine beliebige Zelle im Dataset aus.
2. Klicken Sie auf die Registerkarte Einfügen.
3. Klicken Sie auf Pivot-Tabelle (oder verwenden Sie die Tastenkombination - ALT + N + V)
4. Stellen Sie im Dialogfeld Pivot-Tabelle erstellen sicher, dass die Tabelle/der Bereich richtig ist und dass Neues Arbeitsblatt ausgewählt ist.
5. Aktivieren Sie das Kontrollkästchen "Diese Daten zum Datenmodell hinzufügen"
6. OK klicken.

Die obigen Schritte würden ein neues Blatt einfügen, das die neue Pivot-Tabelle enthält.

Ziehen Sie die Region in den Zeilenbereich und den Vertriebsmitarbeiter in den Wertebereich. Sie erhalten eine Pivot-Tabelle wie unten gezeigt:

Die obige Pivot-Tabelle gibt die Gesamtzahl der Vertriebsmitarbeiter in jeder Region (und nicht die unterschiedliche Zahl) an.

Führen Sie die folgenden Schritte aus, um die eindeutige Anzahl in der Pivot-Tabelle zu erhalten:

1. Klicken Sie mit der rechten Maustaste auf eine beliebige Zelle in der Spalte "Anzahl der Vertriebsmitarbeiter".
2. Klicken Sie auf Wertfeldeinstellungen
3. Wählen Sie im Dialogfeld "Wertfeldeinstellungen" als Berechnungsart "Eindeutige Anzahl" aus (möglicherweise müssen Sie in der Liste nach unten scrollen, um sie zu finden).
4. OK klicken.

Sie werden feststellen, dass sich der Name der Spalte von „Anzahl der Vertriebsmitarbeiter“ in „Eindeutige Anzahl der Vertriebsmitarbeiter“ ändert. Sie können es nach Belieben ändern.

Einige Dinge, die Sie wissen, wenn Sie Ihre Daten zum Datenmodell hinzufügen:

• Wenn Sie Ihre Daten im Datenmodell speichern und dann in einer älteren Excel-Version öffnen, erhalten Sie eine Warnung – „Einige Pivot-Tabellenfunktionen werden nicht gespeichert“. Möglicherweise sehen Sie die eindeutige Anzahl (und das Datenmodell) nicht, wenn Sie in einer älteren Version geöffnet werden, die dies nicht unterstützt.
• Wenn Sie Ihre Daten einem Datenmodell hinzufügen und eine Pivot-Tabelle erstellen, werden die Optionen zum Hinzufügen berechneter Felder und berechneter Spalten nicht angezeigt.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Was ist, wenn Sie eindeutige Werte (und keine eindeutigen Werte) zählen möchten?

Wenn Sie eindeutige Werte zählen möchten, haben Sie keine integrierte Funktionalität in der Pivot-Tabelle und müssen sich nur auf Hilfsspalten verlassen.

Denken Sie daran - Eindeutige Werte und eindeutige Werte sind nicht dasselbe. Klicken Sie hier, um den Unterschied zu erfahren.

Ein Beispiel könnte sein, wenn Sie über den folgenden Datensatz verfügen und herausfinden möchten, wie viele Vertriebsmitarbeiter in jeder Region einzigartig sind. Dies bedeutet, dass sie nur in einer bestimmten Region tätig sind und nicht in den anderen.

In solchen Fällen müssen Sie eine von mehreren Hilfsspalten erstellen.

In diesem Fall funktioniert die folgende Formel:

=WENN(WENN(ZÄHLENWENN($C$2:$C$1001,C2,$B$2:$B$1001,B2)/ZÄHLENWENN($C$2:$C$1001,C2)1,0,1),0)

Die obige Formel prüft, ob ein Vertriebsmitarbeitername nur in einer Region oder in mehr als einer Region vorkommt. Dies geschieht, indem die Anzahl der Vorkommen eines Namens in einer Region gezählt und durch die Gesamtzahl der Vorkommen des Namens dividiert wird. Wenn der Wert kleiner als 1 ist, weist dies darauf hin, dass der Name in zwei oder mehr als zwei Regionen vorkommt.

Falls der Name in mehr als einer Region vorkommt, wird eine 0 zurückgegeben, ansonsten eine Eins.

Die Formel prüft auch, ob der Name in derselben Region wiederholt wird oder nicht. Wenn der Name wiederholt wird, gibt nur die erste Instanz des Namens den Wert 1 zurück und alle anderen Instanzen geben 0 zurück.

Dies mag etwas komplex erscheinen, aber es hängt wiederum davon ab, was Sie erreichen möchten.

Wenn Sie also eindeutige Werte in einer Pivot-Tabelle zählen möchten, verwenden Sie Hilfsspalten, und wenn Sie unterschiedliche Werte zählen möchten, können Sie die integrierte Funktionalität (in Excel 2013 und höher) oder eine Hilfsspalte verwenden.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Möglicherweise gefallen Ihnen auch die folgenden Pivot-Tabellen-Tutorials:

• So filtern Sie Daten in einer Pivot-Tabelle in Excel
• So gruppieren Sie Datumsangaben in Pivot-Tabellen in Excel
• So gruppieren Sie Zahlen in der Pivot-Tabelle in Excel
• So wenden Sie die bedingte Formatierung in einer Pivot-Tabelle in Excel an
• Datenschnitte in Excel-Pivot-Tabelle
• So aktualisieren Sie die Pivot-Tabelle in Excel
• Löschen einer Pivot-Tabelle in Excel