Pivot-Tabellen, Gruppierung und ein mysteriöser Cache 4

Vor einiger Zeit hatte ich einen Artikel veröffentlicht, wie man Werte in Pivot-Tabellen gruppieren kann (falls du diesen Artikel verpasst hast, guckst du hier: Werte gruppieren in Pivot-Tabellen). Und darin hatte ich das Problem erwähnt, das sich in diesem Zusammenhang ergeben kann, wenn man mehrere Pivot-Tabellen nutzt. Gruppierungen gelten nämlich gleichermaßen für alle Pivot-Tabellen, die auf den gleichen Quelldaten aufbauen. Und das ist manchmal unerwünscht und sehr nervig.

Wie man dieses Problem jedoch umgehen kann, beschreibe ich im heutigen Beitrag.

Und so geht’s:

Zwei Seiten einer Medaille

Wenn man aus einer Datentabelle mehrere Pivot-Tabellen erstellt, dann hat das einige Vorteile. Eine Änderung der Quelldaten wirkt sich auf sämtliche Pivot-Tabellen aus. Und wenn ich in einer Tabelle beispielsweise ein berechnetes Feld erstelle oder Werte gruppieren, dann ist dieses Feld oder die vorgenommene Gruppierung ebenfalls automatisch in allen Pivot-Tabellen verfügbar. Der Grund dafür ist, dass alle Pivot-Tabellen den gleichen Pivot-Cache verwenden.

Der Pivot-Cache (sprich: Käsch) ist ein spezieller Speicherbereich, der im Hintergrund erstellt wird, wenn man die erste Pivot-Tabelle erzeugt. Die Daten aus der Quelltabelle werden dabei in diesen Cache kopiert und die Pivot-Tabelle greift anschließend auf diesen Cache zu, und nicht direkt auf die ursprüngliche Datenquelle.

Ein Pivot-Cache bedient mehrere Pivot-Tabellen

Das verbessert die Performance, weil der Zugriff auf den Cache um ein Vielfaches schneller ist. Und das ist auch der Grund, warum man Pivot-Tabellen explizit aktualisieren muss, wenn sich die Quelldaten verändert haben. Durch die Aktualisierung wird der Cache neu aufgebaut und alle Pivot-Tabellen werden mit den neuen Daten versorgt, auch wenn man die Aktualisierung nur in einer Pivot-Tabelle ausgeführt hat.

Dieser Vorteil des gemeinsamen Pivot-Caches wird aber zum Nachteil, wenn man beispielsweise für ein bestimmtes Feld unterschiedliche Gruppierungen vornehmen möchte. Denn das ist nicht möglich.

Oder vielleicht doch?

Die Lösung: Getrennte Pivot-Caches

Damit man eben doch mit verschiedenen Gruppierungen arbeiten kann, muss man dafür sorgen, dass jede Pivot-Tabelle ihren eigenen Pivot-Cache erhält. Wer jetzt aber nach einer solchen Möglichkeit in den PivotTable-Optionen sucht, den muss ich enttäuschen: Eine solche einfache Einstellung gibt es dort leider nicht.

Stattdessen muss man auf eine längst vergessene Funktion aus früheren Excel-Tagen zurückgreifen. Dort gab es nämlich den sogenannten PivotTable-Assistenten (Excel-Anwender aus grauer Vorzeit werden sich noch daran erinnern 🙂 ). Um an diesen Assistenten auch in aktuellen Excel-Versionen heranzukommen, gibt es zwei Möglichkeiten:

Möglichkeit 1: Schnellzugriffsleiste anpassen

Symbolleiste für Schnellzugriff anpassen

Im Fenster mit den Befehlen wählt man zuerst oben den Eintrag „Nicht im Menüband enthaltene Befehle“ aus und markiert dann unten in der Liste der Befehle den Eintrag „Pivot-Table- und PivotChart-Assistent“. Über die Hinzufügen-Schaltfläche wird der Eintrag in die rechte Liste übernommen. Wenn man das Fenster mit OK geschlossen hat, kann man oben in der Schnellzugriffsleiste über das neue Symbol den Assistenten aufrufen:

Eintrag für PivotTable-Assistent übernehmen

Möglichkeit 2: Die Tastenfolge Alt+N, P
Dazu drückt man zuerst gemeinsam die Tasten Alt + N und lässt dann die beiden Tasten wieder los. Danach drückt man P und der Assistent wird aufgerufen:

So sieht er aus: PivotTable-Assistent

Der PivotTable-Assistent

Bevor wir jedoch den Assistenten bemühen, erstellen wir erst einmal auf dem normalen Weg unsere erste Pivot-Tabelle. Dazu kannst du auch meine Beispieldatei herunterladen. Sie enthält eine formatierte Tabelle namens „tblAuftrag“, aus der ich bereits eine Pivot-Tabelle erstellt habe.

Beispiel: Pivot-Tabelle mit gruppiertem Feld

Die Pivot-Tabelle enthält außerdem schon eine Gruppierung nach dem Auftragswert, und zwar in 250er-Schritten (wenn du wissen möchtest, wie man eine solche Gruppierung erstellt, dann kannst du das in diesem Artikel nachlesen).

Als nächstes wollen wir eine weitere Pivot-Tabelle mit einer Gruppierung nach Auftragswert erstellen, diesmal aber mit 500er-Schritten. Und um das zu bewerkstelligen, brauchen wir den PivotTable-Assistenten, den du jetzt über eine der beiden oben beschriebenen Möglichkeiten aufrufst.

Im ersten Fenster des Assistenten (= Schritt 1 von 3) werden die vorgegebenen Einstellungen unverändert übernommen. Also oben „Microsoft Excel-Liste oder -Datenbank“ und unten „PivotTable“:

PivotTable-Assistent: Schritt 1 von 3

Im Schritt 2 musst du nur prüfen, dass der richtige Bereich erkannt wurde. Danach klickst du wieder auf „Weiter“.

PivotTable-Assistent: Schritt 2 von 3

Vorsicht, Falle:
Solltest du mit deinen eigenen Beispielen arbeiten und die Quelldaten liegen nicht in einer formatierten Tabelle, sondern in einer einfachen Liste, dann erhältst du nach dem zweiten Schritt die folgende Meldung:
„Der neue Bericht wird weniger Speicher benötigen, wenn als Datenquelle die bestehende PivotTable angegeben wird, die aus derselben Datenquelle erstellt wurde. Soll der neue Bericht auf den gleichen Daten wie der bestehende Bericht basieren?“

Warnung bei einfachen Listen

Diese Meldung musst du unbedingt mit „Nein“ bestätigen! Nur dann wird ein eigener Pivot-Cache erstellt!
Wenn es sich bei der Datenquelle jedoch um eine formatierte Tabelle handelt, wird Excel dich nicht mit dieser Meldung behelligen und automatisch einen zweiten Cache erstellen (ein weiterer Grund, warum es sich lohnt, mit formatierten Tabellen zu arbeiten)

Im dritten und letzten Schritt musst du nur entscheiden, ob die neue Pivot-Tabelle in einem neuen oder in einem bestehenden Arbeitsblatt erstellt werden soll. Ich habe mich für das bestehende Blatt entschieden und wähle eine Zelle unterhalb der vorhandenen Pivot-Tabelle aus:

PivotTable-Assistent: Schritt 2 von 3

Nun können wir uns an den Aufbau der Pivot-Tabelle machen. Dazu ziehe ich den Auftragswert in den Zeilenbereich, die Auftragsnummer und auch den Auftragswert nochmal in den Spaltenbereich, um folgende Darstellung zu erhalten:

Die neue Pivot-Tabelle

Jetzt kommt die neue Gruppierung. Stelle die aktive Zelle auf einen einzelnen Wert in der ersten Spalte der Pivot-Tabelle (nicht auf die Überschrift!) und wähle dann aus den PivotTable-Tools den Punkt „Feld gruppieren“:

Neue Gruppierung einrichten

Im Gruppierungsfenster ändere ich die vorgeschlagenen Werte auf 0, 3000 und 500:

Gruppierung anpassen

Und nach einem Klick auf OK wirst du sehen, dass die neue Gruppierung in 500er-Schritte funktioniert und die alte Gruppierung in der ersten Pivot-Tabelle in 250er-Schritten ebenfalls weiterhin gültig ist:

Zwei Pivot-Tabellen mit unterschiedlichen Gruppierungen

Ohne den Trick mit dem PivotTable-Assistenten und dem zweiten Pivot-Cache wäre das nicht möglich gewesen!

Alles hat seinen Preis

So praktisch dieser Kniff ist, sollen die damit verbundenen Nachteile nicht verschwiegen werden:
Jeder Pivot-Cache vergrößert den Speicherbedarf der Arbeitsmappe. Auch bei nur einem Pivot-Cache hat sich die Dateigröße bereits erhöht, ein weiterer Cache wirkt sich hier zusätzlich negativ aus. Wenn die Quelltabelle also extrem umfangreich ist, könnte das schon ins Gewicht fallen.

Außerdem lassen sich Datenschnitte und Zeitachsen für Pivot-Tabellen mit getrennten Pivot-Caches nicht mehr gemeinsam nutzen.

Wenn du mit diesen Nachteilen leben kannst, kann die vorgestellte Technik jedoch sehr hilfreich sein.
 

Das könnte dich auch interessieren:

Vorher schon wissen, was hinterher rauskommt

Eingabemöglichkeiten einschränken

Dem Verbrecher auf der Spur

Das doppelte Lottchen, oder: Dublettensuche mit Excel