Du kan finne og markere dupliserte oppføringer mellom to kolonner ved å bruke funksjonen for betinget formatering i Google Sheets.
Mens du jobber i Google Sheets med store datasett, støter du sannsynligvis på et problem der du må håndtere mange dupliserte verdier. Mens noen duplikatoppføringer er plassert med vilje, mens andre er feil. Dette gjelder spesielt når du samarbeider på samme ark med et team.
Når det gjelder å analysere data på Google Sheets, kan det være viktig og praktisk å filtrere ut duplikater. Selv om Google Sheets ikke har noen innebygd støtte for å finne duplikater i ark, tilbyr det flere måter å sammenligne, identifisere og fjerne dupliserte data i celler.
Noen ganger vil du sammenligne hver verdi i en kolonne med en annen kolonne og finne ut om det er noen duplikater i den og omvendt. I Google Sheets kan du enkelt finne duplikater mellom to kolonner ved hjelp av funksjonen for betinget formatering. I denne artikkelen vil vi vise deg hvordan du sammenligner to kolonner i Google Sheets og finner duplikater mellom dem.
Finn dupliserte oppføringer mellom to kolonner ved hjelp av betinget formatering
Betinget formatering er en funksjon i Google Regneark som lar brukeren bruke spesifikke formateringer som skriftfarge, ikoner og datafelt på en celle eller et celleområde basert på visse forhold.
Du kan bruke denne betingede formateringen til å markere duplikatoppføringene mellom to kolonner, enten ved å fylle cellene med farge eller endre tekstfargen. Du må sammenligne hver verdi i en kolonne med en annen kolonne og finne ut om en verdi gjentas. For at dette skal fungere, må du bruke betinget formatering på hver kolonne separat. Følg disse trinnene for å gjøre det:
Åpne regnearket du vil se etter duplikater i Google Regneark. Velg først den første kolonnen (A) for å sjekke med kolonne B. Du kan markere hele kolonnen ved å klikke på kolonnebokstaven over den.
Klikk deretter på "Format"-menyen fra menylinjen og velg "Betinget formatering".
Betinget formatering-menyen åpnes på høyre side av Google-arkene. Du kan bekrefte at celleområdet er det du valgte under alternativet "Bruk på område". Hvis du vil endre området, klikker du på "områdeikonet" og velger et annet område.
Klikk deretter på rullegardinmenyen under "Formatregler" og velg alternativet "Egendefinert formel er".
Nå må du angi en egendefinert formel i boksen "Verdi eller formel".
Hvis du valgte en hel kolonne (B:B), skriv inn følgende COUNTIF-formel i 'Verdi eller formel'-boksen under Formatregler:
=countif($B:$B,$A2)>0Eller,
Hvis du valgte et celleområde i en kolonne (si hundre celler, A2:A30), bruk denne formelen:
=ANTALLHVIS($B$2:$B$30, $A2)>0Når du skriver inn formelen, sørg for å erstatte alle forekomster av bokstaven 'B' i formelen med bokstaven i kolonnen du har uthevet. Vi legger til "$"-tegnet før cellereferansene for å gjøre dem til absolutt område, så det endrer ikke at vi bruker formelen.
I Formateringsstil-delen kan du velge formateringsstilen for utheving av de dupliserte elementene. Som standard vil den bruke den grønne fyllfargen.
Du kan velge en av de forhåndsinnstilte formateringsstilene ved å klikke på "Standard" under alternativene for "Formateringsstil", og deretter velge en av forhåndsinnstillingene.
Eller du kan bruke hvilket som helst av de syv formateringsverktøyene (fet, kursiv, understreking, gjennomstreking, tekstfarge, fyllfarge) under "Formateringsstil"-delen for å fremheve duplikatene.
Her velger vi en fyllfarge for de dupliserte cellene ved å klikke på "Fyllfarge"-ikonet og velge den "gule" fargen.
Når du har valgt formateringen, klikker du på "Ferdig" for å markere cellene.
COUNTIF-funksjonen teller hvor mange ganger hver celleverdi i 'Kolonne A' vises i 'Kolonne B'. Så hvis et element vises én gang i kolonne B, returnerer formelen TRUE. Deretter vil elementet bli uthevet i 'Kolonne A' basert på formateringen du valgte.
Dette fremhever ikke duplikatene, men fremhever i stedet elementene som har duplikater i kolonne B. Det betyr at hvert gult uthevet element har duplikater i kolonne B.
Nå må vi bruke betinget formatering på kolonne B ved å bruke samme formel. For å gjøre det, velg den andre kolonnen (B2:B30), gå til 'Format'-menyen og velg 'Betinget formatering'.
Alternativt kan du klikke på knappen "Legg til en ny regel" under ruten "Betingede formatregler".
Deretter bekrefter du området (B2:B30) i 'Bruk på område'-boksen.
Sett deretter alternativet "Formater celler hvis..." til "Egendefinert formel er" og skriv inn formelen nedenfor i formelboksen:
=ANTALLHVIS($A$2:$A$30, $B2)>0Her bruker vi kolonne A-område ($A$2:$A$30) i det første argumentet og '$B2' i det andre argumentet. Denne formelen vil sjekke celleverdien i 'kolonne B' mot hver celle i kolonne A. Hvis en match (duplikat) blir funnet, vil betinget formatering heve elementet i 'kolonne B'
Deretter spesifiser formateringen i "Formateringsstil"-alternativene og klikk "Ferdig". Her velger vi den oransje fargen for kolonne B.
Dette vil fremheve elementene i kolonne B som har duplikater i kolonne A. Nå har du funnet og uthevet dupliserte elementer mellom to kolonner.
Du har sikkert lagt merke til at selv om det er et duplikat for 'Arcelia' i kolonne A, er det ikke uthevet. Det er fordi duplikatverdien bare er i én kolonne (A), ikke mellom kolonnene. Derfor er den ikke fremhevet.
Uthev duplikater mellom to kolonner i samme rad
Du kan også markere radene som har samme verdier (duplikater) mellom to kolonner ved å bruke betinget formatering. Regelen for betinget formatering kan kontrollere hver rad og fremhever radene som har samsvarende data i begge kolonnene. Slik gjør du dette:
Velg først begge kolonnene du vil sammenligne, og gå deretter til "Format"-menyen og velg "Betinget formatering".
I ruten Betingede formatregler bekrefter du området i boksen "Bruk på område" og velger "Egendefinert formel er" fra rullegardinmenyen "Formelceller hvis...".
Deretter skriver du inn formelen nedenfor i 'Verdi eller formel'-boksen:
=$A2=$B2Denne formelen vil sammenligne de to kolonnene rad for rad og markere rader som har identiske verdier (duplikater). Som du kan se, er formelen som legges inn her kun for den første raden i det valgte området, men formelen vil automatisk bli brukt på alle radene i det valgte området av funksjonen for betinget formatering.
Deretter spesifiser formateringen fra "Formateringsstil"-alternativene og klikk "Ferdig".
Som du kan se, vil bare radene som har samsvarende data (duplikater) mellom to kolonner bli uthevet og alle andre duplikater vil bli ignorert.
Uthev dupliserte celler i flere kolonner
Når du arbeider med større regneark med mange kolonner, vil du kanskje markere alle duplikatene som vises på tvers av flere kolonner i stedet for bare én eller to kolonner. Du kan fortsatt bruke betinget formatering for å markere duplikatet i flere kolonner.
Først velger du området for alle kolonner og rader du vil søke etter duplikater i stedet for bare én eller to kolonner. Du kan velge hele kolonner ved å holde nede Ctrl-tasten og deretter klikke på bokstaven øverst i hver kolonne. Alternativt kan du også klikke på den første og siste cellen i området ditt mens du også holder nede Shift-tasten for å velge flere kolonner samtidig.
I eksemplet velger vi A2:C30.
Klikk deretter på "Format"-alternativet i menyen og velg "Betinget formatering".
I reglene for betinget format setter du formatreglene til 'Egendefinert formel er', og skriv deretter inn følgende formel i boksen 'Verdi eller formel':
=countif($A$2:$C$30,A2)>Vi legger til "$"-tegnet før cellereferansene for å gjøre dem til absolutte kolonner, så det endrer ikke at vi bruker formelen. Du kan også skrive inn formelen uten '$'-tegnene, det fungerer uansett.
Velg deretter formateringen der du vil markere de dupliserte cellene ved å bruke "Formateringsstil"-alternativene. Her velger vi "Gul" fyllfarge. Etter det klikker du på "Ferdig".
Dette vil fremheve duplikatene på tvers av alle kolonnene du valgte, som vist nedenfor.
Etter å ha brukt den betingede formateringen, kan du redigere eller slette den betingede formateringsregelen når som helst.
Hvis du vil redigere gjeldende regel for betinget formatering, velg en celle med betinget formatering, gå til "Format" på menyen og velg "Betinget formatering".
Dette åpner ruten 'Betingede formatregler' til høyre med en liste over formatregler som er brukt på det gjeldende utvalget. Når du holder musepekeren over regelen, vil den vise deg sletteknappen, klikk på sletteknappen for å fjerne regelen. Eller, hvis du vil redigere regelen som vises for øyeblikket, klikk på selve regelen.
Hvis du vil legge til en annen betinget formatering over gjeldende regel, klikker du på knappen "Legg til en ny regel".
Tell duplikatene mellom to kolonner
Noen ganger vil du telle antall ganger en verdi i en kolonne gjentas i en annen kolonne. Det kan enkelt gjøres ved å bruke den samme COUNTIF-funksjonen.
For å finne antall ganger en verdi i kolonne A finnes i kolonne B, skriv inn følgende formel i en celle i en annen kolonne:
=COUNTIF($B$2:$B$30,$A2)Skriv inn denne formelen i celle C2. Denne formelen teller antall ganger verdien i celle A2 finnes i kolonnen (B2:B30) og returnerer antallet i celle C2.
Når du skriver inn formelen og trykker på Enter, vil Autofyll-funksjonen vises, klikk på "Avkryss" for å autofylle denne formelen til resten av cellene (C3:C30).
Hvis autofyll-funksjonen ikke vises, klikker du på den blå firkanten nederst til høyre i celle C2 og drar den ned for å kopiere formelen i celle C2 til cellene C3:C30.
'Sammenligning 1'-kolonne (C) vil nå vise deg antall ganger hver tilsvarende verdi i kolonne A vises i kolonne B. For eksempel finnes ikke verdien til A2 eller "Franklyn" i kolonne B, så COUNTIF-funksjonen returnerer "0". Og verdien "Loreta" (A5) finnes to ganger i kolonne B, og returnerer derfor "2".
Nå må vi gjenta de samme trinnene for å finne duplikattellingene til kolonne B. For å gjøre det, skriv inn følgende formel i celle D2 i kolonne D (Sammenligning 2):
=COUNTIF($A$2:$A$30,$B2)I denne formelen, bytt ut området fra '$B$2:$B$30' til '$A$2:$A$30' og '$B2' til '$A2'. Funksjonen teller antall ganger verdien i celle B2 finnes i kolonne A (A2:A30) og returnerer antallet i celle D2.
Deretter fyller du formelen automatisk til resten av cellene (D3:D30) i kolonne D. Nå vil 'Sammenligning 2' vise deg antall ganger hver tilsvarende verdi i kolonne B vises i kolonne A. For eksempel , verdien av B2 eller "Stark" finnes to ganger i kolonne A, så COUNTIF-funksjonen returnerer "2".
Merk: Hvis du vil telle duplikatene på tvers av alle kolonner eller flere kolonner, må du bare endre området i det første argumentet til COUNTIF-funksjonen til flere kolonner i stedet for bare én kolonne. Endre for eksempel området fra A2:A30 til A2:B30, som vil telle alle duplikatene i to kolonner i stedet for bare én.
Det er det.