Office: Konvertieren von PivotTable-Zellen in Arbeitsblattformeln

Konvertieren von PivotTable-Zellen in Arbeitsblattformeln
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr... Weniger Eine PivotTable verfügt über mehrere Layouts, die eine vordefinierte Struktur für den Bericht bereitstellen, aber Sie können 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 umwandeln und dann das Layout dieser Zellen ändern, indem Sie alle in einem Arbeitsblatt verfügbaren Features in vollem Umfang nutzen. Sie können die Zellen entweder in Formeln umwandeln, die Cubefunktionen verwenden, oder die Funktion PIVOTDATENZUORDNEN verwenden. Durch das Konvertieren von Zellen in Formeln wird der Vorgang zum Erstellen, aktualisieren und Verwalten dieser benutzerdefinierten PivotTables erheblich vereinfacht.

Wenn Sie Zellen in Formeln umwandeln, 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 können Sie jedoch nicht mehr auf die interaktiven Features einer PivotTable zugreifen, wie etwa das Filtern, Sortieren oder erweitern und reduzieren von Ebenen.

Hinweis:  Wenn Sie eine OLAP-PivotTable (Online Analytical Processing) konvertieren, können Sie die Daten weiterhin aktualisieren, um aktuelle Messwerte abzurufen, doch können Sie die im Bericht angezeigten tatsächlichen Mitglieder nicht aktualisieren.

Informationen zu gängigen Szenarien für die Konvertierung von PivotTables in Arbeitsblattformeln
Im folgenden finden Sie typische Beispiele für das, 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 periodischen Bericht, den Sie monatlich für Ihre Mitarbeiter erstellen müssen. Sie benötigen nur eine Teilmenge der Berichtsinformationen, und Sie möchten die Daten auf eine angepasste Art und Weise entwerfen. Sie können Zellen in einem gewünschten Design Layout einfach verschieben und 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 Verkaufsinformationen für die letzten zwei Jahre aufgeschlüsselt nach Region und Produktgruppe anzeigen, und Sie möchten in weiteren Zeilen erweiterte 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 Verkäufe nach Region und Produktgruppe angezeigt werden, die nicht in der ursprünglichen PivotTable enthalten sind. 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 aus, um die Ergebnisse für jede Zeile abzurufen.

Verwenden mehrerer Datenquellen    

Angenommen, Sie möchten Ergebnisse zwischen einer Produktions-und einer Testdatenbank vergleichen, um sicherzustellen, dass die Testdatenbank erwartete Ergebnisse produziert. Sie können Zellformeln ganz einfach kopieren und dann das Verbindungsargument so ändern, dass es auf die Testdatenbank zeigt, um diese beiden Ergebnisse zu vergleichen.

Verwenden von Zellbezügen zum variieren der Benutzereingabe    

Angenommen, Sie möchten, dass der gesamte Bericht basierend auf der Benutzereingabe geändert wird. Sie können die Argumente für die Cube-Formeln in Zellbezüge auf dem Arbeitsblatt ändern und dann in diesen Zellen unterschiedliche Werte eingeben, um unterschiedliche Ergebnisse abzuleiten.

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

Angenommen, Sie müssen einen Bericht erstellen, der eine 2008-Spalte mit dem Namen "ist-Umsätze" enthält, eine 2009-Spalte mit dem Namen "projizierte Umsätze", aber Sie möchten keine weiteren Spalten. Sie können einen Bericht erstellen, der nur diese Spalten enthält, im Gegensatz zu einer PivotTable, für die eine symmetrische Berichterstellung erforderlich ist.

Erstellen eigener Cube-Formeln und MDX-Ausdrücke    

Angenommen, Sie möchten einen Bericht erstellen, in dem Umsätze für ein bestimmtes Produkt von drei bestimmten Vertriebsmitarbeitern für den Monat Juli angezeigt werden. Wenn Sie über MDX-Ausdrücke und OLAP-Abfragen informiert sind, können Sie die Cube-Formeln selbst eingeben. Obwohl diese Formeln recht aufwendig werden können, können Sie die Erstellung vereinfachen und die Genauigkeit dieser Formeln mithilfe der AutoVervollständigen-Formel verbessern. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen für Formeln.

Konvertieren von Zellen in Formeln, die Cubefunktionen verwenden
Hinweis: Sie können eine OLAP-PivotTable (Online Analytical Processing) nur mithilfe dieser Vorgehensweise konvertieren.

1. Wenn Sie die PivotTable für die spätere Verwendung speichern möchten, empfiehlt es sich, eine Kopie der Arbeitsmappe zu erstellen, bevor Sie die PivotTable konvertieren, indem Sie auf Datei > Speichernunter 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:
   
   • Wechseln Sie zu einem Layout, das dem gewünschten Layout am ehesten ähnelt.
     
     
   • Interagieren Sie mit dem Bericht, beispielsweise filtern, Sortieren und umgestalten des Berichts, 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 klicken Sie 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 die PivotTable konvertiert werden soll:
   
   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.
     
     Nur die PivotTable-Zeilenbeschriftungen, Spaltenbeschriftungen und Wertebereich konvertieren, aber die Berichtsfilter beibehalten    
     
     
   • Stellen Sie sicher, dass das Kontrollkästchen Berichtsfilter konvertieren deaktiviert ist. (Dies ist der Standardwert.)
     
     Dadurch werden alle Zeilenbeschriftungen, Spaltenbeschriftungen und Werte 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-Formatversion 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.
   
   In der Statusleiste wird eine Meldung angezeigt, während der Konvertierungsvorgang durchgeführt wird. Wenn der Vorgang lange dauert und Sie es vorziehen, zu einem anderen Zeitpunkt zu konvertieren, drücken Sie ESC, um den Vorgang abzubrechen.
   
   Hinweise: 
   
   • Sie können keine Zellen mit Filtern konvertieren, die auf ausgeblendete Ebenen angewendet werden.
     
     
   • Sie können keine Zellen konvertieren, in denen Felder über eine benutzerdefinierte Berechnung verfügen, die über die Registerkarte Werte anzeigen als im Dialogfeld Werte Feld Einstellungen erstellt wurden. (Klicken Sie auf der Registerkarte Optionen in der Gruppe Aktives Feld auf Aktives Feld, und klicken Sie dann auf Werte Feld Einstellungen.)
     
     
   • Für Zellen, die konvertiert werden, wird die Zellenformatierung beibehalten, aber PivotTable-Formatvorlagen werden entfernt, da diese Formatvorlagen nur für PivotTables gelten können.
     
     

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

1. Stellen Sie sicher, dass der Befehl " PIVOTDATENZUORDNEN generieren " in der Gruppe " PivotTable " auf der Registerkarte " Optionen " aktiviert ist.
   
   Hinweis: Mit dem Befehl " PIVOTDATENZUORDNEN generieren " können Sie die Option " GetPivotTable-Funktionen für PivotTable-Bezüge verwenden " in der Kategorie " Formeln " im Abschnitt " Arbeiten mit Formeln " im Dialogfeld " Excel-Optionen " festlegen oder deaktivieren.
   
   
2. Stellen Sie in der PivotTable sicher, dass die Zelle, die Sie in den einzelnen Formeln 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 einfügen möchten.
   
   
4. Klicken Sie auf die Zelle in der PivotTable, die Sie in der Formel in der PivotTable verwenden möchten. Einer Formel, die die Daten aus der PivotTable abruft, wird eine PIVOTDATENZUORDNEN-Arbeitsblattfunktion hinzugefügt. Diese Funktion ruft weiterhin die richtigen Daten ab, wenn sich das Berichtslayout ändert oder wenn Sie die Daten aktualisieren.
   
   
5. Beenden Sie die Eingabe der Formel, und drücken Sie die EINGABETASTE.
   
   

Hinweis: Wenn Sie alle Zellen entfernen, auf die in der PIVOTDATENZUORDNEN-Formel im Bericht verwiesen wird, gibt die Formel #Ref! zurück.

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

:)