Formule générique
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Sommaire
Pour marquer le premier doublon dans une liste, vous pouvez utiliser une formule basée sur la fonction COUNTIF. Si vous le souhaitez, vous pouvez marquer les doublons suivants avec un marqueur différent. Dans l'exemple illustré, la formule de la cellule C4 est:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Cette formule a été copiée dans la colonne, de C4 à C11.
Explication
Au cœur, cette formule est composée de deux ensembles de la fonction COUNTIF encapsulée dans la fonction IF. Le IF externe + COUNTIF vérifie d'abord si la valeur en question (B4) apparaît plus d'une fois dans la liste:
=IF(COUNTIF($B$4:$B$11,B4)>1
Sinon, la fonction IF externe renvoie une chaîne vide ("") comme résultat final. Si la valeur apparaît plus d'une fois, nous exécutons un autre combo IF + COUNTIF. Celui-ci fait le travail de signaler les doublons:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Cette partie de la formule utilise une référence extensible ($ B $ 4: B4) qui se développe lorsque la formule est copiée dans la colonne. (Le premier B4 de la plage est absolu (verrouillé), le second est relatif, il change donc lorsque la formule est copiée dans la liste).
N'oubliez pas que cette partie de la formule n'est exécutée que si le premier COUNTIF a renvoyé un nombre supérieur à 1. Ainsi, à chaque ligne, la formule vérifie le nombre à l'intérieur de la plage jusqu'à la ligne actuelle. Si le nombre est 1, nous marquons le doublon avec "x", car c'est le premier que nous ayons vu. Si ce n'est pas 1, nous savons qu'il doit s'agir d'un doublon ultérieur, et nous marquons avec "xx"
Formule de base
Pour marquer le premier doublon d'une liste uniquement avec un 0 ou un 1, vous pouvez utiliser cette formule simplifiée, qui utilise une plage extensible et la fonction NB.SI.
=(COUNTIFS($B$5:B5,B5)=2)+0
Cette formule renverra 1 uniquement lorsqu'une valeur a été rencontrée deux fois - la première occurrence renverra zéro:
Pour marquer la deuxième occurrence et toutes les occurrences suivantes, la formule dans F5 ci-dessus est:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Remarque: dans les deux exemples, l'ajout de zéro n'est qu'un moyen simple de forcer les valeurs VRAI et FAUX à 1 et 0.
De plus, l'utilisation de COUNTIFS au lieu de COUNTIF permet d'évaluer les valeurs d'autres colonnes dans le cadre du test des doublons. Chaque colonne supplémentaire doit également être saisie en tant que plage d'extension.
