Formules

Fermé
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 - 18 nov. 2021 à 15:30
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 - 24 nov. 2021 à 15:46
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

17 réponses

via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
18 nov. 2021 à 15:43
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
18 nov. 2021 à 15:58
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
18 nov. 2021 à 16:34
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)
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
18 nov. 2021 à 17:03
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
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
19 nov. 2021 à 09:55
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
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
19 nov. 2021 à 13:34
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
19 nov. 2021 à 16:16
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
19 nov. 2021 à 17:34
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
22 nov. 2021 à 12:36
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 ?
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
22 nov. 2021 à 15:15
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é)
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
22 nov. 2021 à 16:04
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
22 nov. 2021 à 16:30
0
ALS35 Messages postés 1033 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 9 janvier 2024 139
22 nov. 2021 à 16:45
Bonjour à tous,

Je n'ai peut-être pas tout compris mais pourquoi pas simplement :
=RECHERCHEV(GAUCHE(A2;3);Feuil2!$A$1:$B$16;2;FAUX)

Cordialement
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
Modifié le 22 nov. 2021 à 18:21
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

0
ALS35 Messages postés 1033 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 9 janvier 2024 139
22 nov. 2021 à 18:48
Bonjour via55,

Oui, et si on utilise un tableau structuré nommé Villes pour la correspondance, on n'a plus rien à faire tout est dynamique
https://www.cjoint.com/c/KKwrVDEY6C7

Cordialement
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
23 nov. 2021 à 12:36
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 ?
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
23 nov. 2021 à 13:25
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
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
24 nov. 2021 à 11:17
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
0
ALS35 Messages postés 1033 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 9 janvier 2024 139
24 nov. 2021 à 11:30
Bonjour,

Bah non, la formule est faite pour trouver les trois caractères de gauche de ton dossier, si dans le tableau de correspondance tu ne mets que deux lettres cela ne marche pas ! Si tu mélange maintenant des abréviations de deux ou trois lettres il faut peut-être reprendre la formule plus complexe de via55, salutations.

Cordialement
0
via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024 2 735
24 nov. 2021 à 12:52
Re,
J'avais bien précisé dans mon dernier fichier qu'il fallait toujours un code de 3 caractères
puisque la formule recherche en comparant les 3 premiers caractères pour LLxxxxx elle recherche LLXdan sle tableau de correspondance et ne trouve pas
Mais en fait en réfléchissant bien, (pourquoi n'y ai-je pas pensé plus tôt !!?), à condition de n'avoir que des codes avec 3 ou 2 caractères pas besoin de la lourde formule matricielle du début il suffit en E2 de mettre :
=SIERREUR(RECHERCHEV(GAUCHE(A2;3);Villes;2;0);RECHERCHEV(GAUCHE(A2;2);Villes;2;0))
et de l'étirer vers la bas
Cdlmnt
Via
0
ALS35 Messages postés 1033 Date d'inscription jeudi 18 juillet 2019 Statut Membre Dernière intervention 9 janvier 2024 139 > via55 Messages postés 14495 Date d'inscription mercredi 16 janvier 2013 Statut Membre Dernière intervention 7 novembre 2024
24 nov. 2021 à 14:10
Bonjour à tous, salutations via55,

Et même un autre SIERREUR sur le deuxième RECHERCHEV pour éviter le NA si aucune correspondance en deux ou trois lettres :
=SIERREUR(RECHERCHEV(GAUCHE(A2;3);Villes;2;0);SIERREUR(RECHERCHEV(GAUCHE(A2;2);Villes;2;0);""))

Cordialement
0
Chavanie Messages postés 83 Date d'inscription mardi 13 juillet 2021 Statut Membre Dernière intervention 20 septembre 2024 1
24 nov. 2021 à 15:46
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,
0