Discussion :

[bigboss60]

Bonjour,

J'ai en pièce jointe un fichier excel. En "Feuil 1", j'ai un bordereau d'envoi. je voudrais que en fonction du code postal que je mettrais en "D18", qu'apparaisse l'adresse qui est lié sur la "Feuil 2". Mais voila, il y a plusieurs codes postaux par départements, pour celà, je voudrais que la recherche se fasse sur les deux premier caractères de D18 et que l'adresse se colle aux cellules "E36", "E37" et "E39".

Merci d'avance de votre aide.

[Invité]

Bonjour Bigboss60,

Le plus simple serait d'utiliser la fonction "RECHERCHEV" mais pour cela il faut :
-que votre feuille 2 soit triée dans l'ordre ascendant sur la colonne "N° de département".
-si on ne veut pas trop compliquer la formule la colonne "Adresse" devrait être décomposée en 3 colonnes : Adresse, Code postal, Ville.

Sur votre exemple vous indiquez une adresse en feuille1 qui n'a rien avoir avec les adresses de la feuille2 ?

[Bigalo]

Bonsoir,

En définissant ainsi les noms CP et Adresse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CP		=Feuil2!$D$2:$D$99
Adresses	=DECALER(CP;0;-1)

On peut ensuite combiner INDEX() et EQUIV() pour récupérer l’adresse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Adresses;EQUIV(GAUCHE(D18;2);CP;0))

[Invité]

Bonsoir Bigalo,

Bien sûr on peut utiliser décaler et les cellules nommées (mais à mon avis non obligatoire) pour la recherche puisqu'il ne faut prendre que les 2 premiers caractères sur la feille1 en D18 mais le problème est sur la feuille2 où l'adresse comprend BP50000, le Code postal et la ville qu'il faut ensuite affecter dans 3 cellules différentes sur la feuille1.

Si BP est toujours 50000 pas de problème mais ce sera toujours le cas (si oui on peut là aussi utiliser décaler, puis pour le Code postal, puis pour la ville) ?

Un peu lourd comme formule alors que cela pourrait être très simple, non ?

D'autre part l'exemple d'adresse sur la feuille1 ne semble pas le prouver !

[Bigalo]

Bonsoir jacques_jean,

on peut utiliser décaler et les cellules nommées (mais à mon avis non obligatoire)

Pas obligatoire, c’est évident, mais les cellules nommées permettent se simplifier les formules ET d’améliorer leur lisibilité. Quant à DECALER() dans la référence associée à Adresses, cela rend plus claire la relation entre CP, (que j’aurais dû appeler Dép), et Adresses.

puisqu'il ne faut prendre que les 2 premiers caractères sur la feuille1 en D18

Que change le fait de ne devoir prendre que les 2 premiers caractères ?

mais le problème est sur la feuille2 où l'adresse comprend BP50000, le Code postal et la ville qu'il faut ensuite affecter dans 3 cellules différentes sur la feuille1

Quel problème ? Sur Feuille2, l’adresse est dans une cellule unique qui regroupe une boite postale, un code postal et la ville. Elle est donc facile à récupérer avec une formule unique. Qu’ensuite, on utilise cette formule dans 3 cellules distinctes devant afficher chacune la même info ne présente aucune difficulté particulière !

[bigboss60]

Ce qu'il me faudrait je pense, ce serait une fonction qui va comparé les deux premier caractère de mon code postale en D18 et ensuite faire une recherche sur la colonne D de la feuille 2, une fois l'équivalent trouvé, il va me copié l'adresse dans la feuille 1 en cellule E37. Vu que les colonnes nom et service sont toute pareils, je n'ai juste qu'a collé la colonne adresse correspondant au bon numéro de département. Est-ce possible de réaliser une telle fonction et comment l'écrire? J'utilise plus Excel en VBA et rarement les fonctions dans les tableau Excel. Merci beaucoup de votre aide.

Cordialement

[bigboss60]

Excusez moi pour l'adresse que j'ai mis en Feuille 1, j'avais mis ca en exemple.

[marc56]

Pour chaque département il y a une adresse de destination et ce que tu souhaites c'est la récupérer en saisissant uniquement le code postal. Ai-je bien compris?

[bigboss60]

Oui c'est tout à fait ca.

[Bigalo]

Ma solution sans VBA ne te convient pas ?

[marc56]

Regarde si cela te convient. J'ai changé la base qui s'appelle "Adresses", car il est plus facile de concaténer que de d'extraire. Si tu as besoin d'explications, n'hésites pas.

[bigboss60]

En fait je n'ai pas compris ta méthode sans VBA. Peut tu m'écrire la fonction, j'utilise rarement les fontions excel et j'ai un peu de mal à comprendre ton explication.

[marc56]

Tout d'abord, as-tu compris le rôle de la cellule I5, c'est comme si nous avions 2 parties dans ta feuille 1,
- A gauche la zone que tu vas imprimer (non définie ici) et
- A droite une zone de saisie pour les variables. Dans cette partie j'ai fait figurer l'adresse variable car je ne sais pas où elle doit aller.
J'ai créé une base de donnée, onglet "Adresse".Cette base est une zone qui va de A2 à G101 et porte le nom "Département".
Si tu regardes le code de la cellule :
=SI($I$5="";"";RECHERCHEV($I$5;Département;2))
Traduction : si la cellule I5 est vide, alors afficher vide,sinon rechercher la valeur I5 dans la zone "Département" et lire la deuxième cellule sur la ligne.
(La présence des $ n'est pas impérative ici, c'est pour pouvoir tirer la cellule vers le bas et faire toujours référence à la cellule I5 (position absolue).)
Idem pour la seconde ligne.
Pour la ligne 3, c'est presque la même chose, avec un assemblage de "BP" + le résultat de la recherche de la boite postale qui se trouve en 4 ème position sur la ligne.
=SI($I$5="";"";"BP "&RECHERCHEV($I$5;Département;4))

[Bigalo]

Re,

En fait je n'ai pas compris ta méthode sans VBA.

Quelle version d'Excel utilises-tu ? Etant donné que l'interface de la version 2007 est complètement différente, cette info me permettra d'adapter les explications.

[bigboss60]

J'utilise la version 2002, ta technique semble nikel Marc mais le problème c'est que je suis obligé de coupé mon code postal par exemple au lieux de mettre 60000 je dois écrire 60 000 et celà risque de ne pas trop plaire aux utilisateur qui préfèrerais tout saisir d'un coup avoir à changer de cellules pour écrire la suite du code postal. Merci beaucoup de votre aide.

[Bigalo]

Re,

• Sélectionne la Feuil2 puis la plage D2:99
• Ensuite, dans le menu Insertion, puis dans le sous-menu Nom, choisis Définir...

Dans la fenêtre de dialogue, entre comme nom Dép. En principe, la zone Fait référence à doit être ainsi préremplie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Feuil2!$D$2:$D$99

Répète l'opération, en définissant le nom Adresses, associé à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Dép;0;-1)

ce qui correspond aux cellules sur la même ligne que la plage Dép, mais une colonne avant (plus à gauche).


Tu peux ensuite combiner INDEX() et EQUIV() pour récupérer l’adresse, en entrant cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 =INDEX(Adresses;EQUIV(GAUCHE(D18;2);CP;0))

Dans les 3 cellules où l'adresse doit apparaître.

J'utilise la version 2002

[marc56]

Je ne coupe pas le code postal, tu veux peut-être parler de la boite postale?
Si c'est la cas, ce n'est pas coupé à la saisie, c'est le formatage qui l'affiche ainsi après saisie, donc pas de problème.
Combien d'adresses différentes en tout? 1 par département ou 1 pour plusieurs départements?

[bigboss60]

Une pour plusieurs départements

[marc56]

Combien d'adresses différentes, 3, 10,30?, cette question pour savoir s'il faut optimiser la base ou pas.

[bigboss60]

Il y a trois adresses diférentes, en fait j'ai réussi à le faire fonctionner avec la technique de Bigalo. Encore merci à vous tous de votre aide. J'ai pu voir qu'il est aussi possible de faire pas mal de chose sans avoir à passer par du VBA.

Encore merci pour vos réponse.