Excel-Tipp: Pivot-Auswertungen

Pivot-Auswertungen sind ein sehr effektives Werkzeug, um sich oder anderen in ganz kurzer Zeit, also mit einem sehr geringen zeitlichen Aufwand einen Überblick über große Datenmengen zu verschaffen. Voraussetzung dazu ist allerdings die richtige Fragestellung und die richtige Auswahl der Zeilen- und Spaltenfelder sowie der Wertefelder aus der Pivot-Tabelle.

Nehmen wir an, zu Ihren Routineaufgaben gehört es, die zusammengefassten Umsätze der vergangenen Woche an Ihren Vorgesetzten zu berichten, strukturiert nach:

Umsatz

Produkt-Nr.

Vertrieb

Rechnungsdatum

Region

Aus dem Rechnungsausgangsbuch können Sie sich eine Excelaufstellung über die erfassten Ausgangsrechnungen für einen bestimmten Zeitraum generieren lassen. Die in Excel enthaltenen Funktionen bzw. Hilfsmittel Teilergebnis und Filtern sind Ihnen bekannt. Für Ihre Auswertung erweitern Sie die Umsatzliste auf folgenden Inhalt, indem Sie das Datum entsprechend anzeigen lassen und die Aufstellung um die lfd. Zeilennummer des Excelarbeitsblattes sowie um die Anzahl 1 in jeder Zeile erweitern. Somit stehen Ihnen zur Auswertung folgende Reihen-, Spalten- und Wertefelder zur Verfügung:

Zeilen-Nr.

Umsatz

Produkt-gruppe

Vertrieb

Datum

Region

Monat

KW

Quartal

Anzahl

Bisher haben Sie mit der Funktion Teilergebnis Woche für Woche folgende Teilauswertungen in einer Arbeitsmappe zusammengestellt:

Produktgruppe

Vertrieb

Region

Monat

KW

Quartal

Über die Filterfunktion können Sie eine Vielzahl von Arbeitsblättern entsprechend der angeforderten Auswertung bearbeiten. Danach können Sie entsprechend der erhaltenen Auswertungen weiter untergliedern. Dies kostet viel Arbeitszeit und das Ergebnis sehen Sie immer noch nicht in einer einfachen Übersicht. Die optimale Lösung für die Zusammenfassung Ihrer Ursprungsdaten in einem Arbeitsblatt bietet Excel mit dem Werkzeug Pivot-Table.

Pivot-Table

Mit dieser Standardauswertung fassen Sie Ihre umfangreichen Quelldaten in kürzester Zeit so zusammen, dass Sie die Datenflut auf die Quintessenz der Daten unter Verzicht auf die Details reduzieren. Damit Sie Ihre Quelltabelle mit Pivot auswerten können, müssen Sie als erstes Ihre auszuwertende Tabelle, (Grundtabelle) öffnen. Dann öffnen Sie über die Hauptregisterkarte Einfügen das Menü PivotTable:


Bitte Abbildung anklicken für detailliertere Darstellung

Ggf. müssen Sie in der Hauptregisterkarte Einfügen => PivotTable anklicken, damit Sie dieses Tool nutzen können. Über Einfügen im Hauptmenüband wird folgende Toolbar geöffnet:


Bitte Abbildung anklicken für detailliertere Darstellung

Im ersten Block (Tabellen) finden Sie den Button um PivotTable zu starten. Als erstes öffnen Sie in Excel die Ursprungstabelle, die Sie auswerten wollen. Mit dem Pivot-Startbefehl öffnet sich die nachfolgende Dialogbox für die Erstellung der Pivot-Tabelle. Excel markiert in der Ursprungstabelle den belegten Tabellenbereich. Sofern Sie einen anderen Tabellenbereich auswerten wollen, ist der Bereich entsprechend anzupassen. Dazu geben Sie an, wo die Auswertung abgelegt werden soll:



Nach dem Klick auf OK öffnet sich die nachfolgende Bildschirmansicht:



Bitte Abbildung anklicken für detailliertere Darstellung

Damit die richtige Auswertung erstellt wird, müssen Sie sich im Klaren sein, nach welchen Kriterien die Auswertung erfolgen soll. Kriterien sind grundsätzlich alle Spalten. Dazu überträgt das Pivot Tool die Spaltenbezeichnungen in der Zeile mit den Spaltenüberschriften tabellarisch in die Dialogbox am rechten Bildschirmrand. Die Grafik am linken Bildschirmrand zeigt an, mit welcher Struktur Pivot die Auswertungstabelle erstellt. Dazu können Sie dann aus der Dialogbox am rechten Bildschirmrand auswählen, welche Spalten aus der Quelltabelle in die Pivot-Auswertung, übernommen werden sollen. Wenn in den ausgewählten Spalten Werte enthalten sind, bildet das Pivot-Tool eine Summe dieser Spalten und eine Quersumme aller ausgewählten Spalten mit Werten. Eine Pivot Tabelle benötigt somit:

• Reihenfelder, 
• Wertefelder und 
• Spaltenfelder, 

die sich aus dem Spaltenkopf aus der Zeile mit Überschriften jeder Spalte in der Quelltabelle ergeben, wie im Beispielsfall:

Produktgruppe

Vertrieb

Datum

Region

Monat

KW

Quartal

Reihen- und Spaltenfelder

Die Spalten können, bis auf die Wertefelder und die Spaltenanzahl, frei als Reihenfeld oder Spaltenfeld definiert werden. Maßgeblich für eine sinnvolle Auswertung sollte die jeweilige Anzahl der Positionen in den einzelnen Spalten der Quelltabelle sein. Wenn eine Spalte als Reihenfeld ausgewählt wird, erfolgt die Anzeige der Überschrift jeder Spalte vertikal nach unten. Damit Sie jede Aufstellung dieser Art gut auswerten können, sollten Sie Ihre Tabelle um folgende Informationen aus den vorliegenden Daten und dem Excel-Tabellenblatt erweitern:

• Zeilen-Nr. 
• Anzahl 
• Monat 
• KW 
• Quartal

Sie verwenden für Ihre Pivot-Auswertung also folgende Quelltabelle als Grundlage:



Zur besseren Übersicht habe ich in der abgebildeten Tabelle die Zeilen 5 bis 5668 ausgeblendet. Das Werkzeug Pivot verdichtet dann die Ursprungstabelle entsprechend der Auswahl im rechten Bereich. Dazu muss im oberen Teil der Dialogbox die entsprechende Spalte angeklickt werden diese dann im unteren Bereich entsprechend der Excelstruktur von Spalten und Zeilen zugeordnet werden. Dazu muss ausgewählt werden, dass die für jede Produktgruppe, gegliedert nach der Region die summierte Anzahl (Zwischensumme) ermittelt wird. Dann bildet Pivot die Summen der Spalten- und Zeileninhalte.


Bitte Abbildung anklicken für detailliertere Darstellung

Erstellung der Auswertung ohne Pivot

Ohne Pivot müssen Sie die Informationen in der Ursprungstabelle mit der Summewenns-Formel verdichten. Jeder der die Summewenns-Funktion nutzt, liebt und hasst sie gleichzeitig. Damit die Daten überhaupt verdichtet werden können, müssen Sie zuerst aus der Spalte, nach der die Sortierung erfolgen soll, die Duplikate entfernen. Dazu kopieren Sie die entsprechende Spalte in ein leeres Arbeitsblatt und bezeichnen dies entsprechend. Im zweiten Schritt entfernen Sie die Duplikate in dieser Spalte über Duplikate entfernen (im Bereich Datentools des Hauptmenübandes). In der Musterauswertung verbleiben für die Spalte Region fünf eindeutige Werte:



Dann müssen Sie die Duplikate für die Spalten aus der Ursprungstabelle entfernen. Dazu kopieren Sie die Spalte Produktgruppe in Spalte A in ein leeres Arbeitsblatt und bezeichnen dieses Blatt entsprechend. Dort duplizieren Sie die Spalte A in Spalte B und entfernen die Duplikate. Es verbleiben:



Dann erstellen Sie die Matrix für die Auswertung in einem weiteren Excelblatt in der Arbeitsmappe:



Und tragen in jede Zelle im Bereich der Auswertung folgende Formel ein:

=SUMMEWENNS('Umsatzliste (5672)'!$J:$J;'Umsatzliste (5672)'!$F:$F;$A2;'Umsatzliste (5672)'!$C:$C;B$1)

Nachdem Ausfüllen der Formel nach unten und nach rechts ergibt sich folgende Auswertung:



Zusammengefasst muss im Falle der Auswertung mit summewenns eine eigene Arbeitsmappe mit folgendem Inhalt aufgebaut werden:



Dies ist ein erheblicher zeitlicher Mehraufwand, insbesondere, wenn verschiedene Auswertungen aus der Ursprungsliste erstellt werden sollen, weil für jedes Auswahlkriterium eine Mappe mit den entsprechenden Auswertungskriterien erstellt werden muss.

Pivot Auswertungen filtern

In den Pivot Auswertungen ist standardmäßig die Verbindung zur Funktion filtern hergestellt, so das ganz leicht mit dieser einzigartigen Auswahlform, noch detaillierter Kenntnisse aus der Auswertung gezogen werden können.

Man erkennt diese an dem Pfeil nach unten im Text Spaltenüberschift bzw. Zeilenüberschrift. Unabdingbar ist, dass es keine leere Spalte innerhalb der Daten in der Ursprungsliste gibt. Vor Auswertungsbeginn sollte der Cursor auf der Zelle A1 stehen, damit das vollständige Datenblatt zur Auswertung herangezogen werden kann.

Pivot-Chart

Aus der Pivot-Auswertung lässt sich mit einem Klick ein Diagramm erstellen. Über die Registerkarte Einfügen können Sie einen Pivot-Chart starten.


Bitte Abbildung anklicken für detailliertere Darstellung

Musterauswertungen mit Pivot

Umsatz je Vertrieb und Region:


Bitte Abbildung anklicken für detailliertere Darstellung

letzte Änderung S.W. am 19.07.2024 Autor:  Siegfried Weiler

Autor:in

	Herr Siegfried Weiler Siegfried Weiler ist Dipl.-Betriebswirt (BA). Er verfügt über Erfahrungen in der Steuerberatung und hat in einer Firmengruppe das Rechnungswesen mit Excel-Tools aufgebaut. Er arbeitet seit 30 Jahren mit Excel,
weitere Fachbeiträge des Autors | Forenbeiträge