Pivot-Cache in Excel - Was ist das und wie wird es am besten verwendet?

Wenn Sie mit Excel-Pivot-Tabellen arbeiten, sollten Sie Pivot Cache unbedingt kennen.

Was ist Pivot-Cache?

Pivot-Cache wird automatisch generiert, wenn Sie eine Pivot-Tabelle erstellen.

Es ist ein Objekt, das ein Replikat der Datenquelle enthält. Sie können es zwar nicht sehen, aber es ist ein Teil der Arbeitsmappe und mit der Pivot-Tabelle verbunden. Wenn Sie Änderungen an der Pivot-Tabelle vornehmen, verwendet diese nicht die Datenquelle, sondern den Pivot-Cache.

Der Grund, warum ein Pivot-Cache generiert wird, besteht darin, die Funktionsweise der Pivot-Tabelle zu optimieren. Selbst wenn Sie Tausende von Datenzeilen haben, fasst eine Pivot-Tabelle sie superschnell zusammen. Sie können Elemente per Drag & Drop in die Felder Zeilen/Spalten/Werte/Filter ziehen und die Ergebnisse werden sofort aktualisiert.

Pivot Cache ermöglicht dieses schnelle Funktionieren einer Pivot-Tabelle.

Während Sie denken, dass Sie direkt mit den Quelldaten verknüpft sind, greifen Sie in Wirklichkeit auf den Pivot-Cache (und nicht auf die Quelldaten) zu, wenn Sie Änderungen in der Pivot-Tabelle vornehmen.

Dies ist auch der Grund, warum Sie die Pivot-Tabelle aktualisieren müssen, um alle im Dataset vorgenommenen Änderungen widerzuspiegeln.

Nebenwirkungen von Pivot-Cache

Ein Nachteil des Pivot-Cache ist, dass er die Größe Ihrer Arbeitsmappe erhöht.

Da es sich um ein Replikat der Quelldaten handelt, wird beim Erstellen einer Pivot-Tabelle eine Kopie dieser Daten im Pivot-Cache gespeichert.

Wenn Sie große Datensätze zum Erstellen einer Pivot-Tabelle verwenden, erhöht sich die Dateigröße der Arbeitsmappe erheblich.

Teilen des Pivot-Cache

Wenn Sie bereits über eine Pivot-Tabelle verfügen und eine zusätzliche Pivot-Tabelle mit denselben Quelldaten erstellen, teilt Excel ab Excel 2007 automatisch den Pivot-Cache (d. h., beide Pivot-Tabellen verwenden denselben Pivot-Cache). Dies ist hilfreich, da es die Duplizierung des Pivot-Cache vermeidet und wiederum zu einer geringeren Speichernutzung und einer geringeren Dateigröße führt.

Einschränkungen des freigegebenen Pivot-Cache

Während ein gemeinsam genutzter Pivot-Cache die Funktionsweise von Pivot-Tabellen und die Speichernutzung verbessert, leidet er unter den folgenden Einschränkungen:

• Wenn Sie eine Pivot-Tabelle aktualisieren, werden alle mit demselben Cache verknüpften Pivot-Tabellen aktualisiert.
• Wenn Sie Felder in einer der Pivot-Tabellen gruppieren, wird sie auf alle Pivot-Tabellen angewendet, die denselben Pivot-Cache verwenden. Wenn Sie beispielsweise Datumsangaben nach Monaten gruppieren, wird diese Änderung in allen Pivot-Tabellen berücksichtigt.
• Wenn Sie ein berechnetes Feld/Element in eine der Pivot-Tabellen einfügen, wird es in allen Pivot-Tabellen angezeigt, die den Pivot-Cache gemeinsam nutzen.

Um diese Einschränkungen zu umgehen, müssen Sie Excel zwingen, einen separaten Pivot-Cache für verschiedene Pivot-Tabellen zu erstellen (bei Verwendung derselben Datenquelle).

Hinweis: Wenn Sie verschiedene Datenquellen für verschiedene Pivot-Tabellen verwenden, generiert Excel automatisch separate Pivot-Caches dafür.

Erstellen eines doppelten Pivot-Cache (mit derselben Datenquelle)

Es gibt drei Möglichkeiten, einen doppelten Pivot-Cache zu erstellen, während Pivot-Tabellen aus derselben Datenquelle erstellt werden:

#1 Verschiedene Tabellennamen verwenden

• Klicken Sie auf eine beliebige Stelle in der Datenquelle und gehen Sie zu Einfügen -> Tabelle (oder Sie können die Tastenkombination verwenden - Strg + T).
• Klicken Sie im Dialogfeld Tabelle erstellen auf OK. Es erstellt eine Tabelle mit dem Namen Table1.
• Wenn eine beliebige Zelle in der Tabelle ausgewählt ist, gehen Sie zu Einfügen -> Pivot-Tabelle.
• Im Dialogfeld Pivot-Tabelle erstellen würden Sie feststellen, dass im Feld Tabelle/Bereich der Name der Tabelle steht. OK klicken.
  • Dadurch wird die erste Pivot-Tabelle erstellt.
• Gehen Sie zur Datenquelle (Tabelle), wählen Sie eine beliebige Zelle aus und gehen Sie zu Tabellentools Design -> Tools -> In Bereich konvertieren. Es wird eine Eingabeaufforderung angezeigt, in der Sie gefragt werden, ob Sie die Tabelle in den Normalbereich konvertieren möchten. Klicken Sie auf Ja. Dadurch wird die Tabelle in reguläre Tabellendaten umgewandelt.

Wiederholen Sie nun die obigen Schritte und ändern Sie einfach den Tabellennamen (von Table1 in Table2 oder was auch immer Sie wollen). Sie können ihn ändern, indem Sie den Namen in das Feld unter dem Tabellennamen auf der Registerkarte Tabellentools-Design eingeben.

Obwohl beide Tabellen (Table1 und Table2) auf dieselbe Datenquelle verweisen, stellt diese Methode sicher, dass für jede Tabelle zwei separate Pivot-Caches generiert werden.

#2 Verwenden des Assistenten für alte Pivot-Tabellen

Verwenden Sie diese Schritte, wenn Sie eine zusätzliche Pivot-Tabelle mit einem separaten Pivot-Cache erstellen möchten, während Sie dieselbe Datenquelle verwenden.

• Wählen Sie eine beliebige Zelle in den Daten aus und drücken Sie ALT + D + P.
  • Dadurch wird der Pivot-Tabellen- und Pivot-Diagramm-Assistent geöffnet.
• Klicken Sie in Schritt 1 von 3 auf Weiter.
• Stellen Sie in Schritt 2 von 3 sicher, dass der Datenbereich korrekt ist und klicken Sie auf Weiter.
• Excel zeigt eine Eingabeaufforderung an, die im Wesentlichen sagt, klicken Sie auf Ja, um einen gemeinsamen Pivot-Cache zu erstellen, und auf Nein, um einen separaten Pivot-Cache zu erstellen.
• Klicken Sie auf Nein.
• Wählen Sie in Schritt 3 des Assistenten aus, ob die Pivot-Tabelle in einem neuen Arbeitsblatt oder demselben Arbeitsblatt angezeigt werden soll, und klicken Sie dann auf Fertig stellen.

Hinweis: Stellen Sie sicher, dass die Daten keine Excel-Tabelle sind.

Zählen Sie die Anzahl der Pivot-Caches

Sie können die Anzahl der Pivot-Caches zählen, nur um zu vermeiden, dass mehrere Pivot-Caches aus derselben Datenquelle stammen.

Hier ist eine schnelle Möglichkeit, es zu zählen:

• Drücken Sie ALT + F11, um den VB-Editor zu öffnen (oder gehen Sie zur Registerkarte Entwickler -> Visual Basic).
• Klicken Sie im Menü des Visual Basic-Editors auf Ansicht und wählen Sie Direktes Fenster (oder drücke Strg + G). Dadurch wird das Direktfenster sichtbar.
• Fügen Sie im Direktfenster den folgenden Code ein und drücken Sie die Eingabetaste:

  ?ActiveWorkbook.PivotCaches.Count

Es wird sofort die Anzahl der Pivot-Caches in der Arbeitsmappe angezeigt.

Verbessern der Leistung beim Arbeiten mit Pivot-Tabellen

Es gibt einige Dinge, die Sie tun können, um die Leistung von Arbeitsmappen (Dateigröße und Speichernutzung) zu verbessern, während Sie mit Pivot-Tabellen arbeiten:

#1 Löschen Sie die Quelldaten

Sie können die Quelldaten löschen und nur den Pivot-Cache verwenden. Sie können weiterhin alles mit dem Pivot-Cache tun, da er eine Momentaufnahme der Originaldaten enthält. Da Sie jedoch die Quelldaten gelöscht haben, würde sich die Größe Ihrer Arbeitsmappendatei verringern.

Wenn Sie die Quelldaten wiederherstellen möchten, doppelklicken Sie einfach auf den Schnittpunkt der Gesamtsummen für diese Pivot-Tabelle. Es erstellt ein neues Arbeitsblatt und zeigt alle Daten an, die zum Erstellen dieser Pivot-Tabelle verwendet wurden.

#2 Speichern Sie die Daten nicht im Pivot-Cache

Wenn Sie eine Datei mit einer Pivot-Tabelle und Quelldaten speichern, wird auch der Pivot-Cache gespeichert, der eine Kopie der Quelldaten enthält. Das bedeutet, dass Sie die Quelldaten an zwei Stellen speichern: im Arbeitsblatt, das die Daten enthält, und im Pivot-Cache.

Es besteht die Möglichkeit, die Daten nicht im Cache zu speichern und zu schließen. Dies führt zu einer geringeren Dateigröße.

Um dies zu tun:

• Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
• Gehen Sie zu Analysieren -> Pivot-Tabelle -> Optionen.
• Wechseln Sie im Dialogfeld Pivot-Tabellenoptionen zur Registerkarte Daten.
• Deaktivieren Sie die Option - Quelldaten mit Datei speichern.
• Aktivieren Sie die Option - Daten beim Öffnen der Datei aktualisieren.
  • Wenn Sie diese Option nicht aktivieren, werden die Daten beim Öffnen der Excel-Arbeitsmappe nicht aktualisiert und Sie können die Pivot-Tabellenfunktionen nicht verwenden. Damit es funktioniert, müssen Sie die Pivot-Tabelle manuell aktualisieren.

Wenn Sie dies tun, speichert Excel die Daten nicht im Pivot-Cache, sondern aktualisiert sie, wenn Sie die Excel-Arbeitsmappe das nächste Mal öffnen. Ihre Daten können sich in derselben Arbeitsmappe, einer anderen Arbeitsmappe oder einer externen Datenbank befinden. Wenn Sie die Datei öffnen, werden die Daten aktualisiert und der Pivot-Cache wird neu erstellt.

Dies kann zwar zu einer geringeren Dateigröße führen, das Öffnen der Datei kann jedoch etwas länger dauern (da Excel den Cache neu erstellt).

Siehe auch: Quelldaten mit Pivot-Tabelle speichern.

Hinweis: Wenn Sie diese Option verwenden, stellen Sie sicher, dass die Datenquelle intakt ist. Wenn Sie die Quelldaten (aus der Arbeitsmappe oder einer externen Datenquelle) löschen, können Sie den Pivot-Cache nicht neu erstellen.

#3 Den Pivot-Cache für eine bessere Leistung freigeben

Wenn Sie versehentlich (oder absichtlich) in eine Situation geraten, in der Sie einen doppelten Pivot-Cache haben und das Duplikat löschen und den Pivot-Cache freigeben möchten, gehen Sie wie folgt vor:

• Löschen Sie eine der Pivot-Tabellen, für die Sie den Cache löschen möchten. Wählen Sie dazu die Pivot-Tabelle aus und gehen Sie zu Home -> Clear -> Clear All.
• Kopieren Sie nun einfach die Pivot-Tabelle, die Sie duplizieren möchten, und fügen Sie sie ein (entweder in dasselbe Arbeitsblatt oder in ein separates Arbeitsblatt).
  • Es wird empfohlen, sie in separate Arbeitsblätter einzufügen, damit sie sich beim Erweitern nicht mit der anderen Pivot-Tabelle überschneidet. Obwohl ich es manchmal nebeneinander kopiere, um verschiedene Ansichten zu vergleichen. Dieses Kopieren und Einfügen der Pivot-Tabelle stellt sicher, dass der Pivot-Cache gemeinsam genutzt wird.

• Microsoft-Hilfe – Aufheben der Freigabe eines Datencaches zwischen PivotTable-Berichten.

Andere Pivot-Tabellen-Tutorials, die Ihnen gefallen könnten:

• Vorbereiten von Quelldaten für Pivot-Tabelle.
• So gruppieren Sie Datumsangaben in Pivot-Tabellen in Excel.
• So gruppieren Sie Zahlen in der Pivot-Tabelle in Excel.
• So aktualisieren Sie die Pivot-Tabelle in Excel.
• Verwenden von Slicern in der Excel-Pivot-Tabelle.
• So fügen Sie ein berechnetes Feld für eine Excel-Pivot-Tabelle hinzu und verwenden es.
• So wenden Sie die bedingte Formatierung in einer Pivot-Tabelle in Excel an.