Sådan sammenligner du to Excel-ark til dubletter: 5 hurtige måder

Når du arbejder med store datasæt i Excel, er det almindeligt at have flere regneark, der indeholder lignende data. Det kan være noget af en udfordring at sammenligne disse ark for duplikerede poster, især når dataene er spredt ud over forskellige kolonner og rækker.

De mest almindelige metoder til at finde dubletter i to Excel-ark er at bruge:

1. VLOOKUP, COUNTIF eller EXACT funktioner

2. Betinget formatering

3. Strømforespørgsel

4. Eksterne værktøjer og tilføjelser

5. Visuel kontrol for dubletter

Denne artikel går trin-for-trin gennem disse fem metoder til at lokalisere og håndtere dubletter på tværs af flere regneark. Til sidst vil du se, hvor nemt det er at administrere duplikerede værdier.

Lad os komme igang!

Indholdsfortegnelse

• Eksempel på arbejdsark og data
• 1. Brug af funktionerne VLOOKUP, COUNTIF eller EXACT til at finde dubletter
  • A. Hvordan bruger du VLOOKUP-funktionen til at finde dubletter i to ark?
    • Hvad hvis du håndterer forskellige arbejdsbøger?
  • B. Hvordan bruger du COUNTIF-funktionen til at finde dubletter på tværs af arbejdsark?
  • C. Hvordan bruger du den nøjagtige funktion til at finde dubletter på tværs af arbejdsark?
• 2. Sådan bruger du betinget formatering til duplikerede rækker
  • Sådan bruges Betinget formateringsregler Manager
• 3. Sådan bruger du Power Query til at finde dubletter på tværs af regneark
• 4. Værktøjer og tilføjelsesprogrammer til at identificere dubletter på tværs af arbejdsark
• 5. Sådan kontrollerer du visuelt for dubletter i to ark
• 3 tips til forberedelse af dine Excel-regneark
• Sådan håndteres fejl og uoverensstemmelser
• Afsluttende tanker

Eksempel på arbejdsark og data

For at følge eksemplerne i denne artikel skal du oprette en ny Excel-projektmappe med disse data i kolonne A på det første ark:

• Æble

• orange

• Pære

• Jordbær

Indsæt disse data i kolonne A på det andet ark:

• Pære

• Jordbær

• Æble

• Ananas

Dine arbejdsark ser sådan ud:

Når du har sat dine regneark op, kan vi nu gennemgå de forskellige måder at finde dubletter på i to ark, begyndende med funktionerne VLOOKUP, COUNTIF og EXACT.

Lad os gå!

1. Brug af funktionerne VLOOKUP, COUNTIF eller EXACT til at finde dubletter

Excel har tre indbyggede funktioner, der kan gøre det nemt at finde dubletter: VLOOKUP , COUNTIF , og EXACT .

Disse funktioner er designet til at hjælpe dig med at finde, tælle og sammenligne data i dine regneark, hvilket gør dem til ideelle værktøjer til at finde duplikerede poster.

Hver funktion tjener et unikt formål, og forståelsen af, hvordan du bruger dem, kan strømline din dataanalyseproces betydeligt.

I de følgende afsnit vil vi undersøge, hvordan du bruger funktionerne VLOOKUP, COUNTIF og EXACT til effektivt at identificere dubletter i dine Excel-regneark.

A. Hvordan bruger du VLOOKUP-funktionen til at finde dubletter i to ark?

VLOOKUP står for Vertical Lookup. Du bruger denne funktion til at finde dublerede værdier mellem to kolonner. Dette er syntaksen:

=VOPSLAG(opslagsværdi, tabelmatrix, kolonneindekstal, [områdeopslag])

• lookup_value : Den værdi, du vil søge efter i den første kolonne i table_array.

• table_array : Området af celler, der indeholder de data, du vil søge i.

• col_index_num : Kolonnenummeret i table_arrayet, du vil returnere værdien fra.

• range_lookup : Valgfrit. Det er enten SAND (omtrentlig match) eller FALSK (nøjagtig match). Standard er TRUE.

For at bruge funktionen på tværs af to regneark i en Excel-fil, skal du vide, hvordan du refererer til et separat ark i en formel. For at gøre det skal du skrive arknavnet efterfulgt af udråbstegn (!) og din celle eller celleområde.

For eksempel refererer dette til cellerne A2 til A5 i ark 2 i den samme projektmappe: Ark 2!$A$2:$A$5.

Følg disse trin for at bruge VLOOKUP-funktionen i dit eksempelregneark:

1. Vælg celle B2 for at vise det første sammenligningsresultat.

2. Indtast denne formel: =VLOOKUP(A2,Ark2!$A$2:$A$5, 1, FALSK).

3. Tryk på Enter for at få vist sammenligningsresultatet.

4. Udfyld formlen for at sammenligne værdierne for resten af ​​rækkerne i det første ark.

Resultaterne vil se ud som på billedet nedenfor:

Du kan rydde op i displayet ved at vise en brugervenlig besked i stedet for en fejl, når en dublet ikke findes.

For eksempel vil denne formel vise "Ja" eller "Nej" for henholdsvis fundet og ikke fundet værdier:

=HVIS(ISNA(VLOOKUP(A2, Ark 2!$A$2:$A$5, 1, FALSK)), “Nej”, “Ja”)

Der er flere eksempler på denne funktion i vores artikel om .

Hvad hvis du håndterer forskellige arbejdsbøger?

Hvis dine regneark er i separate projektmapper, er funktionsbrugen den samme med to Excel-filer. Det er dog lidt mere komplekst at henvise til det andet regneark.

Du skal:

• Indsæt navnet på Excel-projektmappen i parentes

• Følg med navnet på arbejdsarket

• Indsæt projektmappen og arbejdsarket i anførselstegn.

For eksempel, hvis cellerne er i et ark med navnet Sheet2 i en projektmappe med navnet "WB 2.xlsx", vil formatet se sådan ud:

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

Før du indtaster formlen, skal du lukke den anden projektmappe. Ellers får du en fejl.

B. Hvordan bruger du COUNTIF-funktionen til at finde dubletter på tværs af arbejdsark?

COUNTIF-funktionen i Excel bruges til at tælle antallet af celler inden for et specificeret område, der opfylder et givet kriterium.

For at sammenligne flere ark kan du tælle antallet af celler i det andet regneark, der matcher en celle i det første regneark.

Dette er syntaksen for funktionen:

=ANTALHVIS(interval; kriterier)

• Område : det område af celler, du vil tælle baseret på de angivne kriterier.

• Kriterier : den betingelse, der skal være opfyldt, for at en celle kan tælles.

Følg disse trin for at bruge funktionen med eksempeldataene:

1. Vælg celle B2 for at vise det første sammenligningsresultat.

2. Indtast denne formel: =ANTALHVIS(Ark2!$A$2:$A$5, A2)

3. Tryk på Enter for at få vist sammenligningsresultatet.

4. Udfyld formlen for at sammenligne værdierne for resten af ​​rækkerne i det første ark.

Funktionen vil finde ét match for nogle af cellerne og ingen for andre. Sammenligningscellen viser antallet. Her er resultatet fra prøvedataene:

COUNTIF-funktionen kan bruges til andre nyttige opgaver som f.eks.

C. Hvordan bruger du den nøjagtige funktion til at finde dubletter på tværs af arbejdsark?

EXACT-funktionen i Excel kan også bruges til at søge efter dubletter i de samme celler i to forskellige Excel-regneark. Syntaksen er:

=EXAKT(tekst1; tekst2)

• text1 er den første tekststreng, du vil sammenligne.

• text2 er den anden tekststreng, som du vil sammenligne.

Følg disse trin:

1. Vælg celle B2.

2. Indtast formlen =EXACT(A2, Ark2!A2)

3. Tryk på Enter for at få vist sammenligningsresultatet. Formlen returnerer TRUE, hvis begge værdier er identiske eller FALSK ellers.

4. Udfyld formlen for at sammenligne værdierne for resten af ​​rækkerne i det første ark.

Bemærk, at denne metode ikke søger efter dubletter på tværs af et celleområde. I stedet leder du kun efter match baseret på den samme celle i et andet ark.

Dette kan være nyttigt med bestilte data, hvor du kun forventer få undtagelser.

Funktionerne VLOOKUP, COUNTIF og EXACT er nyttige til at finde dubletter, men Excel er et alsidigt program, og der er andre muligheder. I næste afsnit ser vi på, hvordan du kan bruge betinget formatering til at identificere dubletter i to ark.

2. Sådan bruger du betinget formatering til duplikerede rækker

I dette afsnit lærer du, hvordan du bruger betinget formatering til at finde og fremhæve dublerede rækker i to Excel-regneark.

Følg disse trin for at oprette en betinget formateringsregel:

1. Vælg celleområdet, der indeholder dataene (A2:A5 i dette tilfælde).

2. Klik på fanen "Hjem" i Excel-båndet.

3. Klik på "Betinget formatering" i gruppen "Styles".

4. Vælg "Ny regel" fra rullemenuen.

Den næste opgave er at angive en formel, som din regel skal bruge. Følg disse trin:

1. Vælg "Brug en formel til at bestemme, hvilke celler der skal formateres" i dialogboksen

2. Indtast følgende formel: =ANTALHVIS(Ark2!$A$2:$A$5, A2) > 0

Til sidst anvender du den formatering, du foretrækker, for dublerede celler.

1. Klik på knappen Formater for at åbne dialogboksen Formater celler.

2. Vælg et format, f.eks. udfyld dubletter med en gul baggrundsfarve.

3. Klik på OK.

Dine duplikerede data er nu fremhævet med gult.

Sådan bruges Betinget formateringsregler Manager

Når du har oprettet reglen for betinget formatering, kan du administrere den ved hjælp af Betinget formateringsregler.

Sådan får du adgang til manageren:

1. Gå til fanen Hjem.

2. Klik på Betinget formatering.

3. Vælg "Administrer regler".

Du vil se en liste over alle betingede formateringsregler, der er anvendt på det valgte ark. Du kan redigere, slette eller ændre rækkefølgen af ​​regler ved at vælge reglen og klikke på de relevante knapper.

Følg disse trin for at anvende den samme regel på det andet ark:

1. Vælg det område, du vil sammenligne, i det andet ark.

2. Gå til Manager for betinget formateringsregler.

3. Vælg reglen, klik på Dupliker regel og tryk derefter på Rediger regel.

4. Erstat "Sheet2" med navnet på det første ark, der skal sammenlignes.

Nu hvor du har anvendt reglen om betinget formatering på begge ark, vil dubletter blive fremhævet i henhold til den formatering, du har valgt.

Sørg for at justere området og cellereferencerne i formlerne efter behov for at dække alle de data, du vil sammenligne.

Betinget formatering kan virke lidt primitiv. Hvis du vil have bedre kontrol, så kan Power Query være svaret! I det næste afsnit dækker vi, hvordan du kan bruge Power Query til at finde dubletter.

3. Sådan bruger du Power Query til at finde dubletter på tværs af regneark

Power Query er et datatransformations- og dataforberedelsesværktøj i Microsoft Excel. At identificere de samme værdier er blot en af ​​de mange analyseopgaver, du kan udføre med værktøjet.

For at gøre det skal du først importere dataene i de to regneark til separate tabeller. Følg disse trin i hvert ark:

1. Højreklik på celleområdet.

2. Vælg "Hent data fra tabel/område".

3. Ændre tabelnavnet til noget passende.

Efter import af begge ark er den første opgave at flette dataene:

1. Gå til fanen Data.

2. Klik på "Hent data".

3. Vælg "Kombiner forespørgsler".

4. Vælg "Merge" og vælg de to tabeller.

5. Klik på de to nøglekolonner.

6. Vælg "Indre" som "Join Kind", og klik på OK.

Power Query Editor åbnes med de kombinerede data fra begge tabeller i dit Excel-ark. Du vil se to kolonner, en fra hver tabel. Da du kun er interesseret i de duplikerede værdier, kan du fjerne den anden kolonne.

Du kan klikke på "Luk og indlæs" i Power Query Editor for at indlæse dubletterne til et nyt regneark.

For at udforske flere aspekter af denne kraftfulde funktion, følg eksemplerne i vores artikel om.

Excel har også tredjepartsværktøjer og tilføjelsesprogrammer, der tilføjer muligheden for problemfrit at finde dubletter, så lad os tage et kig på nogle af disse værktøjer i næste afsnit.

4. Værktøjer og tilføjelsesprogrammer til at identificere dubletter på tværs af arbejdsark

Eksterne værktøjer og tilføjelser kan tilbyde avanceret funktionalitet, som muligvis ikke er tilgængelig i indbyggede Excel-funktioner. Disse værktøjer kan yderligere strømline processen med at sammenligne ark for dubletter.

Spreadsheet Compare er et Microsoft-værktøj, der giver dig mulighed for at sammenligne to projektmapper side om side, fremhæve forskelle og let identificere dubletter. Du kan downloade det fra Microsofts websted.

Der er flere tilføjelser, du kan installere for at automatisere processen med at finde dubletter. Et eksempel er " Duplicate Remover ". Sådan installerer du et tilføjelsesprogram:

1. Gå til fanen Indsæt.

2. Klik på "Hent tilføjelse".

3. Søg efter "Duplicate".

4. Klik på "Tilføj" på det valgte værktøj.

5. Sådan kontrollerer du visuelt for dubletter i to ark

Hvis alt andet fejler, så brug dine øjne! Dialogboksen Arranger Windows i Excel giver dig mulighed for at se flere regneark eller projektmapper side om side.

Selvom den ikke direkte finder dubletter, kan den hjælpe dig visuelt med at sammenligne data på tværs af arbejdsark eller projektmapper for at finde dubletter. Følg disse trin:

1. Klik på fanen "Vis" i Excel-båndet.

2. Klik på "Arranger alle" i "Vindue"-gruppen.

3. Vælg en arrangementsmulighed, f.eks. "Lodret" eller "Horizontal".

Dette vil vise begge ark side om side eller over hinanden. Nu kan du manuelt sammenligne dataene i hvert ark for at identificere dubletter.

Du skal rulle gennem dataene og visuelt inspicere hver værdi for at finde matches.

Bemærk, at denne metode ikke er effektiv til store datasæt, da den kræver manuel sammenligning. Brug af de andre metoder i denne artikel vil være mere effektiv til at finde dubletter i større datasæt.

Og det er den sidste af vores almindelige metoder til at finde duplikerede værdier i Excel-ark! I det næste afsnit giver vi dig nogle tips til at forberede dine arbejdsark.

3 tips til forberedelse af dine Excel-regneark

Før du begynder at sammenligne flere ark, skal du sørge for, at kolonnerne og rækkerne i dine datasæt er korrekt opstillet.

Sørg for, at begge har samme struktur og samme overskriftsnavne. Hvis det er nødvendigt, kan du omarrangere kolonnerne i begge ark, så de passer til hinanden.

Her er tre forslag til at sikre nøjagtige sammenligninger:

1. Arranger dine data i samme rækkefølge i begge ark. Dette gør det lettere for Excel-funktioner at arbejde effektivt.

2. Normaliser dine data ved at bruge konsekvent formatering, store bogstaver og datatyper. Dette vil forhindre uoverensstemmende poster på grund af mindre forskelle.

3. Fjern unødvendige tomme rækker eller kolonner, da de kan forstyrre sammenligningsprocessen.

Du kan vurdere, hvor meget duplikering du har i et datasæt ved at tælle de unikke værdier. Denne video gennemgår flere metoder:

Sådan håndteres fejl og uoverensstemmelser

Uoverensstemmelser i dine data kan påvirke sammenligningsprocessen. Her er fire tips til at løse uoverensstemmelser:

1. Tjek for uoverensstemmelser i datatyper, såsom blanding af tekst og numeriske værdier i samme kolonne.

2. Sørg for, at der bruges ensartet formatering til datoer, tal og andre datatyper.

3. Undersøg dine data for manglende eller forkerte indtastninger, og opdater om nødvendigt.

4. Standardiser forkortelser eller inkonsistente navnekonventioner i dine datasæt.

Afsluttende tanker

At finde dubletter på tværs af to Excel-regneark er en vigtig opgave til datastyring og -analyse, hvilket sikrer dataintegritet og nøjagtighed. Excel tilbyder flere teknikker til at identificere dubletter, hver med sine egne fordele og begrænsninger.

Valget af metode afhænger af brugerens behov, datasættets størrelse og kompleksitet samt det ønskede resultat. For mindre datasæt og ligetil sammenligninger kan brug af , COUNTIF eller betinget formatering være tilstrækkeligt.

Til større datasæt eller mere komplekse datatransformationer er Power Query et kraftfuldt og fleksibelt værktøj, der kan håndtere en lang række dataforberedelsesopgaver, herunder at finde dubletter.

For at afslutte det, er sammenligning for dubletter en super praktisk færdighed at have i din værktøjskasse. Med trickene i denne artikel kan du få øje på de irriterende dubletter og holde dine data rene.

Stol på os, efterhånden som du bliver bedre til dette, vil du komme igennem dine dataopgaver og imponere alle omkring dig!