Bekijk video - Vergelijk twee kolommen in Excel voor overeenkomsten en verschillen
De enige vraag die ik veel krijg is - 'hoe vergelijk ik twee kolommen in Excel?'.
Dit kan op veel verschillende manieren, en de te gebruiken methode hangt af van de datastructuur en wat de gebruiker ervan wil.
U wilt bijvoorbeeld twee kolommen vergelijken en alle overeenkomende gegevenspunten zoeken of markeren (die zich in beide kolommen bevinden), of alleen de verschillen (waarbij een gegevenspunt in de ene kolom staat en niet in de andere), enz.
Omdat ik hier zo vaak naar wordt gevraagd, heb ik besloten deze enorme tutorial te schrijven met de bedoeling om de meeste (zo niet alle) mogelijke scenario's te behandelen.
Als je dit nuttig vindt, geef het dan door aan andere Excel-gebruikers.
Merk op dat de technieken om kolommen te vergelijken die in deze zelfstudie worden getoond, niet de enige zijn.
Op basis van uw dataset moet u mogelijk de methode wijzigen of aanpassen. De basisprincipes zouden echter hetzelfde blijven.
Als je denkt dat er iets is dat aan deze tutorial kan worden toegevoegd, laat het me dan weten in de comments
Vergelijk twee kolommen voor exacte rijovereenkomst
Dit 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: Vergelijk cellen in dezelfde rij
Hieronder staat een dataset waarbij ik moet controleren of de naam in kolom A hetzelfde is in kolom B of niet.
Als er een overeenkomst is, heb ik het resultaat nodig als "TRUE", en als het niet overeenkomt, heb ik het resultaat nodig als "FALSE".
De onderstaande formule zou dit doen:
=A2=B2
Voorbeeld: cellen in dezelfde rij vergelijken (met de ALS-formule)
Als u een meer beschrijvend resultaat wilt krijgen, kunt u een eenvoudige ALS-formule gebruiken om "Overeenkomst" te retourneren wanneer de namen hetzelfde zijn en "Mismatch" wanneer de namen verschillend zijn.
=ALS(A2=B2,"Overeenkomst","Niet-overeenkomend")
Opmerking: als u de vergelijking hoofdlettergevoelig wilt maken, gebruikt u de volgende IF-formule:
=ALS(EXACT(A2,B2),"Overeenkomst","Niet-overeenkomend")
Met de bovenstaande formule zouden 'IBM' en 'ibm' als twee verschillende namen worden beschouwd en zou de bovenstaande formule 'Mismatch' retourneren.
Voorbeeld: rijen markeren met overeenkomende gegevens
Als u de rijen met overeenkomende gegevens wilt markeren (in plaats van het resultaat in een aparte kolom te krijgen), kunt u dat doen met behulp van voorwaardelijke opmaak.
Hier zijn de stappen om dit te doen:
- Selecteer de volledige dataset.
- Klik op het tabblad 'Startpagina'.
- Klik in de groep Stijlen op de optie 'Voorwaardelijke opmaak'.
- Klik in de vervolgkeuzelijst op 'Nieuwe regel'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de formule in: =$A1=$B1
- Klik op de knop Opmaak en geef de opmaak op die u op de overeenkomende cellen wilt toepassen.
- Klik OK.
Hiermee worden alle cellen gemarkeerd waar de namen in elke rij hetzelfde zijn.
Vergelijk twee kolommen en markeer overeenkomsten
Als u twee kolommen wilt vergelijken en overeenkomende gegevens wilt markeren, kunt u de dubbele functionaliteit gebruiken in voorwaardelijke opmaak.
Merk op dat dit anders is dan wat we hebben gezien bij het vergelijken van elke rij. In dit geval zullen we geen rij voor rij vergelijken.
Voorbeeld: twee kolommen vergelijken en overeenkomende gegevens markeren
Vaak krijg je datasets met overeenkomsten, maar deze bevinden zich mogelijk niet in dezelfde rij.
Iets zoals hieronder weergegeven:
Merk op dat de lijst in kolom A groter is dan die in B. Er zijn ook enkele 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 zijn de stappen om dit te doen:
- Selecteer de volledige dataset.
- Klik op het tabblad Start.
- Klik in de groep Stijlen op de optie 'Voorwaardelijke opmaak'.
- Beweeg de cursor over de optie Celregels markeren.
- Klik op Dubbele waarden.
- Zorg ervoor dat 'Dupliceren' is geselecteerd in het dialoogvenster Waarden dupliceren.
- Geef de opmaak op.
- Klik OK.
De bovenstaande stappen geven u het resultaat zoals hieronder weergegeven.
Opmerking: Voorwaardelijke opmaak duplicaatregel is niet hoofdlettergevoelig. Dus 'Apple' en 'appel' worden als hetzelfde beschouwd en zouden als duplicaten worden gemarkeerd.
Voorbeeld: twee kolommen vergelijken en niet-overeenkomende gegevens markeren
Als u de namen wilt markeren die in de ene lijst voorkomen en niet in de andere, kunt u hiervoor ook de voorwaardelijke opmaak gebruiken.
- Selecteer de volledige dataset.
- Klik op het tabblad Start.
- Klik in de groep Stijlen op de optie 'Voorwaardelijke opmaak'.
- Beweeg de cursor over de optie Celregels markeren.
- Klik op Dubbele waarden.
- Zorg ervoor dat in het dialoogvenster Dubbele waarden 'Uniek' is geselecteerd.
- Geef de opmaak op.
- Klik OK.
Dit geeft u het resultaat zoals hieronder weergegeven. Het markeert alle cellen met een naam die niet in de andere lijst voorkomt.
Vergelijk twee kolommen en vind ontbrekende gegevenspunten
Als u wilt bepalen of een gegevenspunt uit de ene lijst aanwezig is in de andere lijst, moet u de opzoekformules gebruiken.
Stel u heeft een dataset zoals hieronder weergegeven en u wilt bedrijven identificeren die wel in kolom A maar niet in kolom B staan,
Om dit te doen, kan ik de volgende VERT.ZOEKEN-formule gebruiken.
=ISRROR(VERT.ZOEKEN(A2,$B$2:$B$10,1,0))
Deze formule gebruikt de functie VERT.ZOEKEN om te controleren of een bedrijfsnaam in A in kolom B voorkomt of niet. Als het aanwezig is, retourneert het die naam uit kolom B, anders retourneert het een #N/A-fout.
Deze namen die de fout #N/A retourneren, zijn de namen die ontbreken in kolom B.
De functie ISERROR retourneert TRUE als het resultaat van VERT.ZOEKEN een fout is en ONWAAR als het geen fout is.
Als u een lijst wilt met alle namen waar er geen overeenkomst is, kunt u de resultatenkolom filteren om alle cellen met WAAR te krijgen.
U kunt ook de MATCH-functie gebruiken om hetzelfde te doen;
=NIET(ISGETAL(MATCH(A2,$B$2:$B$10,0)))
Opmerking: persoonlijk gebruik ik liever de Match-functie (of de combinatie van INDEX/MATCH) in plaats van VERT.ZOEKEN. Ik vind het flexibeler en krachtiger. Het verschil tussen Vlookup en Index/Match lees je hier.
Vergelijk twee kolommen en haal de overeenkomende gegevens eruit
Als u twee gegevenssets hebt en u wilt items in de ene lijst met de andere vergelijken en het overeenkomende gegevenspunt ophalen, moet u de opzoekformules gebruiken.
Voorbeeld: trek de overeenkomende gegevens (exact)
In de onderstaande lijst wil ik bijvoorbeeld de marktwaarderingswaarde voor kolom 2 ophalen. Hiervoor moet ik die waarde opzoeken in kolom 1 en vervolgens de overeenkomstige marktwaarderingswaarde ophalen.
Hieronder is de formule die dit zal doen:
=VERT.ZOEKEN(D2,$A$2:$B$14,2,0)
of
=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)
Voorbeeld: trek de overeenkomende gegevens (gedeeltelijk)
Als u een dataset krijgt met een klein verschil in de namen in de twee kolommen, zal het gebruik van de hierboven getoonde opzoekformules niet werken.
Deze opzoekformules hebben een exacte overeenkomst nodig om het juiste resultaat te geven. Er is een geschatte overeenkomstoptie in de functie VERT.ZOEKEN of VERGELIJKEN, maar die kan hier niet worden gebruikt.
Stel je hebt de dataset zoals hieronder weergegeven. 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 een dergelijk geval kunt u een gedeeltelijke zoekopdracht gebruiken door jokertekens te gebruiken.
De volgende formule geeft in dit geval het juiste resultaat:
=VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)
of
=INDEX($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)
In het bovenstaande voorbeeld is de asterisk (*) een jokerteken dat een willekeurig aantal tekens kan vertegenwoordigen. 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.
*Exxon* zou bijvoorbeeld overeenkomen met ExxonMobil (aangezien * een willekeurig aantal tekens kan vertegenwoordigen).
Misschien vind je de volgende Excel-tips en -tutorials misschien ook leuk:
- Hoe twee Excel-bladen te vergelijken (voor verschillen)
- Hoe lege cellen in Excel te markeren
- Markeer ELKE andere RIJ in Excel.
- Excel Advanced Filter: een complete gids met voorbeelden.
- Markeer rijen op basis van een celwaarde in Excel.
