Tutoriel

Les listes sur Microsoft Excel et technique de regroupement

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.

Capture activer les liste de choix sur Excel
Figure 1 : Activer la liste déroulante de choix.

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

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

Capture afficher liste de choix dans une cellule
Figure 2 : Liste déroulante de choix en action.

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".

Capture Menu validation de données sur le tableur
Figure 3 : Menu "Validation de données" sur Microsoft Excel.

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

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

Capture Validation de données, choisir une liste
Figure 4 : Validation de données, autoriser une liste.

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

Valeurs comme source d'une validation de données
Figure 5 : Autoriser une liste de valeurs.

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.

Message d'erreur si la liste de validation échoue
Figure 6 : Alerte d'erreur validation de données.

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.

Capture liste de valeur déroulée

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.

Source d'une validation renseignée avec une plage
Figure 7 : Remplir une liste de choix à l'aide d'une plage de cellules.

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

Capture sélection liste peuplée avec une 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().

Source d'une validation renseignée avec une plage décalée
Figure 8 : Utiliser une plage de cellules décalée comme source d'une liste.

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.

Capture sélection liste peuplée avec une plage décalée

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".

Définir un nom pour une source externe
Figure 9 : Nommer une plage de cellules sur Microsoft Excel.

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.

Définir un nom qui référence un nom d'un autre classeur
Figure 10 : Faire référence à un nom d'un autre classeur.

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

Capture bouton utilisé dans formule

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

Nom externe comme source d'une liste de validation
Figure 11 : Utiliser un nom de cellule comme source d'une liste.

Limitation

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

Résultat liste de validation externe

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.

Boite de dialogue source de liste erronnée
Figure 12 : Alerte d'erreur, validation de données.

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.

Liste active selon un critère

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

Liste active en mode écriture

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

Définir la source d'une liste active
Figure 13 : Activer la validation suivant un critère.

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".

Liste qui filtre selon les caractères saisis

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.

Source d'une liste pour autocomplétion
Figure 14 : Validation de données auto-remplissage de 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".

Désactiver l'alerte d'erreur sur Microsoft Excel
Figure 15 : Désactiver l'alerte d'erreur dans les validations de donné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.

Fonction ligne de Microsoft Excel
Figure 16 : La fonction LIGNE() d’Excel.

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".

Calcul recherche chaine dans une autre
Figure 17 : Formule correspondance premier caractères d'une chaine.

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.

Utiliser la fonction d'Excel petite.valeur
Figure 18 : La fonction d'Excel 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);"")

Utiliser la fonction d'Excel index
Figure 19 : Retrouver une valeur à l'aide de la fonction INDEX().

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)

Autocomplétion version 2 comme source de la liste
Figure 20 : Remplir la liste de validation avec les valeurs filtrées.

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.

Liste autocomplétion en action

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".

Liste pour récupérer les valeurs qui contiennent
Figure 21 : Filtrer une liste sur les valeurs qui contiennent une chaine précise.

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

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

Formule pour caractère générique
Figure 22 : Formule d'extraction de caractères.

Filtrer une liste selon les valeurs d'une autre colonne

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

Filtre sur une autre colonne
Figure 23 : Filtrer une liste d'après les valeurs d'une autre plage.

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.

Fonction si pour filtrer sur une colonne

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.

Capture liste validation filtrée sur les doublons
Figure 24 : Liste de validation sans les doublons.

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.

Fonction nb.si pour filtrer les doublons
Figure 25 : Éliminer les doublons à l'aide de la fonction NB.SI().

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.

Liste en cascade, liste parente
Figure 26 : Filtrer des listes en cascade.

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.

Liste en cascade, liste enfant
Figure 27 : Fonction SI() de comparaison de valeurs.

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 st utilisée, avec $B$21 qui est l'adresse de la première liste de validation.

Liste enfant filtrée
Figure 28 : Liste filtrée depuis une autre.

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.

Liste sur table réflexive à 4 niveaux
Figure 29 : Filtrer des listes depuis une profondeur.

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 le classeur de démonstration.

Choisir un tableau

Liste avec tableau de noms et tableaux des plages
Figure 30 : Une liste de validation qui permet de choisir un tableau pour peupler une autre liste.

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.

Liste peuplée avec le nom des tableaux

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 des noms de plage
Figure 31 : Définir des noms de plages sur Microsoft Excel.

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.

Excel liste de validation avec les noms des plages
Figure 32 : Plage des noms de tableaux comme source de la validation.

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.

Excel liste de validation avec la plage choisie
Figure 33 : Réaliser une indirection à l'aide de la fonction INDIRECT().

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.

Vision d'ensemble de la feuille de calcul
Figure 34 : Liste renseignée avec les données de la bonne plage.

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.

>