Excel-TippDie Funktion PIVOTDATENZUORDNEN()
Mit der Funktion PIVOTDATENZUORDNEN() können Sie Daten aus einer bestehenden Pivot-Tabelle basierend auf der aktuellen Tabellenstruktur abfragen. Das Interessante an PIVOTDATENZUORDNEN() ist die Tatsache, dass Sie hier Daten durch Angabe von Pivot-Feldern anstelle von Zellreferenzen abrufen.
Dies hat den Vorteil, dass die Ergebnisse nicht abhängig von einer bestimmten Zellposition in der Tabelle 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
Schauen Sie sich dies an einem Beispiel an. Sie sehen in der folgenden Abbildung eine Pivot-Tabelle, welche die Umsätze pro Verkäufer und Artikelpreis darstellt.
Es soll der Gesamtumsatz für den Mitarbeiter Max Meyer in eine andere Zelle übernommen werden. Erfassen Sie hierzu mit der Tastatur in die entsprechende Zelle den Bezug =L4. Der Wert wird daraufhin, wie gewohnt, in die entsprechende Zelle übernommen.
Klicken Sie jetzt auf dem 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 darstellt.
Korrekte Auswahl eines Werts mit der Funktion PIVOTDATENZUORDNEN()
Entfernen Sie den Filter wieder in den Zeilenbeschriftungen und 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 auf eine Wertzelle in einer Pivot-Tabelle mit der linken Maustaste 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 die 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. Bitte erfassen Sie den Namen (Text) 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/Elementpaar, 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.
