Innehåll
Formulering av problemet
Pivottabeller är ett av de mest fantastiska verktygen i Excel. Men än så länge, tyvärr, kan ingen av versionerna av Excel göra en så enkel och nödvändig sak i farten som att bygga en sammanfattning för flera initiala dataintervall som finns, till exempel på olika ark eller i olika tabeller:
Innan vi börjar, låt oss förtydliga ett par punkter. A priori tror jag att följande villkor är uppfyllda i våra uppgifter:
- Tabeller kan ha valfritt antal rader med vilken data som helst, men de måste ha samma rubrik.
- Det ska inte finnas några extra data på arken med källtabeller. Ett ark – ett bord. För att styra råder jag dig att använda ett kortkommando ctrl+Slutet, som flyttar dig till den senast använda cellen i kalkylbladet. Helst bör detta vara den sista cellen i datatabellen. Om när du klickar på ctrl+Slutet en tom cell till höger eller under tabellen markeras – ta bort dessa tomma kolumner till höger eller rader under tabellen efter tabellen och spara filen.
Metod 1: Bygg tabeller för en pivot med Power Query
Från och med 2010 års version för Excel finns det ett gratis Power Query-tillägg som kan samla in och omvandla all data och sedan ge den som en källa för att bygga en pivottabell. Att lösa vårt problem med hjälp av detta tillägg är inte alls svårt.
Låt oss först skapa en ny tom fil i Excel – montering kommer att ske i den och sedan skapas en pivottabell i den.
Sedan på fliken Data (om du har Excel 2016 eller senare) eller på fliken Strömfråga (om du har Excel 2010-2013) välj kommandot Skapa fråga – Från fil – Excel (Hämta data — från fil — Excel) och ange källfilen med tabellerna som ska samlas in:
I fönstret som visas väljer du valfritt ark (det spelar ingen roll vilket) och trycker på knappen nedan byta (Redigera):
Fönstret Power Query Query Editor bör öppnas ovanpå Excel. På höger sida av fönstret på panelen Begär parametrar ta bort alla automatiskt skapade steg utom det första – Källa (Källa):
Nu ser vi en allmän lista över alla ark. Om det förutom datablad finns några andra sidoblad i filen, är vår uppgift i det här steget att endast välja de blad från vilka information behöver laddas, exklusive alla andra som använder filtret i tabellhuvudet:
Ta bort alla kolumner utom kolumn Datagenom att högerklicka på en kolumnrubrik och välja Ta bort andra kolumner (Ta bort andra kolumner):
Du kan sedan utöka innehållet i de insamlade tabellerna genom att klicka på dubbelpilen högst upp i kolumnen (kryssrutan Använd det ursprungliga kolumnnamnet som prefix du kan stänga av den):
Om du gjorde allt korrekt, bör du vid det här laget se innehållet i alla tabeller som samlats under varandra:
Det återstår att höja den första raden till tabellhuvudet med knappen Använd första raden som rubriker (Använd första raden som rubriker) fliken Hem (Hem) och ta bort dubbletter av tabellrubriker från data med ett filter:
Spara allt gjort med kommandot Stäng och ladda – Stäng och ladda in... (Stäng & ladda — Stäng & ladda till...) fliken Hem (Hem), och välj alternativet i fönstret som öppnas Endast anslutning (Endast anslutning):
Allt. Det återstår bara att göra en sammanfattning. För att göra detta, gå till fliken Infoga – Pivottabell (Infoga — pivottabell), välj alternativet Använd extern datakälla (Använd extern datakälla)och sedan genom att klicka på knappen Välj anslutning, vår begäran. Ytterligare skapande och konfiguration av pivoten sker på ett helt standard sätt genom att dra de fält vi behöver till rader, kolumner och värdeområden:
Om källdata ändras i framtiden eller några fler butiksblad läggs till, räcker det med att uppdatera frågan och vår sammanfattning med kommandot Uppdatera alla fliken Data (Data – Uppdatera alla).
Metod 2. Vi förenar tabeller med UNION SQL-kommandot i ett makro
En annan lösning på vårt problem representeras av detta makro, som skapar en datamängd (cache) för pivottabellen med kommandot ENHET SQL frågespråk. Detta kommando kombinerar tabeller från alla specificerade i arrayen Bladnamn ark av boken till en enda datatabell. Det vill säga, istället för att fysiskt kopiera och klistra in intervall från olika ark till ett, gör vi samma sak i datorns RAM. Sedan lägger makrot till ett nytt ark med det angivna namnet (variabel ResultSheetName) och skapar en fullfjädrad (!) sammanfattning av den baserat på den insamlade cachen.
För att använda ett makro, använd Visual Basic-knappen på fliken utvecklare (Utvecklaren) eller kortkommandon andra+F11. Sedan lägger vi in en ny tom modul genom menyn Infoga – Modul och kopiera följande kod 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 bladnamn där den resulterande pivoten kommer att visas ResultSheetName = "Pivot av en array" namn med källtabeller SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'vi bildar en cache för tabeller från ark från 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ästa i Set objRS = CreateObject("ADODB.Recordset") objRS .Öppna Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Sluta med 'återskapa arket för att visa den resulterande pivottabellen Vid fel Återuppta nästa applikation.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'visa den genererade cache-sammanfattningen på det här arket Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Ingenting med wsPivot objPivotCache.CreatePivotCache.CreatePivotTa"(R") SetablePstinivT"(R") Set objPivotCache = Ingenting Range("A3"). Välj Avsluta med slutunder Det färdiga makrot kan sedan köras med en kortkommando andra+F8 eller knappen Makron på fliken utvecklare (Utvecklare – Makron).
Nackdelar med detta tillvägagångssätt:
- Data uppdateras inte eftersom cachen inte har någon anslutning till källtabellerna. Om du ändrar källdata måste du köra makrot igen och bygga sammanfattningen igen.
- När du ändrar antalet ark är det nödvändigt att redigera makrokoden (array Bladnamn).
Men i slutändan får vi ett riktigt fullfjädrat pivotbord, byggt på flera intervall från olika ark:
Voilà!
Teknisk anmärkning: om du får ett felmeddelande som "Provider inte registrerad" när du kör makrot, så har du troligen en 64-bitarsversion av Excel eller så är en ofullständig version av Office installerad (ingen Access). För att åtgärda situationen, byt ut fragmentet i makrokoden:
Provider=Microsoft.Jet.OLEDB.4.0;
till:
Provider=Microsoft.ACE.OLEDB.12.0;
Och ladda ner och installera den kostnadsfria databehandlingsmotorn från Access från Microsofts webbplats – Microsoft Access Database Engine 2010 Redistributable
Metod 3: Konsolidera PivotTable Wizard från gamla versioner av Excel
Denna metod är lite föråldrad, men ändå värd att nämna. Formellt sett fanns det i alla versioner fram till och med 2003 ett alternativ i PivotTable Wizard att "bygga en pivot för flera konsolideringsintervall". Men en rapport som är konstruerad på detta sätt kommer tyvärr bara att vara en ynklig sken av en verklig fullfjädrad sammanfattning och stöder inte många av "chipsen" i konventionella pivottabeller:
I en sådan pivot finns det inga kolumnrubriker i fältlistan, det finns ingen flexibel strukturinställning, uppsättningen av funktioner som används är begränsad och i allmänhet är allt detta inte särskilt likt en pivottabell. Kanske var det därför som, från och med 2007, tog Microsoft bort den här funktionen från standarddialogrutan när man skapade pivottabellsrapporter. Nu är den här funktionen endast tillgänglig via en anpassad knapp Pivottabellguiden(Pivottabellsguiden), som om så önskas kan läggas till i verktygsfältet Snabbåtkomst via Arkiv – Alternativ – Anpassa verktygsfältet Snabbåtkomst – Alla kommandon (Arkiv — Alternativ — Anpassa verktygsfältet Snabbåtkomst — Alla kommandon):
Efter att ha klickat på den tillagda knappen måste du välja lämpligt alternativ i det första steget i guiden:
Och sedan i nästa fönster, välj varje område i tur och ordning och lägg till det i den allmänna listan:
Men återigen, det här är ingen fullfjädrad sammanfattning, så förvänta dig inte för mycket av den. Jag kan endast rekommendera detta alternativ i mycket enkla fall.
- Skapa rapporter med pivottabeller
- Ställ in beräkningar i pivottabeller
- Vad är makron, hur man använder dem, var man kopierar VBA-kod osv.
- Datainsamling från flera ark till ett (PLEX-tillägg)