Liste Excel des villes selon le code postal saisi

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


Sujets que vous pourriez aussi aimer :


Villes et codes postaux

Pour les besoins d'un bulletin ou formulaire d'inscription, il est important de suggérer une liste déroulante des villes associées à un code postal saisi. Cette fonctionnalité apporte confort et rapidité. En outre, elle sécurise et homogénéise la saisie des informations. De la sorte, jamais une ville ne sera inscrite avec une casse ou une orthographe différente.



Source et problématique
Pour aboutir une telle solution, une base de données des communes est nécessaire. C'est pourquoi nous proposons de réceptionner tout d'abord un classeur, offrant ces données et la structure de l'outil.

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

Le classeur réceptionné est composé de deux feuilles. Elles sont respectivement nommées Formulaire et Villes. Sur la première des deux, nous retrouvons la zone d'inscription pour recevoir la saisie du code postal, en cellule C6. Si vous consultez la section Nombre du ruban Accueil, vous constatez qu'elle est formatée comme un texte. Cette remarque est importante. Les communes ont été importées depuis une base de données Access. Et, pour préserver le chiffre 0 en préfixe des codes qui le requièrent, ces codes postaux sont considérés comme des chaînes de texte. Ce formatage est donc essentiel pour réaliser des comparaisons en bonne et due forme.

La cellule E6 est celle de la ville à associer. Bientôt, elle devra offrir une liste déroulante permettant à l'utilisateur de choisir parmi les propositions associées au code postal renseigné. Cette liste doit être dynamique. Sa longueur doit varier en fonction du nombre de propositions. Il s'agit de l'un des défis intéressants de cette formation.

La seconde feuille renferme donc la base de données. Les communes sont énumérées en colonne C. Les codes postaux associés sont listés en colonne D voisine. Fort logiquement, plusieurs villes peuvent correspondre à un même code postal. Mais ce n'est pas toujours le cas. La situation se présente le plus souvent dans le contexte de petites communes regroupées.

En colonne H, vous remarquez la présence d'une zone d'extraction. C'est en dessous de la cellule H2 que nous proposons d'extraire toutes les villes associées à un code postal renseigné. Mais pour cela, nous aurons besoin de bâtir un calcul intermédiaire de repérage.



Pour information, les villes et codes postaux recensés concernent la région PACA.

Repérer les villes concordantes
Dans un premier temps donc, nous proposons de construire la formule capable d'identifier toutes les villes attachées à un code postal renseigné, depuis le formulaire. Et par esprit logique, nous allons exploiter la colonne E de la feuille Villes pour l'accueillir. Nous avions démontré cette solution, notamment au travers de la formation Excel pour extraire les données recoupées.

L'astuce consiste à identifier les villes concordantes avec un numéro incrémenté. Elles pourront ainsi être facilement prélevées dans la chronologie imposée. Un test est donc premièrement nécessaire pour réaliser la comparaison des codes postaux. Donc, nous devons exploiter la fonction Excel Si. Et pour l'incrémentation, nous devons être en mesure de prélever le numéro le plus élevé déjà attribué, pour l'affecter d'une unité supplémentaire. C'est la fonction Excel Max qui extrait la valeur la plus grande dans une plage de cellules. Et nous allons le comprendre, pour que cette incrémentation puisse exister, nous allons définir une plage qui grandit en même temps que le calcul se déplace.

• Sur la feuille Villes, sélectionner la première cellule pour la détection, soit E3,
• Taper le symbole égal (=) pour débuter la syntaxe de la formule,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Pour établir le critère, sélectionner le premier code postal, soit la cellule D3,
• Taper de nouveau le symbole égal (=) pour la condition à vérifier,
• En bas de la fenêtre Excel, cliquer sur l'onglet Formulaire pour activer sa feuille,
• Sélectionner alors la cellule du code postal, soit C6, ce qui donne : Formulaire!C6,
• Enfoncer la touche F4 du clavier pour figer les références de cette cellule,

En effet et cela va de soi, cette formule est destinée à être répliquée sur les lignes du dessous, pour rechercher la correspondance sur tous les codes postaux de la base de données. Mais cette correspondance doit toujours être analysée par rapport à la même cellule, celle de la saisie du code postal dans le formulaire. Elle ne doit pas suivre le déplacement imposé. Donc, nous la figeons.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction pour donner la plus grande valeur suivie d'une parenthèse, soit Max(,
• Sur la feuille Villes, désigner la cellule au-dessus du calcul, soit E2,

Nous spécifions ainsi le point de départ de la plage de cellules pour identifier les codes postaux correspondants.

• Taper le symbole deux points (:) pour générer la plage de cellules E2:E2,

En l'état, cette plage ne désigne qu'une cellule. Et c'est là que l'astuce fondamentale réside. Nous allons figer la borne supérieure seulement. De fait, la borne inférieure, libre de tout mouvement, va se déplacer avec le calcul répliqué. Cette plage va grandir au fur et à mesure des codes postaux étudiés. C'est ainsi que l'incrémentation est rendue possible.

• Cliquer entre la lettre E et le chiffre 2 de la première référence,
• Puis, enfoncer la touche F4 du clavier, ce qui donne : $E$2:E2,
• Cliquer ensuite à la fin de la formule pour y replacer le point d'insertion,
• Fermer alors la parenthèse de la fonction Max,
• Incrémenter cette valeur d'une unité, soit : +1,
• Puis, taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets et fermer la parenthèse de la fonction Si, soit : ''),
• Valider le calcul par le raccourci clavier CTRL + Entrée,

Comme vous le savez parfaitement désormais, cette combinaison de touches permet de valider la formule, tout en conservant active la cellule du résultat. C'est ainsi que nous pouvons l'exploiter dans la foulée, afin de reproduire sa logique.

• Double cliquer sur la poignée de la cellule du résultat pour répliquer sa logique sur le tableau,

Bien sûr, en l'état, le résultat est vierge partout. Aucun code postal n'a été saisi dans la feuille Formulaire. Et comme le stipule la branche Sinon de la fonction Si, en l'absence de correspondance, la cellule est conservée vierge.

• En cellule C6 de la feuille Formulaire, saisir le code postal 83840,

De retour sur la feuille Villes, vous remarquez que tous les codes postaux concordants sont repérés par des numéros incrémentés.

La syntaxe complète de la formule que nous avons bâtie est la suivante :

=SI(D3=Formulaire!$C$6;MAX($E$2:E2) + 1;'')

Extraire toutes les villes d'un code postal
Nous devons désormais exploiter ce résultat intermédiaire pour extraire toutes les villes correspondantes, à partir de la cellule H3 de la feuille Villes. C'est ainsi que nous allons construire la source de données pour la future liste déroulante.

L'astuce consiste à extraire la ville pour chaque numéro repéré. C'est lui qui donne l'information sur l'indice de ligne pour l'extraction. L'indice de colonne est connu. La ville est inscrite dans une rangée bien déterminée de la base de données.

La fonction Excel Index permet de restituer une information de base de données, située au croisement d'une ligne et d'une colonne.

=Index(Tableau_de_recherche; Numéro_de_ligne; Numéro_de_colonne)

Pour lui fournir cet indice variable de ligne, nous devons imbriquer la fonction Excel Equiv dans son deuxième argument :

=Equiv(Colonne_de_recherche; Valeur_cherchée; Mode_de_correspondance)

La valeur cherchée par la fonction Equiv est ce numéro incrémenté. Nous devons donc lui imbriquer la fonction Excel Ligne en deuxième argument. Elle retourne le numéro de ligne d'une cellule. Si nous ne figeons pas cette dernière, avec le déplacement de la formule, elle fournira tous les numéros incrémentés à retrouver.

Enfin, pour éviter toute erreur en retour, nous devons englober le tout dans la fonction Excel SiErreur. Cette dernière permet d'intercepter les potentielles anomalies et de les gérer.

• Sélectionner la première cellule pour l'extraction, soit H3,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau de recherche par ses étiquettes de colonne, soit : B:D,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Inscrire la fonction pour l'indice de ligne, suivie d'une parenthèse, soit Ligne(,
• Sélectionner une cellule de la première ligne, par exemple A1,
• 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 de recherche en cliquant sur son étiquette E, soit : E:E,

En cas de souci, vous pouvez inscrire manuellement ses bornes.

• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Equiv,
• Inscrire le chiffre 0 pour réaliser une recherche stricte et non pas approximative,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,
• Saisir le chiffre 2 pour indiquer la colonne des villes,
• 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,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe et il s'agit de la ville repérée par le premier numéro.

• Cliquer et glisser la poignée de la formule sur quelques lignes vers le bas,

Toutes les villes repérées par le pointage précédent, sont fidèlement extraites. Si vous changez le code postal dans la feuille Formulaire, tous les repérages et extractions se mettent instantanément à jour dans la feuille Villes. Nous avons donc réussi à bâtir la source de données pour la liste déroulante. Il reste néanmoins à établir la jonction dynamique. La formule d'extraction que nous avons construite, est la suivante :

=SIERREUR(INDEX(B:D;EQUIV(LIGNE(A1); E:E; 0); 2); '')

Source de données variables
Vous l'avez constaté, en réalisant quelques essais sur différents codes postaux, le nombre des résultats proposés par l'extraction varie. Et c'est tout à fait logique. Donc un problème se pose. Comment définir précisément la plage de cellules, devant servir de source, pour remplir la liste déroulante des villes suggérées. Il n'est pas question de désigner une plage volontairement très grande pour prévoir tous les cas. Toutes les cellules vierges, non marquées par l'extraction, seraient restituées dans la liste. L'ergonomie de notre solution technique en pâtirait considérablement.

La solution consiste premièrement à nommer le point de départ de la zone d'extraction. Puis, nous devons exploiter la fonction Excel Decaler, afin de faire varier sa hauteur en fonction du contenu détecté. Nous l'avions largement mise en lumière dans la formation Excel pour créer des listes et des recherches dynamiques.

Mais avant cela, nous devons repérer la hauteur des données extraites par une valeur numérique. C'est elle qui permettra de compter les lignes.

• En cellule G3, à gauche de la première valeur extraite, taper la formule suivante :

=SI(H3<>'';1;'')

Elle est très simple. Lorsqu'une donnée extraite est détectée, le chiffre 1 est inscrit dans la cellule en regard. Le cas échéant, la cellule est conservée vide.

• Après validation de la formule, double cliquer sur sa poignée pour la répliquer,

Grâce à ces chiffres, nous pouvons désormais exploiter la fonction Excel NB sur cette plage. Elle retourne le nombre de valeurs numériques comptabilisées. Donc, elle renseigne sur le nombre de lignes du tableau d'extraction.

Dans d'autres situations, nous avions exploité la fonction Excel NbVal. Elle compte toutes les cellules non vides. A priori, elle pouvait donc faire l'affaire directement sur la colonne des villes extraites. Mais en raison des doubles guillemets exploités dans la fonction SiErreur, bien que vides en apparence, les cellules du dessous ne sont pas considérées comme telles par Excel.

• Sélectionner maintenant la cellule de la première ville extraite, soit H3,
• Dans la zone Nom en haut à gauche de la feuille, saisir le mot Liste,
• Puis, valider nécessaire cette inscription par la touche Entrée du clavier,

Nous venons d'attribuer un nom au point de départ de cette plage d'extraction. Nous devons maintenant le rendre dynamique par une formule. Elle consiste à exploiter la fonction Decaler. Comme son nom l'indique, à partir d'un point de départ renseigné, elle permet notamment de faire varier la hauteur, selon le contenu détecté :

=Decaler(Cellule_depart; Decalage_ligne; Decalage_colonne; [Hauteur] ; [Largeur])

• 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,

Une boîte de dialogue apparaît. Elle énumère les plages nommées recensées sur ce classeur. Nous n'avons défini qu'un seul nom dans ce projet et il apparaît sélectionné par défaut. La zone Fait référence à, en bas de la boîte de dialogue, indique quelle est la plage désignée. C'est cette syntaxe que nous devons rendre dynamique par la fonction Decaler.

• Placer le point d'insertion juste après le symbole égal (=),
• Saisir la fonction suivie d'une parenthèse, soit Decaler(,

De cette manière, le premier argument est déjà renseigné. La cellule H3 est effectivement le point de départ de la plage d'extraction à adapter en hauteur.

• Placer le point d'insertion juste après cette cellule H3,
• Puis taper un point-virgule (;) suivi du chiffre 0, soit : ;0,

Selon la syntaxe de cette fonction, nous indiquons ainsi qu'aucun décalage en ligne n'est à opérer par rapport à la cellule de départ.

• Taper de nouveau un point-virgule (;) suivi du chiffre 0, soit : ;0,

Nous spécifions ainsi qu'aucun décalage en colonne n'est à observer par rapport à la cellule de démarrage.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur à définir,

C'est elle qui doit varier dynamiquement selon le contenu détecté par la fonction NB.

• Saisir la fonction suivie d'une parenthèse, soit : NB(,
• Puis, désigner l'intégralité de la colonne en cliquant sur son étiquette G, soit : Villes!$G:$G,
• Fermer la parenthèse de la fonction NB,
• Puis, fermer la parenthèse de la fonction Decaler,
• Cliquer sur le bouton de la coche verte à gauche de la formule,

Si aucune erreur n'est retournée, cela signifie qu'Excel valide la syntaxe.

• Cliquer sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille Excel,

Pour l'instant, rien ne certifie que la plage évolue selon nos souhaits. Nous allons devoir le vérifier.

Liste déroulante et contenu variable
Il est donc temps d'établir la liaison. La liste déroulante des villes sur la feuille Formulaire doit se nourrir des résultats dynamiques d'extraction, produits en colonne H de la feuille Villes. Et c'est donc la plage nommée liste qui l'identifie en s'adaptant à sa hauteur. C'est ce nom qui doit être utilisé dans la relation à établir.

• En bas de la fenêtre Excel, cliquer sur l'onglet Formulaire pour activer sa feuille,
• Sélectionner ensuite la cellule de la ville, soit la case E6,
• 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 zone Autoriser de la boîte de dialogue qui suit, choisir Liste,

Attention, ce terme désigne une liste déroulante au sens large et non pas notre plage nommée. Pour cela, nous devons la désigner explicitement.

• En dessous, cliquer dans la zone de saisie Source pour l'activer,
• Puis, taper l'expression de relation suivante : =Liste,

C'est ainsi que nous spécifions de quel contenu dynamique la liste déroulante doit se nourrir.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider cette création,
• De retour sur la feuille, cliquer sur la flèche désormais visible en bas de la cellule E6,

Cette action a pour effet de déployer le contenu de la liste déroulante associée. Et comme vous pouvez le voir, toutes les villes extraites par notre précédente formule, sont strictement proposées. Il ne reste plus qu'à choisir.

• En C6, saisir le code postal : 05500,
• Puis déployer de nouveau la liste déroulante en E6,

Comme vous pouvez le voir, la liste des villes s'est automatiquement adaptée et elle s'est fortement allongée. Nous avons donc accompli notre mission consistant à charger dynamiquement une liste déroulante des villes associées à un code postal saisi. Dans une prochaine formation, nous exploiterons ces travaux pour créer un formulaire d'inscription avec Excel, sans l'appui du code VBA.