Excel : renvoyer valeur d'une liste
Résolu
Sacha68
Messages postés
20
Date d'inscription
Statut
Membre
Dernière intervention
-
pépé35530 Messages postés 2943 Date d'inscription Statut Membre Dernière intervention -
pépé35530 Messages postés 2943 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
J'ai un casse-tête sur Excel.
J'ai dans une feuille une liste avec des contacts : nom, prénom, numéro de rue, intitulé de rue ; dans une deuxième feuille j'ai une liste avec : intitulé de rue, numéro de début de rue, numéro de fin de rue, quartier correspondant.
Mon souhait est de rechercher dans la deuxième liste le quartier correspondant à l'adresse de mon contact.
Il n'y aurait pas de problème si chaque rue dépendant d'un seul quartier mais malheureusement certaines rues en fonction du nuémro appartiennent à un quartier différent.
Pour les rues avec un seul quartier j'ai utilisé la formule suivante =SI(liste_rue_parConseil_Quart!F2=0;RECHERCHEV(F2;liste_rue_parConseil_Quart!$A$2:$E$1021;5;FAUX);)
Je serais ravie d'une petite piste !
Merci à tous les courgeux et courageuses
Sacha
J'ai un casse-tête sur Excel.
J'ai dans une feuille une liste avec des contacts : nom, prénom, numéro de rue, intitulé de rue ; dans une deuxième feuille j'ai une liste avec : intitulé de rue, numéro de début de rue, numéro de fin de rue, quartier correspondant.
Mon souhait est de rechercher dans la deuxième liste le quartier correspondant à l'adresse de mon contact.
Il n'y aurait pas de problème si chaque rue dépendant d'un seul quartier mais malheureusement certaines rues en fonction du nuémro appartiennent à un quartier différent.
Pour les rues avec un seul quartier j'ai utilisé la formule suivante =SI(liste_rue_parConseil_Quart!F2=0;RECHERCHEV(F2;liste_rue_parConseil_Quart!$A$2:$E$1021;5;FAUX);)
Je serais ravie d'une petite piste !
Merci à tous les courgeux et courageuses
Sacha
A voir également:
- Excel : renvoyer valeur d'une liste
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
- Si ou excel - Guide
- Excel moyenne - Guide
9 réponses
Bonjour,
j'ai trouvé une formule, attention, elle est compliquée :
=SI(liste_rue_parConseil_Quart!F2=0;SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;4;;);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0);4;;));)
Cette formule fonctionne si tu as deux quartiers pour une même rue.
je vais t'expliquer son fonctionnement, pour une partie :
SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;4;;)
il faut savoir que la fonction équivalence a la même fonction que recherchev sauf qu'au lieu de retrouver une valeur, ca va te donner le premier numéro de ligne ou se trouve ta rue.
la fonction décaler permet de décaler à partir d'une référence du nombre de lignes et de colonnes.
dans l'exemple :
DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)
on part de la feuille liste_rue_parconseil_quart A2, on reste sur la ligne où est trouvé ton numéro et on va chercher ton numéro de début.
on ajoute une fonction et :
ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6
==> si ton numéro de début <= a A6 (ton numéro de rue) et si ton numéro de fin >= a A6 alors, on affiche cette ligne, sinon, la ligne du dessous
cela suppose que ta feuille est triée par quartier.
essaie cette formule. décompose la au pire et dis moi ce que cela donne.
j'ai trouvé une formule, attention, elle est compliquée :
=SI(liste_rue_parConseil_Quart!F2=0;SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;4;;);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0);4;;));)
Cette formule fonctionne si tu as deux quartiers pour une même rue.
je vais t'expliquer son fonctionnement, pour une partie :
SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;4;;)
il faut savoir que la fonction équivalence a la même fonction que recherchev sauf qu'au lieu de retrouver une valeur, ca va te donner le premier numéro de ligne ou se trouve ta rue.
la fonction décaler permet de décaler à partir d'une référence du nombre de lignes et de colonnes.
dans l'exemple :
DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)
on part de la feuille liste_rue_parconseil_quart A2, on reste sur la ligne où est trouvé ton numéro et on va chercher ton numéro de début.
on ajoute une fonction et :
ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$a$1021;0)-1;2;;)>=A6
==> si ton numéro de début <= a A6 (ton numéro de rue) et si ton numéro de fin >= a A6 alors, on affiche cette ligne, sinon, la ligne du dessous
cela suppose que ta feuille est triée par quartier.
essaie cette formule. décompose la au pire et dis moi ce que cela donne.
Merci Mélanie pour ta formule ! Pour l'instant je ne l'ai que vaguement testé. En l'état ça ne renvoyait pas les bonnes valeurs.
J'ai donc modifié la mienne en intégrant la tienne à partir de equiv et decaler pour voir ce que cela donnait
=SI(liste_rue_parConseil_Quart!F2=0;RECHERCHEV(F2;liste_rue_parConseil_Quart!$A$2:$E$1021;5;FAUX);SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;4;;);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0);4;;)))
Ca marche toujours pour les rue à un quartier mais pous les autres ça me renvoit des quartiers certes mais pas les bons.
A vrai dire j'ai un peu de mal à rentrer dans la formule. Je m'y plongerai demain et te tiendrai au courant du résultat.
Merci déjà pour cette piste que je vais exploiter :)
J'aime les défis ;)
Sacha
J'ai donc modifié la mienne en intégrant la tienne à partir de equiv et decaler pour voir ce que cela donnait
=SI(liste_rue_parConseil_Quart!F2=0;RECHERCHEV(F2;liste_rue_parConseil_Quart!$A$2:$E$1021;5;FAUX);SI(ET(DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;1;;)<=A6;DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;2;;)>=A6);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0)-1;4;;);DECALER(liste_rue_parConseil_Quart!$A$2;EQUIV(F2;liste_rue_parConseil_Quart!$A$2:$A$1021;0);4;;)))
Ca marche toujours pour les rue à un quartier mais pous les autres ça me renvoit des quartiers certes mais pas les bons.
A vrai dire j'ai un peu de mal à rentrer dans la formule. Je m'y plongerai demain et te tiendrai au courant du résultat.
Merci déjà pour cette piste que je vais exploiter :)
J'aime les défis ;)
Sacha
J'ai fait des essais et essaie de décortiquer la formule. Je n'arrive pas vraiment à comprendre le >=A6 et pour la fonction DECALER je vois qu'il y un argument, celui qui correspond à "colonne" qui varie : 1 - 2 - 4 - 4. Je ne comprends pas bien le deux fois 4.
Pour que je comprennne mieux je crois qu'il faudrait que l'on parte sur un tableau identitique. J'ai déposé un fichier allégé sur http://dl.free.fr/getfile.pl?file=/HE7CXvcN
Il faut savoir aussi que certaines rue peuvent avoir 1, 2, 3 ou x quartiers ; cet élément n'est pas figé.
En tout cas merci pour ton aide et pour tes pistes !!!!
Sacha
Pour que je comprennne mieux je crois qu'il faudrait que l'on parte sur un tableau identitique. J'ai déposé un fichier allégé sur http://dl.free.fr/getfile.pl?file=/HE7CXvcN
Il faut savoir aussi que certaines rue peuvent avoir 1, 2, 3 ou x quartiers ; cet élément n'est pas figé.
En tout cas merci pour ton aide et pour tes pistes !!!!
Sacha
Bonjour,
Excusez l'incruste...
Viens en plus de la problématique initiale, s'ajouter le problème des numéros pairs et impairs qui peuvent ne pas appartenir au même quartier.
Fort de cela, je penses que la solution passera forcément par une macro, ou alors une formule usine à gaz.
Je peux me tromper bien sur. Mais bon si vous n'êtes pas allergique à VBA...
Excusez l'incruste...
Viens en plus de la problématique initiale, s'ajouter le problème des numéros pairs et impairs qui peuvent ne pas appartenir au même quartier.
Fort de cela, je penses que la solution passera forcément par une macro, ou alors une formule usine à gaz.
Je peux me tromper bien sur. Mais bon si vous n'êtes pas allergique à VBA...
Bonjour,
Ci joint un lien vers un fichier qui pourrait répondre à ton problème :
http://www.cijoint.fr/cjlink.php?file=cj201108/cijxhVYgyx.xls
La colonne D permet de calculer un numéro de ligne
=EQUIV(D2;Feuil2!$A$2:$A$4;0)
La colonne E affiche le quartier :
=SI(C2<=RECHERCHEV(D2;Feuil2!$A$2:$D$4;3;FAUX);INDEX(Feuil2!$A$2:$D$5;E2;4);INDEX(Feuil2!$A$2:$D$5;E2+1;4))
A+
pépé
Ci joint un lien vers un fichier qui pourrait répondre à ton problème :
http://www.cijoint.fr/cjlink.php?file=cj201108/cijxhVYgyx.xls
La colonne D permet de calculer un numéro de ligne
=EQUIV(D2;Feuil2!$A$2:$A$4;0)
La colonne E affiche le quartier :
=SI(C2<=RECHERCHEV(D2;Feuil2!$A$2:$D$4;3;FAUX);INDEX(Feuil2!$A$2:$D$5;E2;4);INDEX(Feuil2!$A$2:$D$5;E2+1;4))
A+
pépé
Merci Pépé pour ce fichier ! Effectivement avec ton exemple ça fonctionne bien :) mais lorsqu'il n'y a pas de numéro de rue ça ne fonctionne plus :(
J'ai tenté de rajouter des choses dans ta formule et ça corrige certaines erreurs mais pas toutes. Voici le lien http://dl.free.fr/getfile.pl?file=/CpdwkIlH avec ta formule et ma formule corrigée.
Merci en tout cas :) :)
J'ai tenté de rajouter des choses dans ta formule et ça corrige certaines erreurs mais pas toutes. Voici le lien http://dl.free.fr/getfile.pl?file=/CpdwkIlH avec ta formule et ma formule corrigée.
Merci en tout cas :) :)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour,
tu n'avais pas précisé ce cas.
Peut-il y avoir plus de deux quartiers ?
Je revois mon fichier pour intégrer ta condition supplémentaire.
es-tu sous excel 203 ou Excel 2003 ou 2011 ?
A+
pépé
tu n'avais pas précisé ce cas.
Peut-il y avoir plus de deux quartiers ?
Je revois mon fichier pour intégrer ta condition supplémentaire.
es-tu sous excel 203 ou Excel 2003 ou 2011 ?
A+
pépé
Bonjour,
J'ai mofifié la formule en colonne F pour tenir compte des rues sans numéros.
=SI(ESTNA(E2)=FAUX;SI(C2<=RECHERCHEV(D2;Feuil2!$A$2:$D$4;3;FAUX);INDEX(Feuil2!$A$2:$D$5;E2;4);INDEX(Feuil2!$A$2:$D$5;E2+1;4));RECHERCHEV(D2;liste_rues;4;FAUX))
la colonne E sera masquée
A+
pépé
J'ai mofifié la formule en colonne F pour tenir compte des rues sans numéros.
=SI(ESTNA(E2)=FAUX;SI(C2<=RECHERCHEV(D2;Feuil2!$A$2:$D$4;3;FAUX);INDEX(Feuil2!$A$2:$D$5;E2;4);INDEX(Feuil2!$A$2:$D$5;E2+1;4));RECHERCHEV(D2;liste_rues;4;FAUX))
la colonne E sera masquée
A+
pépé
Bonsoir,
Je n'ai pas tout testé.
Précaution : il faut que dans la base de données des rues, le tri soit fait correctement à savoir que si deux quartiers pour une rue, les infos doivent impérativement se suivre.
Bonne soirée
pépé
Je n'ai pas tout testé.
Précaution : il faut que dans la base de données des rues, le tri soit fait correctement à savoir que si deux quartiers pour une rue, les infos doivent impérativement se suivre.
Bonne soirée
pépé
Pépé, j'ai dû adapter ta formule et intégrer un NBSI mais je crois que c'est bon :)
Voici le fichier si ça peut vous servir http://dl.free.fr/getfile.pl?file=/8eTwPhIX
Mélanie avais-tu pu trouver la solution avec la fonction DECALER ?
Ben franchement merci pour votre aide, c'est chouette !
Sacha
Voici le fichier si ça peut vous servir http://dl.free.fr/getfile.pl?file=/8eTwPhIX
Mélanie avais-tu pu trouver la solution avec la fonction DECALER ?
Ben franchement merci pour votre aide, c'est chouette !
Sacha
Bonsoir,
D'après ta demande, je pensais que certaines rues n'avaient pas de numéro. En réalité, le SI que j'avais rajouté était inutile puisque je faisais le test sur la colonne indiquant le numéro de ligne.
Mais ta solution de voir si une adresse est unique ou sur plusieurs quartiers évite de faire apparaître un message d'erreur en colonne N° Correspondance.
Bonne continuation
pépé
D'après ta demande, je pensais que certaines rues n'avaient pas de numéro. En réalité, le SI que j'avais rajouté était inutile puisque je faisais le test sur la colonne indiquant le numéro de ligne.
Mais ta solution de voir si une adresse est unique ou sur plusieurs quartiers évite de faire apparaître un message d'erreur en colonne N° Correspondance.
Bonne continuation
pépé