Pivottabelle: PIVOTDATENZUORDNEN (ab Excel 2002)
Die Funktion PIVOTDATENZUORDNEN (engl.: GETPIVOTDATA) gibt Daten aus einer
Pivottabelle zurück. Sie können PIVOTDATENZUORDNEN verwenden, um Datenzusammenfassungen aus einem Pivottabelle abzurufen, vorausgesetzt, die Datenzusammenfassung im Bericht ist sichtbar, also nicht ausgeblendet. Greift die Funktion auf ausgeblendete Werte zu, wird der Fehlerwert #BEZUG! ausgegeben.
PIVOTDATENZUORDNEN ermöglicht, auf einzelne Werte (also nicht auf Bereiche) der Pivottabelle mit den Bezeichnungen der Kategorien, also der Spalten- und Zeilenbeschriftungen zuzugreifen.
Der Vorzug dieser Funktion liegt darin, wenn sich nach
Aktualisierung der Pivottabelle die Datenzusammenfassungszeilen ändern, werden immer noch die richtigen Zellen getroffen. Würde man Zelladressen benutzen, würden nach Aktualisierung eventuell falsche Werte herangezogen.
Wenn Sie noch eine Excelversion unterhalb von Excel2002 benutzen, können Sie das Gleiche nur erreichen, indem Sie eine
Summenproduktformel auf die
Ausgangstabelle ansetzen.
Die Syntax der Funktion:
PIVOTDATENZUORDNEN(Datenfeld;PivotTable;Feld1;Element1;Feld2;Element2;...)
Datenfeld: Der erste Parameter gibt den Datenfeldnamen an, auf den sich die Funktion bezieht. Er muss in Anführungszeichen gesetzt werden.
PivotTable: Der zweite Parameter gibt irgendeine Zelladresse der Pivottabelle an. Dieser Parameter dient dazu, die Tabelle zu identifizieren. Sie könnten ja mehrere Pivottabellen zu einem Quellbereich in der Tabelle haben.
Feld1, Element1, Feld2, Element2;
: Stehen für Paare aus Feld- und Elementnamen (zwischen 1 und 14), die die Daten beschreiben, die Sie abrufen möchten. Diese Paare können in einer beliebigen Reihenfolge auftreten. Feld- und Elementnamen, die nicht aus Datumsangaben oder Zahlen bestehen, werden in Anführungszeichen eingeschlossen.
Tipp:
Sie können eine PIVOTDATENZUORDNEN-Formel schnell eingeben, indem Sie
= in die Zelle eingeben, in der Sie den Wert zurückgeben möchten. Anschließend klicken Sie im PivotTable-Bericht auf die Zelle, die die Daten enthält, die Sie zurückgeben möchten. Danach schließen Sie die Formelzelle mit Enter ab. Diese Methode hat den Vorteil, dass sich keine Fehler bei der Formelerstellung einschleichen können.
Dieser Tipp funktioniert nicht bei Teilergebnissen <> Summe. Siehe
unten.
Um die Funktion PIVOTDATENZUORDNEN zu verstehen, gebe ich Ihnen nachfolgende Beispiele:
Zum Blattanfang
Das ist die Ausgangstabelle:
| | A | B | C | D |
| 1 | Rechnungs-Nr. | Kunde | Artikel | Umsatz |
| 2 | 1000 | Kunde5 | Artikel1 | 81,89 |
| 3 | 1001 | Kunde1 | Artikel5 | 83,43 |
| 4 | 1002 | Kunde2 | Artikel5 | 99,97 |
| 5 | 1003 | Kunde4 | Artikel3 | 69,23 |
| 6 | 1004 | Kunde1 | Artikel4 | 16,51 |
| 7 | 1005 | Kunde4 | Artikel2 | 17,79 |
| 8 | 1006 | Kunde1 | Artikel5 | 54,91 |
| 9 | 1007 | Kunde5 | Artikel2 | 11,31 |
| 10 | 1008 | Kunde3 | Artikel2 | 48,65 |
| 11 | 1009 | Kunde4 | Artikel3 | 10,62 |
Zum Blattanfang
Die daraus erstellte Pivottabelle:
Sie wollen auswerten, welcher Kunde welchen Umsatz pro Artikel erzeugt hat. Dabei haben Sie den Gesamtumsatz pro Artikel in Spalte L und pro Kunde in Zeile 8:
| | F | G | H | I | J | K | L |
| 1 | Summe von Umsatz | Kunde | | | | | |
| 2 | Artikel | Kunde1 | Kunde2 | Kunde3 | Kunde4 | Kunde5 | Gesamtergebnis |
| 3 | Artikel1 | | | | | 81,89 | 81,89 |
| 4 | Artikel2 | | | 48,65 | 17,79 | 11,31 | 77,75 |
| 5 | Artikel3 | | | | 79,85 | | 79,85 |
| 6 | Artikel4 | 16,51 | | | | | 16,51 |
| 7 | Artikel5 | 138,34 | 99,97 | | | | 238,31 |
| 8 | Gesamtergebnis | 154,85 | 99,97 | 48,65 | 97,64 | 93,20 | 494,31 |
Verstehen Sie nun die Pivottabelle als eine Kreuztabelle; es müssen die passenden Begriffe/Parameter benutzt werden, um mit der Funktion PIVOTDATENZUORDNEN bestimmte Daten herauszuziehen.
Zum Blattanfang
Das Ergebnis aus der Zelle unten rechts herausziehen = Das Gesamtergebnis:
Um z.B. den Gesamtumsatz zu ermitteln, lautet die Formel:
| | F | G | H | I | J | K | L | M | N |
| 1 | Summe von Umsatz | Kunde | | | | | | Gesamtergebnis: | 494,31 |
| 2 | Artikel | Kunde1 | Kunde2 | Kunde3 | Kunde4 | Kunde5 | Gesamtergebnis | | |
| 3 | Artikel1 | | | | | 81,89 | 81,89 | | |
| 4 | Artikel2 | | | 48,65 | 17,79 | 11,31 | 77,75 | | |
| 5 | Artikel3 | | | | 79,85 | | 79,85 | | |
| 6 | Artikel4 | 16,51 | | | | | 16,51 | | |
| 7 | Artikel5 | 138,34 | 99,97 | | | | 238,31 | | |
| 8 | Gesamtergebnis | 154,85 | 99,97 | 48,65 | 97,64 | 93,20 | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | N1 | =PIVOTDATENZUORDNEN("Umsatz";$F$1) |
|
Der erste Parameter gibt den Datenfeldnamen an, auf den sich die Funktion bezieht, der zweite Parameter gibt irgendeine Zelladresse der Pivottabelle an. Dieser Parameter dient dazu, die Tabelle zu identifizieren. Sie könnten ja mehrere Pivottabellen zu einem Quellbereich in der Tabelle haben. Mit diesen Parametern wird das Ergebnis ausgegeben, welches unten rechts in der Tabelle steht.
Zum Blattanfang
Ein Unterergebnis herausziehen:
Fügen Sie als weitere Begriffe/Parameter jeweils ein Paar "Kategoriename";"Elementname" hinzu, um sich auf andere Felder der Tabelle zu beziehen. Die Bezeichnungen müssen in Anführungszeichen stehen und durch Strichpunkte getrennt sein.
Im Beispiel wollen wir den Umsatz von Kunde4 herausziehen:
| | F | G | H | I | J | K | L | M | N |
| 1 | Summe von Umsatz | Kunde | | | | | | Gesamtergebnis Kunde 4: | 97,64 |
| 2 | Artikel | Kunde1 | Kunde2 | Kunde3 | Kunde4 | Kunde5 | Gesamtergebnis | | |
| 3 | Artikel1 | | | | | 81,89 | 81,89 | | |
| 4 | Artikel2 | | | 48,65 | 17,79 | 11,31 | 77,75 | | |
| 5 | Artikel3 | | | | 79,85 | | 79,85 | | |
| 6 | Artikel4 | 16,51 | | | | | 16,51 | | |
| 7 | Artikel5 | 138,34 | 99,97 | | | | 238,31 | | |
| 8 | Gesamtergebnis | 154,85 | 99,97 | 48,65 | 97,64 | 93,20 | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | N1 | =PIVOTDATENZUORDNEN("Umsatz";$F$1;"Kunde";"Kunde4") |
|
Oder entsprechend das Gesamtergebnis von Artikel2:
| | F | G | H | I | J | K | L | M | N |
| 1 | Summe von Umsatz | Kunde | | | | | | Gesamtergebnis Artikel2: | 77,75 |
| 2 | Artikel | Kunde1 | Kunde2 | Kunde3 | Kunde4 | Kunde5 | Gesamtergebnis | | |
| 3 | Artikel1 | | | | | 81,89 | 81,89 | | |
| 4 | Artikel2 | | | 48,65 | 17,79 | 11,31 | 77,75 | | |
| 5 | Artikel3 | | | | 79,85 | | 79,85 | | |
| 6 | Artikel4 | 16,51 | | | | | 16,51 | | |
| 7 | Artikel5 | 138,34 | 99,97 | | | | 238,31 | | |
| 8 | Gesamtergebnis | 154,85 | 99,97 | 48,65 | 97,64 | 93,20 | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | N1 | =PIVOTDATENZUORDNEN("Umsatz";$F$1;"Artikel";"Artikel2") |
|
Zum Blattanfang
Einen Wert aus dem Inneren der Pivottabelle herausziehen:
Durch ein weiteres Parameterpaar erhalten Sie einen Wert aus dem Inneren der Pivottabelle. Die Bezeichnungen müssen in Anführungszeichen stehen und durch Strichpunkte getrennt sein.
Im Beispiel wollen wir den Umsatz von Kunde4 bei Artikel2 herausziehen:
| | F | G | H | I | J | K | L | M | N |
| 1 | Summe von Umsatz | Kunde | | | | | | Gesamtergebnis Kunde4 bei Artikel2: | 17,79 |
| 2 | Artikel | Kunde1 | Kunde2 | Kunde3 | Kunde4 | Kunde5 | Gesamtergebnis | | |
| 3 | Artikel1 | | | | | 81,89 | 81,89 | | |
| 4 | Artikel2 | | | 48,65 | 17,79 | 11,31 | 77,75 | | |
| 5 | Artikel3 | | | | 79,85 | | 79,85 | | |
| 6 | Artikel4 | 16,51 | | | | | 16,51 | | |
| 7 | Artikel5 | 138,34 | 99,97 | | | | 238,31 | | |
| 8 | Gesamtergebnis | 154,85 | 99,97 | 48,65 | 97,64 | 93,20 | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | N1 | =PIVOTDATENZUORDNEN("Umsatz";$F$1;"Kunde";"Kunde4";"Artikel";"Artikel2") |
|
Zum Blattanfang
Ausnahme beim Zugriff auf Teilergebnisse <> Summe
Beim Zugriff auf Teilergebnisse geht PIVOTDATENZUORDNEN automatisch vom Teilergebnis Summe aus. Bei allen anderen Teilergebnisfunktionen ist eine andere Formelsyntax einzusetzen, siehe nachstehendes Beispiel zu Mittelwert.
Teilergebnisse können ja unabhängig von der zentralen Funktion festgelegt werden. Nehmen wir an, Sie hätten sich basierend auf der
Ausgangstabelle folgende Pivottabelle erstellt, die in den Teilergebnissen die Summe pro Artikel anzeigt und wollen in Zelle J1 die Summe von Artikel2 ausgeben:
| | F | G | H | I | J |
| 1 | Summe von Umsatz | | | Summe von Artikel2: | 77,75 |
| 2 | Artikel | Kunde | Ergebnis | | |
| 3 | Artikel1 | Kunde5 | 81,89 | | |
| 4 | Artikel1 Ergebnis | | 81,89 | | |
| 5 | Artikel2 | Kunde3 | 48,65 | | |
| 6 | | Kunde4 | 17,79 | | |
| 7 | | Kunde5 | 11,31 | | |
| 8 | Artikel2 Ergebnis | | 77,75 | | |
| 9 | Artikel3 | Kunde4 | 79,85 | | |
| 10 | Artikel3 Ergebnis | | 79,85 | | |
| 11 | Artikel4 | Kunde1 | 16,51 | | |
| 12 | Artikel4 Ergebnis | | 16,51 | | |
| 13 | Artikel5 | Kunde1 | 138,34 | | |
| 14 | | Kunde2 | 99,97 | | |
| 15 | Artikel5 Ergebnis | | 238,31 | | |
| 16 | Gesamtergebnis | | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | J1 | =PIVOTDATENZUORDNEN("Umsatz";$F$1;"Artikel";"Artikel2") |
|
Nun wollen Sie aber je Artikel den Mittelwert je Kunde als Teilergebnis ausgegeben haben und ändern dies so:
- Markieren Sie eine Zelle der Pivottabelle in Spalte F
- Klicken Sie in der Symbolleiste Pivottabelle die Schaltfläche Feldeinstellungen an
- Haken Sie Teilergebnisse Anpassen an
- Und markieren Mittelwert
- Verlassen Sie das Fenster über die Schaltfläche OK
Ihre Pivottabelle sieht nun so aus, das Gesamtergebnis ist weiterhin die Summe. Und Sie möchten in Zelle J1 den Mittelwert pro Kunde von Artikel2 ausgeben. Dabei ist beim Mittelwert die Besonderheit zu beachten, dass PIVOTDATENZUORDNEN nur mit der engl. Bezeichnung AVERAGE funktioniert, für die übrigen Teilergebnisfunktionen funktionieren aber die deutschen Bezeichnungen:
| | F | G | H | I | J |
| 1 | Summe von Umsatz | | | Mittelwert pro Kunde von Artikel2: | 25,92 |
| 2 | Artikel | Kunde | Ergebnis | | |
| 3 | Artikel1 | Kunde5 | 81,89 | | |
| 4 | Artikel1 Mittelwert | | 81,89 | | |
| 5 | Artikel2 | Kunde3 | 48,65 | | |
| 6 | | Kunde4 | 17,79 | | |
| 7 | | Kunde5 | 11,31 | | |
| 8 | Artikel2 Mittelwert | | 25,92 | | |
| 9 | Artikel3 | Kunde4 | 79,85 | | |
| 10 | Artikel3 Mittelwert | | 39,93 | | |
| 11 | Artikel4 | Kunde1 | 16,51 | | |
| 12 | Artikel4 Mittelwert | | 16,51 | | |
| 13 | Artikel5 | Kunde1 | 138,34 | | |
| 14 | | Kunde2 | 99,97 | | |
| 15 | Artikel5 Mittelwert | | 79,44 | | |
| 16 | Gesamtergebnis | | 494,31 | | |
| Formeln der Tabelle |
| Zelle | Formel | | J1 | =PIVOTDATENZUORDNEN(F2;"Artikel[Artikel2;Average]") |
|
Zum Blattanfang
Mehrere Datenfelder
Beispiel 1: Verschiedene Datenfelder mit verschiedenen Funktionen auswerten
Aus der
Ausgangstabelle haben Sie folgende Pivottabelle mit mehreren Datenfeldern erstellt und wollen gemäß Spalte J auswerten:
| | F | G | H | I | J | K |
| 1 | Kunde | Daten | Ergebnis | | Gesamtzahl der Artikel: | 10 |
| 2 | Kunde1 | Anzahl von Artikel | 3 | | Artikelzahl Kunde4: | 3 |
| 3 | | Summe von Umsatz | 154,85 | | Gesamtumsatz: | 494,31 |
| 4 | Kunde2 | Anzahl von Artikel | 1 | | Umsatz von Kunde4: | 97,64 |
| 5 | | Summe von Umsatz | 99,97 | | | |
| 6 | Kunde3 | Anzahl von Artikel | 1 | | | |
| 7 | | Summe von Umsatz | 48,65 | | | |
| 8 | Kunde4 | Anzahl von Artikel | 3 | | | |
| 9 | | Summe von Umsatz | 97,64 | | | |
| 10 | Kunde5 | Anzahl von Artikel | 2 | | | |
| 11 | | Summe von Umsatz | 93,20 | | | |
| 12 | Gesamt: Anzahl von Artikel | | 10 | | | |
| 13 | Gesamt: Summe von Umsatz | | 494,31 | | | |
| Formeln der Tabelle |
| Zelle | Formel | | K1 | =PIVOTDATENZUORDNEN("Anzahl von Artikel";$F$1) | | K2 | =PIVOTDATENZUORDNEN("Anzahl von Artikel";$F$1;"Kunde";"Kunde4") | | K3 | =PIVOTDATENZUORDNEN("Summe von Umsatz";$F$1) | | K4 | =PIVOTDATENZUORDNEN("Summe von Umsatz";$F$1;"Kunde";"Kunde4") |
|
Beispiel 2: Gleiches Datenfeld mit verschiedenen Funktionen auswerten
Aus der
Ausgangstabelle haben Sie folgende Pivottabelle mit mehreren Datenfeldern erstellt und wollen gemäß Spalte J auswerten:
| | F | G | H | I | J | K |
| 1 | Kunde | Daten | Ergebnis | | Mittelwert aller Kunden pro Rechnung: | 49,43 |
| 2 | Kunde1 | Mittelwert von Umsatz | 51,62 | | Mittelwert Kunde4 pro Rechnung: | 32,55 |
| 3 | | Summe von Umsatz | 154,85 | | Gesamtumsatz aller Kunden: | 494,31 |
| 4 | Kunde2 | Mittelwert von Umsatz | 99,97 | | Umsatz von Kunde4: | 97,64 |
| 5 | | Summe von Umsatz | 99,97 | | | |
| 6 | Kunde3 | Mittelwert von Umsatz | 48,65 | | | |
| 7 | | Summe von Umsatz | 48,65 | | | |
| 8 | Kunde4 | Mittelwert von Umsatz | 32,55 | | | |
| 9 | | Summe von Umsatz | 97,64 | | | |
| 10 | Kunde5 | Mittelwert von Umsatz | 46,60 | | | |
| 11 | | Summe von Umsatz | 93,20 | | | |
| 12 | Gesamt: Mittelwert von Umsatz | | 49,43 | | | |
| 13 | Gesamt: Summe von Umsatz | | 494,31 | | | |
| Formeln der Tabelle |
| Zelle | Formel | | K1 | =PIVOTDATENZUORDNEN("Mittelwert von Umsatz";$F$1) | | K2 | =PIVOTDATENZUORDNEN("Mittelwert von Umsatz";$F$1;"Kunde";"Kunde4") | | K3 | =PIVOTDATENZUORDNEN("Summe von Umsatz";$F$1) | | K4 | =PIVOTDATENZUORDNEN("Summe von Umsatz";$F$1;"Kunde";"Kunde4") |
|
