Comment créer une liste déroulante dynamique avec des valeurs uniques (aucun doublons) sur Excel

Dans un précédent tutoriel, nous avions vu comment créer simplement une liste déroulante dans une cellule pour sélectionner des informations directement avec la souris. La liste source que nous avions utilisée était très simple, et nous allons ici voire comment procéder lorsque celle-ci est plus complexe et notamment lorsque celle-ci est composés d’éléments qui se répètent, que nous souhaitons ne récupérer qu’une seule fois.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation

Dans un précédent tutoriel, nous avions déjà eu l’occasion de découvrir comment créer une liste déroulante, permettant de sélectionner simplement des éléments à insérer dans une cellule à l’aide de la souris.

Outre le fait de gagner du temps, cette méthode permet par ailleurs de fiabiliser les données en évitant d’avoir à ressaisir celles-ci.

L’exemple que nous avions étudié était volontairement très simple et composé de seulement quelques éléments :

À présent, imaginons que nous partions de l’exemple suivant :

Nous retrouvons ici une liste des ventes réalisées par les commerciaux d’une entreprise dans différents pays.

L’objectif va alors être de récupérer les ventes réalisées par un commercial donné, pour un pays donné également :

Enfin, pour simplifier la récupération des différentes informations, nous allons vouloir que le nom du commercial ainsi que le nom du pays soit obtenu par un menu déroulant présentant chacune des listes.

Cela s’effectue très rapidement en suivant la méthode de la liste de validation de données que nous avons déjà étudiée dans le tutoriel précédent :

Mais l’inconvénient de cette méthode c’est que la liste présentée reprend l’ensemble des termes présentés dans la liste source, sans tenir compte de la présence ou non de doublons… et c’est justement ce que nous allons voir dans ce tutoriel !

2. La méthode vite fait, bien fait !

La première méthode que nous allons avoir est de la loin la plus rapide à mettre en place et ne nécessite que quelques clics :

• Pour commencer, nous sélectionnons les données de la liste des noms de commerciaux :

• Puis nous copions celles-ci (en cliquant sur le bouton Copier situé dans le menu Accueil du Ruban, ou en utilisant les touches [Ctrl]+[c] du clavier) :

• Et enfin, nous collons ces valeurs dans un autre endroit de la feuille (en cliquant sur le bouton Coller situé dans le menu Accueil du Ruban, ou en utilisant les touches [Ctrl]+[v] du clavier)

• Pour finir, nous supprimons les données saisies en double dans cette nouvelle base, pour cela, après avoir vérifié que les données sont toujours sélectionnées nous cliquons sur le bouton Supprimer les doublons du menu Données :

• Dans la fenêtre qui s’affiche, nous décochons la case Mes données ont des en-têtes puis nous validons en appuyant sur le bouton [OK] :

Excel nous informe alors avoir détecté des doublons dans la base qu’il a supprimé :

Ne reste alors plus qu’à modifier la liste utilisée pour créer la liste déroulante.

Pour cela, nous nous sélectionnons la cellule dans la laquelle nous souhaitons que le nom du commercial soit inséré puis nous ouvrons la fenêtre de Validation des données (Données > Validation des données > Validation des données…)

Après avoir mis-à-jour le champs Source, nous validons simplement en appuyant sur le bouton [OK] :

De cette manière, nous retrouvons bien la liste des commerciaux, sans aucun doublon :

Cette méthode, rapide à mettre en place souffre toutefois d’un gros inconvénient : elle est rigide, ce qui signifie que si nous sommes amenés à insérer un nouvel élément dans notre base source, celui-ci ne sera malheureusement pas ajouté à la liste.

3. La méthode dynamique !

Heureusement, pour corriger ce problème, il existe une autre méthode, légèrement plus complexe à mettre en place, mais qui a pour avantage d’être dynamique et de s’adapter automatiquement aux changements intervenants sur la base.

Pour commencer, nous allons insérer une nouvelle colonne directement à droite du tableau :

Pour nous allons étendre la largeur du tableau en saisissant simplement un nom à cette colonne (par exemple « Ordre ») :

Ensuite, nous allons nous placer sur la première cellule du tableau (D9 dans notre exemple), afin de saisir la formule suivante :

 =NB.SI(A$8:A9;[@Commercial]) 

Cette formule permet de dénombrer le nombre de fois que le commercial situé sur la ligne est répété dans la plage de cellules qui se trouve juste au-dessus (en partant des titres de colonne) :

Si nous analysons la formule qui a été dupliquée sur les cellules du dessous, par exemple dans la cellule D12, nous allons alors constater que le point de départ sera toujours le titre de la colonne.

Cela est rendu possible grâce au symbole dollar que nous avons inséré devant le chiffre 8 de la référence à la cellule A8 (pour tout savoir sur le symbole dollar et les différents types de référence de cellules, je vous invite à consulter le tutoriel dédié en cliquant ici).

Ensuite, nous allons chercher à savoir si le résultat est égal à 1, ce qui signifierait alors qu’il s’agit de la première fois que le nom du commercial est saisi dans la base. Et lorsque c’est effectivement le cas, alors nous recherchons la position actuel du noms de ce commercial grâce à la formule MAX() :

 =SI(NB.SI(A$8:A9;[@Commercial])=1;MAX(D$8:D8)+1) 

À présent, à chaque fois qu’un nouveau commercial apparaît dans la base, un compteur affichera son ordre d’apparition dans la colonne Ordre :

Nous n’avons pas précisé d’action à effectuer lorsque le test SI() retourne FAUX, c’est pourquoi nous avons des FAUX dans la colonne Ordre. Nous pouvons laisser la formule ainsi, cela ne va pas impacter la suite du tutoriel.

Maintenant que chaque commercial à un numéro qui permet de l’identifier, nous allons pouvoir récupérer une nouvelle liste avec chacun d’entre eux, classé par ordre d’apparition :

 =INDEX(_ventes[Commercial];EQUIV(LIGNE()-8;_ventes[Ordre];0))
 

Nous utilisons ici les formules INDEX et EQUIV qui permettent chercher une occurrence afin de récupérer le résultat correspondant dans une autre colonne (retrouvez ici toutes les informations sur la formule INDEX-EQUIV).

La petite subtilité dans cette formule vient du fait que nous recherchons le numéro du commercial en fonction de la ligne dans laquelle la formule est saisie (et que nous retrouvons en utilisant la formule LIGNE()) :

Ici, la formule est saisie dans la cellule K9, la formule LIGNE() va donc retourner comme valeur 9.

Pour obtenir le premier enregistrement, nous soustrayons donc 8 à ce résultat !

Ensuite, nous pouvons étendre la formule sur un grand nombre de cellules (en utilisant la poignée de recopie : le petit carré noir situé en bas à droite de la cellule K9) :

Cela étant fait, nous pouvons constater que lorsque tous les noms de commerciaux ont été récupérés, la formule INDEX-EQUIV retourne une erreur #N/A :

Pour régler ce problème, nous allons simplement l’imbriquer dans une formule SIERREUR(), qui permet d’effectuer un traitement lorsqu’une valeur retourne une erreur :

 =SIERREUR(INDEX(_ventes[Commercial];EQUIV(LIGNE()-8;_ventes[Ordre];0));"")
 

Ici, nous insérons simplement une cellule vide avec les doubles guillemets consécutives en cas d’erreur :

Ne reste ensuite plus qu’à mettre à jour la liste dans la validation des données :

Un dernier point avant de terminer ce tutoriel : si nous analysons la toute fin de la liste, celle-ci est constituée de nombreux champs vides ! Cela vient du fait que la liste duplique exactement la liste source que nous venons de créer :

Pour créer une liste dynamique, nous allons insérer directement la formule DECALER() dans la validation des données. Cette formule permet en effet de récupérer une plage de cellules pour une hauteur spécifiée en paramètre :

De cette manière, la liste commence donc à la cellule K9 et comprend le nombre d’éléments correspondant au maximum de la colonne D (dans laquelle nous retrouvons les positions de chaque commercial) :

La liste se termine avec le dernier commercial de la base, et nous pouvons simplement tester le dynamisme de la méthode en ajoutant un nouveau commercial en fin de base :

Et magie, celui-ci se trouve bien ajouté à la liste déroulante :

Notre liste est maintenant terminée !

Il convient évidemment de réaliser la même opération pour les noms de pays :

En sachant qu’en fonction du commercial sélectionné, tous les pays ne doivent pas forcément être présentés ! Mais cela fera l’objet d’un prochain tutoriel ;)