Listes déroulantes et recherches dynamiques dans Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Listes déroulantes et recherches dynamiques dans Excel

Par liste déroulante dynamique, on entend liste capable de faire évoluer son contenu en fonction de la source de données. Si des valeurs sont ajoutées à la source, la liste déroulante doit s'adapter et les intégrer. Cela signifie qu'elle ne peut pas être bâtie sur une plage de cellules aux bornes fixes. Par recherche dynamique, on parle de recherche réalisée par le biais de la fonction RechercheV() dans un tableau où là encore, les bornes ne sont pas définies à l'avance. Cela signifie que si le tableau évolue parce que des données lui sont ajoutées, la recherche sera capable de les prendre en compte pour récupérer le nouveau contenu correspondant. Pour information, les listes déroulantes et la fonction RechercheV() sous leur forme classique, sont traitées par des supports sur le site.

Cas pratique

• Télécharger le classeur listes-recherches-dynamiques.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,
• Cliquer sur l'onglet facturation pour activer la première feuille du classeur,

Il s'agit d'une facturation client Excel réalisée au titre d'une formation. Elle est automatisée dans le sens où une liste déroulante, bâtie sur le catalogue de la feuille articles, permet de sélectionner une référence. En fonction de cette référence, les désignations et prix unitaires sont directement importés dans la facture. Il n'est nul besoin de les saisir. Si bien qu'il ne reste plus à l'utilisateur qu'à entrer la quantité achetée et les montants se calculent automatiquement.


Pour nous simplifier la vie, sur les opérations à venir, nous allons remonter le tableau de la feuille articles en première ligne. Vous comprendrez mieux pourquoi, lorsque nous aurons besoin d'adapter la RechercheV notamment.

• Cliquer sur l'onglet articles pour afficher sa feuille,
• Sélectionner les 5 premières lignes par leur étiquette à gauche de la feuille Excel,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel qui apparaît, choisir Supprimer,

Vous supprimez ainsi d'un coup les cinq premières lignes de la feuille. Le tableau remonte et la cellule Code article doit désormais être positionnée en C1.


La liste déroulante
Telle qu'elle est conçue pour l'instant, cette liste ne permet donc pas de prendre en compte les nouveaux contenus. C'est ce que nous allons vérifier.

• Ajouter une nouvelle référence au catalogue comme l'illustre la capture ci-dessous :

• Revenir sur la feuille Facturation,
• Cliquer sur la liste déroulante dans une cellule de la colonne Code article,

Même si vous la faites défiler jusqu'en bas, vous n'y trouvez pas cette nouvelle référence B010. Notre liste n'est en effet pas dynamique et nous souhaitons corriger ce souci.


Pour ce faire, nous allons commencer par supprimer cette liste, pour ensuite la reconstruire de la bonne façon.

• Commencer par supprimer tous les codes articles ainsi que toutes les quantités saisies,

Vous devez retrouver une facturation vierge de contenu.

• Sélectionner toutes les cellules de la colonne Code article soit C6:C26,
• Activer le ruban Données et cliquer sur le bouton Validation de données,
• Dans la zone Autoriser, choisir Tout et valider par Ok.

Vous supprimez ainsi la liste déroulante précédemment construite. Nous allons la recréer de façon à ce qu'elle soit désormais dynamique. Pour ce faire, nous avons besoin d'une plage nommée sur laquelle nous allons bâtir une formule qui permettra à cette plage d'évoluer avec son contenu. Comme la liste sera bâtie sur cette plage, si la plage évolue, le contenu de la liste évolue.

• Activer la feuille articles,
• Sélectionner tous les codes articles, soit C2:C11,
• Taper le terme references dans la zone Nom en haut à gauche de la feuille,
• Valider en enfonçant la touche Entrée,

Vous venez d'attribuer le nom references à la plage de cellules des codes articles sélectionnés.

• Revenir sur la feuille facturation,
• Sélectionner de nouveau la première colonne de la facture, C6:C26,
• Dans le ruban Données, cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue qui suit, fixer la zone Autoriser sur Liste,
• Activer ensuite la zone Source en cliquant dedans,
• Puis cliquer sur l'onglet de la feuille articles,
• Et sélectionner tous les codes article du catalogue, C2:C11,

Vous remarquez que la sélection prend le nom references dans la boîte de dialogue.

• Valider par Ok.

De retour sur la facture, la liste déroulante est de nouveau proposée. Cependant, à ce stade, elle n'est pas encore dynamique. En effet, si vous ajoutez une nouvelle référence au catalogue, vous constatez qu'elle n'est pas intégrée au contenu de la liste. C'est ce à quoi nous allons nous atteler maintenant que la liste a été recréée sur une plage de cellules avec un nom.

• Activer le ruban Formules,
• Cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue, sélectionner la plage references,
• Dans la zone de saisie, tout à fait en bas, remplacer l'expression par la formule suivante :

=DECALER(articles!$C$2;0;0;NBVAL(articles!$C:$C)-1)

En fait, vous commencez par saisir la fonction =DECALER(, puis vous sélectionnez le premier code article sur la feuille articles. Excel se charge d'encadrer la cellule de dollars pour la figer et de la préfixer du nom de la feuille. Vous tapez ensuite ;0;0;nbval(. Et vous sélectionnez la colonne des codes article par l'étiquette C de colonne. Là encore, Excel se charge d'ajouter les dollars et le nom de la feuille en préfixe. Vous fermez la parenthèse de la fonction nbval(), vous tapez -1 et vous fermez la parenthèse de la fonction Decaler().

• Vous validez la boîte de dialogue.

De retour sur la facture, vous constatez que la nouvelle référence est intégrée à la liste. Nous venons de créer une liste déroulante au contenu dynamique, évolutif.

Fonction Decaler() - Decryptage
La fonction Excel Decaler() permet, comme son nom l'indique de déplacer les bornes d'une plage de cellules de références, selon les informations qu'on lui donne, un critère ici en l'occurrence. Elle a besoin, en premier paramètre de connaître la cellule de départ de la plage de cellules, on lui indique donc le premier code article C2. Les deux paramètres qui suivent sont fixés à zéro (;0;0;). Ils permettent à l'origine de décaler un tableau en ligne ou en colonne. Ce n'est pas ce que nous voulons. Nous souhaitons l'agrandir en hauteur, déplacer sa borne inférieure vers le bas si du contenu est ajouté. C'est ce que permet de faire le quatrième paramètre. Nous lui indiquons de déplacer la borne inférieure de la plage tant qu'il y a du contenu grâce à la fonction NBVAL() qui permet de compter toutes les cellules non vides. Ainsi dès qu'une nouvelle référence est ajoutée, NBVAL() en compte une de plus, directement intégrée grâce à la fonction DECALER(). NBVAL() nous repousse une cellule trop bas, donc nous lui retranchons 1 (-1) et la fonction est terminée. La liste déroulante, grâce à la plage nommée et à la formule bâtie sur son nom est désormais dynamique. Mais si vous choisissez la nouvelle référence b011 dans la liste, vous remarquez qu'elle provoque une erreur dans les colonnes Désignation et Prix unitaire de la facture. En effet la RechercheV() qui importe ces informations selon le code article choisi, n'est quant à elle toujours pas dynamique. Vous allez voir que le principe est similaire.

La RechercheV dynamique - Matrice aux bornes non définies
Les fonctions RechercheV réalisent la recherche du code article dans le tableau de la feuille articles, selon des bornes fixes : articles!$C$1:$E$10. C'est le deuxième argument de la fonction RechercheV qui définit le tableau de recherche. Tel qu'il est saisi dans la formule, tout ce qui est au-delà de la ligne 10 n'est pas pris en compte. Donc si une nouvelle référence est ajoutée, elle n'est pas trouvée. La fonction Decaler() sur ce deuxième argument, va nous permettre, une fois de plus, de déplacer la borne inférieure du tableau de recherche, s'il y a du contenu à considérer.

• Sélectionner toute la plage de cellules des Désignations, D6:D26,
• Enfoncer la touche F2 du clavier,

Vous passez ainsi en saisie de la formule pour la première cellule de la plage.

• Depuis la barre de formule, sélectionner intégralement le deuxième argument de la fonction RechercheV(), soit articles!$C$1:$E$10,

C'est cette plage de cellules que nous devons rendre dynamique si son contenu évolue.

• Saisir la fonction Decaler en ouvrant la parenthèse : decaler(,
• Cliquer sur la feuille articles et sélectionner la première ligne du tableau, C1:E1,
• Enfoncer la touche F4 du clavier pour figer cette plage,

Il s'agit de la ligne de référence du tableau, celle de départ. Elle ne doit donc pas bouger en reproduisant la formule sur d'autres cellules de la facture. La touche F4 du clavier permet donc de figer cette plage. Vous remarquez la présence de symboles Dollars qui encadrent cette plage. La formation sur les références absolues explique soigneusement cette technique cruciale d'Excel.

• Taper ensuite comme précédemment les arguments suivants : ;0;0;,

Il nous reste maintenant à définir selon quel critère la borne inférieure du tableau doit se placer. Comme toujours, tant qu'il y a des cellules non vides. C'est une fois encore la fonction NBVAL() qui permet de vérifier ce critère en comptant les cellules non vides, sur plusieurs colonnes à la fois.

• Saisir nbval(,
• Dans la parenthèse, sélectionner les trois colonnes du tableau par leurs étiquettes : C:E,
• Les figer en enfonçant la touche F4 du clavier,

Ces colonnes ne disposent pas d'indices de lignes. Nous figeons leur déplacement sur la gauche ou sur la droite mais pas vers le bas, puisqu'il s'agit justement de comptabiliser les nouvelles saisies sur les lignes situées en dessous du tableau.

• Fermer la parenthèse de la fonction nbval,
• Taper -1 pour la même raison que précédemment,
• Fermer la parenthèse de la fonction Decaler() qui englobe le nbval(),
• Valider la formule par CTRL + Entrée pour la répercuter sur l'ensemble des cellules présélectionnées,

Comme vous le remarquez, cette fois, la désignation de la nouvelle référence b011 est importée. La rechercheV a adapté les bornes du tableau pour considérer les nouvelles saisies et rapatrier leur contenu.


Il convient maintenant d'adapter la formule de la colonne P.U – H.T. Le calcul est exactement le même. Seul le troisième paramètre de la fonction RechercheV change. En effet, le prix unitaire se trouve dans la troisième colonne du tableau de recherche et non en deuxième colonne comme la désignation. Nous lui indiquons donc 3 au lieu de 2. Pour adapter cette formule, il suffit simplement de remplacer l'actuelle plage définissant le tableau, par celle qui s'adapte avec la fonction Decaler().

• Sélectionner tous les prix unitaires : F6:F26,
• Enfoncer la touche F2 du clavier pour activer la saisie de la formule,
• Depuis la barre de formule, remplacer le deuxième argument (articles!$C$1:$E$10) de la rechercheV par : DECALER(articles!$C$1:$E$1;0;0;NBVAL(articles!$C:$E)-1),
• Valider par CTRL +Entrée pour répercuter le calcul sur l'ensemble de la plage de cellules,

Le prix unitaire pour la nouvelle référence est lui aussi désormais importé. Nos deux RechercheV() étant désormais dynamiques, ainsi que la liste déroulante, notre facturation est ainsi très fonctionnelle et professionnelle. Nous allons néanmoins continuer de tester le procédé en ajoutant une nouvelle référence au catalogue.

• Activer la feuille articles,
• Ajouter la référence B012 dans la colonne des Codes articles,
• Saisir la désignation Carte SD 512 Mo,
• Saisir le prix unitaire de 21,
• Revenir sur la feuille Facturation,
• Sélectionner la nouvelle référence avec la liste déroulante,

Sa désignation et son prix unitaire sont automatiquement importés. C'est parfait tout est dynamique et évolutif.


Applications - decaler()
Comme vous l'avez compris, la fonction qui permet de rendre dynamique une opération est la fonction Decaler(). C'est elle qui permet d'ajuster les bornes d'une plage de cellules en fonction de son contenu. Donc, dès qu'une formule Excel fait référence à une plage de cellules, cette dernière peut devenir dynamique en s'adaptant au nouveau contenu, grâce à la fonction decaler(). Voyons cela avec la fonction sans doute la plus utilisée dans Excel, la somme.

• Ajouter une nouvelle feuille au classeur,
• Réaliser le petit tableau présenté par la capture ci-dessous :

Dans la colonne B, figurent des valeurs à sommer. En D3, nous réalisons une somme classique sur les valeurs de ce tableau.

• Ajouter une valeur numérique en B7,

Vous le constatez et vous le saviez, ce nombre n'est pas pris en compte par la somme dont le résultat est invariant car conçue sur une plage de cellules fixes. Nous devons donc modifier cette plage à l'aide de la fonction decaler() pour que toutes les nouvelles saisies effectuées sur les lignes du dessous soient prises en compte.

• Sélectionner la cellule D3,
• Enfoncer la touche F2 pour passer en mode saisie,
• Sélectionner la plage de cellules de la somme,
• Saisir decaler( à la place,
• Sélectionner la première cellule des valeurs numériques, soit B3,
• La figer avec la touche F4 et saisir comme toujours ;0;0;,
• Taper nbval( et cliquer sur l'étiquette de colonne du tableau soit, B,
• Figer avec F4, fermer la parenthèse, taper -1 et fermer la seconde parenthèse,

Une fois le calcul validé, le résultat ne change pas mais la somme est censée être devenue dynamique.

• Taper une nouvelle valeur à la suite des valeurs à additionner,

Dès que vous enfoncez la touche Entrée, vous constatez que la somme se met à jour en intégrant la nouvelle valeur dans l'addition. Nous avons donc bien créé une somme automatique dynamique.