[Résolu] Définir le pays en fonction d'une adresse postale

[TNTDAVID]

Bonjour,

J'ai une liste de clients avec les adresses postal ou les villes en information.
Malheureusement dans ces adresses il n'est pas indiqué le Pays!
Existe t-il une fonction dans le tableur OpenOffice que serait capable de définir en fonction de l'adresse de quel pays il s'agit?
Je pourrais ainsi classer mes clients en fonction de leurs nationalités.

Merci d'avance pour votre aide.

David Barros

[tintin]

Bonjour et bienvenue,

pays en fonction d'une adresse postal ou ville

Alors là ?
Quid des homonymies ?

• Paris France, Paris Texas ?
• Brest France, Brest Biélorussie ?
• Tripoli Libye, Tripoli Liban ?
• Sydney Australie, Sydney Canada ?
• etc.

Bon courage

OpenOffice 4.1.6

Votre signature fait mention d'une version d'OpenOffice qui n'est plus maintenue.
Si c'est une obligation ou un choix mentionnez-le dans votre signature.

Connaître la version de Apache OpenOffice (AOO) ou LibreOffice (LibO) installée

Afin de profiter des dernières améliorations, il est fortement recommandé d'être à jour de la dernière versio

Dans votre signature veuillez ajouter la version exacte du système d'exploitation (Windows XP SP3, Vista SP2, Windows 7 SP1, Win 8.1, Win 10, Ubuntu 20.04, macOS 11.4 par exemple).
Accès direct à votre signature pour la corriger.

Cordialement.

[Kev74]

Bonjour,

je pense qu'il faudrait créer une fonction en fonction des codes postales des pays, comme dit tintin par ville, ça peut être problématique, mais si le format de chaque code postal est différent pour chaque pays, cela est possible, sinon, est-ce que la zone que vous avez est limité à un certain nombre de pays, exemple, que les pays de l'union européenne car ça risque d'être difficile pour tous les 210 pays du monde?

Exemple, en France, on a 5 chiffres, il faudrait créer une fonction CodePostal qui lorsqu'elle détecte 5 chiffres, cela renvoie France. Mais est-ce que dans un autre pays, est-ce qu'il y a des codes postaux identiques à la France ?

J'espère que j'ai pu mettre en piste certains volontaires ici

Au revoir

[Jean-Louis Cadeillan]

Bonsoir,

Quid des homonymies ?

+1; il n'y a pas de correspondance biunivoque entre un nom de ville et un pays. Pour lever cette ambiguïté, on peut s'y prendre en 2 temps :

• l'automaticité de la fonction RECHERCHEV() qui récupère le pays le plus probable d'une ville donnée (classer les pays en fonction de cette probabilité), méthode heuristique ;
• la possibilité semi-automatique de proposer par liste déroulante tous les pays (et seulement les pays) où cette ville existe, en utilisant une liste de validité basée sur la fonction SI().

À tester, ci-joint.
Cordialement,
Jean-Louis

[TNTDAVID]

Merci pour votre aide très précieuse.

Si c'est une obligation ou un choix mentionnez-le dans votre signature.

En effet je travail sur une ancienne version car j'utilise rarement OpenOffice et je ferrais donc une mise à jours.

Mais est-ce que dans un autre pays, est-ce qu'il y a des codes postaux identiques à la France ?

Malheureusement oui mais votre idée était bonne.

À tester, ci-joint.

Excellent la fonction RECHERCHEV() mais dans mon cas il y a bien trop de villes et de Pays, je vais donc simplifier!

Je souhaite savoir si l'adresse est en France ou bien à l'étranger.
Si dans l'adresse le mot "Rue " est trouvé, je considérait alors que le client est Français si non il sera étranger.

Code : Tout sélectionner

=SI(OU(ESTNUM(TROUVE("Rue "; B2)));"FRANCAIS";"ETRANGER")

Mais il y a d'autres types de voie comme; Avenue, Chemin, impasse, allée etc...
Savez vous comment créer une formule qui renvoie "FRANCAIS" si l'une des expression de la liste "Type de voies" est trouvé dans l'adresse du client?
Est-il possible de ne pas prendre en compte les majuscules ou minuscules lors de la comparaison?

Merci d'avance pour votre aide.

David Barros

[Jean-Louis Cadeillan]

Bonsoir,
Sur un échantillon d'adresses (voir ci-joint), en retenant 11 noms de voies différents, j'ai un taux de vrais négatifs de près de 20% avec la piste (encore une ) que tu envisages de prendre pour déterminer si l'adresse est française ou non…

Savez vous comment créer une formule qui renvoie "FRANCAIS" si l'une des expression de la liste "Type de voies" est trouvé dans l'adresse du client?

La formule en B2 est :

Code : Tout sélectionner

=SI(MAX(SI(ESTERR(CHERCHE($Voies.$A$1:$A$20;A2));0;1));"FRANÇAIS";"ÉTRANGER")

J'ai mis les noms de voies dans une autre feuille, la feuille Voies : dans cet exemple, on pourrait en ajouter encore 9. La formule est matricielle et se valide par Ctrl+Maj+Entrée.
Pour recopier une formule matricielle vers le bas :

• il suffit de positionner la souris dans le coin inférieur droit de la cellule : le curseur se transforme en croix (+), puis, il faut cliquer/glisser vers le bas tout en appuyant simultanément sur la touche Ctrl ;
• une autre manière de faire est de copier la cellule initiale (Ctrl+C), puis de sélectionner la plage des cellules qui doivent contenir la même formule, puis de coller (Ctrl+V).

Est-il possible de ne pas prendre en compte les majuscules ou minuscules lors de la comparaison?

utiliser CHERCHE() en lieu et place (encore une ) de TROUVE().
Cordialement,
Jean-Louis

[Kev74]

Bonsoir,

Oui, c'est vrai pour les codes postales, ça risque d'être difficile mais la "typographie" de chacun est différent, j'ai vu qu'en Pologne, ils mettent un tiret du 6 et en France, non par exemple.

Sinon, j'ai récupéré un fichier avec plusieurs villes du monde entier, est-ce que cela vous intéresse que je vous le donne en fichier joint avec la formule à ajouter dès que je serai de retour ? Par contre, les villes seront écrites en Anglais, est-ce que ça pose problème déjà pour vous même si je mets le pays en Français à côté ? Sauf si la réponse de Jean-Louis Cadeillan vous convient et qu'il vous semble que de savoir le pays n'est plus nécessaire.

Bonne soirée

[TNTDAVID]

CODE : TOUT SÉLECTIONNER   AGRANDIR
=SI(MAX(SI(ESTERR(CHERCHE($Voies.$A$1:$A$20;A2));0;1));"FRANÇAIS";"ÉTRANGER")
J'ai mis les noms de voies dans une autre feuille, la feuille Voies : dans cet exemple, on pourrait en ajouter encore 9. La formule est matricielle et se valide par Ctrl+Maj+Entrée.

Merci pour votre exemple très détaillé!
Ou avez vous enregistré la feuille Voies?
Pouvez vous la joindre?

De mon côté j'ai crée une feuille que j'ai nommé "Voies" avec 54 types de voies de A1 à A54 que j'ai enregistré dans le même dossier que la liste de mes clients.

Voies.ods

Puis j'ai appliqué votre formule dans mon tableau des clients:

Code : Tout sélectionner

=SI(MAX(SI(ESTERR(CHERCHE($Voies.$A$1:$A$54;B2));0;1));"FRANÇAIS";"ÉTRANGER")

Après avoir appliqué la formule avec un Ctrl+Maj+Entrée, une fenêtre qui détaillait les fonctions "SI(MAX(SI(ESTERR(CHERCHE" c'est ouvert en me proposent de corriger automatiquement une erreur.
A présent même si je supprime la formule et je recommence la procédure la fenêtre qui détail les fonctions ne s'ouvre plus.

Si j'observe ma cellule je trouve le code si dessous:

Code : Tout sélectionner

=SI(MAX(SI(ESTERR(CHERCHE($#REF !.$A$1:$A$54;B2));0;1));"FRANÇAIS";"ÉTRANGER")

"$Voies" et remplacé par "$#REF !" et le résutat est toujours "ÉTRANGER".

Je ne parvient pas à comprendre a quel moment je fait erreur?

Oui, c'est vrai pour les codes postales, ça risque d'être difficile mais la "typographie" de chacun est différent, j'ai vu qu'en Pologne, ils mettent un tiret du 6 et en France, non par exemple.

Les Etats-unis on des codes postaux à 5 chiffres comme en France.

Sinon, j'ai récupéré un fichier avec plusieurs villes du monde entier, est-ce que cela vous intéresse que je vous le donne en fichier joint avec la formule à ajouter dès que je serai de retour ? Par contre, les villes seront écrites en Anglais, est-ce que ça pose problème déjà pour vous même si je mets le pays en Français à côté ? Sauf si la réponse de Jean-Louis Cadeillan vous convient et qu'il vous semble que de savoir le pays n'est plus nécessaire.

Oui bien-sur ça pourrait me servir!

Merci d'avance pour votre aide précieuse.

[Jean-Louis Cadeillan]

Bonsoir,

Où avez vous enregistré la feuille Voies?

J'ai dans l'idée que tu confonds les mots « Feuille » et « Classeur ». Un classeur est un fichier (.ods, en l’occurrence) qui peut comporter plusieurs feuilles. L'accès à chacune des feuilles d'un classeur se fait généralement en cliquant sur l'onglet correspondant, les onglets étant les uns à côté des autres en bas de la fenêtre. Donc tu n'as pas vu l'onglet Voies à droite de l'onglet Feuille1 de mon fichier…
L'erreur #REF! vient de ce que la feuille Voies n'était pas présente dans ton fichier.
Au passage, tu pouvais très bien mettre la liste des noms possible de voies sur la même feuille que celle de ta liste de clients, quitte à cacher la colonne. L'avais opté pour une feuille séparée pour plus de clarté (mal m'en a pris ).
Si tu renseignes de tes 53 noms de voies A2 à A54 de cette feuilles Voies, la formule correspondante en Feuille1 sera :

Code : Tout sélectionner

=SI(MAX(SI(ESTERR(CHERCHE($Voies.$A$2:$A$54;A2));0;1));"FRANÇAIS";"ÉTRANGER")

toujours en validation matricielle.

Cordialement,
Jean-Louis

[TNTDAVID]

J'ai dans l'idée que tu confonds les mots « Feuille » et « Classeur ».

En plein dans le mille.
Désolé mais je débute avec les tableurs.

Maintenant tous fonctionne bien et j'aimerai ajouter une condition à cette formule:

Code : Tout sélectionner

=SI(MAX(SI(ESTERR(CHERCHE($Voies.$A$2:$A$54;B2));0;1));"FRANÇAIS";"ÉTRANGER")

Si dans la colonne "D" les mails finissent en ".fr", la réponse doit être "FRANÇAIS" même si l'adresse postal n'a pas de consonance Française.

Comment réadapter la formule pour ajouter cette nouvelle vérification?

Merci encore pour votre aide.

[Kev74]

Bonsoir, je pense que c'est cela que vous cherchiez :

Code : Tout sélectionner

=SI(OU(MAX(SI(ESTERR(CHERCHE($Feuille1.$A$2:$A$54;B2));0;1));DROITE(D2;2)="fr");"FRANÇAIS";"ÉTRANGER")

, à valider comme écrit précédemment par JLC :

La formule est matricielle et se valide par Ctrl+Maj+Entrée.
Pour recopier une formule matricielle vers le bas :
il suffit de positionner la souris dans le coin inférieur droit de la cellule : le curseur se transforme en croix (+), puis, il faut cliquer/glisser vers le bas tout en appuyant simultanément sur la touche Ctrl ;
une autre manière de faire est de copier la cellule initiale (Ctrl+C), puis de sélectionner la plage des cellules qui doivent contenir la même formule, puis de coller (Ctrl+V).

Demain, je vous enverrai ce que j'ai fait avec plusieurs villes du monde entier, le souci, c'est qu'il y a presque 2 millions de villes et je crois pas que le site autorise un fichier tant volumineux à l'envoi...

Bonne nuit

[Jean-Louis Cadeillan]

Bonjour,

Si dans la colonne "D" les mails finissent en ".fr", la réponse doit être "FRANÇAIS" même si l'adresse postal n'a pas de consonance Française.

Cela ne nous dit pas de manière explicite la table de vérité :

TNTDAVID_20210628_JLC03.jpg

La solution du OU() met « français » à la place de « ? »… je ne suis pas sûr que ce soit ce que tu souhaites.
Cordialement,
Jean-Louis

[TNTDAVID]

Bonsoir, je pense que c'est cela que vous cherchiez :
CODE : TOUT SÉLECTIONNER   AGRANDIR
=SI(OU(MAX(SI(ESTERR(CHERCHE($Feuille1.$A$2:$A$54;B2));0;1));DROITE(D2;2)="fr");"FRANÇAIS";"ÉTRANGER")

Oui c'est presque ça et j'ai fini par trouvé la solution grâce à votre exemple.

Code : Tout sélectionner

=SI(OU(MAX(SI(ESTERR(CHERCHE($Voies.$A$2:$A$67;B2));0;1));OU(DROITE(C2;3)=".fr"));"FRANÇAIS";"ÉTRANGER")

La solution du OU() met « français » à la place de « ? »… je ne suis pas sûr que ce soit ce que tu souhaites.

Oui c'est cela que je veut et j'ai fini par trouvé grâce au OU() que vous m'avez conseillé que j'ai ajouté au code de Kev74!

Merci à vous tous pour toutes cette aide.

[Jean-Louis Cadeillan]

Bonjour,

Oui c'est presque ça et j'ai fini par trouver la solution grâce à votre exemple.

Ton deuxième OU() ne sert à rien.Tu peux l'enlever (en ne laissant que DROITE(C2;3)=".fr"), ça ne changera pas le résultat de ta formule.

Oui c'est cela que je veux

Donc, si une adresse courriel se termine par « .en », et si son adresse postale comporte un nom de voie français (mais square et place sont aussi anglais, par exemple) tu considères que c'est un client français ?
Cordialement,
Jean-Louis

[Kev74]

Bonsoir,

du coup, j'ai réussi à créer une bonne "base de données" dans un tableur, ce qui n'est pas la meilleure solution mais vu que vous êtes sur un tableur.

https://cjoint.com/c/KGblzY0I2ss

[quote="Kev74"]il pèse environ 13 Ko[/quote]Il pèse environ 1,4 Mo

Voilà, il pèse environ 13 Ko, regardez si vous en avez vraiment besoin, et dites-moi si vous ne comprenez, dans la Feuille1, vous avez toutes les villes en anglais suivi des pays en français, beaucoup de villes en anglais ont le même nom qu'en français. Dans la Feuille2, vous mettez dans la colonne A le nom de la ville et en B, la recherche se fait automatiquement et prend en compte les homonymies, comme l'a fait JLC, n'oubliez pas à étirer la formule, si vous gardez la même feuille pour que ça marche avec d'autres villes. Juste, j'ai eu un petit souci, si on arrive à le résoudre. Exemple: Las Vegas est inscrite deux fois aux Etats-Unis, et je voudrai le mettre qu'une fois, malheureusement, je n y suis pas parvenu, mais ça devrait pas poser vraiment problème.

Bonne après midi

[Jean-Louis Cadeillan]

Bonjour,
Attention, sous OpenOffice, il faut, dans le menu Données > Validité > onglet Critères, cocher Trier les entrées dans l'ordre croissant, sinon, seules les entrées présentes dans les 20 premières lignes apparaîtront… or la formule retourne une plage de 1 048 576 lignes !
Sous LibreOffice, le fait de ne pas cocher Autoriser les cellules vides suffit à faire remonter les bonnes réponses dans la liste.
Cordialement,
Jean-Louis

[TNTDAVID]

du coup, j'ai réussi à créer une bonne "base de données" dans un tableur, ce qui n'est pas la meilleure solution mais vu que vous êtes sur un tableur.

Merci beaucoup pour votre travail!

Ton deuxième OU() ne sert à rien.Tu peux l'enlever (en ne laissant que DROITE(C2;3)=".fr"), ça ne changera pas le résultat de ta formule.

Je viens de tester et vous avez complètement raison!

Donc, si une adresse courriel se termine par « .en », et si son adresse postale comporte un nom de voie français (mais square et place sont aussi anglais, par exemple) tu considères que c'est un client français ?

Oui, il suffit que l'une des deux conditions soit rempli pour que je considère que mon client est francophone!

Grâce a votre aide, je peu a présent envoyez des mails de marketing dans la langue qui correspond le mieux au client.
Merci de m'avoir accompagné jusque au bout du sujet.

Cordialement

David Barros

[micmac]

Bonjour,

Problème résolu ?

Il est d'usage

1. d'ajouter manuellement au début du titre de votre premier message la balise [Résolu] (avec un R majuscule et sans oublier le e accentué, le tout entre crochets [ ])
2. et de cocher l'icône

Adaptez la longueur du titre à 60 caractères maximum, balises comprises.

Cordialement