Eine PivotTable verfügt über mehrere Layouts, die dem Bericht eine vordefinierte Struktur bereitstellen, aber Sie können diese Layouts nicht anpassen. Wenn Sie beim Entwerfen des Layouts eines PivotTable-Berichts mehr Flexibilität 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 in vollem Umfang nutzen. Sie können die Zellen entweder in Formeln konvertieren, die Cubefunktionen verwenden, oder die GETPIVOTDATA-Funktion verwenden. Das Konvertieren von Zellen in Formeln vereinfacht das Erstellen, Aktualisieren und Verwalten dieser benutzerdefinierten PivotTables erheblich.
Wenn Sie Zellen in Formeln konvertieren, greifen diese Formeln auf die gleichen 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.
Hinweis: Wenn Sie eine OLAP-PivotTable (Online Analytical Processing) konvertieren, können Sie die Daten weiterhin aktualisieren, um aktuelle Measurewerte abzurufen, aber Sie können die tatsächlichen Elemente, die im Bericht angezeigt werden, nicht aktualisieren.
Informationen zu gängigen Szenarien zum Konvertieren von PivotTables in Arbeitsblattformeln
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.
Neu anordnen und Löschen von Zellen
Angenommen, Sie verfügen über 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 auf benutzerdefinierte Weise anzulegen. Sie können einfach Zellen in einem gewünschten Entwurfslayout verschieben und anordnen, die Zellen löschen, die für den monatlichen Mitarbeiterbericht nicht erforderlich sind, und dann die Zellen und das Arbeitsblatt nach Ihren Wünschen formatieren.
Einfügen von Zeilen und Spalten
Angenommen, Sie möchten verkaufsinformationen für die letzten zwei Jahre nach Region und Produktgruppe anzeigen und erweiterte Kommentare in zusätzlichen Zeilen 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, die Verkäufe nach Region und Produktgruppe anzeigt, die sich nicht in der ursprünglichen PivotTable befindet. 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 liefert. Sie können Zellformeln einfach kopieren und dann das Verbindungsargument so ändern, dass es auf die Testdatenbank verweist, um diese beiden Ergebnisse zu vergleichen.
Verwenden von Zellbezügen zum Variieren der Benutzereingabe
Angenommen, Sie möchten, dass sich der gesamte Bericht basierend auf der Benutzereingabe ändert. Sie können Argumente in cube-Formeln in Zellbezüge auf dem Arbeitsblatt ändern und dann unterschiedliche Werte in diese Zellen eingeben, um unterschiedliche Ergebnisse abzuleiten.
Erstellen eines nicht uniformen Zeilen- oder Spaltenlayouts (auch als asymmetrische Berichterstellung bezeichnet)
Angenommen, Sie müssen einen Bericht erstellen, der eine 2008-Spalte mit dem Namen Ist-Verkäufe enthält, eine 2009-Spalte mit dem Namen "Projizierte Verkäufe", aber Sie möchten keine anderen Spalten. Im Gegensatz zu einer PivotTable, für die symmetrische Berichte erforderlich sind, können Sie einen Bericht erstellen, der nur diese Spalten enthält.
Erstellen eigener Cubeformeln und MDX-Ausdrücke
Angenommen, Sie möchten einen Bericht erstellen, der die Verkäufe für ein bestimmtes Produkt durch drei bestimmte Vertriebsmitarbeiter 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 die Erstellung dieser Formeln vereinfachen und die Genauigkeit dieser Formeln mithilfe von Formel autoVervollständigen verbessern. Weitere Informationen finden Sie unter Verwenden von AutoVervollständigen für Formeln.
Hinweis: Sie können eine OLAP-PivotTable (Online Analytical Processing) nur mit diesem Verfahren konvertieren.
-
Um die PivotTable für die zukünftige Verwendung zu speichern, 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.
-
Bereiten Sie die PivotTable so vor, dass Sie die Neuanordnung der Zellen nach der Konvertierung minimieren können, indem Sie die folgenden Schritte ausführen:
-
Wechseln Sie zu einem Layout, das dem gewünschten Layout am ehesten ähnelt.
-
Interagieren Sie mit dem Bericht, z. B. filtern, sortieren und den Bericht neu gestalten, um die gewünschten Ergebnisse zu erhalten.
-
-
Klicken Sie auf die PivotTable.
-
Klicken Sie auf der Registerkarte Optionen in der Gruppe Extras auf OLAP-Tools, und klicken Sie dann auf In Formeln konvertieren.
Wenn keine Berichtsfilter vorhanden sind, wird der Konvertierungsvorgang abgeschlossen. Wenn mindestens ein Berichtsfilter vorhanden ist, wird das Dialogfeld In Formeln konvertieren angezeigt.
-
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 gelöscht.
Konvertieren Sie nur die PivotTable-Zeilenbeschriftungen, Spaltenbeschriftungen und Wertebereiche, aber behalten Sie die Berichtsfilter bei.
-
Stellen Sie sicher, dass das Kontrollkästchen Berichtsfilter konvertieren deaktiviert ist. (Dies ist die Standardeinstellung.)
Dadurch werden alle Zeilenbeschriftungen, Spaltenbeschriftungen und Wertebereichszellen in Arbeitsblattformeln konvertiert, und die ursprüngliche PivotTable wird beibehalten, jedoch nur mit den Berichtsfiltern, sodass Sie mit den Berichtsfiltern weiterhin filtern können.
Hinweis: Wenn das PivotTable-Format Version 2000-2003 oder früher ist, können Sie nur die gesamte PivotTable konvertieren.
-
-
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 es vorziehen, zu einem anderen Zeitpunkt zu konvertieren, drücken Sie ESC, um den Vorgang abzubrechen.
Hinweise:
-
Zellen, deren Filter auf ausgeblendete Ebenen angewendet wurden, können nicht konvertiert werden.
-
Sie können keine Zellen konvertieren, in denen Felder eine benutzerdefinierte Berechnung aufweisen, die über die Registerkarte Werte anzeigen als des Dialogfelds Wertefeldeinstellungen erstellt wurden. (Klicken Sie auf der Registerkarte Optionen in der Gruppe Aktives Feld auf Aktives Feld, und klicken Sie dann auf Werte Feldeinstellungen.)
-
Für zellen, die konvertiert werden, wird die Zellformatierung beibehalten, aber PivotTable-Formatvorlagen werden entfernt, da diese Formatvorlagen nur für PivotTables gelten können.
-
Sie können die GETPIVOTDATA-Funktion 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 der Version 2007 aktualisieren möchten oder wenn Sie die Komplexität der Verwendung der Cubefunktionen vermeiden möchten.
-
Stellen Sie sicher, dass der Befehl GETPIVOTDATA generieren in der PivotTable-Gruppe auf der Registerkarte Optionen aktiviert ist.
Hinweis: Der Befehl GETPIVOTDATA generieren legt oder deaktiviert die Option GetPIVOTTABLE-Funktionen für PivotTable-Verweise verwenden in der Kategorie Formeln des Abschnitts Arbeiten mit Formeln im Dialogfeld Excel-Optionen .
-
Stellen Sie in der PivotTable sicher, dass die Zelle, die Sie in jeder Formel verwenden möchten, sichtbar ist.
-
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 einschließen möchten.
-
Klicken Sie auf die Zelle in der PivotTable, die Sie in der Formel in der PivotTable verwenden möchten. Der Formel wird eine GETPIVOTDATA-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.
-
Schließen Sie die Eingabe der Formel ab, und drücken Sie die EINGABETASTE.
Hinweis: Wenn Sie eine der Zellen, auf die in der GETPIVOTDATA-Formel verwiesen wird, aus dem Bericht entfernen, gibt die Formel #REF! zurück.
