Exceller

Extraire des éléments uniques d'une liste

Extract Unique Items From List

Formule Excel: extraire des éléments uniques d'une listeFormule générique
{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}
Résumé

Pour extraire uniquement des 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:D4,list),0))}

où 'list' est le plage nommée B5: B11.



Remarque: il s'agit d'un formule matricielle et doit être entré avec Ctrl + Maj + Entrée.



comment trouver le pourcentage du total
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, afin que nous n'obtenions que des valeurs uniques. Ceci est fait avec MATCH et COUNTIF, et l'astuce principale est ici:

 
 COUNTIF ($D: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 un extension de la référence pour la gamme,$ D $ 4: D4.

Une référence en expansion est unbsoluté d'un côté, relatif de l'autre. Dans ce cas, au fur et à mesure que la formule est copiée, la référence se développe pour inclure plus de lignes dans la liste unique.



Noter laréférencecommence en D4,une rangée au dessus 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 déployer . À chaque nouvelle ligne, nous avons un tableau différent comme celui-ci:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

Remarque: COUNTIF gère plusieurs critères avec une relation 'OU' (c'est-à-dire que COUNTIF (plage, {'rouge', 'bleu', 'vert'}) compte le rouge, le bleu ou le vert.

comment appliquer une fonction à une colonne dans Excel

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,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},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: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 'list' dans le gamme élargie $ 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 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:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Le seul ajout est la deuxième expression COUNTIF:

il y a une ou plusieurs références circulaires
 
 COUNTIF (list,list)=1

Ici, COUNTIF renvoie un tableau de décomptes d'éléments comme ceci:

 
{2222212}

qui sont comparés à 1, ce qui donne un tableau de valeurs TRUE / FALSE:

 
{FALSEFALSEFALSEFALSEFALSETRUEFALSE}

qui agissent comme un «filtre» pour limiter la sortie aux éléments qui n'apparaissent qu'une seule fois dans les données source.

Dans Excel 365 , le Fonction UNIQUE est le meilleur moyen d'extraire des valeurs uniques.

Auteur Dave Bruns


^