Autor: Peter Haserodt  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Pivottabelle - Kurzeinstieg

Autor: Peter Haserodt - Erstellt: ?      - Letzte Revision: ?
Pivottabellen sind ein viel zu selten genutztes Mittel von Excel.

Dies liegt an zwei Dingen:
1. Das man nicht weiß, wie man diese erstellt und
2. Das man nicht weiß, wofür man diese erstellt.

Vor allem mit dem 2. wollen wir uns beschäftigen.
Damit Sie aber nicht zuviele Beispieldaten eingeben müssen, als Ausnahme ein Download.
Dies ist eine Exceldatei mit einem Arbeitsblatt, in welchem ein paar Beispieldaten stehen (ca.7 KB):
_liste.zip

Ein kleiner Ausschnitt dieser Liste:

 
 ABCDEFGH
1        
2 FilialenQuartalArtikelAnzahlUmsatzGewinn 
3 DortmundI.Qua.Kinderschuhe36 2.728,80 €    791,35 €  
4 HamburgI.Qua.Kinderschuhe39 2.956,20 €    857,30 €  
5 EssenIV.QuaPflegemittel127 1.587,50 €    952,50 €  
6 DortmundIV.QuaHerrenschuhe32 3.155,20 € 1.009,66 €  
 


Eine mögliche Pivottabelle aus diesen Daten:
 
 ABCDEF
1Filialen(Alle)    
2      
3Summe von UmsatzQuartal    
4ArtikelI.Qua.II.Qua.III.QuaIV.QuaGesamtergebnis
5Damenschuhe   34.987,50 € 34.987,50 €   36.787,50 €   32.512,50 €    139.275,00 €
6Herrenschuhe   28.594,00 € 26.129,00 €   30.467,40 €   32.636,60 €    117.827,00 €
7Kinderschuhe   20.920,80 € 20.162,80 €   23.119,00 €   25.923,60 €      90.126,20 €
8Pflegemittel     9.625,00 € 10.012,50 €     8.875,00 €   10.087,50 €      38.600,00 €
9Zubehör     6.672,50 €   6.375,00 €     6.528,00 €     6.477,00 €      26.052,50 €
10Gesamtergebnis 100.799,80 € 97.666,80 € 105.776,90 € 107.637,20 €    411.880,70 €
 


Tatsächlich dauert die Erstellung einer solchen Pivottabelle ca. 5-10 Sekunden, je nachdem, wie schnell man mit der Maus ist.
(Am Ende dieses Artikels erhalten Sie einen Schnellkurs zum Erstellen)
Wir wollen uns aber hier ansehen, was eigentlich das Ziel ist:
Dafür vergessen wir mal kurz die ersten beiden Zeilen, die machen das ganze erst zur Pivot-Tabelle.

Der Bereich A4 - F10


Hier habe ich eine ganz simple Kreuztabelle!
Es wird zu jeder Spaltenüberschrift (1.Qua, 2.Qua ...) und jeder Zeilenüberschrift (Damenschuhe ...) ein Kreuzprodukt ermittelt. (Wobei Produkt hier mißverständlich sein kann, da wir die Summe ermitteln)

Im Klartext
:
In Zelle B5 steht die Summe aller verkauften Damenschuhe im ersten Quartal.
Dies setzt sich fort und in der letzten Spalte bzw. letzten Zeile erhalte ich jeweils die Gesammtsummen.

Excel hat nichts weiter gemacht, als eine Art SummeWenn zu bilden.
Für B5 z.B. Summiere mir alle Umsätze der Liste, die in Artikel die Bezeichnung Damenschuhe haben und in Quartal 1.Qua.

Unsere Liste wurde also aufgesplittet und zum Teil transponiert.
Links habe ich die eindeutigen Einträge von Artikel und als Spaltenüberschriften ab Zelle B4 die eindeutigen Quartale.
Ich bilde somit ein Kreuz aus den Artikeln und den Quartalen und in diesem Kreuz die jeweilige Summe des zugehörigen Umsatzes.

Und wer es nicht glaubt, kann es selbst kurz nachvollziehen, dass wir dies mit Bordmitteln auch ganz einfach erreichen könnten.
Im Beispiel habe ich nur die erste Formel ausgegeben, diese können Sie dann jeweils nach unten und nach rechts ziehen

Ausflug: Die Kreuztabelle per Hand erstellt:
 
 ABCDE
1 I.Qua.II.Qua.III.QuaIV.Qua
2Damenschuhe34987,534987,536787,532512,5
3Herrenschuhe285942612930467,432636,6
4Kinderschuhe20920,820162,82311925923,6
5Pflegemittel962510012,5887510087,5
6Zubehör6672,5637565286477
Formeln der Tabelle
B2 : =SUMMENPRODUKT((Liste!$D$3:$D$122=$A2)*(Liste!$C$3:$C$122=B$1)*(Liste!$F$3:$F$122))
 
Dieser kleine Ausflug hat mich mehr Zeit gekostet, als 100 Pivottabellen zu erstellen!

Was benötigen wir für Pivottabellen?

Selbstverständlich intakte Listen - Daten Daten Daten!
Also Dinge, die wir miteinander in Bezug setzen können, wie z.B. Artikel und Quartale.
Dann benötigen wir natürlich Zahlen, die wir in das Kreuz einbringen können.
Letztendlich immer nur : Gib mir am Kreuzungspunkt eine Zahl zurück, die ein Aggregat bildet.

Was ist nun ein Aggregat?


Aggregat ~ Zusammenfassung
Aggregatfunktionen spielen in Excel (oder auch in Access) eine große Rolle. Diese bilden ein Aggregat von Werten unter bestimmten Bedingungen, z.B. der Summe, oder Minimum oder Max oder Mittelwert oder ...
Ich kann Zahlen in einer Summe zusammenfassen oder das Min von Zahlen ermitteltn oder das Max ...

In unserem Beispiel haben wir als Kreuzprodukt die Summe ermittelt. Die Pivottabelle stellt uns auch die anderen Aggregatsfunktionen per Mausklick zur Verfügung.
Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
Wann wird die Kreuztabelle zur Pivottabelle?
Bis jetzt haben wir die ersten beiden Zeilen ausgelassen und in unserem Beispiel alle Filialen miteinbezogen.
Also das Kreuz wurde immer aus alle Artikeln und allen Quartalen ermittelt.

Das geniale an der Pivottabelle ist, dass ich eine dritte Dimension erhalten kann.
Was oben im Beispiel in Zelle B1 als (Alle) steht, ist eigentlich ein Dropdown, in welchem ich nun auch noch die Filialen auswählen kann, z.B. nur Dortmund.

Also kann ich mir per Mausklick sofort die Summe aller Umsätze für Artikel in Quartalen für eine bestimmte Filiale anschauen oder oder oder ...


Ausführung: Schnellkurs

Wie erstelle ich nun eine Pivottabelle:
Da sich dies von Excelversionen leicht unterscheidet hier nur das grundlegene Verständnis und Vorgehensweise:

In unserem Beispiel sind die

Filialen die Seitenüberschrift
Artikel die Zeilenüberschrift
Quartale die Spaltenüberschrift
Umsatz die Datenfelder

Markieren Sie eine beliebige Zelle in der Liste.

Bis Excel 2003 gehen Sie dann auf das Menü Daten und dort auf Pivot (Dies ist je nach Excelversion noch ergänzt)
Ab Excel 2007 Register Einfügen, Gruppe Tabellen, dort Pivot Table

Jetzt öffnet sich ein Dialog (auch Versionsabhängig), wählen Sie dort Microsoft Excelbericht und Pivottabelle und in neuem Blatt (dies kann sich beim Assistenten verteilen)

Je nach Version kommt nun ein weiterer Schritt in welchem Sie die Zuordnungen treffen oder direkt im Arbeitsblatt, das ziehen der jeweiligen Felder in die entsprechenden Bereiche ist aber vom Prinzip gleich.

Tatsächlich ist das Erstellen einer Pivottabelle in der Online Hilfe von Excel sehr ausführlich beschrieben, so dass ich dies hier nicht abschreiben möchte.

Wichtig war mir nur, dass Sie verstehen, warum wohin welche Felder ziehen und was der Hintergrund ist.

(Und Sie natürlich neugierig auf die Pivottabelle machen! )

Die Pivottabelle hat viele Möglichkeiten, die sich ihnen sicherlich schnell erschließen (rechte Mausklicks helfen dort oft).
Ich hoffe, dass es bald ein paar mehr Details zu Pivottabellen hier in OE geben wird, vielleicht von ihnen ? sm3 (1K)

Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben
ToDo
Google Werbung