Pivottabell over flere dataområder

Formulering av problemet

Pivottabeller er et av de mest fantastiske verktøyene i Excel. Men så langt, dessverre, kan ingen av versjonene av Excel gjøre en så enkel og nødvendig ting på farten som å bygge et sammendrag for flere innledende dataområder, for eksempel på forskjellige ark eller i forskjellige tabeller:

Før vi begynner, la oss avklare et par punkter. A priori tror jeg at følgende betingelser er oppfylt i våre data:

• Tabeller kan ha et hvilket som helst antall rader med alle data, men de må ha samme overskrift.
• Det skal ikke være ekstra data på arkene med kildetabeller. Ett ark – ett bord. For å kontrollere, anbefaler jeg deg å bruke en hurtigtast Ctrl+Slutt, som flytter deg til den sist brukte cellen i regnearket. Ideelt sett bør dette være den siste cellen i datatabellen. Hvis når du klikker på Ctrl+Slutt enhver tom celle til høyre eller under tabellen er uthevet – slett disse tomme kolonnene til høyre eller rader under tabellen etter tabellen og lagre filen.

Metode 1: Bygg tabeller for en pivot ved hjelp av Power Query

Fra og med 2010-versjonen for Excel, er det et gratis Power Query-tillegg som kan samle inn og transformere alle data og deretter gi det som en kilde for å bygge en pivottabell. Å løse problemet vårt ved hjelp av dette tillegget er ikke vanskelig i det hele tatt.

La oss først lage en ny tom fil i Excel - montering vil finne sted i den og deretter opprettes en pivottabell i den.

Så på fanen Data (hvis du har Excel 2016 eller nyere) eller på fanen Strømforespørsel (hvis du har Excel 2010-2013) velg kommandoen Lag spørring – Fra fil – Excel (Få data – fra fil – Excel) og spesifiser kildefilen med tabellene som skal samles inn:

I vinduet som vises, velg et hvilket som helst ark (det spiller ingen rolle hvilket) og trykk på knappen nedenfor Endring (Redigere):

Power Query Query Editor-vinduet skal åpnes på toppen av Excel. På høyre side av vinduet på panelet Be om parametere slett alle automatisk opprettede trinn unntatt de første – kilde (Kilde):

Nå ser vi en generell liste over alle ark. Hvis det i tillegg til dataark er noen andre sideark i filen, er oppgaven vår på dette trinnet å velge bare de arkene som informasjonen skal lastes fra, unntatt alle de andre som bruker filteret i tabelloverskriften:

Slett alle kolonner unntatt kolonne Dataved å høyreklikke på en kolonneoverskrift og velge Slett andre kolonner (Fjerne andre kolonner):

Du kan deretter utvide innholdet i de innsamlede tabellene ved å klikke på dobbeltpilen øverst i kolonnen (avkrysningsboks Bruk originalt kolonnenavn som prefiks du kan slå den av):

Hvis du gjorde alt riktig, bør du på dette tidspunktet se innholdet i alle tabellene samlet under hverandre:

Det gjenstår å heve den første raden til tabelloverskriften med knappen Bruk første linje som overskrifter (Bruk første rad som overskrifter) tab Hjemprodukt (Hjem) og fjern dupliserte tabelloverskrifter fra dataene ved hjelp av et filter:

Lagre alt som er gjort med kommandoen Lukk og last – Lukk og last inn... (Lukk og last – Lukk og last til...) tab Hjemprodukt (Hjem), og velg alternativet i vinduet som åpnes Kun tilkobling (Kun tilkobling):

Alt. Det gjenstår bare å lage et sammendrag. For å gjøre dette, gå til fanen Sett inn – Pivottabell (Sett inn – pivottabell), velg alternativet Bruk ekstern datakilde (Bruk ekstern datakilde)og deretter ved å klikke på knappen Velg tilkobling, vår forespørsel. Videre opprettelse og konfigurasjon av pivoten skjer på en helt standard måte ved å dra feltene vi trenger inn i radene, kolonnene og verdiområdet:

Hvis kildedataene endres i fremtiden eller noen flere butikkark legges til, vil det være nok å oppdatere spørringen og sammendraget vårt ved å bruke kommandoen Oppdater alle tab Data (Data – Oppdater alle).

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

En annen løsning på problemet vårt er representert av denne makroen, som lager et datasett (cache) for pivottabellen ved å bruke kommandoen ENHET SQL spørringsspråk. Denne kommandoen kombinerer tabeller fra alle spesifiserte i matrisen Arknavn ark av boken i én enkelt datatabell. Det vil si at i stedet for fysisk å kopiere og lime inn områder fra forskjellige ark til ett, gjør vi det samme i datamaskinens RAM. Deretter legger makroen til et nytt ark med det gitte navnet (variabel Resultatarknavn) og lager et fullverdig (!) sammendrag på det basert på den innsamlede cachen.

For å bruke en makro, bruk Visual Basic-knappen på fanen utvikler (Utvikler) eller hurtigtast andre+F11. Deretter setter vi inn en ny tom modul gjennom menyen Sett inn – modul og kopier følgende kode dit:

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 pivoten vil bli vist ResultSheetName = "Pivot av" hun 'en array navn med kildetabeller SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'vi danner en hurtigbuffer 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) & "$]" Neste i Sett objRS = CreateObject("ADODB.Recordset") objRS .Åpne Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Avslutt med 'opprett arket på nytt for å vise den resulterende pivottabellen Ved feil Fortsett neste Application.DisplayAlerts = False Worksheets(ResultSheetName).Slett Sett wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'viser det genererte hurtigbuffersammendraget på dette arket. Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotCache.CreatePivotTable"(R") Set objPivotCache = Nothing Range("A3"). Velg End With End Sub    

Den ferdige makroen kan deretter kjøres med en hurtigtast andre+F8 eller Makro-knappen på fanen utvikler (Utvikler – Makroer).

Ulemper med denne tilnærmingen:

• Dataene oppdateres ikke fordi cachen ikke har noen tilkobling til kildetabellene. Hvis du endrer kildedataene, må du kjøre makroen på nytt og bygge sammendraget på nytt.
• Når du endrer antall ark, er det nødvendig å redigere makrokoden (array Arknavn).
  

Men til slutt får vi et ekte fullverdig pivotbord, bygget på flere serier fra forskjellige ark:

Det!

Teknisk merknad: hvis du får en feilmelding som "Leverandør ikke registrert" når du kjører makroen, har du mest sannsynlig en 64-biters versjon av Excel eller en ufullstendig versjon av Office er installert (ingen tilgang). For å fikse situasjonen, bytt ut fragmentet i makrokoden:

	 Leverandør=Microsoft.Jet.OLEDB.4.0;  

til:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Og last ned og installer den gratis databehandlingsmotoren fra Access fra Microsofts nettsted – Microsoft Access Database Engine 2010 Redistributable

Metode 3: Konsolider pivottabellveiviseren fra gamle versjoner av Excel

Denne metoden er litt utdatert, men likevel verdt å nevne. Formelt sett, i alle versjoner til og med 2003, var det et alternativ i PivotTable Wizard for å "bygge en pivot for flere konsolideringsområder". Imidlertid vil en rapport konstruert på denne måten, dessverre, bare være et ynkelig utseende av en ekte fullverdig oppsummering og støtter ikke mange av "brikkene" til konvensjonelle pivottabeller:

I en slik pivot er det ingen kolonneoverskrifter i feltlisten, det er ingen fleksibel strukturinnstilling, settet med funksjoner som brukes er begrenset, og generelt er alt dette lite likt en pivottabell. Kanskje det er derfor, fra og med 2007, fjernet Microsoft denne funksjonen fra standarddialogen når du opprettet pivottabellrapporter. Nå er denne funksjonen kun tilgjengelig via en egendefinert knapp Pivottabellveiviser(Veviser for pivottabell), som om ønskelig kan legges til hurtigtilgangsverktøylinjen via Fil – Alternativer – Tilpass verktøylinjen for hurtigtilgang – Alle kommandoer (Fil — Alternativer — Tilpass verktøylinjen for hurtigtilgang — Alle kommandoer):

Etter å ha klikket på knappen lagt til, må du velge riktig alternativ i det første trinnet i veiviseren:

Og i neste vindu velger du hvert område etter tur og legger det til i den generelle listen:

Men igjen, dette er ikke en fullverdig oppsummering, så ikke forvent for mye av det. Jeg kan anbefale dette alternativet bare i veldig enkle tilfeller.

• Opprette rapporter med pivottabeller
• Sett opp beregninger i pivottabeller
• Hva er makroer, hvordan du bruker dem, hvor du skal kopiere VBA-kode osv.
• Datainnsamling fra flere ark til ett (PLEX-tillegg)