Hvordan sammenligne to lister i Excel

Excel er et effektivt databehandlingsprogram. Og en av metodene for informasjonsanalyse er sammenligning av to lister. Hvis du sammenligner to lister riktig i Excel, vil det være veldig enkelt å organisere denne prosessen. Det er nok bare å følge noen av punktene som skal diskuteres i dag. Den praktiske implementeringen av denne metoden avhenger helt av behovene til personen eller organisasjonen på et bestemt tidspunkt. Derfor bør flere mulige tilfeller vurderes.

Sammenligning av to lister i Excel

Selvfølgelig kan du sammenligne to lister manuelt. Men det vil ta lang tid. Excel har sitt eget intelligente verktøysett som lar deg sammenligne data ikke bare raskt, men også for å få informasjon som ikke er så lett å få med øynene. Anta at vi har to kolonner med koordinatene A og B. Noen verdier gjentas i dem.

Formulering av problemet

Så vi må sammenligne disse kolonnene. Prosedyren for å sammenligne to dokumenter er som følger:

1. Hvis de unike cellene i hver av disse listene er de samme, og det totale antallet unike celler er det samme, og cellene er de samme, kan disse listene betraktes som de samme. Rekkefølgen som verdiene i denne listen er stablet i spiller ikke så stor rolle.
2. Vi kan snakke om delvis sammenfall av lister hvis de unike verdiene i seg selv er de samme, men antallet repetisjoner er forskjellig. Derfor kan slike lister inneholde et annet antall elementer.
3. At de to listene ikke samsvarer, indikeres av et annet sett med unike verdier.

Alle disse tre tilstandene er samtidig betingelsene for vårt problem.

Løsningen på problemet

La oss generere to dynamiske områder for å gjøre det enklere å sammenligne lister. Hver av dem vil tilsvare hver av listene.

For å sammenligne to lister, gjør følgende:

1. I en egen kolonne lager vi en liste over unike verdier som er spesifikke for begge listene. For dette bruker vi formelen: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Selve formelen må skrives som en matriseformel.
2. La oss bestemme hvor mange ganger hver unike verdi forekommer i datamatrisen. Her er formlene for å gjøre dette: =ANTALLHVIS(Liste1,D5) og =ANTELLI(Liste2,D5).
3. Hvis både antall repetisjoner og antall unike verdier er det samme i alle lister som er inkludert i disse områdene, returnerer funksjonen verdien 0. Dette indikerer at samsvaret er XNUMX%. I dette tilfellet vil overskriftene til disse listene få en grønn bakgrunn.
4. Hvis alt unikt innhold er i begge listene, returneres det av formler =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) verdien vil være null. Hvis E1 ikke inneholder null, men en slik verdi er inneholdt i cellene E2 og F2, vil i dette tilfellet områdene bli gjenkjent som samsvarende, men bare delvis. I dette tilfellet vil overskriftene til de tilsvarende listene bli oransje.
5. Og hvis en av formlene beskrevet ovenfor returnerer en verdi som ikke er null, vil listene være fullstendig ikke-samsvarende.

Dette er svaret på spørsmålet om hvordan man analyserer kolonner for treff ved hjelp av formler. Som du kan se, med bruk av funksjoner, kan du implementere nesten alle oppgaver som ved første øyekast ikke er relatert til matematikk.

Eksempel testing

I vår versjon av tabellen er det tre typer lister av hver type beskrevet ovenfor. Den har delvis og fullstendig matchende, så vel som ikke-matchende.

For å sammenligne data bruker vi området A5:B19, der vi vekselvis setter inn disse listeparene. Om hva som blir resultatet av sammenligningen, vil vi forstå av fargen på de originale listene. Hvis de er helt forskjellige, vil det være en rød bakgrunn. Hvis deler av dataene er de samme, så gule. Ved fullstendig identitet vil de tilsvarende overskriftene være grønne. Hvordan lage en farge avhengig av hva resultatet er? Dette krever betinget formatering.

Finne forskjeller i to lister på to måter

La oss beskrive ytterligere to metoder for å finne forskjeller, avhengig av om listene er synkrone eller ikke.

Alternativ 1. Synkrone lister

Dette er et enkelt alternativ. Anta at vi har slike lister.

For å bestemme hvor mange ganger verdiene ikke konvergerte, kan du bruke formelen: =SUMPRODUKT(—(A2:A20<>B2:B20)). Hvis vi fikk 0 som resultat, betyr dette at de to listene er like.

Alternativ 2: Blandede lister

Hvis listene ikke er identiske i rekkefølgen til objektene de inneholder, må du bruke en funksjon som betinget formatering og fargelegge dupliserte verdier. Eller bruk funksjonen ANTALL.HVIS, hvor vi bestemmer hvor mange ganger et element fra en liste forekommer i den andre.

Hvordan sammenligne to kolonner rad for rad

Når vi sammenligner to kolonner, må vi ofte sammenligne informasjon som er i forskjellige rader. For å gjøre dette vil operatøren hjelpe oss HVIS. La oss ta en titt på hvordan det fungerer i praksis. For å gjøre dette presenterer vi flere illustrative situasjoner.

Eksempel. Hvordan sammenligne 2 kolonner for treff og forskjeller i én rad

For å analysere om verdiene som er i samme rad, men forskjellige kolonner er de samme, skriver vi funksjonen IF. Formelen settes inn i hver rad plassert i hjelpekolonnen der resultatene av databehandlingen vil vises. Men det er slett ikke nødvendig å foreskrive det i hver rad, bare kopier det inn i de resterende cellene i denne kolonnen eller bruk autofullføringsmarkøren.

Vi bør skrive ned en slik formel for å forstå om verdiene i begge kolonnene er like eller ikke: =HVIS(A2=B2, "Samsvar", ""). Logikken til denne funksjonen er veldig enkel: den sammenligner verdiene i cellene A2 og B2, og hvis de er like, viser den verdien "Sanner sammen". Hvis dataene er forskjellige, returnerer de ingen verdi. Du kan også sjekke cellene for å se om det er samsvar mellom dem. I dette tilfellet er formelen som brukes: =HVIS(A2<>B2, “Samsvarer ikke”, “”). Prinsippet er det samme, først utføres kontrollen. Hvis det viser seg at cellene oppfyller kriteriet, vises verdien "Stemmer ikke".

Det er også mulig å bruke følgende formel i formelfeltet for å vise både "Match" hvis verdiene er like, og "Ikke samsvarer" hvis de er forskjellige: =HVIS(A2=B2; "Samsvar", "Samsvarer ikke"). Du kan også bruke ulikhetsoperatoren i stedet for likhetsoperatoren. Bare rekkefølgen på verdiene som vises i dette tilfellet vil være litt annerledes: =HVIS(A2<>B2, "Sammen ikke", "Sammenfaller"). Etter å ha brukt den første versjonen av formelen, vil resultatet bli som følger.

Denne varianten av formelen skiller ikke mellom store og små bokstaver. Derfor, hvis verdiene i en kolonne skiller seg fra andre bare ved at de er skrevet med store bokstaver, vil ikke programmet merke denne forskjellen. For å gjøre sammenligningen skille mellom store og små bokstaver, må du bruke funksjonen i kriteriene NØYAKTIG. Resten av argumentene forblir uendret: =HVIS(EXAKT(A2;B2); “Samsvar”, “Unik”).

Hvordan sammenligne flere kolonner for treff på én rad

Det er mulig å analysere verdiene i listene i henhold til et helt sett med kriterier:

1. Finn de radene som har de samme verdiene overalt.
2. Finn de radene der det er treff på bare to lister.

La oss se på noen få eksempler på hvordan du går frem i hver av disse sakene.

Eksempel. Hvordan finne treff i én rad i flere kolonner i en tabell

Anta at vi har en rekke kolonner som inneholder informasjonen vi trenger. Vi står overfor oppgaven med å bestemme de radene der verdiene er de samme. For å gjøre dette, må du bruke følgende formel: =HVIS(OG(A2=B2,A2=C2), "match", " ").

Hvis det er for mange kolonner i tabellen, trenger du bare å bruke den sammen med funksjonen IF operatør ANTALL.HVIS: =HVIS(ANTALLHVIS($A2:$C2,$A2)=3;"match";" "). Tallet som brukes i denne formelen indikerer antall kolonner som skal kontrolleres. Hvis det er forskjellig, må du skrive så mye som er sant for din situasjon.

Eksempel. Hvordan finne treff i én rad i to kolonner i en tabell

La oss si at vi må sjekke om verdiene i en rad samsvarer i to kolonner fra de i tabellen. For å gjøre dette må du bruke funksjonen som en betingelse OR, hvor vekselvis skriv likheten til hver av kolonnene til den andre. Her er et eksempel.

Vi bruker denne formelen: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Det kan oppstå en situasjon når det er mange kolonner i tabellen. I dette tilfellet vil formelen være enorm, og det kan ta mye tid å velge alle nødvendige kombinasjoner. For å løse dette problemet må du bruke funksjonen ANTALL.HVIS: =HVIS(ANTALLHVIS(B2:D2,A2)+ANTALLHVIS(C2:D2,B2)+(C2=D2)=0; "Unik streng"; "Ikke unik streng")

Vi ser at vi totalt sett har to funksjoner ANTALL.HVIS. Med den første bestemmer vi vekselvis hvor mange kolonner som har likhet med A2, og med den andre kontrollerer vi antall likheter med verdien av B2. Hvis vi, som et resultat av å beregne med denne formelen, får en nullverdi, indikerer dette at alle rader i denne kolonnen er unike, hvis flere, er det likheter. Derfor, hvis vi som et resultat av å beregne med to formler og legge til de endelige resultatene får en nullverdi, returneres tekstverdien "Unik streng", hvis dette tallet er større, skrives det at denne strengen ikke er unik.

Hvordan sammenligne 2 kolonner i Excel for treff

La oss nå ta et eksempel. La oss si at vi har en tabell med to kolonner. Du må sjekke om de stemmer overens. For å gjøre dette må du bruke formelen, hvor funksjonen også skal brukes IF, og operatøren ANTALL.HVIS: =IF(COUNTIF($B:$B,$A5)=0, "Ingen treff i kolonne B", "Det er treff i kolonne B")

Ingen ytterligere handling er nødvendig. Etter å ha beregnet resultatet med denne formelen, får vi if verdien av det tredje argumentet til funksjonen IF fyrstikker. Hvis det ikke er noen, så innholdet i det andre argumentet.

Hvordan sammenligne 2 kolonner i Excel for kamper og markere med farge

For å gjøre det lettere å visuelt identifisere samsvarende kolonner, kan du markere dem med en farge. For å gjøre dette, må du bruke funksjonen "Betinget formatering". La oss se i praksis.

Finne og fremheve treff etter farge i flere kolonner

For å finne treffene og markere dem, må du først velge dataområdet der kontrollen skal utføres, og deretter åpne elementet "Betinget formatering" på fanen "Hjem". Der velger du "Duplicate Values" som cellevalgsregel.

Deretter vises en ny dialogboks, der vi i den venstre popup-listen finner alternativet "Repeating", og i den høyre listen velger vi fargen som skal brukes til utvalget. Etter at vi har klikket på "OK" -knappen, vil bakgrunnen til alle celler med likheter bli valgt. Så er det bare å sammenligne kolonnene etter øyet.

Finne og fremheve samsvarende linjer

Teknikken for å sjekke om strenger passer er litt annerledes. Først må vi lage en ekstra kolonne, og der vil vi bruke de kombinerte verdiene ved å bruke &-operatoren. For å gjøre dette, må du skrive en formel av skjemaet: =A2&B2&C2&D2.

Vi velger kolonnen som ble opprettet og inneholder de kombinerte verdiene. Deretter utfører vi samme sekvens av handlinger som er beskrevet ovenfor for kolonnene. Dupliserte linjer vil bli uthevet i fargen du angir.

Vi ser at det ikke er noe vanskelig å lete etter repetisjoner. Excel inneholder alle nødvendige verktøy for dette. Det er viktig å bare øve seg før man setter all denne kunnskapen ut i praksis.