Wenn du diesen Blog schon länger liest, weißt du, dass ich ein großer Fan von Pivot-Tabellen bin. Sie sind perfekt, um mit minimalem Aufwand größte Datenmengen auszuwerten und zu verdichten. In manchen Fällen braucht man jedoch nur einen einzelnen Wert aus einer Pivot-Tabelle, um diesen beispielsweise in einem Management-Dashboard darzustellen.
Wie man ganz gezielt, flexibel und zuverlässig auf einen bestimmten Datenpunkt zugreift, zeige ich dir in diesem Artikel.
Und so geht’s:
Die Ausgangslage
Ich habe eine umfangreiche Auftragstabelle eines fiktiven Unternehmens mit einer Pivot-Tabelle ausgewertet, in der die Verkaufszahlen (Mengen und Umsätze) nach Warengruppe, Jahren und Monaten verdichtet werden:
Die Pivot-Tabelle
(Die Beispieldatei kannst du dir hier herunterladen)
Nehmen wir nun an, ich möchte mir ganz gezielt einen bestimmten Wert aus dieser Pivot-Tabelle an einer anderen Stelle ausgeben lassen. Dies kann zum Beispiel für ein Dashboard notwendig sein, in dem eben nicht alle Daten dargestellt werden sollen. Ich tippe dazu in einer leeren Zelle das Gleichheitszeichen und klicke danach mit der Maus auf den gewünschten Wert in der Pivot-Tabelle. Abhängig von den Einstellungen in der Arbeitsmappe kann jetzt zweierlei passieren:
In der Zelle wird die Adresse der ausgewählten Pivot-Zelle angezeigt:
Variante 1: Ein fester Zellbezug
Oder es erscheint eine kryptische Funktion namens PIVOTDATENZUORDNEN:
Variante 2: Die Funktion PIVOTDATENZUORDNEN
Sollte bei dir nur die normale Zelladresse angezeigt werden, dann setze jetzt in den Excel-Optionen in der Kategorie „Formeln“ die Einstellung „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“:
Funktion in den Excel-Optionen aktivieren
Diese sperrige Option führt dazu, dass in Zukunft bei einem Bezug auf ein Feld in einer Pivot-Tabelle automatisch die Funktion PIVOTDATENZUORDNEN aus dem Bild oben eingesetzt wird. Aber wozu sollte das gut sein? Die normale Zelladresse ist doch viel einfacher zu lesen!
Stimmt. Aber wie du weißt, sind Pivot-Tabellen dynamisch und können bei Änderung der Quelldaten wachsen oder schrumpfen. Stell dir nur vor, es kommt plötzlich eine neue Warengruppe dazu, so dass sich alle Zeilen verschieben und schon verweist der normale Zellbezug auf einen falschen Wert. Und du würdest es vielleicht nicht einmal sofort merken! Und genau hier spielt die neue Funktion ihre Stärken aus.
Die PIVOTDATENZUORDNEN-Funktion
So sieht die allgemeine Syntax dieser etwas unhandlichen Funktion aus (in englischen Excel-Versionen lautet der Funktionsname übrigens GETPIVOTDATA):
=PIVOTDATENZUORDNEN(Datenfeld;PivotTable;[Feld1;Element1;Feld2;Element2;...])
Wie sieht das in unserem Eingangsbeispiel aus?
=PIVOTDATENZUORDNEN("Mengen";$A$1;"Datum";2;"Warengruppe";11;"Jahre";2019)
Mit dem ersten Parameter wird also das Wertefeld festgelegt, das ich auswerten möchte. In meinem Beispiel als „Mengen“.
Der zweite Parameter $A$1 verweist auf die erste Zelle der Pivot-Tabelle, die bei mir eben in A1 beginnt.
Hinweis:
Hier kann tatsächlich nur ein fixer Zellbezug verwendet werden. Der Versuch, stattdessen auf den Namen der Pivot-Tabelle zu verweisen, führt zu einem #NAME? oder einem #BEZUG!-Fehler, je nachdem, ob man Anführungszeichen verwendet oder nicht:
Pivot-Tabellen-Name kann nicht verwendet werden
Danach folgen Pärchen aus Feldname und Feldelement. Je nach Aufbau der Pivot-Tabelle können das auch viele solcher Kombinationen werden, um einen ganz bestimmten Wert zu definieren. In meinem Beispiel sind das
- „Datum“ als erstes Feld und der Wert 2 für den Monat Februar
- „Warengruppe“ als zweites Feld und der Wert 11 für die entsprechende Warengruppe
- „Jahre“ und der Wert 2019 für das entsprechende Jahr
Damit greife ich also ganz gezielt auf einen bestimmten Datenpunkt meiner Pivot-Tabelle zu.
Vorsicht Falle!
Als Feldnamen sind immer die in der Pivot-Tabelle angezeigten Namen zu verwenden, die sich von den Original-Feldnamen unterscheiden können! Laut Feldliste heißen bei mir die Original-Felder „Menge“ und „Umsatz“, in der Pivot-Tabelle habe ich sie aber umbenannt in „Mengen“ und „Umsätze“. Daher muss ich auch letztere in der Funktion verwenden.
Angezeigte Feldnamen sind relevant
Wenn ich nun eine andere Warengruppe, einen anderen Monat oder ein anderes Jahr auswerten möchte, muss ich die Werte an der entsprechenden Position innerhalb der PIVOTDATENZUORDNEN-Funktion anpassen, da diese dort fix eingetragen sind. Dies ist jedoch ganz offensichtlich umständlich und fehleranfällig, daher zeige ich dir jetzt eine flexiblere Variante.
PIVOTDATENZUORDNEN mit flexiblen Bezügen
Zuerst lege ich in den Zellen B28:B30 für jedes der drei benötigten Felder eine Eingabezelle fest. Die Feldbezeichnungen in Spalte A sind dabei nicht so wichtig, sie dienen nur der Orientierung:
Variable Eingabefelder
In das Ergebnisfeld in Zelle B30 trage ich zunächst wieder wie bisher durch Anklicken des Werts gewünschten in der Pivot-Tabelle die statische PIVOTDATENZUORDNEN-Funktion ein.
Und jetzt werden die Elemente-Parameter einfach durch die Zellbezüge der Eingabefelder ersetzt:
Dynamisierung der Funktion
Sobald ich einen oder mehrere Werte in meinem Eingabebereich ändere, passt sich automatisch der Ergebniswert in B32 an. Ist doch gleich viel besser, oder?
Die Kür: Ein anderes Wertefeld
Bisher haben wir das Wertefeld „Mengen“ ausgewertet. Was ist, wenn ich stattdessen das zweite Wertefeld „Umsätze“ bräuchte? Die naheliegende Antwort wäre:
Ein weiteres Eingabefeld und ein Zellbezug darauf. Wie man jedoch im folgenden Bild sieht, führt das zu einem #BEZUG!-Fehler:
Direkter Bezug führt zu einem Fehler
Die PIVOTDATENZUORDNEN-Funktion erwartet das Wertefeld zwingend in Anführungszeichen. Leider hilft es auch nichts, im Eingabefeld Anführungszeichen zu verwenden:
Auch Anführungszeichen helfen nicht
Muss man also hier auf die Flexibilität verzichten? Nein, es gibt eine Lösung!
Man verwendet zusätzlich die TEXT-Funktion und erhält dadurch das gewünschte Ergebnis:
TEXT(B31;"")
Lösung: Die TEXT-Funktion
Zugegeben, nicht so ganz nachvollziehbar, aber zielführend.
Und jetzt kannst du auch im Wertefeld flexibel zwischen „Umsätze“ und „Menge“ umschalten (am besten noch mit einem Dropdown-Feld) und bekommst das entsprechende Resultat.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Danke!
Man kann gerne und oft mit Pivottabellen arbeiten, doch vor diesen flexiblen Zugriffen hat man – sagen wir mal – Respekt.
Hallo Hannes,
ein gesunder Respekt im Umgang mit großen Datenmengen ist nie schädlich. Vertrauen ist gut, Kontrolle ist manchmal besser 🙂
Schöne Grüße,
Martin
Hallo Martin, ich bin seit langer Zeit ein begeisterter Anhänger Deines Wissens. Ich hätte gern mal ein Problem: In meiner etwas größeren Datei (ca. 9,5 MB) existieren zwei Pivot-Tabellen, die bis neulich prima funktioniert haben. Jetzt taucht beim Öffnen der Mappe (und innerhalb der Mappe auf dem betroffenen Tabellenblatt) eine Fehlermeldung auf: „Excel konnte die PivotTable mit dem Namen PivotTable2 nicht aktualisieren, weil es eine andere PivotTable überlappen würde.“ Nachdem die Meldung mit OK bestätigt wurde ist die betreffende PivotTable2 korrekt aktualisiert, die daneben bestehende PivotTable1 aktualisiert nicht (bzw. nicht alles), ein Datensatz in Table1 wird permanent nicht angezeigt und daher auch nicht aktualisiert. Habe die zugrunde liegende Dateiliste mehrfach kontrolliert – scheint alles ok zu sein. Da bin ich mit meinem Latein am Ende. Für einen Tipp bin ich ausgesprochen dankbar!
Freundliche Grüße aus Herborn! Hans (temporär gefrustet!)
Hallo Hans-Georg,
das Fehlermeldung deutet eher darauf hin, dass eine der Pivot-Tabellen nicht genügend Platz hat. Vielleicht hat sich doch ein Datensatz eingeschlichen, der zu einer zusätzlichen Spalte in der Pivot führen würde und daher diesen Fehler auslöst. Füge doch einfach mal einen Schwung leerer Spalten zwischen den beiden Pivot-Tabellen ein, vielleicht hilft das.
Schöne Grüße,
Martin
Hallo Martin,
Dein Hinwies „Hier kann tatsächlich nur ein fixer Zellbezug verwendet werden.“ ist zwar sachlich in Hinblick auf die geforderter Art (=Zellbezug) korrekt, aber nicht unbedingt in Hinblick auf das „fix“. Auch dieser lässt sich dynamisieren, ähnlich dem gesuchten Wertfeld, z.B.: … mit WENN & zwei bzw. mehreren „fixen“ Zellbezüge (Pivotadressen) … oder einem INDIREKT … jeweils in Kombination mit (einer) ausgelagerten Bedingung bzw. Zelladresse.
Beste Grüße, Christian
Hallo Christian,
ja, du hast Recht, es lässt sich natürlich fast immer irgendwie eine Dynamik einbauen. Danke für die ergänzenden Hinweise.
Schöne Grüße,
Martin
Hallo,
vielen Dank für die gute Zusammenfassung! Ich habe folgende Frage: Ist es möglich, auch solche Werte aus einer Pivot-Tabelle zu extrahieren, die gerade nicht sichtbar sind, weil ein Filter gesetzt ist? Bzw. kann ich in der o.g. Funktion angeben, welcher Filter zur Auswahl kommen soll, um die Daten zu ziehen?
Mein Beispiel: Ich habe Umsatzdaten von mehreren Standorten in eine Pivottabelle gebracht. Der Standort ist ein Filter auf der Pivottabelle. Ich mächte nun auf einem Dashboard den Umsatz eines bestimmten Monats für einen bestimmten Standort ausgeben. Bislang gelingt mir das nur, indem ich den Filter der Pivottabelle manuell auf diesen Standort setze. Dann stimmen aber natürlich die Dashboardwerte für die anderen Standorte nicht mehr, da nun natürlich alle Formeln den Umsatz für den gefilterten Standort extrahieren.
Danke!
Hallo Sebastian,
mit der Funktion PIVOTDATENZUORDNEN lassen sich wirklich nur Werte extrahieren, die in der Pivot-Tabelle auch angezeigt werden. Das heißt, du solltest in der Pivot-Tabelle keine Filterfelder verwenden, sondern stattdessen alle benötigten Felder im Zeilenbereich oder Spaltenbereich einbauen. Und danach kannst du die PIVOTDATENZUORDNEN-Funktion genau so verwenden, wie oben im Artikel beschrieben ist.
Schöne Grüße,
Martin