2 Wege zur Berechnung der eindeutigen Zählung mit Pivot-Tabellen

Unterm Strich: Lernen Sie zwei Wege kennen, um die Herausforderung der Datenanalyse, die Berechnung der eindeutigen Anzahl, mit Pivot-Tabellen zu lösen.

Skill Level: Mittelstufe

Video-Tutorial

Download der Excel-Datei

Ich habe hier sowohl die Originaldatei als auch die Lösungsdatei für Sie zum Download bereitgestellt:

Datenanalyse Herausforderung.xlsx (16.1 KB)

Datenanalyse-Herausforderung – Pivot-Tabelle Lösung FINAL.xlsx (118.4 KB)

Zählen von eindeutigen Zeilen

In diesem Beitrag werden wir uns zwei verschiedene Möglichkeiten ansehen, eine eindeutige Zählung mithilfe von Pivot-Tabellen durchzuführen. Diese beiden Methoden wurden als Lösungen für die Datenanalyse-Herausforderung eingereicht, die Sie hier finden können:

Excel-Datenanalyse-Herausforderung

Zur Zusammenfassung der Herausforderung: Wir möchten einen zusammenfassenden Bericht über die Anzahl der Geschäftsabschlüsse nach Stufe erstellen, aber es gibt mehrere Zeilen pro Geschäft in den CRM-Daten. Wir müssen also einen Weg finden, eine eindeutige Zählung (mit eindeutigen Zeilen) für jedes Geschäft zu erstellen, damit wir sie zusammenfassen können.

Danke übrigens an alle, die eine Lösung für die Data Challenge eingereicht haben! Es gab viele tolle Einsendungen.

Lösung 1 – Verwendung einer Hilfsspalte

Das Tolle an dieser Lösung ist, dass sie in jeder Version von Excel verwendet werden kann.

Starten Sie, indem Sie Ihre Daten in eine Excel-Tabelle umwandeln. Markieren Sie dazu einfach eine beliebige Zelle im Datensatz und klicken Sie auf der Registerkarte Start auf Als Tabelle formatieren. Klicken Sie mit der rechten Maustaste auf das gewünschte Tabellenformat und wählen Sie Anwenden und Formatierung löschen.

Wenn das Fenster Als Tabelle formatieren erscheint, klicken Sie auf OK.

Nun, da Ihre Daten im Tabellenformat vorliegen, fügen Sie eine Hilfsspalte rechts von der Tabelle hinzu und beschriften sie mit Deal Count. Verwenden Sie die Funktion COUNTIF, wobei der Bereich die Spalte „Deal ID“ und das Kriterium die Zelle in der Spalte „Deal ID“ ist, die der Zeile entspricht, in der Sie sich befinden.

Die Formel gibt die Anzahl der Zeilen für jede Deal ID-Nummer zurück. Wenn wir die Formel durch die Zahl 1 teilen, erhalten wir in jeder dieser Zellen Brüche, die, wenn sie addiert werden, einen Eintrag für jeden Deal zählen.

Die Änderung der Formel ist hier in grün zu sehen:

=1/COUNTIF(,])

Nun, da wir diese Brüche haben, die uns eine eindeutige Zählung geben, wenn wir unsere Pivot-Tabelle erstellen, können wir fortfahren und die Pivot-Tabelle erstellen, indem wir Pivot-Tabelle auf der Registerkarte Einfügen wählen.

Um unseren zusammenfassenden Bericht mit der neuen Pivot-Tabelle zu erstellen, setzen Sie die Verkaufsstufe in den Bereich „Zeilen“ und die Anzahl der Geschäfte in den Bereich „Summe der Werte“.

So erhalten wir den gewünschten zusammenfassenden Bericht mit der Anzahl der Geschäfte in jeder Verkaufsstufe.

Das Schöne an der Verwendung einer Pivot-Tabelle ist, dass wir beim Hinzufügen oder Löschen von Quelldateneinträgen die Pivot-Tabelle aktualisieren können ( Alt + F5 ), um diese Änderungen zu berücksichtigen.

Lösung Nr. 2 – Power Pivot verwenden

Diese Lösung ist nur für Excel-Versionen ab 2013 für Windows verfügbar.

Wir wollen unsere Daten immer noch als Excel-Tabelle formatieren, aber wir brauchen keine Hilfsspalte für diese Lösung.

Diesmal werden wir beim Erstellen unserer Pivot-Tabelle das Kontrollkästchen „Diese Tabelle zum Datenmodell hinzufügen“ aktivieren. (Datenmodell ist ein anderer Begriff für PowerPivot.)

Wenn Sie diesmal Ihre Pivot-Tabelle erstellen, ziehen Sie die Deal-ID in den Bereich Summe der Werte.

Dadurch erhalten wir zunächst Zahlen, die wir in unserem zusammenfassenden Bericht nicht haben wollen. Um das zu beheben, klicken wir mit der rechten Maustaste auf die Spaltenüberschrift Summe der Deal-ID und wählen Wertefeldeinstellungen. Dadurch öffnet sich ein Fenster, in dem wir Distinct Count als Berechnungstyp auswählen können.

Die Funktion „Eindeutige Zählung“ durchläuft die Spalte „Deal ID“ und liefert uns eine Zählung der eindeutigen Werte, so dass unser zusammenfassender Bericht genauso aussieht wie bei Lösung 1.

Vergleich der beiden Lösungen

Beide dieser Lösungen sind großartig, weil sie aktualisiert werden können, wenn neue Daten zur Quelltabelle hinzugefügt werden.

Der Vorteil von Lösung 1 ist, dass sie in jeder Version von Excel ausgeführt werden kann. Wenn Sie jedoch 2013 oder eine neuere Version unter Windows verwenden, ist Lösung Nr. 2 die bessere Option. Das liegt daran, dass Lösung 1 nicht mehr funktioniert, wenn Sie versuchen, die Daten nach unten zu filtern (z. B. für ein bestimmtes Produkt) oder Slicer zu verwenden, um die Daten weiter zu zerlegen.

Wenn Sie mehr über die Verwendung von Pivot-Tabellen erfahren möchten, habe ich einen separaten Blog-Beitrag, den Sie hier nachlesen können: Einführung in Pivot-Tabellen und Dashboards.

Andere Lösungen

Es gab viele andere tolle Lösungen für die Challenge, die eingereicht wurden. Sie beinhalteten die Verwendung von Power Query und neuen dynamischen Funktionen. Wir werden uns diese in zukünftigen Beiträgen ansehen, aber ich wollte mit diesen beiden beginnen, weil sie in Bezug auf den Zugriff auf Excel-Versionen universeller waren.