Datenanalyse leichtgemacht
PivotTable-Berichte mit Excel 2002 (Office XP)

von Eva & Michel Birnbacher (Ausgabe 02/1, März 2002)

 

Ein PivotTable-Bericht ermöglicht es, Daten von Tabellen aus einem oder mehreren Arbeitsblättern mittels verschiedener Filter und Berechnungsmethoden neu zu ordnen, zusammenzufassen oder umzustrukturieren, wobei die ursprünglichen Daten bei der Bearbeitung unverändert bleiben.

Beispiel: Ein Konditor vermerkt jedes Produkt, das er an seine Kunden liefert, in einer Tabelle.

Kunde

Produkt

Stück

Datum

Preis

Huber & Co

Nußkipferl

20

03.01.01

24,-

Huber & Co

Apfelstrudel

40

03.01.01

40,-

M. Maier

Topfengolatsche

20

03.01.01

26,-

Pigl & Söhne

Apfelstrudel

30

05.01.01

30,-

Pigl & Söhne

Topfengolatsche

50

05.01.01

65,-

(...)

 

 

 

 


Mit Hilfe dieser Daten möchte er folgendes herausfinden:

  • Wieviel wurde von jedem Produkt ausgeliefert?
  • Welcher Kunde hat wieviel im letzten Jahr bestellt?
  • Wie hoch sind die monatlichen Einnahmen?

Genau für solche Anforderungen sind PivotTable-Berichte ideal: sinnvolle Strukturierung und Auswertung von Datenmaterial anhand unterschiedlichster Kriterien. PivotTable-Berichte können auf Basis einer oder mehrerer bereits existierender Excel-Listen oder Excel-Datenbanken erstellt werden. Ebenso können aber auch Daten, die aus externen Datenquellen bezogen werden, innerhalb von Excel mittels PivotTable-Bericht analysiert werden - beispielsweise besteht eine direkte Importmöglichkeit für dBASE-Dateien und MS-Access-Datenbanken.

Als guter Geist steht Ihnen der PivotTable-Assistent zur Seite, der Schritt für Schritt die nötigen Hilfestellungen anbietet bzw. gezielt Informationen abfragt (siehe Abb. 2-4). Bevor Sie die Option PivotTable- und PivotChart-Bericht aufrufen, sollten Sie sich vergewissern, daß die zu analysierende Datei auf dem Bildschirm angezeigt wird.

Erstellen von PivotTable-Berichten

Zum Erstellen eines PivotTable-Berichts sind keine besonderen Vorarbeiten notwendig. Die Grundlage für die nachfolgenden Beispiel-Analysen mittels Excel 2002 (Office XP) und PivotTable-Berichten bildet eine herkömmliche Excel-Tabelle, die die Umsatzstatistik eines fiktiven Unternehmens für das Jahr 2001 enthält (siehe Abb. 1).

Um aus dieser umfangreichen und dadurch sehr unübersichtlichen Tabelle mit Hilfe eines PivotTable-Berichts brauchbare Informationen herauszufiltern, wählen Sie im Menü Daten die Option PivotTable- und PivotChart-Bericht. Automatisch tritt nun der PivotTable-Assistent in Aktion und stellt seine ersten Fragen (siehe Abb. 2).

Excel-Tabelle
Abb. 1: Excel-Tabelle als Grundlage für einen PivotTable-Bericht
PivotTable-Assistent
Abb. 2: PivotTable-Assistent - Erster Schritt

Zunächst müssen Sie entscheiden, welche Daten Sie analysieren wollen. Da die Datenbasis in unserem Fall in Form einer bereits existierenden Excel-Liste vorliegt, belassen wir die Vorgabe Microsoft Excel-Liste oder -Datenbank. Weiters soll eine tabellarische Auswertung durchgeführt werden; daher ist die Frage Wie möchten Sie Ihre Daten darstellen? mit PivotTable zu beantworten. Um zum nächsten Schritt zu gelangen, genügt ein Mausklick auf die Schaltfläche Weiter.

Nun geht es darum, den Datenbereich festzulegen, der ausgewertet werden soll (siehe Abb. 3). Der PivotTable-Assistent schlägt den gesamten Datenbereich vor, der aber jederzeit im Eingabefeld Bereich von Ihnen eingeschränkt werden kann. In unserem Fall belassen wir auch hier die Vorgabe, sodaß das gesamte Datenmaterial in die Analyse miteinbezogen wird. Mit einem Klick auf die Schaltfläche Weiter gelangen wir zum dritten und letzten Schritt.

Abb. 3: Auswahl des Datenbereichs
Abb. 3: Auswahl des Datenbereichs

Dabei wird definiert, wo der fertige PivotTable-Bericht erstellt werden soll. Damit die Verwirrung am Anfang nicht zu groß ist, sollten Sie hier die Option In neuem Arbeitsblatt auswählen (siehe Abb. 4). Ein letzter Klick auf die Schaltfläche Fertig stellen, und schon erscheint der fertige PivotTable-Bericht - oder doch nicht?

Abb. 4: Positionierung des PivotTable-Berichts

Leider nicht ganz. Was Sie als Ergebnis erhalten, ist eine Art Grundgerüst für den PivotTable-Bericht (siehe Abb. 5). Dieses Grundgerüst ist in vier Bereiche eingeteilt: Seitenfelder, Spaltenfelder, Zeilenfelder und Datenfelder. Am Bildschirm erscheint außerdem die sogenannte PivotTable-Feldliste, die alle für die Analyse zur Verfügung stehenden Felder beinhaltet. (Vorsicht: Sobald Sie außerhalb des PivotTable-Berichts hinklicken, verschwindet die Feldliste; klicken Sie in den PivotTable-Bericht hinein, erscheint sie wieder.) Weiters wird auch die PivotTable-Symbolleiste angezeigt, die Sie jederzeit über den Menüpunkt Ansicht - Symbolleisten ein- und ausblenden können.

Abb. 5: Grundgerüst des PivotTable-Berichts
Abb. 5: Grundgerüst des PivotTable-Berichts

Soviel zur Bildschirmdarstellung. Als nächstes gilt es, eine geeignete Fragestellung auszuwählen und die dafür benötigten Felder entsprechend zu positionieren. Folgende Fragestellung soll die Ausgangsbasis für die Erstellung unseres PivotTable-Berichts bilden: Welcher Verkäufer hat im Jahr 2001 welches Produkt wie oft verkauft? Daraus werden nun die benötigten Felder abgeleitet - Verkäufer, Produkt und Anzahl müssen innerhalb des PivotTable-Berichts positioniert werden. Zu beachten ist hierbei lediglich, daß aufgrund der Beschränkung von Excel auf 256 Spalten das umfangreichere Datenmaterial mit Hilfe der Zeilenfelder (also untereinander) dargestellt werden sollte.

Zeigen Sie innerhalb der PivotTable-Feldliste auf das Feld Verkäufer und ziehen Sie es bei gedrückt gehaltener linker Maustaste in den Bereich Spaltenfelder. Während des Ziehvorgangs wird zusätzlich zum Mauszeiger ein grauer Balken mit einem Tabellensymbol angezeigt, der Ihre jeweilige Position kennzeichnet. Sobald der Bereich Spaltenfelder grau umrahmt ist, lassen Sie die linke Maustaste los. Jetzt scheinen im Bereich Spaltenfelder die Namen der einzelnen Verkäufer jeweils in einer eigenen Spalte auf. Als nächstes müssen Sie das Feld Produkt aus der PivotTable-Feldliste im Bereich Zeilenfelder positionieren, damit die einzelnen Produkte zeilenweise angezeigt werden. Zuletzt ist noch der Bereich Datenfelder zu füllen; ziehen Sie dazu das Feld Anzahl aus der PivotTable-Feldliste in den Bereich Datenfelder.

Als Ergebnis erhalten Sie nun den fertigen PivotTable-Bericht (siehe Abb. 6). Wie Sie anhand der Spalten- und Zeilenfelder zuvor definiert haben, erscheint links in jeder Zeile ein anderes Produkt, während oben in jeweils einer eigenen Spalte die Verkäufer der Reihe nach angeordnet sind. Der Datenbereich zeigt die Anzahl der verkauften Produkte je Verkäufer.

Abb. 6: Fertiger PivotTable-Bericht
Abb. 6: Fertiger PivotTable-Bericht

Filter & Fehler

Standardmäßig zeigt der PivotTable-Bericht alle Inhalte an. Wenn Sie aber z.B. einzelne Produkte ausblenden wollen, steht Ihnen dafür eine sehr leistungsfähige Filterfunktion zur Verfügung: Wenn Sie im PivotTable-Bericht auf den Pfeil rechts neben dem Feld Produkt klicken, wird eine Auswahlliste aller Produkte angezeigt. Innerhalb dieser Auswahlliste können Sie jedes beliebige Produkt deaktivieren, indem Sie in das davor befindliche Kästchen klicken. Das Hakerl wird entfernt, und sobald Sie Ihre Wahl mit OK bestätigt haben, scheint das entsprechende Produkt nicht mehr im PivotTable-Bericht auf.

Bevor wir einen Schritt weitergehen, noch ein Hinweis zur Fehlerkorrektur bei falscher Positionierung von Feldern: Wenn der Fehler soeben passiert ist, können Sie ihn mit der Schaltfläche Rückgängig korrigieren. Liegt der Irrtum schon länger zurück oder bringt der PivotTable-Bericht nicht das gewünschte Ergebnis, besteht die Möglichkeit, falsch positionierte bzw. falsch gewählte Felder einfach zu löschen. Dazu klicken Sie im PivotTable-Bericht den Feldnamen an und ziehen ihn bei gedrückter linker Maustaste aus dem PivotTable-Bericht heraus. Bei diesem Vorgang wird wieder zusätzlich zu Ihrem Mauszeiger ein grauer Balken angezeigt, der das gewählte Feld symbolisiert. Sobald dieser den PivotTable-Bericht hinter sich gelassen hat, kennzeichnet ein rotes X den Löschvorgang.

Nicht immer müssen falsch positionierte Felder gelöscht werden - man kann innerhalb eines PivotTable-Berichts auch sehr einfach Felder verschieben: Anklicken, linke Maustaste gedrückt halten und das Feld an die gewünschte Stelle schieben, Maustaste loslassen, fertig.

Feinschliff

Falls Sie nicht nur an den verkauften Stückzahlen, sondern auch an der prozentuellen Verteilung der Produkte je Verkäufer interessiert sind, hilft ein Doppelklick auf das Feld Summe von Anzahl: Daraufhin erscheint das Dialogfenster PivotTable-Feld. Klicken Sie auf die Schaltfläche Optionen, um dieses Dialogfenster zu erweitern (siehe Abb. 7). Öffnen Sie nun die Auswahlliste Daten zeigen als mit Hilfe des kleinen Pfeils daneben und wählen Sie den Eintrag % der Spalte. Im Bereich Datenfelder werden jetzt anstelle der absoluten Zahlen die entsprechenden Prozentwerte angezeigt.

Abb. 7: Optionen für PivotTable-Feld
Abb. 7: Optionen für PivotTable-Feld

Im nächsten Schritt wollen wir den Verkäufer mit dem höchsten Umsatz ausfindig machen. Dazu können sowohl die Zeilenfelder als auch die Spaltenfelder bestehen bleiben; lediglich der Bereich Datenfelder wird den neuen Vorgaben angepaßt. Dafür muß zuerst das Feld Summe von Anzahl aus dem PivotTable-Bericht gezogen werden. Dann wird das Feld Umsatz in ATS aus der PivotTable-Feldliste in den Bereich Datenfelder
gezogen.

Der modifizierte PivotTable-Bericht zeigt nun den Umsatz pro Verkäufer an, gegliedert anhand der einzelnen Produkte. Am Ende jeder Spalte findet sich eine Gesamtsumme, mit deren Hilfe der erfolgreichste Verkäufer schnell ermittelt werden kann. (Das Zahlenformat ist allerdings etwas gewöhnungsbedürftig und sollte angepaßt werden, indem Sie alle betreffenden Spalten markieren und über das Menü Format - Zellen und die Registerkarte Zahlen die gewünschte Darstellung definieren.)

Selbstverständlich ist es auch möglich, innerhalb der Spalten- oder der Zeilenfelder mehrere Felder zu schachteln. Diese Funktion benötigen Sie, wenn Sie zusätzlich zum fleißigsten Verkäufer auch noch die ertragreichste Filiale ausfindig machen wollen. Dazu klicken Sie in der PivotTable-Feldliste auf den Eintrag Filiale und positionieren dieses Feld im Bereich Spaltenfelder vor dem Feld Verkäufer. Der auf diese Weise modifizierte PivotTable-Bericht ist in Abb. 8 ersichtlich: Wie man sehr deutlich erkennen kann, führt Filiale A mit einem Umsatz von ATS 3.802.600,00.

Abb. 8: PivotTable-Bericht - Auswertung nach Filialen
Abb. 8: PivotTable-Bericht - Auswertung nach Filialen

Auch hier besteht wiederum die Möglichkeit, nicht benötigte Details rasch und einfach auszublenden. Nehmen wir an, die einzelnen Verkäufer der Filialen sollen nicht angezeigt werden. Ein Klick mit der rechten Maustaste auf die Zelle mit dem Eintrag A öffnet das Kontextmenü. Dort finden Sie unter anderem die Option Gruppierung und Detail anzeigen. Sobald Sie diese Option anklicken, klappt ein Untermenü auf, in dem Sie den Eintrag Detail ausblenden wählen müssen. Daraufhin wird das Detail (d.h. die einzelnen Verkäufer der Filiale A) ausgeblendet. Verfahren Sie nun auf die gleiche Art und Weise mit Filiale B, und Sie erhalten als Ergebnis eine Gegenüberstellung der beiden Filialen.

Wenn Sie hingegen ganz genau wissen möchten, wie sich die Umsätze der Filiale A zusammensetzen, doppelklicken Sie einfach auf das Gesamtergebnis für Filiale A. Daraufhin wird automatisch ein neues Tabellenblatt angelegt, das die gewünschten Details auflistet (siehe Abb. 9).

Abb. 9: PivotTable-Bericht - Detailwerte
Abb. 9: PivotTable-Bericht - Detailwerte

Schon diese kurze Einführung in das doch sehr umfangreiche Kapitel der PivotTable-Berichte macht deutlich, daß sich diese vor allem durch ein hohes Maß an Flexibilität auszeichnen: Felder können jederzeit eingebunden und ebenso rasch wieder entfernt werden; geraffte Ansichten, aber auch Detailansichten stehen auf Knopfdruck zur Verfügung. Unser Tip: Experimentieren Sie einfach munter drauflos - Sie werden dabei sicher auf zahlreiche nützliche Kombinationen stoßen, um Ihre Daten optimal zu strukturieren und auszuwerten.