Erstellen sie eine pivot-tabelle aus verschiedenen blättern

Mit einer Pivot-Tabelle fassen Sie Ihre Daten in Sekundenschnelle zusammen. Sie können problemlos Berichte erstellen, Daten analysieren und diese mit anderen teilen.

Das ist das Schöne an Pivot-Tabellen.

Aber hier ist eine Wendung:

Normalerweise können Sie beim Erstellen einer Pivot-Tabelle Quelldaten aus einer einzelnen Tabelle in einem Arbeitsblatt auswählen, da Excel den Verweis auf verschiedene Arbeitsblätter nicht zulässt.

Aber manchmal kommt es vor, dass wir Quelldaten aus mehreren Arbeitsblättern verwenden müssen, um eine Pivot-Tabelle zu erstellen.

Deshalb möchte ich Ihnen heute einfache Schritte zur Verwendung mehrerer Arbeitsblätter in einer Pivot-Tabelle vorstellen.

Das Problem!

Nehmen wir an, Sie möchten die Verkaufsdaten Ihres Unternehmens analysieren und extrahieren Jahresdaten für die letzten 4 Jahre.

So erhalten Sie den Datendump in Excel.

Bitte beachten Sie, dass die Daten in den 4 Blättern unterschiedlich sind, der Aufbau aber genau gleich ist, also die gleiche Anzahl an Spalten und Überschriften.

Um diese Daten zu analysieren, müssen Sie nun aus diesen mehreren Blättern einen einzelnen Pivot-Tabellenbericht erstellen.

Einige der gängigen Methoden sind:

1. Kopieren Sie die Daten aus jedem Blatt manuell, fügen Sie sie ein und erstellen Sie einen einzelnen Datensatz auf einem neuen Blatt.
2. Verwenden Sie VBA-Code, um Daten aus mehreren Blättern automatisch zu konsolidieren.
3. Oder Sie können mehrere Arbeitsblätter mithilfe der Konsolidierungsoption von Excel in einem einzigen Arbeitsblatt zusammenfassen.

Tatsache ist jedoch, dass diese Methoden Codierung, Kopieren und Einfügen erfordern oder sich wiederholen.

Die Lösung

Hier werde ich eine neue Methode mit Microsoft-Abfrage diskutieren, die dynamisch, robust und einfach ist.

Glauben Sie mir, Sie werden es lieben!

Mit Microsoft Query können Sie eine Pivot-Tabelle aus mehreren Arbeitsblättern erstellen.

Laden Sie diese Datendatei herunter, um mitzumachen.

Schritte zum Erstellen einer PivotTable aus mehreren Arbeitsblättern

Hier finden Sie einfache Schritte, die Sie befolgen können. Laden Sie vorher bitte diese Datei hier herunter, um sie zu befolgen.

• Wählen Sie zunächst alle Daten auf jedem Blatt aus und benennen Sie sie.
  • Daten für 2005 benannt als – Jahr 2005
  • Daten für 2006 benannt – Jahr 2006
  • Daten für 2007 benannt – Jahr 2007
  • Daten für 2007 benannt – Jahr 2007

• Anschließend wenden Sie Datentabellen auf alle Daten in vier Arbeitsblättern an.
  • Wählen Sie eine beliebige Zelle im Datenbereich aus.
  • Verwenden Sie Strg + T, um die Daten auf jedem Blatt in eine Tabelle umzuwandeln.
  • Stellen Sie sicher, dass „Meine Tabelle hat Kopfzeilen“ jedes Mal aktiviert ist.
  • Wiederholen Sie diesen Vorgang für die 4 Jahre (Blätter).

• Beginnen wir mit der Konsolidierung dieser Daten auf einem neuen Blatt (Tastenkürzel zum Hinzufügen eines neuen Blattes: Umschalt + F11).
• Klicken Sie auf der Registerkarte „Daten“ auf „Aus anderen Quellen“ -> Wählen Sie „Aus Microsoft Query“.

• Im Feld Datenquelle auswählen:
  • Klicken Sie auf Excel-Dateien und dann auf OK.
  • Wählen Sie den Pfad Ihrer Excel-Datei aus, wählen Sie dann Ihre Datei aus und klicken Sie auf „OK“.
  • Die benannten Bereiche werden im Feld „Abfrageassistent – Spalten auswählen“ angezeigt.

• Im Abfrageassistenten:
  • Ziehen Sie jeden benannten Bereich (mit der Pfeilschaltfläche) in die „Spalten in Ihrem Abfragefeld“.
  • Klicken Sie auf Weiter – Fehler vergessen.
  • Und klicken Sie auf OK

• Jetzt haben wir alle Daten in unserem Abfrageeditor und müssen nur noch die Daten aus allen benannten Bereichen kombinieren. Klicken Sie dazu auf den kleinen SQL-Button.

• Löschen Sie im SQL-Feld den gesamten Text, schreiben Sie eine neue Abfrage und klicken Sie auf OK.

Wählen Sie * aus dem Jahr 2005Union allSelect * aus dem Jahr 2006Union allSelect * aus dem Jahr 2007Union allSelect * aus dem Jahr 2008

• Nun enthält die auf dem Bildschirm erscheinende Tabelle die Daten aller 4 Blätter.
• Wir müssen lediglich auf die Registerkarte „Datei“ gehen und diese Tabelle in Excel importieren.
• Am Ende importieren Sie die Daten als Pivot-Tabelle zurück in Excel.
  • Klicken Sie im Menü „Datei“ -> auf „Daten an Microsoft Excel zurückgeben“.
  • Sie können sehen, dass wir insgesamt (auf den 4 Blättern) 592 Datensätze haben.
  • Wenn Sie außerdem weitere Daten zu einem der vier Blätter hinzufügen, wird die Pivot-Tabelle aktualisiert, sobald Sie sie aktualisieren.