Sådan sammenlignes to kolonner (ved hjælp af VLOOKUP & IF)

når du arbejder med data, skal du før eller senere sammenligne data. Dette kan sammenligne to kolonner eller endda data i forskellige ark/arbejdsbøger.

i denne tutorial vil jeg vise dig forskellige metoder til at sammenligne to kolonner og se efter kampe eller forskelle.

der er flere måder at gøre dette på, og i denne tutorial vil jeg vise dig nogle af disse (f.eks.

så lad os komme i gang!

Sammenlign to kolonner (Side om side)

dette er den mest grundlæggende type sammenligning, hvor du skal sammenligne en celle i en kolonne med cellen i samme række i en anden kolonne.

Antag, at du har et datasæt som vist nedenfor, og du vil blot kontrollere, om værdien i kolonne A i en bestemt celle er den samme (eller anderledes) sammenlignet med værdien i den tilstødende celle.

selvfølgelig kan du gøre dette, når du har et lille datasæt, når du har et stort, du kan bruge en simpel sammenligningsformel for at få dette gjort. Og husk, der er altid en chance for menneskelig fejl, når du gør dette manuelt.

så lad mig vise dig et par nemme måder at gøre dette på.

Sammenlign side om side ved hjælp af operatøren lig med tegn

Antag, at du har nedenstående datasæt, og du vil vide, hvilke rækker der har de matchende data, og hvilke rækker der har forskellige data.

nedenfor er en simpel formel til sammenligning af to kolonner (side om side):

=A2=B2

ovenstående formel giver dig en sand, hvis begge værdier er de samme og falske, hvis de ikke er det.

nu, hvis du har brug for at kende alle de værdier, der matcher, skal du blot anvende et filter og kun vise alle de sande værdier. Og hvis du vil vide alle de værdier, der er forskellige, skal du filtrere alle de værdier, der er falske (som vist nedenfor):

Sammenlign Side om side ved hjælp af funktionen IF

en anden metode, som du kan bruge til at sammenligne to kolonner, kan være ved hjælp af funktionen IF.

dette svarer til metoden ovenfor, hvor vi brugte lig med (=) operatør, med en ekstra fordel. Når du bruger IF-funktionen, kan du vælge den værdi, du vil have, når der er kampe eller forskelle.

hvis der for eksempel er et match, kan du få teksten “Match” eller kan få en værdi som 1. Tilsvarende, når der er en uoverensstemmelse, kan du programmere formlen til at give dig teksten “Mismatch” eller give dig en 0 eller tom celle.

nedenfor er if-formlen, der returnerer ‘Match’, når de to celler har celleværdien, og ‘ikke et Match’, når værdien er anderledes.

=IF(A2=B2,"Match","Not a Match")

ovenstående formel bruger den samme betingelse til at kontrollere, om de to celler (i samme række) har matchende data eller ej (a2=B2). Men da vi bruger IF-funktionen, kan vi bede den om at returnere en bestemt tekst, hvis betingelsen er sand eller falsk.

når du har formelresultaterne i en separat kolonne, kan du hurtigt filtrere dataene og få rækker, der har de matchende data eller rækker med uoverensstemmende data.

Fremhæv rækker med matchende Data (eller forskellige Data)

en anden god måde at hurtigt kontrollere de rækker, der har matchende data (eller har forskellige data), er at fremhæve disse rækker ved hjælp af betinget formatering.

du kan gøre begge dele – Fremhæv rækker, der har samme værdi i en række, såvel som tilfældet, når værdien er anderledes.

Antag, at du har et datasæt som vist nedenfor, og du vil fremhæve alle de rækker, hvor navnet er det samme.

nedenfor er trinnene til at bruge betinget formatering til at fremhæve rækker med matchende data:

1. Vælg hele datasættet (undtagen overskrifterne)
2. Klik på fanen Hjem
3. i gruppen stilarter skal du klikke på Betinget formatering
4. i de indstillinger, der vises, skal du klikke på ‘Ny regel’
5. i dialogboksen’ Ny formateringsregel ‘skal du klikke på indstillingen -” Brug en formel til at bestemme, hvilke celler der skal formateres’
6. i feltet’ Formatværdier, hvor denne formel er sand’, skal du indtaste formlen: =$A2=$B2
7. Klik på Formatknappen
8. Klik på fanen ‘fyld’ og vælg den farve, som du vil fremhæve rækkerne med samme værdi i begge kolonner
9. Klik på OK

ovenstående trin vil øjeblikkeligt fremhæve rækkerne, hvor navnet er den samme værdi i begge kolonner samme i begge kolonner A og B (i samme række). Og i tilfælde af at navnet er anderledes, vil disse rækker ikke blive fremhævet.

hvis du vil sammenligne to kolonner og fremhæve rækker, hvor navnene er forskellige, skal du bruge nedenstående formel i dialogboksen betinget formatering (i trin 6).

=$A2<>$B2

Hvordan virker dette?

når vi bruger betinget formatering med en formel, fremhæver den kun de celler, hvor formlen er sand.

når vi bruger $a2=$B2, kontrollerer den hver celle (i begge kolonner) og ser, om værdien i en række i kolonne A er lig med den i kolonne B eller ej.

hvis det er et nøjagtigt match, vil det fremhæve det i den angivne farve, og hvis det ikke stemmer overens, vil det ikke.

det bedste ved betinget formatering er, at det ikke kræver, at du bruger en formel i en separat kolonne. Når du anvender reglen på et datasæt, forbliver den også dynamisk. Dette betyder, at hvis du ændrer et navn i datasættet, justeres betinget formatering i overensstemmelse hermed.

Sammenlign to kolonner ved hjælp af VLOOKUP (Find matchende/forskellige Data)

i ovenstående eksempler viste jeg dig, hvordan du sammenligner to kolonner (eller lister), når vi bare sammenligner side om side celler.

i virkeligheden vil dette sjældent være tilfældet.

i de fleste tilfælde har du to kolonner med data, og du bliver nødt til at finde ud af, om der findes et datapunkt i den ene kolonne i den anden kolonne eller ej.

i sådanne tilfælde kan du ikke bruge en simpel lig-til at underskrive eller endda en IF-funktion.

du har brug for noget mere kraftfuldt …

… noget der er lige op i VLOOKUP ‘ s alley!

lad mig vise dig to eksempler, hvor vi sammenligner to kolonner ved hjælp af VLOOKUP-funktionen for at finde matches og forskelle.

Sammenlign to kolonner ved hjælp af VLOOKUP og Find Matches

Antag, at vi har et datasæt som vist nedenfor, hvor vi har nogle navne i kolonne A og B.

hvis du skal finde ud af, hvad der er navnene i kolonne B, der også er i kolonne A, kan du bruge nedenstående VLOOKUP-formel:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

ovenstående formel sammenligner de to kolonner (A og B) og giver dig navnet, hvis navnet også er i kolonne B A, og det returnerer “ingen Match”, hvis navnet er i kolonne B og ikke i kolonne A.

som standard returnerer VLOOKUP-funktionen en #N/A-fejl, hvis den ikke finder et nøjagtigt match. Så for at undgå at få fejlen har jeg pakket VLOOKUP-funktionen i IFERROR-funktionen, så den giver “ingen Match”, når navnet ikke er tilgængeligt i kolonne A.

du kan også gøre omvendt sammenligning – for at kontrollere, om navnet er i kolonne A såvel som kolonne B. nedenstående formel ville gøre det:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

Sammenlign to kolonner ved hjælp af VLOOKUP og Find Forskelle (manglende datapunkter)

i ovenstående eksempel kontrollerede vi, om dataene i en kolonne var der i en anden kolonne eller ej.

du kan også bruge det samme koncept til at sammenligne to kolonner ved hjælp af funktionen VLOOKUP og finde manglende data.

Antag, at vi har et datasæt som vist nedenfor, hvor vi har nogle navne i kolonne A og B.

hvis du skal finde ud af, hvad der er navnene i kolonne B, der ikke er der i kolonne A, kan du bruge nedenstående VLOOKUP-formel:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

ovenstående formel kontrollerer navnet i kolonne B mod alle navnene i kolonne A. Hvis det finder et nøjagtigt match, returnerer det det navn, og hvis det ikke finder og nøjagtigt matcher, returnerer det #N/A-fejlen.

da jeg er interesseret i at finde de manglende Navne, der er der er kolonne B og ikke i kolonne A, skal jeg kende de Navne, der returnerer #N/A-fejlen.

derfor har jeg pakket VLOOKUP-funktionen i IF og ISERROR-funktionerne. Hele denne formel giver værdien- “ikke tilgængelig”, når navnet mangler i kolonne A, og” tilgængelig”, når det er til stede.

for at kende alle de Navne, der mangler, kan du filtrere resultatkolonnen baseret på værdien “ikke tilgængelig”.

du kan også bruge nedenstående MATCH-funktion til at få det samme resultat:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

almindelige forespørgsler ved sammenligning af to kolonner

nedenfor er nogle almindelige forespørgsler, jeg normalt får, når folk forsøger at sammenligne data i to kolonner.

1.kvartal. Hvordan sammenlignes flere kolonner i samme række for kampe? Tæl de samlede dubletter også.

Ans. Vi har givet proceduren til at sammenligne to kolonner for den samme række ovenfor. Men hvis du vil sammenligne flere kolonner i samme række, så se eksemplet

=IF(AND(A2=B2, A2=C2),"Full Match", "")

her har vi sammenlignet data fra kolonne A, kolonne B og kolonne C. Efter dette har jeg anvendt ovenstående formel i kolonne D og få resultatet.

nu for at tælle duplikaterne skal du bruge funktionen Countif.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

2. kvartal. Hvilken operatør bruger du til kampe og forskelle?

Ans. Nedenfor er operatørerne at bruge:

• for at finde matches skal du bruge lig med tegn (=)
• for at finde forskelle (uoverensstemmelser) skal du bruge ikke-lig med tegn (<>)

3. Hvordan sammenligner man to forskellige tabeller og trækker matchende data?

Ans. Til dette kan du bruge VLOOKUP-funktionen eller indekset & MATCH-funktionen. For at forstå denne ting på en bedre måde vil vi tage et eksempel.

her vil vi tage to tabeller og nu ønsker at gøre pull matchende data. I den første tabel har du et datasæt, og i den anden tabel skal du tage listen over frugter og derefter bruge træk matchende data i en anden kolonne. For pull matching, bruge formlen

=INDEX($B:$B,MATCH($D2,$A:$A,0))

4. Sådan fjerner du dubletter?

Ans. For at fjerne duplikatdata skal du først finde duplikatværdierne.

for at finde duplikatet kan du bruge forskellige metoder som betinget formatering, Vlookup, if-sætning og mange flere. Der er også et indbygget værktøj, hvor du bare kan vælge dataene og fjerne duplikaterne fra en kolonne eller endda flere kolonner

5.kvartal. Jeg kan se, at der er en matchende værdi i begge kolonner. De formler, du har delt ovenfor, betragter dog ikke disse som nøjagtige matches. Hvorfor?

Ans: vi betragter noget som et nøjagtigt match, når hver enkelt karakter i en celle er lig med den anden. Der er stor chance for, at der i dit datasæt er førende eller efterfølgende mellemrum.

selvom disse mellemrum stadig kan få værdierne til at virke lig med et blotte øje, er disse forskellige. Hvis du har et sådant datasæt, er det bedst at slippe af med disse mellemrum (du kan bruge f.eks.

7.kvartal. Sådan sammenlignes to kolonner, der giver resultatet som sandt, når alle første kolonners heltalsværdier ikke er mindre end den anden kolonnes heltalsværdier. For at løse dette problem kræver jeg ikke betinget formatering, VLOOKUP-funktion, if-sætning og andre formler. Jeg har brug for formlen til at løse dette problem.

Ans. Du kan bruge array-formlen til at løse dette problem.

syntaksen er {=AND (H6:H12>I6:I12)}. Dette giver dig ” sand “som et resultat, når værdien af kolonne H er større end værdien i kolonne i ellers” falsk ” vil være resultatet.

du vil måske også synes om følgende vejledninger:

• Sammenlign to kolonner i udmærke (for matches og forskelle)
• sådan skjules kolonner baseret på celleværdi i udmærke
• Sådan opdeles en kolonne i flere kolonner i udmærke
• Sådan vælges alternative kolonner i udmærke (eller hver niende kolonne)
• Sådan indsættes i en filtreret kolonne, der springer over de skjulte celler
• bedste bøger (det vil gøre dig til en professionel i 2020)

rapporter denne annonce