Lister tous les codes postaux au choix d'une ville

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


Sujets que vous pourriez aussi aimer :


Extraire les codes postaux d'une ville

Lister tous les codes postaux attachés au nom d'une commune revient à extraire toutes les informations associées à une référence. En France, le nom d'une même commune existe dans plusieurs départements. Grâce aux calculs matriciels, cette extraction minutieuse peut être réalisée sur la base d'une seule formule.



Sur l'exemple illustré par la capture ci-dessus, nous agissons sur une base de données de près de 40 000 enregistrements. Ils correspondent aux nombres de communes recensées en France. Selon la désignation du nom d'une ville dans le tableau de synthèse, tous les codes postaux associés sont livrés. Nous connaissons ainsi instantanément toutes les localités dans lesquelles cette commune existe.

Source et présentation
Cette base de données des communes de France est essentielle pour mener à bien cette étude.

• Télécharger le classeur liste-cp-villes.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é,

Les villes sont énumérées en colonne B de la base de données s'étendant jusqu'à la colonne E. Pour chacune, on y trouve notamment l'information sur le code postal et les coordonnées GPS. Si vous atteignez la fin du tableau par le raccourci clavier CTRL + Fin par exemple, vous constatez que la dernière commune est renseignée sur la ligne 39202.

Sur la droite, un petit tableau d'extraction est prévu entre les colonnes G et I. La cellule G4 doit accueillir la saisie du nom de la commune dont il s'agit d'énumérer les codes postaux. A validation, ces derniers ainsi que les coordonnées GPS doivent être listés dans les colonnes respectives H et I, à partir de la ligne 4.

En déployant la zone Nom en haut à gauche de la feuille Excel, vous constatez que des intitulés sont attribués à certaines colonnes de la base de données. Les communes sont identifiées par le nom nc tandis que les codes postaux et les coordonnées GPS sont identifiés par les intitulés cp et gps. Fort naturellement, nous exploiterons ces noms pour simplifier la construction de la formule matricielle. Les plages sont en effet trop hautes pour être désignées en toute ergonomie.

Extraire toutes les données d'une référence
Pour importer tous les codes postaux associés au nom de la commune saisi en cellule G4, nous devons bien sûr exploiter les fonctions d'extraction Index et Equiv. Mais pour regrouper toutes les positions concordantes trouvées par la fonction Equiv, nous devons exploiter la fonction Petite.Valeur. Cette analyse et ce regroupement sont néanmoins soumis à condition. Un critère doit être émis sur la colonne des communes pour vérifier la présence du nom tapé. La fonction conditionnelle Si est donc nécessaire. Elle doit permettre de restreindre le champ d'action pour l'analyse des codes postaux à restituer.

• Sélectionner la plage d'extraction pour les codes postaux, soit les cellules H4 à H18,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet, en cas de recherche infructueuse, les fonctions d'extraction retournent des messages d'erreur. Et vous vous en doutez, la recherche des concordances sur les codes postaux dans une base de données aussi volumineuse conduira à de très nombreux échec. Nous devons donc neutraliser ces retours d'erreur grâce à cette fonction.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des codes postaux par son nom, soit : cp,

Il s'agit naturellement de la rangée dans laquelle sont situées les données à extraire. Comme cette matrice n'est constituée que d'une seule colonne, nous n'aurons pas besoin de renseigner le troisième argument de la fonction Index. Il concerne l'indice de colonne pour l'extraction. Dans ce contexte, la fonction Index pointera implicitement sur cette rangée unique.

• Taper un point-virgule (;) pour passer dans l'argument des indices de ligne,
• Saisir la fonction des petites valeurs suivie d'une parenthèse, soit : Petite.Valeur(,

Ces positions en ligne, nous devons les trouver grâce à la fonction Equiv que nous exploiterons dans l'enchaînement. En effet, pour une extraction groupée des codes postaux, nous devons les réunir grâce à cette fonction Petite.Valeur qui va analyser leurs rangs respectifs en fonction de leurs classements.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

En effet, la restitution des codes postaux est sujette à condition. Ces derniers doivent correspondre avec la ville inscrite par l'utilisateur en cellule G4. Nous devons donc premièrement émettre ce critère sur la matrice des communes.

• Désigner la matrice des communes par son nom, soit : nc,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Désigner la cellule G4 en tapant ses coordonnées par exemple, soit : G4,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction cherchant ces positions, suivie d'une parenthèse, soit : Equiv(,

C'est effectivement dans la mesure où la condition sur la ville est remplie que la recherche des équivalences sur les codes postaux doit être amorcée.

• En guise de valeur cherchée, désigner de nouveau la plage des codes postaux, soit : cp,
• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction Equiv,
• En guise de tableau de recherche, désigner encore la plage des codes postaux, soit : cp,

Dans ce raisonnement matriciel, chaque code postal sera analysé sur l'intégralité de sa propre plage. Dès qu'il sera trouvé lui-même et que la concordance sur la ville est avérée, sa position sera extraite.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

Il n'est effectivement pas nécessaire de renseigner la branche Sinon de la fonction Si. Elle concerne les non concordances. De plus, nous gérons toutes les anomalies grâce à la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,

Il ne s'agit pas de lui fournir un rang unique mais tous les rangs potentiels permettant l'extraction de tous les codes postaux associés à la ville, au fur et à mesure. Leur nombre varie selon la commune. Il s'agit donc de prévoir large. Nous allons construire une matrice virtuelle d'une hauteur de 20 lignes pour permettre l'extraction au plus de 20 codes postaux.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

En guise de cellule, nous allons lui fournir la matrice de 20 lignes. Dans ce raisonnement matriciel, elle retournera dans l'ordre les 20 rangs pour la restitution ordonnée et groupée des codes postaux identifiés. Cette matrice est une construction. Elle doit donc être interprétée.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Puis, construire la matrice statique selon la syntaxe suivante : '1:20',

Il ne faut pas oublier les guillemets. Nous définissons bien une rangée débutant sur la première ligne et se terminant sur la vingtième.

• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour ignorer le résultat en cas d'erreur,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Les résultats se font attendre et pour cause. C'est une base de données de plus de 30 000 lignes qui est analysée, sous condition et dans un traitement récursif semblable à celui des boucles en programmation VBA Excel. Cependant, les codes postaux attachés à une même commune finissent par tomber.

Pour une vérification simple, il suffit de taper l'une des villes redondantes placée en haut de la liste, comme Abancourt. Une fois le traitement matriciel achevé, vous constatez que ses deux codes postaux associés sont parfaitement extraits. Certains noms de communes comme la ville de Valence se retrouvent dans de plus nombreux départements.

La formule matricielle que nous avons construite est la suivante :

{=SIERREUR(INDEX(cp; PETITE.VALEUR(SI(nc=G4; EQUIV(cp;cp;0)); LIGNE(INDIRECT('1:20')))); '')}



Pour l'extraction des coordonnées GPS, le principe est strictement identique. Il convient de remplacer la matrice cp par la matrice gps en premier argument de la fonction Index :

{=SIERREUR(INDEX(gps; PETITE.VALEUR(SI(nc=G4; EQUIV(cp;cp;0)); LIGNE(INDIRECT('1:20')))); '')}

Bien entendu, la plage de cellules I4:I18 doit être sélectionnée avant de construire le calcul. Et bien sûr, la formule matricielle doit être validée par le raccourci clavier CTRL + MAJ + Entrée.