Een van de vele mogelijkheden van Microsoft Excel is de mogelijkheid om twee lijsten met gegevens te vergelijken, overeenkomsten tussen de lijsten te identificeren en te identificeren welke items in slechts één lijst worden gevonden. Dit is handig bij het vergelijken van financiële gegevens of bij het controleren of een bepaalde naam in een database voorkomt. U kunt de VERGELIJKEN-functie gebruiken om overeenkomende of niet-overeenkomende records te identificeren en te markeren, of u kunt conditioneringsopmaak gebruiken met de AANTAL. ALS-functie. De volgende stappen vertellen u hoe u elk kunt gebruiken om uw gegevens te matchen.
Stappen
Methode 1 van 2: Records identificeren met de MATCH-functie
Stap 1. Kopieer de gegevenslijsten op één werkblad
Excel kan werken met meerdere werkbladen binnen een enkele werkmap, of met meerdere werkmappen, maar u zult het vergelijken van de lijsten gemakkelijker vinden als u hun informatie naar een enkel werkblad kopieert.
Stap 2. Geef elk lijstitem een unieke identificatie
Als uw twee lijsten geen gemeenschappelijke manier hebben om ze te identificeren, moet u mogelijk een extra kolom toevoegen aan elke gegevenslijst die dat item in Excel identificeert, zodat het kan zien of een item in een bepaalde lijst gerelateerd is aan een item in de andere lijst. De aard van deze identifier hangt af van het soort gegevens dat u probeert te matchen. U heeft een identifier nodig voor elke kolomlijst.
- Voor financiële gegevens die aan een bepaalde periode zijn gekoppeld, zoals belastinggegevens, kan dit de beschrijving van een actief zijn, de datum waarop het actief is verworven of beide. In sommige gevallen kan een vermelding worden geïdentificeerd met een codenummer; als hetzelfde systeem echter niet voor beide lijsten wordt gebruikt, kan deze identifier overeenkomsten creëren waar er geen zijn of overeenkomsten negeren die moeten worden gemaakt.
- In sommige gevallen kunt u items uit de ene lijst nemen en deze combineren met items uit een andere lijst om een identificatie te maken, zoals een fysieke activabeschrijving en het jaar waarin het is gekocht. Om zo'n identifier te maken, voegt u gegevens uit twee of meer cellen aan elkaar (toevoegen, combineren) met behulp van het ampersand (&). Om een itembeschrijving in cel F3 te combineren met een datum in cel G3, gescheiden door een spatie, typt u de formule '=F3&" "&G3' in een andere cel in die rij, zoals E3. Als u alleen het jaar in de identifier wilt opnemen (omdat de ene lijst volledige datums gebruikt en de andere alleen jaren), zou u de functie JAAR opnemen door in plaats daarvan '=F3&" "&YEAR(G3)' in cel E3 in te voeren. (Neem de enkele aanhalingstekens niet op; ze zijn er alleen om het voorbeeld aan te geven.)
- Nadat u de formule hebt gemaakt, kunt u deze naar alle andere cellen van de ID-kolom kopiëren door de cel met de formule te selecteren en de vulgreep over de andere cellen van de kolom te slepen waar u de formule wilt kopiëren. Wanneer u uw muisknop loslaat, wordt elke cel waar u overheen sleept gevuld met de formule, met de celverwijzingen aangepast aan de juiste cellen in dezelfde rij.
Stap 3. Standaardiseer data waar mogelijk
Terwijl de geest herkent dat "Inc." en "Incorporated" betekenen hetzelfde, Excel niet, tenzij u het een of ander woord opnieuw laat formatteren. Evenzo kunt u waarden zoals $ 11, 950 en $ 11, 999,95 als dicht genoeg beschouwen om overeen te komen, maar Excel zal dat niet doen, tenzij u het zegt.
- U kunt met sommige afkortingen omgaan, zoals "Co" voor "Company" en "Inc" voor "Incorporated door de LEFT-tekenreeksfunctie te gebruiken om de extra tekens af te kappen. Andere afkortingen, zoals "Assn" voor "Association", kunnen het beste zijn opgelost door een stijlgids voor gegevensinvoer op te stellen en vervolgens een programma te schrijven om onjuiste indelingen op te zoeken en te corrigeren.
- Voor reeksen getallen, zoals postcodes waarbij sommige vermeldingen het achtervoegsel ZIP+4 bevatten en andere niet, kunt u opnieuw de functie LEFT string gebruiken om alleen de primaire postcodes te herkennen en te matchen. Om Excel numerieke waarden te laten herkennen die dichtbij maar niet hetzelfde zijn, kunt u de functie ROUND gebruiken om dichte waarden naar hetzelfde getal af te ronden en ze te matchen.
- Extra spaties, zoals het typen van twee spaties tussen woorden in plaats van één, kunnen worden verwijderd met behulp van de TRIM-functie.
Stap 4. Maak kolommen voor de vergelijkingsformule
Net zoals u kolommen moest maken voor de lijst-ID's, moet u kolommen maken voor de formule die de vergelijking voor u doet. Voor elke lijst heb je één kolom nodig.
U wilt deze kolommen labelen met iets als 'Ontbrekend?'
Stap 5. Voer de vergelijkingsformule in elke cel in
Voor de vergelijkingsformule gebruikt u de MATCH-functie die is genest in een andere Excel-functie, ISNA.
- De formule heeft de vorm van "=ISNA(MATCH(G3, $L$3:$L$14, FALSE))", waarbij een cel van de identifier-kolom van de eerste lijst wordt vergeleken met elk van de identifiers in de tweede lijst om kijk of het overeenkomt met een van hen. Als het niet overeenkomt, ontbreekt een record en wordt het woord "TRUE" in die cel weergegeven. Als het wel overeenkomt, is het record aanwezig en wordt het woord "FALSE" weergegeven. (Neem bij het invoeren van de formule de tussenliggende aanhalingstekens niet op.)
- U kunt de formule op dezelfde manier naar de resterende cellen van de kolom kopiëren als waarop u de formule voor de celidentificatie hebt gekopieerd. In dit geval verandert alleen de celverwijzing voor de identificatiecel, aangezien het plaatsen van de dollartekens voor de rij- en kolomverwijzingen voor de eerste en laatste cellen in de lijst met de tweede celidentificaties ze absolute verwijzingen maakt.
- U kunt de vergelijkingsformule voor de eerste lijst kopiëren naar de eerste cel van de kolom voor de tweede lijst. U moet dan de celverwijzingen bewerken zodat "G3" wordt vervangen door de verwijzing voor de eerste identifier-cel van de tweede lijst en "$L$3:$L$14" wordt vervangen door de eerste en laatste identifier-cel van de tweede lijst. (Laat de dollartekens en de dubbele punt alleen staan.) U kunt deze bewerkte formule vervolgens kopiëren naar de resterende cellen in de vergelijkingsrij van de tweede lijst.
Stap 6. Sorteer de lijsten om indien nodig gemakkelijker niet-overeenkomende waarden te zien
Als uw lijsten groot zijn, moet u ze mogelijk sorteren om alle niet-overeenkomende waarden samen te voegen. De instructies in de onderstaande substappen converteren de formules naar waarden om herberekeningsfouten te voorkomen, en als uw lijsten groot zijn, vermijdt u een lange herberekeningstijd.
- Sleep uw muis over alle cellen in een lijst om deze te selecteren.
- Selecteer Kopiëren in het menu Bewerken in Excel 2003 of in de groep Klembord van het Home-lint in Excel 2007 of 2010.
- Selecteer Plakken speciaal in het menu Bewerken in Excel 2003 of via de vervolgkeuzeknop Plakken in de groep Klembord van het lint van Excel 2007 of 2010s Home.
- Selecteer "Waarden" in de lijst Plakken als in het dialoogvenster Plakken speciaal. Klik op OK om het dialoogvenster te sluiten.
- Selecteer Sorteren in het menu Gegevens in Excel 2003 of de groep Sorteren en filteren van het lint Gegevens in Excel 2007 of 2010.
- Selecteer "Koptekstrij" in de lijst "Mijn gegevensbereik heeft" in het dialoogvenster Sorteren op, selecteer "Ontbrekend?" (of de naam die u daadwerkelijk aan de kolomkop van de vergelijking hebt gegeven) en klik op OK.
- Herhaal deze stappen voor de andere lijst.
Stap 7. Vergelijk de niet-overeenkomende items visueel om te zien waarom ze niet overeenkomen
Zoals eerder opgemerkt, is Excel ontworpen om te zoeken naar exacte gegevensovereenkomsten, tenzij u het instelt om naar geschatte overeenkomsten te zoeken. Uw non-match kan zo simpel zijn als het per ongeluk transponeren van letters of cijfers. Het kan ook iets zijn dat onafhankelijke verificatie vereist, zoals controleren of beursgenoteerde activa in de eerste plaats moeten worden gerapporteerd.
Methode 2 van 2: Voorwaardelijke opmaak met AANTAL. ALS
Stap 1. Kopieer de gegevenslijsten op één werkblad
Stap 2. Bepaal in welke lijst u overeenkomende of niet-overeenkomende records wilt markeren
Als u records in slechts één lijst wilt markeren, wilt u waarschijnlijk de records markeren die uniek zijn voor die lijst; dat wil zeggen records die niet overeenkomen met records in de andere lijst. Als u records in beide lijsten wilt markeren, wilt u records markeren die wel met elkaar overeenkomen. Voor dit voorbeeld gaan we ervan uit dat de eerste lijst de cellen G3 tot en met G14 bevat en dat de tweede lijst de cellen L3 tot en met L14 bevat.
Stap 3. Selecteer de items in de lijst waarin u unieke of overeenkomende items wilt markeren
Als u overeenkomende items in beide lijsten wilt markeren, moet u de lijsten één voor één selecteren en de vergelijkingsformule (beschreven in de volgende stap) op elke lijst toepassen.
Stap 4. Pas de juiste vergelijkingsformule toe
Om dit te doen, moet u het dialoogvenster Voorwaardelijke opmaak openen in uw versie van Excel. In Excel 2003 doet u dit door Voorwaardelijke opmaak te selecteren in het menu Opmaak, terwijl u in Excel 2007 en 2010 op de knop Voorwaardelijke opmaak in de groep Stijlen van het Home-lint klikt. Selecteer het regeltype als "Formule" en voer uw formule in het veld Regelbeschrijving bewerken in.
- Als u records wilt markeren die uniek zijn voor de eerste lijst, is de formule "=AANTAL. ALS($L$3:$L$14, G3=0)", waarbij het cellenbereik van de tweede lijst wordt weergegeven als absolute waarden en de verwijzing naar de eerste cel van de eerste lijst als relatieve waarde. (Voer geen aanhalingstekens in.)
- Als u records wilt markeren die uniek zijn voor de tweede lijst, is de formule "=AANTAL. ALS($G$3:$G$14, L3=0)", waarbij het cellenbereik van de eerste lijst wordt weergegeven als absolute waarden en de verwijzing naar de eerste cel van de tweede lijst als relatieve waarde. (Voer geen aanhalingstekens in.)
- Als u de records in elke lijst wilt markeren die in de andere lijst worden gevonden, hebt u twee formules nodig, één voor de eerste lijst en één voor de tweede. De formule voor de eerste lijst is "=AANTAL. ALS($L$3:$L$14, G3>0)", terwijl de formule voor de tweede lijst AANTAL. ALS($G$3:$G$14, L3>0)" is. eerder opgemerkt, selecteert u de eerste lijst om de formule toe te passen en selecteert u vervolgens de tweede lijst om de formule toe te passen.
- Pas de opmaak toe die u wilt om de gemarkeerde records te markeren. Klik op OK om het dialoogvenster te sluiten.
Video - Door deze service te gebruiken, kan bepaalde informatie worden gedeeld met YouTube
Tips
- In plaats van een celverwijzing te gebruiken met de AANTAL. ALS voorwaardelijke opmaakmethode, kunt u een waarde invoeren waarnaar moet worden gezocht en een of meer lijsten markeren voor instanties van die waarde.
- Om de vergelijkingsformulieren te vereenvoudigen, kunt u namen voor uw lijst maken, zoals 'Lijst1' en 'Lijst2'. Bij het schrijven van de formules kunnen deze lijstnamen de absolute celbereiken vervangen die in de bovenstaande voorbeelden worden gebruikt.
