Comment créer une liste déroulante à partir d'une colonne de données dans Excel

L'outil de validation des données de Microsoft Excel vous permet d'ajouter une liste déroulante à une cellule basée sur des données existantes dans une colonne. Cependant, comment cela fonctionne dépend de la question de savoir si les données source font partie d'une table Excel formatée. Jetons un coup d'œil à quelques exemples.

Utilisation d'une colonne dans une table Excel formatée

Imaginez que vous avez ce tableau Excel formaté nommé « Scores » contenant des noms, des nations et des scores de joueurs, et vous devez extraire certaines données sommaires. Plus précisément, dans la cellule H2, vous souhaitez créer une liste déroulante de toutes les nations répertoriées dans la colonne B, et dans les cellules I2 et J2, affichez les noms des joueurs et le score moyen, respectivement, selon le pays sélectionné.

Pour créer une liste déroulante, sélectionnez la cellule où vous voulez qu'elle soit (dans ce cas, cellule I2), et dans l'onglet Données du ruban, cliquez sur « Validation des données » dans l'option déroulante avec le même nom.

Ensuite, dans le champ Autoriser de l'onglet Paramètres, sélectionnez « Liste ».

Idéalement, vous pourriez taper:

=Scores(Nation)

Heureusement, il y a deux façons de contourner cela.

Méthode 1: référence aux cellules du tableau

Faites attention de ne pas sélectionner la colonne entière en cliquant sur la flèche qui apparaît lorsque vous survolez la lettre de référence de la colonne (dans ce cas, « B »), car cela sélectionnera toute la colonne, y compris l'en-tête de la ligne 1 et les cellules sous votre table. Vous avez réussi à sélectionner la plage correcte si seulement les cellules de la colonne dans la table sont entourées d'une ligne en pointillés.

Maintenant, après avoir cliqué sur « OK », lorsque vous sélectionnez la cellule H2, un bouton déroulant apparaît, que vous pouvez cliquer pour sélectionner une option dans la source. Remarquez également comment seules les valeurs uniques sont affichées dans la liste – en d'autres termes, Excel reconnaît les valeurs en double et les affiche une seule fois.

Les listes déroulantes de validation des données dans Excel adoptent le même ordre que la source. Dans cet exemple, pour commander alphabétiquement les nations, vous devrez trier les données source par la colonne B.

La beauté de l'utilisation de cette méthode est que si vous ajoutez plus de lignes au tableau Excel formaté, la source de validation des données s'ajuste automatiquement pour inclure la cellule ou les cellules supplémentaires.

Maintenant, vous pouvez utiliser la fonction filtrante dans la cellule I2 pour répertorier les joueurs du pays sélectionné:

=FILTER(Scores(Player),Scores(Nation)=H2)

Enfin, utilisez la fonction moyenne dans la cellule J2 pour afficher le score moyen des joueurs de cette nation:

=AVERAGEIF(Scores(Nation),H2,Scores(Total))

Méthode 2: Nommez la plage

La deuxième façon de créer une liste déroulante à partir d'une colonne dans une table formatée consiste à nommer la plage source.

Avant de lancer la boîte de dialogue Validation des données, sélectionnez toutes les cellules du tableau qui contiennent les valeurs que vous souhaitez inclure dans la liste déroulante, tapez un nom pour la plage dans la zone de nom dans le coin supérieur gauche de la fenêtre Excel et appuyez sur Entrée.

Pour garder les choses simples (et mémorables!), Le nom que vous entrez pour la plage sélectionnée doit être le même que l'en-tête de colonne.

=Nation

Comme pour la méthode précédente, Excel reconnaît que les données se trouvent dans une table formatée, donc elle étend automatiquement la plage si des données sont ajoutées à la ligne suivante.

L'utilisation de gammes nommées dans Excel présente de nombreux avantages. Par exemple, la dénomination des gammes rend votre classeur plus accessible aux personnes utilisant des lecteurs d'écran. En outre, vous pouvez rapidement naviguer vers des gammes nommées en les tapant dans la zone de nom ou en les sélectionnant dans les options lorsque vous cliquez sur la flèche vers le bas, et les gammes nommées peuvent également être utilisées dans les formules.

Maintenant, comme avec la méthode 1, vous pouvez utiliser les fonctions filtrantes et moyennes pour compléter l'extraction des données.

Cependant, cette fois, les formules peuvent être plus simples, car vous pouvez référencer la gamme que vous avez nommée « nation » sans faire référence au tableau dans lequel se trouve la plage.

Donc, pour le filtre dans la cellule I2, c'est:

=FILTER(Scores(Player),Nation=H2)

Et pour la moyenne dans la cellule J2, c'est:

=AVERAGEIF(Nation,H2,Scores(Total))

Utilisation d'une colonne dans un ensemble de données non formaté

Certains types de données – comme des tableaux renversés – peuvent être formatés sous forme de table Excel, il peut donc y avoir des moments où vous devez trouver un moyen de créer une liste déroulante à partir d'une colonne dans un ensemble de données non formaté.

Après avoir sélectionné la cellule qui contiendra la liste déroulante et cliqué sur « Validation des données » dans l'onglet Données du ruban, sélectionnez « Liste » dans le champ Autoriser.

Dans ce cas, taper:

=INDIRECT("B2:B"&COUNTA(B:B))

Comprend toutes les valeurs de la colonne B de la cellule B2 à la cellule B22 comme source.

Décomposons cette formule source pour voir comment elle fonctionne.

• = Indirect (: Cela indique à Excel que vous souhaitez que la source soit déterminée en utilisant une référence dynamique.
• « B2: B »: Plus précisément, la référence dynamique commence à la cellule B2 et se termine à une autre cellule de la colonne B.
• & Counta (b: b)): Cela compte toutes les cellules de la colonne B qui ne sont pas vides et ajoutent le total à la référence. Dans ce cas, 22 cellules de la colonne B contiennent des valeurs, ce qui transforme B2: B en B2: B22.

Ainsi, si vous ajoutez une autre ligne de données en bas, la fonction COUNTA récupérera ceci et informera la référence indirecte dans la règle de validation des données selon laquelle la source a étendu vers le bas d'une ligne supplémentaire.

L'utilisation d'une formule pour déterminer les valeurs à inclure dans la source de validation des données – plutôt que de simplement sélectionner la colonne entière – est considérée comme la meilleure pratique car elle empêche la ligne d'en-tête et les lignes vides d'être incluses dans la liste déroulante.

Maintenant que votre liste déroulante est prête à partir, utilisez les fonctions indirectes et COUNTA à côté de la fonction de filtre dans la cellule I2 et la fonction moyenne dans la cellule J2 pour compléter la feuille de calcul.

---

Les listes déroulantes ajoutées via l'outil de validation des données sont incroyablement puissantes et polyvalentes et peuvent être utilisées dans de nombreux scénarios dans Excel. Par exemple, vous pouvez utiliser des listes déroulantes pour rendre les graphiques réguliers dynamiques – un moyen infaillible d'impressionner vos amis et vos collègues.