Monsieur Excel
Pour tout savoir faire sur Excel !

16 juin 2013

Liste dynamique sans répétition

Le problème de ce jour représente un véritable casse-tête. Vous disposez en colonne C d’une liste de noms dans laquelle il y a des répétitions. Vous souhaitez obtenir en colonne A, de façon dynamique, la liste de ces noms sans aucune répétition.

Comment faites-vous ? Essayez donc de trouver une solution par vous- même : il s’agit là d’un véritable challenge pour pro d’Excel …


La solution que je vais vous présenter provient de Roberto Mensa, qui a déjà été notre inspirateur à plusieurs reprises. Voici donc la formule entrée en A2 et recopiée ensuite vers le bas :

=index(Noms;petite.valeur(si(frequence(equiv(Noms;Noms;);equiv(Noms;Noms;));equiv(Noms;Noms;));ligne(A1)))

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Pour mieux la comprendre, nous allons la décortiquer…

equiv(Noms;Noms;) engendre le résultat {1;2;1;4;5;6;2;8} : c’est la liste des positions dans la liste des noms de la première occurrence de chaque nom rencontré.

Remarque 1 – Quand le troisième argument de l’equiv() est absent, sa valeur par défaut est « 0 ». Si l’on avait omis le dernier « ; », la valeur par défaut de ce troisième argument aurait été « 1 ». Le fait de ne pas saisir le « 0 » ne sert qu’à économiser un caractère !


frequence({1;2;1;4;5;6;2;8};{1;2;1;4;5;6;2;8}) est évalué en {2;2;0;1;1;1;0;1;0}. On obtient donc à chaque fois la fréquence du nom si le nom est original, et « 0 » autrement.

si({2;2;0;1;1;1;0;1;0};{1;2;1;4;5;6;2;8}) devient {1;2;FAUX;4;5;6;FAUX;8;FAUX}. On a maintenant la liste des positions des noms originaux.

Le reste est élémentaire. La fonction petite.valeur() sert à évacuer les résultats faux et ne garde que ceux avec un nombre. La fonction index reprend le nom indexé par la énième petite valeur.

Remarque 2 – Bien entendu, tout cela est encore mieux quand la définition de « Noms » est élastique. Mais cela ne devrait vous poser aucun problème si vous lisez ce blog de façon régulière…

Brillant, n’est-ce pas ?

3 Commentaire(s):

  • Excusez moi, en fait en regardant mieux je me suis un peu enflammé...
    La fonction retourne bien une seule occurrence de la liste. Pas uniquement les noms qui sont présent une seule fois.

    By Blogger cequad cequad, sur 2:42 PM  

  • Bonjour,

    Cela fait bientôt plus d'un an que je suis votre blog avec grand intérêt, que je recommande aux utilisateurs Excel que je rencontre lors de l'exercice de mon activité professionnelle pour améliorer leur pratique d'Excel.

    J'ai remarqué que la définition de la zone nommée a été un piège en terme de résultat lorsque j'ai cherché à faire cet exercice.

    Si la zone nommée démarre en cellule C2, il est indispensable pour le critère k de la formule PETITE.VALEUR() d'indiquer la valeur LIGNE(A1).

    Cependant, lors de la pratique de cet exercice, il m'a semblé plus "naturel" de nommer la zone de sélection en partant de C1 (habitude que j'ai prise avec l'usage de la fonction DECALER() et la sélection intégrale de la colonne). A ce moment donné, j'ai dû remplacer la valeur k par LIGNE(A2) lors de l'application de la formule en cellule A2 et le résultat obtenu devient identique.

    Bravo pour votre blog riche en enseignements sur la pratique d'Excel, cet outil aura toujours beaucoup à nous apprendre.

    By Blogger Guillaume Mordacq, sur 1:14 PM  

  • Bonjour

    Conscient que votre article vous permette d'être un exemple pédagogique pour illustrer la fonction FREQUENCE, on peut toutefois noter qu'il existe depuis bien longtemps sur le site d'Excelabo une formule qui me parait un tantinet plus simple :
    =INDEX(Noms;EQUIV(0;NB.SI($B$1:B1;Noms);0))
    A valider aussi en matricielle.

    Cordialement

    By Anonymous Anonyme, sur 3:04 PM  

Enregistrer un commentaire

<< Accueil