Sådan sammenlignes to lister i Excel

Excel er et effektivt databehandlingsprogram. Og en af ​​metoderne til informationsanalyse er sammenligningen af ​​to lister. Hvis du sammenligner to lister korrekt i Excel, vil det være meget nemt at organisere denne proces. Det er nok bare at følge nogle af de punkter, der vil blive diskuteret i dag. Den praktiske implementering af denne metode afhænger helt af personens eller organisationens behov på et bestemt tidspunkt. Derfor bør flere mulige tilfælde overvejes.

Sammenligning af to lister i Excel

Selvfølgelig kan du sammenligne to lister manuelt. Men det vil tage lang tid. Excel har sit eget intelligente værktøjssæt, der giver dig mulighed for at sammenligne data ikke kun hurtigt, men også for at få information, der ikke er så let at få med dine øjne. Antag, at vi har to kolonner med koordinaterne A og B. Nogle værdier gentages i dem.

Formulering af problemet

Så vi er nødt til at sammenligne disse kolonner. Proceduren for at sammenligne to dokumenter er som følger:

1. Hvis de unikke celler i hver af disse lister er de samme, og det samlede antal unikke celler er det samme, og cellerne er de samme, kan disse lister betragtes som de samme. Den rækkefølge, som værdierne på denne liste er stablet i, betyder ikke så meget.
2. Vi kan tale om delvist sammenfald af lister, hvis de unikke værdier i sig selv er de samme, men antallet af gentagelser er anderledes. Derfor kan sådanne lister indeholde et andet antal elementer.
3. Det faktum, at de to lister ikke stemmer overens, er angivet med et andet sæt unikke værdier.

Alle disse tre forhold er samtidig betingelserne for vores problem.

Løsningen af ​​problemet

Lad os generere to dynamiske områder for at gøre det nemmere at sammenligne lister. Hver af dem vil svare til hver af listerne.

Gør følgende for at sammenligne to lister:

1. I en separat kolonne opretter vi en liste over unikke værdier, der er specifikke for begge lister. Til dette bruger vi formlen: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Selve formlen skal skrives som en matrixformel.
2. Lad os bestemme, hvor mange gange hver unik værdi forekommer i dataarrayet. Her er formlerne for at gøre dette: =COUNTHVIS(Liste1,D5) og =COUNTI(Liste2,D5).
3. Hvis både antallet af gentagelser og antallet af unikke værdier er det samme i alle lister, der er inkluderet i disse områder, returnerer funktionen værdien 0. Dette indikerer, at matchningen er XNUMX%. I dette tilfælde vil overskrifterne på disse lister få en grøn baggrund.
4. Hvis alt unikt indhold er på begge lister, returneres det af formler =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) værdien vil være nul. Hvis E1 ikke indeholder nul, men en sådan værdi er indeholdt i cellerne E2 og F2, vil områderne i dette tilfælde blive genkendt som matchende, men kun delvist. I dette tilfælde bliver overskrifterne på de tilsvarende lister orange.
5. Og hvis en af ​​formlerne beskrevet ovenfor returnerer en værdi, der ikke er nul, vil listerne være fuldstændig ikke-matchende.

Dette er svaret på spørgsmålet om, hvordan man analyserer kolonner for match ved hjælp af formler. Som du kan se, kan du med brug af funktioner implementere næsten enhver opgave, der ved første øjekast ikke er relateret til matematik.

Eksempel Test

I vores version af tabellen er der tre typer lister af hver type beskrevet ovenfor. Den har delvist og fuldstændig matchende, såvel som ikke-matchende.

For at sammenligne data bruger vi området A5:B19, hvori vi skiftevis indsætter disse lister. Om hvad der vil være resultatet af sammenligningen, vil vi forstå af farven på de originale lister. Hvis de er helt forskellige, så vil det være en rød baggrund. Hvis en del af dataene er de samme, så gule. I tilfælde af fuldstændig identitet vil de tilsvarende overskrifter være grønne. Hvordan laver man en farve alt efter hvad resultatet er? Dette kræver betinget formatering.

At finde forskelle i to lister på to måder

Lad os beskrive yderligere to metoder til at finde forskelle, afhængigt af om listerne er synkrone eller ej.

Mulighed 1. Synkrone lister

Dette er en nem mulighed. Antag, at vi har sådanne lister.

For at bestemme, hvor mange gange værdierne ikke konvergerede, kan du bruge formlen: =SUMPRODUKT(—(A2:A20<>B2:B20)). Hvis vi fik 0 som resultat, betyder det, at de to lister er ens.

Mulighed 2: Blandede lister

Hvis listerne ikke er identiske i rækkefølgen af ​​de objekter, de indeholder, skal du anvende en funktion såsom betinget formatering og farvelægge duplikerede værdier. Eller brug funktionen TÆL.HVIS, hvorved vi bestemmer, hvor mange gange et element fra en liste forekommer i den anden.

Sådan sammenligner du 2 kolonner række for række

Når vi sammenligner to kolonner, skal vi ofte sammenligne oplysninger, der er i forskellige rækker. For at gøre dette vil operatøren hjælpe os HVIS. Lad os tage et kig på, hvordan det fungerer i praksis. For at gøre dette præsenterer vi flere illustrative situationer.

Eksempel. Sådan sammenligner du 2 kolonner for kampe og forskelle i en række

For at analysere, om værdierne, der er i samme række, men forskellige kolonner, er de samme, skriver vi funktionen IF. Formlen indsættes i hver række placeret i hjælpekolonnen, hvor resultaterne af databehandlingen vil blive vist. Men det er slet ikke nødvendigt at foreskrive det i hver række, bare kopier det til de resterende celler i denne kolonne eller brug autofuldførelsesmarkøren.

Vi bør skrive en sådan formel ned for at forstå, om værdierne i begge kolonner er de samme eller ej: =HVIS(A2=B2, "Match", ""). Logikken i denne funktion er meget enkel: den sammenligner værdierne i cellerne A2 og B2, og hvis de er ens, viser den værdien "sammenfaldende". Hvis dataene er anderledes, returnerer de ingen værdi. Du kan også tjekke cellerne for at se, om der er et match mellem dem. I dette tilfælde er den anvendte formel: =HVIS(A2<>B2, "Skal ikke matche", ""). Princippet er det samme, først udføres kontrollen. Hvis det viser sig, at cellerne opfylder kriteriet, vises værdien "Stemmer ikke".

Det er også muligt at bruge følgende formel i formelfeltet til at vise både "Match", hvis værdierne er ens, og "Ikke matcher", hvis de er forskellige: =HVIS(A2=B2; "Match", "Match ikke"). Du kan også bruge ulighedsoperatoren i stedet for ligestillingsoperatoren. Kun rækkefølgen af ​​de værdier, der vil blive vist i dette tilfælde, vil være lidt anderledes: =HVIS(A2<>B2, "Skal ikke matche", "sammenfaldende"). Efter at have brugt den første version af formlen, vil resultatet være som følger.

Denne variation af formlen er ufølsom over for store og små bogstaver. Derfor, hvis værdierne i en kolonne kun adskiller sig fra andre ved, at de er skrevet med store bogstaver, vil programmet ikke bemærke denne forskel. For at gøre sammenligningen skelet mellem store og små bogstaver, skal du bruge funktionen i kriterierne EKSAKT. Resten af ​​argumenterne forbliver uændrede: =HVIS(EXAKT(A2,B2), "Match", "Unik").

Sådan sammenligner du flere kolonner for kampe i én række

Det er muligt at analysere værdierne i listerne i henhold til et helt sæt kriterier:

1. Find de rækker, der har de samme værdier overalt.
2. Find de rækker, hvor der er match på kun to lister.

Lad os se på et par eksempler på, hvordan man går videre i hver af disse sager.

Eksempel. Sådan finder du matches i en række i flere kolonner i en tabel

Antag, at vi har en række kolonner, der indeholder den information, vi har brug for. Vi står over for opgaven med at bestemme de rækker, hvor værdierne er de samme. For at gøre dette skal du bruge følgende formel: =HVIS(OG(A2=B2,A2=C2), "match", " ").

Hvis der er for mange kolonner i tabellen, så skal du blot bruge det sammen med funktionen IF operatør TÆL.HVIS: =HVIS(ANTALHVIS($A2:$C2,$A2)=3;"match";" "). Tallet brugt i denne formel angiver antallet af kolonner, der skal kontrolleres. Hvis det afviger, så skal du skrive så meget, som det er sandt for din situation.

Eksempel. Sådan finder du matches i en række i en hvilken som helst 2 kolonner i en tabel

Lad os sige, at vi skal kontrollere, om værdierne i en række matcher i to kolonner fra dem i tabellen. For at gøre dette skal du bruge funktionen som betingelse OR, hvor der skiftevis skrives ligheden af ​​hver af kolonnerne til den anden. Her er et eksempel.

Vi bruger denne formel: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Der kan være en situation, hvor der er mange kolonner i tabellen. I dette tilfælde vil formlen være enorm, og det kan tage meget tid at vælge alle de nødvendige kombinationer. For at løse dette problem skal du bruge funktionen TÆL.HVIS: =HVIS(ANTALHVIS(B2:D2,A2)+TÆL.HVIS(C2:D2,B2)+(C2=D2)=0; "Unik streng"; "Ikke unik streng")

Vi ser, at vi i alt har to funktioner TÆL.HVIS. Med den første bestemmer vi skiftevis, hvor mange kolonner der har en lighed med A2, og med den anden kontrollerer vi antallet af ligheder med værdien af ​​B2. Hvis vi som et resultat af beregning med denne formel får en nulværdi, indikerer dette, at alle rækker i denne kolonne er unikke, hvis flere, er der ligheder. Derfor, hvis vi som et resultat af at beregne med to formler og tilføje de endelige resultater får en nulværdi, returneres tekstværdien "Unik streng", hvis dette tal er større, skrives det, at denne streng ikke er unik.

Sådan sammenligner du 2 kolonner i Excel for kampe

Lad os nu tage et eksempel. Lad os sige, at vi har en tabel med to kolonner. Du skal tjekke om de matcher. For at gøre dette skal du anvende formlen, hvor funktionen også vil blive brugt IF, og operatøren TÆL.HVIS: =HVIS(ANTALHVIS($B:$B,$A5)=0, "Ingen kampe i kolonne B", "Der er kampe i kolonne B")

Der kræves ingen yderligere handling. Efter at have beregnet resultatet med denne formel, får vi hvis værdien af ​​funktionens tredje argument IF Tændstikker. Hvis der ikke er nogen, så indholdet af det andet argument.

Sådan sammenligner du 2 kolonner i Excel for kampe og fremhæver med farve

For at gøre det nemmere visuelt at identificere matchende kolonner, kan du fremhæve dem med en farve. For at gøre dette skal du bruge funktionen "Betinget formatering". Lad os se i praksis.

Finde og fremhæve match efter farve i flere kolonner

For at bestemme matchene og fremhæve dem skal du først vælge det dataområde, som kontrollen skal udføres i, og derefter åbne punktet "Betinget formatering" på fanen "Hjem". Der skal du vælge "Duplicate Values" som celleudvælgelsesregel.

Derefter kommer en ny dialogboks frem, hvor vi i venstre pop-up liste finder muligheden "Gentagelse", og i højre liste vælger vi den farve, der skal bruges til valget. Når vi har klikket på knappen "OK", vil baggrunden for alle celler med ligheder blive valgt. Så skal du bare sammenligne kolonnerne efter øje.

Finde og fremhæve matchende linjer

Teknikken til at kontrollere, om strengene matcher, er lidt anderledes. Først skal vi oprette en ekstra kolonne, og der vil vi bruge de kombinerede værdier ved at bruge &-operatoren. For at gøre dette skal du skrive en formel af formen: =A2&B2&C2&D2.

Vi vælger den kolonne, der blev oprettet, og som indeholder de kombinerede værdier. Dernæst udfører vi den samme rækkefølge af handlinger, som er beskrevet ovenfor for kolonnerne. Duplikerede linjer vil blive fremhævet i den farve, du angiver.

Vi ser, at der ikke er noget svært i at lede efter gentagelser. Excel indeholder alle de nødvendige værktøjer til dette. Det er vigtigt lige at øve sig, inden al denne viden omsættes i praksis.