Für jeden, der sich mit Datenanalyse in Excel beschäftigt, stellt die Berechnung eindeutiger Werte eine häufige Herausforderung dar. Insbesondere wenn es darum geht, doppelte Einträge zu vermeiden und nur die tatsächlich unterschiedlichen Datensätze zu zählen, sind herkömmliche Summen oft nicht ausreichend. In diesem Artikel beleuchten wir zwei effektive Methoden, um eine eindeutige Zählung mithilfe von Pivot-Tabellen zu realisieren. Diese Techniken wurden als Lösungen für eine Datenanalyse-Herausforderung entwickelt, bei der es darum ging, eine Zusammenfassung der abgeschlossenen Geschäftsfälle nach deren Status zu erstellen, wobei jede einzelne Geschäftsfall in den CRM-Daten mehrfach vorkommen konnte. Das Ziel war es, eine eindeutige Zählung zu erhalten, um diese korrekt summieren zu können.
Der Kern dieser Herausforderung liegt im Umgang mit mehrfach vorhandenen Datensätzen pro eindeutiger Kennung, wie z.B. einer Deal-ID. Wir zeigen Ihnen, wie Sie sowohl mit älteren als auch mit neueren Excel-Versionen zu präzisen Ergebnissen kommen. Wenn Sie tiefer in die Welt der Pivot-Tabellen eintauchen möchten, finden Sie hier weiterführende Informationen: Introduction to Pivot Tables and Dashboards.
Methode 1: Verwendung einer Hilfsspalte
Diese Methode zeichnet sich dadurch aus, dass sie in jeder Excel-Version anwendbar ist, was sie zu einer universellen Lösung macht.
Zunächst sollten Ihre Daten als Excel-Tabelle formatiert sein. Wählen Sie dazu einfach eine beliebige Zelle in Ihrem Datensatz aus und klicken Sie auf der Registerkarte “Start” auf “Als Tabelle formatieren”. Wählen Sie das gewünschte Tabellenformat aus.
Daten als Excel-Tabelle formatierenBestätigen Sie die “Als Tabelle formatieren”-Dialogbox mit “OK”.
Nachdem Ihre Daten nun im Tabellenformat vorliegen, fügen Sie eine Hilfsspalte rechts neben der Tabelle hinzu und benennen Sie diese “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 aktuellen Zeile entspricht.
COUNTIF-Funktion erklärtDie Formel gibt die **Anzahl der Zeilen** für jede Deal-ID zurück. Indem wir diese Formel durch die Zahl 1 teilen, erhalten wir in jeder Zelle Bruchteile, die sich, wenn sie **addiert** werden, zu **einer Zählung pro Deal** summieren.
Die Anpassung der Formel sieht wie folgt aus:
=1/COUNTIF([Deal ID];[@[Deal ID]])
Brüche für jeden Datensatz summieren sich zu 1Nachdem wir diese Brüche erstellt haben, die bei der Erstellung unserer Pivot-Tabelle eine **eindeutige Zählung** ergeben, können wir nun mit der **Erstellung der Pivot-Tabelle** fortfahren, indem wir auf der Registerkarte “Einfügen” die Option “PivotTable” wählen.
Um unseren Zusammenfassungsbericht mithilfe der neuen Pivot-Tabelle zu erstellen, ziehen Sie “Sales Stage” in den Bereich “Zeilen” und “Deal Count” in den Bereich “Summe von Werten”.
PivotTable-Felder FensterDies liefert uns den gewünschten zusammenfassenden Bericht mit der Anzahl der **Deals in jeder Verkaufsphase**.
Zusammenfassender BerichtEin großer Vorteil der Verwendung von Pivot-Tabellen ist, dass wir bei **Hinzufügen oder Löschen von Quelldateneinträgen** die Pivot-Tabelle einfach **aktualisieren** können (Alt + F5), um diese Änderungen zu berücksichtigen.
Methode 2: Verwendung von Power Pivot
Diese Lösung ist nur für Excel-Versionen ab 2013 für Windows verfügbar.
Auch hier ist es wichtig, dass Ihre Daten als Excel-Tabelle formatiert sind. Eine Hilfsspalte wird für diese Methode jedoch nicht benötigt.
Beim Erstellen unserer Pivot-Tabelle aktivieren wir diesmal das Kontrollkästchen “Diese Tabelle dem Datenmodell hinzufügen”. (Datenmodell ist ein anderer Begriff für Power Pivot.)
PivotTable-ErstellungsfensterBeim Aufbau Ihrer Pivot-Tabelle ziehen Sie die **Deal ID** in den Bereich “Summe von Werten”.
Anfänglich erhalten wir Zahlen, die wir für unseren Zusammenfassungsbericht nicht benötigen. Um dies zu korrigieren, klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift “Summe von Deal ID” und wählen Sie “Wertfeldeinstellungen”. Daraufhin öffnet sich ein Fenster, in dem wir “Eindeutige Anzahl” als Berechnungstyp auswählen können.
Wertfeldeinstellungen Eindeutige AnzahlDie Funktion **Eindeutige Anzahl** durchläuft die Spalte “Deal ID” und liefert uns eine Zählung der **eindeutigen Werte**, sodass unser Zusammenfassungsbericht genauso aussieht wie bei Methode 1.
Vergleich der beiden Lösungen
Beide vorgestellten Lösungen sind vorteilhaft, da sie bei Hinzufügen neuer Daten zur Quelltabelle aktualisiert werden können.
Der Vorteil von Methode 1 liegt darin, dass sie in jeder Excel-Version durchgeführt werden kann. Wenn Sie jedoch Excel 2013 oder neuer unter Windows verwenden, ist Methode 2 die überlegene Option. Dies liegt daran, dass Methode 1 bei Filtern der Daten (z.B. für ein bestimmtes Produkt) oder der Verwendung von Slicern zur weiteren Datenaufschlüsselung Probleme bereiten kann.
Wenn Sie mehr über die Verwendung von Pivot-Tabellen erfahren möchten, finden Sie in einem separaten Blogbeitrag weitere Informationen: Introduction to Pivot Tables and Dashboards.
Weitere Lösungsansätze
Es gab zahlreiche weitere großartige Lösungen für die Herausforderung, darunter auch solche, die Power Query und neue dynamische Funktionen nutzten. Diese werden wir in zukünftigen Beiträgen genauer betrachten. Wir wollten jedoch mit diesen beiden beginnen, da sie in Bezug auf die Kompatibilität mit verschiedenen Excel-Versionen universeller sind.
Wenn Sie Fragen zu einer dieser Lösungen haben, hinterlassen Sie bitte einen Kommentar unten!