Extraire des éléments uniques d'une liste

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 (uniques,list),0))}

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

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

comment trouver le pourcentage du total

Le cœur de cette formule est une recherche de base avec INDEX :

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

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. Cela se fait avec MATCH et COUNTIF, et l'astuce principale est ici :

 
= INDEX (list,row)

Ici, COUNTIF compte combien de fois les éléments déjà présents dans la liste unique apparaissent dans la liste principale, à l'aide d'un référence en expansion pour la gamme,$D:D4.

Une référence en expansion est uneabsolu 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 s'étendra pour inclure plus de lignes dans la liste unique.

Noter laréférencecommence en D4,une rangée 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 reçoit plusieurs critères, COUNTIF renvoie plusieurs résultats dans un déployer . À chaque nouvelle ligne, nous avons un tableau différent comme celui-ci :

 
 COUNTIF ($D:D4,list)

Remarque : COUNTIF gère plusieurs critères avec une relation 'OU' (c'est-à-dire que COUNTIF (plage, {'red','blue', 'green'}) 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 :

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

MATCH localise les éléments en recherchant un nombre 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 baie avec LOOKUP

Vous pouvez créer une formule non matricielle pour extraire des éléments uniques à l'aide de la fonction flexible RECHERCHE :

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

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 nombre de chaque valeur de 'list' dans le gamme en expansion $D:D4
• La comparaison avec zéro crée un tableau de valeurs VRAI et FAUX
• 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 supérieure à toutes les valeurs de lookup_vector
• LOOKUP correspondra à 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),list)

Le seul ajout est la deuxième expression COUNTIF :

il y a une ou plusieurs références circulaires

 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Ici, COUNTIF renvoie un tableau de nombres d'éléments comme celui-ci :

 
 COUNTIF (list,list)=1

qui sont comparés à 1, résultant en un tableau de valeurs VRAI/FAUX :

 
{2222212}

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

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