Hvordan bruke Excel's betingede formatering for å sammenligne lister

Bilde: iStock / NicoElNino

Å sammenligne lister for vanlige verdier, eller duplikater, er en oppgave som ofte har mange variabler. Du kan sammenligne verdier i samme liste, eller du vil kanskje sammenligne en liste med en annen. Så er det definisjonen av duplikat. Du finner mange løsninger hvis du søker på internett, men du finner ingen løsning i størrelse. Du må kjenne dine data og bruke en passende løsning.

I denne artikkelen vil vi bruke betinget formatering for å sammenligne lister og øye duplikater. Først bruker vi den innebygde duplikatregelen for å sammenligne elementer i en enkelt liste; så bruker vi den til å sammenligne to lister. Deretter bruker vi en tilpasset betinget formateringsregel for å finne duplikater når den innebygde regelen ikke er tilstrekkelig.

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 disse reglene er tilgjengelige i eldre båndversjoner. Du kan jobbe med dine egne data eller laste ned demonstrasjonen .xls og .xlsx-filer. Nettleserutgaven støtter eksisterende betingede formateringsregler, og du kan til og med bruke innebygde regler. Du kan imidlertid ikke bruke egendefinerte regler i nettleseren.

Innebygd regel

Du kan bruke en formel med betinget formatering for å sammenligne data, men noen ganger kan de innebygde reglene få jobben gjort. Du trenger ingen spesialisert kunnskap, men du bør forstå hvordan funksjonen fungerer for å unngå frustrasjon. For å illustrere skal vi først se på hvordan den innebygde regelen sammenligner elementer i en enkelt liste ved å bruke det enkle arket som er vist i figur A på følgende måte:

  1. Velg den første listen, B1: B12. (Jeg gjentok det siste med vilje.)
  2. Klikk på Betinget formatering i kategorien Stiler på Hjem-fanen.
  3. Velg Uthev celleregler, og velg deretter Dupliser verdier i den påfølgende menyen ( figur A ).
  4. I den resulterende dialogen velger du et passende format og klikker OK. Som du kan i figur B, fremhevet denne innebygde regelen duplikater i samme kolonne fordi vi valgte en enkelt kolonne.

Figur A

Denne innebygde duplikatregelen sammenligner elementer i en enkelt liste.

Figur B

De innebygde reglene fremhever duplikater i samme kolonne.

La oss nå bruke den samme innebygde regelen for å sammenligne listen i kolonner B med listen i kolonne C. For å gjøre det, velg B2: C12 og følg de samme trinnene som ovenfor. Figur C viser resultatene. Denne regelen gjelder utheving hvis elementet vises mer enn en gang - hvor som helst. Dette kan inneholde duplikater i samme kolonne eller elementer som forekommer mer enn en gang på tvers av begge kolonnene.

Figur C

Dupliseringsregelen fremhever alle elementer som forekommer mer enn en gang i det valgte området.

Egendefinerte regler

De to listene vist i figur A er like, men det er subtile forskjeller. Noen få elementer er unike for begge listene. I tillegg er noen ganger elementet i kolonne B forskjellig fra det tilsvarende elementet i kolonne C. I denne delen bruker vi en tilpasset betinget formateringsregel for å oppdage elementene som er forskjellige fra en kolonne til den andre. Du kan tenke på disse varene som ikke samsvarer. Vi har allerede sett at den innebygde regelen evaluerer alle verdier i hvilken som helst posisjon, og det er ikke det vi ønsker.

Betinget formatering kan raskt identifisere forskjeller mellom to lister - fra kolonne til kolonne - ved å bruke et uttrykk i skjemaet:

= COUNTIF ( otherlist, firstcellinselected list ) = 0

La oss nå bruke denne regelen på kolonne B som følger:

  1. Velg listen i kolonne B, B2: B12.
  2. Klikk på kategorien Hjem, klikk betinget formatering i gruppen Stiler, og velg Ny regel fra rullegardinlisten.
  3. Velg Bruk en formel for å bestemme hvilke celler du vil formatere.
  4. Skriv inn = COUNTIF (C2: C2, B2) = 0 i Formel-kontrollen.
  5. Klikk på Format, klikk på Fyll-fanen, velg en farge og klikk OK. Figur D viser formatet og regelen.
  6. Klikk OK for å gå tilbake til arket vist i figur E.

Figur D

Denne regelen vil utheve elementer i kolonne B som ikke samsvarer med de tilsvarende elementene i kolonne C.

Figur E

De uthevede elementene i kolonne B stemmer ikke overens med den tilsvarende verdien i kolonne C.

Denne regelen fremhever ikke duplikater; hvilken som helst verdi i kolonne B som inneholder en verdi som ikke er i den korresponderende cellen i kolonne C, er lett å identifisere takket være cellens kontrasterende fyllfarge. Den fremhever ikke verdier som oppstår i begge kolonnene som den innebygde regelen gjorde. For å bruke regelen på kolonne B i stedet, bruker du regelen = COUNTIF (B2: B2, C2) = 0 etter å ha valgt C2: C11.

Denne regelen fungerer med verdier så vel som tekstoppføringer. Listene trenger heller ikke samsvare i størrelse. For eksempel fremhever regelen den siste verdien, Mishi Kobe Niku, fordi den tilsvarende cellen i kolonne C er tom. I tillegg betyr små forskjeller. Hvis du for eksempel fjerner apostrofkarakteren i Bobs, stemmer ikke elementene lenger. På den annen side er sammenligningen ikke store og små bokstaver. Hvis formatet ikke fremhever et element som du forventer, kan du sammenligne de to elementene. Det er mulig det er en ekstra romkarakter.

Deretter bruker vi den samme teknikken med en litt annen referanse for å sammenligne to lister; Hvis et element i kolonne B ikke forekommer noe sted i kolonne C, uthev elementet i kolonne B. Tidligere har vi fremhevet uoverensstemmede elementer fra kolonne til kolonne; nå skal vi fremheve uovertruffne elementer i begge kolonnene.

I dette eksemplet vil vi fremheve elementer i kolonne B som ikke forekommer i kolonne C, som følger:

  1. Uthev B2: B12.
  2. Klikk på kategorien Hjem, klikk betinget formatering i gruppen Stiler, og velg Ny regel fra rullegardinlisten.
  3. Velg Bruk en formel for å bestemme hvilke celler du vil formatere.
  4. Angi = COUNTIF ($ C $ 2: $ C $ 12, $ B2) = 0 i formelkontrollen.
  5. Klikk på Format, klikk på Fyll-fanen, velg en farge og klikk OK. Figur F viser formatet og regelen.
  6. Klikk OK for å gå tilbake til arket vist i figur G.

Figur F

Denne regelen vil utheve elementer i kolonne B som ikke forekommer noe sted i kolonne C.

Figur G

Enhver verdi i kolonne B som ikke forekommer i kolonne C er lett å identifisere takket være cellens kontrasterende fyllfarge.

Som du ser er det tre elementer i kolonne B som ikke forekommer i kolonne C. Men hva med kolonne C? For å identifisere elementer i kolonne C som ikke forekommer i kolonne B, gjenta trinnene ovenfor. I trinn 1 velger du imidlertid C2: C12 og i trinn 4 skriver du inn formelen = COUNTIF ($ B $ 2: $ B $ 12, $ C2) = 0 ; Velg en annen farge i trinn 5, hvis du foretrekker det. Som du kan se i figur H, forekommer ikke fire elementer i kolonne C, inkludert emnet på C12, ikke i kolonne B.

Figur H

To lignende regler fremhever elementer som ikke forekommer noe sted i den andre listen (kolonnen).

Følg med

I en påfølgende artikkel fortsetter vi med mer kompliserte duplikatfunnsproblemer.

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)
  • Du har brukt Excel galt hele tiden (og det er OK) (ZDNet)

© Copyright 2021 | pepebotifarra.com