Slik bruker du Excel-formler for å sammenligne lister med flere kolonner

Bilde: iStock / ijeab

Det er enkelt å finne dupliserte verdier i samme kolonne; du kan sortere eller bruke et filter avhengig av omstendighetene. Det er litt vanskeligere å finne duplikater som spenner over flere kolonner. En slags kan fungere, men da må du oppdage duplikatene. Så selv om det er bedre enn ingen løsning i det hele tatt, er det ikke en god løsning. Betinget formatering kan være et alternativ, men hvis du allerede har flere formater, er det kanskje ikke den beste løsningen. Hvis du vil ha en løsning som skriker ut: "Her er jeg! Jeg er en duplikat!" hvorfor ikke gjøre nettopp det? I denne artikkelen skal jeg vise deg en enkel uttrykksløsning som kan være enklere å implementere og mer effektiv enn betinget formatering.

Mer om Office

  • 18 Excel-tips hver bruker skal beherske
  • Microsoft Office, dataanalyse, virtuell assistent topp mest ønsket jobbferdigheter
  • Ansettingssett: Microsoft Power BI-utvikler (TechRepublic Premium)
  • Abonner på vårt Microsoft Weekly nyhetsbrev

Jeg bruker Excel 2016 (desktop) på et Windows 10-system, men denne løsningen vil fungere i eldre versjoner. Du kan jobbe med dine egne data eller laste ned demonstrasjonen .xls og .xlsx-filer.

Merk: dette er den andre av tre artikler i en serie. For de forrige artiklene, se Hvordan bruke Excel sin betingede formatering for å sammenligne lister.

Eksempeldata

La oss se raskt på et enkelt eksempel. Arket vist i figur A inneholder en kolonne med datoer og en kolonne med initialer. Noen få datoer forekommer mer enn en gang, og noen få innledende sett gjentas også; disse representerer duplikater i en enkelt kolonne. Vi er interessert i rader som gjentar samme dato og samme opprinnelige sett. Det er det jeg mener med en duplikat med flere kolonner. Og vi antar at du ikke vil bruke en tilpasset betinget regel.

Figur A

Vi vil legge til en formelløsning som oppdager duplikater.

Det er lett å oppdage duplikatene - rad 4 og 8 og rad 5 og 9 - i et så enkelt ark, men hva om du hadde hundrevis eller tusenvis av rader å sjekke? Et filter fungerer, men det er en sårbar løsning. I dette tilfellet er det tre forskjellige datoer. Det betyr at en bruker må gjennomgå minst tre sett med poster for å finne duplikater. Selv da må du stole på at brukeren din vil oppdage dem! Den innebygde betingede formateringsregelen vil fremheve alt fordi alle verdiene er duplikater! Du kan prøve et avansert filter eller til og med en tilpasset betinget formatregel, men begge deler krever hopping.

Concatenate

La oss ta en annen tilnærming fra sortering, filtrering og tilpasset formatering. I stedet bruker vi COUNTIF () for å telle antall kombinerte verdier og en IF () -funksjon for å returnere en passende melding. Det første trinnet er å slå sammen alle kolonnene, slik at du ikke virkelig sammenligner; du teller.

For å koble sammen verdiene, skriv inn følgende formel i D3 og kopier den til D4: D10:

 = B3 og C3 

Denne teknikken er fleksibel, og du kan slå sammen flere kolonner ved å kombinere dem med & tegnet. Excel returnerer serieverdiene ( figur B ), men det vil ikke forstyrre teknikken. (Tidsverdiene kan være problematiske, avhengig av hvordan de er lagt inn.)

Figur B

Sammenkoble verdiene.

Telle

Nå er vi klare til å telle de sammenlagte resultatene i kolonne D, så skriv inn formelen

 = HVIS (COUNTIF ($ D $ 3: D3, D3)> 1, "Her er jeg! Jeg er en duplikat!", "Original") 

i E3 og kopier til de gjenværende cellene. Som du ser i figur C, er det like enkelt å finne duplikater med flere kolonner som å sortere etter kolonne E (selv om dette enkle eksemplet ikke krever det ekstra trinnet). Du kan finne dupliserte poster eller delmengder av poster. Du bestemmer utfallet ved å bestemme hvilke kolonner du skal slå sammen.

Figur C

De resulterende strengene identifiserer dupliserte poster.

COUNTIF () -funksjonen teller antall ganger det sammenlagte resultatet oppstår innenfor det utvidede området. Hvis tellingen er større enn 1, returnerer formelen strengen "Her er jeg! Jeg er en duplikat!"; Ellers returnerer formelen strengen "Original." Det er viktig å merke seg at den første forekomsten av en duplikat evalueres som original; bare duplikater etter originalen returnerer duplikatstrengen. Du kan redusere den visuelle støyen litt ved å returnere en tom streng i stedet for strengen, "Original" når det ikke er duplikat.

Denne teknikken tilpasser seg lett til flere kolonner. Bare legg til hver kolonne i sammenslåingsformelen. Selvfølgelig er det andre måter å identifisere duplikater med flere kolonner i Excel, men denne krever ingen spesialisert kunnskap og er utrolig enkel å implementere.

Følg med

I en påfølgende artikkel vil vi fortsette denne studien med å finne duplikater med et mer komplekst eksempel sett - sammenligne lister med flere kolonner i forskjellige datasett.

Microsoft Weekly Newsletter

Vær din virksomhets Microsoft-innsider ved hjelp av disse Windows- og Office-opplæringsprogrammene og våre eksperters analyser av Microsofts bedriftsprodukter. Leveres mandager og onsdager

Registrer deg i dag

Send meg spørsmålet ditt om Office

Jeg svarer på lesernes spørsmål når jeg kan, men det er ingen garanti. Ikke send filer med mindre du blir bedt om det; innledende forespørsler om hjelp som kommer med vedlagte filer, blir slettet ulest. Du kan sende skjermbilder av dataene dine for å avklare spørsmålet ditt. Vær så spesifikk som mulig når du kontakter meg. For eksempel "Vennligst feilsøk arbeidsboka mine og fikse hva som er galt", vil sannsynligvis ikke få noe svar, men "Kan du fortelle meg hvorfor denne formelen ikke returnerer forventede resultater?" kanskje. Nevn appen og versjonen du bruker. Jeg får ikke refundert TechRepublic for min tid eller kompetanse når jeg hjelper leserne, og ber heller ikke om et gebyr fra lesere jeg hjelper. Du kan kontakte meg på

Se også:

  • Hvordan bruke en Excel-valideringskontroll som beskytter en spesifikk grense (TechRepublic)
  • Spørsmål og svar på Office: Topptekst og enkle tellinger ved hjelp av en PivotTable (TechRepublic)
  • Hvordan lage gjennomsiktig tekst i PowerPoint 2016 (TechRepublic)
  • Hvordan lage et raskt og enkelt online skjema med 365 Microsoft Forms (TechRepublic)
  • Slik bygger du et enkelt timeliste i Excel 2016 (TechRepublic)
  • 7 måter du (kanskje) kan få Microsoft Office 365 gratis (ZDNet)
  • Du har brukt Excel galt hele tiden (og det er OK) (ZDNet)

© Copyright 2020 | mobilegn.com