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
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
A voir également:
- Formules
- Formules - Télécharger - Études & Formations
- Formules excel de base - Guide
- Formules de politesse mail anglais - Guide
- Excel a rencontré un problème au niveau d'une ou de plusieurs références de formules ✓ - Forum Excel
- Problème de Formule Excel 2013 ✓ - Forum Excel
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
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
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
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
18 nov. 2021 à 15:58
Merci mais pouvez vous me dire :
Merci bien
- 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
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
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)
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
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
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
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
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
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
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
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
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
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
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
19 nov. 2021 à 16:16
Et voici
https://www.cjoint.com/c/KKtpp3R3tKd
https://www.cjoint.com/c/KKtpp3R3tKd
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
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
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
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
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 ?
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
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
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é)
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
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
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
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
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
22 nov. 2021 à 16:30
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
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
Je n'ai peut-être pas tout compris mais pourquoi pas simplement :
=RECHERCHEV(GAUCHE(A2;3);Feuil2!$A$1:$B$16;2;FAUX)
Cordialement
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
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
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
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
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
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
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
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 ?
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
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
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
=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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
cela semble fonctionner
Je ne manquerai pas de vous recontacter si je rencontre d'autres pb
En tous les cas super et merci,