Twee kolommen in Excel vergelijken (overeenkomsten en verschillen)
Bekijk video – Twee kolommen in Excel vergelijken (overeenkomsten en verschillen)
De vraag die ik vaak krijg is: “Hoe vergelijk ik twee kolommen in Excel?”.
Dit kan op veel verschillende manieren worden gedaan, en de te gebruiken methode hangt af van de gegevensstructuur en wat de gebruiker ervan wil.
U kunt bijvoorbeeld twee kolommen willen vergelijken en alle overeenkomende gegevenspunten (die zich in beide kolommen bevinden) vinden of markeren, of alleen de verschillen (waarbij een gegevenspunt zich in de ene kolom bevindt en niet in de andere), enz.
Omdat ik hier zo vaak vragen over krijg, heb ik besloten deze enorme tutorial te schrijven met de bedoeling de meeste (zo niet alle) mogelijke scenario’s te behandelen.
Als je dit nuttig vindt, geef het dan door aan andere Excel-gebruikers.
Deze tutorial behandelt:
Merk op dat de technieken om kolommen te vergelijken die in deze tutorial worden getoond, niet de enige zijn.
Op basis van uw dataset moet u de methode mogelijk wijzigen of aanpassen. De basisprincipes zouden echter hetzelfde blijven.
Als je denkt dat er iets aan deze tutorial kan worden toegevoegd, laat het me weten in de commentaarsectie
Compare Two Columns For Exact Row Match
Deze is de eenvoudigste vorm van vergelijking. In dit geval moet u rij voor rij vergelijken en bepalen welke rijen dezelfde gegevens hebben en welke niet.
Voorbeeld: Cellen in dezelfde rij vergelijken
Hieronder staat een gegevensverzameling waarbij ik moet controleren of de naam in kolom A dezelfde is in kolom B of niet.
Als er een overeenkomst is, moet het resultaat “TRUE” zijn, en als er geen overeenkomst is, moet het resultaat “FALSE” zijn.
De onderstaande formule zou dit doen:
=A2=B2
Voorbeeld: Cellen in dezelfde rij vergelijken (met IF-formule)
Als u een meer beschrijvend resultaat wilt krijgen, kunt u een eenvoudige IF-formule gebruiken om “Match” te retourneren als de namen hetzelfde zijn en “Mismatch” als de namen verschillend zijn.
=IF(A2=B2,"Match","Mismatch")
Note: Als u de vergelijking hoofdlettergevoelig wilt maken, gebruikt u de volgende IF-formule:
=IF(EXACT(A2,B2),"Match","Mismatch")
Met de bovenstaande formule zouden ‘IBM’ en ‘ibm’ als twee verschillende namen worden beschouwd en zou de bovenstaande formule ‘Mismatch’ opleveren.
Voorbeeld: Rijen met overeenkomende gegevens markeren
Als u de rijen wilt markeren die overeenkomende gegevens hebben (in plaats van het resultaat in een aparte kolom te krijgen), kunt u dat doen door voorwaardelijke opmaak te gebruiken.
Hier volgen de stappen om dit te doen:
- Selecteer de hele dataset.
- Klik op het tabblad ‘Home’.
- Klik in de groep Stijlen op de optie ‘Voorwaardelijke opmaak’.
- Klik in de vervolgkeuzelijst op ‘Nieuwe regel’.
- Klik in het dialoogvenster ‘Nieuwe opmaakregel’ op de optie ‘Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt’.
- In het formuleveld voert u de formule in: =$A1=$B1
- Klik op de knop Opmaak en geef de opmaak op die u op de overeenkomende cellen wilt toepassen.
- Klik op OK.
Dit markeert alle cellen waarin de namen in elke rij hetzelfde zijn.
Twee kolommen vergelijken en overeenkomsten markeren
Als u twee kolommen wilt vergelijken en overeenkomende gegevens wilt markeren, kunt u de functie voor dupliceren gebruiken in de voorwaardelijke opmaak.
Merk op dat dit anders is dan wat we hebben gezien bij het vergelijken van elke rij. In dit geval zullen we niet rij voor rij vergelijken.
Voorbeeld: Vergelijk twee kolommen en markeer overeenkomende gegevens
Vaak krijg je datasets waarin er overeenkomsten zijn, maar deze staan misschien niet in dezelfde rij.
Zoiets als hieronder te zien is:
Merk op dat de lijst in kolom A groter is dan die in B. Ook staan sommige namen in beide lijsten, maar niet in dezelfde rij (zoals IBM, Adobe, Walmart).
Als u alle overeenkomende bedrijfsnamen wilt markeren, kunt u dat doen met behulp van voorwaardelijke opmaak.
Hier volgen de stappen om dit te doen:
- Selecteer de hele gegevensverzameling.
- Klik op het tabblad Home.
- Klik in de groep Stijlen op de optie ‘Voorwaardelijke opmaak’.
- Beweeg de cursor over de optie Celregels markeren.
- Klik op Dupliceren van waarden.
- Zorg ervoor dat in het dialoogvenster Dupliceren van waarden ‘Dupliceren’ is geselecteerd.
- Specificeer de opmaak.
- Klik op OK.
De bovenstaande stappen zouden u het resultaat geven zoals hieronder getoond.
Note: Voorwaardelijke opmaak duplicaat regel is niet hoofdlettergevoelig. Dus ‘appel’ en ‘appel’ worden als hetzelfde beschouwd en zouden worden gemarkeerd als duplicaten.
Voorbeeld: Vergelijk twee kolommen en markeer niet overeenkomende gegevens
In het geval dat u de namen wilt markeren die wel in de ene lijst voorkomen en niet in de andere, kunt u ook hiervoor de voorwaardelijke opmaak gebruiken.
- Selecteer de gehele dataset.
- Klik op het tabblad Home.
- In de groep Stijlen klikt u op de optie ‘Voorwaardelijke opmaak’.
- Beweeg de cursor over de optie Celregels markeren.
- Klik op Waarden dupliceren.
- In het dialoogvenster Waarden dupliceren, zorgt u ervoor dat ‘Uniek’ is geselecteerd.
- Specificeer de opmaak.
- Klik op OK.
Dit geeft u het resultaat zoals hieronder is weergegeven. Alle cellen met een naam die in de andere lijst niet voorkomt, worden gemarkeerd.
Vergelijk twee kolommen en zoek ontbrekende gegevenspunten
Als u wilt weten of een gegevenspunt uit de ene lijst in de andere lijst voorkomt, moet u de opzoekformules gebruiken.
Voorstel dat u een dataset hebt zoals hieronder afgebeeld en u wilt bedrijven identificeren die in kolom A aanwezig zijn, maar niet in kolom B,
Om dit te doen, kan ik de volgende VLOOKUP-formule gebruiken.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Deze formule gebruikt de VLOOKUP-functie om te controleren of een bedrijfsnaam in A al dan niet in kolom B aanwezig is. Als die aanwezig is, wordt die naam uit kolom B teruggegeven, anders wordt een #N/A-fout teruggegeven.
De namen die de #N/A-fout teruggeven, zijn de namen die ontbreken in kolom B.
ISERROR-functie zou TRUE teruggeven als het VLOOKUP-resultaat een fout is en FALSE als het geen fout is.
Als u een lijst wilt krijgen van alle namen waar geen overeenkomst is, kunt u de resultaatkolom filteren om alle cellen met TRUE te krijgen.
U kunt ook de functie MATCH gebruiken om hetzelfde te doen;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Note: Persoonlijk geef ik de voorkeur aan het gebruik van de functie Match (of de combinatie van INDEX/MATCH) in plaats van VLOOKUP. Ik vind het flexibeler en krachtiger. Het verschil tussen Vlookup en Index/Match kunt u hier lezen.
Vergelijk twee kolommen en trek de overeenkomstige gegevens op
Als u twee datasets hebt en u wilt items in de ene lijst met de andere vergelijken en het overeenkomstige gegevenspunt ophalen, moet u de lookup-formules gebruiken.
Voorbeeld: Trek de overeenkomende gegevens (Exact)
In de onderstaande lijst wil ik bijvoorbeeld de marktwaarderingswaarde voor kolom 2 ophalen. Om dit te doen, moet ik die waarde in kolom 1 opzoeken en vervolgens de bijbehorende marktwaarde ophalen.
Hieronder staat de formule waarmee dit kan worden gedaan:
=VLOOKUP(D2,$A:$B,2,0)
of
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Voorbeeld: Trek de overeenkomende gegevens (gedeeltelijk)
In het geval dat u een dataset krijgt waarbij er een klein verschil is in de namen in de twee kolommen, zal het gebruik van de hierboven getoonde lookup-formules niet werken.
Deze lookup-formules hebben een exacte overeenkomst nodig om het juiste resultaat te geven. Er is een benaderende overeenkomst optie in VLOOKUP of MATCH functie, maar dat kan hier niet worden gebruikt.
Voorstel dat je de gegevensverzameling hebt zoals hieronder afgebeeld. Merk op dat er namen zijn die niet volledig zijn in kolom 2 (zoals JPMorgan in plaats van JPMorgan Chase en Exxon in plaats van ExxonMobil).
In zo’n geval kunt u een gedeeltelijke lookup gebruiken door jokertekens te gebruiken.
De volgende formule geeft in dit geval het juiste resultaat:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
of
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
In het bovenstaande voorbeeld is het sterretje (*) een jokerteken dat een willekeurig aantal tekens kan weergeven. Wanneer de opzoekwaarde er aan beide kanten mee wordt geflankeerd, wordt elke waarde in kolom 1 die de opzoekwaarde in kolom 2 bevat, als een overeenkomst beschouwd.
Zo zou *Exxon* een overeenkomst zijn voor ExxonMobil (aangezien * een willekeurig aantal tekens kan vertegenwoordigen).
Je vindt de volgende Excel-tips misschien ook leuk & Tutorials:
- Hoe vergelijk je twee Excel-sheets (voor verschillen)
- Hoe markeer je lege cellen in Excel.
- Highlight ELVERY Other ROW in Excel.
- Excel Advanced Filter: Een complete gids met voorbeelden.
- Rijen markeren op basis van een celwaarde in Excel.