Hvordan sammenligne to Excel-ark for duplikater: 5 raske måter

Når du arbeider med store datasett i Excel, er det vanlig å ha flere regneark som inneholder lignende data. Å sammenligne disse arkene for dupliserte poster kan være litt av en utfordring, spesielt når dataene er spredt over ulike kolonner og rader.

De vanligste metodene for å finne duplikater i to Excel-ark er å bruke:

1. VLOOKUP, COUNTIF eller EXACT-funksjoner

2. Betinget formatering

3. Power Query

4. Eksterne verktøy og tillegg

5. Visuelle kontroller for duplikater

Denne artikkelen går trinn-for-trinn gjennom disse fem metodene for å finne og håndtere duplikater på tvers av flere regneark. På slutten vil du se hvor enkelt det er å administrere dupliserte verdier.

La oss komme i gang!

Innholdsfortegnelse

• Eksempel på regneark og data
• 1. Bruke funksjonene VLOOKUP, COUNTIF eller EXACT for å finne duplikater
  • A. Hvordan bruker du VLOOKUP-funksjonen til å finne duplikater i to ark?
    • Hva om du håndterer forskjellige arbeidsbøker?
  • B. Hvordan bruker du COUNTIF-funksjonen til å finne duplikater på tvers av regneark?
  • C. Hvordan bruker du NØYAKTIG funksjon for å finne duplikater på tvers av regneark?
• 2. Hvordan bruke betinget formatering for dupliserte rader
  • Slik bruker du betinget formateringsreglerbehandling
• 3. Hvordan bruke Power Query til å finne duplikater på tvers av regneark
• 4. Verktøy og tillegg for å identifisere duplikater på tvers av regneark
• 5. Hvordan visuelt se etter duplikater i to ark
• 3 tips for å forberede Excel-regnearkene dine
• Hvordan håndtere feil og inkonsekvenser
• Siste tanker

Eksempel på regneark og data

For å følge eksemplene i denne artikkelen, lag en ny Excel-arbeidsbok med disse dataene i kolonne A i det første arket:

• eple

• oransje

• Pære

• Jordbær

Sett disse dataene inn i kolonne A på det andre arket:

• Pære

• Jordbær

• eple

• Ananas

Regnearkene dine ser slik ut:

Når du har satt opp regnearkene dine, kan vi nå gå gjennom de forskjellige måtene å finne duplikater i to ark, og starter med funksjonene VLOOKUP, COUNTIF og EXACT.

La oss gå!

1. Bruke funksjonene VLOOKUP, COUNTIF eller EXACT for å finne duplikater

Excel har tre innebygde funksjoner som kan gjøre det enkelt å finne duplikater: VLOOKUP , COUNTIF og EXACT .

Disse funksjonene er utviklet for å hjelpe deg med å finne, telle og sammenligne data i regnearkene dine, noe som gjør dem til ideelle verktøy for å finne dupliserte oppføringer.

Hver funksjon tjener et unikt formål, og å forstå hvordan du bruker dem kan strømlinjeforme dataanalyseprosessen din betydelig.

I de følgende delene vil vi utforske hvordan du bruker funksjonene VLOOKUP, COUNTIF og EXACT for å effektivt identifisere duplikater i Excel-regnearkene dine.

A. Hvordan bruker du VLOOKUP-funksjonen til å finne duplikater i to ark?

VLOOKUP står for Vertical Lookup. Du bruker denne funksjonen til å finne dupliserte verdier mellom to kolonner. Dette er syntaksen:

=VLOOKUP(oppslagsverdi, tabellmatrise, kolonneindeksnummer, [områdeoppslag])

• lookup_value : Verdien du vil søke etter i den første kolonnen i table_array.

• table_array : Området med celler som inneholder dataene du vil søke i.

• col_index_num : Kolonnenummeret i table_arrayen du vil returnere verdien fra.

• range_lookup : Valgfritt. Det er enten SANN (omtrentlig samsvar) eller FALSE (eksakt samsvar). Standard er TRUE.

For å bruke funksjonen på tvers av to regneark i en Excel-fil, må du vite hvordan du refererer til et eget ark i en formel. For å gjøre det, skriv inn arknavnet etterfulgt av utropstegnet (!) og cellen eller celleområdet ditt.

For eksempel refererer dette til cellene A2 til A5 i ark 2 i samme arbeidsbok: Ark 2!$A$2:$A$5.

Følg disse trinnene for å bruke VLOOKUP-funksjonen i eksempelregnearket:

1. Velg celle B2 for å vise det første sammenligningsresultatet.

2. Skriv inn denne formelen: =VLOOKUP(A2,Ark2!$A$2:$A$5, 1, FALSE).

3. Trykk Enter for å vise sammenligningsresultatet.

4. Fyll ned formelen for å sammenligne verdiene for resten av radene i det første arket.

Resultatene vil se ut som bildet nedenfor:

Du kan rydde opp i displayet ved å vise en brukervennlig melding i stedet for en feilmelding når et duplikat ikke blir funnet.

For eksempel vil denne formelen vise "Ja" eller "Nei" for henholdsvis funnet og ikke funnet verdier:

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

Det er flere eksempler på denne funksjonen i artikkelen vår om .

Hva om du håndterer forskjellige arbeidsbøker?

Hvis regnearkene dine er i separate arbeidsbøker, er funksjonsbruken den samme med to Excel-filer. Det er imidlertid litt mer komplekst å referere til det andre regnearket.

Du trenger å:

• Legg ved navnet på Excel-arbeidsboken i parentes

• Følg med navnet på regnearket

• Legg ved arbeidsboken og regnearket i anførselstegn.

For eksempel, hvis cellene er i et ark kalt Sheet2 i en arbeidsbok kalt "WB 2.xlsx", vil formatet se slik ut:

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

Lukk den andre arbeidsboken før du skriver inn formelen. Ellers får du en feilmelding.

B. Hvordan bruker du COUNTIF-funksjonen til å finne duplikater på tvers av regneark?

COUNTIF-funksjonen i Excel brukes til å telle antall celler innenfor et spesifisert område som oppfyller et gitt kriterium.

For å sammenligne flere ark, kan du telle antall celler i det andre regnearket som samsvarer med en celle i det første regnearket.

Dette er syntaksen til funksjonen:

=ANTALLHVIS(område; kriterier)

• Område : celleområdet du vil telle basert på de angitte kriteriene.

• Kriterier : betingelsen som må være oppfylt for at en celle skal telles.

Følg disse trinnene for å bruke funksjonen med eksempeldataene:

1. Velg celle B2 for å vise det første sammenligningsresultatet.

2. Skriv inn denne formelen: =ANTALLHVIS(Ark2!$A$2:$A$5, A2)

3. Trykk Enter for å vise sammenligningsresultatet.

4. Fyll ned formelen for å sammenligne verdiene for resten av radene i det første arket.

Funksjonen vil finne ett samsvar for noen av cellene og ingen for andre. Sammenligningscellen viser tellingen. Her er resultatet fra prøvedataene:

COUNTIF-funksjonen kan brukes til andre nyttige oppgaver som .

C. Hvordan bruker du NØYAKTIG funksjon for å finne duplikater på tvers av regneark?

EKSAKT-funksjonen i Excel kan også brukes til å se etter duplikater i de samme cellene i to forskjellige Excel-regneark. Syntaksen er:

=EKSAKT(tekst1; tekst2)

• text1 er den første tekststrengen du vil sammenligne.

• text2 er den andre tekststrengen du vil sammenligne.

Følg disse instruksjonene:

1. Velg celle B2.

2. Skriv inn formelen =EXACT(A2, Ark2!A2)

3. Trykk Enter for å vise sammenligningsresultatet. Formelen vil returnere TRUE hvis begge verdiene er identiske eller FALSE ellers.

4. Fyll ned formelen for å sammenligne verdiene for resten av radene i det første arket.

Merk at denne metoden ikke søker etter duplikater over et celleområde. I stedet ser du bare etter treff basert på samme celle i et annet ark.

Dette kan være nyttig med bestilte data der du kun forventer noen få unntak.

Funksjonene VLOOKUP, COUNTIF og EXACT er nyttige for å finne duplikater, men Excel er et allsidig program, og det finnes andre alternativer. I neste avsnitt ser vi på hvordan du kan bruke betinget formatering for å identifisere duplikater i to ark.

2. Hvordan bruke betinget formatering for dupliserte rader

I denne delen lærer du hvordan du bruker betinget formatering for å finne og markere dupliserte rader i to Excel-regneark.

Følg disse trinnene for å opprette en betinget formateringsregel:

1. Velg celleområdet som inneholder dataene (A2:A5 i dette tilfellet).

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

3. Klikk på "Betinget formatering" i "Stiler"-gruppen.

4. Velg "Ny regel" fra rullegardinmenyen.

Den neste oppgaven er å gi en formel som regelen din skal bruke. Følg disse instruksjonene:

1. Velg "Bruk en formel for å bestemme hvilke celler som skal formateres" i dialogboksen

2. Skriv inn følgende formel: =ANTALLHVIS(Ark2!$A$2:$A$5, A2) > 0

Til slutt bruker du formateringen du foretrekker for dupliserte celler.

1. Klikk på Format-knappen for å åpne dialogboksen Formater celler.

2. Velg et format, f.eks. fyll duplikater med gul bakgrunnsfarge.

3. Klikk OK.

Dupliserte data er nå uthevet i gult.

Slik bruker du betinget formateringsreglerbehandling

Når du har opprettet regelen for betinget formatering, kan du administrere den ved hjelp av Betinget formateringsregelbehandling.

Slik får du tilgang til administratoren:

1. Gå til fanen Hjem.

2. Klikk på Betinget formatering.

3. Velg "Administrer regler".

Du vil se en liste over alle betingede formateringsregler som er brukt på det valgte arket. Du kan redigere, slette eller endre rekkefølgen på regler ved å velge regelen og klikke på de aktuelle knappene.

Følg disse trinnene for å bruke samme regel på det andre arket:

1. Velg området du vil sammenligne i det andre arket.

2. Gå til Betinget formateringsreglerbehandling.

3. Velg regelen, klikk på Dupliser regel og trykk deretter på Rediger regel.

4. Erstatt "Sheet2" med navnet på det første arket som skal sammenlignes.

Nå som du har brukt regelen for betinget formatering på begge arkene, vil duplikater bli uthevet i henhold til formateringen du har valgt.

Sørg for å justere området og cellereferansene i formlene etter behov for å dekke alle dataene du vil sammenligne.

Betinget formatering kan virke litt primitivt. Hvis du vil ha bedre kontroll, kan Power Query være svaret! I den neste delen dekker vi hvordan du kan bruke Power Query til å finne duplikater.

3. Hvordan bruke Power Query til å finne duplikater på tvers av regneark

Power Query er et datatransformasjons- og dataforberedelsesverktøy i Microsoft Excel. Å identifisere de samme verdiene er bare en av de mange analyseoppgavene du kan utføre med verktøyet.

For å gjøre det, bør du først importere dataene i de to regnearkene til separate tabeller. Følg disse trinnene i hvert ark:

1. Høyreklikk på celleområdet.

2. Velg "Hent data fra tabell/område".

3. Endre tabellnavnet til noe passende.

Etter å ha importert begge arkene, er den første oppgaven å slå sammen dataene:

1. Gå til fanen Data.

2. Klikk på "Hent data".

3. Velg "Kombiner spørringer".

4. Velg "Slå sammen" og velg de to tabellene.

5. Klikk på de to nøkkelkolonnene.

6. Velg "Indre" som "Bli med" og klikk OK.

Power Query Editor åpnes med de kombinerte dataene fra begge tabellene i Excel-arket. Du vil se to kolonner, en fra hver tabell. Siden du kun er interessert i dupliserte verdier, kan du fjerne den andre kolonnen.

Du kan klikke på "Lukk og last" i Power Query Editor for å laste duplikatene til et nytt regneark.

For å utforske flere aspekter ved denne kraftige funksjonen, følg eksemplene i artikkelen vår om.

Excel har også tredjepartsverktøy og tillegg som legger til muligheten til sømløst å finne duplikater, så la oss ta en titt på noen av disse verktøyene i neste avsnitt.

4. Verktøy og tillegg for å identifisere duplikater på tvers av regneark

Eksterne verktøy og tillegg kan tilby avansert funksjonalitet som kanskje ikke er tilgjengelig i integrerte Excel-funksjoner. Disse verktøyene kan ytterligere effektivisere prosessen med å sammenligne ark for duplikater.

Spreadsheet Compare er et Microsoft-verktøy som lar deg sammenligne to arbeidsbøker side ved side, fremheve forskjeller og enkelt identifisere duplikater. Du kan laste den ned fra Microsofts nettsted.

Det er flere tillegg du kan installere for å automatisere prosessen med å finne duplikater. Et eksempel er " Duplicate Remover ". Slik installerer du et tillegg:

1. Gå til Sett inn-fanen.

2. Klikk på "Get Add-In".

3. Søk etter "Dupliser".

4. Klikk "Legg til" på verktøyet du ønsker.

5. Hvordan visuelt se etter duplikater i to ark

Hvis alt annet feiler, bruk øynene! Dialogboksen Ordne Windows i Excel lar deg vise flere regneark eller arbeidsbøker side ved side.

Selv om den ikke finner duplikater direkte, kan den hjelpe deg visuelt å sammenligne data på tvers av regneark eller arbeidsbøker for å finne duplikater. Følg disse instruksjonene:

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

2. Klikk på "Arranger alle" i "Vindu"-gruppen.

3. Velg et arrangementsalternativ, f.eks. "Vertikal" eller "Horisontal".

Dette vil vise begge arkene side ved side eller over hverandre. Nå kan du manuelt sammenligne dataene i hvert ark for å identifisere duplikater.

Du må bla gjennom dataene og visuelt inspisere hver verdi for å finne samsvar.

Merk at denne metoden ikke er effektiv for store datasett, da den krever manuell sammenligning. Å bruke de andre metodene i denne artikkelen vil være mer effektivt for å finne duplikater i større datasett.

Og det er den siste av våre vanlige metoder for å finne dupliserte verdier i Excel-ark! I neste avsnitt vil vi gi deg noen tips for å forberede arbeidsarkene dine.

3 tips for å forberede Excel-regnearkene dine

Før du begynner å sammenligne flere ark, sørg for at kolonnene og radene i datasettene dine er riktig stilt opp.

Sørg for at begge har samme struktur og samme overskriftsnavn. Om nødvendig kan du omorganisere kolonnene i begge arkene for å matche hverandre.

Her er tre forslag for å sikre nøyaktige sammenligninger:

1. Ordne dataene dine i samme rekkefølge i begge arkene. Dette gjør det enklere for Excel-funksjoner å fungere effektivt.

2. Normaliser dataene dine ved å bruke konsekvent formatering, bruk av store bokstaver og datatyper. Dette vil forhindre feilaktige oppføringer på grunn av mindre forskjeller.

3. Fjern unødvendige tomme rader eller kolonner, da de kan forstyrre sammenligningsprosessen.

Du kan vurdere hvor mye duplisering du har i et datasett ved å telle de unike verdiene. Denne videoen går gjennom flere metoder:

Hvordan håndtere feil og inkonsekvenser

Inkonsekvenser i dataene dine kan påvirke sammenligningsprosessen. Her er fire tips for å løse inkonsekvenser:

1. Se etter avvik i datatyper, for eksempel blanding av tekst og numeriske verdier i samme kolonne.

2. Sørg for at konsekvent formatering brukes for datoer, tall og andre datatyper.

3. Undersøk dataene dine for manglende eller feil oppføringer, og oppdater om nødvendig.

4. Standardiser forkortelser eller inkonsekvente navnekonvensjoner i datasettene dine.

Siste tanker

Å finne duplikater på tvers av to Excel-regneark er en viktig oppgave for dataadministrasjon og -analyse, og sikrer dataintegritet og nøyaktighet. Excel tilbyr flere teknikker for å identifisere duplikater, hver med sine egne fordeler og begrensninger.

Valg av metode avhenger av brukerens behov, størrelsen og kompleksiteten til datasettet, og ønsket resultat. For mindre datasett og enkle sammenligninger kan bruk av , COUNTIF eller betinget formatering være tilstrekkelig.

For større datasett eller mer komplekse datatransformasjoner er Power Query et kraftig og fleksibelt verktøy som kan håndtere et bredt spekter av dataforberedelsesoppgaver, inkludert å finne duplikater.

For å avslutte det, er sammenligning for duplikater en super hendig ferdighet å ha i verktøykassen. Med triksene i denne artikkelen kan du oppdage de irriterende duplikatene og holde dataene dine rene.

Stol på oss, etter hvert som du blir bedre på dette, vil du komme deg gjennom dataoppgavene dine og imponere alle rundt deg!