✅ Kombinera data från flera kalkylblad till ett enda kalkylblad i Excel

Innehållsförteckning

Kombinera data från flera arbetsblad med hjälp av Power Query

Jag fick nyligen en fråga från en läsare om att kombinera flera kalkylblad i samma arbetsbok till ett enda kalkylblad.

Jag bad honom att använda Power Query för att kombinera olika ark, men då insåg jag att det kan vara svårt för någon ny i Power Query.

Så jag bestämde mig för att skriva den här självstudien och visa de exakta stegen för att kombinera flera ark till en enda tabell med Power Query.

Nedan en video där jag visar hur jag kombinerar data från flera blad/tabeller med Power Query:

Nedan finns skriftliga instruktioner om hur du kombinerar flera ark (om du föredrar skriftlig text framför video).

Obs! Power Query kan användas som ett tillägg i Excel 2010 och 2013 och är en inbyggd funktion från Excel 2016 och framåt. Baserat på din version kan vissa bilder se annorlunda ut (bildfångster som används i den här självstudien är från Excel 2016).

Kombinera data från flera arbetsblad med hjälp av Power Query

När du kombinerar data från olika blad med Power Query krävs det att ha data i en Excel -tabell (eller åtminstone i namngivna intervall). Om data inte finns i en Excel -tabell fungerar metoden som visas här inte.

Anta att du har fyra olika ark - öst, väst, norr och söder.

Var och en av dessa kalkylblad har data i en Excel -tabell, och tabellens struktur är konsekvent (dvs rubrikerna är desamma).

Klicka här för att ladda ner data och följa med.

Denna typ av data är extremt lätt att kombinera med Power Query (som fungerar riktigt bra med data i Excel -tabellen).

För att denna teknik ska fungera bäst är det bättre att ha namn för dina Excel -tabeller (arbeta utan det också, men det är lättare att använda när tabellerna heter).

Jag har gett tabellerna följande namn: East_Data, West_Data, North_Data och South_Data.

Här är stegen för att kombinera flera kalkylblad med Excel -tabeller med Power Query:

Gå till fliken Data.
Klicka på alternativet "Hämta data" i gruppen Hämta och omvandla data.
Gå till alternativet "Från andra källor".
Klicka på alternativet "Tom fråga". Detta öppnar Power Query -redigeraren.
I frågeredigeraren skriver du följande formel i formelfältet: = Excel.CurrentWorkbook(). Observera att Power Query -formlerna är skiftlägeskänsliga, så du måste använda den exakta formeln som nämnts (annars får du ett fel).
Tryck på Enter -tangenten. Detta visar dig alla tabellnamnen i hela arbetsboken (det visar dig också de namngivna områdena och/eller anslutningarna om det finns i arbetsboken).
[Valfritt steg] I det här exemplet vill jag kombinera alla tabeller. Om du bara vill kombinera specifika Excel-tabeller kan du klicka på rullgardinsmenyn i namnhuvudet och välja de du vill kombinera. På samma sätt, om du har namngivna intervall eller anslutningar, och du bara vill kombinera tabeller, kan du också ta bort dessa namngivna intervall.
Klicka på dubbelpilen i innehållsrubriken.
Välj de kolumner som du vill kombinera. Om du vill kombinera alla kolumner, se till att (Markera alla kolumner) är markerat.
Avmarkera alternativet "Använd originalkolumnnamn som prefix".
Klicka på OK.

Stegen ovan skulle kombinera data från alla kalkylblad till en enda tabell.

Om du tittar noga hittar du den sista kolumnen (längst till höger) med namnet på Excel -tabellerna (East_Data, West_Data, North_Data och South_Data). Detta är en identifierare som berättar vilken post som kom från vilken Excel -tabell. Detta är också anledningen till att jag sa att det är bättre att ha beskrivande namn för Excel -tabellerna.

Här är några ändringar du kan göra på den kombinerade data i Power Query själv:

Dra och placera kolumnen Namn till början.
Ta bort "_Data" från namnkolumnen (så du har kvar öst, väst, norr och söder i namnkolumnen). För att göra detta, högerklicka på namnrubriken och klicka på Ersätt värden. Ersätt _Data med ett tomt i dialogrutan Ersätt värden.
Ändra datakolumnen för att endast visa datum (och inte tiden). För att göra detta, klicka på kolumnrubriken Datum, gå till fliken "Transformera" och ändra datatypen till Datum.
Byt namn på frågan till ConsolidatedData.

Nu när du har den kombinerade data från alla kalkylblad i Power Query kan du ladda den i Excel - som en ny tabell i ett nytt kalkylblad.

Att göra detta. följ stegen nedan:

Klicka på fliken "Arkiv".
Klicka på Stäng och ladda till.
I dialogrutan Importera data väljer du Tabell och Nya kalkylblad.
Klicka på Ok.

Stegen ovan kombinerar data från alla kalkylblad och ger dig den kombinerade informationen i ett nytt kalkylblad.

Ett problem du måste lösa när du använder den här metoden

Om du har använt ovanstående metod för att kombinera alla tabellerna i arbetsboken kommer du sannolikt att få ett problem.

Se antalet rader med de kombinerade data - 1304 (vilket är rätt).

Om jag nu uppdaterar frågan ändras antalet rader till 2607. Uppdatera igen så ändras det till 3910.

Här är problemet.

Varje gång du uppdaterar frågan läggs alla poster i originaldata till de kombinerade data.

Obs! Du kommer bara att möta detta problem om du har använt Power Query för att kombinera ALLA EXCEL -TABELLER i arbetsboken. Om du valt specifika tabeller som ska kombineras kommer du inte att möta detta problem.

Låt oss förstå orsaken till detta problem och hur vi åtgärdar detta.

När du uppdaterar en fråga går den tillbaka och följer alla steg vi tog för att kombinera data.

I steget där vi använde formeln = Excel.CurrentWorkbook (), det gav oss en lista över alla tabeller. Detta fungerade bra första gången eftersom det bara fanns fyra bord.

Men när du uppdaterar finns det fem tabeller i arbetsboken - inklusive den nya tabellen som Power Query infogade där vi har den kombinerade data.

Så varje gång du uppdaterar frågan, bortsett från de fyra Excel -tabeller som vi vill kombinera, lägger den också till den befintliga frågetabellen till den resulterande data.

Detta kallas rekursion.

Så här löser du problemet.

När du har infogat = Excel.CurrentWorkbook () i formelsfältet i Power Query och tryckt på enter får du en lista med Excel -tabeller. För att du bara ska kunna kombinera tabellerna från kalkylbladet måste du på något sätt bara filtrera dessa tabeller som du vill kombinera och ta bort allt annat.

Här är stegen för att se till att du bara har de tabeller som krävs:

Klicka på rullgardinsmenyn och håll muspekaren över textfilter.
Klicka på alternativet Innehåller.
I dialogrutan Filterrader anger du _Data i fältet bredvid alternativet "innehåller".
Klicka på OK.

Du kanske inte ser någon ändring i data, men om du gör detta kommer du att förhindra att den resulterande tabellen läggs till igen när frågan uppdateras.

Observera att vi i ovanstående steg har använt ”_Data”För att filtrera när vi namngav tabeller på det sättet. Men vad händer om dina tabeller inte är namngivna konsekvent. Tänk om alla tabellnamn är slumpmässiga och inte har något gemensamt.

Här är sättet att lösa detta - använd filtret "inte lika" och ange namnet på frågan (vilket skulle vara ConsolidatedData i vårt exempel). Detta säkerställer att allt förblir detsamma och att den resulterande frågetabellen som skapas filtreras bort.

Bortsett från det faktum att Power Query gör hela processen med att kombinera data från olika blad (eller till och med samma blad) ganska enkel, en annan fördel med att använda den är att den gör den dynamisk. Om du lägger till fler poster i någon av tabellerna och uppdaterar Power Query kommer den automatiskt att ge dig den kombinerade informationen.

Viktig anmärkning: I exemplet som används i den här självstudien var rubrikerna desamma. Om rubrikerna är olika kommer Power Query att kombinera och skapa alla kolumner i den nya tabellen. Om uppgifterna är tillgängliga för den kolumnen visas den, annars visas den noll.

Du kanske också gillar följande Power Fråga -självstudier: