Pivot-Tabelle über mehrere Datenbereiche hinweg

Formulierung des Problems

Pivot-Tabellen sind eines der erstaunlichsten Tools in Excel. Aber leider kann bisher keine der Excel-Versionen eine so einfache und notwendige Sache wie das Erstellen einer Zusammenfassung für mehrere anfängliche Datenbereiche, die sich beispielsweise auf verschiedenen Blättern oder in verschiedenen Tabellen befinden:

Bevor wir beginnen, lassen Sie uns ein paar Punkte klären. A priori glaube ich, dass die folgenden Bedingungen in unseren Daten erfüllt sind:

• Tabellen können eine beliebige Anzahl von Zeilen mit beliebigen Daten haben, aber sie müssen denselben Header haben.
• Auf den Blättern mit Quelltabellen sollten keine zusätzlichen Daten vorhanden sein. Ein Blatt – eine Tabelle. Zur Steuerung rate ich Ihnen, eine Tastenkombination zu verwenden Ctrl+Ende, wodurch Sie zur zuletzt verwendeten Zelle im Arbeitsblatt gelangen. Idealerweise sollte dies die letzte Zelle in der Datentabelle sein. Wenn Sie auf klicken Ctrl+Ende jede leere Zelle rechts oder unterhalb der Tabelle wird hervorgehoben – löschen Sie diese leeren Spalten rechts oder Zeilen unterhalb der Tabelle nach der Tabelle und speichern Sie die Datei.

Methode 1: Erstellen Sie mithilfe von Power Query Tabellen für einen Pivot

Ab der Version 2010 für Excel gibt es ein kostenloses Power Query-Add-In, das beliebige Daten sammeln und transformieren und sie dann als Quelle zum Erstellen einer Pivot-Tabelle bereitstellen kann. Die Lösung unseres Problems mit Hilfe dieses Add-Ins ist überhaupt nicht schwierig.

Lassen Sie uns zuerst eine neue leere Datei in Excel erstellen – darin wird die Assemblierung stattfinden und dann wird darin eine Pivot-Tabelle erstellt.

Dann auf der Registerkarte Datum (wenn Sie Excel 2016 oder höher haben) oder auf der Registerkarte Power Query (wenn Sie Excel 2010-2013 haben) wählen Sie den Befehl Abfrage erstellen – Aus Datei – Excel (Daten abrufen — Aus Datei — Excel) und geben Sie die Quelldatei mit den zu sammelnden Tabellen an:

Wählen Sie im angezeigten Fenster ein beliebiges Blatt aus (egal welches) und drücken Sie die Schaltfläche unten Change (Bearbeiten):

Das Power Query-Abfrage-Editor-Fenster sollte über Excel geöffnet werden. Auf der rechten Seite des Fensters auf dem Panel Anforderungsparameter alle automatisch erstellten Schritte außer dem ersten löschen – Quelle (Quelle):

Jetzt sehen wir eine allgemeine Liste aller Blätter. Wenn neben den Datenblättern weitere Seitenblätter in der Datei vorhanden sind, besteht unsere Aufgabe in diesem Schritt darin, nur die Blätter auszuwählen, aus denen Informationen geladen werden müssen, und alle anderen mithilfe des Filters im Tabellenkopf auszuschließen:

Alle Spalten außer Spalte löschen Datumindem Sie mit der rechten Maustaste auf eine Spaltenüberschrift klicken und auswählen Löschen Sie andere Spalten (Entfernen andere Spalten):

Anschließend können Sie den Inhalt der gesammelten Tabellen erweitern, indem Sie auf den Doppelpfeil am oberen Rand der Spalte klicken (Checkbox Verwenden Sie den ursprünglichen Spaltennamen als Präfix du kannst es ausschalten):

Wenn Sie alles richtig gemacht haben, sollten Sie an dieser Stelle die Inhalte aller Tabellen gesammelt untereinander sehen:

Es bleibt, die erste Zeile mit der Schaltfläche zum Tabellenkopf zu heben Verwenden Sie die erste Zeile als Überschrift (Verwenden Sie die erste Zeile als Überschrift) Tab Startseite (Home) und entfernen Sie doppelte Tabellenüberschriften mit einem Filter aus den Daten:

Speichern Sie alles, was Sie mit dem Befehl gemacht haben Schließen und laden – Schließen und laden… (Schließen & Laden — Schließen & Laden nach…) Tab Startseite (Home), und wählen Sie im sich öffnenden Fenster die Option aus Nur Verbindung (Nur Verbindung):

Alles. Es bleibt nur noch eine Zusammenfassung zu erstellen. Gehen Sie dazu auf die Registerkarte Einfügen – PivotTable (Einfügen – Pivot-Tabelle), wählen Sie die Option Externe Datenquelle verwenden (Externe Datenquelle verwenden)und dann durch Klicken auf die Schaltfläche Wählen Sie die Verbindung, unsere Anfrage. Die weitere Erstellung und Konfiguration des Pivots erfolgt ganz normal, indem wir die benötigten Felder in den Zeilen-, Spalten- und Wertebereich ziehen:

Wenn sich in Zukunft die Quelldaten ändern oder noch ein paar Lagerblätter hinzugefügt werden, dann reicht es aus, die Abfrage und unsere Zusammenfassung mit dem Befehl zu aktualisieren Alle erfrischen Tab Datum (Daten — Alle aktualisieren).

Methode 2. Wir vereinen Tabellen mit dem UNION-SQL-Befehl in einem Makro

Eine weitere Lösung für unser Problem stellt dieses Makro dar, das mit dem Befehl einen Datensatz (Cache) für die Pivot-Tabelle erstellt EINHEIT SQL-Abfragesprache. Dieser Befehl kombiniert Tabellen aus allen im Array angegebenen Blattnamen Blätter des Buches in eine einzige Datentabelle. Das heißt, anstatt physisch Bereiche von verschiedenen Blättern zu einem zu kopieren und einzufügen, tun wir dasselbe im Arbeitsspeicher des Computers. Dann fügt das Makro ein neues Blatt mit dem angegebenen Namen (Variable Name des Ergebnisblatts) und erstellt darauf basierend auf dem gesammelten Cache eine vollwertige (!) Zusammenfassung.

Um ein Makro zu verwenden, verwenden Sie die Visual Basic-Schaltfläche auf der Registerkarte Entwickler (Entwickler) oder Tastenkürzel Andere+F11. Dann fügen wir über das Menü ein neues leeres Modul ein Einfügen – Modul und kopiere dort folgenden Code:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'Blattname, auf dem der resultierende Drehpunkt angezeigt wird ResultSheetName = "Pivot" 'ein Array von Blättern Namen mit Quelltabellen SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'Wir bilden einen Cache für Tabellen aus Blättern von SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Beenden Sie mit 'Erstellen Sie das Blatt neu, um die resultierende Pivot-Tabelle anzuzeigen Bei Fehler Fortsetzen der nächsten Anwendung.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'Anzeige der generierten Cache-Zusammenfassung auf diesem Blatt Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3"). Wählen Sie End With End Sub    

Das fertige Makro kann dann mit einem Tastaturkürzel ausgeführt werden Andere+F8 oder die Schaltfläche Makros auf der Registerkarte Entwickler (Entwickler – Makros).

Nachteile dieses Ansatzes:

• Die Daten werden nicht aktualisiert, da der Cache keine Verbindung zu den Quelltabellen hat. Wenn Sie die Quelldaten ändern, müssen Sie das Makro erneut ausführen und die Zusammenfassung erneut erstellen.
• Beim Ändern der Blattanzahl muss der Makrocode (array Blattnamen).
  

Aber am Ende erhalten wir eine wirklich vollwertige Pivot-Tabelle, die auf mehreren Bereichen aus verschiedenen Blättern aufgebaut ist:

Voilà!

Technischer Hinweis: Wenn Sie beim Ausführen des Makros eine Fehlermeldung wie „Anbieter nicht registriert“ erhalten, haben Sie höchstwahrscheinlich eine 64-Bit-Version von Excel oder eine unvollständige Version von Office installiert (kein Access). Um die Situation zu beheben, ersetzen Sie das Fragment im Makrocode:

	 Provider = Microsoft.Jet.OLEDB.4.0;  

zu:

	Anbieter=Microsoft.ACE.OLEDB.12.0;  

Und laden Sie die kostenlose Datenverarbeitungs-Engine von Access von der Microsoft-Website herunter und installieren Sie sie – Microsoft Access Database Engine 2010 Redistributable

Methode 3: Konsolidieren Sie den PivotTable-Assistenten aus alten Excel-Versionen

Diese Methode ist etwas veraltet, aber dennoch erwähnenswert. Formal gab es in allen Versionen bis einschließlich 2003 im PivotTable Wizard die Option „einen Pivot für mehrere Konsolidierungskreise zu bauen“. Ein so konstruierter Report wird aber leider nur ein kläglicher Schein einer wirklich vollwertigen Zusammenfassung sein und unterstützt viele der „Chips“ herkömmlicher Pivot-Tabellen nicht:

In einem solchen Pivot gibt es keine Spaltenüberschriften in der Feldliste, es gibt keine flexible Struktureinstellung, die Menge der verwendeten Funktionen ist begrenzt, und im Allgemeinen ist dies alles nicht sehr ähnlich zu einer Pivot-Tabelle. Vielleicht hat Microsoft deshalb ab 2007 diese Funktion aus dem Standarddialog beim Erstellen von Pivot-Tabellen-Berichten entfernt. Jetzt ist diese Funktion nur über eine benutzerdefinierte Schaltfläche verfügbar PivotTable-Assistent(Pivot-Tabellen-Assistent), die bei Bedarf über zur Symbolleiste für den Schnellzugriff hinzugefügt werden kann Datei – Optionen – Symbolleiste für den Schnellzugriff anpassen – Alle Befehle (Datei – Optionen – Symbolleiste für den Schnellzugriff anpassen – Alle Befehle):

Nachdem Sie auf die hinzugefügte Schaltfläche geklickt haben, müssen Sie im ersten Schritt des Assistenten die entsprechende Option auswählen:

Wählen Sie dann im nächsten Fenster nacheinander jeden Bereich aus und fügen Sie ihn der allgemeinen Liste hinzu:

Aber noch einmal, dies ist keine vollständige Zusammenfassung, also erwarten Sie nicht zu viel davon. Ich kann diese Option nur in ganz einfachen Fällen empfehlen.

• Erstellen von Berichten mit PivotTables
• Richten Sie Berechnungen in PivotTables ein
• Was sind Makros, wie verwendet man sie, wohin kopiert man VBA-Code usw.
• Datenerfassung von mehreren Blättern zu einem (PLEX-Add-on)