Datenanalyse in ExcelPIVOTDATENZUORDNEN() – Datenanalyse optimieren mit Excel

Mit der Funktion PIVOTDATENZUORDNEN() können Sie Daten aus einer bestehenden Pivot-Tabelle basierend auf der aktuellen Tabellenstruktur abfragen.

Das Interessante an PIVOTDATENZUORDNEN() ist, dass Sie hier Daten durch Angabe von Pivot-Feldern anstelle von Zellreferenzen abrufen.

Dies hat den Vorteil, dass die Ergebnisse nicht von einer bestimmten Zellposition in der Tabelle abhängig sind.

Werden die Daten in der Pivot-Tabelle anders angeordnet, indem zum Beispiel die Zeilen und Spalten getauscht werden oder weitere Einträge hinzukommen, dann bleibt das Ergebnis der Funktion PIVOTDATENZUORDNEN() trotzdem erhalten.

Beispiel mit problematischer Auswahl eines Werts aus einer Pivot-Tabelle

Das folgende Beispiel soll zeigen, warum ein einfacher Verweis auf eine Zelle einer Pivot-Tabelle problematisch sein kann.

Sie sehen in der folgenden Abbildung eine Pivot-Tabelle, in der die Verkäufer (Zeilenbeschriftungen), die jeweiligen Artikelpreise (Spaltenbeschriftungen) sowie die Umsätze pro Verkäufer (Werte in der Pivot-Tabelle) dargestellt sind.

Es soll der Gesamtumsatz aus Spalte L der Pivot-Tabelle für den Mitarbeiter Max Meyer in Zeile 4 in eine andere Zelle (C10) übernommen werden.

Tragen Sie hierzu in die Zelle C10 ein:

=L4

Der Wert wird daraufhin, wie gewohnt, in die entsprechende Zelle übernommen.

Klicken Sie auf das Filtersymbol bei der Zeilenbeschriftung und deaktivieren das Kontrollkästchen für den Verkäufer Ingo Geier.

Durch das Deaktivieren des Kontrollkästchens wird die Pivot-Tabelle jetzt verkürzt dargestellt. Die Formel mit der „normalen“ Zellreferenz liefert weiterhin den Wert aus der Zelle L4, die jetzt aber nicht mehr den Gesamtumsatz des Verkäufers Max Meyer beinhaltet.

Korrekte Auswahl eines Werts mit der Funktion PIVOTDATENZUORDNEN()

Entfernen Sie den Filter wieder in den Zeilenbeschriftungen.

Erfassen Sie nun in einer anderen Zelle ein =, um Excel zu signalisieren, dass Sie eine Formel erstellen wollen.

Klicken Sie dann mit der linken Maustaste auf den Gesamtumsatz des Verkäufers Max Meyer. Excel erstellt jetzt automatisch eine Formel mit der Funktion PIVOTDATENZUORDEN().

Die PIVOTDATENZUORDNEN-Funktion wird automatisch generiert, wenn Sie mit der linken Maustaste auf eine Wertzelle in einer Pivot-Tabelle klicken.

Bestätigen Sie die Eingabe der Formel, indem Sie die Enter-Taste drücken. Analog zum „normalen“ Zellbezug wird jetzt der richtige Wert in der Ergebniszelle dargestellt. Im Beispiel der Wert 60.353.

Filtern Sie jetzt wieder die Liste wie im ersten Versuch, indem Sie den Verkäufer Ingo Geier nicht in der Pivot-Tabelle anzeigen lassen. Dann können Sie den großen Vorteil der Funktion PIVOTDATENZUORDNEN() erkennen.

Trotz Filterung der Liste wird mit der Funktion PIVOTDATENZUORDNEN() der richtige Wert in der Zelle dargestellt.

Was die Funktion PIVOTDATENZUORDNUNG() leistet

Warum auch bei einer Veränderung der Pivot-Tabelle das Ergebnis korrekt bleibt, erkennen Sie, wenn Sie sich die Syntax von PIVOTDATENZUORDNEN() genauer anschauen:

=PIVOTDATENZUORDNEN(Datenfeld; PivotTable; [Feld1; Element1]; [Feld2; Element2]; …)

• Datenfeld: Der Name des abzufragenden Wertefelds in der Pivot-Tabelle. Dies ist im Beispiel der Name „Wert“ für die Zahlen in Spalte L mit dem Gesamtergebnis. Erfassen Sie ein Textformat immer in Anführungszeichen.
• PivotTable: Ein Verweis auf eine Zelle in der Pivot-Tabelle, aus der die Daten abgefragt werden sollen. In der Regel ist das immer die erste Zelle der entsprechenden Pivot-Tabelle; im Beispiel ist dies die Zelle A1.
• Feld1; Element1 (optional): Ein Feld/Element-Paar. Hier definieren Sie die Filterkriterien aus den Feldnamen der Pivot-Tabelle und dem jeweiligen Filterkriterium. Im Beispiel sind dies die Werte „Verkäufer“ (Feld) und „Max Meyer“ (Kriterium).

Wichtig: Sie können mehrere Feld/Element-Paare hintereinander erfassen, um noch weitere Eingrenzungen vornehmen zu können.

Definieren Sie kein Feld/Element-Paar, dann wird keine weitere Eingrenzung vorgenommen und es wird die Summe des Datenfeldes (im Beispiel der Gesamtumsatz aller Verkäufer in Zelle L7 bzw. L6 nach der Filterung) zurückgegeben.

Die entsprechenden Möglichkeiten sind in der folgenden Abbildung dargestellt. Sie erkennen, welche Parameter in der Funktion PIVOTDATENZUORDNUNG() jeweils angegeben sind.

Wichtig: Ist der Zeilen- oder Spaltentitel eine Zahl, wird diese in der Formel ohne doppelte Anführungszeichen ("") eingegeben; siehe im Beispiel "Preis";88.