Um die Ecke gerechnet

Datenanalyse mit Pivot-Tabellen

Große Datenmengen werten Sie mithilfe von Pivot-Tabellen sehr effektiv aus. Wollen Sie zum Beispiel den Anteil der drei Topseller am Gesamtumsatz Ihres Warensortiments ermitteln, aufgeschlüsselt nach Verkaufsgebiet und Quartal, legen Sie eine Pivot-Tabelle an. Wir gehen die Rechenschritte mit Ihnen durch.

Pivot-Tabellen mit Excel

© Archiv

Pivot-Tabellen mit Excel

Große Datenmengen lassen sich mit Pivot-Tabellen sehr effektiv auswerten. Wollen Sie zum Beispiel den Anteil der drei Topseller am Gesamtumsatz Ihres Warensortiments ermitteln, aufgeschlüsselt nach Verkaufsgebiet und Quartal, legen Sie eine Pivot-Tabelle an. Wir gehen die Rechenschritte mit Ihnen durch.

Quelldaten festlegen

Als Quelldaten für den Pivot-Bericht dient in diesem Workshop eine Excel-Tabelle mit den Umsatzdaten eines fiktiven Lebensmittelgroßhändlers. Die Tabelle besteht aus den Spalten Kategorie (etwa Getränke, Gewürze, Süßwaren), Produkt, Umsatz und Quartal. Damit sich daraus ein Pivot-Bericht erzeugen lässt, muss die Tabelle eine Überschriftenzeile besitzen und darf keine leeren Spalten oder Zeilen enthalten.

Pivot-Tabellen mit Excel: Bericht-Assistent

© Archiv

Der neue PivotTable-Bericht soll mit Daten gefüllt werden.

Markieren Sie eine beliebige Zelle der Tabelle und wechseln Sie auf die Registerkarte Einfügen. Dort klicken Sie in der Gruppe Tabelle auf den oberen Bereich der Schaltfläche PivotTable. Excel erkennt automatisch die Ausdehnung der Tabelle und trägt den Bereich in das Dialogfeld PivotTable erstellen ein. Sie können das Dialogfeld daher ohne weitere Änderungen mit Klick auf die Schaltfläche OK bestätigen.

Excel fügt anschließend ein neues Tabellenblatt ein, auf dem sich mehrere Rahmen befinden. Dies ist der eigentliche PivotTable-Bericht. Rechts neben dem Tabellenblatt sehen Sie die Pivot-Tabellen Feldliste, in der die vier Spaltenüberschriften der Quelldaten enthalten sind. Mithilfe dieser Felder können Sie steuern, nach welchen Kriterien die Daten der Umsatztabelle ausgewertet werden sollen.

Gesamtumsätze ermitteln

In der ersten Ausbaustufe soll der PivotTable-Bericht die Umsätze der verschiedenen Kategorien zusammenfassen und addieren.

Pivot-Tabellen mit Excel: Summenbildung

© Archiv

Die Umsätze sind jetzt nach Kategorien aufgeschlüsselt.

Klicken Sie in der Feldliste auf das Feld Umsatz und ziehen Sie es mit gedrückter Maustaste nach rechts unten in den Bereich Werte. Im Bereich Werte taucht dadurch eine Schaltfläche Summe von Umsatz auf. Sie signalisiert, dass die Werte, die sich in der Spalte Umsatz befinden, summiert werden. Auf Ihrem Tabellenblatt sehen Sie nun eine Zahl: Der PivotTable-Bericht zeigt hier die Summe aller Umsätze an. Formatieren Sie diese Zahl im Buchhaltungsformat, indem Sie die Zelle mit der rechten Maustaste anklicken und dann in der angezeigten Minisymbolleiste auf die Schaltfläche mit dem Geldschein klicken. Passen Sie gegebenenfalls die Spaltenbreite an, damit die Summe korrekt dargestellt wird.

Ziehen Sie nun das Feld Kategorie nach unten in den Bereich Zeilenbeschriftung. Der PivotTable-Bericht erhält dann wie gewünscht für jede Kategorie eine eigene Zeile, in der der Umsatz ausgegeben wird, den die Produkte dieser Kategorie zusammen erzielt haben. Klicken Sie im Pivot-Table-Bericht auf die kleine Schaltfläche neben der Spaltenüberschrift Kategorie. Dadurch öffnet sich ein Menü, mit dem Sie die Sortierung der Kategorien ändern oder einzelne Kategorien aus dem Bericht entfernen können.

Umsätze aufschlüsseln nach Produkt und Quartal

Pivot-Tabellen mit Excel: Daten aufschlüsseln

© Archiv

Die Umsatzzahlen werden jetzt nicht nur nach Kategorien, sondern auch nach Quartalen und Produkten aufgeschlüsselt.

Bis jetzt gruppiert der PivotTable-Bericht lediglich die Werte einer einzelnen Spalte (der Spalte Kategorie) und berechnet die Zwischensummen dieser Gruppen. Im nächsten Schritt soll nun der Bericht um eine neue Dimension erweitert werden und zusätzlich die Verkäufe der einzelnen Quartale und Produkte ausweisen.

Ziehen Sie das Feld Quartal in den Bereich Spaltenbeschriftung. Dadurch erweitert sich der Bericht um vier Spalten, da die Ausgangstabelle in der Spalte Quartale insgesamt vier verschiedene Werte enthält. Ziehen Sie das Feld Produkt in den Bereich Zeilenbeschriftung. Beachten Sie dabei, dass Sie es unterhalb des bereits vorhandenen Feldes Kategorie anordnen. Die Umsatzzahlen werden nach drei Kriterien aufgeschlüsselt (Kategorie, Produkt und Quartal).

Sie können die Detailtiefe der Tabelle für einzelne Kategorien reduzieren, indem Sie auf das kleine Minuszeichen vor dem Kategoriennamen klicken. Falls es nicht sichtbar sein sollte, klicken Sie im Register Optionen in der Gruppe Einblenden/Ausblenden auf die Schaltfläche Schaltflächen +/-.

Alternative Rechenfunktion

Wenn Sie in der Feldliste ein Feld in den Bereich Werte ziehen, entscheidet Excel in Abhängigkeit des Feldinhaltes, welche Berechnung für das Feld durchgeführt werden soll. Im Falle der Umsatzzahlen war dies die Funktion Summe; bei einem Feld, das keine Zahlen, sondern Text enthält, wäre es die Funktion Anzahl. Excel kennt jedoch noch eine ganze Reihe anderer Funktionen.

Pivot-Tabellen mit Excel: Wertfeldeinstellungen

© Archiv

Im Dialogfeld Wertfeldeinstellungen legen Sie die Art und die Darstellung der Berechnung fest.

Klicken Sie eine Zelle des PivotTable-Berichts an, damit die Feldliste am rechten Bildschirmrand angezeigt wird. Erscheint die Feldliste nicht auf dem Bildschirm, klicken Sie auf der Registerkarte Optionen auf die Schaltfläche Feldliste. Klicken Sie in der Feldliste im Bereich Werte auf das gewünschte Feld und rufen Sie im Menü der Schaltfläche den Befehl Wertfeldeinstellungen auf.

Wählen Sie im angezeigten Dialogfeld auf der Registerkarte Zusammenfassen mit den gewünschten Berechnungstyp in der Liste aus. Auf der Registerkarte Werte anzeigen als können Sie zum Beispiel festlegen, dass bei einer Summe nicht die Zahl selbst, sondern ihr prozentualer Anteil zur Gesamtsumme angezeigt wird (% des Ergebnisses). Excel berechnet das automatisch. Um die Darstellung der Werte zu ändern, rufen Sie über die Schaltfläche Zahlenformat den bekannten Dialog zur Formatierung von Zellen auf.

Details anzeigen

Pivot-Tabellen mit Excel: Tabelle exportieren

© Archiv

Per Doppelklick auf ein Datenfeld können Sie Detaildaten als separate Excel-Tabelle extrahieren.

Im letzten Schritt haben Sie gesehen, dass ein PivotTable-Bericht vor allem dazu dient, große Datenmengen zusammenzufassen. Aus dieser Vogelperspektive können Sie sich zwar sehr gut einen schnellen Überblick verschaffen, aber gelegentlich werden Sie auch wissen wollen, aus welchen Einzelwerten sich eine bestimmte Zahl des PivotTable-Berichts zusammensetzt. Die Lösung ist mit Excel denkbar einfach.

Führen Sie im Datenbereich des Berichts auf der Zelle, zu der Sie detaillierte Informationen wünschen, einen Doppelklick aus. Excel ergänzt dann die Arbeitsmappe um ein neues Tabellenblatt, auf dem sich eine Tabelle mit den Detaildaten der angeklickten Zelle befindet. Bei den Detaildaten handelt es sich um eine Kopie der betreffenden Quelldaten, das heißt, das neue Tabellenblatt hat keine Verbindung zum PivotTable-Bericht mehr.

Bildergalerie

Pivot-Tabellen mit Excel: Daten aufschlüsseln
Galerie
Um die Ecke gerechnet

Die Umsatzzahlen werden jetzt nicht nur nach Kategorien, sondern auch nach Quartalen und Produkten aufgeschlüsselt.

Daten aktualisieren

Beim Einfügen eines PivotTable-Berichts kopiert Excel die Quelldaten und speichert sie in der aktuellen Arbeitsmappe. Das gilt selbst dann, wenn sich die Quelldaten in der gleichen Arbeitsmappe befinden wie der PivotTable-Bericht (s. Beispiel). Änderungen an den Quelldaten werden also nicht automatisch in den Bericht übernommen! Selbstverständlich können Sie bei Bedarf die Quelldaten erneut in den Bericht einlesen. Klicken Sie dazu einfach auf der Registerkarte Optionen auf Aktualisieren.

Nicht benötigte Daten ausblenden

Pivot-Tabellen mit Excel: Filterbedingungen

© Archiv

Einstellen der Filterbedingung bei Einfach- und Mehrfachauswahl.

Oft wird ein PivotTable-Bericht zunächst deutlich mehr Informationen liefern, als Sie benötigen. Wenn Sie sich zum Beispiel nur für die Umsätze einer bestimmten Warengruppe interessieren, stören die Verkaufsdaten der übrigen Warengruppe, da sie Sie von Ihrer eigentlichen Aufgabe ablenken. In solchen Fällen können Sie die überflüssigen Daten in der PivotTabelle ausblenden.

Ziehen Sie in der Feldliste das Feld Kategorie in den Bereich Berichtsfilter. In der ersten Zeile des Berichts - hier stand sonst immer der Text Seitenfelder hierhin ziehen - befindet sich nun das Listenfeld Kategorie. Momentan ist dort noch der Eintrag (Alle) ausgewählt. Öffnen Sie das Listenfeld mit einem Klick auf die kleine Pfeilschaltfläche. Um mehrere Kategorien auszuwählen (neu seit Excel 2007), schalten Sie im Menü das Optionsfeld Mehrere Elemente auswählen ein. Wählen Sie die Kategorie, deren Produkte im Bericht angezeigt werden sollen. Klicken Sie dann auf die Schaltfläche OK, um die gewünschten Daten im PivotTable-Bericht anzuzeigen.

Topseller ermitteln

Mit einem PivotTable-Bericht können Sie jedoch noch weitaus differenziertere Auswertungen vornehmen. Sie können sich zum Beispiel die drei umsatzstärksten Produkte der Kategorien Getränke, Milchprodukte und Süßwaren anzeigen lassen.

Richten Sie den PivotTable-Bericht so ein, dass alle Kategorien angezeigt werden (siehe Schritt 3). Markieren Sie die gewünschten Kategorien, indem Sie bei der Auswahl die [Strg]-Taste drücken und die betreffenden Zellen nacheinander anklicken. Klicken Sie mit der rechten Maustaste in eine der markierten Zellen und wählen Sie im Kontextmenü Filter/Nur ausgewählte Elemente beibehalten. Dadurch werden die übrigen Kategorien ausgeblendet und der Bericht enthält nur die gewünschten Kategorien.

Pivot-Tabellen mit Excel: Top-10-Filter

© Archiv

Bestimmen Sie, welche Kriterien der Top-10-Filter erfüllen soll.

Markieren Sie eine Kategorie und klicken Sie in der Registerkarte Optionen in der Gruppe Aktives Feld auf Gesamtes Feld erweitern. Dadurch werden im Bericht alle Produkte der drei Kategorien eingeblendet. Klicken Sie mit der rechten Maustaste einen beliebigen Produktnamen an und wählen Sie im Kontextmenü den Befehl Filter/Top 10. Ändern Sie schließlich im Dialogfenster Top-10-Filter den Vorgabewert von 10 auf 3. Nach einem Mausklick auf die Schaltfläche OK hat der Bericht die eingangs geforderte Form.

Eigene Gruppen erstellen

Bisher haben Sie die Daten der Umsatztabelle nach bereits vorhandenen Werten zusammengefasst (z. B. nach den verschiedenen Kategorien). Neben dieser, durch den Inhalt der Quelldaten vorgegebenen Gruppierung, können Sie mit Excel auch individuelle Kriterien definieren, auf deren Basis die Daten gruppiert werden sollen. So lassen sich zum Beispiel mehrere Kategorien zu einer Warengruppe zusammenfassen.

Pivot-Tabellen mit Excel: Zusammenfassung

© Archiv

Die Kategorien wurden zu Warengruppen zusammengefasst.

Markieren Sie die Elemente des Feldes, die Sie gruppieren möchten, also verschiedene Produkter einer Kategorie. Diese Markierung muss dabei nicht zusammenhängend sein. Klicken Sie in der Registerkarte Optionen auf Gruppenauswahl. Excel fügt nun ein neues Feld in die Feldliste ein, das für die neue Gruppierung verwendet wird. Das erste Element der neuen Gruppe heißt standardmäßig Gruppe1. Wollen Sie weitere Gruppen anlegen, wiederholen Sie die beiden ersten Schritte, so oft wie nötig. Geben Sie der neuen Gruppierung und ihren Elementen aussagekräftige Namen. Klicken Sie dazu die Zellen an und ändern Sie sie in der Bearbeitungsleiste.

Mehr zum Thema

Word - Text um Bild
Office-Tipp

In Word 2010 und 2013 gibt es Bilderrahmen mit einer pfiffigen Funktion für das Freistellen von Bildinhalten. Wir zeigen, wie es geht.
Wir zeigen den normgerechten Aufbau bei Briefen mit Microsoft Word.
Anleitung

Ein DIN-gerechter Aufbau erleichtert dem Lesenden die Übersicht. Word bietet dafür mit seinen Vorlagen und Funktionen alle Voraussetzungen.
Excel - Nettoarbeitstage
Office-Tipp

Wir erklären, wie Sie in Excel etwa für eine Arbeits- oder Urlaubsplanung die Wochenenden herausrechnen und die Nettoarbeitstage berechnen.
Kalender synchronisiert auf iPad
Zeit-Management

Kalender auf dem Smartphone, PC und im Web synchronisieren - mit unseren 9 Tipps schaffen Sie genau das. Verpassen Sie keinen Termin mehr!
Outlook - Logo
Office-Tipp

Wir zeigen, wie Sie in Outlook die Kalenderwoche anzeigen lassen können. Unser Office-Tipps sorgt für Durchblick bei der Terminplanung.