Excel fonction si alternative imbriquée Résolu/Fermé

Question

Bonjour,

J'ai un gros problème avec cette formule de calcule.

Voici le problème, je dois faire une fonction SI imbriquée avec les différente règle de gestion qui son en faite

NOMBRE DE KILOMETRES:                                    Tarification: 

Jusqu'à 10 Km                                                     Terme fixe de 1,10 €

Du 11e au 20e                                                     Terme variable de 0,07 €

Du 21e au 30e                                                     Terme variable de 0,04 €

A partir du 31e                                                    Terme variable de 0,03 €

La tarification s'effectue par tranches successives.

Exemple : Pour un trajet d'une distance de 23 km, le tarif aller est de : 1,10 + (0,07 * 10) + (0,04 * 3) = 1,92€


Moi en faite je suis  coincé la dans la formule au niveau des tranche : =SI(Distance<=10;1,1;SI(Distance<=20;1,1+(0,07*(la);SI(Distance<=30;1,1+(0,07*10)+(0,04*(la));1,1+(0,07*10)+(0,04*10)+(0,03*Distance))))

merci de me rep au plus vite

michel_m · Accepted Answer

Pierre, excuses moi de t'avoir dérangé  en essayant de t'aider,mais c'est promis, ca ne se reproduira plus
--

dna.factory · Answer

décortique ta formule en 4 cases
dans la premiere case tu calcule le prix des 10 premiers kilometre
dans la deuxieme le prix des kilometres 11 à 20
ainsi de suite
et dans une cinquieme case : tu additione tout
quand tu auras réussi, reviens nous voir, si tu as toujours besoin d'aide pour regrouper les 4 formules en une

michel_m · Answer

Bonjour Pierre, dna,

une question similaire à la tienne a déjà été posée sur le forum

peut-être que son principe pourra t'aider:
https://www.cjoint.com/?dxmICTMyzV

attention: formule matricielle à valider par Ctrl+maj+entree et non par "entrée" comme d'hab
Excel met alors automatiquement les accolades qui encadrent la formule.

emerj · Answer

=SI(F10>31;F10*0,03;SI(F10>20;F10*0,04;SI(F10>10;F10*0,07;F10*1,1)))
tu procède à l'envers pour écrire la formule, tu commence par la condition la plus grande et ainsi ton calcul trouve la bonne plage de valorisation

Pierre211 · Answer

Non désoler cela ne m'aide pas du tout merci de m'aider car cela est assez complèxe.
En faite ce que je ne comprendre pas c'est au niveau de la tarification ex: pour tant de KM <=20;1,10+(0,07*par le terme variable qui est soit 1,2,3,4,5,6,7,8,9,10) 
J'espère que cela est claire pour vous.

Merci encore de votre aide.

Vaucluse · Answer

Bonjour
la meilleures solutions à mon avis, car votre formule n'est pas mauvaise:
Chaque nouvelle tranche vous donne une constante sur la tranche précédente:
soit entre autres:
De 10 à 20=1,1*10+0,07*10)=11,7
+ de 20 à 30=11,7+0,04*10=12,1
Il suffit de calculer ces constantes pour les intégrer dans vbotre formule qui vbous donnera quelues chose comme:
=SI(A1<=10;A1*1,1;SI(A1<=20;(A1-20)*0,07+11,7;SI(A1<=30;(A1-30)*0,04+12,1 .... ETC ...
Crdlmnt

emerj · Answer

à ce stade, tu dois expliquer plus clairement le fonctionnement de ton calcul
1) il y a un terme fixe perçu calculé pour chaque déplacement de 1.1€ par KM parcouru quelque soit la distance, plus un majoration variable par tranche de tarification ( on additionne la valeur de chaque tranche , alors) 

ex je fais 22 km ===== 22*1.1 + 10*0.07 +2*0.04

2) il y a un terme fixe perçu calculé pour chaque déplacement de 1.1€ par KM parcouru quelque soit la distance, plus un majoration variable calculé sur la base de la tranche du kilométrage parcouru

ex je fais 22 km ===== 22*1.1 + 12*0.04

...

merci de tes explications

emerj · Answer

tu sais si le problème est trop complexe pour écrire directement la formule, il faut le détailler par ligne simple dans exel, puis écrire des conditions limitées , quand tu dépasses le nombre de si en série ( 7  je cois)
il faut penser à  un calcul avec  une matrice.
Il y a alors plus de possibilité d'exploiter un résultat en raisonnant sur les lignes et colonnes de la matrice

leplot · Answer

Bonjour

j'apporte également ma contribution en suivant l'idée de "emerj" de formule matricielle.

=INDEX({1,1;1,1;1,8;2,2};EQUIV(A1;{0;11;21;31})) + INDEX({0;0,07;0,04;0,03};EQUIV(A1;{0;11;21;31})) * (A1+1-INDEX({0;11;21;31};EQUIV(A1;{0;11;21;31})))

Le problème peut être écrit sous la forme
Bornes        Fixe         Variable
0 - 10          1.1           0
11-20          1.1           0.07
21-30          1.8           0.04    '1.8 = 1.1 + 10*0.07
31 - +         2.2           0.03    '2.2 = 1.1 + 10*0.07 + 10*0.04

On effectue des recherches de la cellule A1 dans la première colonne (Equiv) ce qui donne le rang à prendre.
On recherche dans le rang X dans la colonne 2 (Index) = fixe
On recherche dans le rang X dans la colonne 3 (Index) = Variable * (A1 - début de borne)

Dans la formule, les données sont saisies directement en matrice {;;;} mais on peut écrire le tableau précédent et faire les recherche dessus. C'est plus simple pour faire des mises à jour.

LePlot

Vaucluse · Answer

mon poste n'intéresse personne à priori

Ah? il m'avait semblé pourtant!

Vaucluse · Answer

Excusez moi, j'avais cru que mon message 5 pouvait résoudre votre probkème de manière assez simple. Qu'en est il ? à votre avis?
... mais effectivement à la réflexion, ce n'est peut être pas tout à fait ça.Je reviens vaec ce que  j'ai compris  maintenant.
Crdlmt

xkristi · Answer

' soir à tous 

Simplement parce que Pierre avait bossé sur sa formule 

si la distance est en B2
sa formule corrigée serait celle-ci (du moins je crois)

=SI(B3<=10;1,1;SI(B3<20;1,1+(0,07*(B3-10));SI(B3<=30;1,1+(0,07*10)+(0,04*(B3-20));1,1+(0,07*10)+(0,04*10)+(0,03*B3))))

Vaucluse · Answer

Re
le fichier ci joint correspond il à votre attente?
crdlmnt

http://www.cijoint.fr/cjlink.php?file=cj200903/cijvaifv21.xls

Pierre211 · Answer

Merci c'est résolue merci beaucoup de votre aide à tous

Raymond PENTIER · Answer

Bonjour Pierre. Ton problème a suscité beaucoup de propositions !

Bien que la discussion soit résolue, j'ajoute la mienne.
Elle est très proche de celle que tu as présentée dès le départ =SI(dist=0;0;SI(dist<=10;1,1;
SI(dist<=20;1,1+(dist-10)*0,07;SI(dist<=30;1,8+(dist-20)*0,04;2,2+(dist-30)*0,03))))
J'ai testé cette formule avec diverses valeurs : elle marche chaque fois. Bien entendu dist est le nom donné à la cellule où on saisit la distance ...

Et saluts amicaux à tous les participants !

Excel fonction si alternative imbriquée

15 réponses

Discussions similaires