Commande par tranche...formule

[sadar]

Bonjour,

J'ai une feuille exel qui nous sert à faire une commande basée sur un référentiel découpé en 3 tranches appelées mini, moyen, max.

VMini=20, VMoyen=30, VMax=40 Ces valeurs sont associées à un prix
PMini=1500, PMoyen=2600, PMax=3150

Lors de la commande, soit ValCde la valeur saisie par l'utilisateur, le calcul doit se faire en respectant les tranches par exemple :

Si ValCde est compris entre 01 et 20 prendre PMini
Si ValCde est compris entre 21 et 30 prendre PMoyen
Si ValCde est compris entre 31 et 40 prendre PMax
et ainsi de suite...
Si ValCde est > 40 et est compris entre 41 et 60 prendre PMini + PMax
Si ValCde est > 40 et est compris entre 61 et 70 prendre PMoyen + PMax
Si ValCde est > 40 et est compris entre 71 et 90 prendre PMini + PMoyen + PMax
Si ValCde est > 40 et est compris entre 91 et 110 prendre PMini + PMini + PMoyen + PMax

etc....

Donc je bloque sur la formule qui saurait modéliser ce mode de fonctionnement car un enchainement de SI ne convient pas dès lors que l'on passe la barre des 90.

Merci de votre aide.

 

[tototiti2008]

Re : Commande par tranche...formule

Bonjour sadar,

Bienvenue sur XLD,

Visiblement, tes tranches se créent en fonction de la décomposition de leur borne supérieure en VMini, VMoyen et VMax
une question : dans la trache 91-110, tu dis "prendre PMini + PMini + PMoyen + PMax"
Comme VMax vaut 2 VMini, pourquoi ça ne donne pas "prendre PMoyen + PMax + PMax" ?
Si on peut décomposer la borne supérieure de plusieurs manières, laquelle privilégier ?

 

[hoerwind]

Re : Commande par tranche...formule

Bonjour et bienvenue sur le forum, salut titotiti,

D'après ce que j'ai compris, voir la pièce jointe.

 

[tototiti2008]

Re : Commande par tranche...formule

Bonjour hoerwind,
Re,

Oui, sans doute que je me pose trop de questions
C'est le "etc..." à la fin des plages décrites qui m'a fait gamberger

 

[hoerwind]

Re : Commande par tranche...formule

Re,

Je me suis quelque peu mélangé les pinceaux dans les formules, cette version me semble plus correcte.

 

[sadar]

Re : Commande par tranche...formule

Je reviens juste de déj'... je regarde le fichier en PJ et reviens vers vous.

Dans le cas où l'on aurait le choix entre plusieurs solutions il faut (toujours) prendre celle qui reviens... la moins chère !

En général cela suit assez bien la règle de base qui est de commander les tranches les unes après les autres. Par exemple pour une valeur de ValCde à 60 on ne prend pas 3 VMini, ni 2 VMoyen mais 1 VMini + VMax

 

[sadar]

Re : Commande par tranche...formule

Je viens de voire la réponse, l'inconvénient de cette solution c'est que on est obligé de rentrer un tableau statique. Dans la réponse fournie cas si l'on commande 300 ou 200 on aura le même prix. Bref, la solution que je cherche serait, idéalement un calcul dynamique donnant un résultat identique à RECHERCHEV dans notre cas.

Sinon, merci quand même car je n'aurai pas pensé à cette fonction !

 

[hoerwind]

Re : Commande par tranche...formule

Re,

Il est difficile de trouver une formule "passe-partout" parce que :
- les bornes des tranches ne sont pas constantes, de 01 à 20 c'est un pas de 20, de 21 à 40 un pas de 10, de 41 à 60 un pas de 20, de 61 à 70 à nouveau un pas de 10, puis on repasse à un pas de 20, où est la logique ?
- le etc est peut-être évident pour toi, mais pas pour quelqu'un qui ne connait pas le projet.

Un mot d'explication s'avère donc indispensable !

 

[sadar]

Re : Commande par tranche...formule

100% d'accord, je suis, grâce à ce qui a été fourni, sur la piste d'une formule à base de

=SI(B9<=C$2;SI(B9>INDEX(A$2:C$2;EQUIV(B9;A$2:C$2;1));INDEX(A$5:C$5;EQUIV(B9;A$2:C$2;1)+1);INDEX(A$5:C$5;EQUIV(B9;A$2:C$2;1)));"")

Voir les formules présentes en B12, C12 et D12

Je travaille maintenant sur la possibilité de la rendre moins statique maintenant que j'ai grosso-modo le principe. Je posterai le résultat si cela vous intéresse

Sinon, pour le coté projet : c'est un fichier qui sert à commander des UO (Unités d'Oeuvres) à un prestataire. Les valeurs par tranches représentent des forfaits en J/H.

Donc au final lors de la commande, on commande un forfait de xx jours qui doivent se situer dans une tranche en appliquant un algorithme qui devra au final nous dire que 90 jours commandés correspondent à
VMini + VMoyen + VMax + VMini et donc nous donner un montant de
PMini + PMoyen + PMax + PMini...

J'y suis presque encore un effort

 

[hoerwind]

Re : Commande par tranche...formule

Re,

Comme quoi poser une question éclairci souvent la problème.
D'après la pièce jointe les tranches ne sont plus les mêmes que celles au départ, maintenant on y trouve une logique.
Il ne reste qu'à trouver une logique dans les sommes à additionner, vois le début en M2:O2
Si tu remplis ce tableau, peut-être qu'on trouvera une solution élégante.

 

[sadar]

Re : Commande par tranche...formule

La logique de base est la suivante

les valeurs de base de tranche (TR) 1 sont = à VMin, VMoy, VMax et les prix correspondants PMin, PMoy, PMax
TR 2 = VMin+(VMax*1), VMoy+(VMax*1), VMax+(VMax*1) prix PMin+(PMax*1), PMoy+(PMax*1), PMax+(PMax*1)
TR 3 = VMin+(VMax*2), VMoy+(VMax*2), VMax+(VMax*2) prix PMin+(PMax*2), PMoy+(PMax*2), PMax+(PMax*2)
...
et ainsi de suite voir en PJ

je pense donc que si la valeur saisie est > à VMax de la tranche 1
Il faut alors diviser cette valeur VMax pour la ramener dans les valeurs de la tranche 1 puis une fois la borne trouvée (par borne j'entends VMin, VMoy, VMax) reste plus qu'à appliquer VMin+(VMax*n), VMoy+(VMax*n), VMax+(VMax*n)

Bref yapluka le premier arrivé fais signe...bon ben vu l'hh on verra demain hein !

 

[sadar]

Re : Commande par tranche...formule

La soluce finale en PJ (voir cellule I3)

=INDEX(B$10$10;EQUIV(SI(ValCde=CMax*(ENT(ValCde/CMax));CMax;SI(ValCde-(CMax*ENT(ValCde/CMax))<=SMax;SMax;ValCde-(CMax*ENT(ValCde/CMax))));B$5$5;1))+(PMax*SI(ValCde=CMax*(ENT(ValCde/CMax));ENT(ValCde/CMax)-1;ENT(ValCde/CMax)))

c'était évident non ?