Recherche dans tableau
Bonjour,
Je suis actuellement entrain de faire un fichier excel assez complet qui pourra m'aider par la suite à effectuer un voyage de 3 mois aux USA. J'aimerai que lorsque je choisi une ville de départ et d'arrivée, le fichier me donne diverse information (que vous pouvez retrouver dans le fichier que je vais joindre avec ce message, si vous voulez mieux comprendre ce que je veux faire). J'ai réussi à insérer une liste déroulante en cascade à 3 niveaux après avoir regarder sur divers forum, mais je n'arrive pas à faire en sorte que le fichier excel me donne l'horaire d'arrivée, l'heure locale et le prix du billet, qui se trouve dans le même tableau que pour les autres informations. J'aurai donc aimé savoir comment je pouvais faire pour que ce problème soit résolu ?
Je suis là pour apprendre, donc si vous modifiez le fichier excel directement, n'hésitez pas à poster un commentaire pour m'expliquer comment vous avez fait
Merci d'avance
P.S. : Je sais que ce doit être un peu confus ce que je demande, c'est pourquoi j'ai joint le fichier excel qui sera certainement beaucoup plus explicite
bonjour
je ne comprend pas comment marche ce qui devrait normalement etre une abaque
exemple : tu decolles de miami à 9:00 qu'est qui empeche d'atterrir a chicago a 23:00 il y a bien des departs ok ; mais les arrivées
j'imagine bien qu'ells doivent etre > au depart mais de combien ?
cordialement
Bonsoir,
Voici ce que j'ai fait, dis moi si cela correspond à tes attentes et je t'explique comment ca fonctionne.
Bonne soirée
Sha'
Tout d'abord merci à tous d'avoir essayer de résoudre mon problème
tulipe_4 : En fait la page que je vous ai envoyé correspond aux vols Paris-USA. Je n'ai donc pas mis de contrainte pour que le retour soit obligatoirement plus loin dans le temps que l'aller, puisque seul moi et des amis allons participer à se voyage.
Sharney : Ta solution est certainement celle qui se rapproche le plus de ce que je cherche. Cependant, lorsque je choisi Delta comme compagnies, excel me met dans les cellules "#REF". De plus, pour le prix du billet retour, le prix n'est pas le bon et la feuille choisi non plus. Mais je vais tout de même essayer de travailler sur ces formules pour que ça fonctionne. Si jamais tu veux bien m'expliquer comment tu en es arrivé à ces formules, cela pourrait m'aider à les changer pour arriver au résultat que j'attend
Jean-Eric : J'ai regardé ce que tu as fait, c'est pas mal non plus ! Mais le problème, c'est que lorsque que l'on choisi la compagnie, même les horaires de l'autre s'affiche.
Mais merci d'avoir à tous essayé, je vais utiliser tout vos fichiers pour essayer de régler mon problème en attendant d'autre réponse
Salut,
Ma difficulté est que je ne connais pas très bien la formule que j'ai utilisé (j'en apprends encore) mais je vais essayer de simplifier :
=INDEX(Aller!$A$2:$F$177;SOMMEPROD(EQUIV(Choix!B$3;Aller!$A$2:$A$177;0)*EQUIV(Choix!B$9;Aller!$B$2:$B$177;0)*EQUIV(Choix!B$11;Aller!$C$2:$C$177;0));4)qui se décompose ainsi :
INDEX(Aller!$A$2:$F$177;2ePartie;4)qui permet de renvoyer la valeur correspond à une référence de ligne et de colonne dans une matrice. la référence de ligne étant déterminée par la deuxième partie du code.
SOMMEPROD(EQUIV(Choix!B$3;Aller!$A$2:$A$177;0)*EQUIV(Choix!B$9;Aller!$B$2:$B$177;0)*EQUIV(Choix!B$11;Aller!$C$2:$C$177;0))qui permet de déterminer la référence de la ligne à choisir. Le premier équiv donne la référence de la 1ere ligne où l'on retrouve la ville dans la colonne 1 de l'abaque, le 2e equiv donne concernant la compagnie, et le 3e concernant l'heure de départ.
Le seul soucis est que la fonction SOMMEPROD réalise le produit de ces trois références je crois, ce qui te crée l'erreur... Il me reste à trouver la solution pour améliorer cela.
Sinon, concernant les données Retour, il suffit de remplacer les Aller! par des Retour! dans les formules
D'accord Sharney, merci
J'ai remplacé "Aller!" par "Retour!".
J'ai aussi essayé de trouver l'erreur dans cette formule, mais je ne trouve pas :/. Au delà du fait que ça ne donne pas les 2 dernières informations bonne, je ne comprend pas non plus pourquoi lorsque l'on choisi "Delta" à la place d'"American Airlines", aucun résultat ne s'affiche. Pourtant je reste persuadé qu'il ne doit pas y avoir avoir grand chose à changer à cette formule qui me parait être la plus logique. La première idée que j'avais concernant la formule était avec la recherche horizontale. Après je ne m'y connait pas encore assez avec Excel pour trouver comment, mais si quelqu'un peut nous aider
Salut,
Je crois que j'ai trouvé l'astuce. En réalité quand on utilise SOMMEPROD, cela permet de multiplier/additionner des références. Donc il multipliait les références et demandait de récupérer la donnée inexistante.
Du coup j'ai modifié les formules et maintenant les données sont bonnes.
SOMMEPROD(EQUIV(Choix!B$3;Aller!$A$2:$A$177;0)+EQUIV(Choix!B$11;Aller!$C$2:$C$177;0)-1)Le premier EQUIV recherche la ville de départ dans le 1ère colonne et renvoie le numéro référence de la première apparition de celui-ci (Miami = 41e référence). Comme j'ai remarqué que les horaires sont toujours les mêmes, le deuxième EQUIV permet de retrouver l'heure de départ dans la 3e colonne et renvoie le numéro référence de la première apparition de celui-ci (12:00 = 4e référence). Le -1 permet de compenser l'addition précédente.
Ce qui donne par exemple pour Miami 12:00 : 41 + 4 - 1 = 44e référence (ouligne 45) soit, ce que l'on souhaite
Bonne soirée
J'ai regardé, si l'on descend du nombre de case indiqué par la formule, et qu'on remonte qu'une case, on arrive au bonne endroit. Je n'ai plus qu'a trouver comment utiliser cela pour que le résultat s'affiche et je mettrais en ligne le fichier final, pour que d'autre puisse se servir de ce sujet pour résoudre un problème
Merci à tous, je vous donne des nouvelles pour vous dire si le sujet est résolu ou non
En fait il faudrait juste que je trouve une formule, je ne sais pas si vous pouvais m'aider.
Imaginons qu'en A1 j'ai une case avec un nombre variable. Prenons un exemple avec A1=6
J'aimerai que A2 soit égale à une autre case avec une formule de ce type dans la cellule:
B(1+A1) de tel sorte que dans notre exemple on ait :
B(1+A1) = B(1+6) = cellule B7
C'est possible ?
Merci d'avance
Finalement j'ai trouvé la solution !
J'ai ajouté la formule de Sharney dans la cellule H2 en ajoutant +1 à la fin pour avoir le bon résultat. J'aurai aussi pu changer la matrice. J'ai ensuite inséré en B13 la formule suivante :
=INDEX(Aller!A1:F177;Choix!H2;4)
Aller!A1:F177 : Correspond à la matrice
Choix!H2 : Ligne recherché dans le tableau
4 : Numéro de la colonne du tableau
Ensuite, je n'ai plus qu'a changé les deux derniers paramètres pour tomber sur la bonne cellule et le tour est joué
Aaah j'ai oublié de mettre le fichier à jour. Il n'y a pas besoin d'utiliser une cellule différente, cela peut facilement être pris en compte directement dans ta cellule de résultat.
De plus, si tu modifies ta matrice pour ne pas prendre en compte la ligne 1 (des titres) mais commencer en $A$2, tu n'as pas besoin d'ajouter +1. Mais cela peut être une façon de faire aussi.
En tout cas, voici le fihcier mis à jour de mon côté.
Finalement j'ai encore un problème :/
Dès que je change les horaires, le numéro affiché par la formule de Sharney n'est plus bon :/
Je vous envoi le fichier que j'ai modifié
Salut,
Peux-tu me confirmer une chose : est-ce que tous les horaires de départ seront à 100% différents ou y'a-t-il des doublons ? S'ils sont tous exactement différents, les formules peuvent être :
=INDEX(Aller!$A$2:$F$177;EQUIV(Choix!B$11;Aller!$C$2:$C$177;0);4)
=INDEX(Aller!$A$2:$F$177;EQUIV(Choix!B$11;Aller!$C$2:$C$177;0);5)
=INDEX(Aller!$A$2:$F$177;EQUIV(Choix!B$11;Aller!$C$2:$C$177;0);6)(De même en remplaçant Aller par Retour)
Si certains horaires sont en double, je vais voir si je trouve la solution.
Cordialement,
Sha'
EDIT : Voici ci-joint une version avec une formule qui devrait fonctionner en permanence, même avec des doublons. Elle est très longue mais ça a le mérite de fonctionner.
=SI(B$3=Aller!$A$177;INDEX(Aller!$A$170:$F$177;EQUIV(Choix!B$11;Aller!$C$170:$C$177;0);4);INDEX(INDEX(Aller!$A$2:$F$177;EQUIV(B$3;Aller!$A$2:$A$177;0);1):INDEX(Aller!$A$2:$F$177;EQUIV(INDEX(Aller!$H$2:$H$23;EQUIV(B$3;Aller!$H$2:$H$23;0)+1;1);Aller!$A$2:$A$177;0)-1;6);EQUIV(B$11;INDEX(Aller!$A$2:$F$177;EQUIV(B$3;Aller!$A$2:$A$177;0);3):INDEX(Aller!$A$2:$F$177;EQUIV(INDEX(Aller!$H$2:$H$23;EQUIV(B$3;Aller!$H$2:$H$23;0)+1;1);Aller!$A$2:$A$177;0)-1;3);0);4))Merci beaucoup Sharney, ça marche à la perfection !
Mais de rien.
Au cas où tu aurais un bug et que cela t'intéresse, voici en gros, comment ça marche :
=SI(B$3=Aller!$A$177;Formule1;Formule2)Si la ville de départ est le dernier choix possible, j'effectue la Formule 1 sinon j'effectue la Formule 2.
Formule 1 :
INDEX(Aller!$A$170:$F$177;EQUIV(Choix!B$11;Aller!$C$170:$C$177;0);4)Dans la plage de données $A$170:$F$177 (correspondant au dernier choix possible), je cherche la ligne correspond à mon horaire choisi et renvoie la valeur en colonne 4 (heure arrivée), colonne 5 (heure réelle) ou colonne 6 (tarif).
Formule 2 :
INDEX(Réf1 : Réf2;EQUIV(Choix!B$11;Réf1* : Réf2* ; 0);4)Dans la plage de données Réf1:Réf2 (dépendant au choix de la ville de départ, cf ci-dessous), je cherche la ligne correspondant à mon horaire choisi et renvoie la valeur en colonne 4 (heure arrivée), colonne 5 (heure réelle) ou colonne 6 (tarif).
Réf1 :
INDEX(Aller!$A$2:$F$177;EQUIV(B$3;Aller!$A$2:$A$177;0);1)Je cherche la première cellule portant le nom de la ville de départ dans la plage de données $A$2:$A$177 et récupère la référence de la colonne 1 (soit A2 à A170)
Réf2 :
INDEX(Aller!$A$2:$F$177;EQUIV(INDEX(Aller!$H$2:$H$23;EQUIV(B$3;Aller!$H$2:$H$23;0)+1;1);Aller!$A$2:$A$177;0)-1;6)J'ai un double INDEX/EQUIV. Celui du centre me permet de chercher la valeur de la ville de départ dans la plage de données $H$2:$H$23 et me renvoie la valeur de la ligne suivante (Par exemple : Chicago 2 si j'ai sélectionné Chicago 1) Ensuite je cherche cette donnée dans la table de données $A$2:$A$177 et récupère la référence de la ligne précédente, colonne 6.
Ainsi mes références me permettent de chercher dans une table de données restreinte en fonction du choix de la ville de départ.
Les Réf1* et Réf2* sont dans le même principe mais renvoie obligatoirement dans la colonne 3 afin de pouvoir effectué la fonction EQUIV.
A noter : On est obligé de distinguer la formule 1 pour la dernière ville car la formule 2 ne fonctionne pas pour celle-ci, notamment, Excel renverrait une erreur #REF car ne trouverait pas la cellule qui correspond pour Ref2.
Bonne journée,
Sha'
D'accord, merci de m'avoir expliqué
Je ne sais pas comment tu as fais pour trouver cette formule, mais bien joué