David uit Florida stelt de vraag van vandaag:
Ik heb twee werkboeken. Beide hebben dezelfde gegevens in kolom A, maar de overige kolommen zijn verschillend. Hoe kan ik die twee werkmappen samenvoegen?
Ik vroeg David of het mogelijk is dat het ene werkboek meer records heeft dan het andere. En het antwoord is ja. Ik vroeg David of het sleutelveld maar één keer in elk bestand voorkomt. Het antwoord is ook ja. Vandaag ga ik dit oplossen met Power Query. De Power Query-tools zijn te vinden in Windows-versies van Excel 2016+ in de sectie Ophalen en transformeren van het tabblad Gegevens. Als u Windows-versies van Excel 2010 of Excel 2013 heeft, kunt u de Power Query-invoegtoepassing voor die versies downloaden.
Hier is Davids werkboek 1. Het bevat Product en vervolgens drie kolommen met gegevens.
Hier is Davids werkboek 2. Het bevat productcode en vervolgens andere kolommen. In dit voorbeeld zijn er extra producten in werkmap2, maar de oplossingen werken als een van beide werkmappen extra kolommen heeft.
Hier zijn de stappen:
-
Selecteer gegevens, gegevens ophalen, uit bestand, uit werkmap:
Laad gegevens uit een bestand - Blader naar de eerste werkmap en klik op OK
- Kies in het dialoogvenster Navigator het werkblad aan de linkerkant. (Zelfs als er maar één werkblad is, moet u dit selecteren.) U ziet de gegevens aan de rechterkant.
- Open in het Navigator-dialoogvenster de vervolgkeuzelijst Laden en kies Laden naar …
- Kies Alleen een verbinding maken en druk op OK.
-
Herhaal stap 1-5 voor de tweede werkmap.
Maak een verbinding met de werkmap Als je beide werkmappen hebt gedaan, zou je twee verbindingen moeten zien in het paneel Query's en verbindingen aan de rechterkant van je Excel-scherm.
Verbindingen met beide werkmappen Ga verder met de stappen om de werkmappen samen te voegen:
-
Gegevens, gegevens ophalen, zoekopdrachten combineren, samenvoegen.
Voeg twee zoekopdrachten met verschillende kolommen samen - Kies de eerste query in de vervolgkeuzelijst bovenaan in het dialoogvenster Samenvoegen.
- Kies de tweede query uit de tweede vervolgkeuzelijst in het dialoogvenster Samenvoegen.
- Klik op het kopje Product in het bovenste voorbeeld (dit is het sleutelveld. Merk op dat u twee of meer sleutelvelden meerdere keren kunt selecteren door Ctrl + klikken)
- Klik op het kopje Productcode in het tweede voorbeeld.
-
Open het Join Type en kies Full Outer (alle rijen van beide)
Stappen 8 - 12 hier geïllustreerd -
Klik OK. Het gegevensvoorbeeld toont de extra rijen niet en toont alleen "Tabel" herhaaldelijk in de laatste kolom.
Dit ziet er niet veelbelovend uit - Merk op dat er een pictogram "Uitvouwen" in de kop voor DavidTwo staat. Klik op dat icoon.
-
Optioneel, maar ik verwijder de selectie van "Gebruik originele kolomnaam als voorvoegsel" altijd. Klik OK.
Vouw de velden uit werkmap 2 uit De resultaten worden in deze preview getoond:
Alle records uit beide werkmappen - Gebruik Home, Close & Load in Power Query.
Hier is de mooie functie: als de onderliggende gegevens in een van beide werkmappen veranderen, kunt u op het pictogram Vernieuwen klikken om nieuwe gegevens in de resultatenwerkmap op te halen.
Opmerking
Het pictogram voor Vernieuwen is meestal verborgen. Sleep de linkerrand van het paneel Query's en verbindingen naar links om het pictogram weer te geven.
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2216: combineer twee werkmappen op basis van een gemeenschappelijke kolom.
Hé, welkom terug bij netcast, ik ben Bill Jelen. De vraag van vandaag van David, die op mijn seminar was in Melbourne, Florida, voor het Space Coast Chapter van het IIA.
David heeft twee verschillende werkmappen waarin kolom A beide gemeenschappelijk is. Dus hier is werkboek 1, hier is werkboek 2 - beide hebben productcode. Deze heeft items die de eerste niet heeft, of vice versa, en David wil alle kolommen combineren. We hebben hier dus drie kolommen en hier vier kolommen. Ik heb deze allebei in dezelfde werkmap gezet, voor het geval je de werkmap downloadt om mee te werken. Neem elk van deze, verplaats het naar zijn eigen werkmap en sla het op.
Oké, om deze bestanden te combineren, gaan we Power Query gebruiken. Power Query is ingebouwd in Excel 2016. Als u de Windows-versie 10 of 13 gebruikt, kunt u naar Microsoft gaan en Power Query downloaden. U kunt beginnen met een nieuwe lege werkmap met een leeg werkblad. Je gaat dit bestand opslaan - Opslaan als, je weet wel, misschien Workbook, om de resultaten van gecombineerde bestanden .xlsx te tonen. Oké? En wat we gaan doen is, we gaan twee vragen stellen. We gaan naar Gegevens, Gegevens ophalen, Uit bestand, Uit werkmap en dan kiezen we het eerste bestand. Selecteer in een voorbeeld het blad met uw gegevens en we hoeven niets aan deze gegevens te doen. Open dus gewoon het laadvak en kies Laden naar, alleen verbinding maken, klik op OK. Perfect. Nu gaan we dat herhalen voor het tweede item: gegevens, uit bestand,Kies in een werkmap DavidTwo, kies de bladnaam en open vervolgens de belasting, Laden naar, alleen een verbinding maken. Je ziet hier in dit paneel, we hebben beide aansluitingen aanwezig. Oké.
Nu het eigenlijke werk - Gegevens, Gegevens ophalen, Query's combineren, Samenvoegen en vervolgens in het dialoogvenster Samenvoegen DavidOne, DavidTwo kiezen, en deze volgende stap is volledig niet intuïtief. Je moet dit doen. Kies de gemeenschappelijke kolom of kolommen, dus Product en Product. Oké. En wees dan hier heel voorzichtig met het join-type. Ik wil alle rijen van beide omdat er een een extra rij kan hebben en dat moet ik zien, en dan klikken we op OK. Oké. En hier is het eerste resultaat. Het ziet er niet naar uit dat het werkte; het ziet er niet naar uit dat het de extra items heeft toegevoegd die in bestand 2 stonden. En we hebben deze kolom 5 - het is nu nul. Ik ga met de rechtermuisknop op kolom 5 klikken en zeg: Verwijder die kolom. Dus open dit uitvouwpictogram en schakel dit selectievakje uit voor Gebruik originele kolomnaam als voorvoegsel en BAM! het werkt. Dus de extra items die in bestand 2 stonden, die niet in bestand 1,verschijnen.
Oké. In het bestand van vandaag lijkt het erop dat deze productcodekolom beter is dan deze productkolom, omdat deze extra rijen heeft. Maar er kan in de toekomst een dag komen waarop Werkboek 1 dingen heeft die Werkboek 2 niet heeft. Dus ik laat ze daar allebei achter, en ik ga geen null-waarden verwijderen, omdat, hoewel deze rij onderaan helemaal nul lijkt te zijn, er in de toekomst een situatie kan zijn waarin we hebben hier een paar nullen omdat er iets ontbreekt. Oké? Dus, eindelijk, Close & Load, en we hebben onze zestien rijen.
Laten we nu, in de toekomst, zeggen dat er iets verandert. Oké, dus we gaan terug naar een van die twee bestanden en ik verander de klasse voor Apple in 99, en laten we zelfs iets nieuws invoegen en deze werkmap opslaan. Oké. En dan, als we willen dat ons samenvoegbestand wordt bijgewerkt, kom dan hier - nu, pas op, als je dit de eerste keer doet, kun je het pictogram Vernieuwen niet zien - je moet deze balk pakken en over slepen . En we zullen Refresh doen, en 17 rijen geladen, de watermeloen verschijnt, de Apple verandert in 99 - het is iets prachtigs. Nou, wil je meer weten over Power Query? Koop dit boek van Ken Puls en Miguel Escobar, M is voor (DATA) AAP. Ik zal je op de hoogte brengen.
Afronding vandaag: David uit Florida heeft twee werkboeken die hij wil combineren; ze hebben allebei dezelfde velden in kolom A, maar de andere kolommen zijn allemaal verschillend; het ene werkboek kan extra items bevatten die niet in het andere staan en David wil die; er zijn geen duplicaten in beide bestanden; we gaan Power Query gebruiken om dit op te lossen, dus begin in een nieuwe lege werkmap op een leeg werkblad; je gaat drie vragen doen, de eerste: gegevens, uit bestand, werkmap en dan laden naar alleen aangemaakte verbinding; hetzelfde voor de tweede werkmap, en dan Data, Get Data, Merge, selecteer de twee verbindingen, selecteer de kolom die in beide gebruikelijk is - in mijn geval Product - en dan van het Join Type, wil je volledig deelnemen allemaal uit bestand 1, allemaal uit bestand 2. En dan is het mooie als de onderliggende gegevens veranderen,u kunt de zoekopdracht gewoon vernieuwen.
Bezoek de URL in de YouTube-beschrijving om de werkmap van de video van vandaag te downloaden.
Nou, hey, ik wil net als David dat hij op mijn seminar is gekomen, ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: combineer-gebaseerd-op-gemeenschappelijke-kolom.xlsx
Power Query is een geweldige tool in Excel.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
'Druk altijd op F4 als u bereik of matrix in een functie leest'
Tanja Kuhn
