Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Häufige Fragen

Pivottabelle: PIVOTDATENZUORDNEN (ab Excel 2002)

Autor: Beate Schmitz - Erstellt: 2008-12      - Letzte Revision: ?

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:
 ABCD
1Rechnungs-Nr.KundeArtikelUmsatz
21000Kunde5Artikel181,89 €
31001Kunde1Artikel583,43 €
41002Kunde2Artikel599,97 €
51003Kunde4Artikel369,23 €
61004Kunde1Artikel416,51 €
71005Kunde4Artikel217,79 €
81006Kunde1Artikel554,91 €
91007Kunde5Artikel211,31 €
101008Kunde3Artikel248,65 €
111009Kunde4Artikel310,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:
 FGHIJKL
1Summe von UmsatzKunde     
2ArtikelKunde1Kunde2Kunde3Kunde4Kunde5Gesamtergebnis
3Artikel1    81,89 €81,89 €
4Artikel2  48,65 €17,79 €11,31 €77,75 €
5Artikel3   79,85 € 79,85 €
6Artikel416,51 €    16,51 €
7Artikel5138,34 €99,97 €   238,31 €
8Gesamtergebnis154,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:
 FGHIJKLMN
1Summe von UmsatzKunde     Gesamtergebnis:494,31 €
2ArtikelKunde1Kunde2Kunde3Kunde4Kunde5Gesamtergebnis  
3Artikel1    81,89 €81,89 €  
4Artikel2  48,65 €17,79 €11,31 €77,75 €  
5Artikel3   79,85 € 79,85 €  
6Artikel416,51 €    16,51 €  
7Artikel5138,34 €99,97 €   238,31 €  
8Gesamtergebnis154,85 €99,97 €48,65 €97,64 €93,20 €494,31 €  

Formeln der Tabelle
ZelleFormel
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:
 FGHIJKLMN
1Summe von UmsatzKunde     Gesamtergebnis Kunde 4:97,64 €
2ArtikelKunde1Kunde2Kunde3Kunde4Kunde5Gesamtergebnis  
3Artikel1    81,89 €81,89 €  
4Artikel2  48,65 €17,79 €11,31 €77,75 €  
5Artikel3   79,85 € 79,85 €  
6Artikel416,51 €    16,51 €  
7Artikel5138,34 €99,97 €   238,31 €  
8Gesamtergebnis154,85 €99,97 €48,65 €97,64 €93,20 €494,31 €  

Formeln der Tabelle
ZelleFormel
N1=PIVOTDATENZUORDNEN("Umsatz";$F$1;"Kunde";"Kunde4")
Oder entsprechend das Gesamtergebnis von Artikel2:
 FGHIJKLMN
1Summe von UmsatzKunde     Gesamtergebnis Artikel2:77,75 €
2ArtikelKunde1Kunde2Kunde3Kunde4Kunde5Gesamtergebnis  
3Artikel1    81,89 €81,89 €  
4Artikel2  48,65 €17,79 €11,31 €77,75 €  
5Artikel3   79,85 € 79,85 €  
6Artikel416,51 €    16,51 €  
7Artikel5138,34 €99,97 €   238,31 €  
8Gesamtergebnis154,85 €99,97 €48,65 €97,64 €93,20 €494,31 €  

Formeln der Tabelle
ZelleFormel
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:
 FGHIJKLMN
1Summe von UmsatzKunde     Gesamtergebnis Kunde4 bei Artikel2:17,79 €
2ArtikelKunde1Kunde2Kunde3Kunde4Kunde5Gesamtergebnis  
3Artikel1    81,89 €81,89 €  
4Artikel2  48,65 €17,79 €11,31 €77,75 €  
5Artikel3   79,85 € 79,85 €  
6Artikel416,51 €    16,51 €  
7Artikel5138,34 €99,97 €   238,31 €  
8Gesamtergebnis154,85 €99,97 €48,65 €97,64 €93,20 €494,31 €  

Formeln der Tabelle
ZelleFormel
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:
 FGHIJ
1Summe von Umsatz  Summe von Artikel2:77,75 €
2ArtikelKundeErgebnis  
3Artikel1Kunde581,89 €  
4Artikel1 Ergebnis 81,89 €  
5Artikel2Kunde348,65 €  
6 Kunde417,79 €  
7 Kunde511,31 €  
8Artikel2 Ergebnis 77,75 €  
9Artikel3Kunde479,85 €  
10Artikel3 Ergebnis 79,85 €  
11Artikel4Kunde116,51 €  
12Artikel4 Ergebnis 16,51 €  
13Artikel5Kunde1138,34 €  
14 Kunde299,97 €  
15Artikel5 Ergebnis 238,31 €  
16Gesamtergebnis 494,31 €  

Formeln der Tabelle
ZelleFormel
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:
 FGHIJ
1Summe von Umsatz  Mittelwert pro Kunde von Artikel2:25,92 €
2ArtikelKundeErgebnis  
3Artikel1Kunde581,89 €  
4Artikel1 Mittelwert 81,89 €  
5Artikel2Kunde348,65 €  
6 Kunde417,79 €  
7 Kunde511,31 €  
8Artikel2 Mittelwert 25,92 €  
9Artikel3Kunde479,85 €  
10Artikel3 Mittelwert 39,93 €  
11Artikel4Kunde116,51 €  
12Artikel4 Mittelwert 16,51 €  
13Artikel5Kunde1138,34 €  
14 Kunde299,97 €  
15Artikel5 Mittelwert 79,44 €  
16Gesamtergebnis 494,31 €  

Formeln der Tabelle
ZelleFormel
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:
 FGHIJK
1KundeDatenErgebnis Gesamtzahl der Artikel:10
2Kunde1Anzahl von Artikel3 Artikelzahl Kunde4:3
3 Summe von Umsatz154,85 € Gesamtumsatz:494,31 €
4Kunde2Anzahl von Artikel1 Umsatz von Kunde4:97,64 €
5 Summe von Umsatz99,97 €   
6Kunde3Anzahl von Artikel1   
7 Summe von Umsatz48,65 €   
8Kunde4Anzahl von Artikel3   
9 Summe von Umsatz97,64 €   
10Kunde5Anzahl von Artikel2   
11 Summe von Umsatz93,20 €   
12Gesamt: Anzahl von Artikel 10   
13Gesamt: Summe von Umsatz 494,31 €   

Formeln der Tabelle
ZelleFormel
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:
 FGHIJK
1KundeDatenErgebnis Mittelwert aller Kunden pro Rechnung:49,43 €
2Kunde1Mittelwert von Umsatz51,62 € Mittelwert Kunde4 pro Rechnung:32,55 €
3 Summe von Umsatz154,85 € Gesamtumsatz aller Kunden:494,31 €
4Kunde2Mittelwert von Umsatz99,97 € Umsatz von Kunde4:97,64 €
5 Summe von Umsatz99,97 €   
6Kunde3Mittelwert von Umsatz48,65 €   
7 Summe von Umsatz48,65 €   
8Kunde4Mittelwert von Umsatz32,55 €   
9 Summe von Umsatz97,64 €   
10Kunde5Mittelwert von Umsatz46,60 €   
11 Summe von Umsatz93,20 €   
12Gesamt: Mittelwert von Umsatz 49,43 €   
13Gesamt: Summe von Umsatz 494,31 €   

Formeln der Tabelle
ZelleFormel
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")



Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
ToDo
Google Werbung