5 Dinge, die Sie über Pivot-Caches wissen sollten

Ist ein Controlling ohne Pivot-Tabelle vorstellbar? Seit die Pivot-Tabelle mit der Version Excel 5.0 eingeführt wurde, ist sie für mich ein unverzichtbares Werkzeug. Das war 1995 ein Quantensprung und ermöglichte so etwas wie Business Intelligence mit Hilfe von Excel. Über die in den IT-Systemen häufig vorhandene Excel-Integration hat die Pivot-Tabelle auch Einzug in die meisten ERP-, BI-, MIS-Systeme gehalten. Ist die Pivot-Tabelle recht intuitiv zu bedienen, sind die technischen Informationen dazu weniger bekannt. Wenn Sie mit Pivot-Tabellen arbeiten, sollten Sie den Pivot-Cache unbedingt kennenlernen!

Was ist ein Pivot-Cache?

Der Pivot-Cache ist ein Pufferspeicher, der automatisch generiert wird, wenn Sie eine Pivot-Tabelle erstellen. Der Begriff kommt vom französischen Wort „la cachette“ bzw. „la cache“ und bedeutet übersetzt „das Versteck“. Im Prinzip handelt es sich um eine Art Zwischenspeicher, in dem bestimmte Daten gelagert werden und der die Performance verbessert. Sie können sie zwar nicht sehen und nicht bearbeiten, er ist aber Teil der Arbeitsmappe und mit der Pivot-Tabelle verbunden. Wenn Sie Änderungen in der Pivot-Tabelle vornehmen, wird nicht die Datenquelle, sondern der Pivot-Cache verwendet.

In den Pivot-Cache werden auch die Quelldaten eingeladen. Sie greifen 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 Datensatz vorgenommenen Änderungen widerzuspiegeln. Der Begriff Pivot stammt übrigens auch aus dem Französischen und steht für Dreh- und Angelpunkt, Zapfen.

1. Nebenwirkungen

Der Pivot-Cache vergrößert grundsätzlich die Excel-Datei. Beim Erstellen einer Pivot-Tabelle wird eine Kopie der Quelldaten im Pivot-Cache gespeichert. Folglich nimmt die Größe der Excel-Datei erheblich zu, wenn Sie mit einer großen Excel-Liste als Datenquelle arbeiten. In diesem Fall wäre die Nutzung von Power Query empfehlenswert. Dennoch werden die Daten im Cache bei jeder kleinen Änderung an der Pivot-Tabelle laufend aktualisiert, so dass bei einer großen Datenquelle Probleme mit der Performance auftreten können. In diesem Fall ist es empfehlenswert Power Pivot zu nutzen.

2. Gemeinsame Nutzung des Pivot-Caches

Wenn Sie in einer Excel-Datei bereits eine Pivot-Tabelle erstellt haben und eine zusätzliche Pivot-Tabelle mit denselben Quelldaten erstellen, nutzt Excel (ab Version Excel 2007) automatisch denselben Pivot-Cache. Der von mehren Pivot-Tabellen gemeinsame genutzte Cache hat folgende Auswirkungen:

• Wenn Sie eine Pivot-Tabelle aktualisieren, werden alle Pivot-Tabellen, die mit demselben Cache verknüpft sind, aktualisiert.
• Wenn Sie beispielsweise Datumsangaben nach Monaten, Quartalen, Jahren gruppieren, werden neue Felder erzeugt und stehen in allen Pivot-Tabellen zur Verfügung.
• Erstellen Sie ein berechnetes Feld/Element, wird es in allen Pivot-Tabellen, die denselben Pivot-Cache nutzen, zur Verfügung gestellt verwenden.

Wollen Sie das nicht, müssen Sie einen separaten Pivot-Cache erstellen (bei Verwendung derselben Datenquelle), was aber zu einer größeren Datei führt.

3. Erstellen mehrerer Pivot-Caches (mit derselben Datenquelle)

Verwenden verschiedener Intelligenter Tabellen

• Erstellen Sie aus einer Liste eine Intelligente Tabelle  mit <Strg> + <T>.
• Erstellen Sie aus dieser Datenquelle eine Pivot-Tabelle. Damit wird der erste Pivot-Cache angelegt
• Konvertieren Sie anschließend die Intelligente Tabelle wieder in einen Bereich.
• Dann erstellen Sie erneut aus der Liste eine intelligente Tabelle und eine Pivot-Tabelle

Obwohl beide Tabellen (Table1 und Table2) auf dieselbe Datenquelle verweisen, stellt diese Methode sicher, dass für jede Tabelle ein separater Pivot-Cache generiert wird.

Assistenten der „alten“ Pivot-Table einsetzen

• Wählen Sie eine beliebige Zelle in der Datenquelle aus und geben Sie den Short Cut <ALT> + <n> + <p> ein.

• 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 besagt, dass Sie auf <Ja> klicken sollten, um einen freigegebenen Pivot-Cache zu erstellen, und auf <Nein>, um einen separaten Pivot-Cache zu erstellen.

4. Anzahl der Pivot-Caches einer Datei ermitteln

Wenn Sie die Anzahl der Pivot-Caches in einer Excel-Datei zählen möchten, müssen Sie VBA einsetzen.

• Dazu öffnen Sie den Visual Basic Editor mit <ALT> + <F11> oder wechseln Sie zur Registerkarte Entwicklertools – > Visual Basic).
• Klicken Sie im Visual Basic-Editormenü auf Ansicht und wählen Sie Direktfenster (oder drücken Sie Strg + G). Dadurch wird das Direktfenster sichtbar.
• Geben Sie im Direktfenster den Code ActiveWorkbook.PivotCaches.Count ein, und drücken Sie <Enter>. Die Anzahl der Pivot-Caches in der Arbeitsmappe wird angezeigt.

5. Optimierung der Performance von Pivot-Tabellen

Folgendes können Sie tun , um die Dateigröße und Speicherauslastung zu verbessern:

Löschen der Quelldaten

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

Falls Sie die Quelldaten wiederherstellen möchten, klicken Sie einfach doppelt auf die Gesamtsumme der Pivot-Tabelle. Es wird ein neues Sheet erzeugt, die zum Erstellen dieser Pivot-Tabelle verwendet wurden.

So speichern Sie die Quell-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. Dies bedeutet, dass Sie die Quelldaten an zwei Stellen speichern: im Arbeitsblatt mit den Daten und im Pivot-Cache. Um die Daten nicht im Cache zu speichern, gehen Sie folgendermaßen vor:

• Markieren Sie eine beliebige Zelle in der Pivot-Tabelle.
• Rufen Sie die Optionen der Pivot Table auf und dort die Registerkarte Daten.
• Deaktivieren Sie Quelldaten mit Datei speichern.
• Aktivieren Sie Aktualisieren beim Öffnen der Datei. Wenn Sie diese Option nicht aktivieren, werden beim Öffnen der Excel-Arbeitsmappe die Daten nicht aktualisiert, und Sie können die PivotTable-Funktionen 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. Es kann allerdings etwas länger dauern, die Datei zu öffnen, da Excel den Cache neu erstellt.

Löschen eines Pivot-Caches

Wenn Sie einen Pivot-Cache löschen möchten, führen Sie folgende Schritte aus:

• Löschen Sie eine der Pivot-Tabellen, für die Sie den Pivot-Cache löschen möchten. Wählen Sie dazu die Pivot-Tabelle aus und nutzen das Menü Start–> Löschen–> Alle >Löschen.
• Kopieren Sie nun einfach die Pivot-Tabelle, die Sie unter Nutzung des gleichen Pivot-Caches nutzen möchten, und fügen Sie sie im selben Arbeitsblatt oder in einem separaten Arbeitsblatt ein. Durch das Einfügen der Pivot-Tabelle wird der Pivot-Cache freigegeben.

Wie Sie die Pivot-Tabelle und Power-Pivot für Ihr Controlling nutzen können, erfahren Sie hier…..