Hoe twee lijsten in Excel te vergelijken

Excel is een efficiënt gegevensverwerkingsprogramma. En een van de methoden voor informatie-analyse is de vergelijking van twee lijsten. Als u twee lijsten in Excel correct vergelijkt, zal het organiseren van dit proces heel eenvoudig zijn. Het volstaat om enkele van de punten te volgen die vandaag zullen worden besproken. De praktische invulling van deze methode is geheel afhankelijk van de behoefte van de persoon of organisatie op een bepaald moment. Daarom moeten verschillende mogelijke gevallen worden overwogen.

Twee lijsten vergelijken in Excel

Natuurlijk kunt u twee lijsten handmatig vergelijken. Maar het zal lang duren. Excel heeft zijn eigen intelligente toolkit waarmee u niet alleen snel gegevens kunt vergelijken, maar ook informatie kunt krijgen die niet zo gemakkelijk met uw ogen te verkrijgen is. Stel we hebben twee kolommen met coördinaten A en B. Sommige waarden worden daarin herhaald.

Formulering van het probleem

We moeten deze kolommen dus vergelijken. De procedure voor het vergelijken van twee documenten is als volgt:

1. Als de unieke cellen van elk van deze lijsten hetzelfde zijn, en het totale aantal unieke cellen is hetzelfde, en de cellen zijn hetzelfde, dan kunnen deze lijsten als hetzelfde worden beschouwd. De volgorde waarin de waarden in deze lijst zijn gestapeld maakt niet zo veel uit.
2. We kunnen spreken van gedeeltelijk samenvallen van lijsten als de unieke waarden zelf hetzelfde zijn, maar het aantal herhalingen is anders. Dergelijke lijsten kunnen daarom een ​​ander aantal elementen bevatten.
3. Het feit dat de twee lijsten niet overeenkomen, wordt aangegeven door een andere set unieke waarden.

Al deze drie voorwaarden tegelijk zijn de voorwaarden van ons probleem.

De oplossing van het probleem

Laten we twee dynamische bereiken genereren om het vergelijken van lijsten gemakkelijker te maken. Elk van hen komt overeen met elk van de lijsten.

Ga als volgt te werk om twee lijsten te vergelijken:

1. In een aparte kolom maken we een lijst met unieke waarden die specifiek zijn voor beide lijsten. Hiervoor gebruiken we de formule: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). De formule zelf moet worden geschreven als een matrixformule.
2. Laten we bepalen hoe vaak elke unieke waarde voorkomt in de gegevensarray. Hier zijn de formules om dit te doen: =AANTAL.ALS(Lijst1,D5) en =AANTAL.I(Lijst2,D5).
3. Als zowel het aantal herhalingen als het aantal unieke waarden hetzelfde zijn in alle lijsten die in deze bereiken zijn opgenomen, dan retourneert de functie de waarde 0. Dit geeft aan dat de overeenkomst XNUMX% is. In dit geval krijgen de kopjes van deze lijsten een groene achtergrond.
4. Als alle unieke inhoud in beide lijsten staat, wordt deze geretourneerd door formules =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) de waarde zal nul zijn. Als E1 geen nul bevat, maar een dergelijke waarde staat in de cellen E2 en F2, dan worden de bereiken in dit geval herkend als overeenkomend, maar slechts gedeeltelijk. In dit geval worden de kopjes van de bijbehorende lijsten oranje.
5. En als een van de hierboven beschreven formules een waarde niet-nul retourneert, komen de lijsten volledig niet overeen.

Dit is het antwoord op de vraag hoe kolommen te analyseren op overeenkomsten met behulp van formules. Zoals u kunt zien, kunt u met het gebruik van functies bijna elke taak uitvoeren die op het eerste gezicht niets met wiskunde te maken heeft.

Voorbeeld testen

In onze versie van de tabel zijn er drie soorten lijsten van elk hierboven beschreven type. Het heeft gedeeltelijk en volledig passend, evenals niet-overeenkomend.

Om gegevens te vergelijken, gebruiken we het bereik A5:B19, waarin we afwisselend deze paren lijsten invoegen. Over wat het resultaat van de vergelijking zal zijn, zullen we begrijpen door de kleur van de originele lijsten. Als ze totaal anders zijn, is het een rode achtergrond. Als een deel van de gegevens hetzelfde is, dan geel. Bij volledige identiteit zijn de bijbehorende kopjes groen. Hoe maak je een kleur afhankelijk van het resultaat? Dit vereist voorwaardelijke opmaak.

Verschillen in twee lijsten op twee manieren vinden

Laten we nog twee methoden beschrijven om verschillen te vinden, afhankelijk van of de lijsten synchroon zijn of niet.

Optie 1. Synchrone lijsten

Dit is een gemakkelijke optie. Stel dat we zulke lijsten hebben.

Om te bepalen hoe vaak de waarden niet convergeerden, kunt u de formule gebruiken: =SOMPRODUCT(—(A2:A20<>B2:B20)). Als we 0 als resultaat hebben, betekent dit dat de twee lijsten hetzelfde zijn.

Optie 2: Geshufflede lijsten

Als de lijsten niet identiek zijn in de volgorde van de objecten die ze bevatten, moet u een functie toepassen zoals voorwaardelijke opmaak en dubbele waarden inkleuren. Of gebruik de functie COUNTIF, waarmee we bepalen hoe vaak een element uit de ene lijst voorkomt in de tweede.

Hoe 2 kolommen rij voor rij te vergelijken?

Wanneer we twee kolommen vergelijken, moeten we vaak informatie vergelijken die in verschillende rijen staat. Om dit te doen, zal de operator ons helpen ALS. Laten we eens kijken hoe het in de praktijk werkt. Hiervoor presenteren we een aantal illustratieve situaties.

Voorbeeld. Hoe 2 kolommen voor overeenkomsten en verschillen in één rij te vergelijken?

Om te analyseren of de waarden die in dezelfde rij maar in verschillende kolommen staan ​​hetzelfde zijn, schrijven we de functie IF. De formule wordt ingevoegd in elke rij in de hulpkolom waar de resultaten van de gegevensverwerking worden weergegeven. Maar het is helemaal niet nodig om het in elke rij voor te schrijven, kopieer het gewoon naar de resterende cellen van deze kolom of gebruik de autocomplete-markering.

We moeten een dergelijke formule opschrijven om te begrijpen of de waarden in beide kolommen hetzelfde zijn of niet: =ALS(A2=B2, "Overeenkomst", ""). De logica van deze functie is heel eenvoudig: het vergelijkt de waarden in de cellen A2 en B2, en als ze hetzelfde zijn, geeft het de waarde "Coincide" weer. Als de gegevens anders zijn, wordt er geen waarde geretourneerd. U kunt ook de cellen controleren om te zien of er een overeenkomst tussen is. In dit geval is de gebruikte formule: =ALS(A2<>B2, "Komt niet overeen", ""). Het principe is hetzelfde, eerst wordt de controle uitgevoerd. Als blijkt dat de cellen aan het criterium voldoen, wordt de waarde "Komt niet overeen" weergegeven.

Het is ook mogelijk om de volgende formule in het formuleveld te gebruiken om zowel "Overeenkomen" weer te geven als de waarden hetzelfde zijn, en "Niet overeenkomen" als ze verschillend zijn: =ALS(A2=B2; “Overeenkomen”, “Niet overeenkomen”). U kunt ook de ongelijkheidsoperator gebruiken in plaats van de gelijkheidsoperator. Alleen de volgorde van de waarden die in dit geval worden weergegeven, zal iets anders zijn: =ALS(A2<>B2, "komt niet overeen", "valt samen"). Na gebruik van de eerste versie van de formule, is het resultaat als volgt.

Deze variatie van de formule is niet hoofdlettergevoelig. Daarom, als de waarden in de ene kolom alleen verschillen van andere omdat ze in hoofdletters zijn geschreven, zal het programma dit verschil niet opmerken. Om de vergelijking hoofdlettergevoelig te maken, moet u de functie in de criteria gebruiken EXACT. De rest van de argumenten blijven ongewijzigd: =ALS(EXACT(A2,B2), "Overeenkomst", "Uniek").

Meerdere kolommen vergelijken voor overeenkomsten in één rij

Het is mogelijk om de waarden in de lijsten te analyseren volgens een hele reeks criteria:

1. Vind die rijen die overal dezelfde waarden hebben.
2. Zoek die rijen met overeenkomsten in slechts twee lijsten.

Laten we eens kijken naar een paar voorbeelden van hoe te werk te gaan in elk van deze gevallen.

Voorbeeld. Matches vinden in één rij in meerdere kolommen van een tabel

Stel dat we een reeks kolommen hebben die de informatie bevatten die we nodig hebben. We staan ​​voor de taak om die rijen te bepalen waarin de waarden hetzelfde zijn. Om dit te doen, moet u de volgende formule gebruiken: =ALS(EN(A2=B2,A2=C2), “overeenkomst”, ” “).

Als de tabel te veel kolommen bevat, hoeft u deze alleen samen met de functie te gebruiken IF operator COUNTIF: =ALS(AANTAL.ALS($A2:$C2,$A2)=3;”overeenkomst”;” “). Het getal dat in deze formule wordt gebruikt, geeft het aantal kolommen aan dat moet worden gecontroleerd. Als het verschilt, moet u zoveel schrijven als voor uw situatie geldt.

Voorbeeld. Matches vinden in één rij in 2 kolommen van een tabel

Laten we zeggen dat we moeten controleren of de waarden in één rij overeenkomen in twee kolommen van die in de tabel. Om dit te doen, moet je de functie als voorwaarde gebruiken OR, waar afwisselend de gelijkheid van elk van de kolommen naar de andere wordt geschreven. Hier is een voorbeeld.

We gebruiken deze formule: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Er kan zich een situatie voordoen waarin de tabel veel kolommen bevat. In dit geval zal de formule enorm zijn en kan het veel tijd kosten om alle benodigde combinaties te selecteren. Om dit probleem op te lossen, moet u de functie gebruiken: COUNTIF: =ALS(AANTAL.ALS(B2:D2,A2)+AANTAL.ALS(C2:D2,B2)+(C2=D2)=0; "Unieke tekenreeks"; "Niet unieke tekenreeks")

We zien dat we in totaal twee functies hebben COUNTIF. Bij de eerste bepalen we afwisselend hoeveel kolommen overeenkomst hebben met A2, en bij de tweede controleren we het aantal overeenkomsten met de waarde van B2. Als we als resultaat van het berekenen met deze formule een nulwaarde krijgen, geeft dit aan dat alle rijen in deze kolom uniek zijn, als er meer zijn, zijn er overeenkomsten. Daarom, als we als resultaat van het berekenen met twee formules en het optellen van de uiteindelijke resultaten een nulwaarde krijgen, wordt de tekstwaarde "Unieke tekenreeks" geretourneerd, als dit aantal groter is, wordt geschreven dat deze tekenreeks niet uniek is.

Hoe 2 kolommen in Excel te vergelijken voor overeenkomsten

Laten we nu een voorbeeld nemen. Laten we zeggen dat we een tabel hebben met twee kolommen. Je moet kijken of ze overeenkomen. Om dit te doen, moet u de formule toepassen, waarbij de functie ook zal worden gebruikt IF, en de operator COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Geen overeenkomsten in kolom B", "Er zijn overeenkomsten in kolom B")

Verdere actie is niet nodig. Nadat we het resultaat met deze formule hebben berekend, krijgen we als de waarde van het derde argument van de functie IF wedstrijden. Als er geen zijn, dan de inhoud van het tweede argument.

Hoe 2 kolommen in Excel te vergelijken voor overeenkomsten en te markeren met kleur

Om het gemakkelijker te maken om overeenkomende kolommen visueel te identificeren, kunt u ze markeren met een kleur. Om dit te doen, moet u de functie "Voorwaardelijke opmaak" gebruiken. Laten we eens kijken in de praktijk.

Matches zoeken en markeren op kleur in meerdere kolommen

Om de overeenkomsten te bepalen en te markeren, moet u eerst het gegevensbereik selecteren waarin de controle wordt uitgevoerd en vervolgens het item "Voorwaardelijke opmaak" openen op het tabblad "Start". Selecteer daar "Duplicate Values" als celselectieregel.

Daarna verschijnt een nieuw dialoogvenster, waarin we in de linker pop-uplijst de optie "Herhalen" vinden en in de rechterlijst de kleur selecteren die voor de selectie zal worden gebruikt. Nadat we op de knop "OK" hebben geklikt, wordt de achtergrond van alle cellen met overeenkomsten geselecteerd. Vergelijk de kolommen dan gewoon op het oog.

Overeenkomende lijnen zoeken en markeren

De techniek om te controleren of strings overeenkomen, is iets anders. Eerst moeten we een extra kolom maken en daar zullen we de gecombineerde waarden gebruiken met de operator &. Om dit te doen, moet u een formule van het formulier schrijven: =A2&B2&C2&D2.

We selecteren de kolom die is gemaakt en de gecombineerde waarden bevat. Vervolgens voeren we dezelfde reeks acties uit als hierboven beschreven voor de kolommen. Dubbele lijnen worden gemarkeerd in de kleur die u opgeeft.

We zien dat het zoeken naar herhalingen niets moeilijks is. Excel bevat hiervoor alle benodigde tools. Het is belangrijk om eerst te oefenen voordat je al deze kennis in de praktijk brengt.