Pivot Tabell iwwer verschidde Dateberäicher

Formuléierung vum Problem

Pivot Dëscher sinn ee vun den erstaunlechsten Tools an Excel. Awer bis elo, leider, kann keng vun de Versioune vun Excel sou eng einfach an noutwendeg Saach op der Flucht maachen wéi e Resumé fir e puer initial Dateberäicher ze bauen, zum Beispill op verschiddene Blieder oder a verschiddenen Dëscher:

Ier mer ufänken, loosse mer e puer Punkten klären. A priori gleewen ech datt déi folgend Konditiounen an eisen Donnéeën erfëllt sinn:

• Dëscher kënnen all Zuel vun Zeile mat all Donnéeën hunn, mä si mussen déi selwecht Header hunn.
• Et sollt keng extra Donnéeën op de Blieder mat Quelltabellen sinn. Ee Blat - een Dësch. Fir ze kontrolléieren, roden ech Iech eng Tastatur Ofkiirzung ze benotzen Ctrl+Enn, déi Iech op déi lescht benotzt Zell am Aarbechtsblat beweegt. Idealerweis sollt dëst déi lescht Zell an der Datentabelle sinn. Wann Dir klickt op Ctrl+Enn all eidel Zelle op der rietser oder ënner der Tabell gëtt markéiert - läscht dës eidel Kolonnen op der rietser oder Reihen ënner der Tabell no der Tabell a späichert d'Datei.

Method 1: Build Dëscher fir e Pivot benotzt Power Query

Vun der 2010 Versioun fir Excel un, gëtt et e gratis Power Query Add-in deen all Daten sammelen an transforméiere kann an se dann als Quell ginn fir eng Pivot Tabell ze bauen. Eise Problem mat der Hëllef vun dësem Add-in ze léisen ass guer net schwéier.

Als éischt, loosst eis eng nei eidel Datei an Excel erstellen - d'Versammlung wäert dran stattfannen an da gëtt eng Pivot-Table dran erstallt.

Dann op der Tab Daten (wann Dir Excel 2016 oder spéider hutt) oder op der Tab Muecht Ufro (wann Dir Excel 2010-2013 hutt) wielt de Kommando Erstellt Ufro - Vun Datei - Excel (Gitt Daten - Vun Datei - Excel) a spezifizéiert d'Quelldatei mat den Dëscher déi gesammelt ginn:

An der Fënster déi erschéngt, wielt all Blat (et ass egal wéi eng) an dréckt op de Knäppchen hei ënnen änneren (Ännerung):

D'Power Query Query Editor Fënster soll uewen op Excel opmaachen. Op der rietser Säit vun der Fënster op der Panel Ufro Parameteren läschen all automatesch erstallt Schrëtt ausser déi éischt - Source (Source):

Elo gesi mir eng allgemeng Lëscht vun alle Blieder. Wann zousätzlech zu den Dateblieder e puer aner Säiteblieder an der Datei sinn, dann ass eis Aufgab op dësem Schrëtt nëmmen déi Blieder aus deenen d'Informatioun muss geluede ginn, ausser all déi aner déi de Filter am Tabellkopf benotzen:

Läschen all Kolonnen ausser Kolonn Datenandeems Dir op eng Kolonntitel klickt a wielt Aner Kolonnen läschen (Entfernen aner Kolonnen):

Dir kënnt dann den Inhalt vun de gesammelten Dëscher ausbauen andeems Dir op den Duebelpfeil uewen an der Kolonn klickt (Checkbox Benotzt den ursprénglechen Kolonnnumm als Präfix Dir kënnt et ausschalten):

Wann Dir alles richteg gemaach hutt, da sollt Dir op dësem Punkt den Inhalt vun allen Dëscher gesinn, déi een ënner deem aneren gesammelt sinn:

Et bleift déi éischt Zeil op den Dësch Header mat de Knäppchen ze erhéijen Benotzt déi éischt Zeil als Header (Benotzt déi éischt Zeil als Header) Reiter Home (Heem) a läscht duplizéiert Tabellkopf vun den Daten mat engem Filter:

Späichert alles wat mam Kommando gemaach gëtt Zoumaachen a lueden - Zoumaachen a lueden an ... (Zoumaachen & Lueden - Zoumaachen & Lueden op ...) Reiter Home (Heem), an an der Fënster déi opmaacht, wielt d'Optioun Nëmme Verbindung (Nëmmen Verbindung):

Alles. Et bleift nëmmen e Resumé ze bauen. Fir dëst ze maachen, gitt op d'Tab Insert - PivotTable (Insert - Pivot Table), wielt d'Optioun Benotzt extern Datenquell (Benotzt extern Datenquell)an dann andeems Dir op de Knäppchen klickt Wielt Verbindung, eis Ufro. Weider Schafung a Konfiguratioun vum Pivot geschitt op eng komplett Standard Manéier andeems d'Felder déi mir brauchen an d'Reihen, Kolonnen a Wäerterberäich zéien:

Wann d'Quelldaten an der Zukunft ännert oder e puer méi Buttekblieder bäigefüügt ginn, da geet et duer fir d'Ufro an eise Resumé mat dem Kommando ze aktualiséieren Alles erfrëschen Reiter Daten (Daten - Alles erfrëschen).

Method 2. Mir verbannen Dëscher mat der UNION SQL Kommando an engem Macro

Eng aner Léisung fir eise Problem gëtt duerch dëse Makro vertrueden, deen en Dateset (Cache) fir d'Pivottabelle mat dem Kommando erstellt UNITY SQL Ufro Sprooch. Dëse Kommando kombinéiert Dëscher vun all spezifizéierter an der Array Blat Nimm Blieder vum Buch an eng eenzeg Datentabelle. Dat ass, amplaz kierperlech Kopie an Paste Gamme vu verschiddene Blieder zu engem, mir maachen déi selwecht am Computer d'RAM. Da füügt de Makro en neit Blat mat dem Numm (Variabel ResultSheetName) a schafft e vollwäertege (!) Resumé doriwwer op Basis vum gesammelten Cache.

Fir e Macro ze benotzen, benotzt de Visual Basic Knäppchen op der Tab Entwéckler (Entwéckler) oder Tastatur Ofkiirzung Alt+F11. Da setzen mir en neien eidele Modul duerch de Menü Insert - Modul a kopéiert de folgende Code do:

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 'sheet name where the resulting pivot will be displayed ResultSheetName = "Pivot of" she 'an array Nimm mat Quelltabellen SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") 'mir bilden e Cache fir Tabellen aus Blieder vu SheetsNames Mat ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Fir i = LBound (SheetsNames) Fir UBound (SheetsNames) arSQL(i + 1) = "SELECT * VUN [" & 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 ) End With 'Re-create the sheet to display the resulting pivot table On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot. Name = ResultSheetName 'weist de generéierten Cache-Zesummefaassung op dësem Blat Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Näischt Mat wsPivot objPivotCache.CreatePivotCache.CreatePivotTable":R3 SetupTangivotT":R3 Set objPivotCache = Nothing Range ("AXNUMX"). Wielt Enn Mat Enn Sub    

De fäerdege Makro kann dann mat enger Tastatur Ofkiirzung lafen Alt+F8 oder de Macros Knäppchen op der Tab Entwéckler (Entwéckler - Macros).

Nodeeler vun dëser Approche:

• D'Donnéeën ginn net aktualiséiert well de Cache keng Verbindung mat de Quelltabellen huet. Wann Dir d'Quelldaten ännert, musst Dir de Macro erëm lafen an de Resumé erëm opbauen.
• Wann Dir d'Zuel vun de Blieder ännert, ass et néideg de Makrocode z'änneren (Array Blat Nimm).
  

Awer um Enn kréie mir e richtege vollwäertege Pivot-Dësch, opgebaut op verschiddene Beräicher vu verschiddene Blieder:

Et!

Technesch Notiz: Wann Dir e Feeler wéi "Provider net registréiert" kritt wann Dir de Macro leeft, dann hutt Dir héchstwahrscheinlech eng 64-Bit Versioun vun Excel oder eng onkomplett Versioun vum Office ass installéiert (keen Zougang). Fir d'Situatioun ze fixéieren, ersetzt de Fragment am Makrocode:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

un:

	Provider=Microsoft.ACE.OLEDB.12.0;  

An download an installéiert de gratis Dateveraarbechtungsmotor vum Access vun der Microsoft Websäit - Microsoft Access Database Engine 2010 Redistributable

Method 3: Konsolidéieren PivotTable Wizard aus al Versioune vun Excel

Dës Method ass e bëssen ausgeglach, awer nach ëmmer erwähnenswert. Formell geschwat, an all Versioune bis an och 2003, gouf et eng Optioun am PivotTable Wizard fir "e Pivot fir verschidde Konsolidéierungsberäicher ze bauen". Wéi och ëmmer, e Bericht, deen op dës Manéier gebaut gëtt, wäert leider nëmmen e traureg Schëld vun engem richtege vollwäertege Resumé sinn an ënnerstëtzt net vill vun de "Chips" vu konventionelle Pivottabellen:

An esou engem Pivot gëtt et keng Kolonnrubriken an der Feldlëscht, et gëtt keng flexibel Strukturastellung, de Set vu Funktiounen déi benotzt gëtt ass limitéiert, an allgemeng ass dëst alles net ganz ähnlech mat enger Pivot-Tabelle. Vläicht ass dat firwat, ab 2007, Microsoft huet dës Funktioun aus dem Standarddialog geläscht wann Dir Pivot-Table Berichter erstellt. Elo ass dës Feature nëmmen duerch e personaliséierte Knäppchen verfügbar PivotTable Wizard(Pivot Table Wizard), déi, wann Dir wëllt, op d'Quick Access Toolbar via Datei - Optiounen - Customize Quick Access Toolbar - All Kommandoen (Datei - Optiounen - Quick Access Toolbar personaliséieren - All Kommandoen):

Nodeems Dir op de addéierte Knäppchen klickt, musst Dir déi entspriechend Optioun am éischte Schrëtt vum Wizard auswielen:

An dann an der nächster Fënster, wielt all Gamme am Tour a füügt se op d'allgemeng Lëscht:

Awer erëm, dëst ass kee vollwäertege Resumé, also erwaart Iech net ze vill dovun. Ech kann dës Optioun nëmmen a ganz einfache Fäll recommandéieren.

• Erstellt Berichter mat PivotTables
• Setzt Berechnungen an PivotTables op
• Wat sinn Makroen, wéi se se benotzen, wou de VBA Code kopéiert, etc.
• Datesammlung vu multiple Blieder op een (PLEX Add-on)