öffentlich
Redaktion Druckversion

VBA für Einsteiger - MS Excel 2003 (2)

Listen auswerten

Perfektes Arbeiten mit dem Pivot-Tabellen-Bericht

Allgemeines

Eine Pivot-Tabelle, Pivot-Table oder Pivottabellenbericht (Pivot, engl. s.v.w. Drehpunkt) aus einer Liste zu erstellen ist eine weitere sehr anspruchsvolle Art und Weise, Datenbestände auszuwerten. Diese anspruchsvolle Auswertung benötigt Ihre besondere Aufmerksamkeit, liefert dafür aber angesichts der vielen Möglichkeiten überraschende Ergebnisse.

Dabei werden zunächst die unterschiedlichen Einträge der Felder in Zeilen und/oder Spalten eingetragen. In deren Kreuzungspunkten erscheinen die Daten von Datenfeldern, die mit Funktionen zusammengefasst werden.

Die Felder sind Spalten jedes Datensatzes der auszuwertenden Liste, wie im folgenden Bild anhand der Datei Produkte.xls bzw. Lösung Produkte.xls zu sehen.

Teil der Tabelle

Wollen Sie aber diese Felder zueinander in Beziehung bringen (z.B. welche Produkte haben die jeweiligen Verkäufer mit welchem Umsatz verkauft?), so definiert man für die Pivottabelle die Produkte als Spaltenfelder und die Verkäufer als Zeilenfelder. In den sich kreuzenden Zuordnungen im Datenbereich setzen Sie die Zahlen von Umsatz und fassen sie mit der Funktion Summe zusammen:

Zum Aufbau einer Pivottabelle

Ergebnis des fertigen Pivottabellenberichts

Den unterschiedlichen Einträgen für das Feld Produkt (Fisch und Gemüse) werden die Umsätze pro unterschiedliche Einträge im Feld Verkäufer (Kaufmann, Krüger und Lehmann) zugeordnet.
Die Gesamtergebnisse ergeben sich aus der ausgewählten Zusammenfassungsfunktion (z.B. Summe). Das alles kann noch zusätzlich dreidimensional pro einzelnes Jahr (als sog. Seitenfeld ausgewählt) dargestellt werden. Bei Alle können Sie die jeweilig einzeln aufgeführten Jahre (2005, 2006) auswählen.

Bereits bestehende Pivottabellen lassen sich recht einfach umfangreich bearbeiten.

Sie können weitere Felder nachträglich hinzufügen.
Als Voraussetzung muss sich die aktive Zelle in der Pivottabelle befinden.
Mit der Symbolleiste PivotTable können Sie über das Symbol Feldliste ausblenden/einblenden
weitere Felder in die Pivottabelle aus der angezeigten Feldliste ziehen.

Mit Feldliste Pivottabelle nachträglich verändern

Ein und Ausblenden der Feldinhalte erledigen Sie über Auswahl am Listensymbol.

Einträge in Feldern einer Pivottabelle nachträglich ein- und ausblenden

Anhand der Datei Personal.xls können Sie selbst einen Pivottabellenbericht erstellen.

Zunächst aktivieren Sie in der Tabelle Liste die Zelle A2. Wählen Sie anschließend "Daten" - "Pivot Table- und PivotChartbericht.." und in der erscheinenden Dialogbox "MS-Excel- Liste oder Datenbank..." und als Darstellung "PivotTable", " Weiter".

Excel erkennt automatisch im 2. Schritt den zu analysierenden Datenbereich. Wenn Sie mit der Datenauswahl einverstanden sind: "Weiter".

Erklären Sie im Schritt 3, dass der zu erstellende Pivottabellenbericht in einem neuen Arbeitsblatt erstellt werden soll, "Fertig stellen".

In einem neuen Arbeitsblatt wird die Struktur des zu erwarteten Pivottabellenberichts angezeigt. Eine Pivottabellen-Feldliste bietet alle vorhandenen Felder der Datentabelle zur Auswahl an. Ziehen Sie mit der Maus Abteilungsname in "Seitenfelder hierher ziehen", danach

Nachname in "Zeilenfelder hierher ziehen",

Tätigkeit in "Spaltenfelder hierher ziehen"

und Gehalt in "Datenfelder hierher ziehen".

Sie können den Datenbereich vollständig markieren und die Zellen in ein Währungsformat darstellen. Für Abteilungsname wählen Sie z.B. Buchhaltung.

Fertige Pivottabelle

Das Prinzip eines Pivottabellenberichts sei hiermit erklärt.

Wenn Sie die einzelnen Felder des Berichtes mit der rechten Maustaste anklicken, eröffnen sich Ihnen im Kontextmenü vielfältige Möglichkeiten zur Veränderung des Ergebnisses. Sie können etwa einzelne Felder ausblenden.

Pivottabellenfeld ausblenden

Das Einblenden von Gesamtergebnissen erfolgt über das Kontextmenü und "Tabellenoptionen".

Gesamtergebnis der Zeilenfelder in rechter Spalte einblenden

Pivottabellen haben ihre Grenzen im Speicherplatz

Über bestehende Einschränkungen macht Microsoft keine Angaben. Die Größe der Pivot-Tabelle hängt höchstens vom Arbeitsspeicher Ihres PC ab.

Für die Anzahl der Spaltenfelder gilt: Das Produkt aus der Anzahl der Elemente in allen Spaltenfeldern einer Pivottabelle darf maximal 32.768 betragen.

2 Spaltenfelder mit 1 und 3 Elementen

z.B. Eine Pivot-Tabelle besitzt fünf Spaltenfelder mit 10, 5, 2, 40 und 3 Elementen, das bedeutet 10*5*2*40*3 = 12 000 eine Erhöhung um ein weiteres Feld mit nur 3 Elementen würde das Produkt auf 36.000 vergrößern.

Für die Anzahl der Zeilenfelder gilt: Das Produkt der Anzahl an Elemente in allen Zeilenfeldern kann rund 2,1 Milliarden betragen!

Beispiel von 2 Spaltenfeldern mit 1 und 3 Elementen

Die Anzahl der Datensätze ist von der Kapazität des PC abhängig.

HINWEISE zum Optimieren des PC-Speicherbedarfs:

Sie können für die Pivottabellen Speicherplatz sparen. Dazu gibt es im Kontextmenü den Befehl "Tabellenoptionen". Dort finden Sie unter Optionen externe Daten die Auswahl den Befehl "Speicherplatz optimieren", wenn die Pivottabelle ihre Daten aus externer Quelle erhält.

Tipps: So sparen Sie auf Ihrem PC Speicherplatz

Stellen Sie sicher, dass die Pivottabelle aus nicht zu vielen Feldern besteht. Wenn es nicht anders geht, legen Sie die größte Anzahl der Felder als Zeilen- bzw. Seitenfelder an.

Wenn mehrere Pivot-Tabellen aus einer Datenquelle erstellt werden, so wähle man im Pivot-Tabellen-Assistenten die Option anderen Pivot-Tabellenbericht. Das verhindert eine weitere notwendige Kopie im Arbeitsspeicher.

Verringern Sie die Anzahl der Pivot-Tabellen in der Arbeitsmappe. Wenn die Pivot-Tabelle auf einer Excel-Arbeitsmappe basiert, erstellen Sie die Pivot-Tabelle nicht in der gleichen Arbeitsmappe. Dadurch wird verhindert, dass sich Liste und die Pivot-Tabelle gleichzeitig im Arbeitsspeicher befinden.

Mitglied werden, Vorteile nutzen!

  • Sie können alles lesen und herunterladen: Beiträge, PDF-Dateien und Zusatzdateien (Checklisten, Vorlagen, Musterbriefe, Excel-Rechner u.v.a.m.)
  • Unsere Autoren beantworten Ihre Fragen

Downloads zu diesem Beitrag

Über den Autor:

bild117282

Dieter Frommhold, Jahrgang 1943, Diplom-Lehrer für Physik und Mathematik, Verlagslektor und Programmierer. Seit 1971 langjährige Lehrtätigkeit über Betriebssysteme und Programmierung.

Ab 1994 arbe ...

Newsletter abonnieren