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 het controleren om te zien of een bepaalde naam in een database staat. U kunt de MATCH-functie gebruiken om overeenkomende of niet-overeenkomende records te identificeren en te markeren, of u kunt conditionering opmaken gebruiken met de AANTAL.ALS-functie. De volgende stappen vertellen u hoe u elk moet gebruiken om uw gegevens aan te passen.

Methode één van de twee:
Records identificeren met de MATCH-functie

  1. 1 Kopieer de gegevenslijsten naar een enkel 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 kopieert naar een enkel werkblad.
  2. 2 Geef elk lijstitem een ​​uniek ID. 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 aan Excel identificeert, zodat het kan zien of een item in een bepaalde lijst gerelateerd is aan een artikel in de andere lijst. De aard van deze ID hangt af van het soort gegevens dat u probeert aan te passen. U hebt een ID nodig voor elke kolomlijst.
    • Voor financiële gegevens die zijn gekoppeld aan een bepaalde periode, zoals belastinggegevens, kan dit de beschrijving van een activum zijn, de datum waarop het activum 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 dit ID overeenkomen met waar er geen zijn of negeer overeenkomsten die moeten worden gemaakt.
    • In sommige gevallen kunt u items uit één lijst nemen en deze combineren met items uit een andere lijst om een ​​ID te maken, zoals een beschrijving van een fysieke activum en het jaar waarin deze is gekocht. Om een ​​dergelijke identifier te maken, voegt u (toevoegen, combineren) gegevens van twee of meer cellen samen met behulp van de ampersand (&). Als u een artikelbeschrijving in cel F3 wilt combineren met een datum in cel G3, gescheiden door een spatie, geeft u de formule '= F3 &' '& G3' op in een andere cel in die rij, zoals E3. Als u alleen het jaar in de identifier wilt opnemen (omdat de ene lijst de volledige datum gebruikt en de andere alleen jaren), neemt u de YEAR-functie op door in plaats daarvan '= F3 & "" & YEAR (G3) "in cel E3 in te voeren. (Neem geen enkele aanhalingstekens op, ze zijn er alleen om het voorbeeld aan te geven.)
    • Nadat u de formule hebt gemaakt, kunt u deze in 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 naartoe wilt kopiëren. Wanneer u uw muisknop loslaat, wordt elke cel die u hebt gesleept, gevuld met de formule, waarbij de celverwijzingen zijn aangepast aan de juiste cellen in dezelfde rij.
  3. 3 Standaardiseer data waar mogelijk. Terwijl de geest herkent dat "Inc." en "Incorporated" betekent hetzelfde, Excel doet dat niet, tenzij u het een of ander woord opnieuw hebt geformatteerd. Evenzo kunt u waarden als $ 11,950 en $ 11,999,95 zo dicht bij elkaar plaatsen dat ze overeenkomen, maar Excel doet dit alleen als u dit zegt.
    • U kunt af en toe een aantal afkortingen gebruiken, zoals "Co" voor "Bedrijf" en "Inzet" voor "Opgenomen met de LINKER tekenreeksfunctie om de extra tekens af te kappen. Andere afkortingen, zoals" Assn "voor" Associatie ", kunnen het beste zijn behandeld door een handleiding voor het invoeren van gegevensinvoer op te stellen en vervolgens een programma te schrijven om onjuiste indelingen op te zoeken en te corrigeren.
    • Voor cijferreeksen, zoals ZIP-codes waarbij sommige vermeldingen het ZIP + 4-achtervoegsel bevatten en andere niet, kunt u opnieuw de LEFT-reeksfunctie gebruiken om alleen de primaire postcodes te herkennen en aan te passen. Om Excel numerieke waarden te laten herkennen die dichtbij maar niet hetzelfde zijn, kunt u de functie ROUND gebruiken om close-waarden af ​​te ronden naar hetzelfde nummer en ze te evenaren.
    • Extra spaties, zoals het typen van twee spaties tussen woorden in plaats van één, kan worden verwijderd met behulp van de TRIM-functie.
  4. 4 Maak kolommen voor de vergelijkingsformule. Net zoals u kolommen voor de lijst-ID's moest maken, moet u kolommen maken voor de formule die de vergelijking voor u doet. Voor elke lijst heeft u één kolom nodig.
    • U wilt deze kolommen labelen met zoiets als 'Ontbreekt?'
  5. 5 Voer de vergelijkingsformule in elke cel in. Voor de vergelijkingsformule gebruikt u de MATCH-functie 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 ID-kolom van de eerste lijst wordt vergeleken met elk van de ID's in de tweede lijst met kijk of het overeenkomt met een van hen. Als het niet overeenkomt, ontbreekt een record en wordt het woord "TRUE" weergegeven in die cel. Als het overeenkomt, is de record aanwezig en wordt het woord "FALSE" weergegeven. (Vermeld bij het invoeren van de formule de insluitende aanhalingstekens niet.)
    • U kunt de formule in de resterende cellen van de kolom kopiëren op dezelfde manier als waarop u de formule voor celidentificatie hebt gekopieerd. In dit geval wordt alleen de celverwijzing voor de ID-cel gewijzigd, omdat het plaatsen van de dollartekens vóór de rij en kolomverwijzingen voor de eerste en laatste cellen in de lijst van de tweede cel-ID's hen absolute verwijzingen maken.
    • 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 naar de eerste identificatiecel van de tweede lijst en "$ L $ 3: $ L $ 14" wordt vervangen door de eerste en laatste identificatiecel van de tweede lijst.(Laat alleen de dollartekens en de dubbele punt achter.) U kunt deze bewerkte formule vervolgens kopiëren naar de resterende cellen in de vergelijkingsrij van de tweede lijst.
  6. 6 Sorteer de lijsten om niet-overeenkomende waarden gemakkelijker te bekijken, indien nodig. Als uw lijsten groot zijn, moet u ze mogelijk sorteren om alle niet-overeenkomende waarden bij elkaar te voegen. De instructies in de onderstaande substappen zetten de formules om in waarden om hercalculatiefouten te voorkomen. Als uw lijsten groot zijn, wordt een lange herberekeningstijd voorkomen.
    • Sleep met je muis over alle cellen in een lijst om deze te selecteren.
    • Selecteer Kopiëren uit het menu Bewerken in Excel 2003 of uit de groep Klembord van het Home-lint in Excel 2007 of 2010.
    • Selecteer Plakken speciaal in het menu Bewerken in Excel 2003 of klik op de vervolgkeuzeknop Plakken in de groep Klembord van het lint Home 2007 van Excel 2007 of 2010.
    • 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 "Kopregel" in de lijst "Mijn gegevensbereik heeft" in het dialoogvenster Sorteren op, selecteer "Ontbrekend?" (of de naam die u feitelijk de kolomkop van de vergelijking hebt gegeven) en klik op OK.
    • Herhaal deze stappen voor de andere lijst.
  7. 7 Vergelijk de niet-overeenkomende items visueel om te zien waarom ze niet overeenkomen. Zoals eerder vermeld, is Excel ontworpen om te zoeken naar exacte gegevensovereenkomsten, tenzij u dit instelt om naar geschatte gegevens 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 het controleren of in de eerste plaats vermelde activa moeten worden gerapporteerd.

Methode twee van twee:
Voorwaardelijke opmaak met AANTAL.ALS

  1. 1 Kopieer de gegevenslijsten naar een enkel werkblad.
  2. 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 uniek voor die lijst markeren; dat wil zeggen, records die niet overeenkomen met records in de andere lijst. Als u records in beide lijsten wilt markeren, moet u records markeren die wel bij elkaar passen. Voor de doeleinden van dit voorbeeld nemen we aan dat de eerste lijst cellen G3 tot en met G14 omvat en de tweede lijst cellen L3 tot en met L14.
  3. 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) toepassen op elke lijst.
  4. 4 Pas de juiste vergelijkingsformule toe. Hiervoor moet u het dialoogvenster Voorwaardelijke opmaak in uw versie van Excel openen. 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 klikt in de groep Stijlen van het Home-lint. Selecteer het regeltype als "Formule" en voer uw formule in in het veld Bewerk de regelbeschrijving.
    • 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 een relatieve waarde. (Voer de korte aanhalingstekens niet 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 referentie naar de eerste cel van de tweede lijst als een relatieve waarde. (Voer de korte aanhalingstekens niet in.)
    • Als u de records in elke lijst in de andere lijst wilt markeren, hebt u twee formules nodig, een voor de eerste en een 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 is ($ G $ 3: $ G $ 14, L3> 0) ". Zoals eerder vermeld, 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 markeren van de records die worden gemarkeerd. Klik op OK om het dialoogvenster te sluiten.