wanneer u met gegevens in Excel werkt, moet u vroeg of laat gegevens vergelijken. Dit kan twee kolommen of zelfs gegevens in verschillende bladen/werkmappen vergelijken.
in deze Excel-zelfstudie laat ik u verschillende methoden zien om twee kolommen in Excel te vergelijken en te zoeken naar overeenkomsten of verschillen.
er zijn meerdere manieren om dit te doen in Excel en in deze zelfstudie zal ik u enkele van deze laten zien (zoals het vergelijken met behulp van de VLOOKUP-formule of als formule of voorwaardelijke opmaak).
dus laten we beginnen!
inhoudsopgave:
- Vergelijk twee kolommen (naast elkaar)
- vergelijk naast elkaar met behulp van de operator
- naast elkaar vergelijken met behulp van de IF-functie
- Markeer rijen met overeenkomende gegevens (of andere gegevens)
- Vergelijk twee kolommen met behulp van VLOOKUP (zoek overeenkomende/verschillende gegevens)
- Vergelijk twee kolommen met behulp van VLOOKUP en zoek overeenkomsten
- Vergelijken van Twee Kolommen Met VERT. en Zoek de Verschillen (Ontbrekende Data Punten)
- veelvoorkomende query ’s bij het vergelijken van twee kolommen
Vergelijk twee kolommen (naast elkaar)
Dit is het meest basale type vergelijking waarbij u een cel in één kolom moet vergelijken met de cel in dezelfde rij in een andere kolom.
stel dat u een dataset hebt zoals hieronder getoond en u wilt gewoon controleren of de waarde in kolom A in een specifieke cel hetzelfde (of verschillend) is in vergelijking met de waarde in de aangrenzende cel.
natuurlijk kun je dit doen als je een kleine dataset hebt als je een grote hebt, kun je een eenvoudige vergelijkingsformule gebruiken om dit voor elkaar te krijgen. En onthoud, er is altijd een kans op menselijke fouten als je dit handmatig doet.
laat me je een paar eenvoudige manieren tonen om dit te doen.
vergelijk naast elkaar met behulp van de operator
stel dat u de onderstaande dataset hebt en u wilt weten welke rijen de overeenkomende gegevens hebben en welke rijen verschillende gegevens hebben.
Hieronder is een eenvoudige formule om twee kolommen te vergelijken (naast elkaar)):
=A2=B2
de bovenstaande formule geeft u een waar als beide waarden hetzelfde zijn en onwaar voor het geval dat niet zo is.
als u alle waarden wilt weten die overeenkomen, past u gewoon een filter toe en toont u alleen alle waardes. En als u alle waarden wilt weten die verschillen, filtert u alle waarden die onwaar zijn (zoals hieronder getoond):
bij het gebruik van deze methode om kolomvergelijking in Excel uit te voeren, is het altijd het beste om te controleren of uw gegevens geen voor-of achterliggende spaties hebben. Als deze aanwezig zijn, ondanks het feit dat ze dezelfde waarde hebben, zal Excel ze als anders laten zien. Hier is een geweldige gids over het verwijderen van voor-en achterspaties in Excel.
naast elkaar vergelijken met behulp van de IF-functie
een andere methode die u kunt gebruiken om twee kolommen te vergelijken, kan zijn met behulp van de IF-functie.
dit is vergelijkbaar met de methode hierboven waar we de “equal to (=) operator” gebruikten, met één extra voordeel. Wanneer u de IF-functie gebruikt, kunt u de waarde kiezen die u wilt krijgen wanneer er overeenkomsten of verschillen zijn.
bijvoorbeeld, als er een overeenkomst is, kunt u de tekst “Overeenkomst” krijgen of kunt u een waarde zoals 1 krijgen. Evenzo, wanneer er een mismatch is, kunt u de formule programmeren om u de tekst “Mismatch” te geven of u een 0 of lege cel te geven.
hieronder staat de IF-formule die ‘Match’ retourneert wanneer de twee cellen de celwaarde hebben en ‘Not A Match’ wanneer de waarde anders is.
=IF(A2=B2,"Match","Not a Match")
de bovenstaande formule gebruikt dezelfde voorwaarde om te controleren of de twee cellen (in dezelfde rij) overeenkomende gegevens hebben of niet (A2=B2). Maar omdat we de IF-functie gebruiken, kunnen we het vragen om een specifieke tekst te retourneren in het geval de voorwaarde WAAR of onwaar is.
zodra u de formuleresultaten in een aparte kolom hebt, kunt u snel de gegevens filteren en Rijen krijgen met de overeenkomende gegevens of rijen met niet-overeenkomende gegevens.
Markeer rijen met overeenkomende gegevens (of andere gegevens)
een andere geweldige manier om snel de rijen met overeenkomende gegevens (of andere gegevens) te controleren, is om deze rijen te markeren met voorwaardelijke opmaak.
u kunt beide doen – markeer rijen die dezelfde waarde in een rij hebben, evenals het geval wanneer de waarde anders is.
stel dat u een dataset hebt zoals hieronder getoond en dat u alle rijen wilt markeren met dezelfde naam.
Hieronder staan de stappen om voorwaardelijke opmaak te gebruiken om rijen met overeenkomende gegevens te markeren:
- Selecteer de volledige dataset (behalve de headers)
- klik op het tabblad Home
- in de groep Stijlen, klik op Conditionele opmaak
- In de opties die worden weergegeven, klik op ‘nieuwe regel’
- in de ‘Nieuwe opmaakregel’ dialoogvenster, klik op de optie – “Gebruik een formule om te bepalen welke cellen te formatteren’
- in de ‘Format values where this formula is true’ veld, Voer de formule in: = $ A2=$B2
- klik op de knop Formaat
- klik op het tabblad ‘vullen’ en selecteer de kleur waarin u de rijen met dezelfde waarde wilt markeren in beide kolommen
- klik op OK
de bovenstaande stappen zouden onmiddellijk de rijen markeren waarvan de naam hetzelfde is in beide kolommen A en B (in dezelfde rij). En in het geval dat de naam anders is, worden die rijen niet gemarkeerd.
als u twee kolommen wilt vergelijken en rijen wilt markeren waarbij de namen verschillend zijn, gebruikt u de onderstaande formule in het dialoogvenster Voorwaardelijke opmaak (in Stap 6).
=$A2<>$B2
Hoe werkt dit?
wanneer we voorwaardelijke opmaak gebruiken met een formule, wordt alleen die cellen gemarkeerd waar de formule waar is.
wanneer we $A2=$B2 gebruiken, zal het elke cel (in beide kolommen) controleren en zien of de waarde in een rij in kolom A gelijk is aan die in kolom B of niet.
in het geval dat het een exacte overeenkomst is, zal het het markeren in de opgegeven kleur, en in het geval het niet overeenkomt, zal het niet.
het beste deel over voorwaardelijke opmaak is dat u geen formule in een aparte kolom hoeft te gebruiken. Wanneer u de regel op een dataset toepast, blijft deze ook dynamisch. Dit betekent dat als u een naam in de dataset wijzigt, voorwaardelijke opmaak dienovereenkomstig zal worden aangepast.
Vergelijk twee kolommen met behulp van VLOOKUP (zoek overeenkomende/verschillende gegevens)
in de bovenstaande voorbeelden heb ik u laten zien hoe u twee kolommen (of lijsten) kunt vergelijken wanneer we alleen cellen naast elkaar vergelijken.
in werkelijkheid zal dit zelden het geval zijn.
in de meeste gevallen heeft u twee kolommen met gegevens en MOET u uitzoeken of een gegevenspunt in de ene kolom in de andere kolom bestaat of niet.
in dergelijke gevallen kunt u geen eenvoudige equal-to sign of zelfs een IF-functie gebruiken.
je hebt iets krachtigers nodig …
… iets dat in VLOOKUP ‘ s steegje past!
laat me u twee voorbeelden tonen waarin we twee kolommen in Excel vergelijken met behulp van de functie VERT. Zoeken om overeenkomsten en verschillen te vinden.
Vergelijk twee kolommen met behulp van VLOOKUP en zoek overeenkomsten
stel dat we een dataset hebben zoals hieronder weergegeven, waar we enkele namen hebben in de kolommen A en B.
Als u hebt om uit te vinden wat zijn de namen in kolom B die ook in kolom A, u kunt gebruik maken van de onderstaande formule VERT.ZOEKEN:
=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")
De bovenstaande formule vergelijkt de twee kolommen (A en B) en geeft u de naam op in het geval dat de naam in kolom B en A, en het geeft “Geen Wedstrijd” in het geval dat de naam in Kolom B en niet in Kolom A.
standaard is de VLOOKUP-functie retourneert een #N/Een fout in het geval dat het niet een exacte match. Dus om te voorkomen dat je het fout, heb ik wikkelde de VLOOKUP-functie in de IFERROR functie, zodat het geeft “Geen Wedstrijd” wanneer de naam niet beschikbaar is in kolom A.
U kunt ook andersom vergelijking om te controleren of de naam in Kolom A en Kolom B. De onderstaande formule zou dat doen:
=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")
Vergelijken van Twee Kolommen Met VERT. en Zoek de Verschillen (Ontbrekende Data Punten)
Terwijl in het bovenstaande voorbeeld hebben we gecontroleerd of de gegevens in één kolom, was er in een andere kolom of niet.
u kunt hetzelfde concept ook gebruiken om twee kolommen te vergelijken met behulp van de VLOOKUP-functie en ontbrekende gegevens te vinden.
stel dat we een dataset hebben zoals hieronder getoond, waar we enkele namen hebben in de kolommen A en B.
Als u moet uitzoeken wat de namen zijn die in kolom B staan die er niet zijn in kolom A, kunt u de onderstaande VLOOKUP-formule gebruiken:
=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")
de bovenstaande formule controleert de naam in kolom B met alle namen in kolom A. In het geval dat het een exacte overeenkomst vindt, zal het die naam teruggeven, en in het geval het geen exacte overeenkomst vindt, zal het de #N/A-fout retourneren.
omdat ik geïnteresseerd ben in het vinden van de ontbrekende Namen die er zijn is Kolom B en niet in kolom A, moet ik de namen weten die de #N/A-fout retourneren.
daarom heb ik de VLOOKUP-functie in de IF-en ISERROR-functies verpakt. Deze hele formule geeft de waarde – “niet beschikbaar” wanneer de naam ontbreekt in kolom A, en” beschikbaar ” wanneer deze aanwezig is.
om alle ontbrekende namen te weten, kunt u de resultaatkolom filteren op basis van de waarde “niet beschikbaar”.
u kunt ook de onderstaande MATCH-functie gebruiken om hetzelfde resultaat te krijgen:
=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")
veelvoorkomende query ’s bij het vergelijken van twee kolommen
hieronder zijn enkele veelvoorkomende query’ s die ik meestal krijg wanneer mensen gegevens in twee kolommen in Excel proberen te vergelijken.
Q1. Hoe meerdere kolommen in Excel in dezelfde rij te vergelijken voor wedstrijden? Tel ook de totale duplicaten.
Ans. We hebben de procedure gegeven om twee kolommen in excel te vergelijken voor dezelfde rij hierboven. Maar als u meerdere kolommen in excel voor dezelfde rij wilt vergelijken, zie dan het voorbeeld
=IF(AND(A2=B2, A2=C2),"Full Match", "")
hier hebben we gegevens van kolom A, kolom B en kolom C vergeleken. hierna heb ik de bovenstaande formule in kolom D toegepast en krijg ik het resultaat.
om de duplicaten te tellen, moet u de functie Aantal.Als gebruiken.
=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")
Q2. Welke operator gebruikt u voor wedstrijden en verschillen?
Ans. Hieronder zijn de operators te gebruiken:
- om overeenkomsten te vinden, gebruik de gelijk aan teken ( = )
- om verschillen (mismatches) te vinden, gebruik de niet-gelijk-om te tekenen (<>)
V3. Hoe twee verschillende tabellen te vergelijken en de bijbehorende gegevens te trekken?
Ans. Hiervoor kunt u de functie VERT.zoeken of INDEX & MATCH-functie gebruiken. Om dit op een betere manier te begrijpen zullen we een voorbeeld nemen.
hier zullen we twee tabellen nemen en nu willen we pull matching data doen. In de eerste tabel hebt u een dataset en in de tweede tabel neemt u de lijst met vruchten en gebruikt u pull matching data in een andere kolom. Gebruik de formule voor pull matching
=INDEX($B:$B,MATCH($D2,$A:$A,0))
v4. Hoe duplicaten in Excel te verwijderen?
Ans. Om dubbele gegevens te verwijderen, moet u eerst de dubbele waarden vinden.
om het duplicaat te vinden, kunt u verschillende methoden gebruiken, zoals voorwaardelijke opmaak, Vlookup, If Statement en nog veel meer. Excel heeft ook een ingebouwde tool waar u gewoon de gegevens kunt selecteren en de duplicaten uit een kolom of zelfs meerdere kolommen
Q5 Kunt verwijderen. Ik kan zien dat er een overeenkomende waarde in beide kolommen. De formules die u hierboven hebt gedeeld, beschouwen deze echter niet als exacte overeenkomsten. Waarom?
Ans: Excel beschouwt iets als een exacte overeenkomst wanneer elk teken van de ene cel gelijk is aan de andere. Er is een grote kans dat er in uw dataset voor-of achterliggende spaties zijn.
hoewel deze spaties de waarden nog steeds gelijk kunnen laten lijken aan een met het blote oog, zijn deze voor Excel anders. Als je zo ‘ n dataset hebt, is het het beste om van deze spaties af te komen (Je kunt hiervoor Excel-functies gebruiken zoals TRIM).
Q7. Hoe twee kolommen te vergelijken die het resultaat als Waar geven wanneer de gehele waarden van alle eerste kolommen niet minder zijn dan de gehele waarden van de tweede kolom. Om dit probleem op te lossen, heb ik geen voorwaardelijke opmaak, Vlookup-functie, If-Statement en andere formules nodig. Ik heb de formule nodig om dit probleem op te lossen.
Ans. U kunt de matrixformule gebruiken om dit probleem op te lossen.
de syntaxis is {=AND (H6:H12>I6: I12)}. Dit geeft u ” True “als resultaat wanneer de waarde van Kolom H groter is dan de waarde in kolom I anders zal” False ” het resultaat zijn.
de volgende Excel-tutorials zijn misschien ook interessant:
- Vergelijk twee kolommen in Excel (voor overeenkomsten en verschillen)
- kolommen verbergen op basis van de celwaarde in Excel
- een kolom in meerdere kolommen in Excel splitsen
- alternatieve Kolommen selecteren in Excel (of elke n-de kolom)
- plakken in een gefilterde kolom de verborgen cellen overslaan
- maakt u een Excel Pro in 2020)
rapporteer dit ad 