Liste déroulante dynamique dans Excel 7

 dans Créez vos outils / Trucs et astuces par

Difficulté: ToqueToqueToque Avancé

Ustensiles: Excel

Ingrédients: DECALER, EQUIV, NBVAL, SOMMEPROD, SI, STXT, TEXTE, Validations de données

Bases requises: Fonctions avancées d’Excel

 

Liste déroulante dynamique dans Excel

Pour le moment, j’ai partagé un article sur le contrôle de gestion (résultats 2011 de l’observatoire international du contrôle de gestion), un sur SAP (Transaction OY18), un sur les interactions SAP & Excel, il ne reste donc plus qu’à partager un article sur Excel. 
Pour cet article il s’agit d’un cours un peu technique puisque nous allons utiliser les fonctions suivantes dans Excel:
– Champs nommés
– Fonction DECALER
– Fonction EQUIV
– Fonction NBVAL
– Fonction SOMMEPROD
– Fonction SI
– Fonction STXT
– Fonction TEXTE
– Validations de données
La liste s’arrête là !!! 
 
L’objectif de ce petit cours est le suivant: créer une liste déroulante dynamique. Dans notre exemple, nous avons une liste de prénoms (plus d’une centaine) et nous souhaitons sélectionner dans une liste déroulante un prénom. Il est pénible à chaque fois de faire descendre la liste pour trouver le bon prénom. Il serait tellement plus simple de saisir la première lettre, voire les deux ou trois premières lettres puis de sélectionner parmi les prénoms commençant par ces lettres; un peu comme la recherche intuitive dans Google !!!
J’ai d’ailleurs eu l’occasion de rendre un grand service avec cette fonctionnalité pour une collègue qui gère le CE qui en avait marre de chercher le salarié dans une liste déroulante !
 
Voici le lien vers le fichier EXCEL comprenant la fonctionnalité et les explications pour réaliser cette « prouesse » : Télécharger le fichier
 
Mais je vais partager les explications dans cet article.


FONCTION DECALER
Avant tout, voici un petit schéma qui explique la fonction DECALER, fonction centrale de la méthodologie.

DECALER(réf, lignes, colonnes, [hauteur], [largeur])


La syntaxe de la fonction DECALER contient les arguments suivants :
  • réf    Obligatoire. Représente la référence par rapport à laquelle le décalage doit être opéré. L’argument réf doit être une référence à une cellule ou à une plage de cellules adjacentes ; sinon, la fonction DECALER renvoie la valeur d’erreur #VALEUR!.
  • lignes    Obligatoire. Représente le nombre de lignes vers le haut ou vers le bas dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l’argument lignes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq lignes en dessous de la référence. L’argument lignes peut être positif (c’est-à-dire en dessous de la référence de départ) ou négatif (c’est-à-dire au-dessus de la référence de départ).
  • colonnes    Obligatoire. Représente le nombre de colonnes vers la droite ou vers la gauche dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l’argument colonnes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq colonnes vers la droite par rapport à la référence. L’argument colonnes peut être positif (c’est-à-dire à droite de la référence de départ) ou négatif (c’est-à-dire à gauche de la référence de départ).
  • hauteur    Facultatif. Représente la hauteur, exprimée en nombre de lignes que la référence renvoyée doit avoir. L’argument hauteur doit être un nombre positif.
  • largeur    Facultatif. Représente la largeur, exprimée en nombre de colonnes que la référence renvoyée doit avoir. L’argument largeur doit être un nombre positif.

 

LA METHODOLOGIE
Partie 1
En premier lieu, il vous faut créer à disposition une liste (je prendrai pour exemple le fichier mis à disposition). Dans l’exemple: A2:102.
Nous allons créer ensuite deux champs nommés, l’un sur une seule cellule (pour que les formules soient plus lisibles par la suite) et l’autre sera dynamique (le nombre de valeurs pourra varier si une ajoute des prénoms à la fin par exemple).
Pour créer un champ nommé: CTRL + F3 (ou dans le ruban Formules, pavé Noms définis, cliquer sur Gestionnaire de noms).
Remplir comme les images ci-dessous:
– Premier_prenom: =’Liste dynamique’!$A$3
– Prenoms : =DECALER(‘Liste dynamique’!$A$2;1;0;NBVAL(‘Liste dynamique’!$A:$A)-1;1)
Premier_prenom correspond à la première cellule de la liste.
Pour ceux qui ne comprennent pas forcément les formules (comme DECALER), ne pas hésiter à me solliciter, je me ferai un plaisir de vous l’expliquer.
Partie 2
A présent, nous allons créer la liste de validation.
Avant tout, il est nécessaire de s’assurer que la liste est triée par ordre alphabétique. C’est un pré-requis indispensable.
Ensuite, il faut aller dans le ruban « Données », dans outils de données, cliquer sur Validation des données.
Dans le premier onglet, il faut choisir « Liste » dans la partie Autoriser et dans la source, remplir avec la formule suivante (je vais l’expliquer plus loin):
=SI(D2<> » »;DECALER(Premier_prenom;EQUIV(D2& »* »;Prenoms;0)-1;;SOMMEPROD((STXT(Prenoms;1;NBCAR(D2))=TEXTE(D2; »0″))*1));Prenoms)
Sur l’onglet message, c’est à votre convenance, vous pouvez vous inspirer de l’exemple.
Enfin pour l’onglet « Alerte d’erreur », il est IMPORTANT de décocher la case à cocher. si vous ne le faites pas, lorsque vous saisirez une valeur dans la liste déroulante, une message d’erreur s’affichera. Vous pouvez tester !!!
A présent, voici les explication de la formule affichée ci-dessus en bleu:

Si la cellule D2 n’est pas vide,
Alors
Il faut prendre tous les prénoms qui commencent par la valeur de la cellule D2
DECALER(Premier_prenom;EQUIV(D2& »* »;Prenoms;0)-1;;SOMMEPROD((STXT(Prenoms;1;NBCAR(D2))=TEXTE(D2; »0″))*1))
Sinon
nous prenons la liste complète (Prenoms).

Explication de la fonction
DECALER(Premier_prenom;EQUIV(D2& »* »;Prenoms;0)-1;;SOMMEPROD((STXT(Prenoms;1;NBCAR(D2))=TEXTE(D2; »0″))*1))

En reprenant les caractéristiques de la fonction DECALER, nous avons donc:
DECALER(réf, lignes, colonnes, [hauteur], [largeur])

réf :Premier_Prenom
lignes (Décalage vertical): EQUIV(D2& »* »;Prenoms;0)-1
colonnes (Décalage horizontal): n/a
Hauteur :SOMMEPROD((STXT(Prenoms;1;NBCAR(D2))=TEXTE(D2; »0″))*1)
Largeur: n/a

Décalage vertical:
L’objectif est de trouver le premier prénom de la liste qui commence par la valeur de D2. Pour cela, on utilise la fonction EQUIV qui permet de chercher une valeur (ici D2& »* ») dans une liste (Prénoms). EQUIV renvoie la position relative d’un élément dans une matrice.
Le caractère « * » permet de spécifier « commence par ».
Enfin, on enlève ensuite 1 au résultat car le titre « Prénom » (en A2) ne doit pas être pris en compte.

Décalage horizontale:
L’objectif est de trouver le nombre de prénoms qui commencent par la valeur spécifiée en cellule D2.
On peut le formuler ainsi:
Combien de fois l’égalité cellule D2 est égale à n premières lettres de chaque prénom (avec n = nombre de caractères de D2) ?
En utilisant la formue SOMMEPROD, on vérifie le nombre d’égalité qu’on multiplie ensuite par 1.
Cela nous donne la hauteur de la sélection

J’espère que cette fonctionnalité vous a plus, n’hésitez pas à me faire part de vos commentaires ou questions…

Laissez un commentaire

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

7 commentaires sur “Liste déroulante dynamique dans Excel”

  • Répondre ↓
    Anonyme

    Bonjour, votre article m'a servi à merveille.

    Après avoir terminé la modification de mon fichier, je suis cependant très embêté : je comptais en effet l'envoyer à plusieurs personnes, afin qu'elles n'aient que leur nom à remplir dans une liste déroulante (comme en D2 dans votre fichier exemple), et que tout un tas de données s'affichent en rapport au prénom choisi.
    Jusque là, tout va très bien.

    Mais dès que je protège la feuille pour que ces personnes ne me modifient pas les cellules de formules par mégarde, je constate que la saisie prédictive ne fonctionne plus ! (en D2 dans votre exemple).

    Auriez-vous une astuce pour que celle-ci fonctionne aussi bien en mode protégé?

    Merci d'avance et félicitations pour ce super tuto.

  • Répondre ↓
    Jérémy LAPLAINE

    Bonjour,
    Tout d'abord, je vous remercie pour le compliment.
    Pour votre problème, le premier point à vérifier concerne la protection de la cellule. Avez-vous bien décocher l'option "Verrouillée" dans format de cellule (bouton droit de la souris sur la cellule) puis onglet Protection ?
    J'ai testé de mon côté et cela fonctionne mais avec une contrainte: Après avoir saisi les première lettres, il faut quitter la cellule (TAB ou ENTREE) afin de pouvoir choisir dans la liste déroulante. J'avoue que cela n'est pas des plus pratiques mais je tâcherai de regarder si une solution à ce problème existe.
    Bonne journée,

    Jérémy

  • Répondre ↓
    Anonyme

    Merci tout d'abord de votre réponse si rapide.

    Effectivement, en quittant la cellule par le bouton entrée, on peut y revenir pour choisir dans la liste déroulante.
    Hélas, les personnes à qui je compte envoyer ce fichier ne connaîtront pas l'astuce, et elles perdent donc l'intérêt de l'écriture prédictive (et j'avoue ne pas vouloir les faire crouler sous les informations étant donné mon fichier à envoyer).

    Si vous trouvez une solution plus simple pour accéder directement aux choix correspondants aux n premières lettres entrées, je suis preneur !

    Merci en tout cas.

  • Répondre ↓
    Jérémy LAPLAINE

    Bonjour,
    J'ai trouvé la solution au problème. Lorsque vous souhaitez protéger la feuille (Ruban Révision -> Protéger la feuille), une boîte de dialogue s'ouvre avec de nombreuses cases à cocher. Il faut avoir la case cochée pour "Modifier les objets".
    J'ai testé et cela fonctionne. Attention toutefois, cela permet aux utilisateurs de modifier tous les objets (images par exemple).
    Bonne continuation,
    Jérémy

  • Répondre ↓
    Anonyme

    Bonjour,
    effectivement cela résout le problème, vous avez bien trouvé la solution.
    Hélas, ayant quelques images sur mon fichier Excel, je préfère garder une liste déroulante sans "écriture prédictive" et en étant sûr que rien ne sera modifié.

    Tant pis.

    Merci en tout cas.