Exceller

Mettre en évidence les lignes en double

Highlight Duplicate Rows

Formule Excel: mettre en évidence les lignes en doubleFormule générique
= COUNTIFS (A:A,$A1,B:B,$B1,C:C,$C1)
Résumé

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:$B,$B4,$C:$C,$C4,$D:$D,$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 renvoie vrai que lorsque les 3 cellules d'une ligne apparaissent plusieurs fois dans leurs colonnes respectives.



comment trouver max dans excel

L'option de colonne d'aide «triche» en combinant toutes les valeurs d'une ligne ensemble dans une seule cellule à l'aide de 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 un 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:$E,$E4)>1

C'est une règle beaucoup plus simple et vous pouvez masquer la colonne d'assistance si vous le souhaitez.

comment trouver le plus petit nombre dans Excel

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
Démarrage rapide | Plus d'exemples | Dépannage | Formation Auteur Dave Bruns


^