Generisk formel
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Sammendrag
Excel inneholder en innebygd forhåndsinnstilling for å markere dupliserte verdier med betinget formatering, men det fungerer bare på cellenivå. Hvis du vil markere hele rader som er duplikater, må du bruke din egen formel, som forklart nedenfor.
Hvis du vil markere dupliserte rader i et usortert datasett, og du ikke vil legge til en hjelpekolonne, kan du bruke en formel som bruker COUNTIFS-funksjonen til å telle dupliserte verdier i hver kolonne med dataene.
Hvis du for eksempel har verdier i cellene B4: D11, og vil markere hele dupliserte rader, kan du bruke ganske stygg formel:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Navngitte områder for en renere syntaks
Årsaken til at formelen ovenfor er så stygg, er at vi må låse hvert kolonneområde fullt ut, og deretter bruke en blandet referanse for å teste hver celle i hver kolonne. Hvis du oppretter navngitte områder for hver kolonne i dataene: col_a, col_b og col_c, kan formelen skrives med en mye renere syntaks:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Forklaring
I formelen teller COUNTIFS antall ganger hver verdi i en celle vises i "foreldrekolonnen". Per definisjon må hver verdi vises minst en gang, så når tellingen> 1, må verdien være et duplikat. Referansene er nøye låst, slik at formelen bare vil gjelde når alle 3 cellene på rad vises mer enn en gang i sine respektive kolonner.
Hjelpekolonnealternativet "jukser" ved å kombinere alle verdiene på rad sammen i en enkelt celle ved hjelp av sammenkobling. Deretter teller COUNTIF bare antall ganger denne sammenkoblede verdien vises i kolonne D.
Hjelpekolonne + sammenkobling
Hvis du ikke har noe imot å legge til en hjelpekolonne i dataene dine, kan du forenkle den betingede formateringsformelen ganske mye. I en hjelpekolonne kan du sammenkoble verdier fra alle kolonnene. Legg for eksempel til en formel i kolonne E som ser slik ut:
=B4&C4&D4
Bruk deretter følgende formel i den betingede formateringsregelen:
=COUNTIF($E$4:$E$11,$E4)>1
Dette er en mye enklere regel, og du kan skjule hjelpekolonnen hvis du vil.
Hvis du har et veldig stort antall kolonner, kan du bruke TEXTJOIN-funksjonen (Excel 2016 365) til å utføre sammenkobling ved hjelp av et område:
=TEXTJOIN(",",TRUE,A1:Z1)
Du kan da bruke COUNTIF som ovenfor.
SUMPRODUKT
Hvis du bruker en versjon av Excel før 2007, kan du bruke SUMPRODUCT slik:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
