Les listes sont importantes en informatique.

Elles assurent la cohérence de l'information.
C'est à dire qu'une information sera toujours saisie avec la même orthographe.

Elles deviennent indispensables lorsque l'on souhaite récupérer dans un tableau des données liées qui sont situées dans une autre table.

Les listes déroulantes de choix

Si l'on souhaite choisir une valeur qui existe déjà dans la colonne, le plus simple est d'afficher la liste de choix.

Dans la cellule où écrire la valeur utilisez le bouton droit de la souris.

Dans le menu contextuel, choisir l'option "Liste déroulante de choix...".

Sélectionner dans la liste la valeur souhaitée.

Validation des données

Pour suivre cette partie, vous devez savoir utiliser les "Validations de données".

Dans les divers exemples, j'utilise des adresses de cellules pour une meilleure compréhension.
Dans la réalité, il est plus judicieux d'utiliser des noms de cellules.

Rendez-vous à l'onglet "Données" et le menu "Validation de données".

Dans les options à la rubrique "Autoriser", choisir "Liste".

Le contenu de la liste se défini dans la zone "Source :".

Liste de valeurs

Il est possible d'écrire directement les valeurs de la liste en dur dans la zone "Source".

Les éléments de la liste seront séparés par un point-virgule (;).

L'ensemble des valeurs de la liste est limité à 256 caractères, point-virgule compris.

Les valeurs de la liste sont dites case-sensitive, c'est à dire qu'Excel fait la distinction entre majuscules et minuscules.

Si dans source j'ai écrit la valeur "oui" en minuscule, l'écrire en majuscule dans la liste provoque une erreur.

Il est bien sûr conseillé de ne mettre dans ce type de liste que des valeurs qui ne changent jamais (valeurs constantes), comme les jours de la semaine, la liste des mois, etc.

Liste peuplée avec une plage

C'est la façon la plus classique d’utiliser une liste de validation.

En restant positionné dans la zone "Source" de la fenêtre de validation, sélectionnez la plage qui peuplera la liste.

Il est possible et même souhaitable d'utiliser un nom de cellules plutôt que les adresses de la plage.

Plage décalée

Souvent la plage qui alimente la liste s’étend ou se réduit selon que vous ajoutiez ou supprimiez des données.

En pareil cas, il faut systématiquement refaire la liste pour prendre en compte les nouvelles données.

Bien sûr ce n'est pas réaliste et peu ergonomique.

Pour utiliser une liste décalée, je vais mettre en œuvre la fonction DECALER().

Vous trouverez des informations sur son fonctionnement dans l'article : Calcul avec les X dernières lignes.

Le but est de prendre toutes les données de la plage.

Pour se faire, on donne pour hauteur de la plage à considérer le résultat de la fonction NBVAL(), qui retourne le nombre de valeurs non vides de la plage.

La conséquence est qu'il ne doit pas y avoir de cellules vides dans la plage à prendre en compte.

Liste externe

Parfois, il est intéressant de peupler la liste avec des valeurs qui se trouvent dans un autre classeur.

Classeur source

J'appelle classeur source, celui qui contient les données qui doivent peupler la liste.

Dans l'exemple, ce classeur s'appellera, "liste-source.xlsx".

Classeur cible

C'est celui qui contient la validation de données.

Mise en œuvre

En premier lieu, il faut nommer la plage dans le classeur source.

La liste est nommée : "Source".

Ensuite dans le classeur cible, il faut créer un nom qui fasse référence au nom (source) créé dans le classeur "liste-source.xlsx".

J'utiliserais le nom Liste-Externe.

Pour faire référence à la plage servant de liste, il ne faut pas sélectionner la plage dans le classeur source.

La formule produite par Excel n'est pas correcte.

De plus Microsoft Excel n'utilise pas le nom de la liste, mais les adresses de la plage.

='[liste-source.xlsx]Feuil1'!$C$10:$C$13

Utiliser plutôt le bouton "Utiliser DsFormule" de l'onglet "Formules" qui produit la syntaxe suivante :

='liste-source.xlsx'!Source

Utiliser ce nom comme source de la liste dans la validation de données.

Limitation

Le classeur source doit être ouvert pour pouvoir utiliser la liste.

Exemples de filtres

Message d'erreur

Il est possible dans certaines circonstances que Microsoft Excel provoque un message d’erreur lors de la validation de la fenêtre de définition de la source.

Validez en cliquant sur le bouton « Oui ».

Activer la liste selon un critère

Dans certain cas, nous ne souhaitons pas que la liste soit utilisable selon certaines conditions.

Dans cet exemple, j'active la liste uniquement si une cellule contient une valeur précise.

Ici, la liste montre les valeurs uniquement si la cellule $D$15 contient la valeur "Écriture".

Dans les autres cas, la liste est inopérante.

Auto complétion

Lorsque la liste est longue, il peut être pénible de la faire défiler.

Ici, je propose de filtrer la liste sur les premiers caractères.

Si on saisit la lettre "A" dans la liste, elle est filtrée sur les données commençant par "A".

Si on saisit "AL", elle n'affiche que les valeurs commençant par "AL".

La liste est case-insensitive.

C'est à dire qu'écrire "AL" est identique à "al".

Formule :

=SI($B$15<>"";DECALER($B$3;EQUIV($B$15&"*";$B$3:$B$13;0)-1;;SOMMEPROD((STXT($B$3:$B$13;1;NBCAR($B$15))=TEXTE($B$15;"0"))*1));$B$3:$B$13)

$B$15 : Cellule qui contient la validation de données.

$B$3:$B$13 : Plage de cellules qui remplissent la liste.

Caractère étoile (*)

C'est un caractère générique spécifique à Windows et non Excel.

Il permet de retrouver des valeurs (par exemple un nom de fichier) quand on n'en connait qu'une partie.

Ainsi faire une recherche sur la chaine "pig*" retournera :

• pigeon ;
• pigeonnier ;
• pignon.

=NB.SI($C$4:$C$14;"m"&"*"), comptera le nombre de valeurs commençant par la lettre "m".

Ce caractère ne distingue pas les majuscules et minuscules et "m"&"*" est identique à "M"&"*".

Limitations

Dans l'onglet "Alerte d'erreur", il est nécessaire de décocher "Quand les données non valides sont tapées".

De plus, la plage qui peuple la liste doit être triée.

Une autre façon de filtrer

Retour sur l'auto complétion

La technique abordée plus haut possède une limite gênante. Si la liste n'est pas triée, elle ne fonctionne plus.

Je vais utiliser une autre technique qui lèvera cette limite.

Nous verrons par la suite que cette procédure offre un large éventail de possibilités.

Principe de fonctionnement

Calculer le numéro de ligne

Je rajoute une colonne au tableau. Cette colonne contiendra le numéro de ligne des données au sein du tableau.

C'est le numéro de la ligne en cours soit la fonction LIGNE() qui est utilisée.

À ce numéro j'enlève le numéro de ligne de l'entête du tableau, ce qui donne en cellule "D3", la formule =LIGNE(B3)-LIGNE(B$2).

Dans le calcul, les dollars sur les adresses de cellules ont leur importance.

L'adresse de la ligne d'entête doit être figée, alors que celle de la ligne en cours non.

B$2 : c'est l'adresse de l'entête du tableau (soit la ligne 2).

B3 : C'est la première ligne des données (soit la ligne 3).

La fonction fait le calcul : 3 - 2 soit 1 sur la première ligne de données.

Pour la seconde ligne de données qui est située en ligne 4, le résultat produit la valeur 2.

Pour la 8° donnée, la fonction retourne 6 (8 - 2).

Calcul source du filtre

Dans une autre colonne, je réalise le calcul qui servira de filtre.

Si le résultat correspond à mon critère, j'affiche le numéro de ligne calculée à l'étape précédente, sinon, je n'affiche rien.

Voici la formule pour la première ligne.

=SI(STXT($B3;1;NBCAR($B$15))=TEXTE($B$15;"0");$C3;"")

Avec l'adresse $B$15 qui correspond à celle de la zone de liste.

Avec la lettre "m" en $B$15, et la valeur "Maëva" en $B3 :

STXT($B3;1;NBCAR($B$15)) donne "M".

TEXTE($B$15;"0") donne "m".

Je rappelle que Windows ne fait pas de distinction majuscule/minuscule.

Regrouper les valeurs

Maintenant que j'ai récupéré les valeurs qui répondent à mon critère, il faut les regrouper pour en faire une liste.

Regrouper les numéros de lignes

Dans une nouvelle colonne, il faut regrouper, les numéros de ligne valides en haut du tableau.

Comme cette colonne va dénaturer le tableau (informations incohérentes sur la même ligne), je laisse une colonne vierge avant celle-ci.

Petite.Valeur

=SIERREUR(PETITE.VALEUR($D$3:$D$13; C3); "")

Retourne la n-ième (2°, 3°, etc.) plus petite valeur.

Dans notre cas, elle retourne sur la première ligne, le plus petit numéro de ligne renseigné à la colonne précédente.

Sur la seconde ligne, le numéro de ligne de la deuxième plus petite valeur (soit l'avant dernière) et ainsi de suite.

Récupérer la valeur correspondante au numéro de ligne

C'est le rôle de la fonction INDEX(...) que de retrouver des valeurs en fonction d'un numéro de ligne.

=SIERREUR(INDEX($B$3:$B$13;$F3;0);"")

Créer la liste

Il reste à utiliser la liste créée à l'étape précédente, pour ne prendre que les lignes qui contiennent une valeur.

Ici, rien de neuf, j'utilise la fonction DECALER() pour ne prendre en compte que les valeurs qui répondent à mon critère.

=DECALER($G$3;0;0;NBVAL($G$3:$G$13)-NB.SI($G$3:$G$13;"")+1;1)

Les limitations ne sont plus d'actualité. Le tableau n'a plus besoin d'être trié et l'alerte d'erreur peut rester cochée.

Filtrer les listes sans limite

Cette technique permet toutes les fantaisies.

Il suffit de modifier la formule qui calcule si les valeurs du tableau correspondent au critère.

Le reste des opérations est inchangé.

Filtrer les valeurs qui contiennent

Dans l'exemple précédent, je filtre les valeurs qui commencent par une ou des lettres précises.

Mais si je veux trouver les valeurs qui contiennent une chaine particulière ?

Quand je saisi « au » dans la liste, je veux filtrer sur "Paul, Aurore, Aude".

Le fonctionnement est identique au cas précédent.

Il suffit de changer la formule dans la colonne "D".

Filtrer une liste selon les valeurs d'une autre colonne

Je ne veux afficher dans la liste, que les clients actifs (colonne "Actif" = VRAI).

Dans la colonne "E", je teste si la valeur de la colonne "Actif" correspond au critère "VRAI" situé dans la cellule $C$15.

Si la valeur est "VRAI", j'affiche le numéro de ligne correspondant, sinon rien.

La formule est simpliste puisque c’est juste une fonction SI().

Filtrer les doublons

Sur le même principe, je veux éliminer les doublons de la liste.

Au lieu de chercher si une valeur correspond au critère, je filtre dans la colonne "D" les valeurs dont le résultat de la fonction NB.SI() est supérieur à 1.

Liste en cascade

Parfois, nous souhaitons filtrer une liste selon la valeur sélectionnée dans une autre.

Filtrer sur le même tableau

Je souhaite filtrer une liste en fonction d'un élément choisi dans une autre liste.

Quand je choisi une valeur dans la liste des villes, la liste des clients affiche ceux qui habitent dans cette ville.

Là aussi j'emploi la même technique, et il suffit dans la colonne "G", de ne prendre que les valeurs qui correspondent à l'élément sélectionné dans la première liste.

C’est une simple comparaison de deux valeurs, et ce cas n’est qu’une variation de celui vu précédemment.

Donc là aussi, c’est la fonction SI() qui est utilisée, avec $B$21 qui est l'adresse de la première liste de validation.

Réflexive

Un tableau réflexif est un tableau dont une colonne utilise les valeurs d'une autre colonne du même tableau.

Si vous souhaitez plus d'informations, je vous propose de consulter l'article sur la réflexivité.
Bien qu'il soit orienté Microsoft Access, il énonce le concept.

Il y a dans le tableau une colonne qui calcule la profondeur de la valeur (niveau 1 - 2 - 3 - etc.).

Il faut créer autant de zones de calcul que de profondeur.

S'il y a 4 niveaux dans la liste, je vais réaliser les calculs 4 fois.

Pour le premier niveau, je reprends les valeurs dont la colonne "Parent" est vide.

Pour les autres niveaux, je prends les valeurs qui ont pour parent l'élément de la liste du niveau précédent.
Sauf si la liste précédente n'est pas renseignée.

Vous trouverez un exemple complet dans l'article « Relations réflexives avec Microsoft Acccess ».

Choisir un tableau

Parfois, on ne sait pas à l'avance les valeurs de quelle plage ou tableau on souhaite utiliser dans une validation de données.

Une liste permettra de choisir le tableau qui servira de source à une seconde liste.

En premier lieu, créez les différentes listes nécessaires.

Nommer les listes

Dans cet exemple, j'ai nommé les 3 listes ("Électroménager", "Alimentation"; "Loisirs").

Le nom doit être choisi avec soin.

Il ne peut contenir d'espace ou de caractères spéciaux hormis l'underscore (_).

Liste de choix du tableau

Créer un tableau (comme plus bas le tableau des familles), renseigné avec les noms donnés à l’étape précédente.

La première liste de validation sera renseignée avec cette plage.

Liste filtrée sur le bon tableau

Créer une seconde liste de validation.

Elle recevra les valeurs du tableau choisi à l'étape précédente.

Sa source sera renseignée avec la fonction INDIRECT().

Cette fonction prend en paramètre une adresse de cellule sous forme de texte.

Ensuite elle affiche la valeur à cette adresse.

Ainsi INDIRECT("A1"), affiche la valeur contenue dans la cellule "A1".

Dans notre cas, l'argument sera la plage nommée choisie dans la première liste qui est située en $C$5.

Conclusion sur les listes de validation

Comme on le voit, la technique de regroupement permet à peu près tous les types de filtres que vous pourriez imaginer.

Elle n’est pas obligatoirement liée aux listes de validation et peut-être utile en toutes circonstances.

Elle fait intervenir essentiellement 3 fonctions :

• LIGNE() ;
• PETITE.VALEUR() ;
• INDEX().

On rajoute la fonction DECALER() si on veut utiliser le filtre comme source d'une liste.

Même si la procédure peut sembler lourde, elle est toujours identique et seule la formule de filtre est à modifier.

Téléchargement

Vous pouvez utiliser le classeur exemple mis à disposition.
Il a été réalisé à l’aide de Microsoft Excel 2010 en version 32 bits avec Microsoft Windows 7 lui aussi en 32 bits.

---

Achetez mon ouvrage !

Mon PDF « Créer un planning perpétuel sur Microsoft Excel sans macro » est disponible à la vente.

Pour plus d’informations, rendez-vous sur la page dédiée.

---