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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - 034 Liste déroulante sans doublons - 01

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

Excel formation - 034 Liste déroulante sans doublons - 02

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 :

Excel formation - 034 Liste déroulante sans doublons - 03

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 :

Excel formation - 034 Liste déroulante sans doublons - 04

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 :

Excel formation - 034 Liste déroulante sans doublons - 05

  • 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) :

Excel formation - 034 Liste déroulante sans doublons - 06

  • 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)

Excel formation - 034 Liste déroulante sans doublons - 07

  • 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 :

Excel formation - 034 Liste déroulante sans doublons - 08

  • 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 formation - 034 Liste déroulante sans doublons - 09

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

Excel formation - 034 Liste déroulante sans doublons - 10

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…)

Excel formation - 034 Liste déroulante sans doublons - 11

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

Excel formation - 034 Liste déroulante sans doublons - 12

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

Excel formation - 034 Liste déroulante sans doublons - 13

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 :

Excel formation - 034 Liste déroulante sans doublons - 14

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

Excel formation - 034 Liste déroulante sans doublons - 15

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) :

Excel formation - 034 Liste déroulante sans doublons - 16

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.

Excel formation - 034 Liste déroulante sans doublons - 17

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 :

Excel formation - 034 Liste déroulante sans doublons - 18

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()) :

Excel formation - 034 Liste déroulante sans doublons - 19

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) :

Excel formation - 034 Liste déroulante sans doublons - 20

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 :

Excel formation - 034 Liste déroulante sans doublons - 21

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 :

Excel formation - 034 Liste déroulante sans doublons - 22

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

Excel formation - 034 Liste déroulante sans doublons - 23

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 :

Excel formation - 034 Liste déroulante sans doublons - 24

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 :

Excel formation - 034 Liste déroulante sans doublons - 25

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) :

Excel formation - 034 Liste déroulante sans doublons - 26

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 :

Excel formation - 034 Liste déroulante sans doublons - 27

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

Excel formation - 034 Liste déroulante sans doublons - 28

Notre liste est maintenant terminée !

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

Excel formation - 034 Liste déroulante sans doublons - 29

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 ;)

 



Articles qui pourraient vous intéresser

COMMENT CRÉER UNE BARRE DE PROGRESSION EN VBA A RÉUTILISER DANS TOUS VOS CLASSEURS EXCEL !

Comment calculer les jours fériés sur Excel

Comment convertir un fichier Excel en PDF Excel2pdf

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

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.