Suchen und Ändern der Quelldaten für eine Excel-Pivot-Tabelle. Bei fehlenden Quelldaten versuchen Sie, diese neu zu erstellen. Ändern Sie die Optionen, um Quelldaten mit der Pivot-Tabellendatei zu speichern.

HINWEIS: Informationen zu Makros, mit denen die Quelldaten verwaltet werden können, finden Sie auf der Seite mit den Pivot-Tabellen-Quelldatenmakros. Wenn ein Makro die Quelldaten aufheben soll, rufen Sie die Seite Excel-Datenmakro aufheben

Quelldaten suchen und korrigieren

auf. In diesem Video erfahren Sie, wie Sie die Pivot-Tabelle suchen Überprüfen Sie anschließend die Datenquelle, um sicherzustellen, dass sie alle benötigten Zeilen und Spalten enthält. Passen Sie gegebenenfalls die Datenquelle an, um neue Zeilen oder Spalten einzuschließen.

HINWEIS: Lange Zeit Verwenden Sie eine benannte Excel-Tabelle als Datenquelle. Sie wird automatisch angepasst, wenn neue Zeilen hinzugefügt werden.

Die schriftlichen Anweisungen befinden sich unter dem Video, und Sie können die Beispieldatei herunterladen, um sie zu befolgen mit dem Video.

Suchen der Quelldaten

Nachdem eine Pivot-Tabelle erstellt wurde, möchten Sie möglicherweise neue Datensätze zu den Quelldaten hinzufügen oder die vorhandenen Datensätze ändern In einer großen Arbeitsmappe kann es schwierig sein, die genaue Quelle für die Pivot-Tabelle zu finden, wenn mehrere Tabellen oder Listen vorhanden sind.

HINWEIS: Wenn Sie mein Pivot Power Premium-Add-In gekauft haben, klicken Sie auf Pivot-Tabelle Info, dann klicken Gehen Sie zu Quelldaten.

Führen Sie die folgenden Schritte aus, um die Quelldaten für eine Pivot-Tabelle zu ermitteln:

1. Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
2. Auf dem Menüband, Klicken Sie auf der Registerkarte PivotTable-Tools auf die Registerkarte Analysieren (in Excel 2010 auf die Registerkarte Optionen).
3. Klicken Sie in der Gruppe Daten auf den oberen Abschnitt des Befehls Datenquelle ändern.

Das Dialogfeld PivotTable-Datenquelle ändern wird geöffnet, und Sie können die Quellentabelle oder den Quellbereich in sehen das Feld Tabelle / Bereich. Dies kann ein Verweis auf ein Blatt und einen Zellbereich sein, z. B.

• Bestellungen! $ A $ 1: $ H $ 9

oder ein Tabellenname. Beispiel:

• Sales_East

Hinter dem Dialogfeld sehen Sie den Quellbereich auf dem Arbeitsblatt, umgeben von einem sich bewegenden Rand.

In der Abbildung unten endet der Quelldatenbereich in Zeile 9, und in Zeile 10 wurde ein neuer Datensatz hinzugefügt. Dieser Datensatz wird nicht in der Pivot-Tabelle angezeigt, es sei denn, die Datenquelle wurde angepasst.

Anpassen des Quelldatenbereichs

Wenn die Quelldaten einer Pivot-Tabelle eine statische Referenz sind Bei einem bestimmten Blatt und Bereich wird es nicht automatisch angepasst, wenn neue Daten hinzugefügt werden. In der Abbildung oben bezieht sich der Datenquellenbereich auf Bestellungen! $ A $ 1: $ H $ 9 und kann manuell angepasst werden, um Zeilen einzuschließen 10.

So passen Sie den statischen Quellbereich manuell an:

Sie können den Quellbereich bei Bedarf anpassen, sodass alle neuen Daten enthalten sind. Eine bessere Lösung besteht jedoch darin, eine dynamische Quelle zu erstellen Anweisungen finden Sie weiter unten.

Ändern der Quelldaten

Gelegentlich müssen Sie möglicherweise eine Pivot-Tabelle ändern, sodass eine andere Datenquelle verwendet wird. In diesem Beispiel wurde eine neue Tabelle mit nur Daten für den Osten erstellt egion. Die Pivot-Tabelle wird geändert, um diese Quelle zu verwenden. Anschließend kann sie an einen Manager in der Region Ost gesendet werden, ohne dass die Daten der Region West angezeigt werden.

HINWEIS: Nach dem Wechsel zu einer anderen Quellentabelle Möglicherweise müssen Sie einige Felder umbenennen oder der Pivot-Tabelle andere Felder hinzufügen.

Gehen Sie folgendermaßen vor, um die Quelldaten für eine Excel-Pivot-Tabelle zu ändern:

1. Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
2. Klicken Sie in der Multifunktionsleiste auf der Registerkarte PivotTable-Tools auf die Registerkarte Analysieren (in Excel 2010 auf die Registerkarte Optionen).
3. In den Daten Klicken Sie in der Gruppe auf den oberen Abschnitt des Befehls Datenquelle ändern.
4. Das Dialogfeld PivotTable-Datenquelle ändern wird geöffnet, und Sie können die Quellentabelle oder den Bereich im Feld Tabelle / Bereich anzeigen. In diesem Screenshot Die aktuelle Datenquelle ist die Sales_Data-Tabelle, die Daten aus beiden Regionen enthält.

   

5. Löschen Sie die vorhandene Datenquelle Geben Sie einen neuen Tabellennamen ein oder wählen Sie eine neue Datenquelle aus e Bereich. Die Tabelle / der Bereich wird in Sales_East geändert, wo nur die Ostdaten aufgelistet sind.

   

6. Klicken Sie auf OK.

Dynamische Quelle für Pivot-Tabelle

Anstatt einen statischen Bereich als Datenquelle für die Pivot-Tabelle zu verwenden, besteht eine bessere Lösung darin, einen Dynamikbereich zu erstellen, dessen Größe automatisch angepasst wird.

Dynamische Quelle erstellen – Excel-Tabelle

Dynamische Quelle – benannter Bereich

Dynamische Quelle – Dynamisches Array

Verwenden einer dynamischen Quelle

Dynamische Quelle – Excel-Tabelle

Anstatt einen statischen Bereich als Datenquelle für die Pivot-Tabelle zu verwenden, besteht eine bessere Lösung darin, einen Dynamikbereich basierend auf einer benannten Excel-Tabelle zu erstellen. Hier finden Sie schriftliche Anweisungen oder sehen Sie sich das folgende Video an.

Notieren Sie sich den Tabellennamen, damit Sie ihn als Pivot-Tabellenquelle verwenden können.

Dynamische Quelle – benannter Bereich

Wenn Sie ihn nicht verwenden können In einer Excel-Tabelle als Quelldaten erstellen Sie einen dynamischen benannten Bereich basierend auf einer INDEX- oder OFFSET-Formel. Hier finden Sie schriftliche Anweisungen oder sehen Sie sich das folgende Video an.

Das Video zeigt, wie Sie einen erstellen Dynamikbereich mit der OFFSET-Funktion in Excel 2007 und die Schritte sind in späteren Versionen ähnlich.

HINWEIS: Merken Sie sich den Bereichsnamen, damit Sie ihn als Pivot-Tabellenquelle

Dynamische Quelle – Dynamisches Array

Wenn Sie über eine Excel-Version verfügen, die die neuen Funktionen wie SORT oder UNIQUE unterstützt, können Sie diese Funktionen zum Erstellen dynamischer Arrays verwenden. Bei dieser Technik werden nur Formeln verwendet – keine Makros

Im Abschnitt Download erhalten Sie die Beispieldatei für gefilterte Quelldaten. Sie zeigt, wie Sie einen benannten Bereich nur mit den sichtbaren Zeilen aus einer benannten Excel-Tabelle einrichten.

Hier ist der Filter d Daten auf einem anderen Blatt mit nur den 2 Wiederholungen und 3 Kategorien aus den sichtbaren Zeilen.

Dann Sie können eine Pivot-Tabelle nur basierend auf diesen gefilterten Daten erstellen.

Dynamische Quelle für Pivot-Tabelle verwenden

Nachdem Sie eine dynamische Quelle erstellt haben, die die Pivot-Tabelle enthält Führen Sie die folgenden Schritte aus, um diese Quelldaten zu verwenden:

1. Wählen Sie eine beliebige Zelle in der Pivot-Tabelle aus.
2. Klicken Sie in der Multifunktionsleiste auf der Registerkarte PivotTable-Tools auf Analysieren Registerkarte (klicken Sie in Excel 2010 auf die Registerkarte Optionen).
3. Klicken Sie in der Gruppe Daten auf den oberen Abschnitt des Befehls Datenquelle ändern.
4. Deaktivieren Sie im Dialogfeld PivotTable-Datenquelle ändern das Feld Tabelle / Bereich.
5. Geben Sie im Feld Tabelle / Bereich den Namen der von Ihnen erstellten Tabelle oder des benannten Bereichs ein. In diesem Beispiel ist die dynamische Quelle eine benannte Tabelle – tblOrders.
6. Klicken Sie auf OK

Quelldaten mit Pivot-Tabelle speichern

Wenn Sie eine Pivot-Tabelle in Ihrer Arbeitsmappe erstellen, werden die Quelldatensätze in einem speziellen Speicherbereich gespeichert – einem Pivot-Cache. Wenn Sie die Datei schließen, kann Excel die Quelldaten in diesem Pivot-Cache speichern oder diesen Speicher löschen.

Beide Optionen haben Vor- und Nachteile:

Wenn Sie die Quelldaten speichern:

• Die Datei ist größer
• Datei wird möglicherweise schneller geöffnet

Wenn Sie die Quelldaten nicht speichern:

• Füllung wird kleiner
• Datei wird möglicherweise geöffnet langsam, während der Pivot-Cache neu erstellt wird
• Die Pivot-Tabelle muss nach dem Öffnen der Datei aktualisiert werden.

So ändern Sie die Einstellung

Um die Speicherung zu aktivieren Ein- oder Ausschalten der Einstellung für Quelldaten:

• Klicken Sie mit der rechten Maustaste auf eine Zelle in der Pivot-Tabelle und klicken Sie auf PivotTable-Optionen.
• Auf der Registerkarte Daten im Abschnitt PivotTable-Daten Fügen Sie das Häkchen in Quelldaten mit Datei speichern hinzu oder entfernen Sie es.
• Klicken Sie auf OK.

Daten beim Öffnen aktualisieren

Wenn Sie sich für eine Drehung entscheiden Deaktivieren Sie die Option Quelldaten mit Datei speichern, und aktivieren Sie die Option Daten beim Öffnen der Datei aktualisieren. Diese Einstellung befindet sich direkt unter dem Kontrollkästchen Quelldaten speichern.

Andernfalls wird eine Meldung angezeigt, wenn Sie versuchen, die Daten zu filtern oder andere Layoutänderungen vorzunehmen.

• „Der PivotTable-Bericht wurde ohne die zugrunde liegenden Daten gespeichert. Verwenden Sie den Befehl Daten aktualisieren, um den Bericht zu aktualisieren. „

Wenn diese Meldung angezeigt wird, klicken Sie auf OK und aktualisieren Sie die Pivot-Tabelle manuell.

Und um Ärger zu vermeiden Aktivieren Sie beim Öffnen der Option Datei die Option Daten aktualisieren.

Quelldaten der Pivot-Tabelle neu erstellen

Wenn Sie versehentlich das Arbeitsblatt löschen, das die Quelldaten für Ihre Pivot-Tabelle enthält, oder wenn Wenn Sie eine Datei ohne die Daten der Pivot-Tabelle erhalten haben, können Sie sie möglicherweise mithilfe der Funktion „Details anzeigen“ der Pivot-Tabelle neu erstellen.

HINWEIS: Dies funktioniert nicht für alle Pivot-Tabellen, ist es jedoch Es lohnt sich zu versuchen, die Quelldaten wiederherzustellen.

Führen Sie die folgenden Schritte aus, um die Funktion „Details anzeigen“ zu verwenden, um die Quelldaten für eine Pivot-Tabelle neu zu erstellen:

1. Stellen Sie sicher, dass dass keines der Elemente in den Pivot-Tabellenfeldern ausgeblendet ist – löschen Sie alle angewendeten Filter und Slicer.
2. Die Pivot-Tabelle muss nicht alle Felder enthalten – stellen Sie einfach sicher, dass dort ist mindestens ein Feld im Val ues area.
3. Zeigt die Gesamtsummen für Zeilen und Spalten an. Wenn die Gesamtsummen nicht sichtbar sind, wählen Sie eine Zelle in der Pivot-Tabelle aus und klicken Sie in der Multifunktionsleiste unter PivotTable-Tools auf die Registerkarte Analysieren. Klicken Sie in der Gruppe Layout auf Gesamtsummen und dann für Zeilen und Spalten auf Ein.
4. Doppelklicken Sie auf die Gesamtsummenzelle unten rechts in der Pivot-Tabelle. Dadurch sollte ein neues Blatt mit den zugehörigen Datensätzen aus den ursprünglichen Quelldaten erstellt werden.

Korrigieren Sie das Extrahierte Daten

Wenn Sie die Quelldaten wiederherstellen, müssen Sie möglicherweise einige zusätzliche Änderungen vornehmen, um den ursprünglichen Zustand wiederherzustellen.

• Benennen Sie die Tabelle unter Verwendung des Namens der ursprünglichen Quelldaten um, falls bekannt.
• Wenn die ursprünglichen Quelldaten Formeln enthielten, müssen Sie sie neu erstellen. da die Funktion Details anzeigen nur die Daten exportiert.
• Die Spalten in den extrahierten Daten befinden sich in derselben Reihenfolge wie in den ursprünglichen Quelldaten.
• Die extrahierten Daten sind formatiert mit dem Standardtabellenformat der Arbeitsmappe. Sie können ein anderes Tabellenformat oder eine eigene Formatierung anwenden.
• Hinweis: Wenn Sie Änderungen an den Quelldaten vorgenommen und die Pivot-Tabelle nicht aktualisiert haben, werden diese Änderungen nicht in die extrahierten Daten übernommen.

Verbindung zu den extrahierten Daten herstellen

Wenn Sie die Tabelle umbenennen, die während des Prozesses „Details anzeigen“ erstellt wurde, und denselben Namen wie die Tabelle verwenden, in der sich die Quelle ursprünglich befand Daten, die Pivot-Tabelle stellt möglicherweise automatisch eine Verbindung zu den neuen Quelldaten her.

Wenn nicht, können Sie eine Verbindung zu den neu erstellten Quelldaten herstellen. Befolgen Sie die Schritte im obigen Abschnitt: Dynamische Quelle für Pivot-Tabelle verwenden – Verwenden Sie den Tabellennamen, den Sie der neuen Tabelle gegeben haben.

Beispieldatei herunterladen

Beispiel für die Pivot-Quelle: Um den Lernprogrammen zu folgen, können Sie die Datei DateAmt.zip herunterladen. Die komprimierte Datei hat das xlsx-Format und enthält keine Makros.

Beispiel für gefilterte Quelldaten: Dieses Beispiel gilt für Excel-Versionen mit den neuen Funktionen wie SORT und UNIQUE. Dow nLaden Sie diese Beispieldatei, um zu sehen, dass eine Pivot-Tabelle nur aus den sichtbaren Zeilen in einer Excel-Tabelle mit Filter erstellt wird. Die komprimierte Datei hat das XLSX-Format und enthält keine Makros.

Pivot-Tabellen-Tools

Um Zeit beim Erstellen, Formatieren und Ändern Ihrer Pivot-Tabellen zu sparen, verwenden Sie die Tools in meinem Pivot Power Premium Add-In. Mit nur wenigen Klicks können Sie:

• die Formatierung aus einer Pivot-Tabelle kopieren und auf eine andere Pivot-Tabelle anwenden.
• Ändern Sie alle Werte von Anzahl in Summe.
• Entfernen Sie die „Summe von“ aus allen Überschriften.

und vieles mehr!

Verpassen Sie nicht unsere Excel-Tipps