Formule générique
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Sommaire
Pour extraire uniquement les valeurs uniques d'une liste ou d'une colonne, vous pouvez utiliser une formule matricielle basée sur INDEX, MATCH et COUNTIF. Dans l'exemple illustré, la formule en D5, copiée vers le bas, est:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
où "liste" est la plage nommée B5: B11.
Remarque: il s'agit d'une formule matricielle et doit être saisie à l'aide de Ctrl + Maj + Entrée.
Explication
Le cœur de cette formule est une recherche de base avec INDEX:
=INDEX(list,row)
En d'autres termes, donnez à INDEX la liste et un numéro de ligne, et INDEX récupérera une valeur à ajouter à la liste unique.
Le travail acharné consiste à déterminer le numéro de ROW pour donner INDEX, de sorte que nous n'obtenions que des valeurs uniques. Ceci est fait avec MATCH et COUNTIF, et l'astuce principale est ici:
COUNTIF($D$4:D4,list)
Ici, COUNTIF compte le nombre de fois où les éléments déjà dans la liste unique apparaissent dans la liste principale, en utilisant une référence expansive pour la plage, $ D $ 4: D4.
Une référence en expansion est absolue d'un côté, relative de l'autre. Dans ce cas, au fur et à mesure que la formule est copiée, la référence s'étendra pour inclure plus de lignes dans la liste unique.
Notez que la référence commence en D4, une ligne au-dessus de la première entrée unique, dans la liste unique. C'est intentionnel - nous voulons compter les éléments * déjà * dans la liste unique, et nous ne pouvons pas inclure la cellule actuelle sans créer une référence circulaire. Donc, nous commençons sur la ligne ci-dessus.
Important: assurez-vous que l'en-tête de la liste unique n'apparaît pas dans la liste principale.
Pour les critères de COUNTIF, nous utilisons la liste principale elle-même. Lorsqu'il est donné plusieurs critères, COUNTIF renverra plusieurs résultats dans un tableau. À chaque nouvelle ligne, nous avons un tableau différent comme celui-ci:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Remarque: COUNTIF gère plusieurs critères avec une relation «OU» (c'est-à-dire COUNTIF (intervalle, («rouge», «bleu», «vert»)) compte le rouge, le bleu ou le vert.
Nous avons maintenant les tableaux dont nous avons besoin pour trouver des positions (numéros de ligne). Pour cela, nous utilisons MATCH, configuré pour une correspondance exacte, pour trouver des valeurs nulles. Si nous mettons les tableaux créés par COUNTIF ci-dessus dans MATCH, voici ce que nous obtenons:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH localise les éléments en recherchant un décompte de zéro (c'est-à-dire en recherchant des éléments qui n'apparaissent pas encore dans la liste unique). Cela fonctionne, car MATCH renvoie toujours la première correspondance lorsqu'il y a des doublons.
Enfin, les positions sont introduites dans INDEX sous forme de numéros de ligne et INDEX renvoie le nom à cette position.
Version sans tableau avec LOOKUP
Vous pouvez créer une formule sans tableau pour extraire des éléments uniques à l'aide de la fonction de recherche flexible:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
La construction de la formule est similaire à la formule INDEX MATCH ci-dessus, mais LOOKUP peut gérer l'opération de tableau de manière native.
- COUNTIF renvoie le décompte de chaque valeur de "liste" dans la plage d'extension $ D $ 4: D4
- La comparaison à zéro crée un tableau de valeurs TRUE et FALSE
- Le nombre 1 est divisé par le tableau, créant un tableau d'erreurs 1 et # DIV / 0
- Ce tableau devient le lookup_vector dans LOOKUP
- La valeur de recherche de 2 est plus grande que toutes les valeurs du lookup_vector
- LOOKUP correspond à la dernière valeur sans erreur dans le tableau de recherche
- LOOKUP renvoie la valeur correspondante dans result_vector, la plage nommée "list"
Extraire les éléments qui n'apparaissent qu'une seule fois
La formule LOOKUP ci-dessus est facile à étendre avec la logique booléenne. Pour extraire une liste d'éléments uniques qui n'apparaissent qu'une seule fois dans les données source, vous pouvez utiliser une formule comme celle-ci:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Le seul ajout est la deuxième expression COUNTIF:
COUNTIF(list,list)=1
Ici, COUNTIF renvoie un tableau de décomptes d'éléments comme ceci:
(2;2;2;2;2;1;2)
qui sont comparés à 1, ce qui donne un tableau de valeurs TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
qui agissent comme un «filtre» pour limiter la sortie aux éléments qui n'apparaissent qu'une seule fois dans les données source.
