Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen 20

Mit der Funktion PIVOTDATENZUORDNEN kannst du gezielt Ergebnisse aus deiner Pivot-Tabelle herauspicken und weiterverarbeiten.
 

Wie man zu Analysezwecken seine Daten mit Hilfe von Pivot-Tabellen verdichtet, habe ich schon in den Artikeln Der beste Freund des Excel-Analysten: Pivot-Tabellen und Der Horror geht weiter: Pivot-Tabellen Teil 2 gezeigt.

Wie greift man jetzt aber gezielt auf einzelne Ergebnisse innerhalb einer Pivot-Tabelle zu, um diese woanders weiterzuverarbeiten? (Und warum sollte man so etwas überhaupt wollen?)

Hier bekommst du die Antworten darauf:

Die Ausgangslage

Zur Veranschaulichung habe ich eine Pivot-Tabelle mit Umsatzzahlen nach Region, Verkäufer und Produkten erstellt:

Beispiel-Tabelle

Beispiel-Tabelle

Du kannst die Tabelle hier herunterladen.

Aus dieser Pivot-Tabelle werden wir nun über eine Excel-Funktion gezielt einzelne Ergebnisse ansprechen.
Wozu man dafür eine spezielle Funktion benutzen soll und nicht nur einen einfachen Zellenbezug?

Nun: Der Zellenbezug funktioniert gut, solange die Pivottabelle nicht verändert wird. Findet jedoch eine Neuberechnung statt, weil die zugrunde liegenden Daten aktualisiert wurden, verweist der Zellenbezug nicht mehr auf die korrekte Stelle.

Und genau hier setzt die Spezialfunktion an.

Die Funktion

Diese tolle Funktion trägt den noch tolleren Namen PIVOTDATENZUORDNEN.

Die Syntax sieht folgendermaßen aus:
=PIVOTDATENZUORDNEN(Datenfeld;Pivotabelle;[Feld1;Wert1];[Feld2;Wert2]...)

Sieht kompliziert aus, ist aber gar nicht so schlimm. Fangen wir ganz langsam an:

Beispiel 1

Im ersten Beispiel wollen wir auf die Zelle mit dem Gesamtergebnis zugreifen. Auch wenn als Spaltenüberschrift “Gesamtergebnis” darüber steht, heißt das zugrunde liegende Datenfeld “Umsatz”:

Zugriff auf das Gesamtergebnis

Zugriff auf das Gesamtergebnis

Am einfachsten ist es, man orientiert sich an der Feldliste, die rechts angezeigt wird, sobald man mit der aktiven Zelle in der Pivot-Tabelle steht.

Wichtig ist nur, dass der Feldname in Anführungszeichen gesetzt wird.

Der zweite Parameter unserer Funktion ist die Pivot-Tabelle. In meinem Beispiel habe ich hier die Zelle A1 angegeben. Es kann aber jede x-beliebige Zelle sein, die sich innerhalb der Pivot-Tabelle befindet. Der Grund, warum man diesen Parameter überhaupt braucht: Es könnte ja sein, dass dein Arbeitsblatt mehrere Pivot-Tabellen enthält. Excel muss schließlich wissen, um welche es sich handeln soll.

Und damit wird bereits das gewünschte Ergebnis angezeigt.

Beispiel 2
Als nächstes wollen wir das Gesamtergebnis nur für die Vertriebsregion Süd sehen. Hier müssen wir zwei weitere Parameter angeben, nämlich das Feld “Region” und den dazugehörigen Feldwert “Süd”:

Ergebnis für eine einzelne Region

Ergebnis für eine einzelne Region

Auch hier gilt wieder: Feldnamen und Werte sind in Anführungszeichen zu setzen.

Beispiel 3
Anstelle der Region wollen wir jetzt das Ergebnis für ein einzelnes Produkt sehen. Die Formel ist fast identisch, lediglich das Feld heißt jetzt “Produkt” und der Wert enthält den Namen eines Produkts:

Ergebnis für ein einzelnes Produkt

Ergebnis für ein einzelnes Produkt

So weit, so gut.

Beispiel 4
Und jetzt wollen wir noch etwas tiefer bohren und den Umsatz eines einzelnen Verkäufers für ein bestimmtes Produkt sehen. Dazu muss ich ein weiteres Parameter-Paar angeben:

Kombination Verkäufer und Produkt

Kombination Verkäufer und Produkt

Ich gebe zu, die Formel wird damit schon ziemlich lang, aber das Prinzip sollte jetzt verständlich sein.

Fehlerquellen

Es kann sein, dass die PIVOTDATENZUORDNEN-Funktion anstelle eines Ergebnisses den Fehler “#BEZUG” anzeigt. Mögliche Ursachen:

  • Du hast im zweiten Parameter keine Zelle innerhalb der Pivot-Tabelle angegeben
  • Du hast dich bei den Feldnamen oder Werten vertippt oder sie nicht in Anführungszeichen gesetzt
  • Das Feld, auf das du dich beziehst, wird momentan in der Pivot-Tabelle nicht angezeigt

Das Beste kommt zum Schluß

Die Funktion PIVOTDATENZORDNEN und die anzugebenden Parameter sind am Anfang etwas schwer zu verstehen. Als Belohnung dafür, dass du bis zum Schluß durchgehalten hast, verrate ich dir daher noch einen Trick.

Anstatt die Funktion mit den einzelnen Feldnamen und Werten umständlich von Hand einzugeben, machst du einfach folgendes:

  1. Du klickst in die Zelle, in der deine PIVOTDATENZUORDNEN-Funktion stehen soll
  2. du gibst in diese Zelle nur das Gleichheitszeichen “=” ein und
  3. du klickst in der Pivot-Tabelle auf das Ergebnis, das du gerne abfragen möchtest

Fertig!

Excel schreibt automatisch die Funktion mit allen notwendigen Parametern in die Zelle:

Excel erstellt die Funktion automatisch

Excel erstellt die Funktion automatisch

Sollte bei dir anstelle der PIVOTDATENFUNKTION nur ein normaler Zellverweis erscheinen, musst du zuerst noch eine kleine Einstellung ändern:
Im Menü “Datei” wählst du den Eintrag “Optionen” (unter Excel 2007: Office-Menü, dann die Schaltfläche Excel-Optionen”) und klickst links in der Liste auf den Eintrag “Formeln”.

Dann aktivierst du die Option “GetPivotData-Funktionen für PivotTable-Bezüge verwenden”:

Im Zweifelsfall muss eine Option aktiviert werden

Diese Option muss aktiviert sein

Geht doch!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

20 Gedanken zu “Wenn’s auf den Einzelnen ankommt: Werte in Pivot-Tabellen ansprechen

  • Niko

    Hallo,

    vielen Dank für deine wunderbare Erklärung. Was mache ich wenn ein Wert der heute noch da war, morgen nicht mehr existiert und die Formel einen #Bezug! ausspuckt? Ich möchte eine Abweichung in Tagen auswerten. Mal gibt es alle Werte von -2 bis +2 und mal fehlt zum Beispiel -1. Aber die Summer möchte ich dennoch von -2 bis +2 haben. Hast du eine Idee?

    Freundliche Grüße

    • Martin Weiß Beitragsautor

      Hallo Niko,

      eine Möglichkeit wäre eine kleine Matrixformel. Angenommen, Deine Werte stehen im Bereich A1:A5, wobei eine oder mehrere dieser Zellen einen #Bezug!-Fehler enthalten kann. Dann bekommst Du die Summe für die echten Werte mit {=SUMME(WENN(ISTZAHL(A1:A5);A1:A5))}

      Hinweis: Die geschweiften Klammern dürfen nicht direkt eingegeben werden. Stattdessen beendest Du die Eingabe mit STRG+Umschalt+Enter.

      Grüße,
      Martin

  • Beate

    Hallo,

    ich habe eine Frage zu deinem Tipp am Schluss. Bei mir taucht das Problem auf das nur ein normaler Zellverweis erscheint und nicht PIVOTDATENFUNKTION. Da ich einen Mac habe funktioniert der Menü-Weg, über Datei und Optionen, den du angegeben hast nicht um das Problem zu lösen. Ich habe auch schon vergeblich im Internet geschaut wo das bei Mac zu finden ist. Kannst du mir vielleicht weiter helfen?

    Danke
    Viele Grüße
    Beate

    • Martin Weiß Beitragsautor

      Hallo Beate,

      da ich keinen Zugriff auf ein Mac-System habe, kann ich hier leider keinen konkreten Tipp geben. In den Windows-Versionen von Excel (2007, 2010 und 2013) funktioniert es einwandfrei. Welche Mac-Version von Excel setzt Du ein? Zumindest unter Excel für Mac 2011 gibt es die PIVOTDATENFUNKTION. Ob sie in einer früheren Version schon verfügbar ist, kann ich nicht sagen.

      Vielleicht liest hier ein anderer Mac-User mit, der helfen kann.

      Schöne Grüße,
      Martin

  • Milos

    Hallo Martin
    Vielen Dank für das super Tutorial. Wie kann ich jedoch ein SVerweis über ein Pivot legen, welche wie bei der PIVOTDATENZUORDNUNG auch nach Aktualisierungen die richtigen Werte angiebt? Hast du da eine entsprechende Formel oder Tipps?

    Besten Dank und Grüsse

    Milos

    • Martin Weiß Beitragsautor

      Hallo Milos,

      SVERWEIS im Zusammenhang mit Pivottabellen ist schwierig, da die Tabelle ja in alle Richtungen dynamisch ist. Allerdings ist mir nicht klar, warum Du einen SVERWEIS einsetzen möchtest:
      Die beschriebene Funktion PIVOTDATENZUORDNUNG macht genau das, was ansonsten ein SVERWEIS machen würde…

      Schöne Grüße,
      Martin

  • Jessica

    Hallo Martin,

    vielen Dank für die Mühe, welche du in diese klasse Seite bereits investiert hast!
    Ich nutze für die Arbeit Excel 2007 und fertige gerade eine Pivot-Tabelle zur Stunden-/ Kostenübersicht der Mitarbeiter an.
    Da dies per Monat aufgelistet wird und dadurch das Scrollen trotz Fensterfixierung unnötig mühsam ist, möchte ich die Spalten mit den Gesamtergebnissen der Zeilen gerne im Datenfeld rechts angezeigt bekommen, nicht links …
    Leider lassen sich die Gesamtwerte in keiner mir bekannten Weise umpositionieren 🙁
    (Außer der Wechsel von Zeilenbeschriftung Spaltenbeschriftung)

    Vielen lieben Dan schon mal im Voraus für die Hilfe 🙂
    Jessica

    • Martin Weiß Beitragsautor

      Hallo Jessica,

      da muss ich Dich enttäuschen: Meines Wissens gibt es keine Möglichkeit, das Gesamtergebnis einer Pivot-Tabelle links anstatt rechts darstellen zu lassen.

      Grüße,
      Martin

  • Andrea

    Hallo Martin,
    vielleicht kannst du mir weiterhelfen. Ich habe eine Pivot-Tabelle die deiner vom Grundaufbau recht ähnlich ist. Für die Zwischenergebnisse für Nord, Ost, etc. habe ich mir über Feldeinstellungen ein Teilergebnis anzeigen lassen. Wenn ich mir nun nur die Umsätze von z.B. Müller anzeigen lasse, ändert sich natürlich auch mein Teilergebnis. Gibt es eine Möglichkeit, dass die Zwischenergebnisse für die Regionen “ganz”, also die Summe aller Vertreter bleiben, egal welchen Vertreter ich mir gerade anzeigen lasse?
    Ich hoffe ich hab mich einigermaßen Verständlich ausgedrückt.
    Viele Liebe Grüße
    Andrea

    • Martin Weiß Beitragsautor

      Hallo Andrea,

      innerhalb der Pivot-Tabelle ist das nicht möglich, denn es ist ja gerade der Sinn eines Filters, dass nur noch die gefilterten Ergebnisse angezeigt werden. Du wirst also ggf. eine zweite Pivot-Tabelle brauchen: In der ersten kannst Du nach den Verkäufern filtern, in der zweiten werden die unveränderten Regionen-Ergebnisse angezeigt.

      Schöne Grüße,
      Martin

  • Andrea

    Hallo Martin,

    meine automatisch durch Anklicken des Feldes in der Pivottagelle generierte Formel sieht folgendermaßen aus und liefert auch das richtige Ergebnis:

    =PIVOTDATENZUORDNEN(“[Measures].[FTE]”;$A$5;”[Datum].[J-Q-M-T]”;”[Datum].[J-Q-M-T].[Quartal].&[20152]”;”[Mitarbeiter].[Standort]”;”[Mitarbeiter].[Standort].&[39279]”;”[Mitarbeiter].[Mitarbeiterstatus]”;”[Mitarbeiter].[Mitarbeiterstatus].&[1]”)

    Aber der Standort heißt in der Tabelle nicht 39279 sondern Brüssel. Wo die Zahl herkommt, weiß ich nicht, die Pivotdatei ist von einem Fremdanbieter. Kann ich die Formel irgendwie so abändern, dass ich dort Brüssel angebe? Dann ist sie leichter lesbar und bei dir stehen dort doch auch die Namen aus der Pivot!?

    Ich habe schon mal versucht, die Zahl einfach durch Brüssel oder “Brüssel” zu ersetzen. Dann kommt #BEZUG.
    Ebenso wenn ich die eckigen Klammern weglasse. Mir fällt jetzt nichts mehr ein, ich hoffe aber dir. 🙂

    Vielen Dank vorab und viele Grüße

    Andrea

    • Martin Weiß Beitragsautor

      Hallo Andrea,

      die strukturierten Verweise in Deiner Formel kommen dadurch zustande, dass offensichtlich mehrere Quelltabellen miteinander verknüpft und in einer Pivot-Tabelle verwendet werden. Und in einer dieser Quelltabellen lautet eine Spaltenüberschrift eben “39279”. Du musst also die entsprechende Quelltabelle finden und zuerst dort die Spaltenüberschrift ändern. Danach kannst Du die Pivot-Tabelle aktualisieren und dann sollte auch die Formel das gewünschte Ergebnis liefern.

      Grüße,
      Martin

  • Thomas

    Hi,

    der Tipp hat mir sehr geholfen! Allerdings würde ich nun gerne auf Monatlich gruppierte werte(über ein feld namens “Datum”) in dem Pivot zugreifen.
    Geht das irgendwie?

    Danke & Grüße,
    Thomas

    • Thomas

      Hat sich erledigt… hatte ein Problem mit den Überschriften in der Pivottabelle. Über PivotTabelle ->Layout->Tabellarisch (Offive:Mac 2011) hab ich das umgestellt… wenn man die richtigen Bezeichnungen nutzt, funktioniert es auf einmal 😉

  • sebastian

    Hallo Martin,

    ich habe folgende Syntax: =PIVOTDATENZUORDNEN(” 31.05.2016″;$A$7;”Bereich Muster-Bank”;”MFA”). So weit so einfach. Da es sich aber um viele solcher Zuordnungen handelt und ich nicht jeden Monat die Formeln neu anpassen möchte (auch nicht mit suchen und ersetzen) wollte ich das Datum in der Formel durch einen Bezug auf eine Zelle mit dem Datum ersetzen. Wichtig ist evtl noch die Besonderheit, dass in meiner Feldliste im Bereich der Spaltenüberschrift den Eintrag “Summenzeichen” Werte und in dem Wertebereich die einzelnen Datumsangaben (Die Datumseingaben sind eigentlich die jeweiligen Summen zu einem bestimmtem Datum) eingetragen sind.
    Hast du eine Idee, wie ich das Datum in der Zuordnung sozusagen dynamisieren kann?

    vg Sebastian

    • Martin Weiß Beitragsautor

      Hallo Sebastian,

      grundsätzlich kann man die Funktion schon dynamisieren, indem man statt eines festen Wertes eine Zelle angibt, in der dieser Wert steht. Allerdings habe ich den Aufbau Deiner Pivot-Tabelle noch nicht so richtig verstanden: Die Datumseingaben sind Summen zu einem bestimmten Datum? Was genau meinst Du damit? Wenn ich mir Deine Funktion ansehe, dann scheint das Datum “31.05.2016” ja der Name eines Feldes zu sein, also eine Spaltenbezeichnung in der Quelltabelle?

      Ich stehe auf dem Schlauch…

      Grüße,
      Martin

  • Peter Petz

    Hallo,

    Ich habe beim Speicheren von XLS Files (Version 2013) mit Pivottabellen folgendes Problem:
    Ich habe beisplw. im File Dummy1.xlsx eine Pivot deren Datenbereich auf eine Tabelle X verweist.
    Speichere ich die Datei neuerlich unter Dummy2.xlsx verweist der Datenbereich nun auf Tabelle X aber in der Datei Dummy1.xlsx. Natürlich sollte der Verweis auf Dummy2.xlsx ab Besten aber nur auf die Tabelle verweisen.

    Gibt´s hier irgendwelche Ideen?

    Danke und beste Grüße
    Peter Petz

    • Martin Weiß Beitragsautor

      Hallo Peter,

      dieses Verhalten ist mir etwas schleierhaft. Ich habe es bei mir zwei Varianten ausprobiert.
      Variante 1: Datei mit “Speichern unter” unter einem neuen Dateinamen abgespeichert, geschlossen und wieder geöffnet. Ergebnis: Der Verweis auf die Quelltabelle hat sich angepasst und ist korrekt.
      Variante 2: Datei geschlossen, im Windows-Explorer umbenannt und die umbenannte Version wieder geöffnet. Ergebnis: Auch hier greift die Pivottabelle auf die korrekten Quelldaten in der gleichen Tabelle zurück.
      Daher ist es mir im Augenblick ein Rätsel, was Du anders gemacht haben könntest…

      Schöne Grüße,
      Martin

      • Peter Petz

        Hallo Martin,

        Danke für die Rückmeldung.
        Ich habe das Problem nun insofern gelöst, als dass ich die Quelltabellen “enttabelisiert”, wieder in eine neue Tabelle umgewandelt habe und dann alle Pivots nochmals auf diese neuen Tabellen verwiesen habe … und siehe da, es funktioniert.

        LG
        Peter Petz

  • Yvonne

    Hallo ,

    funktioniert die Funktion nur, wenn die Pivot geöffnet ist? Wenn ich nur die Zieldatei öffne und die verknüpfte Pivot nicht, werden mir nur Bezugsfehler angezeigt bis ich die Pivot öffne. Kann man das umgehen?

    Danke und Gruß
    Yvonne