Problème d'arrondi par rapport à une liste de référence
Bonjour,
Je suis confronté à un problème d'arrondi que je n'arrive pas à résoudre:
J'ai d'une part une grille tarifaire 'A' par tranche de poids se présentant sous la forme ci-dessous:
Tranche de poids / tarif
5 /5.83
10 /5.94
20 /7.56
30 /9.99
40 /12.79
50 /13.22
100 /14.75
300 /20.33
500 /31.33
1000 /99.00
(NB: le tableau se lit ainsi --> de 0 à 5 kg, le tarif à appliquer et 5.83 EUR)
J'ai d'autre part un second tableau 'B' avec des poids auxquel je souhaite faire correspondre le tarif sur la base du tableau 'A'
poids /tarif
2.5 /5.83
7 /5.94
10 /5.94
13 /7.56
15 /7.56
23 /9.99
72 /14.75
150 /20.33
302 /31.33
999 /99.00
Question comment remplir la colonne 'tarif' du tableau 'B' automatiquement??
J'ai tenté les fonctions arrondi.sup, arrondi.inf, recherchev, plafond, plancher, arrondi.au.multiple... sans résultat probant.
L'idée serait d'éviter les solutions du type si <5 alors ; si <10 alors... (car les grilles tarifaires sont sur à chaque fois différentes)
La difficulté réside ici que les tranches de poids de mes grilles tarifaires n'ont pas d'intervalles régulier (ex: de 10 en 10 jusqu'a 100 kgs puis de 50 en 50 jusqu'à 250 kgs...)
La seule facon que j'ai trouvé pour contourner ce problème est de rajouter manuellement des lignes à mes grilles tarifaires ( au lieu d'avoir une tranche de 5 à 10 je vais avoir 5 tranches 6/7/8/9/10
Merci d'avance pour votre aide
Le challenge est lancé!! A vous de jouer!
Bonsoir,
et
regarde le fichier joint
PS, je ne trouve pas exactement comme toi.....
Qui a raison, qui a tort?
A toi de le dire....
merci cousinhub!
Très sympathique cette combinaison index / equiv.
Cependant mon exemple était correct, il faut lire la grille tarifaire ainsi:
de 0 à 5kgs inclus le prix sera de 5.83€
de 5 à 10kgs inclus le prix sera de 5.94€
etc
donc selon ce postulat un colis de 10kgs coutera 5.94€ ce qui ne colle pas tt à fait à ton résultat. (j'ai vu que tu avais décalé les tarifs par rapport au poids en inclus la tranche 0)
j'ai tenté de jouer sur l'attribut type de la fonction equiv mais je n'arrive pas au bon résultat.
Une autre idée peut être?
merci
Bonjour,
Une petite adaptation de la formule de Cousinhub :
Sur la base de son fichier, en E3 :
=INDEX($B$3:$B$12;EQUIV(D3-0.0001;$A$3:$A$13;1))à recopier vers le bas
Permet d'exclure ainsi la borne basse.
Il serait plus logique de mettre dans le tableau de référence :
5.01
10.01
20.01
etc.
auquel cas, la formule initiale de cousinhub devient correcte.
@+
Bonjour à tous,
thibo, ta formule marche mais n'est pas strictement rigoureuse
Si la quantité est comprise entre 10 et 10,0001 kg, ce qui est certes peu probable en
situation réelle, le tarif sera de 5,94 alors qu'en théorie il devrait être égal à 7,56 car
strictement supérieur à 10 kg
Donc voici une adaptation de la formule de cousinhub (et qui a l'air de marcher
=SI(ESTNA(EQUIV(D3;$A$3:$A$13;0));INDEX($B$3:$B$12;EQUIV(D3;$A$3:$A$13;1));INDEX($B$3:$B$12;EQUIV(D3;$A$3:$A$13;1)-1))Le raisonnement est le suivant :
Si la quantité (D3) est égale à une des tranches, on a juste à prendre le tarif de la tranche
précédente.
Par contre, il y a peut-être moyen de raccourcir la formule ou de trouver une autre
formule illustrant le raisonnement ci-dessus?
Quoi qu'il en soit, je laisse l'optimisation aux meilleurs formulistes que moi! 8)
Voici le fichier correspondant : https://www.excel-pratique.com/~files/doc2/baya_v2.xls
J'ai laissé la formule de cousinhub pour comparaison.
A+
Bonjour à tous
Ici, aussi, 'ça' marche, sauf qu'un des résultats demandé est incohérent
https://www.excel-pratique.com/~files/doc2/baya_v3.xls
Cordialement
Amadéus a écrit :...Ici, aussi, 'ça' marche, sauf qu'un des résultats demandé est incohérent...
Euh.. donc ça ne marche pas? Si un des résultats est incohérent!
En effet, avec ta formule amadéus, on retrouve le même résultat que cousinhub! A savoir,
pour une quantité de 10 kg, on a un tarif de 7,56 au lieu des 5,94 souhaités par baya!
Bonjour vba-new
Euh.. donc ça ne marche pas? Si un des résultats est incohérent!
Je me permet de dire incohérent parce que le raisonnement logique qui vient à l'esprit est un tarif par tranche de poids de...à... et là ça coince;
Remarque que pour avoir le même résultat que celui souhaité..après tout, pourquoi pas ? Il reste le fameux SI qui dirait avant la formule utilisée dans le tableau
=SI( Valeur=10;5,94; La formule) et le tour est jouè.
Cordialement
Salut Amadéus
Amadéus a écrit :Je me permet de dire incohérent parce que le raisonnement logique qui vient à l'esprit est un tarif par tranche de poids de...à... et là ça coince;
En effet, mais c'est baya qui a défini les tranches de poids et tant qu'on n'aura pas plus de
précisions, faudra qu'on fasse avec ce qu'on a
Amadéus a écrit :...Il reste le fameux SI qui dirait avant la formule utilisée dans le tableau
=SI( Valeur=10;5,94; La formule) et le tour est jouè.
Re-en effet, en fait, moi j'étais parti sur une formule générale qui marcherait quelque soit le poids!
Mais bon, à baya de nous dire ce qui est nécessaire ou pas.
A+
Merci à tous pour vos réponses!
Effectivement je cherche une formule qui fonctionnerait quelque soit le poids (le SI me parait un peu laborieux à mettre en place)
En ce qui concerne la définition des tranches de poids, je suis malheureusement dépendant de mes transporteurs ce qui signifie que je n'ai pas trop de contrôle sur leur format de tarifs.
La proposition de VBA news me semble correcte. Je vais la tester dès demain "dans la vraie vie" avec différentes grilles.
Merci pour vos différentes propositions. je vous tiens au courant.
++
Ça marche baya.
Par contre, si par la suite tu pouvais nous joindre un fichier qui reprennent la structure de
tes données tarifaires, ça nous aiderait grandement.
A+
Merci bcp pour votre aide. la formule de vba new fonctionne en condition réelle.
Promis la prochaine fois j'apprend à poster un fichier
a+