Pivottabel på tværs af flere dataområder

Formulering af problemet

Pivottabeller er et af de mest fantastiske værktøjer i Excel. Men indtil videre, desværre, kan ingen af ​​versionerne af Excel gøre en så enkel og nødvendig ting på farten som at bygge et resumé for flere indledende dataområder placeret, for eksempel på forskellige ark eller i forskellige tabeller:

Før vi starter, lad os præcisere et par punkter. A priori mener jeg, at følgende betingelser er opfyldt i vores data:

• Tabeller kan have et hvilket som helst antal rækker med alle data, men de skal have samme overskrift.
• Der bør ikke være ekstra data på arkene med kildetabeller. Et ark – et bord. For at kontrollere råder jeg dig til at bruge en tastaturgenvej Ctrl+Ende, som flytter dig til den sidst brugte celle i regnearket. Ideelt set bør dette være den sidste celle i datatabellen. Hvis når du klikker på Ctrl+Ende enhver tom celle til højre eller under tabellen er fremhævet – slet disse tomme kolonner til højre eller rækker under tabellen efter tabellen og gem filen.

Metode 1: Byg tabeller til en pivot ved hjælp af Power Query

Fra 2010-versionen til Excel er der et gratis Power Query-tilføjelsesprogram, der kan indsamle og transformere alle data og derefter give det som en kilde til opbygning af en pivottabel. At løse vores problem ved hjælp af denne tilføjelse er slet ikke svært.

Lad os først oprette en ny tom fil i Excel – samling vil finde sted i den, og derefter oprettes en pivottabel i den.

Så på fanen data (hvis du har Excel 2016 eller nyere) eller på fanen Strømforespørgsel (hvis du har Excel 2010-2013) vælg kommandoen Opret forespørgsel – Fra fil – Excel (Hent data - fra fil - Excel) og angiv kildefilen med de tabeller, der skal indsamles:

I vinduet, der vises, skal du vælge et hvilket som helst ark (det er lige meget hvilket) og trykke på knappen nedenfor Skift (Edit):

Vinduet Power Query Query Editor bør åbne oven på Excel. På højre side af vinduet på panelet Forespørgselsparametre slet alle automatisk oprettede trin undtagen det første – Kilde (Kilde):

Nu ser vi en generel liste over alle ark. Hvis der ud over dataark er nogle andre sideark i filen, så er vores opgave på dette trin kun at vælge de ark, hvorfra information skal indlæses, undtagen alle de andre, der bruger filteret i tabeloverskriften:

Slet alle kolonner undtagen kolonne dataved at højreklikke på en kolonneoverskrift og vælge Slet andre kolonner (Fjerne andre kolonner):

Du kan derefter udvide indholdet af de indsamlede tabeller ved at klikke på dobbeltpilen øverst i kolonnen (afkrydsningsfelt Brug det originale kolonnenavn som præfiks du kan slå det fra):

Hvis du gjorde alt korrekt, skal du på dette tidspunkt se indholdet af alle tabeller samlet under hinanden:

Det er tilbage at hæve den første række til tabeloverskriften med knappen Brug første linje som overskrifter (Brug første række som overskrifter) fanen Home (Hjem) og fjern duplikerede tabeloverskrifter fra dataene ved hjælp af et filter:

Gem alt gjort med kommandoen Luk og indlæs – Luk og læs ind... (Luk og indlæs — Luk og indlæs til...) fanen Home (Hjem), og vælg indstillingen i det vindue, der åbnes Kun tilslutning (Kun tilslutning):

Alt. Det er kun tilbage at lave et resumé. For at gøre dette skal du gå til fanen Indsæt – Pivottabel (Indsæt — pivottabel), vælg indstillingen Brug ekstern datakilde (Brug ekstern datakilde)og derefter ved at klikke på knappen Vælg tilslutning, vores anmodning. Yderligere oprettelse og konfiguration af pivoten sker på en helt standard måde ved at trække de felter, vi har brug for, ind i rækkerne, kolonnerne og værdiområdet:

Hvis kildedataene ændres i fremtiden, eller der tilføjes et par flere butiksark, vil det være nok at opdatere forespørgslen og vores oversigt ved hjælp af kommandoen Opdater alle fanen data (Data – Opdater alle).

Metode 2. Vi forener tabeller med UNION SQL-kommandoen i en makro

En anden løsning på vores problem er repræsenteret af denne makro, som opretter et datasæt (cache) til pivottabellen ved hjælp af kommandoen ENHED SQL-forespørgselssprog. Denne kommando kombinerer tabeller fra alle specificerede i arrayet Arknavne ark af bogen i en enkelt datatabel. Det vil sige, at i stedet for fysisk at kopiere og indsætte intervaller fra forskellige ark til ét, gør vi det samme i computerens RAM. Derefter tilføjer makroen et nyt ark med det givne navn (variabel Resultatarknavn) og opretter en fuldgyldig (!) oversigt på det baseret på den indsamlede cache.

For at bruge en makro skal du bruge Visual Basic-knappen på fanen developer (Udvikler) eller tastaturgenvej andre+F11. Så indsætter vi et nyt tomt modul gennem menuen Indsæt – Modul og kopier følgende kode dertil:

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 's arknavn, hvor den resulterende pivot vil blive vist ResultSheetName = "Pivot of sheet" 'en array navne med kildetabeller SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'vi danner en cache for tabeller fra ark fra SheetsNames med ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Næste i Indstil objRS = CreateObject("ADODB.Recordset") objRS .Åbn Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'genopret arket for at vise den resulterende pivot-tabel Ved fejl Genoptag næste Application.DisplayAlerts = False Worksheets(ResultSheetName).Slet sæt wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'viser den genererede cache-oversigt på dette ark. Sæt objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Indstil objPivotCache.Recordset = objRS Indstil objRS = Intet med wsPivot objPivotCache.CreatePivotCache.CreatePivotT."(R) objPivotCache = Nothing Range("A3"). Vælg End With End Sub    

Den færdige makro kan derefter køres med en tastaturgenvej andre+F8 eller knappen Makroer på fanen developer (Udvikler – Makroer).

Ulemper ved denne tilgang:

• Dataene opdateres ikke, fordi cachen ikke har nogen forbindelse til kildetabellerne. Hvis du ændrer kildedataene, skal du køre makroen igen og bygge oversigten igen.
• Når du ændrer antallet af ark, er det nødvendigt at redigere makrokoden (array Arknavne).
  

Men i sidste ende får vi et rigtigt fuldgyldigt pivotbord, bygget på flere intervaller fra forskellige ark:

Sådan!

Teknisk note: hvis du får en fejl som "Provider ikke registreret", når du kører makroen, så har du højst sandsynligt en 64-bit version af Excel, eller en ufuldstændig version af Office er installeret (ingen Access). For at løse situationen skal du erstatte fragmentet i makrokoden:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

til:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Og download og installer den gratis databehandlingsmotor fra Access fra Microsofts websted – Microsoft Access Database Engine 2010 Redistributable

Metode 3: Konsolider PivotTable Wizard fra gamle versioner af Excel

Denne metode er lidt forældet, men stadig værd at nævne. Formelt set var der i alle versioner til og med 2003 en mulighed i PivotTable Wizard for at "bygge en pivot for flere konsolideringsområder". En rapport, der er konstrueret på denne måde, vil desværre kun være et ynkeligt udseende af et rigtigt fuldgyldigt resumé og understøtter ikke mange af "chipsene" i konventionelle pivottabeller:

I en sådan pivot er der ingen kolonneoverskrifter i feltlisten, der er ingen fleksibel strukturindstilling, det anvendte sæt af funktioner er begrænset, og generelt minder alt dette ikke meget om en pivottabel. Måske er det derfor, fra og med 2007, Microsoft fjernede denne funktion fra standarddialogen ved oprettelse af pivottabelrapporter. Nu er denne funktion kun tilgængelig via en brugerdefineret knap Pivottabel-guiden(Pivottabel-guiden), som, hvis det ønskes, kan tilføjes til Quick Access Toolbar via Fil – Indstillinger – Tilpas værktøjslinjen Hurtig adgang – Alle kommandoer (Fil — Indstillinger — Tilpas værktøjslinjen Hurtig adgang — Alle kommandoer):

Efter at have klikket på den tilføjede knap, skal du vælge den relevante mulighed i det første trin i guiden:

Og så i det næste vindue skal du vælge hvert område efter tur og tilføje det til den generelle liste:

Men igen, dette er ikke et fuldgyldigt resumé, så forvent ikke for meget af det. Jeg kan kun anbefale denne mulighed i meget simple tilfælde.

• Oprettelse af rapporter med pivottabeller
• Opsæt beregninger i pivottabeller
• Hvad er makroer, hvordan man bruger dem, hvor skal man kopiere VBA-kode osv.
• Dataindsamling fra flere ark til ét (PLEX-tilføjelse)