Aus der Excel-Trickkiste

Mit diesen Spezialtechniken holen Sie mehr aus Ihren Pivot-Tabellen

Pivot-Tabellen sind das mächtigste Werkzeug von Microsoft Excel, um Daten zu aggregieren, komprimieren und zu analysieren. Schon mit wenigen Mausklicks können Anwender standardisierte Pivot-Tabellen erstellen. Excel bietet jedoch noch mehr. Ignatz Schels zeigt Ihnen Spezialtechniken, mit denen Sie die volle Leistungsfähigkeit dieses Werkzeugs für sich nutzbar machen.
Aus der Excel-Trickkiste

Mit diesen Spezialtechniken holen Sie mehr aus Ihren Pivot-Tabellen

Pivot-Tabellen sind das mächtigste Werkzeug von Microsoft Excel, um Daten zu aggregieren, komprimieren und zu analysieren. Schon mit wenigen Mausklicks können Anwender standardisierte Pivot-Tabellen erstellen. Excel bietet jedoch noch mehr. Ignatz Schels zeigt Ihnen Spezialtechniken, mit denen Sie die volle Leistungsfähigkeit dieses Werkzeugs für sich nutzbar machen.

Die Pivot-Tabelle ist der unumstrittene Star der Analyse-Werkzeuge von Microsoft Excel. Keine Funktion kann so effektiv Daten analysieren, aggregieren und komprimieren wie die Kreuztabelle (so der frühere Name der mittlerweile mit "PivotTable" bezeichneten Funktion). Der französische Begriff "Pivot" kommt aus der Militärtechnik. Pivoter heißt drehen, ein Pivot ist ein beweglicher Zapfen, auf dem Geschütze positioniert werden. In der Datenverarbeitung kam die PivotTable zum ersten Mal für die Darstellung von Wertpapierentwicklungen zum Einsatz. Excel bietet seit der Version 5.0 PivotTables an, um größere Datenmengen zusammenzufassen, zu gruppieren und die Ergebnisse in Zeilen und Spalten anzuordnen.

Dieser Beitrag richtet sich vor allem an Excel-Anwender, die bereits mit PivotTables arbeiten. Eine Einführung in das Thema Pivot mit einer Anleitung, wie Sie PivotTables und PivotCharts zur Auswertung von Projektkennzahlen verwenden können, liefert der zweiteilige Beitrag "Keine Angst vor Pivot – Projektkennzahlen flexibel auswerten" (Projekt Magazin, Ausgabe 24/2012).

Die Datenquelle dynamisch einbinden

Die Basis einer PivotTable ist die Datenquelle und das sind in der Praxis Daten aus einer Liste oder einer Tabelle. Das Standardverfahren zum Anlegen einer PivotTable verführt leider dazu, die Zellbezüge zu dieser Datenquelle mit $-Zeichen neben Zeilennummern und Spaltenbuchstaben absolut zu setzen, was jedoch Risiken mit sich bringt. Denn neue Datensätze oder zusätzliche Spalten ignoriert die PivotTable in diesem Fall. Meist wird der Anwender nicht einmal darauf hingewiesen, dass seine Analysedaten veraltet sind. Es gibt jedoch gute Alternativen, um die Datenquelle so einzubinden, dass neue Daten automatisch berücksichtigt werden.

Risiko Datenquelle – ein Beispiel

Der Beispielplan in Bild 1, der als Liste in einem Excel-Tabellenblatt vorliegt, soll Datenquelle für eine PivotTable sein, um Plan- und Ist-Kosten für die einzelnen Abteilungen zu ermitteln.

Bild 1: Der Beispielprojektplan mit Kostenberechnung als Datenquelle für eine PivotTable.

Bild 1: Der Beispielprojektplan mit Kostenberechnung als Datenquelle für eine PivotTable.
Bild vergrößern

Um die PivotTable zu erstellen, gehen Sie üblicherweise wie folgt vor:

  1. Mit dem Zellzeiger in der Liste wählen Sie Einfügen / Tabellen / PivotTable. Ein Dialogfenster mit der Aufforderung "Wählen Sie die zu analysierenden Daten aus" erscheint.
  2. Die erste Option Tabelle oder Bereich auswählen ist aktiv, der Listenbereich ($A$1:$I$11) wird als zu analysierender Bereich angeboten.
  3. Als Ziel für die PivotTable ist Neues Arbeitsblatt vorbelegt. Durch einen Klick auf OK erstellt Excel die PivotTable.
  4. Ziehen Sie in der Feldliste ("PivotTable-Felder", Bild 2) das Feld Abteilung in den Bereich Zeilen und die drei Kostenfelder in den Bereich Werte. Die Daten werden automatisch mit der Funktion SUMME aggregiert, das Ergebnis zeigt die Kosten für die einzelnen Abteilungen.
Bild 2: Die fertige PivotTable mit zugehöriger Feldliste. Standardmäßig setzt Excel absolute Zellbezüge zur Datenquelle.

Bild 2: Die fertige PivotTable mit zugehöriger Feldliste. Standardmäßig setzt Excel absolute Zellbezüge zur Datenquelle.
Bild vergrößern

Wenn Sie die Quelldaten jetzt wie in Bild 3 gezeigt um einen weiteren Datensatz ergänzen (Zeile 12) und die PivotTable anschließend aktualisieren (PivotTable-Tools / Analysieren / Daten bzw. in Excel 2010: PivotTable-Tools / Optionen / Daten), zeigt Excel den neuen Datensatz in der PivotTable nicht an. Es erscheint auch kein Hinweis darauf, dass sich die Daten geändert haben.

Bild 3: Ergänzt man den Beispiel-Projektplan nachträglich (Zeile 12), zeigt Excel die neuen Daten in der PivotTable nicht an.

Bild 3: Ergänzt man den Beispiel-Projektplan nachträglich (Zeile 12), zeigt Excel die neuen Daten in der PivotTable nicht an.

Dass Excel den neuen Datensatz nicht berücksichtigt, erkennen Sie auch, wenn Sie auf das Symbol Datenquelle ändern unter PivotTable-Tool / Analysieren / Daten klicken. Dort wird nach wie vor der ursprüngliche Datenbereich $A$1 bis I$11angezeigt – die neuen Daten in Zeile 12 sind nicht eingeschlossen.

Ganz falsch: Leere Datensätze und Spaltenbezüge

Wer jetzt auf die Idee kommt, die Datenquelle um einige Leerzeilen zu erweitern, um für spätere Ergänzungen vorzusorgen, nimmt in Kauf, dass die Leerzeilen ständig herausgefiltert werden müssen und sich zudem PivotCharts damit nicht mehr sauber zeichnen lassen.

Auch die Idee, der PivotTable gleich die ganzen Spalten für die Auswertung anzubieten, sollte schnell verworfen werden. Außer den gerade genannten Nachteilen müsste Excel in diesem Fall mit 1.048.576 Zeilen pro Spalte eine große Menge nicht gebrauchter Zellen adressieren, was sich stark auf die Performance und den Speicherbedarf der Arbeitsmappe auswirkt.

Mit den nachfolgend beschriebenen Lösungen vermeiden Sie solche absoluten Bezüge zur Datenquelle.

Dynamischen Bereichsnamen verwenden

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 1
Alle anzeigen