Workshop: Pivot-Tabellen
Gezielte Recherche in Excel-Tabellen
Wer aus großen Datenbeständen gezielt Informationen herausfiltern möchte, kann die Pivot-Tabellenfunktion von Excel nutzen. Diese Funktion wurde für die Anzeige, Zusammenführung, Auswertung und Filterung von Daten aus bestehenden Tabellen entwickelt.
Mehr zu Microsoft
In einer Pivot-Tabelle lassen sich die Zeilen und Spalten einer Ausgangstabelle beliebig positionieren, um unterschiedliche Darstellungen und Analyseansichten der Daten zu ermöglichen. Unser folgendes Beispiel soll Ihnen diese Excel-Funktion erläutern. Der Workshop wurde mit Excel 2010 erstellt, die Pivot-Funktion ist aber auch schon in den Vorgängerversionen von Microsoft Excel integriert. Je nach Excel-Version können die Schritte, Befehle oder Menüs abweichen. Hier sollten Sie die Excel-Hilfe Ihrer Version zurate ziehen.
Die Beispiel-Datenquelle für eine Pivot-Tabelle vorbereiten
Bei Pivot-Tabellen spielen die Spaltenüberschriften eine große Rolle. Aus den üblichen Spaltenüberschriften einer Excel-Tabelle werden in der Pivot-Tabelle Felder, nach deren Inhalt Sie die Daten auswerten können. Legen Sie zum Beispiel in einer Excel-Tabelle in der ersten Zeile folgende Überschriften an: "Name", "Ort" und "Umsatz". Damit Excel eindeutig erkennt, dass es sich bei dieser Zeile um Überschriften handelt, markieren Sie die Überschriften, und formatieren Sie diese in der Gruppe "Schriftart" Fett, indem Sie auf das Symbol "F" klicken.
Um nun ohne viel Aufwand die Datenbasis einer Beispieltabelle zu erstellen, setzen Sie die "Ausfülloptionen" und die "Zufallsfunktion" von Excel ein. Setzen Sie beispielsweise den Cursor in die erste freie Zelle unter der Überschrift "Name", und tippen Sie dort "Firma 1" ein. Ziehen Sie diesen Eintrag mit dem Kopieranfasser bis zur Zeile "21". Dort sollte dann "Firma 20" stehen. Falls nicht, klicken Sie auf den SmartTag unterhalb des Kopieranfassers und aktivieren dort die Option "Datenreihe ausfüllen".
Umsatzzahlen für unser Beispiel per Zufallszahl erzeugen
Auch die Spalte "Umsatz" soll schnell gefüllt werden. Dafür setzen Sie einen Zufallsgenerator ein. Tippen Sie in die erste Zelle unterhalb der Überschrift "Umsatz" folgende Formel ein: "=ZUFALLSZAHL()*10000". Die Funktion "Zufallszahl()" gibt eine Zahl zwischen den Werten "0" und "1" multipliziert mit 1.000 verteilt zurück.
Auch dieses Ergebnis ziehen Sie mit den Kopieranfasser bis in die Zeile "21". Damit das Ergebnis als Umsatz dargestellt wird, markieren Sie die Umsatzzahlen, und wählen Sie dann im Bereich "Zahl" aus dem Auswahlfeld den Eintrag "Währung" aus.
In die Spalte der "Orte" setzen Sie zum Beispiel für die ersten fünf Firmen "Kiel" ein, für die nächsten fünf Firmen jeweils "Hamburg", "München" und "Nürnberg". Benennen Sie das Tabellenblatt um, indem Sie einen Doppelklick auf das Register "Tabelle 1" machen. Tippen Sie stattdessen ein: "Datenquelle", und betätigen Sie abschließend die "Enter-Taste".
Steht die Datenquelle, kann man eine erste Pivot-Tabelle vorbereiten
Nachdem Sie die Datenquelle angelegt haben, wechseln Sie im Menüband auf das Register "Einfügen". Dort finden Sie in der Funktionsgruppe "Tabellen" das Symbol "PivotTable". Damit der Bereich Ihrer Daten erkannt wird, klicken Sie zunächst an eine beliebige Stelle innerhalb des ausgefüllten Tabellenbereichs. Wählen Sie dann den Befehl "PivotTable" aus. Nach diesem Mausklick werden Sie aufgefordert, den Bereich der zu analysierenden Daten anzugeben.
Da Ihr Cursor sich in der Tabelle befindet, sollte der Datenbereich automatisch erkannt werden. Dieses erkennen Sie daran, dass ein gestrichelter Rahmen um den Datenbereich verläuft. Sollte das einmal nicht der Fall sein, markieren Sie den auszuwertenden Bereich inklusive Überschriften manuell. Das Dialogfenster "PivotTable erstellen" wird eingeblendet.
Um eine einwandfreie Datentrennung der Datenquelle und der Pivot-Tabelle zu erhalten, sollten Sie grundsätzlich die Option "Neues Arbeitsblatt" für den PivotTable-Bericht auswählen. Mit einem Klick auf die Schaltfläche "OK" wird Ihre Pivot-Tabelle erstellt. Benennen Sie den Tabellennamen in "Pivot" um.
Die Auswertung mithilfe einer Pivot-Tabelle beginnt
Im neu erstellten Pivot-Tabellenblatt sehen Sie auf der rechten Seite die "PivotTable-Feldliste". Diese Feldliste enthält die Überschriften der Quelltabelle. Setzen Sie in der PivotTable-Feldliste ein Häkchen vor den Eintrag "Ort". Sofort werden Ihnen in der Pivot-Tabelle alle Orte angezeigt, und zwar gruppiert. Jeder Ort taucht nur ein einziges Mal auf.
Damit sie jetzt die Umsätze je Ort sehen, setzen Sie ein Häkchen in das Kontrollfeld "Umsatz". Schon werden Ihnen die Gesamtumsätze pro Ort angezeigt. Fügen Sie jetzt noch ein Häkchen vor dem Eintrag "Name" hinzu, werden Ihnen unterhalb der Orte alle Firmen mitsamt Umsatz angezeigt. So bequem und schnell gelangen Sie mithilfe der Pivot-Tabelle zu einer Datenauswertung.
Formatieren von Werten in einer Pivot-Tabelle
Sie haben es bestimmt schon gesehen: Die Summe der Umsätze wird nicht als Währung dargestellt. Formatierungen aus der Ursprungstabelle werden nicht mit in die Pivot-Tabelle übernommen. Aber Sie können die Werte direkt in der Pivot-Tabelle formatieren. Klicken Sie dazu auf einen beliebigen Wert unterhalb der Spalte "Summe von Umsatz". Aktivieren Sie das Register "Optionen" im Menüband. Klicken Sie in der Funktionsgruppe "Aktives Feld" auf den Befehl "Feldeinstellungen".
Im jetzt eingeblendeten Dialogfenster "Wertfeldeinstellungen" klicken Sie auf die Schaltfläche "Zahlenformat". Das Dialogfenster "Zellen formatieren" wird eingeblendet. Wählen Sie unterhalb der "Kategorie" den Eintrag "Währung" und betätigen Sie anschließend die Schaltfläche "OK". Alle Werte in dieser Spalte werden jetzt im Euro-Format dargestellt.
Layout einer Pivot-Tabelle verändern
Aktivieren Sie in den "PivotTable-Tools" das Register "Entwurf". Mit den Befehlen in diesem Register ändern Sie das Layout Ihrer Pivot-Tabelle. Neben den "PivotTable-Formaten", mit deren Hilfe Sie die farbliche Darstellung ändern, finden Sie die Gruppe "Layout". Um die Gesamtergebnisse der Orte jeweils unterhalb der Firmen darzustellen, klicken Sie in dieser Gruppe auf das Symbol "Teilergebnis", und wählen Sie im dann eingeblendeten Untermenü den Befehl "Teilergebnis unten in der Gruppe anzeigen".
Damit das Ergebnis noch übersichtlicher wird, klicken Sie auf das Symbol "Leere Zeilen" und aktivieren im Untermenü "Leerzeile nach jedem Element einfügen". Setzen Sie jetzt noch ein Häkchen in den Kontrollkasten "Verbundene Spalten" in der Gruppe "Optionen für PivotTable-Formate". Nachdem das Layout Ihren Erfordernissen entspricht, aktivieren Sie erneut das Register "Optionen". Die Änderungen haben keinen Einfluss auf die Quelldatei.
Einen Datenschnitt für die schnelle Recherche erzeugen
"Datenschnitte" sind eine neue Funktion in Excel 2010. Sie filtern damit in Pivot-Tabellen Ihre Daten schnell und gezielt. Zum Erstellen von Datenschnitten müssen Sie sich an einer beliebigen Stelle innerhalb Ihrer Pivot-Tabelle befinden. Klicken Sie dann im Register "Optionen" in der Funktionsgruppe "Sortieren und Filtern" auf den Befehl "Datenschnitt einfügen". Daraufhin erscheint das Dialogfenster "Datenschnitt auswählen" mit den in dieser Pivot-Tabelle vorhandenen Feldnamen.
Aktivieren Sie in diesem Dialogfenster ein oder mehrere Kontrollkästchen. In unserem Beispiel wählen wir das Feld "Ort" aus. Mit einem Klick auf die Schaltfläche "OK" wird der Datenschnitt für das Feld "Ort" in die Tabelle eingefügt. Alle vorhandenen Orte werden in diesem Filter dargestellt. Um jetzt nach einem Ort zu filtern, klicken Sie diesen an.
Möchten Sie nach Orten filtern, die zusammenstehen, so markieren Sie den ersten gewünschten Ort, halten die "Shift-Taste (Großbuchstabe)" gedrückt und markieren dann den letzten gewünschten Ort. Daten, die nicht unmittelbar zusammenstehen, markieren Sie durch Mausklick mit gleichzeitig gedrückter "Strg-Taste".
Den Datenschnitt können Sie auf beliebige vorhandene Felder ausführen. Um eine Filterung zu löschen, klicken Sie im Datenschnittfilter auf das Symbol "Filter löschen". Um einen kompletten Datenschnitt zu löschen, markieren Sie den äußeren Rahmen und betätigen die "Entfernen-Taste" ("Entf"). Sie können, auch weitere Datenschnitte einfügen. Die Ergebnisse der Filterung werden dann miteinander kombiniert.
Felder einer Pivot-Tabelle auf das Wesentliche reduzieren
Um die Tabelle übersichtlicher zu gestalten, sind die Einzelergebnisse der Firmen nicht relevant. Es sollen nur noch die Gesamtumsätze in den Orten angezeigt werden. Klicken Sie dazu auf einen der Ortsnamen in Ihrer Pivot-Tabelle. Wählen Sie anschließend in der Gruppe "Aktives Feld" das Symbol "Gesamtes Feld reduzieren". Es werden wunschgemäß nur noch die Gesamtumsätze pro Ort angezeigt.
Bei Bedarf können Sie mit einem Klick auf das Symbol "Gesamtes Feld erweitern" wieder alle Einzelumsätze einblenden. Um nur für einzelne Orte die Firmen ein- oder auszublenden, finden Sie vor jedem Ort ein Plus- oder Minuszeichen. Mit einem Mausklick auf diese Zeichen werden dann die Einzelwerte ein- oder ausgeblendet.
Kreuztabellen zeigen Daten der gesamten Quelltabelle
Sehr beliebt bei Recherchen oder Auswertungen sind "Kreuztabellen". In Kreuztabellen werden Werte angezeigt, wenn Zeilen- und Spalten einen gemeinsamen Treffer besitzen. Um die bestehende Pivot-Tabelle als Kreuztabelle darzustellen, ziehen Sie z.B. das Feld "Ort" aus dem Bereich "Zeilenbeschriftung" in die "Spaltenbeschriftung". Immer wenn eine Firma in einem Ort ansässig ist, wird in der entsprechenden "Orts"-Spalte der zugehörige Wert angezeigt.
Was heisst eigentlich Pivot?
Der Begriff Pivot steht für "Dreh-, Angelpunkt." Und diese Bezeichnung trifft den Sinn von Pivot-Tabellen genau: Sie können Ihre Daten aus jedem Blickwinkel heraus betrachten. Einfach ausgedrückt: Man sagt der Pivot-Tabelle, welche Werte oder Wertvergleiche man aus einer bestehenden Tabelle benötigt, die Pivot-Funktion durchsucht die Quelldatei und liefert eine neue Tabelle mit den gewünschten Daten. Die Quelldatei dient dabei bloß als Datenlieferant, wird selbst aber nicht verändert.
Auch Umstellungen, Neueinträge oder Layout-Änderungen in der Pivot-Tabelle selbst haben keinen Einfluss auf die Daten und die Darstellung der Originaltabelle. Umgekehrt gilt es zu beachten: Ändern sich die Daten in der Quelltabelle, muss man die Pivot-Tabelle aktualisieren. Dazu klickt man einfach mit der rechten Maustaste in den Datenbereich der Pivot-Tabelle und wählt im Kontextmenü den Eintrag "Daten aktualisieren".
Mehr zu Microsoft
