Excel: Créer des listes déroulantes dynamiques et sans doublons

Publié le 20 novembre 2014
par Sophie Marchand M.Sc.
Power Query - Liste validation transformation 3

Si vous travaillez avec des bases de données dans Excel et que vous souhaitez créer des listes déroulantes dynamiques et sans doublons, à partir d’une de ces bases de données, différentes solutions s’offrent à vous. Le présent article vous montre comment procéder avec Power Query et la validation de données.

 

Base de données

Pour illustrer comment créer des listes déroulantes dynamiques et sans doublons, à partir d’une base de données, nous allons utiliser la base de données suivante, qui regroupe les ventes journalières d’un site web (canal #1). Dans un premier temps, si ce n’est pas déjà fait, nous devons convertir notre base de données sous forme de tableau. Pour savoir comment faire, je vous invite à relire l’article: Découvrez la magie des tableaux Excel.

Power Query - Liste de validation

 

À partir de cette base de données, nous allons créer une liste de #skus et une liste de dates, qui ne contiendront aucun doublon et qui seront présentées en ordre croissant de valeurs.

 

PowerQuery: Importation d’un tableau Excel

À l’aide de Power Query, nous allons importer notre tableau Excel. Il s’agira ensuite d’appliquer quelques transformations à ce tableau Excel afin de créer une liste avec les #skus.

Power Query from table

 

PowerQuery: Étapes de transformation

 

Power Query: Transformation #1

Afin d’obtenir une liste de #skus sans doublons, nous allons enregistrer 4 étapes dans Power Query. La première étape consiste simplement à importer la table de données qui réside dans notre fichier Excel.

Power Query - Liste validation transformation 1

 

Power Query: Transformation #2

La deuxième étape de transformation consiste à supprimer les colonnes excédentaires, pour ne conserver que la colonne avec les #skus.

Power Query - Liste validation transformation 2

 

Power Query: Transformation #3

La troisième étape de transformation consiste à trier les # skus en ordre croissant.

Power Query - Liste validation transformation 3

 

Power Query: Transformation #4

La quatrième étape de transformation consiste à supprimer les doublons.

Power Query - Liste validation transformation 4

 

Résumé des étapes de transformation

Comme nous avons enregistré ces 4 étapes de transformation avec Power Query, nous pourrons facilement, plus tard, mettre à jour la liste, pour tenir compte des nouveaux #skus qui feront leur apparition dans le temps.

Power Query - Liste validation sommaire transformations

 

Liste avec les données sans doublons

Dans un onglet séparé, nous retrouverons donc la liste des #skus, sans doublons et en ordre croissant.

Liste skus

 

Liste déroulante avec les dates

Nous utiliserons la même technique pour créer une liste de dates uniques, en ajoutant une étape, soit celle de convertir le format des données en format “date”.

Power Query - Liste validation dates

 

Zone de calculs

Supposons que nos deux listes servent à alimenter une zone de calculs, comme celle présentée ci-bas. Nous souhaitons ainsi qu’un utilisateur puisse choisir une date et un #sku pour connaître le total des ventes.

Power Query calculs

 

Pour obtenir une liste déroulante avec les #skus uniques, nous utilisons simplement l’option “liste” disponible dans la validation de données. Si vous souhaitez en apprendre davantage sur la validation de données, vous pouvez relire l’article: Astuce Excel: Validation de données (Data validation). Dans notre exemple, vous noterez que les #skus vont jusqu’au #30.

Liste de skus

 

 

Ajouter dynamiquement des données à la liste déroulante

Ajoutons maintenant une nouvelle ligne de données dans notre base de données, avec le #sku 31.

Nouveau sku

 

Rafraîchissons maintenant la requête Liste_skus.

 

Power Query refresh

 

Nous pouvons maintenant observer le nouveau #sku dans la liste.

Liste validation nouveau sku

 

Nous pouvons également le voir apparaître dans notre liste déroulante, puisque celle-ci est liée à une base de données (à une colonne), sous forme de tableau.

Power Query ajout liste validation

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

2 réflexions sur “Excel: Créer des listes déroulantes dynamiques et sans doublons”

  1. Bonjour, voici mon problème. Dans mon dossier excel, j’ai une feuille de calcul du nom de DEO avec 2 tableaux côte à côte. Dans le 2ème tableau, celui où j’ai un souci, j’ai 5 colonnes. La 1ère colonne en R. J’ai mis une liste déroulante en R7 avec 4 secteurs. “Secteur Nord, secteur Centre, secteur Sud, Fixe”. J’ai reproduit la liste déroulante dans la colonne R, à savoir de R7 à R17.
    Mes secteurs ont chacun une liste de plusieurs noms, dans la 2ème feuille de calcul que j’ai nommé “NomCam”. il s’agit de ma 1ère base de données. Ce que je voudrais, c’est lorsque je clique dans la liste déroulante en R7 et que je choisis par exemple “Secteur Nord”, je souhaite que ça me renvoie vers sa liste et que je puisse sélectionner le nom qui m’intéresse et une fois choisie, que la liste déroulante soit invisible ou laisse la place à mon choix. J’ai utilisé des formules que chatgpt m’a donné mais ça n’a pas le résultat que je veux.
    Pouvez-vous m’aider ?

    1. Bonjour,
      Vous pourriez consulter l’article suivant pour vous aider:
      https://www.lecfomasque.com/fonctions-matricielles-dynamiques-dans-excel-listes-dependantes/
      Vous pouvez également poser votre question sur notre forum avec un exemple dans un fichier Excel des données à partir desquelles vous voulez faire vos menus et le résultat souhaité.
      Un membre de la communauté pourra certainement vous aider.
      https://www.lecfomasque.com/forums/

      Au plaisir,
      Kim

Laisser un commentaire

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

Retour en haut