Formule générique
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Sommaire
Excel contient un préréglage intégré pour mettre en évidence les valeurs en double avec une mise en forme conditionnelle, mais cela ne fonctionne qu'au niveau de la cellule. Si vous souhaitez mettre en évidence des lignes entières qui sont des doublons, vous devrez utiliser votre propre formule, comme expliqué ci-dessous.
Si vous souhaitez mettre en évidence des lignes dupliquées dans un ensemble de données non trié et que vous ne souhaitez pas ajouter de colonne d'assistance, vous pouvez utiliser une formule qui utilise la fonction COUNTIFS pour compter les valeurs dupliquées dans chaque colonne des données.
Par exemple, si vous avez des valeurs dans les cellules B4: D11 et que vous souhaitez mettre en évidence des lignes en double entières, vous pouvez utiliser une formule plutôt laide:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Plages nommées pour une syntaxe plus claire
La raison pour laquelle la formule ci-dessus est si moche est que nous devons verrouiller complètement chaque plage de colonnes, puis utiliser une référence mixte pour tester chaque cellule de chaque colonne. Si vous créez des plages nommées pour chaque colonne des données: col_a, col_b et col_c, la formule peut être écrite avec une syntaxe beaucoup plus claire:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Explication
Dans la formule, COUNTIFS compte le nombre de fois où chaque valeur d'une cellule apparaît dans sa colonne «parent». Par définition, chaque valeur doit apparaître au moins une fois, donc lorsque le nombre> 1, la valeur doit être un doublon. Les références sont soigneusement verrouillées afin que la formule ne retourne vrai que lorsque les 3 cellules d'une ligne apparaissent plusieurs fois dans leurs colonnes respectives.
L'option de colonne d'aide «triche» en combinant toutes les valeurs dans une ligne ensemble dans une seule cellule en utilisant la concaténation. Ensuite, COUNTIF compte simplement le nombre de fois où cette valeur concaténée apparaît dans la colonne D.
Colonne d'aide + concaténation
Si cela ne vous dérange pas d'ajouter une colonne d'aide à vos données, vous pouvez simplifier un peu la formule de mise en forme conditionnelle. Dans une colonne d'assistance, concaténez les valeurs de toutes les colonnes. Par exemple, ajoutez une formule dans la colonne E qui ressemble à ceci:
=B4&C4&D4
Utilisez ensuite la formule suivante dans la règle de mise en forme conditionnelle:
=COUNTIF($E$4:$E$11,$E4)>1
C'est une règle beaucoup plus simple et vous pouvez masquer la colonne d'assistance si vous le souhaitez.
Si vous avez un très grand nombre de colonnes, vous pouvez utiliser la fonction TEXTJOIN (Excel 2016 365) pour effectuer une concaténation à l'aide d'une plage:
=TEXTJOIN(",",TRUE,A1:Z1)
Vous pouvez ensuite utiliser COUNTIF comme ci-dessus.
SOMMEPROD
Si vous utilisez une version d'Excel antérieure à 2007, vous pouvez utiliser SUMPRODUCT comme ceci:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
