Gegevensvergelijkingen zoeken in Excel

Onder de ontelbare functies van Microsoft Excel, is er een waarmee je twee lijsten met gegevens kunt vergelijken, de overeenkomsten tussen die lijsten kunt identificeren en ook kunt detecteren welke elementen zich alleen in een van de lijsten bevinden. Dit is erg handig voor het vergelijken van financiële gegevens of om te controleren of een specifieke naam in een database staat. U kunt de MATCH-functie gebruiken om overeenkomende en niet-overeenkomende records te identificeren en te markeren, of de functie AANTAL.ALS gebruiken om een voorwaardelijke indeling te maken. De volgende stappen laten zien hoe u deze functies kunt gebruiken om overeenkomsten tussen gegevens te vinden.

Inhoud

Stappen

Methode 1identificeer records via de functie coincidir
Methode 2voorwaardelijke opmaak met behulp van de aantal.als-functie

Tips

stappen

Methode 1
Identificeer records via de functie COINCIDIR

Titel afbeelding Overeenkomstige gegevens in Excel Stap 1

Kopieer de lijst met gegevens naar één spreadsheet. Excel kan met verschillende spreadsheets werken binnen een enkel boek of met meerdere boeken, maar u zult merken dat het gemakkelijker is om de lijsten te vergelijken als u alle informatie in één spreadsheet kopieert.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 2

Geef een uniek ID op voor elk item in de lijst. Als de twee lijsten geen gemeenschappelijke identificatiemethode delen, moet u waarschijnlijk een extra kolom toevoegen in elke lijst met gegevens die dat element in Excel identificeert. Op deze manier kunt u bepalen of dat element van een bepaalde lijst gerelateerd is aan een element uit de andere lijst. De kenmerken van deze ID zijn afhankelijk van het type gegevens dat u gaat vergelijken. U hebt een ID nodig voor elke lijst met kolommen.

Als u financiële gegevens hebt die zijn gekoppeld aan een bepaalde periode, bijvoorbeeld belastinggegevens, kan de ID de beschrijving van het activum, de datum waarop het activum is verworven of beide zijn. In sommige gevallen kunnen de vermeldingen worden geïdentificeerd met een numerieke code. Als u echter niet hetzelfde systeem voor beide lijsten gebruikt, kan deze ID overeenkomsten maken waar deze niet voorkomen of combinaties negeren die echt bestaan.

In sommige gevallen kunt u items uit een lijst nemen en deze combineren met items uit een andere lijst om een ID te maken, bijvoorbeeld de beschrijving van een fysiek activum en het jaar waarin het is gekocht. Om een dergelijke identifier te maken, is het nodig om de data van twee of meer cellen samen te voegen (toevoegen, combineren) met behulp van het symbool ampersand (&). Om de beschrijving van een element in cel F3 te combineren met een datum in cel G3, gescheiden door een spatie, moet u de formule `= F3 invoeren& " "&G3 `. Als u alleen het jaar in de ID wilt opnemen (omdat de ene lijst de volledige datum gebruikt en de andere alleen jaren), moet u de YEAR-functie opnemen door `= F3 in te voeren&" "&YEAR (G3) `in cel E3 (exclusief de enkele aanhalingstekens - in dit geval worden ze alleen gebruikt om een voorbeeld aan te geven).

Nadat u de formule hebt gemaakt, kunt u deze kopiëren naar alle andere cellen in de kolom Identificatie. Hiertoe selecteert u de cel waarin de formule staat en sleept u het kleine opvullingsvak naar de andere cellen in de kolom waarin u de formule wilt kopiëren. Wanneer u de muisknop loslaat, wordt elke cel waarin u het vak hebt gesleept automatisch ingevuld met de formule en worden de celverwijzingen bijgewerkt met de cellen die overeenkomen met die rij.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 3

Standaardiseer de gegevens indien mogelijk. Terwijl de geest dat herkent "Inc." en "Naamloze vennootschap" ze bedoelen hetzelfde, Excel weet het alleen als je een van de twee uitdrukkingen opnieuw formatteert. Evenzo denk je misschien dat de waarden van $ 11.950 en $ 11.999.95 dicht genoeg liggen om te overwegen dat er een overeenkomst is, maar Excel zal het niet zo zien tenzij je het aangeeft.

U kunt voor de afkortingen zorgen, zoals bijvoorbeeld "co" voor "vennootschap" of "org" voor "organisatie" met de LEFT-functie om de overgebleven tekens af te kappen. Maar er zijn andere afkortingen die niet kunnen worden afgekapt, zoals "S.R.L." voor "Naamloze vennootschap". In dat geval moet u een stijl voor gegevensinvoer instellen en een programma gebruiken dat naar de onjuiste indelingen zoekt en deze corrigeert.

In de getallenketens kan de LEFT-functie ook worden toegepast. Als u bijvoorbeeld postcodes met indeling hebt "ZIP + 4" die een achtervoegsel bevatten achter het nummer en eenvoudige postcodes, gebruik links om de overeenkomst te herkennen en zoek de overeenkomst alleen in het eerste deel van de code. Als u Excel in de buurt numerieke waarden wilt laten herkennen die niet gelijk zijn, kunt u de ROUND-functie gebruiken. Met deze functie kunt u ervoor zorgen dat de close-waarden naar hetzelfde getal worden afgerond, zodat er bij het vergelijken van beide waarden sprake is van een samenloop van waarden.

De spaties die over zijn, bijvoorbeeld als er twee spaties zijn tussen woorden in plaats van één, kunnen worden verwijderd via de CUT-functie.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 4

Maak kolommen voor de vergelijkingsformule. Net zoals je kolommen moest maken voor de ID`s van de lijsten, nu zul je kolommen moeten maken voor de formule die de vergelijking zal maken. Voor elke lijst heeft u een kolom nodig.

Label deze kolommen met een titel die zegt "Ontbrekend record?" of iets dergelijks.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 5

Voer de vergelijkingsformule in elke cel in. Om de vergelijkingsformule te maken, moet u de COINCID-functie gebruiken die is genest in een andere Excel-functie met de naam ESNOD.

De formule moet er ongeveer zo uitzien "= ESNOD (MATCH (G3- $ L $ 3: $ L $ 14-FALSE))", waarbij een cel uit de kolom Identifiers van de eerste lijst wordt gehaald en vergeleken met elk van de ID`s van de tweede lijst om te zien of er een overeenkomst met een is. Als er geen overeenkomst is, betekent dit dat de registratie en het woord ontbreken "TRUE" verschijnt in de cel. Als er een overeenkomst is, betekent dit dat de record is en dat het woord zal verschijnen "VALSE" (Voeg de citaten van de extremen niet toe als u de formule gaat schrijven).

U kunt de formule in de resterende cellen van de kolom kopiëren op dezelfde manier als waarop u de formule van de ID`s hebt gekopieerd. In dit geval wordt alleen de celverwijzing voor de ID gewijzigd. De tekens van gewichten vóór de rij en kolomverwijzingen van de eerste en laatste cel van de lijst met identificatoren in de tweede cel worden gebruikt om de verwijzingen in "absolute referenties".

U kunt de vergelijkingsformule van de eerste lijst kopiëren in de eerste cel van de kolom van de tweede lijst. In dat geval zult u de referenties van het vervangen van cellen moeten bewerken "G3" door de verwijzing van de eerste identificatorencel van de tweede lijst en "$ L $ 3: $ L $ 14" door de eerste en laatste cel van identifiers van de tweede lijst (laat de tekens van gewichten en de twee punten zoals ze zijn). Nu kunt u deze bewerkte formule kopiëren naar de rest van de cellen in de vergelijkingskolom van de tweede lijst.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 6

Sorteer de lijsten zodat het gemakkelijker is om de gegevens te zien die niet overeenkomen. Als de lijsten groot zijn, is het beter om ze te bestellen door alle gegevens samen te voegen die niet overeenkomen. De instructies voor de hieronder beschreven subactiviteiten dienen om formules om te zetten in waarden en zo fouten te voorkomen bij het herberekenen. Als de lijsten lang zijn, zal dit ook de herberekeningstijd verkorten.

Sleep de muis over alle cellen in de lijst om ze te selecteren.

kiezen "exemplaar" in het menu "editie" (Excel 2003) of in de groep "klembord" van het lint (Excel 2007 of 2010).

kiezen "Speciaal vastgelopen" in het menu "editie" (Excel 2003) of in de vervolgkeuzeknop "pasta" van de groep "klembord" (Excel 2007 of 2010) tabblad "inwijding" in het lint.

kiezen "waarden" in de lijst "Plak als" van het dialoogvenster "Speciaal vastgelopen". Klik vervolgens op "aanvaarden" om de doos te sluiten.

kiezen "order" in het menu "gegevens" (Excel 2003) of in de groep "Sorteren en filteren" van het tabblad "gegevens" in het lint (Excel 2007 of 2010).

kiezen "Rij rij" in de lijst "Mijn gegevensbereik heeft" van het dialoogvenster "Sorteer op". kiezen "Ontbrekend record?" (of de naam die u hebt gekozen als de koptekst voor de vergelijkingskolom) en klik op "aanvaarden".

Herhaal deze stappen met de andere lijst.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 7

Vergelijk de niet-overeenkomende items visueel om te zien waarom ze niet overeenkomen. Zoals hierboven uitgelegd, is Excel ontworpen om te zoeken naar exacte overeenkomsten in de gegevens, tenzij u deze instelt op zoek naar benaderingen. de "geen toeval" het kan te wijten zijn aan een eenvoudige omzettingsfout van letters of cijfers. Het kan ook iets zijn dat onafhankelijke verificatie vereist, zoals het controleren van de gegevens om te zien of de activa op de lijst als eerste moeten worden gerapporteerd.

Methode 2
Voorwaardelijke opmaak met behulp van de AANTAL.ALS-functie

Titel afbeelding Overeenkomstige gegevens in Excel Stap 8

Kopieer de lijst met gegevens in een nieuwe spreadsheet.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 9

Bepaal welke lijst u de records wilt markeren die overeenkomen of niet. Als u alleen records in een lijst wilt markeren, is het het beste om de unieke records in die lijst te markeren, dat wil zeggen degene die niet overeenkomen met de records in de andere lijst. Als u records van de twee lijsten wilt markeren, is het beter om die te markeren die wel samenvallen met de andere lijst. Voor de doeleinden van dit voorbeeld wordt aangenomen dat de eerste lijst cellen G3 tot en met G14 inneemt en de tweede cel L3 tot en met L14 inneemt.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 10

Selecteer de items in de lijst waarin u unieke of overeenkomende items wilt markeren. Als u elementen in de twee lijsten wilt markeren, moet u de lijsten één voor één selecteren en de vergelijkingsformule (aangegeven in de volgende stap) in elke lijst toepassen.

Titel afbeelding Overeenkomstige gegevens in Excel Stap 11

Pas de overeenkomstige vergelijkingsformule toe. Om dit te doen, moet u toegang krijgen tot de tabel "Voorwaardelijk formaat" van uw versie van Excel. In Excel 2003, open het geselecteerd "Voorwaardelijk formaat" in het menu "formaat". Klik in Excel 2007 en 2010 op de knop "Voorwaardelijk formaat" van de groep "stijlen" van het tabblad "inwijding" in het lint. kiezen "formule" als een regeltype en voer de formule in het veld in "Bewerk een regelbeschrijving".

Als u de unieke records in de eerste lijst wilt markeren, is de formule dat "= AANTAL.ALS ($ L $ 3: $ L $ 14-G3 = 0)" met het cellenbereik in de tweede lijst uitgedrukt in absolute waarden en de verwijzing naar de eerste cel in de eerste lijst als een relatieve waarde (exclusief de aanhalingstekens in de formule).

Als u de unieke records in de tweede lijst wilt markeren, is de formule dat "= AANTAL.ALS ($ G $ 3: $ G $ 14-L3 = 0)" met het bereik van cellen in de eerste lijst uitgedrukt in absolute waarden en de verwijzing naar de eerste cel in de tweede lijst als een relatieve waarde (geen aanhalingstekens in de formule opnemen).

Als u de records van een lijst in de andere lijst wilt markeren, heeft 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 is "= AANTAL.ALS ($ G $ 3: $ G $ 14-L3>0)". Zoals hierboven uitgelegd, moet u de eerste lijst selecteren om uw formule toe te passen en vervolgens de tweede selecteren om uw formule toe te passen.

Pas het formaat toe waarop u de records wilt markeren die u wilt markeren. Klik op "aanvaarden" om het dialoogvenster te sluiten.

tips

In plaats van een celverwijzing te gebruiken met de AANTAL.ALS-functie van de voorwaardelijke opmaakmethode, kunt u een waarde invoeren. Deze waarde wordt doorzocht in de lijsten en alle instanties die worden gevonden, worden gemarkeerd.
Om de vergelijkingsformules te vereenvoudigen, kunt u bijvoorbeeld een naam voor elke lijst maken "List1" en "list2". Wanneer u vervolgens de formule schrijft, kunnen de namen van de lijsten worden gebruikt om de absolute celbereiken van de vorige voorbeelden te vervangen.

Delen op sociale netwerken:

Verwant