Hoi Paula, kun je lijsten vergelijken in Excel?
of
Wat is de beste manier om twee gegevenssets te vergelijken in Excel?
Zeer vaak is het in Excel nodig om twee lijsten te vergelijken, of twee gegevenssets om ontbrekende of overeenkomende items te vinden. Aangezien dit Excel is, zijn er altijd meer dan één manier om dingen te doen, inclusief het vergelijken van gegevens. Van formules en voorwaardelijke opmaak tot Power Query. In dit artikel gaan we kijken naar een aantal manieren om twee lijsten te vergelijken in Excel en we zullen ook kijken naar het vergelijken van hele rijen van een dataset.
Misschien is de manier van uw voorkeur niet opgenomen hieronder. Zo niet, waarom laat je dan niet hieronder een reactie achter en deel met ons hoe jij het liefst twee lijsten of datasets vergelijkt in Excel. Ik kijk uit naar uw commentaar.
Wij willen lijst 1 met lijst 2 vergelijken.
Inhoud
Snel voorwaardelijke opmaak om twee kolommen met gegevens te vergelijken
De voorwaardelijke opmaak opheffen
Gegevens vergelijken in Excel met de functie MATCH
Twee lijsten vergelijken in Excel 365 met de functie MATCH of XMATCH als een dynamische matrixfunctie
MATCH en dynamische matrices om 2 lijsten te vergelijken
XMATCH Excel 365 om twee lijsten te vergelijken
Tabellen – Vergelijken van lijsten in Excel waarbij de omvang van de bereiken kan veranderen
Het accentueren van verschillen in lijsten met behulp van aangepaste voorwaardelijke opmaak
Kopieer formule naar Aangepaste voorwaardelijke opmaak
Andere formules gebruikt om twee lijsten in Excel te vergelijken
VLOOKUP om twee lijsten in Excel te vergelijken
XLOOKUP om twee lijsten in Excel te vergelijken
COUNTIF om twee lijsten in Excel te vergelijken
Hoe vergelijkt u 2 gegevensverzamelingen in Excel
Lijsten of gegevenssets vergelijken met Power Query
Snel voorwaardelijke opmaak om twee kolommen met gegevens te vergelijken
Voorwaardelijke opmaak stelt u in staat een cel of bereik te markeren op basis van vooraf gedefinieerde criteria. De snelste en eenvoudigste manier om deze twee kolommen snel visueel te vergelijken is door gebruik te maken van de voorgedefinieerde regel Dubbele waarde markeren.
Begin met het selecteren van de twee kolommen met gegevens.
Vanuit het tabblad Home selecteert u de vervolgkeuzelijst Voorwaardelijke opmaak. Selecteer vervolgens Cellen markeren regels. Selecteer vervolgens Waarden dupliceren.
Er wordt een instellingenvak voor Waarden dupliceren geopend waarin u de opmaak kunt definiëren en kunt kiezen tussen Waarden dupliceren of Unieke waarden.
Door Waarden dupliceren te selecteren, worden alle terugkerende items ingesteld op de geselecteerde opmaak. Nu kunt u snel de items in Lijst 1 zien die in Lijst 2 staan, aangezien dit de opgemaakte items zijn. U kunt ook snel de items in lijst 2 zien die niet in lijst 1 staan, aangezien deze geen opmaak hebben.
U kunt echter ook de Unieke items opmaken. Dit kan worden bereikt door Uniek te selecteren in het Setup-vak Duplicate Values.
In dit geval hebben we twee verschillende voorwaardelijke opmaken toegepast. De rode geeft de duplicaten aan en de groene geeft de unieke items aan.
Merk op, ik heb niet alleen de cellen met gegevens genomen, maar alle kolommen A tot en met C. Kolom B bevat geen gegevens, dus die kan de resultaten niet beïnvloeden. Deze cellen bevatten echter wel de toegepaste voorwaardelijke opmaak, dus het zou een betere praktijk zijn om alleen de cellen te selecteren die u nodig hebt.
De voorwaardelijke opmaak wissen
Om alle voorwaardelijke opmaak te wissen, selecteert u eerst de cel, of het bereik. Selecteer dan de voorwaardelijke opmaak drop down op het Home lint. Selecteer vervolgens Regels wissen. Selecteer tenslotte Regels wissen uit geselecteerde cellen.
Als u meer dan één voorwaardelijke opmaak tegelijk hebt toegepast en u er slechts één wilt verwijderen, selecteert u Regels beheren in de vervolgkeuzelijst voor voorwaardelijke opmaak. Selecteer de regel die u wilt verwijderen en selecteer vervolgens Regel verwijderen.
Door op OK te drukken, wordt de regel verwijderd uit de Regelingenmanager en bevatten de cellen niet langer de opmaak.
Dat is dus de meest eenvoudige manier waarop u twee lijsten in Excel kunt vergelijken. Het is snel, eenvoudig en effectief. U kunt ook voorwaardelijke opmaak toepassen op basis van formules, die we later in dit artikel zullen bekijken.
Gegevens in Excel vergelijken met behulp van de functie MATCH
Er zijn veel lookup-formules die u kunt gebruiken om twee bereiken of lijsten in Excel te vergelijken. De eerste die we zullen bekijken is de MATCH-functie.
De MATCH-functie geeft de relatieve positie in een lijst terug. Een getal op basis van zijn positie, indien gevonden, in de lookup array.
De syntaxis voor MATCH is
=MATCH (lookup value, Lookup array, Match type)
Waarbij lookup value de waarde is waarvoor u een overeenkomst wilt vinden. Lookup array is de lijst waarin je zoekt naar een overeenkomst. En met Match type kunt u kiezen tussen een exacte of een benaderende overeenkomst.
We willen een match formule schrijven om te zien of de items in Lijst 2 in Lijst 1 staan.
In cel E3 kunnen we de formule
=MATCH(C2, $A$2:$A$21,0)
Door deze formule in te vullen, wordt voor de waarden waarbij Excel een overeenkomst vindt, de positie van die overeenkomst geretourneerd. Wanneer er geen overeenkomst is, is de returnwaarde een #N/A.
Vaak is de relatieve positie of de #N/A van geen waarde voor ons en moeten we deze waarden omzetten in waar of onwaar. Om dit te doen kunnen wij gemakkelijk onze Match formule uitbreiden met een logische functie. Aangezien Match een getal retourneert, kunnen we de functie ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Vergelijk 2 lijsten in Excel 365 met MATCH of XMATCH als een dynamische matrixfunctie
Als u Excel 365 gebruikt, hebt u nog meer alternatieven wanneer u MATCH gebruikt om lijsten of gegevens te vergelijken. Omdat Excel 365 denkt in arrays, kunnen we nu een array doorgeven als de lookup waarde van MATCH en onze resultaten zullen voor ons morsen. Hierdoor hoeft u de formule niet meer te kopiëren en met slechts 1 formule is uw spreadsheet minder foutgevoelig en compacter.
MATCH en Dynamische matrices om 2 lijsten te vergelijken
Als u nog niet bekend bent met Dynamische matrices, raad ik u aan dit artikel eens te lezen: Excel Dynamic Arrays – Een nieuwe manier om uw Excel-spreadsheets te modelleren: om een beter inzicht te krijgen in hoe ze werken en morsbereiken.
De enige wijziging in de match-formule is dat we in plaats van cel C2 te selecteren als onze lookup-waarde, het bereik C2:12
=ISNUMMER(MATCH (C2:C12,$A$2:$A$21,0))
Zoals u kunt zien, morst de ene formule de resultaten naar beneden in kolom E.
XMATCH Excel 365 om twee lijsten te vergelijken
Excel 365 introduceert ook de nieuwe functie XMATCH. Net als de MATCH functie geeft XMATCH een relatieve positie in een lijst terug. Nu u bekend bent met XLOOKUP, die de oude functie VLOOKUP vervangt, weet u dat XLOOKUP extra kracht heeft. Dit komt in de vorm van nieuwe voorwaarden in de formule-syntaxis, zoals zoekmodus en overeenkomst-typen. Nou, XMATCH ook als deze extra kracht over zijn voorganger MATCH.
De syntaxis voor XMATCH is
XMATCH (Lookup Value, Lookup Array, ,)
Waar
Lookup Value is de waarde die u zoekt om de relatieve positie te vinden
Lookup Array is de rij of kolom die de Lookup Value bevat
Match mode is optioneel. In tegenstelling tot de oude MATCH functie, is de standaard een exacte overeenkomst. U kunt ook kiezen tussen
- Exacte overeenkomst of volgende kleinste
- Exacte overeenkomst of volgende grootste
- Wildcard overeenkomst
Zoekmodus is ook optioneel. De standaard (en enige optie in de oude MATCH functie) is om van boven naar beneden te zoeken. U kunt ook kiezen voor last to first en binaire zoekopdrachten. Als u met binaire zoekopdrachten werkt. De jokerteken-overeenkomst optie werkt niet.
Met XMATCH kunnen we Dynamische matrices of celverwijzingen gebruiken om de formule te maken, net zoals we hebben bekeken met MATCH. Voor dit voorbeeld zullen wij Dynamische Arrays gebruiken. De formule is zeer vergelijkbaar met wat wij gebruikten met MATCH; behalve dat wij niet 0 hoeven te selecteren voor een exacte overeenkomst zoals in XMATCH dit de standaard instelling is. Laten we de zaken een beetje door elkaar gooien en deze keer kijken naar het vinden van items in lijst 2 die niet in lijst 1 staan.
In dit geval kunnen we de formule
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Waarbij not waar in onwaar zal veranderen en onwaar in waar.
Tabellen – Vergelijken van lijsten in Excel waarbij de omvang van de bereiken kan veranderen
In elk van de formules die we tot nu toe hebben bekeken, hebben we in onze Match-functies een cellenbereik geselecteerd dat niet dynamisch is. Dat betekent dat als we nieuwe gegevens toevoegen aan een van de lijsten, we een handmatige stap hebben om onze formule bij te werken om de nieuwe gegevens op te nemen.
Om de lijsten om te zetten in tabellen, selecteert u een van de lijsten en drukt u op CTRL. Dit is de sneltoets voor het converteren naar een tabel. Als u de kop in het cellenbereik hebt geselecteerd, vinkt u het vakje aan om te bevestigen dat uw tabel koppen heeft.
Tabellen maken van nature gebruik van gestructureerde naamgeving. Wanneer u dus een formule schrijft en u selecteert een kolom uit een tabel, dan worden geen celverwijzingen weergegeven, maar de kolomnaam.
Kijkend naar onze vorige formule die XMATCH gebruikt om items in lijst 2 te vinden die niet in lijst 1 staan, kunnen we deze functie nu herschrijven met gebruikmaking van onze tabelverwijzingen
=NOT (ISNUMBER(XMATCH(list2,list1)))
Nu, aangezien we tabellen hebben gebruikt, als we een nieuwe rij aan een van de tabellen toevoegen, zal ons margebereik ook toenemen om de nieuwe gegevens op te nemen.
Verschillen in lijsten markeren met aangepaste voorwaardelijke opmaak
Eerder in dit artikel hebben we gekeken naar een zeer snelle manier om deze twee lijsten te vergelijken met behulp van een vooraf gedefinieerde regel voor duplicaten. We kunnen echter ook aangepaste voorwaardelijke opmaak gebruiken. Als je niet bekend bent met aangepaste voorwaardelijke opmaak, raad ik je aan dit artikel te lezen: Excel Dynamische Voorwaardelijke Opmaak trucs:
We moeten hier kijken naar twee verschillende benaderingen om de verschillen te benadrukken. Wanneer we geen tabellen gebruiken en we hebben een waar/waar formule gemaakt om de verschillen te identificeren, kunnen we een kopie van de formule nemen en deze toevoegen aan onze Aangepaste opmaak. Echter, met Tabellen moeten we het gebruik van celverwijzingen forceren.
Kopieer formule naar Aangepaste Voorwaardelijke Opmaak
Start met het nemen van een kopie van de formule. Omdat we de formule in de spreadsheet hebben getest, kunnen we zien dat hij werkt voordat we hem gebruiken in de voorwaardelijke opmaak. Dit is de beste werkwijze, omdat het met relatieve en absolute celverwijzingen vaak moeilijk kan zijn om de formule juist te krijgen.
Selecteer de cellen waarop u de aangepaste opmaak wilt toepassen. Selecteer vervolgens vanaf het beginlint de vervolgkeuzelijst voor voorwaardelijke opmaak en selecteer Nieuwe regel
Het vak Nieuwe opmaakregel wordt geopend en selecteer Formule gebruiken om te bepalen welke cellen moeten worden opgemaakt. Plak vervolgens de formule en stel het opmaaktype in
Dit zal ertoe leiden dat alle cellen in beide lijsten worden opgemaakt volgens de door u gekozen opmaak.
Bedenk dat we een cellenbereik hebben geselecteerd om deze voorwaardelijke opmaak toe te passen en dat deze niet dynamisch is. Als we tabellen zouden gebruiken, zou dit worden bijgewerkt zonder dat we iets hoeven te veranderen!
Andere formules om twee lijsten in Excel te vergelijken
Er zijn veel formules die u kunt gebruiken om twee lijsten in Excel te vergelijken. We hebben al gekeken naar MATCH en XMATCH, maar nu gaan we kijken naar een paar meer. Elk van de lookup functies zal echt werken samen met enkele andere!
VLOOKUP om twee lijsten te vergelijken in Excel
Als u niet bekend bent met VLOOKUP, kunt u er hier over lezen. Simpel gezegd zal VLOOKUP een overeenkomstige waarde uit een cel teruggeven, als er geen overeenkomstige waarde is zal een #N/A fout worden teruggegeven. In ons voorbeeld werken we met tekst. We kunnen dus een VLOOKUP uitvoeren en testen of die tekst teruggeeft. Als we met getallen zouden werken, zouden we ISTEXT kunnen vervangen door ISNUMBER.
We zouden de functie =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Of als we Dynamische arrays in Excel 365 gebruiken, zouden we de functie
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP om twee lijsten in Excel te vergelijken
XLOOKUP is geïntroduceerd in Excel 365 en u kunt er hier meer over te weten komen. Net als VLOOKUP retourneert XLOOKUP een overeenkomstige waarde uit een cel, en u kunt een resultaat definiëren als de waarde niet wordt gevonden. Met behulp van dynamische matrices zou de functie zijn
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF om twee lijsten in Excel te vergelijken
De COUNTIF-functie telt het aantal keren dat een waarde, of tekst, binnen een bereik voorkomt. Als de waarde niet wordt gevonden, wordt 0 geretourneerd. We kunnen dit combineren met een IF-instructie om onze waar en onwaar waarden terug te geven.
=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)
Hoe vergelijk je 2 datasets in Excel
Het vergelijken van twee lijsten is eenvoudig genoeg en we hebben nu gekeken naar verschillende manieren om dit te doen. Maar het vergelijken van twee gegevensverzamelingen kan een beetje moeilijker zijn.
Laten we eens kijken naar een voorbeeld. We hebben twee tabellen met gegevens, elk met dezelfde kolomkoppen. Als we naar de afbeelding kijken, kunnen we zien dat we meer dan één kolom moeten bekijken om deze twee tabellen te matchen.
Wanneer u meer dan één kolom moet bekijken, is de oplossing een samengestelde kolom te maken waarin de gegevens in één kolom worden gecombineerd. Dit creëert een unieke kolom voor elke rij die we dan kunnen gebruiken als de overeenkomstige kolom
In dit voorbeeld zouden we de Naam en de DoB kunnen combineren om elke tabel een unieke identificatie te geven
Er zijn vele manieren om de inhoud van een cel samen te voegen, in dit geval doen we een eenvoudige concatenate. Aangezien wij tabellen gebruiken, zal de formule zich aan de tabelnaamgeving aanpassen.
= &”-“&
Herhaal de stappen voor de tweede tabel.
Nu kunnen we elk van de bovenstaande voorbeelden gebruiken om deze twee nieuwe kolommen met gegevens te vergelijken. Waar ze overeenkomen, weten we dat ze overeenkomen met de hele rij.
Lijsten of gegevensreeksen vergelijken met behulp van Power Query
U kunt ook lijsten en gegevensreeksen vergelijken met behulp van Excels Power Query. Door verbinding te maken met de tabellen en vervolgens de tabellen samen te voegen, met behulp van verschillende join types, kunnen we beide lijsten vergelijken.
In deze video leert u hoe u twee verschillende datasets kunt vergelijken of met elkaar in overeenstemming brengen met behulp van Excels Power Query
Er is een dataset die bij deze video en oefening hoort en die u uit dit artikel kunt halen.