✅ Kombinera data från flera arbetsböcker i Excel (med hjälp av Power Query)

Innehållsförteckning

Varje arbetsbok har data i en Excel -tabell med samma struktur
Varje arbetsbok har data med samma kalkylbladnamn
Varje arbetsbok har data med olika tabellnamn eller bladnamn

Power Query kan vara till stor hjälp när du vill kombinera flera arbetsböcker till en enda arbetsbok.

Anta till exempel att du har försäljningsdata för olika regioner (öst, väst, norr och söder). Du kan kombinera denna data från olika arbetsböcker till ett enda kalkylblad med hjälp av Power Query.

Om du har dessa arbetsböcker på olika platser/mappar är det en bra idé att flytta alla dessa till en enda mapp (eller skapa en kopia och lägga den arbetsbokskopian i samma mapp).

Så till att börja med har jag fyra arbetsböcker i en mapp (som visas nedan).

I den här självstudien täcker jag tre scenarier där du kan kombinera data från olika arbetsböcker med Power Query:

Varje arbetsbok har data i en Excel -tabell och alla tabellnamn är desamma.
Varje arbetsbok har data med samma kalkylbladnamn. Detta kan vara fallet när det finns ett blad som heter "sammanfattning" eller "data" i alla arbetsböcker och du vill kombinera alla dessa.
Varje arbetsbok har många blad och tabeller, och du vill kombinera specifika tabeller/blad. Denna metod kan också vara till hjälp när du vill kombinera bord/ark som inte har ett konsekvent namn.

Låt oss se hur vi kombinerar data från dessa arbetsböcker i varje fall.

Varje arbetsbok har data i en Excel -tabell med samma struktur

Nedanstående teknik skulle fungera när dina Excel -tabeller har strukturerats på samma sätt (samma kolumnnamn).

Antalet rader i varje tabell kan variera.

Oroa dig inte om några av Excel -tabellerna har ytterligare kolumner. Du kan välja en av tabellerna som mall (eller som "nyckeln" som Power Query kallar det), och Power Query skulle använda den för att kombinera alla andra Excel -tabeller med den.

Om det finns ytterligare kolumner i andra tabeller ignoreras de och endast de som anges i mallen/nyckeln kombineras. Till exempel om den mall/nyckeltabell som du väljer har fem kolumner och en av tabellerna i någon annan arbetsbok har ytterligare två kolumner ignoreras dessa ytterligare kolumner.

Nu har jag fyra arbetsböcker i en mapp som jag vill kombinera.

Nedan är en ögonblicksbild av tabellen jag har i en av arbetsböckerna.

Här är stegen för att kombinera data från dessa arbetsböcker till en enda arbetsbok (som en enda tabell).

Gå till fliken Data.
Klicka på rullgardinsmenyn Ny fråga i gruppen Get & Transform.
Håll muspekaren på "Från fil" och klicka på "Från mapp".
I dialogrutan Mapp anger du sökvägen till mappen som innehåller filerna, eller klicka på Bläddra och leta upp mappen.
Klicka på OK.
Klicka på kombinera -knappen i dialogrutan som öppnas.
Klicka på "Kombinera och ladda".
Markera tabellen i den vänstra rutan i dialogrutan "Kombinera filer" som öppnas. Observera att Power Query visar tabellen från den första filen. Denna fil fungerar som mall (eller nyckel) för att kombinera andra filer. Power Query skulle nu leta efter "Tabell 1" i andra arbetsböcker och kombinera den med den här.
Klicka på OK.

Detta laddar det slutliga resultatet (kombinerade data) i ditt aktiva kalkylblad.

Observera att tillsammans med data lägger Power Query automatiskt till arbetsbokens namn som den första kolumnen i den kombinerade data. Detta hjälper till att hålla reda på vilken data som kom från vilken arbetsbok.

Om du först vill redigera data innan du läser in dem i Excel väljer du "Kombinera och redigera" i steg 6. Detta öppnar det slutliga resultatet i Power Query -redigeraren där du kan redigera data.

Några saker att veta:

Om du väljer en Excel -tabell som mall (i steg 7) använder Power Query kolumnnamnen i denna tabell för att kombinera data från andra tabeller. Om andra tabeller har ytterligare kolumner ignoreras dessa. Om de andra tabellerna inte har en kolumn, som finns i din malltabell, skulle Power Query bara sätta "null" för den.
Kolumnerna behöver inte vara i samma ordning som Power Query använder kolumnrubriker för att mappa kolumner.
Eftersom du har valt Table1 som nyckel, söker Power Query efter Table1 i alla arbetsböcker och kombinerar alla dessa. Om den inte hittar en Excel -tabell med samma namn (tabell1 i det här exemplet) ger Power Query dig ett fel.

Lägga till nya filer i mappen

Låt oss nu ta en minut och förstå vad vi gjorde med ovanstående steg (vilket bara tog oss några sekunder).

Vi kombinerade data från fyra olika arbetsböcker i en enda tabell på några sekunder utan att ens öppna någon av arbetsböckerna.

Men det är inte allt.

Den verkliga POWER of Power Query är att nu när du lägger till fler filer i mappen behöver du inte upprepa något av dessa steg.

Allt du behöver göra för att flytta den nya arbetsboken i mappen, uppdatera frågan, och den kombinerar automatiskt data från alla arbetsböcker i den mappen.

Till exempel i exemplet ovan, om jag lägger till en ny arbetsbok - 'Mid-West.xlsx' till mappen och uppdatera frågan, det ger mig omedelbart den nya kombinerade datauppsättningen.

Så här uppdaterar du en fråga:

Högerklicka på Excel-tabellen som du laddade i kalkylbladet och klicka på Uppdatera.
Högerklicka på frågan i rutan "Workbook Query" och klicka på Uppdatera
Gå till fliken Data och klicka på Uppdatera.

Varje arbetsbok har data med samma kalkylbladnamn

Om du inte har data i en Excel -tabell, men alla bladnamnen (från vilka du vill kombinera data) är desamma, kan du använda metoden som visas i det här avsnittet.

Det finns några saker du måste vara försiktig med när det bara är tabelldata och inte en Excel -tabell.

Arbetsbladets namn ska vara desamma. Detta hjälper Power Query att gå igenom dina arbetsböcker och kombinera data från kalkylblad som har samma namn i varje arbetsbok.
Power Query är skiftlägeskänsligt. Det betyder att ett kalkylblad med namnet "data" och "data" anses vara olika. På samma sätt anses en kolumn med rubriken "Store" och en med "store" vara annorlunda.
Även om det är viktigt att ha samma kolumnrubriker, är det inte viktigt att ha samma ordning. Om kolumn 2 i "East.xlsx" är kolumn 4 i "West.xlsx" matchar Power Query den korrekt genom att mappa rubrikerna.

Låt oss nu se hur vi snabbt kan kombinera data från olika arbetsböcker där kalkylbladets namn är detsamma.

I det här exemplet har jag en mapp med fyra filer.

I varje arbetsbok har jag ett kalkylblad med namnet 'Data' som innehåller data i följande format (notera att detta inte är en Excel -tabell).

Här är stegen för att kombinera data från flera arbetsböcker till ett enda kalkylblad:

Gå till fliken Data.
Klicka på rullgardinsmenyn Ny fråga i gruppen Hämta och omvandla.
Håll muspekaren på "Från fil" och klicka på "Från mapp".
I dialogrutan Mapp anger du sökvägen till mappen som innehåller filerna, eller klicka på Bläddra och leta upp mappen.
Klicka på OK.
Klicka på kombinera -knappen i dialogrutan som öppnas.
Klicka på "Kombinera och ladda".
I dialogrutan "Kombinera filer" som öppnas väljer du "Data" i den vänstra rutan. Observera att Power Query visar kalkylbladets namn från den första filen. Denna fil fungerar som nyckeln/mallen för att kombinera andra filer. Power Query går igenom varje arbetsbok, hittar arket "Data" och kombinerar alla dessa.
Klicka på OK. Nu går Power Query igenom varje arbetsbok, letar efter kalkylbladet med namnet "Data" i den och kombinerar sedan alla dessa datamängder.

Detta laddar det slutliga resultatet (kombinerade data) i ditt aktiva kalkylblad.

Varje arbetsbok har data med olika tabellnamn eller bladnamn

Ibland kanske du inte får strukturerade och konsekventa data (t.ex. tabeller med samma namn eller kalkylblad med samma namn).

Anta till exempel att du får data från någon som skapade dessa datamängder men kallade kalkylblad som East Data, West Data, North Data och South Data.

Eller personen kan ha skapat Excel -tabeller, men med olika namn.

I sådana fall kan du fortfarande använda Power Query, men du måste göra det med ett par ytterligare steg.

Gå till fliken Data.
Klicka på rullgardinsmenyn Ny fråga i gruppen Get & Transform.
Håll muspekaren på "Från fil" och klicka på "Från mapp".
I dialogrutan Mapp anger du sökvägen till mappen som innehåller filerna, eller klicka på Bläddra och leta upp mappen.
Klicka på OK.
Klicka på knappen Redigera i dialogrutan som öppnas. Detta öppnar Power Query -redigeraren där du kommer att se detaljerna för alla filer i mappen.
Håll ned Ctrl-tangenten och välj "Innehåll" och "Namn" -kolumnerna, högerklicka och välj "Ta bort andra kolumner". Detta tar bort alla andra kolumner utom de valda kolumnerna.
Klicka på "Lägg till kolumn" i menyfliksområdet Frågeditor och klicka sedan på "Anpassad kolumn".
I dialogrutan Lägg till anpassad kolumn, namnge den nya kolumnen som "Dataimport" och använd följande formel = Excel.Workbook ([CONTENT]). Observera att denna formel är skiftlägeskänslig och du måste ange den exakt så som jag har visat här.
Nu kommer du att se en ny kolumn som har tabellen skriven i den. Låt mig nu förklara vad som hände här. Du angav Power Query namnen på arbetsböckerna, och Power Query har hämtat objekten som kalkylblad, tabeller och namngivna intervall från varje arbetsbok (som finns i tabellcellen från och med nu). Du kan klicka på det vita utrymmet bredvid textbordet och du skulle se informationen längst ner. I det här fallet, eftersom vi bara har en tabell och ett kalkylblad i varje arbetsbok, kan du bara se två rader.
Klicka på dubbelpilen högst upp i kolumnen "Dataimport".
Avmarkera "Använd originalkolumn som prefix" i kolumndatarutan som öppnas och klicka sedan på OK.
Nu kommer du att se en utökad tabell där du ser en rad för varje objekt i tabellen. I det här fallet listas arkobjektet och tabellobjektet för varje arbetsbok separat.
I kolumnen Kind, filtrera listan för att endast visa tabellen.
Håll ned kontrollknappen och välj kolumnen Namn och data. Högerklicka nu och ta bort alla andra kolumner.
I kolumnen Data klickar du på dubbelpilen längst upp till höger i datahuvudet.
Klicka på OK i rutan för kolumndata som öppnas. Detta kommer att kombinera data i alla tabeller och visas i Power Query.
Nu kan du göra vilken omvandling du vill och sedan gå till fliken Hem och klicka på Stäng och ladda.

Låt mig nu försöka förklara snabbt vad vi gjorde här. Eftersom det inte fanns någon konsistens i bladnamnen eller tabellnamnen använde vi = Excel.Workbook -formeln för att hämta alla objekt i arbetsböckerna i Power Query. Dessa objekt kan innehålla ark, tabeller och namngivna intervall. När vi hade alla objekt från alla filer, filtrerade vi dessa för att endast överväga Excel -tabeller. Sedan utökade vi data i tabellerna och kombinerade alla dessa.

I det här exemplet filtrerade vi data för att endast använda Excel -tabeller (i steg 13). Om du vill kombinera ark och inte tabeller kan du filtrera ark.

Obs - den här tekniken ger dig den kombinerade informationen även om det inte finns någon matchning i kolumnnamn. Om du till exempel i East.xlsx har en kolumn som har stavats fel kommer du att få fem kolumner. Power Query fyller data i kolumner om den hittar den, och om den inte kan hitta en kolumn rapporterar den värdet som "null".

På samma sätt, om du har några ytterligare kolumner i något av tabellens kalkylblad, kommer dessa att inkluderas i det slutliga resultatet.

Nu om du får fler arbetsböcker som du behöver kombinera data från, kopiera och klistra in den i mappen och uppdatera Power Query