Saisie semi-automatique Excel sans code VBA

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


Sujets que vous pourriez aussi aimer :


Liste déroulante et saisie semi-automatique

Nous connaissons le principe des listes déroulantes reliées entre elles. Nous avons déjà abouti quelques travaux à ce sujet. La dernière formation en date a consisté à isoler les villes d'un code postal saisi. Ici, sans l'appui du code VBA, nous souhaitons ajouter une fonctionnalité ergonomique à la liste liée.



Il est question de remplir son contenu selon les fragments de texte tapés par l'utilisateur. Il peut s'agir des premiers caractères d'une ville par exemple. L'idée est de pouvoir restreindre les propositions pour faciliter le choix.

Source et présentation de la problématique
Des données sont nécessaires pour construire un tel outil. C'est ainsi que nous proposons tout d'abord de réceptionner un classeur existant.

• Télécharger le classeur liste-deroulante-saisie-auto.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. Elles sont respectivement nommées : bd_sorties et listes.

• En bas de la fenêtre Excel, cliquer sur l'onglet listes pour activer sa feuille,

Cette feuille propose de choisir un département recensé dans la feuille bd_sorties. Ce choix doit être réalisé en cellule B5.



Dès lors, la cellule voisine C5 doit proposer toutes les villes associées, chargées elles aussi dans une liste déroulante. Mais comme nous le disions, si d'aventure l'utilisateur choisit de taper quelques caractères en lieu et place, cette liste doit être en mesure de restreindre ses suggestions, en fonction des correspondances.

• En bas de la fenêtre Excel, cliquer sur l'onglet bd_sorties pour revenir sur sa feuille,

Les villes de quelques départements sont énumérées en colonne B. La colonne A représente leurs départements d'appartenance.



Selon les techniques que nous maîtrisons désormais, un calcul intermédiaire de repérage est construit en colonne C : =SI(A2=listes!$B$5; MAX($C$1:C1)+1; '').

Il consiste à identifier chaque ville appartenant au département choisi depuis la feuille listes (A2 = listes!$B$5). Dès que la correspondance est avérée, la ville est marquée d'un numéro incrémenté (MAX($C$1:C1)+ 1).

Et c'est sur la base de ces numéros que nous produisons l'extraction des villes associées en colonne F :

=SIERREUR(INDEX(B:B; EQUIV(LIGNE(A1); C:C; 0); 1);'')

Pour cela, nous imbriquons les fonctions d'extraction index et equiv. Le numéro variable de ligne es trouvé par la fonction Excel Equiv agissant sur des indices progressant avec le calcul, grâce à la fonction Ligne.

Extraire une correspondance
Vraisemblablement, il ne nous resterait plus qu'à construire une plage dynamique sur la base de ces extractions. Cette plage servirait de source de données pour remplir la liste déroulante des villes de la feuille listes. Mais ces techniques, nous les maîtrisons déjà.

Nous devons désormais construire un calcul supplémentaire de repérage. Celui-ci doit être en mesure d'identifier, toujours par des numéros incrémentés, les précédentes villes extraites contenant la suite de caractères potentiellement tapés par l'utilisateur. En revanche, si aucune saisie n'est émise, l'extraction précédente doit être fidèlement restituée. Donc, toutes les villes extraites doivent être repérées.

La fonction Excel conditionnelle est donc de mise. Elle doit tester le contenu de la cellule C5 de la feuille listes. Si ce contenu existe, elle doit chercher la suite de caractères dans chacune des villes extraites par le précédent calcul. La fonction Excel Cherche est donc nécessaire :

=Cherche(Texte_cherché; Chaîne_de_recherche ; [position_de_départ])

En premier argument, nous devons donc lui passer la cellule C5 de la feuille listes. En deuxième argument, nous devons lui passer la cellule de la ville extraite dans la colonne F de la feuille bd_sorties. Le troisième argument est facultatif. Ainsi nous débuterons la recherche à partir du premier caractère.

Si l'occurrence cherchée est trouvée, la fonction Cherche renvoie la position identifiée dans la chaîne. Mais dans le cas contraire, elle retourne une erreur empêchant le calcul global de se poursuivre. Nous devons intercepter ces potentielles recherches infructueuses avec la fonction Excel booléenne :

=SiErreur(Calcul_à_essayer; Action_si_erreur)

• Sélectionner la cellule G2 de la feuille bd_sorties,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• En bas de la fenêtre Excel, cliquer sur l'onglet listes pour activer sa feuille,
• Sélectionner la cellule de la ville, soit C5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : listes!$C$5,

En effet, notre calcul de repérage est destiné à être répliqué sur les lignes du dessous. Mais pour chaque ligne, il doit tester le contenu de cette cellule, qui ne doit donc pas bouger.

• Taper alors l'inégalité suivante : <>'',

Nous nous assurons ainsi que son contenu n'est pas vide, donc que l'utilisateur à taper des lettres. Souvenez-vous, le cas échéant, il nous faudra restituer la liste originelle des villes concordantes.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir la fonction de test suivie d'une parenthèse, soit : EstErreur(,
• Taper la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Désigner de nouveau la cellule de la ville en la figeant, ce qui donne : listes!$C$5,
• Taper un point-virgule (;) pour passer dans l'argument de la chaîne de recherche,
• En bas de la fenêtre Excel, cliquer sur l'onglet bd_sorties pour revenir sur sa feuille,
• Désigner alors la première ville extraite, soit la cellule F2,

De cette manière, nous cherchons à savoir si les caractères tapés se trouvent dans cette ville, et puis dans les autres par réplication.

• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction EstErreur,
• Taper un point-virgule (;) pour passer dans la branche Alors de la deuxième fonction Si,
• Saisir deux guillemets ('') pour conserver la cellule du résultat vide,

Effectivement, si la recherche conduit à une erreur, c'est qu'elle est infructueuse dans le texte de cette ville. Nous ne devons donc pas repérer sa ligne.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la deuxième fonction Si,
• Saisir la fonction donnant la plus grande valeur d'une plage, suivie d'une parenthèse, soit Max(,
• Cliquer sur la cellule située juste au-dessus du calcul, soit G1,
• Taper le symbole deux points (:) pour générer la plage G1:G1,
• Sélectionner la première de ces deux références,
• Puis, enfoncer la touche F4 du clavier, ce qui donne : $G$1:G1,

De cette manière, la fonction Max agira sur une plage de cellules qui grandit en hauteur en même temps que le calcul est répliqué sur les lignes du dessous. En effet, nous n'avons pas figé la borne inférieure de cette plage. C'est donc toujours la plus grande et dernière valeur qui sera retournée. Il ne nous reste plus qu'à l'incrémenter à chaque passage.

• Cliquer à la fin de la formule pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Max,
• Puis, ajouter une unité, soit : +1 pour l'incrémentation,
• Fermer la parenthèse de la deuxième fonction Si,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la première fonction Si,

Un nouveau critère est nécessaire pour que le repérage ne s'étende pas au-delà des précédentes extractions. Il consiste à vérifier que la ville de la colonne F existe bien.

• Taper la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la première ville extraite, soit F2,
• Taper l'inégalité : <>'', pour vérifier que son contenu existe bien,
• Taper un point-virgule (;) pour passer dans la branche Alors de cette troisième fonction Si,
• Reproduire le calcul d'incrémentation à l'identique, soit : Max($G$1:G1)+1,

En effet, si aucun caractère n'a été tapé en cellule C5 de la feuille listes et qu'il existe bien une ville associée au département choisi en amont, nous devons la restituer fidèlement. Pour ce faire, nous commençons par la repérer avec un numéro.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la troisième fonction Si,
• Saisir deux guillemets ('') pour conserver le résultat vierge,
• Fermer la parenthèse de la troisième fonction Si,
• Puis, fermer la parenthèse de la première fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons le résultat actif pour l'exploiter dans la foulée. Vous notez l'apparition du premier numéro en regard de la première ville. Jusque-là tout va bien. Nous n'avons encore soumis aucune lettre en cellule C5 de la feuille listes.

• Double cliquer sur la poignée du calcul pour répliquer sa logique sur la hauteur du tableau,

Chaque ville est désormais parfaitement repérée par un numéro incrémenté.

• En bas de la fenêtre Excel, cliquer sur l'onglet listes pour activer sa feuille,
• En cellule C5, taper le texte suivant : Saint,
• Puis, le valider par la touche Entrée du clavier,
• Revenir ensuite sur la feuille bd_sorties,

Comme vous pouvez le voir, seuls deux numéros persistent. Ils sont bien incrémentés et ils sont situés en regard des villes contenant la suite des lettres tapées.



Si vous supprimez le texte de la cellule C5 sur la feuille listes, tous les numéros incrémentés se replacent automatiquement. La formule de repérage que nous avons construite est la suivante :

=SI(listes!$C$5<> ''; SI(ESTERREUR(CHERCHE(listes!$C$5; F2)); ''; MAX($G$1:G1)+1); SI(F2<> ''; MAX($G$1:G1)+1; ''))

Si nous avions souhaité isoler seulement les villes commençant par les lettres tapées et non les contenant, nous aurions construit la formule de repérage intermédiaire suivante :

=SI(listes!$C$5<> ''; SI(GAUCHE(F2; NBCAR(listes!$C$5))=listes!$C$5; MAX($I$1:I1)+1; ''))

Extraire les villes repérées
Nous devons maintenant exploiter ces résultats pour produire l'extraction des villes restreintes. Il s'agit de techniques désormais bien connues. Nous devons employer les fonctions Index et Equiv.

=Index(Tableau_de_recherche; num_ligne ; num_colonne)
=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

Pour la fonction Index, le tableau de recherche n'est autre que la colonne F des villes. L'indice de ligne de l'information à extraire dépend de la position des numéros incrémentés. C'est pourquoi, nous lui imbriquerons la fonction Equiv. La valeur cherchée n'est autre que ne numéro à suivre, grâce à la foncton Ligne à initialiser sur une cellule de la première ligne de la feuille. La colonne de recherche est celle du repérage, soit la colonne G. Le mode de recherche sera fixé à 0 pour une correspondance exacte. Le numéro de colonne pour la fonction Index est nécessairement le premier (1). En effet, nous engageons l'extraction sur une seule colonne, la colonne F.

• Sélectionner la cellule H2 de la feuille bd_sorties,
• Taper le symbole égal (=) pour débuter la construction de la formule d'extraction,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Cliquer sur l'étiquette de colonne F pour désigner le tableau de recherche, soit : F:F,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction donnant la ligne d'une recherche, suivie d'une parenthèse, soit : Equiv(,
• Taper la fonction retournant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne, par exemple H1,
• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des numéros incrémentés par son étiquette, soit : G:G,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,
• Taper le chiffre 1 puisqu'il n'y a qu'une colonne définie pour l'extraction,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le deuxième argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur le tableau,

En l'état, toutes les villes sont extraites et correspondent à la première importation.

• En cellule C5 de la feuille listes, saisir de nouveau le texte Saint,
• Puis, revenir sur la feuille bd_sorties,

Comme vous pouvez le voir, notre extraction s'adapte parfaitement. Dans un premier temps, faute de suggestion, l'intégralité des villes a été restituée. Dans un deuxième temps, seules subsistent les villes contenant les caractères tapés par l'utilisateur.

Rendre la plage dynamique
La colonne H héberge donc le contenu dynamique que nous devons utiliser pour remplir la liste déroulante des villes, en cellule C5 de la feuille listes. Mais la hauteur des extractions est fort logiquement variable. De fait, la liste déroulante doit être capable de se nourrir seulement du contenu strict. Pour cela, nous devons tout d'abord nommer la plage d'extraction. Puis, nous devons retravailler ce nom avec une formule exploitant la fonction Excel Decaler.

=Decaler(Point_de_départ; Décalage_en_ligne ; Décalage_en_colonne ; [Hauteur] ; [Largeur])

Cette dernière permet d'adapter la hauteur de la plage à partir d'un point de départ à spécifier en premier argument. Ce point de départ n'est autre que la première cellule de la plage d'extraction. En quatrième paramètre, nous devons lui passer une fonction de dénombrement à appliquer sur la colonne H d'extraction. Son rôle est de retourner le nombre de valeurs trouvées. Ce nombre définira la hauteur dynamique de la plage. Les deuxième et troisième arguments doivent être conservés nuls. Nous ne souhaitons opérer aucun décalage, ni en ligne, ni en colonne, par rapport au point de départ. Le cinquième et dernier argument concerne la largeur dynamique. Il est facultatif. Il suffit de ne pas le renseigner pour travailler sur une plage d'une colonne.

• Sélectionner la première cellule de la plage d'extraction, soit H2,
• Dans la zone Nom, en haut à gauche de la feuille, saisir le texte Villes,
• Puis, valider nécessairement ce nom par la touche Entrée du clavier,

A ce stade, ce nom désigne une plage de cellules statique. Et plus précisément, il s'agit seulement de la cellule H2.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui apparaît, sélectionner le nom Villes,
• Dans la zone Fait référence à, adapter la syntaxe comme suit :

=DECALER(bd_sorties!$H$2; 0; 0; NB.SI(bd_sorties!$H:$H; '>*<'))

• Puis, valider la syntaxe en cliquant sur le bouton à la coche verte,

Comme nous le disions, nous exploitons donc la fonction Excel Decaler pour adapter la hauteur de la plage de cellules à son contenu extrait. Nous désignons bien la cellule H2 comme le point de départ en premier argument. En quatrième paramètre, nous exploitons la fonction Excel Nb.Si :

=Nb.Si(Plage_du_critère; Critère)

Cette dernière permet de dénombrer un critère passé en deuxième argument sur une plage de cellules mentionnée en premier paramètre. Grâce au critère >*< sur la plage d'extraction (H:H), nous comptons toutes les cellules contenant effectivement un texte. C'est la raison pour laquelle nous n'avons pas exploité la fonction NbVal. Elle aurait aussi comptabilisé les cellules vides mais portant une formule.

• Valider cette syntaxe en cliquant sur le bouton Fermer du gestionnaire de noms,

Bref, de ce dénombrement, nous en déduisons donc la hauteur dynamique de la plage de cellules. Cette hauteur varie en effet selon l'extraction produite.

Déverrouiller la liste déroulante
Ce nom doit désormais être exploité pour nourrir dynamiquement le contenu de la liste déroulante des villes de la feuille listes.

• En bas de la fenêtre Excel, cliquer sur l'onglet listes pour activer sa feuille,
• Sélectionner la cellule C5 de la ville,
• Supprimer son contenu à l'aide de la touche Suppr du clavier,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la boîte de dialogue qui s'affiche, activer l'onglet Options,
• Dans la section Autoriser, choisir l'option Liste,
• Puis, cliquer dans la zone Source juste en dessous pour l'activer,
• Taper alors la syntaxe de correspondance suivante : =Villes,

De cette manière, nous indiquons que la liste déroulante doit se remplir des informations contenues dans la plage de cellules dynamique, reconnue par le nom Villes.

• Valider cette association en cliquant sur le bouton Ok de la boîte de dialogue,
• De retour sur la feuille, cliquer sur la flèche qui se propose en bas à droite de la cellule,

Comme vous le remarquez, toutes les villes du département choisi en B5 sont effectivement chargées. De plus, la hauteur de la liste déroulante est parfaitement ajustée au contenu.

• En cellule D5, choisir maintenant le département de la Drôme (26-Drome),
• Puis, déployer la liste déroulante en cellule C5,

L'offre des villes est plus importante dans ce département avec cette petite base de données de test. Et la confirmation est à nouveau apportée. La liste déroulante s'est allongée automatiquement pour accueillir l'intégralité des villes extraites.

• Désormais, en cellule C5, taper les premières lettres d'une ville potentielle comme : Saint,
• Puis, valider la saisie par la touche Entrée du clavier,

A notre grande surprise, la proposition est refusée. Mais nous connaissons ce mécanisme. Nous l'avons appris au travers de la formation Excel sur les listes limitées. Par défaut, une liste déroulante Excel refuse toute saisie qui ne correspond pas à un élément de la liste. Nous devons donc débrider cette dernière.

• Valider l'alerte en cliquant sur le bouton Annuler de la boîte de dialogue,
• Puis, dans le ruban Données, cliquer de nouveau sur le bouton Validation des données,
• Dans la boîte de dialogue qui suit, activer l'onglet Alerte d'erreur,
• Ensuite, décocher la case Quand des données non valides sont tapées,

C'est ainsi que nous autorisons toute saisie dans la liste déroulante. Elle est désormais déverrouillée.

• Valider ce réglage en cliquant sur le bouton Ok de la boîte de dialogue,
• En cellule C5, taper de nouveau le texte Saint,
• Puis, déployer la liste déroulante en cliquant sur sa flèche,

Cette fois, les propositions s'affichent. Elles sont strictement adaptées aux villes concordant avec les premières lettres tapées. Ce sont nos calculs intermédiaires d'extraction et la plage dynamique qui ont permis de réaliser ce tour de force.

Nous obtenons en effet une liste déroulante liée qui offre par défaut toutes les suggestions attachées. Mais elle est aussi capable de s'ajuster par des propositions intuitives, comme le ferait une saisie semi-automatique.