Generieke formule
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Samenvatting
Excel bevat een ingebouwde voorinstelling voor het markeren van dubbele waarden met voorwaardelijke opmaak, maar het werkt alleen op celniveau. Als u hele rijen wilt markeren die duplicaten zijn, moet u uw eigen formule gebruiken, zoals hieronder wordt uitgelegd.
Als u dubbele rijen in een ongesorteerde set gegevens wilt markeren en u geen hulpkolom wilt toevoegen, kunt u een formule gebruiken die de functie AANTAL.ALS gebruikt om dubbele waarden in elke kolom van de gegevens te tellen.
Als u bijvoorbeeld waarden in de cellen B4: D11 heeft en u volledige dubbele rijen wilt markeren, kunt u een nogal lelijke formule gebruiken:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Benoemde bereiken voor een schonere syntaxis
De reden dat de bovenstaande formule zo lelijk is, is dat we elk kolombereik volledig moeten vergrendelen en vervolgens een gemengde verwijzing moeten gebruiken om elke cel in elke kolom te testen. Als u benoemde bereiken maakt voor elke kolom in de gegevens: col_a, col_b en col_c, kan de formule worden geschreven met een veel schonere syntaxis:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Uitleg
In de formule telt AANTAL.ALS het aantal keren dat elke waarde in een cel in de "bovenliggende" kolom voorkomt. Per definitie moet elke waarde minstens één keer voorkomen, dus als de telling> 1 is, moet de waarde een duplicaat zijn. De verwijzingen zijn zorgvuldig vergrendeld, zodat de formule alleen true retourneert als alle drie de cellen op een rij meer dan eens in hun respectieve kolommen voorkomen.
De hulpkolomoptie "cheats" door alle waarden in een rij samen te combineren in een enkele cel met behulp van aaneenschakeling. AANTAL.ALS telt vervolgens het aantal keren dat deze aaneengeschakelde waarde in kolom D voorkomt.
Hulpkolom + aaneenschakeling
Als u het niet erg vindt om een hulpkolom aan uw gegevens toe te voegen, kunt u de formule voor voorwaardelijke opmaak behoorlijk vereenvoudigen. Voeg in een hulpkolom waarden uit alle kolommen samen. Voeg bijvoorbeeld een formule toe in kolom E die er als volgt uitziet:
=B4&C4&D4
Gebruik vervolgens de volgende formule in de regel voor voorwaardelijke opmaak:
=COUNTIF($E$4:$E$11,$E4)>1
Dit is een veel eenvoudigere regel en u kunt de hulpkolom verbergen als u dat wilt.
Als u een echt groot aantal kolommen heeft, kunt u de TEXTJOIN-functie (Excel 2016365) gebruiken om aaneenschakeling uit te voeren met behulp van een bereik:
=TEXTJOIN(",",TRUE,A1:Z1)
U kunt dan AANTAL.ALS gebruiken zoals hierboven.
SOMPRODUCT
Als u een versie van Excel vóór 2007 gebruikt, kunt u SUMPRODUCT als volgt gebruiken:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
