Matricielles

Menu déroulant avec saisie semi-automatique

Temps de lecture 3 minutes

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

Utiliser les fonctions matricielles d'Excel 365

Pour réaliser un menu déroulant avec saisie semi-automatique, nous allons avoir besoin de la fonction FILTRE.

Cette fonction n'est accessible qu'avec les versions de Microsoft 365 ou Excel Online.

Utilisation de la fonction FILTRE
Utilisation de la fonction FILTRE

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

Etape 1 : Formule pour rechercher une partie du nom

L'astuce pour faire une saisie semi-automatique 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 effectuée dans la cellule.

Menu déroulant saisie semi automatique

Related posts

Historique des cours de bourse dans Excel

Frédéric LE GUEN

C’est quoi #EPARS!

Frédéric LE GUEN

Extraire toutes les lettres d’un texte

Frédéric LE GUEN

10 commentaires

AChesse 29/01/2021 at 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

Répondre
Frédéric LE GUEN 30/01/2021 at 16:58

En englobant le résultat dans la fonction TRANSPOSE

Répondre
David ENDENE 27/01/2021 at 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

Répondre
Frédéric LE GUEN 27/01/2021 at 17:51

Je ne comprends absolument pas ce que vous voulez faire.

Répondre
Olivier 10/12/2020 at 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é :)

Répondre
Olivier 09/12/2020 at 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.

Répondre
Olivier 09/12/2020 at 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"

Répondre
Frédéric LE GUEN 09/12/2020 at 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

Répondre
Olivier 09/12/2020 at 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 )

Répondre
Frédéric LE GUEN 09/12/2020 at 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.

Répondre

Laissez un commentaire

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