recherche entre deux valeurs

[fnony]

Bonjour a tous

j'utilise excel sous windows (toutes versions)

même après recherche je ne trouve pas...

j'ai un fichier de "zone" qui me donne un tarif par rapport à un un kilométrage :

Agence ZONE km Min km MAX Tarif
xxx ZONE 1 0 35 1,00 €
xxx ZONE 2 36 50 2,00 €
xxx ZONE 3 51 70 3,00 €
xxx ZONE 4 71 135 4,00 €
xxx ZONE 5 136 250 5,00 €
xxx ZONE 6 251 500 6,00 €
xxx ZONE 7 501 10000 7,00 €
yyy ZONE 1 0 19 1,00 €
yyy ZONE 2 20 50 2,00 €
yyy ZONE 3 51 70 3,00 €
yyy ZONE 4 71 150 4,00 €
yyy ZONE 5 151 250 5,00 €


Je voudrais pouvoir trouver la zone dans laquelle se trouve le kilométrage ou je souhaite appliquer un tarif pour une agence/client précis:

en fait si je saisi le nom de l'agence et le kilometrage qu'excel puisse me donner la zone et le tarif. ensuite je ferais le calcul.

je verrais bien une formule avec sommeprod mais je ne sais pas comment je fais avec mes valeur min et max???
merci à vous tous de vos réponses
Francois

 

[Fred0o]

Re : recherche entre deux valeurs

Bonjour fnony,

Voici une réponse dans le fichier ci-joint, avec INDEX(EQUIV(.........))

Regarde la pièce jointe fnony_V1.xls

A+

 

[poulie]

Re : recherche entre deux valeurs

Bonjour fnony, Bonjour à tous,

Pour la zone, essaies cette formule en G8, sachant que j'ai mis en colonne A des villes dont Le Havre:

=RECHERCHEV(G5;$A$2:$B$19;2;0)

Sinon, tu as des tranches kilométriques en colonnes C et D différentes mais pour un même coût ?


Bonne réception

 

[fnony]

Re : recherche entre deux valeurs

Bonjour a vous tous
merci de vos apports mais cela ne marche pas pour ce que je veux, et/ou je ne sais pas comment utiliser vos apports
Pour plus d'explication j'ai effectivement des valeurs redontantes.
pour une agence/client j'ai plusieurs zones avec des valeur min et max .
Une zone = un tarif
un tarif = un Nb de KM compris entre deux valeurs (min et max)
le N° de la zone ainsi que le tarif peuvent changer en fonction du client et de l'agence.
Mon calcul porte sur les infos suivantes (voir PJ) : je connais mon nb de KM, je connais mon agence/client, je dois retrouver sa zone (ça c'est pour l'info) et son tarif pour pouvoir calculer le (tarif de la zone) * Nb de KM

j'espère que ces infos vous aiderons..


Pour info je ne suis pas borné sur le format de ma table "ZONE". (au lieu des deux colonnes km min et km max) une colonne pourrait suffire mais je ne sais pas comment traduire en excel.

a plus

 

[Fred0o]

Re : recherche entre deux valeurs

Re-bonjour fnony,

Ci-joint, une proposition qui je pense correspondra mieux à ton besoin. J'ai créé une colonne supplémentaire que j'ai appelé "Cas" très arbitrairement. Cette colonne sélectionne la ligne qui correspond aux critères et à partir de là, je peux facilement retrouver les valeurs recherchées.

Regarde la pièce jointe fnony_V2.xls

A+

 

[fnony]

Re : recherche entre deux valeurs

re Bonjour
merci fred0o : "Cette colonne sélectionne la ligne qui correspond aux critères et à partir de là, je peux facilement retrouver les valeurs recherchées "
ce facilement me plait beaucoup...)
c'est super .. Le résultat est ce que je voulais.
je vais travailler sur le tableau final ..
merci beaucoup de cette solution finale et un grand merci aux autres contributeurs..
Francois

 

[fnony]

Re : recherche entre deux valeurs

Bonsoir
Un petit complément de formule???

j'ai essayé ta formule. ça marche bien mais je me retrouve devant un autre cas de figure:
comment trouver ma zone et le tarif correspondant si j'ai des valeurs identiques dans mon tableau? sachant que pour compliqué j'ai rajouter une colonne "client"??
J'ai essayé avec rechercheV mais sans trouver:
en français:
je souhaite trouver le tarif et la zone d'un client appartenant à une agence sachant que ce tarif peut être différent suivant les zones mais que les zones peuvent avoir le même nom??
je t'ai joint le fichier revu en gardant tes formules
merci de ton apport éclairé
francois

 

[Fred0o]

Re : recherche entre deux valeurs

Bonsoir fnony,

Cette formule est à mettre en "G2" et à tirer vers le bas.

=SI(ET(A2=$I$5;B2=$I$7;D2<=$I$3;E2>=$I$3);"X";"")

Bien sûr, il te faut remettre les formules initiales en "I10" et "I13".

A+

 

[Tibo]

Re : recherche entre deux valeurs

Bonjour à tous,

un essai avec la fonction SOMMEPROD :

en I10 :

=INDEX(C:C;SOMMEPROD((A2:A29=I5)*(B2:B29=I7)*(D2:D29<I3)*(E2:E29>I3)*LIGNE(A2:A29)))

en I13, sa petite soeur :

=INDEX(F:F;SOMMEPROD((A2:A29=I5)*(B2:B29=I7)*(D2:D29<I3)*(E2:E29>I3)*LIGNE(A2:A29)))

Je te laisse tester

@+

 

[fnony]

Re : recherche entre deux valeurs

Bonjour

je vois qu'il y a des matinaux....
Merci à vous deux.. les deux formules fonctionnent.

Tibo: ta formule marche t'elle si l'on remplace les plages ($a2$a65000) par des noms ?
bonne journée
@+
F

 

[James007]

Re : recherche entre deux valeurs

Bonjour,

Effectivement, tu peux nommer tes plages dans toutes tes formules ...
accessoirement, cela les rend plus "lisibles" ...
Mais attention avec SOMMEPROD(), cette fonction ne peut pas fonctionner sur des colonnes entières (non limitées) ...

A +

 

[Tibo]

Re : recherche entre deux valeurs

Bonjour fnony,

Tibo: ta formule marche t'elle si l'on remplace les plages ($a2$a65000) par des noms ?

Tu peux faire référence à des plages nommées. Il faut simplement que chaque plage nommée ait la même dimension (même nombre de lignes)

Bonne journée à toi

@+

Edit : Bing ! Salut James

@+

 

[fnony]

Re : recherche entre deux valeurs

Bonjour

Une dernière question....
peut on utiser la formule décaler sur la formule de Tibo? pour avoir en j2 (par exemple) le résutat adjacent de la colonne E ou D (ou d'une colonne que je rajouterai plus tard) ?

 

[fnony]

Re : recherche entre deux valeurs

Bonjour James..
(prêt pour la nouvelle aventure de James.. Il parait que la scène d'entrée se fait dans la galerie des glace du chateau de versailles) )

merci pour ta réponse, c'est sans doute pour ça que j'ai eu des soucis recemment avec sommeprod..
je peux limiter mes colonnes au total Excel? ex: $a2$a65356?

 

[Tibo]

Re : recherche entre deux valeurs

re,

DECALER peut être effectivement une bonne méthode pour gérer la taille variable de ton tableau.

Cette formule :

=DECALER($A$1;1;0;NBVAL($A:$A)-1)

donne la liste des villes. Il faut adapter le 0 pour les autres listes.

Dans la formule en H10, ça donne ceci :

=INDEX(C:C;SOMMEPROD((DECALER($A$1;1;0;NBVAL($A:$A)-1)=I5)*(DECALER($A$1;1;1;NBVAL($A:$A)-1)=I7)*(DECALER($A$1;1;3;NBVAL($A:$A)-1)<I3)*(DECALER($A$1;1;4;NBVAL($A:$A)-1)>I3)*LIGNE(A2:A29)))

Je te laisse tester

@+