Comment extraire une liste de valeurs uniques par formule

(Last Updated On: 13 juin 2020)

Voici une formule fort utile pour extraire une liste de valeurs uniques dans une colonne de données.

Sur le même thème, vous pourriez également être intéressé par l’article Comment compter le nombre de valeurs uniques dans un tableau.

Formule

{=INDEX(liste de valeurs ; EQUIV(0 ; NB.SI(liste unique ; liste de valeurs) ; 0))}

Voici comment utiliser la formule :

Pour extraire les valeurs uniques d’une liste ou d’une colonne, vous pouvez utiliser une formule matricielle basée sur INDEX, EQUIV et NB.SI.

  1. Entrez la formule ci-dessous en D5 :
  2. Validez en utilisant Ctrl + Shift + Entrée car c’est une formule matricielle
  3. Tirez la formule vers le bas
{=INDEX(ListeClients[Clients];EQUIV(0;NB.SI($D$4:D4;ListeClients[Clients]);0))}

où ListeClients[Clients] est la liste des valeurs B5:B15

On obtient ainsi une liste de valeurs uniques à partir des données de la colonne B.

Quelques explications

La formule NB.SI($D4$:D4; liste principale) compte combien de fois les éléments déjà dans la liste unique apparaissent dans la liste principale.

La syntaxe générale est : NB.SI(plage valeurs; critère).

Par ailleurs, en entrant comme critère de NB.SI une liste de valeurs, la formule NB.SI va considérer chaque élément de la liste de valeurs comme un critère, et retourner une liste des résultats du comptage selon chaque critère.

Par exemple, NB.SI( {“choux” ; “carotte” ; “poireau”} ; {“choux”;”choux”;”courgette”} ) retournera une liste de résultats de type {nb choux dans la liste ; nb choux dans la liste ; nb courgettes dans la liste}, soit {1;1;0} ici car dans la plage entrée, il y a un choux mais pas de courgette.

Note : En étendant la formule vers le bas, la liste unique s’étend, par exemple en D6 on aura la liste unique suivante : $D$4:D5.

Voici en détail l’évaluation de la formule en D6 :

La formule NB.SI compte combien de fois chaque élément de la liste principale apparaît dans la liste unique. Ici le client “APS” apparaît 1 fois dans la liste unique, puis tous les autres clients de la liste principale apparaissent 0 fois dans la liste unique D4:D5 :

Ensuite, la formule EQUIV (0 ; liste des occurences) cherche la position du premier 0 apparaissant dans la liste d’occurences, soit dès qu’un élément de la liste principale n’est pas dans la liste unique, on repère sa position à l’aide de EQUIV. La position du premier 0 ici est 2, cela signifie que la prochaine valeur unique de la liste principale se situe en position 2 :

Enfin, avec INDEX(liste principale ; position), on retourne la valeur de liste pour la position donnée. Ici en position 2, on a Rino, qui est bien la prochaine valeur unique après APS dans la liste principale.

Démonstration en image

EDIT : NOUVELLE FORMULE UNIQUE

Excel comprend désormais 8 nouvelles fonctions dynamiques matricielles qui exploitent directement les tableaux dynamiques pour résoudre des problèmes qui sont traditionnellement difficiles à résoudre avec des formules conventionnelles dont la formule UNIQUE.

La nouvelle formule UNIQUE permet de réaliser avec une formule plus simple la même chose que tout ce que nous avons vu plus haut.

Pour en savoir plus, lisez mon article ici.

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Comment extraire une liste de valeurs uniques par formule

Ce fichier Excel explique comment extraire une liste de valeurs uniques à partir d'un tableau de données.
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Maîtrisez l’essentiel des formules Excel

Enfin, transformez-vous en Expert Excel. Devenez un membre essentiel de votre équipe qui obtient des résultats rapidement. Obtenez une sécurité de l’emploi et une mobilité ascendante imbattable. Tout ce qu’il vous faut, c’est construire à partir de vos connaissances actuelles d’Excel, étape par étape. Pour cela, rejoignez le programme 👏👏 :

🥇 FORMULES EXCEL ULTIMES (2 MODULES OFFERTS)

Cliquez ici pour en savoir plus

 

Souscrivez à ma newsletter :

6 commentaires

  1. Véronique dit

    Merci beaucoup pour cet article et le contenu du site.
    Est-il possible d’ajouter la notion “jusqu’à la dernière valeur trouvée” ?
    La démonstration en image montre que la recherche est dynamique mais la zone des valeurs uniques est figée, répondant #N/A quand il n’y a plus de donnée mais sans agrandir la liste si le nombre de donnée est plus grand.
    Avez-vous une solution ?

  2. MIALY dit

    Merci pour les astuces, seulement comment faire pour débloquer la limite du formule. Je m’explique.

    Pour la liste unique extraite du tableau “Clients”, la formule ne fonctionne plus au-delà de la ligne 13. en effet, si on étire par le poignet de recopie de la formule vers le bas, la formule à partir de la ligne 14, au lieu de faire

    =INDEX(ListeClients[Clients];EQUIV(0;NB.SI($D$4:D13;ListeClients[Clients]);0)) elle fait

    =INDEX(ListeClients[Clients];EQUIV(0;NB.SI($D$4:D12;ListeClients[Clients]);0)) Il existe ainsi une erreur au niveau de la sélection de plage de cellules de la fonction NB.SI

    Par ailleurs, Excel indique que “Excel ne parvient pas à calculer cette formule. Un classeur ouvert contient une référence circulaire, mais il est impossible d’afficher les références érronnées;”.

    Y-a-t’il une possibilité d’utiliser VBA pour faire la même chose et éviter la limite que j’ai évoqué ci-dessus ?

    Merci de votre réponse

    • Lionel dit

      Bonjour Mialy, effectivement il semble que Excel “saute” parfois une cellule lors de la recopie incrémentée. Essayez de repartir d’une cellule plus en amont par exemple D10 puis tirez la formule vers le bas. Si Excel vous indique une erreur encore, modifiez la valeur mal incrémentée par exemple D14 par la bonne valeur D13, puis validez avec Ctrl+Maj+Entrée.
      Lionel

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *