Menu déroulant avec saisie partielle

Temps de lecture : 3 minutes

Cet article va vous expliquer comment créer un menu déroulant avec saisie partielle.

Utiliser les fonctions matricielles 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 un menu déroulant avec saisie semi-automatique, nous allons partir de ce document, intégrer dans un Tableau (nommé tbl_Clients). En travaillant avec un Tableau, ça simplifie l'écriture et la compréhension des références.

Tableau contenant des noms de clients

Mise à jour Version Janvier 2022

Depuis la version 2202 (Fichier > Compte) de Microsoft 365, les menus déroulants proposent tous la sélection partielle par défaut 👍😀

Office version 2202

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 matricielles dynamiques, les fonction d'Excel peuvent interpréter toutes les cellules d'une colonne.

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])

La fonction CHERCHE construite comme une fonction matricielle retourne plusieurs résultats

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 important 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]))

La fonction ESTNUM converti les résultats en VRAI FAUX

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, si nous pouvons parfaitement substituer le second paramètre par une formule qui donne déjà VRAI ou FAUX (comme le test que nous venons de créer).

=FILTRE(tbl_Clients;ESTNUM(CHERCHE(C2;tbl_Clients[Clients])))

Ajouter la fonction FILTRE au test de la fonction ESTNUM

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

Valeur à retourner si la fonction FILTRE ne trouve rien

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 étendue à la cellule contenant la formule matricielle.

=$E$2#

Utilisation dune référence étendue pour appliquer la formule matricielle dans le menu déroulant

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

Validation de données option alerte décochée

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.

Menu déroulant saisie semi automatique

19 Comments

  1. Magalie
    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

    Reply

    • Frédéric LE GUEN
      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)

      Reply

  2. Hamdia
    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.

    Reply

  3. Tao
    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

    Reply

    • Frédéric LE GUEN
      21/09/2021 @ 08:32

      Non. Ca c'est du web

      Reply

  4. Polizzi
    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

    Reply

    • Mittelberger
      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.

      Reply

  5. Raphaël Waeselynck
    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 ?

    Reply

    • Frédéric LE GUEN
      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à

      Reply

  6. AChesse
    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

    Reply

    • Frédéric LE GUEN
      30/01/2021 @ 16:58

      En englobant le résultat dans la fonction TRANSPOSE

      Reply

  7. David ENDENE
    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

    Reply

    • Frédéric LE GUEN
      27/01/2021 @ 17:51

      Je ne comprends absolument pas ce que vous voulez faire.

      Reply

  8. Olivier
    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é :)

    Reply

  9. Olivier
    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.

    Reply

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

    Reply

    • Frédéric LE GUEN
      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

      Reply

  11. Olivier
    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 )

    Reply

    • Frédéric LE GUEN
      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.

      Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MICROSOFT MVP

RECHERCHE D’EMPLOI AVEC

Back to top