Kombinera baserat på Common Column - Excel Tips

David från Florida ställer dagens fråga:

Jag har två arbetsböcker. Båda har samma data i kolumn A, men de återstående kolumnerna är olika. Hur kan jag slå samman dessa två arbetsböcker?

Jag frågade David om det är möjligt att en arbetsbok har fler poster än den andra. Och svaret är ja. Jag frågade David om nyckelfältet bara visas en gång i varje fil. Svaret är också ja. Idag ska jag lösa detta med Power Query. Power Query-verktygen finns i Windows-versioner av Excel 2016+ i avsnittet Get & Transform på fliken Data. Om du har Windows-versioner av Excel 2010 eller Excel 2013 kan du ladda ner Power Query-tillägget för dessa versioner.

Här är Davids arbetsbok 1. Den har produkt och sedan tre kolumner med data.

Den första arbetsboken

Här är Davids arbetsbok 2. Den har produktkod och sedan andra kolumner. I det här exemplet finns det extra produkter i arbetsbok2, men lösningarna fungerar om någon arbetsbok har extra kolumner.

Den andra arbetsboken

Här är stegen:

Välj data, hämta data, från fil, från arbetsbok:

Ladda data från en fil
Bläddra till den första arbetsboken och klicka på OK
I dialogrutan Navigator väljer du kalkylbladet till vänster. (Även om det bara finns ett kalkylblad måste du välja det.) Du ser informationen till höger.
I dialogrutan Navigator öppnar du rullgardinsmenyn Ladda och väljer Ladda till …
Välj Endast skapa en anslutning och tryck på OK.
Upprepa steg 1-5 för den andra arbetsboken.

Skapa en anslutning till arbetsboken
Om du har gjort båda arbetsböckerna ska du se två anslutningar på panelen Frågor och anslutningar till höger om din Excel-skärm.

Anslutningar till båda arbetsböckerna
Fortsätt med stegen för att slå samman arbetsböckerna:
Data, få data, kombinera frågor, slå samman.

Slå samman två frågor med olika kolumner
Välj den första frågan från den övre rullgardinsmenyn i dialogrutan Sammanfoga.
Från den andra rullgardinsmenyn i dialogrutan Sammanfoga väljer du den andra frågan.
Klicka på produktrubriken i den översta förhandsgranskningen (detta är nyckelfältet. Observera att du kan markera två eller flera nyckelfält genom att Ctrl + klicka)
Klicka på rubriken Produktkod i den andra förhandsgranskningen.
Öppna anslutningstypen och välj Hel yttre (Alla rader från båda)

Steg 8 - 12 illustreras här
Klicka på OK. Förhandsgranskningen av data visar inte de extra raderna och visar bara "Tabell" upprepade gånger i den sista kolumnen.

Detta ser inte lovande ut
Observera att det finns en "Expand" -ikon i rubriken för DavidTwo. Klicka på den ikonen.
Valfritt, men jag avmarkerar alltid "Använd originalkolumnamn som prefix". Klicka på OK.

Expandera fälten från arbetsbok 2
Resultaten visas i denna förhandsvisning:

Alla poster från arbetsboken
I Power Query använder du Home, Close & Load.

Här är den vackra funktionen: om underliggande data i endera arbetsboken ändras kan du klicka på ikonen Uppdatera för att dra in nya data till resultatarbetsboken.

Gör om steg 1-16 genom att klicka på den här uppdateringsikonen.

Notera

Ikonen för Uppdatera är vanligtvis dold. Dra den vänstra kanten av fönstret Frågor och anslutningar till vänster för att visa ikonen.

Kolla på video

Videoutskrift

Lär dig Excel från Podcast, avsnitt 2216: Kombinera två arbetsböcker baserat på en gemensam kolumn.

Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga är från David, som var i mitt seminarium i Melbourne, Florida, för rymdkustkapitlet i IIA.

David har två olika arbetsböcker där kolumn A är gemensam för dem båda. Så här är arbetsbok 1, här är arbetsbok 2 - båda har produktkod. Den här har objekt som den första inte har, eller tvärtom, och David vill kombinera alla kolumner. Så vi har tre kolumner här och fyra kolumner här. Jag lägger båda dessa i samma arbetsbok, om du laddar ner arbetsboken för att arbeta med. Ta var och en av dessa, flytta den till sin egen arbetsbok och spara den.

Okej, för att kombinera dessa filer ska vi använda Power Query. Power Query är inbyggd i Excel 2016. Om du är i Windows-versionen av 10 eller 13 kan du gå till Microsoft och ladda ner Power Query. Du kan börja från en ny tom arbetsbok med ett tomt kalkylblad. Du kommer att spara den här filen - Spara som, du vet, kanske arbetsbok, för att visa resultaten av kombinerade filer .xlsx. OK? Och vad vi ska göra är att vi kommer att göra två frågor. Vi ska gå till Data, Get Data, From File, From Workbook, och sedan väljer vi den första filen. I en förhandsgranskning, välj arket som innehåller dina data, och vi behöver inte göra något åt dessa data. Så öppna bara lastrutan och välj Load To, Only Create Connection, klicka på OK. Perfekt. Nu ska vi upprepa det för det andra objektet - Data, från fil,Välj DavidTwo i en arbetsbok, välj arknamnet och öppna sedan lasten, Ladda till, skapa bara en anslutning. Du kommer att se här i den här panelen, vi har båda anslutningarna närvarande. OK.

Nu är det faktiska arbetet - Data, Hämta data, kombinera frågor, slå samman och välj sedan DavidOne, DavidTwo i dialogrutan Sammanfoga, och nästa steg är helt ointuitivt. Du måste göra det här. Välj kolumn eller kolumner gemensamt - så Produkt och Produkt. OK. Och var försiktig här med anslutningstypen. Jag vill ha alla rader från båda eftersom en kan ha en extra rad och jag måste se det och sedan klickar vi på OK. OK. Och här är det ursprungliga resultatet. Det ser inte ut som det fungerade; det ser inte ut som det har lagt till de extra objekten som fanns i fil 2. Och vi har den här kolumnen 5 - den är noll nu. Jag ska högerklicka på kolumn 5 och säga, Ta bort den kolumnen. Så öppna den här utökningsikonen och avmarkera den här rutan för Använd originalkolumnamn som prefix och BAM! det fungerar. Så de extra objekt som fanns i fil 2, som inte finns i fil 1,visas.

OK. Nu i dagens fil ser det ut som att denna produktkodkolumn är bättre än den här produktkolumnen, eftersom den har extra rader. Men det kan finnas en dag i framtiden där Workbook 1 har saker som Workbook 2 inte har. Så jag kommer att lämna dem båda där, och jag kommer inte att bli av med några nollor för, som om, även om den här raden längst ner verkar vara helt noll, kan det i framtiden finnas en situation där vi har några nollor här eftersom något saknas. OK? Så slutligen, stäng & ladda, så har vi våra sexton rader.

Nu, i framtiden, låt oss säga att något förändras. Okej, så vi går tillbaka till en av de två filerna så ändrar jag klassen för Apple till 99, och låt oss till och med infoga något nytt och spara den här arbetsboken. OK. Och om vi vill att vår sammanslagningsfil ska uppdateras, kom hit - nu, se upp, när du gör det första gången kan du inte se ikonen Uppdatera - du måste ta tag i det här fältet och dra det över . Och vi kommer att göra Uppdatera, och 17 rader laddade, vattenmelonen dyker upp, Apple ändras till 99 - det är en vacker sak. Nu, hej, vill du lära dig mer om Power Query? Köp den här boken av Ken Puls och Miguel Escobar, M är för (DATA) MONKEY. Jag får dig snabbare.

Avslutning idag: David från Florida har två arbetsböcker som han vill kombinera; de har båda samma fält i kolumn A, men de andra kolumnerna är alla olika; en arbetsbok kan ha extra föremål som inte finns i den andra och David vill ha dem; det finns inga dubbletter i någon av filerna; vi ska använda kraftfråga för att lösa detta, så börja i en ny tom arbetsbok på ett tomt kalkylblad; du kommer att göra tre frågor, först en - Data, Från fil, arbetsbok och sedan Ladda till endast skapad anslutning; samma sak för den andra arbetsboken och sedan Data, Skaffa data, Slå ihop, välj de två anslutningarna, välj den kolumn som är vanlig i båda - i mitt fall Produkt - och sedan från anslutningstypen vill du fullständig gå med allt från fil 1, allt från fil 2. Och då är det vackra om den underliggande informationen ändras,du kan bara uppdatera frågan.

För att ladda ner arbetsboken från dagens video, besök webbadressen i YouTube-beskrivningen.

Tja, hej, jag vill ha som David för att ha dykt upp på mitt seminarium, jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.

Ladda ner Excel-fil

För att ladda ner excel-filen: combine-based-on-common-column.xlsx

Power Query är ett fantastiskt verktyg i Excel.

Excel-tanke på dagen

Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på: