Pivot-Tabellen sind besondere Ansichten der Daten einer Tabelle. Man kann Daten zusammenfassen, berechnen, verschieben oder filtern ohne dass man den Datenbestand selber, also die Excel-Tabelle, verändert.
Pivot-Tabellen sind ein Hilfsmittel zur Analyse von Datenbeständen.
Bearbeitet werden können Listen/Datenbanken aus Excel, aber auch externen Daten aus Access oder anderen Anwendungen.
Voraussetzung ist eine Datenbanken oder Liste, die gleichartige Elemente enthält, nur dann macht die Zusammenfassung der Daten nach verschiedenen Kriterien Sinn.
Erstellen einer Pivot-Tabelle
Als Beispiel dient die Datei Bestellung.xls (Quelle: Herdt-Verlag)
Zunächst ist es sinnvoll, die Daten der Tabelle mit einem Bereichsnamen zu belegen
z. B. „Liste“.
Markieren Sie eine Zelle im Bereich der Daten, die Sie als Pivot-Table analysieren möchten.
- Wechseln Sie auf die Registerkarte „Einfügen“ – Gruppe „Tabellen“ – Symbol „PivotTable“
Im nachfolgenden Dialogbild wird nochmals der Datenbereich gezeigt, hier können Sie den Datenbereich ändern oder auch auf eine Externe Datenquelle verweisen.
- Weiter legen Sie hier fest, wo die Pivot-Tabelle erstellt werden soll.
Hier sollte man sich für ein neues Arbeitsblatt entscheiden, damit man die analysierten Daten getrennt hält von seinen Originaldaten.
- OK beendet dieses Dialogbild und Excel wechselt in ein neues Tabellenblatt und zeigt Ihnen die Bearbeitungsmöglichkeiten Pivot-Tabelle.
Klassisches Layout der Pivot-Tabelle, Anpassen mit Drag&Drop
- Wählen Sie in der Pivot-Table-Tool-Registerkarte in der ersten Gruppe „PivotTable“ – „Optionen“ – Registerkarte „Anzeige“ – Kontrollkästchen „Klassisches PivotTable-Layout“
Die Ansicht ändert sich wie unten abgebildet und Drag&Drop der Felder funktioniert wieder wie in Excel 2003.
Die einzelnen Bereiche einer PivotTable:
Wertfelder (Entspricht dem eigentlichen „Berechnungsbereich“)
- Ziehen Sie aus dem rechten Bereich ein Feld in den Bereich „Wertfeld“
- Anschließend wird für dieses Feld eine Berechnung durchgeführt.
Zeilenfelder
- Ziehen Sie aus dem rechten Bereich ein Feld in den Bereich „Zeilenfeld“
- es wird jeder Wert aus diesem Feld 1x aufgelistet
Spaltenfelder
- Ziehen Sie aus dem rechten Bereich ein Feld in den Bereich „Spaltenfeld“
- es wird jeder Wert aus diesem Feld 1x nebeneinander (in Spalten) gezeigt
Berichtsfilter
- Ziehen Sie aus dem rechten Bereich ein Feld in den Bereich „Berichtsfilter“
- sie können nach diesem Wert die Daten der PivotTable filtern
Aufgabe: Anzahl der Bestellungen (in der Region „Mitte“) herausfinden
Daher muss man das Feld „Bestellungen“ in den Wertfeld-Bereich ziehen. Automatisch wird bei Feldern vom Typ numerisch von Excel die Summe gebildet, was natürlich zu ändern ist in Anzahl.
Ändern der Berechnungs-Funktion:
- Ein Doppelklick auf dem Feld führt zu folgendem Dialogbild:
Will man die Anzahl berechnen, so wählt man die Funktion Anzahl aus. Im Datenbereich stehen jetzt die Anzahl-Bestellungen.
Aufgabe: Wie viele Bestellungen aus der Region „Mitte“ wurden getätigt
Ziehen Sie das Feld „Region“ in den Bereich „Berichtsfilter“.
So kann man die Anzahl der Bestellungen nach Regionen anzeigen lassen
Will man nun nur die Region „Mitte“ sehen, wählt man am Listfeld Region eben diese aus.
Beispiel: Man möchte neben der Region auch noch nach dem Ort filtern können. Dazu wird ein zweiter Berichtsfilter benutzt.
- Ziehen Sie das Feld „Ort“ unter „Region“
Das Zeilenfeld
Bisher kann man Daten nach verschieden Regionen oder Orten alternativ
anzeigen lassen.
Aufgabe: Anzahl Bestellungen für jede Region auf einmal sehen
Berichtsfilter löschen
indem die Felder einfach auch diesem Bereich herausgezogen werden-
Feld „Region“ in Zeilenfeld ziehen
Ergebnis:
Zu jeder Region noch den Ort zeigen, mit Zwischenergebnis
- Feld „Ort“ ebenfalls in den Bereich Zeilenfeld ziehen.
Ergebnis:
Spaltenfeld als Auswertungsfeld
Eine Tabelle kann natürlich auch die Auswertung in Spalten vornehmen. Man muss die Orte nicht untereinander schreiben, sondern kann sie in der Pivot-Tabelle auch nebeneinander setzen. Dazu wird das entsprechende Feld in den Bereich Spaltenfelder gezogen.
Ergebnis:
(inhaltlich das Gleiche, wie bei der zeilenweisen Auswertung, nur die Darstellung ist eine andere)
Spalten- und Zeilenfeld gemeinsam nutzen
Interessant wird das Ganze allerdings erst, wenn man Daten gleich in zwei Richtungen zusammenfasst.
Beispiel:
Anzahl der Bestellungen gleichzeitig nach Regionen und Städten geordnet
Ergebnis:
Spalten- und Zeilenfelder sind ebenfalls als Listfelder aufklappbar, um damit Daten zu selektieren.
Beispiel: nur Region Süd
Bei den Listfeldern der Spalte und Zeile kann man (wie mit einem logischen ODER verbunden) mehrere Regionen gleichzeitig sich anschauen:
Zeile oder Spalte erlauben eine Mehrfachauswahl
Gesamtergebnisspalte ein- / ausblenden
Die Spalte „Gesamtergebnis“ wird immer automatisch erzeugt.
Ausschalten kann man dies mit der PivtoTable-Tools – Registerkarte „Entwurf“ – Gruppe „Layout“
Hinweis:
Sobald man Feld, das als Zeilen- oder Spaltenfeld benutzt worden ist, gefiltert hat, merkt sich Excel diese Info. Wenn man solch ein Feld jetzt als Berichtsfilterfeld benutzt, dann bleibt der gesetzte Filter dort erhalten
Daten der Pivot-Tabelle aktualisieren
Änderungen in den Quelldaten werden nicht automatisch in der Pivot-Tabelle sichtbar. Sie müssen die Pivot-Tabelle manuell aktualisieren.
- Mauszeiger in die Pivot-Tabelle setzen
- Kontextmenü oder Symbol Aktualisieren
Sortieren einer Pivot-Tabelle
Das Sortieren innerhalt einer Pivot-Tabelle kann analog dem Sortieren eines Tabellenblattes durchgeführt werden. Z. B. mit den Symbolen
in der Tool-Registerkarte
Datenquelle der PivotTable ändern oder bearbeiten
In der Tool-Registerkarte – Gruppe „Daten“ – „Datenquelle ändern“ wählen
Es wird die Datenherkunft gezeigt:
Weitere Beispiele:
PivotTable mit 2 berechneten Feldern (Anzahl und Summe der Bestellungen)
PivotChart (Diagramm)
Zum Erstellen von PivotCharts gehen Sie analog dem Erstellen einer PivotTable vor, nur dass Sie auf der Registerkarte „Einfügen“ das Symbol „PivotTable“ am Listpfeil anklicken, um dann „PivotChart“ wählen zu können.
Pivot-Tabellendaten gruppieren
Bsp. Sie haben viele Datumswerte in einer Tabelle stehen, möchten jetzt allerdings eine Zusammenfassung nach Monat in Ihrer PivotTable haben.
- Erstellen Sie dazu eine Pivot-Tabelle
Um nach dem Feld „Antragsdatum“ monatlich zu gruppieren, rufen Sie auf dem Feld das Kontextmenü auf (re. Maustaste)- wählen aus dem Kontextmenü den Befehl „Gruppieren“
- im Dialogbild können Sie nun die Gruppierungsart, z. B. nach Monat wählen
Ergebnis:
Danke das war sehr hilfreich, sehr gut gemacht mit der Illustration auch optisch sehr ansprechend.
Mit besten Grüßen
Peter Euler