Hvordan bruke COUNTIFS () for å sammenligne flere datasett

Bilde: iStock / jacoblund

Du kan bruke formler og innebygde regler for å fremheve forskjeller mellom to lister. Du kan prøve den ruten for å sammenligne to helt forskjellige datasett, men COUNTIFS () er enkelt og vil få jobben gjort. I denne artikkelen vil vi kombinere COUNTIFS () med et filter og et betinget format - du velger hvilket som best passer dine behov.

Jeg bruker Excel 2016 (desktop) på et Windows 10-system, men du kan bruke begge teknikkene i tidligere versjoner. Du kan jobbe med dine egne data eller laste ned demonstrasjonen .xls og .xlsx-filer. Nettleserutgaven støtter betingede formateringsregler, men du kan ikke bruke en tilpasset regel i nettleseren.

Merk: dette er den tredje av tre artikler i en serie. For de foregående artiklene, se Hvordan bruke Excel's betingede formatering for å sammenligne lister og Hvordan bruke Excel-formler for å sammenligne lister med flere kolonner.

Eksempeldata

De to datasettene som er vist i figur A, deler bare en post, rad 5 og 13. De andre postene deler vanlige verdier i to kolonner, men ikke alle tre. I dette tilfellet bryr vi oss ikke om repeterende data i samme kolonne. Vi bryr oss bare når disse verdiene kombineres for å lage en hel duplikatoppføring. Du kan imidlertid justere COUNTIFS () -funksjonen for å finne delmengder i stedet for en hel post.

Figur A

Vi vil avsløre duplikatene mellom de to datasettene.

For raskt å eksponere duplikater for poster, legger vi først til en COUNTIFS () -funksjon som returnerer 1 hvis det er en duplikat. COUNTIFS () -funksjonen bruker følgende syntaks for å spesifisere flere kriterier for å bestemme hvilke verdier i et område som skal telle:

 COUNTIFS (countrange1, criteria1, countrange2, criteria2 ...) 

Dette eksemplet inkluderer alle tre kolonnene for en sammenligning med full post, men du kan sammenligne delvise poster ved å inkludere bare kolonnene du vil sammenligne. I tillegg, hvis datasettene er på forskjellige ark, må du huske å inkludere arkidentifikatoren.

COUNTIFS () og filter

I dette første eksemplet vil vi kombinere resultatene av en COUNTIFSIO-funksjon med et filter. For å komme i gang skriver du inn følgende funksjon i E3 og kopierer til de resterende radene i det samme datasettet:

 = COUNTIFS ($ B $ 11: $ B $ 15, B3, $ C $ 11: $ C $ 15, C3, $ D $ 11: $ D $ 15, D3) 

Som du ser i figur B, returnerer bare en post 1 - posten i rad 5. Legg merke til at funksjonene i E3: E7 refererer til tilsvarende kolonner i rad 11 til 15 og sammenligner disse verdiene med verdiene i det første datasettet . Det er mulig fordi kriteriene i hvert argument sett er en relativ referanse.

Figur B

En registrering forekommer i begge datasettene.

I et så enkelt datasett er det lett å oppdage duplikatet, men det vil ikke være så enkelt når arket er fylt med mange poster. For å fullføre oppgaven kan du bruke et filter til bare å vise duplikatoppføringen i det første datasettet som følger:

  1. Velg cellen over COUNTIFS () -funksjonene, E2.
  2. Klikk på fanen Data, og klikk deretter på Filter i gruppen Sorter og filter. Hvis du gjør det, vil du legge til nedtrekksfilter i kolonnene B til og med E.
  3. Fra rullegardinmenyen i celle E2, fjern merket for (Velg alle) og merk 1, som vist i figur C.
  4. Klikk OK for å se det filtrerte settet som er vist i figur D.

Figur C

Filtrer etter verdien 1, som angir en duplikatrekord.

Figur D

Det filtrerte datasettet.

Hvis du er som meg, finner du sannsynligvis et filter som er mindre enn tilfredsstillende; du ser bare duplikatet i ett datasett. Du kan legge til et annet sett med COUNTIFS () og filtrere det andre settet også, men du ser bare duplikatene. Hvis du vil skille duplikater uten å filtrere, kan du prøve betinget formatering.

Betinget formatering og COUNTIFS ()

Ved å kombinere betinget formatering og de samme COUNTIFS () -funksjonene, kan du oppnå et større bilde. Det første datasettet er enkelt, men fjern først filteret du la til i den siste delen. Gjør deretter følgende:

  1. Velg B3: D7.
  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 = $ E3 = 1 i Formel-kontrollen.
  5. Klikk på Format, klikk på Fyll-fanen, velg en farge og klikk OK. Figur E viser formatet og regelen.
  6. Klikk OK for å gå tilbake til arket som er vist i figur F.

Figur E

Skriv inn en regel og velg et format.

Figur F

Den nye regelen fremhever det eneste duplikatet, rad 5.

Deretter legger du til følgende COUNTIFS () -funksjoner i det andre datasettet:

 = COUNTIFS ($ B $ 3: $ B $ 7, B11, $ C $ 3: $ C $ 7, C11, $ D $ 3: $ D $ 7, D11) 

Gjenta trinnene ovenfor, men velg B11: D15 i trinn 1, og skriv inn formelen = $ E11 = 1 i trinn 4. Figur G viser resultatene. I vårt eksempel er duplikatradene i samme posisjon i datasettet, men løsningen fungerer uavhengig av deres posisjoner. Det er en unik løsning for en unik situasjon.

Figur G

Betinget formatering fremhever begge dupliserte poster uten å filtrere.

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å

© Copyright 2021 | mobilegn.com