Formules Fermé

Question

Bonjour
J’ai environ 15 000 lignes que j’extrais environ tous les 2 jours (et donc les chiffres changent)
Chaque ligne commence par les 3 premieres lettres d’une ville (BOR pour Bordeaux, NIC pour NICE, PAL pour PALAISEAU etc….) suivis d’un numero → colonne A. En tout je dois avoir environ 20 villes
Je voudrai trouver une formule qui affiche automatiquement le nom des villes en entier et sans les numeros MAIS dans une autre colonne
Exit les VBA et les TCD
Merci pour vos retours
Bonne journee

via55 · Answer

Bonjour

IL te faut un tableau de correspondance que tu peux faire sur une autre feuille avec en 1ere colonne les 3 lettres de code et en colonne 2 les noms des villes en entier
Ensuite tu sélectionnes ce tableau entier puis clic droit Définir un nom et tu lui donnes un nom par exemple listevilles
Dans ta feuille d'extraction dans le colonne où tu veux le nom complet, dans la 1ere cellule de rerour ,par exemple en E2, tu mets la formule :
=RECHERCHEV(A2;listevilles;2;0)
Puis tu étires ta formule jusqu'en bas

Cdlmnt
Via

Chavanie · Answer

Merci mais pouvez vous me dire : 

 a quoi corresponde le 2 et le 0 ? (jai bien vu qu'en changeant le 2 en 3 cela affiche l'integralite de la ville)
Merci bien

Chavanie · Answer

J'ai une version 2013 (et pour certains de mes collegues une version 2007), et comme pour une liste deroulante; il est difficile de faire coincider sur 1 autre onglet.
Lorsque je le mets plus loin dans la feuille et que je tape ladite formule dans le tableau de 15000 lignes j'ai le message d'erreur suivant #N/A (pourtant le tableau est bien selectionne)

via55 · Answer

Le 2 correspond à la colonne de la valeur à retourner, donc ici la 2eme et 0 demande une correspondance exacte

Cela fonctionne parfaitement avec une liste nommée d'un autre onglet, exemple fait sur Excel 2007 :
https://www.cjoint.com/c/KKsqbuN4Dgg

Si tu n'arrives pas à mettre en place sur ton fichier, poste un exemple (allégé, pas besoin de 15000 lignes) de ton fichier sur cjoint et reviens ici colle le lien créé, on regardera

Chavanie · Answer

Merci c'est gentil
Voici donc
En colonne B, je veux que le nom de la ville s'inscrive directement en fonction des 3 premieres lettres le chaque ligne de la colonne A
J'ai envoye le fichier

via55 · Answer

Bonjour

Tu as peut être posté le fichier mais tu n'as pas communiqué le lien créé en le collant ici, donc je ne peux pas y accéder
Recommence l'opération en copiant bien le lien bleu qui s'affiche quand tu as posté ton fichier et en le collant ici dans ton prochain message, comme j'avais fait pour mon fichier dans mon message n° 4

Chavanie · Answer

Et voici
https://www.cjoint.com/c/KKtpp3R3tKd

via55 · Answer

Ah oui c'est plus complexe alors, je n'avais pas compris que les lettres étaient au début d'une chaîne de caractères
Ensuite tu parles de 3 lettres mais dans ton fichier il n'y a parfois que 2 lettres !

Quoiqu'il en soit, en passant par une formule matricielle on arrive à extraire ces lettres et à faire une RECHERCHEV :
https://www.cjoint.com/c/KKtqFvCxJZg

Ton tableau des villes de la 2eme feuille ne doit comporter qu'une seule fois chaque ville

Chavanie · Answer

Re bonjour. 
et encore merci
J'ai re ecrit la formule dans mon tableau: =RECHERCHEV(SUBSTITUE(A2;STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);SOMME((ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1))))*1));"");VILLES;2;0)

Cependant, je me heurte au message d'erreur suivant #NOM?. Cela est peut etre du au fait que la formule ne fait pas reference a la feuille 2 ?

via55 · Answer

Re

Cela vient du fait que n'as pas suivi à la lettre mon exemple et que le nom villes n'est pas trouvé par Excel 

En effet villes n'est pas une adresse mais le nom de la plage contenant ton tableau, plage que tu dois nommer au préalable avant d'appliquer la formule
Comme dit dans mon 1er message une fois table de correspondance créée en feuille 2 tu sélectionnes toute cette plage, puis clic droit Nommer une plage (si tu es en Excel 2007, Définir un nom pour les versions postérieures) et tu lui attribues le nom villes
Tu peux aussi nomme le plage en allant dans Données - Gestionnaire de noms (c'est là qu'on la retrouve de toutes façons après l'avoir nommée, regarde dans l'exemple que je t'avais envoyé)

Chavanie · Answer

Effectivement, j'ai denomme la plage et ca va beaucoup mieux. 
Mais par contre, j'ai toujours le probleme pour les villes qui commencent par 2 lettres suivis des chiffres cad que jai 2 villes qui commencent et qui se ressemblent (MI2 5289725 et MI37892034) et pour lesquelles j'ai le message #N/A

Chavanie · Answer

https://www.cjoint.com/c/KKwpEIBHgqd

via55 · Answer

Bonjour

Le problème était dans le premier exemple donné qu'il y avait des codes sur 3 caractères et des codes sur 2 caractères d'où les matricielles pour extraire la partie numérique
Maintenant selon le dernier fichier envoyé il semble bien  qu'il n'y ait que des codes à 3 caractères (lettres ou lettres et chiffre) donc ce cas oui bien sûr il est plus simple (et il vaut mieux) d'utiliser simplement la formule =RECHERCHEV(GAUCHE(A2;3);villes;2;0) 
Par contre il faut rectifier dans le Gestionnaire de noms la plage de données villes qui ne se réfère qu'à A$1:$B$15 au lieu de A$1:$B$16

L'intérêt de la plage nommée est que si on modifie le tableau de correspondance en rajoutant des villes par exemple, il suffit de modifier l'étendue de la plage dans le Gestionnaire de noms au lieu d'avoir à la modifier dans toutes les formules

On pourrait même nommer cette plage de manière dynamique (avec la fonction DECALER) pour la référence s'adapte automatiquement à la longueur du tableau (qu'on ajoute ou enlève des items) mais pour une liste courte et peu amenée à être modifiée ce n'est pas utile

https://www.cjoint.com/c/KKwrtjo6i3g

Cdlmnt
Via

Chavanie · Answer

Bonjour
En tous les cas merci pour vos patiences. et vos reponses
Sur vos tableaux cela semble fonctionner. Je vais l'essayer et le retranscrire sur mon tableau de 15 000 lignes dans la semaine. 
Je ne manquerai pas de vous tenir au courant.
Par contre, moi qui suis peu aguerrie, sur excel, (jai toujours un mal fou meme avec la formule SI), pourriez vous m expliquer votre formule en langage "vulgarise" ou "debutant moyen" svp ?

via55 · Answer

Bonjour,

=RECHERCHEV(GAUCHE(A2;3);villes;2;0) 
La formule est une RECHECHEV basique qui fonctionne selon la syntaxe 
=RECHERCHEV(valeur cherchée; plage où chercher; n° de la colonne de la valeur à retourner; correspondance exacte (0) ou approchée (1) 

Ici cela se complique légèrement car la valeur cherchée est à extraire d'une chaîne plus longue; il faut uniquement les 3 premiers caractères, ceux de gauche d'où la partie GAUCHE(A2;3)

Cdlmnt
Via

Chavanie · Answer

Super merci beaucoup !
J'ai juste une petite difficulte (en orange pour LLxxx) : je ne comprends pas pourquoi jai le message d'erreur #N/A puisqu il s agit du meme principe que pour les dossiers commencant par 2 lettres.
De plus, en le "mettant" dans un tableau, je pensais que cela allait se mettre automatiquement !


 https://www.cjoint.com/c/KKykmjlfYsd

Chavanie · Answer

Excusez du retard, ma page de CCM ne s'actualise pas automatiquement. Je pensais.... d'ou mon retard de reponse
 cela semble fonctionner
Je ne manquerai pas de vous recontacter si je rencontre d'autres pb
En tous les cas super et merci,

Formules

17 réponses

Discussions similaires

Newsletters