Generisk formel
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Resumé
Excel indeholder en indbygget forudindstilling til fremhævning af duplikatværdier med betinget formatering, men det fungerer kun på celleniveau. Hvis du vil fremhæve hele rækker, der er duplikater, skal du bruge din egen formel som forklaret nedenfor.
Hvis du vil fremhæve duplikerede rækker i et usorteret datasæt, og du ikke vil tilføje en hjælpekolonne, kan du bruge en formel, der bruger COUNTIFS-funktionen til at tælle duplikerede værdier i hver kolonne af dataene.
For eksempel, hvis du har værdier i cellerne B4: D11 og vil fremhæve hele duplikerede rækker, kan du bruge ret grim formel:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Navngivne intervaller for en renere syntaks
Årsagen til, at ovenstående formel er så grim, er, at vi skal låse hvert kolonneområde fuldt ud og derefter bruge en blandet reference til at teste hver celle i hver kolonne. Hvis du opretter navngivne områder for hver kolonne i dataene: col_a, col_b og col_c, kan formlen skrives med en meget renere syntaks:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Forklaring
I formlen tæller COUNTIFS antallet af gange hver værdi i en celle vises i dens "overordnede" kolonne. Per definition skal hver værdi vises mindst en gang, så når antallet> 1 skal værdien være en duplikat. Referencerne er omhyggeligt låst, så formlen kun vil være sand, når alle 3 celler i en række vises mere end én gang i deres respektive kolonner.
Hjælpekolonnefunktionen "snyder" ved at kombinere alle værdier i en række sammen i en enkelt celle ved hjælp af sammenkædning. Derefter tæller COUNTIF simpelthen det antal gange, denne sammenkædede værdi vises i kolonne D.
Hjælpekolonne + sammenkædning
Hvis du ikke har noget imod at tilføje en hjælpekolonne til dine data, kan du forenkle den betingede formateringsformel en hel del. Sammenkæd værdier fra alle kolonner i en hjælpekolonne. Tilføj f.eks. En formel i kolonne E, der ser sådan ud:
=B4&C4&D4
Brug derefter følgende formel i den betingede formateringsregel:
=COUNTIF($E$4:$E$11,$E4)>1
Dette er en meget enklere regel, og du kan skjule hjælpekolonnen, hvis du vil.
Hvis du har et rigtig stort antal kolonner, kan du bruge funktionen TEXTJOIN (Excel 2016 365) til at udføre sammenkædning ved hjælp af et interval:
=TEXTJOIN(",",TRUE,A1:Z1)
Du kan derefter bruge COUNTIF som ovenfor.
SUMPRODUKT
Hvis du bruger en version af Excel før 2007, kan du bruge SUMPRODUCT sådan:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
