Comment créer une liste déroulante avec saisie semi-automatique dans Excel ?
- Trouver si le texte recherché existe ou pas
Pour faire ce travail, il faut utiliser les fonctions CHERCHE et ESTNUM
- Utiliser la fonction FILTRE
La fonction FILTRE va conserver les cellules correspondantes au test
- Écrire la formule dans un menu déroulant
Il y a une astuce pour appeler la formule qui renvoie les résultats
Utiliser les fonctions d'Excel 365
Pour réaliser un menu déroulant avec saisie partielle, nous allons avoir besoin de la fonction FILTRE. Cette fonction n'est accessible qu'avec les versions de Microsoft 365 ou Excel Online.
Pour illustrer comment créer une liste déroulante avec saisie semi-automatique, nous allons partir de ce document, intégrer dans un Tableau (nommé tbl_Clients). En travaillant avec un Tableau. Un Tableau simplifie l'écriture et la compréhension des références.
Etape 1 : Formule pour rechercher une partie du nom
L'astuce pour faire un menu déroulant avec saisie partielle c'est d'utiliser la fonction CHERCHE. Mais ici, nous n'allons pas utiliser la référence d'une seule cellule mais de toute une colonne. C'est maintenant possible car, grâce aux fonctions propagées.
- Quand la chaîne de caractères "te" est trouvée, la formule CHERCHE retourne la position où cette chaîne a été trouvée.
- Quand la chaîne de caractères n'est pas trouvée, la fonction retourne #VALEUR!
=CHERCHE(C2;tbl_Clients[Clients])
Etape 2 : Convertir en test VRAI / FAUX
Ce n'est pas tant de retourner la position où la chaîne de caractères a été trouvée qui est importante mais de savoir que la fonction CHERCHE a trouvé un résultat ou pas. Pour cela, nous allons utiliser la fonction ESTNUM pour transformer le résultat en test logique VRAI ou FAUX (très important dans Excel)
=ESTNUM(CHERCHE(C2;tbl_Clients[Clients]))
Etape 3 : Insérer le test dans la fonction FILTRE
Normalement, la fonction FILTRE attend en 2e paramètre un test logique sur une colonne, comme par exemple B2:B20="Oui". Mais un test logique, retourne VRAI ou FAUX. Donc, nous pouvons parfaitement substituer le second paramètre par une formule qui retourne VRAI ou FAUX (comme le test que nous venons de créer).
=FILTRE(tbl_Clients;ESTNUM(CHERCHE(C2;tbl_Clients[Clients])))
Etape 4 : Ajouter un paramètre en cas de recherche infructueuse
Nous devons aussi traiter le cas où la recherche n'aboutie pas. Pour cela, nous allons renseigner le 3e paramètre pour afficher un message spécifique quand le texte à trouver n'existe pas
=FILTRE(tbl_Clients;ESTNUM(CHERCHE(C2;tbl_Clients[Clients]));"Pas de résultat")
Etape 5 : Intégrer la formule dans un menu déroulant
Il ne reste plus qu'à créer un menu déroulant (Données > Validation de données)
Et d'y indiquer la référence propagée avec le # pour indiquer que nous récupérons tous les résultats renvoyés par la fonction FILTRE.
=$E$2#
Étape 6 : Décochez l'alerte de saisie
Pour terminer, il est impératif de décocher l'option de contrôle de saisie du menu déroulant dans l'onglet Alerte d'erreur.
En effet, si la case est cochée, la formule n'a pas le temps de renvoyer le résultat et un message d'erreur sera toujours renvoyé.
Et c'est tout. Maintenant, votre menu déroulant vous affiche un résultat en fonction de la saisie partielle effectuée dans la cellule.
Marie
Maxime
Frédéric LE GUEN
degraeve
PHOENIX
Nicolas
BENDER
Magalie
Hamdia
Tao
Polizzi
Mittelberger
Raphaël Waeselynck
AChesse
David ENDENE
Olivier
03/08/2023 @ 11:01
Bonjour,
J'ai un tableau sur lequel je dois faire plusieurs liste déroulante avec une saisie semi automatique. Cependant pour ma ligne 1 en suivant le tuto c'est ok, si je souhaite que cela fonctionne sur ma ligne 2 comment dois-je m'y prendre? dois-je refaire toute la manipulation? J'ai essayé de "tirer" l'utilisation de donnée mais impossible.
Est-ce que autant de ligne en menu déroulant existantes, je dois refaire la manipulation décrite ci-dessus?
Merci pour votre retour.
03/04/2023 @ 20:30
Bonjour est il possible de créer un menu déroulant avec saisie partielle mais avec un office 2019 ? j’ai réussi uniquement à le faire mais par ordre alphabétique alors il cherche les première caractères mais si une cellule s’appelle 4101-admin et que je cherche admin il ne me le propose pas. Merci de votre aide
05/04/2023 @ 07:16
Non, je ne pense pas. Il faut les versions de M365 pour le faire à ma connaissance
13/12/2022 @ 22:10
Bonjour,
Très bonne formule, elle fonctionne parfaitement.
En revanche comme de nombreuses personnes, j'aimerais pouvoir étendre cette liste déroulante sur une colonne entière mais cela ne fonctionne pas.
Avez-vous trouvé une solution ?
Merci d'avance.
26/10/2022 @ 15:04
Bonjour,
Superbe tutoriel qui est très pratique.
Cependant, comme certaines personnes qui ont réagit à votre post,
Je recherche un moyen d'utiliser cette astuce sur la même base de donnée mais dans des cellules différentes.
Je m'explique :
- la base de recherche est exactement la même.
- ma cherche sera faite sur plusieurs cellules différentes
Merci pour votre aide.
26/07/2022 @ 16:52
Bonjour,
Depuis la version 2202 (Fichier > Compte) de Microsoft 365, les menus déroulants proposent tous la sélection partielle par défaut
Je n'arrive pas à activer ou faire fonctionner la sélection partielle dans les listes déroulante, je suis en version 2206.
Une idée ?
Merci par avance pour votre aide !
26/07/2022 @ 18:14
Il n'y a rien à activer, c'est natif. Je n'ai pas d'explication sur le fait que cette fonctionnalité n'apparaisse pas dans votre version (un add-in peut-être)
26/07/2022 @ 11:49
Bonjour,
J'ai un message d'erreur lorsque j'utilise la formule "Filtre" => cette fonction est incorrecte
Pouvez vous m'indiquer si la version "2206 (build...)"est adaptée?
je vous remercie d'avance
04/01/2022 @ 15:43
Bonjour,
Après avoir suivi à la lettre votre tuto je me trouve confronter à un problème avec la fonction FILTRE qui est dite incorrect
Version excel plus 2019 sous windows 10
Est ce normal
Merci de votre aide
04/01/2022 @ 16:29
Malheureusement oui. FILTRE n'est présente que pour les versions Excel 365 ou Excel Online (version gratuite d'Excel)
23/10/2021 @ 16:08
Merci pour ce tuto,
J'ai un tableau dont une colonne qui contient des listes déroulantes identiques. Quand je fais comme vous l'avez bien expliqué pour la première cellule ça marche très bien. Mais quand je veux étendre la formule aux autres cellules identiques de la colonne ça ne marche pas.
Merci.
21/09/2021 @ 08:18
Bonjour,
Est-il possible d'afficher la liste déroulante automatiquement dès que l'on tape un premier caractère?
Merci
21/09/2021 @ 08:32
Non. Ca c'est du web
09/09/2021 @ 15:39
Bonjour et merci pour ce tuto très précis!
J'ai réussi à l'adapter à mon cas.
Or j'aimerais l'étendre, càd pouvoir selectionner une valeur sur chaque ligne (et non pas qu'une cellule), à chaque fois sur base de caractères écrit à la main.
La liste étant un grand nombre de clients.
Je veux une aide à la saisie sur chacune de mes lignes de ce type. J'ai essayé de copier/coller la formule mais une fois que la ligne 1) est remplie avec une valeur, le tableau "s'écrase" ne laissant qu'une valeur disponible. Donc la recherche de la ligne 2 ne fonctionne pas
j'espère avoir été assez clair et merci d'avance pour votre réponse
08/06/2022 @ 23:21
Bonjour
Avez vous trouvé une solution pour que cela fonctionne sur une colonne entière? Et non pas que sur une cellule? Le copier coller de la cellule avec la liste de roulante ne fonctionne pas effectivement.
Je pense que ça vient de la formule de la recherche des caractères. Le fameux CHERCHE(C2;.........). Ne faudrait t'il pas que le C2 soit évolutif? C'est à dire qu'on puisse chercher dans toute la colonne C plutôt que la cellule C2 uniquement. Genre un C2#. Je ne sais pas si ça fonctionne. Pas encore fait l'essai.
29/03/2021 @ 15:54
Si je comprends bien, cette méthode permet de faire un champ de recherche unique mais elle n'est pas applicable à une cellule que l'on peut "tirer vers le bas" de sorte à l'intégrer dans un ensemble de lignes.
J'ai pour ma part de gros tableaux utilisant des données qui reviennent souvent (par exemple des noms / prénoms / Société) et pouvoir saisir certaines colonnes en menu déroulant tel que décrit ici serait l'idéal ; mais est-ce possible ?
16/04/2021 @ 08:48
Impossible de répondre à votre question sans voir le contexte. C'est un travail de consultant que vous demandez là
29/01/2021 @ 15:08
Bonjour,
Très bon tuto ! J'ai cependant une question :
Dans l'étape 1, lorsque l'on utilise la fonction CHERCHE dans toute une colonne d'un tableau, la fonction retourne l'ensemble des résultats en colonne à partir de la cellule où la formule est rentrée .
Est-il possible d'avoir les résultats etendu en ligne vers la droite, plutot que en colonne vers le bas?
Merci d'avance
30/01/2021 @ 16:58
En englobant le résultat dans la fonction TRANSPOSE
27/01/2021 @ 16:56
Juste pour savoir si cette technique est valable pour plusieurs cellules, j'ai essayé la technique et elle est excellente. Mais je veux que la liste déroulante interagisse sur une chaîne de données.
Merci pour votre disponibilité et pour vos recherche
27/01/2021 @ 17:51
Je ne comprends absolument pas ce que vous voulez faire.
10/12/2020 @ 09:50
Bon après quelques essais, c'est bien la version d'excel installée sur mon post (sous seven) qui pose problème : elle ne gère pas fonction filtrer.
A domicile, sous W10, aucun problème avec mon fichier.
En tout cas merci de votre aide et du temps que vous m'avez consacré 🙂
09/12/2020 @ 15:14
J'ai résolu le problème en créant un tableau (via insérer, ce que je ne fais pas souvent je préfère nommer moi même des plages) et la fonction cherche fonctionne. Ok également pour l'étape suivante (estnum).
En revanche, ca bloque au moment d'utiliser la fonction "filtre" qui n'est pas reconnue par Excel (Cette fonction est incorrecte). J'ai essayé en anglais (FILTER), la formule passe mais renvoie #NOM? ce qui me fait dire qu'elle ne fonctionne pas (je devrais avoir FAUX comme à l'étape précédente)
Je suis bien sous 365, mais peut être que ma version au boulot est antérieure au déploiement de la fonction... J'ai un vieux poste sous Windows 7.
09/12/2020 @ 14:46
en fait j'ai suivi le tutoriel pas à pas. J'ai ma liste de nom dans la colonne A, avec un en tète "nom". J'ai sélectionné la colonne pour la nommer"liste_noms". celle C2 pour la recherche, E2 pour la formule.
je bloque a la premiere étape : =cherche(C2;liste_noms[noms])
me renvoit la msgbox : "nous avons rencontré une erreur dans cette formule"
09/12/2020 @ 15:00
Pour être sur de saisir exactement le nom du tableau et de la colonne, il suffit de cliquer sur l'entête de colonne comme c'est expliqué ici (le dernier exemple). Je vois un pb entre nom et noms
09/12/2020 @ 13:35
Bonjour,
Votre tutoriel est très clair, mais je me heurte à un problème : excel refuse de valider la partie « cherche » de la formule, et la partie qui bloque est le paramètre optionnel entre crochets. Quand je l’enlève excel me valide la formule. Mais sans ce paramètre rien ne semble marcher...
Je précise que j’utilise office 365.
J’espère que vous passerez dans les parages et pourrez m’aider. Cette liste avec saisie on intéresse beaucoup, je. Outrait l’associer avec une macro filtre (pour que l’utilisateur recherche une donnée dans la liste et puisse filtrer un autre tableau une fois cette donnée trouvée )
09/12/2020 @ 14:18
Avez-vous essayé les étapes intermédiaires ?
Si j'ai pris le temps de détailler chaque étape c'est pour justement voir à quel moment une erreur peu apparaître. Juste recopier la formule finale ne permet pas de repérer facilement les erreurs.
En plus j'ai utilisé des références à un Tableau donc il faut voir dans votre cas comment l'adapter.