Hypothèse prix en fonction de la quantité à produire

Bonjour à tous,

Je sollicite votre aide pour 2 sujets :

• Qtt annuelle :

J'ai une hypothèse de X produits à fabriquer en B1, répartit sur Y années en B2 avec un démarrage du projet Z en B3.

En fonction de ces 3 critères, je cherche à avoir la quantité annuelle en prenant en compte ces 3 critères. on fabrique un produit, il ne peut être qu'un nombre entier. Or je n'arrive pas à obtenir la formule correcte qui respecte aussi la contrainte : la somme de ces quantités annuelles doivent être égale à B1 (la quantité de produit à fabriquer).

Sur cette image en haut nous avons les hypothèse :
B1 : le volume a produire du produit
B2 : le nombre d'année de production
B3 : la date de démarrage de la production attention elle n'est pas toujours fixe et varie entre 2025 et 2036

J'ai essayé en E14 :

=SI(ET(E$8>=$B$3;E$8<$B$3+$B$2);$B$1/$B$2;0)

=SI(ET(E$8>=$B$3;E$8<$B$3+$B$2);ENT($B$1/$B$2)+SI(COLONNE(E14)-COLONNE(E$14)<MOD($B$1;$B$2);1;0);0)

Ces 2 solutions ne fonctionnent pas pour le dernier mois je n'arrive pas à comprendre pourquoi la valeur n'est pas correcte.

• le prix suivant une quantité indiquée :

J'ai une colonne Quantité cumulée allant de 10 à 500 (C41 pour 10 à C90 pour 500)
j'ai une colonne Lieu 1 qui doit me donner le prix suivant la quantité

suivant la quantité en C il doit prendre le prix du produit dans la ligne D9 pour 2025 a S9 pour 2040 et surtout en prenant en compte la quantité annuelle de l'année.

pour un exemple ou :
B1 : le volume a produire du produit : 127pcs
B2 : le nombre d'année de production : 3 ans
B3 : la date de démarrage de la production : 2026

On a donc de 10 à 40 : un calcul simple Qtt x Prix année 1 de production
Pour 50 : Qtt annuel 1 x Prix année 1 de production + (50-Qtt annuel 1) x prix année 2 de production
pour 90 : Qtt annuel 1 x prix année 1 + Qtt annuel 2 x prix annuel 2 + (90 - (Qtt annule 1+ qtt annuel 2) ) * prix année 3 de production
etc ...

J'ai essayé avec :

=SI((B43>$O$16);(B43*$P$9);
SI((B43>$N$16);(B43*$O$9);
SI((B43>$M$16);(B43*$N$9);
SI((B43>$L$16);(B43*$M$9);
SI((B43>$K$16);(B43*$L$9);
SI((B43>$J$16);(B43*$K$9);
SI((B43>$I$16);(B43*$J$9);
SI((B43>$H$16);(B43*$I$9);
SI((B43>$G$16);(B43*$H$9);
SI((B43>$F$16);(B43*$G$9);
(B43*$F$9)))))))))))
Cela ne fonctionne que pour la première année mais pas au delà.

Je suis passé sur un test :
=SI(B43<$D$16; $D$9;
SI(B43<$E$16; $D$14*$D$9 + (B43 - $D$14)*$E$9;
SI(B43<$F$16; $E$14*$E$9 + (B43 - (E$14 + $D$14))*$F$9;
SI(B43<$G$16; $F$14*$F$9 + (B43 - (F$14 + E$14 + $D$14))*$G$9;
SI(B43<$H$16; $G$14*$G$9 + (B43 - (G$14 + F$14 + E$14 + $D$14))*$H$9;
SI(B43<$I$16; $H$14*$H$9 + (B43 - (H$14 + G$14 + F$14 + E$14 + $D$14))*$I$9;
SI(B43<$J$16; $I$14*$I$9 + (B43 - (I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$J$9;
SI(B43<$K$16; $J$14*$J$9 + (B43 - (J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$K$9;
SI(B43<$L$16; $K$14*$K$9 + (B43 - (K$14 + J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$L$9;
SI(B43<$M$16; $L$14*$L$9 + (B43 - (L$14 + K$14 + J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$M$9;
SI(B43<$N$16; $M$14*$M$9 + (B43 - (M$14 + L$14 + K$14 + J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$N$9;
SI(B43<$O$16; $N$14*$N$9 + (B43 - (N$14 + M$14 + L$14 + K$14 + J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$O$9;
SI(B43<$P$16; $O$14*$O$9 + (B43 - (O$14 + N$14 + M$14 + L$14 + K$14 + J$14 + I$14 + H$14 + G$14 + F$14 + E$14 + $D$14))*$P$9; 0)))))))))))))
Mais ce n'est pas ca non plus.

J'essaye de voir avec un Index () Equiv() :
=SIERREUR(INDEX(D9:P9;EQUIV(INDEX(D14:P14;EQUIV(B43;D14:P14;1));D14:P14;1));"")

On approche mais ce n'est pas bon non plus. il prends le volume en B43 et le multiplie par le prix de ce volume. or il devrait prendre la quantité annuelle en compte.

Je vous remercie par avance pour votre aide.

Cordialement,

DukeAtreides

bonjour,

une proposition via des fonctions personnalisées, nécessitant l'activation des macros. (voir formules dans le fichier)

les résultats ne sont pas identiques aux résultats attendus, car je pense qu'il y a une erreur dans tes formules à partir de la ligne 51, j'ai corrigé H51 et avec la correction on obtient le même résultat.

Bonjour H2SO4

Merci pour ton retour, je fais un essai de la proposition.

Par curiosité intellectuel, est ce que le résultat n'est récupérable que par macro ? pas moyen de le faire par formule ?

Je te remercie en tout cas pour le temps que tu y as consacré.

bonjour,

Par curiosité intellectuel, est ce que le résultat n'est récupérable que par macro ? pas moyen de le faire par formule ?

La première fonction personnalisée (la répartition de la quantité à produire selon le nombre d'années de production) peut être aisément être faite par une formule.

La seconde, il doit sans doute y avoir moyen, mais moyennant une formule assez longue et sans doute des colonnes de résultats intermédiaires. Pour moi, c'était plus simple de faire une fonction personnalisée. Mais si tu as la chance qu'un champion des formules passe par là, tu pourrais bien avoir ta solution sans VBA.

Bonsoir H2SO4

Juste pour vous remercier de votre aide. j'ai fait le test et cela m'a beaucoup aider pour la suite du projet.

merci encore pour le temps et votre partage de connaissance ;)

bonsoir,

merci pour ce retour.