Konvertieren von PivotTable-Zellen in Arbeitsblattformeln

Eine PivotTable verfügt über mehrere Layouts, die dem Bericht eine vordefinierte Struktur bieten, doch können Sie diese Layouts nicht anpassen. Wenn Sie mehr Flexibilität beim Entwerfen des Layouts eines PivotTable-Berichts benötigen, können Sie die Zellen in Arbeitsblattformeln konvertieren und dann das Layout dieser Zellen ändern, indem Sie alle in einem Arbeitsblatt verfügbaren Features vollständig nutzen. Sie können die Zellen entweder in Formeln konvertieren, die Cubefunktionen verwenden, oder die Funktion PIVOTDATENZUORDNEN verwenden. Das Konvertieren von Zellen in Formeln vereinfacht das Erstellen, Aktualisieren und Verwalten dieser angepassten PivotTables erheblich.

Wenn Sie Zellen in Formeln konvertieren, greifen diese Formeln auf dieselben Daten wie die PivotTable zu und können aktualisiert werden, um aktuelle Ergebnisse anzuzeigen. Mit der möglichen Ausnahme von Berichtsfiltern haben Sie jedoch keinen Zugriff mehr auf die interaktiven Features einer PivotTable, z. B. Filtern, Sortieren oder Erweitern und Reduzieren von Ebenen.

Lernen Sie häufige Szenarien zum Konvertieren von PivotTables in Arbeitsblattformeln kennen

Im Folgenden finden Sie typische Beispiele dafür, was Sie tun können, nachdem Sie PivotTable-Zellen in Arbeitsblattformeln konvertiert haben, um das Layout der konvertierten Zellen anzupassen.

Neuanordnen und Löschen von Zellen 

Angenommen, Sie haben einen regelmäßigen Bericht, den Sie jeden Monat für Ihre Mitarbeiter erstellen müssen. Sie benötigen nur eine Teilmenge der Berichtsinformationen und bevorzugen es, die Daten in einer angepassten Weise anzuordnen. Sie können Zellen einfach verschieben und in einem von Ihnen gewünschten Entwurfslayout anordnen, die Zellen löschen, die für den monatlichen Mitarbeiterbericht nicht erforderlich sind, und dann die Zellen und das Arbeitsblatt entsprechend Ihren Wünschen formatieren.

Einfügen von Zeilen und Spalten 

Angenommen, Sie möchten Umsatzinformationen für die letzten zwei Jahre nach Region und Produktgruppe anzeigen und in zusätzlichen Zeilen ausführliche Kommentare einfügen. Fügen Sie einfach eine Zeile ein, und geben Sie den Text ein. Darüber hinaus möchten Sie eine Spalte hinzufügen, in der die Umsätze nach Region und Produktgruppe angezeigt werden, die nicht in der ursprünglichen PivotTable enthalten ist. Fügen Sie einfach eine Spalte ein, fügen Sie eine Formel hinzu, um die gewünschten Ergebnisse zu erhalten, und füllen Sie dann die Spalte nach unten, um die Ergebnisse für jede Zeile zu erhalten.

Verwenden mehrerer Datenquellen 

Angenommen, Sie möchten die Ergebnisse einer Produktions- und einer Testdatenbank vergleichen, um sicherzustellen, dass die Testdatenbank die erwarteten Ergebnisse liefert. Sie können ganz einfach Zellformeln kopieren und dann das Verbindungsargument so ändern, dass sie auf die Testdatenbank verweisen und diese beiden Ergebnisse vergleichen.

Verwenden von Zellbezügen zum Variieren von Benutzereingaben 

Angenommen, Sie möchten, dass sich der gesamte Bericht basierend auf Benutzereingaben ändert. Sie können Argumente der Cubeformeln in Zellbezüge auf dem Arbeitsblatt ändern und dann unterschiedliche Werte in diese Zellen eingeben, um unterschiedliche Ergebnisse abzuleiten.

Erstellen eines uneinheitlichen Zeilen- oder Spaltenlayouts (auch als asymmetrische Berichterstellung bezeichnet) 

Angenommen, Sie müssen einen Bericht erstellen, der eine Spalte für 2008 mit dem Namen "Actual Sales" und eine Spalte für 2009 mit dem Namen "Projected Sales" enthält, aber Sie möchten keine anderen Spalten enthalten. Sie können einen Bericht erstellen, der nur diese Spalten enthält, im Gegensatz zu einer PivotTable, für die symmetrische Berichte erforderlich sind.

Erstellen eigener Cubeformeln und MDX-Ausdrücke 

Angenommen, Sie möchten einen Bericht erstellen, der die Verkäufe für ein bestimmtes Produkt von drei bestimmten Vertriebsmitarbeitern für den Monat Juli anzeigt. Wenn Sie mit MDX-Ausdrücken und OLAP-Abfragen vertraut sind, können Sie die Cubeformeln selbst eingeben. Obwohl diese Formeln recht aufwendig werden können, können Sie mit der AutoVervollständigung für Formeln die Erstellung vereinfachen und deren Genauigkeit verbessern. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen für Formeln.

Konvertieren von Zellen in Formeln, die Cubefunktionen verwenden

1. Damit Sie die PivotTable für die zukünftige Verwendung speichern möchten, empfiehlt es sich, eine Kopie der Arbeitsmappe zu erstellen, bevor Sie die PivotTable konvertieren, indem Sie auf "Datei>speichern unter" klicken. Weitere Informationen finden Sie unter Speichern einer Datei.

2. Bereiten Sie die PivotTable so vor, dass Sie die Neuanordnung der Zellen nach der Konvertierung minimieren können, indem Sie wie folgt vorgehen:

   • Ändern Sie zu einem Layout, das dem gewünschten Layout am ähnlichsten ist.
   • Interagieren Sie mit dem Bericht, z. B. indem Sie den Bericht filtern, sortieren und neu gestalten, um die gewünschten Ergebnisse zu erhalten.

3. Klicken Sie auf die PivotTable.

4. Klicken Sie auf der Registerkarte Optionen in der Gruppe Tools auf OLAP-Tools und dann auf In Formeln konvertieren.
   Wenn keine Berichtsfilter vorhanden sind, wird der Konvertierungsvorgang abgeschlossen. Wenn ein oder mehrere Berichtsfilter vorhanden sind, wird das Dialogfeld In Formeln konvertieren angezeigt.

5. Entscheiden Sie, wie Sie die PivotTable konvertieren möchten:
   Konvertieren der gesamten PivotTable 

   • Aktivieren Sie das Kontrollkästchen Berichtsfilter konvertieren .
     Dadurch werden alle Zellen in Arbeitsblattformeln konvertiert, und die gesamte PivotTable wird gelöscht.
     Konvertieren Sie nur die PivotTable-Zeilenbeschriftungen, Spaltenbeschriftungen und Wertebereiche, behalten Sie jedoch die Berichtsfilter bei 

   • Stellen Sie sicher, dass das Kontrollkästchen Berichtsfilter konvertieren deaktiviert ist. (Dies ist die Standardeinstellung.)
     Dadurch werden alle Zeilenbeschriftungen-, Spaltenbeschriftungs- und Wertebereichszellen in Arbeitsblattformeln konvertiert, und die ursprüngliche PivotTable wird beibehalten, jedoch nur mit den Berichtsfiltern, sodass Sie weiterhin mithilfe der Berichtsfilter filtern können.

     Hinweis

     Wenn das PivotTable-Format Version 2000-2003 oder früher ist, können Sie nur die gesamte PivotTable konvertieren.

6. Klicken Sie auf Konvertieren.
   Der Konvertierungsvorgang aktualisiert zunächst die PivotTable, um sicherzustellen, dass aktuelle Daten verwendet werden.
   Während des Konvertierungsvorgangs wird in der status Leiste eine Meldung angezeigt. Wenn der Vorgang lange dauert und Sie ihn lieber zu einem anderen Zeitpunkt konvertieren möchten, drücken Sie ESC, um den Vorgang abzubrechen.

   Hinweis

   • Sie können keine Zellen konvertieren, in denen Filter auf ausgeblendete Ebenen angewendet wurden.
   • Sie können keine Zellen konvertieren, deren Felder eine benutzerdefinierte Berechnung haben, die über die Registerkarte Werte anzeigen als des Dialogfelds Wertfeldeinstellungen erstellt wurden. (Klicken Sie auf der Registerkarte Optionen in der Gruppe Aktives Feld auf Aktives Feld, und klicken Sie dann auf Wertfeldeinstellungen.)
   • Für Zellen, die konvertiert werden, wird die Zellenformatierung beibehalten, aber PivotTable-Formatvorlagen werden entfernt, da diese Formatvorlagen nur auf PivotTables angewendet werden können.

Konvertieren von Zellen mithilfe der Funktion PIVOTDATENZUORDNEN

Sie können die Funktion PIVOTDATENZUORDNEN in einer Formel verwenden, um PivotTable-Zellen in Arbeitsblattformeln zu konvertieren, wenn Sie mit Nicht-OLAP-Datenquellen arbeiten möchten, wenn Sie nicht sofort auf das neue PivotTable-Format Version 2007 aktualisieren möchten oder wenn Sie die Komplexität der Verwendung der Cubefunktionen vermeiden möchten.

1. Stellen Sie sicher, dass der Befehl PIVOTDATENZUORDNEN in der Gruppe PivotTable auf der Registerkarte Optionen aktiviert ist.

   Hinweis

   Der Befehl PIVOTDATENZUORDNEN setzt die Option PIVOTTABLE-Funktion verwenden für PivotTable-Verweise in der Kategorie Formeln des Abschnitts Arbeiten mit Formeln im Dialogfeld Excel-Optionen oder deaktiviert sie.

2. Stellen Sie in der PivotTable sicher, dass die Zelle, die Sie in jeder Formel verwenden möchten, sichtbar ist.

3. Geben Sie in einer Arbeitsblattzelle außerhalb der PivotTable die gewünschte Formel bis zu dem Punkt ein, an dem Sie Daten aus dem Bericht einbeziehen möchten.

4. Klicken Sie in der PivotTable auf die Zelle, die Sie in Ihrer Formel in der PivotTable verwenden möchten. Ihrer Formel wird eine PIVOTDATENZUORDNEN-Arbeitsblattfunktion hinzugefügt, die die Daten aus der PivotTable abruft. Diese Funktion ruft weiterhin die richtigen Daten ab, wenn sich das Berichtslayout ändert oder wenn Sie die Daten aktualisieren.

5. Schließen Sie die Eingabe der Formel ab, und drücken Sie die EINGABETASTE.

Problem: Die Zellen der PivotTable können nicht in Arbeitsblattformeln konvertiert werden