des calculs de couts de transports

[jeromear]

Bonsoir
Je cherche a automatiser un tableau compliqué de calculs de couts de transports en sachant que les valeurs en rouge sont suceptibles de changer selon les destinations.
Peut être avec SOMMEPROD mais je ne suis pas assez calé pour ces calculs.
Si quelqu'un sait faire cela...
Regarde la pièce jointe cout transports.xls

 

[Modeste]

Re : des calculs de couts de transports

Bonjour jeromear,

Pas facile de s'y retrouver dans tes explications d'autant plus que d'un exemple à l'autre, il me semblait trouver des discordances. J'ai fini par considérer qu'au-delà de 100 kg, il fallait ajouter 1% du poids, arrondi à la dizaine immédiatement supérieure.
Si l'hypothèse était la bonne (elle fournit en tout cas les mêmes résultats que les tiens), la pièce jointe devrait te convenir.
Ceci dit, à force d'avoir comparé tes exemples, tenté de comprendre tes propres calculs, puis ... en regardant ma proposition ... j'en arrive à me dire qu'il devait être possible d'écrire la même chose plus simplement.
Dis-nous déjà si le résultat et l'hypothèse formulée ci-dessus sont corrects. A partir de là, il se trouvera bien un esprit moins tortueux (ou vraisemblablement plus doué) pour ré-écrire ça.

 

[job75]

Re : des calculs de couts de transports

Bonjour jeromear, Modeste

Belle formule (ouf) en D22, à tirer vers le bas :

=105%*(INDEX(A$2:A$8;EQUIV(PLAFOND(C22;10);{0;41;71;101;301;801;1501}))+MAX(PLAFOND(C22;10)%;1)*INDEX(D$2:D$8;EQUIV(PLAFOND(C22;10);{0;41;71;101;301;801;1501})))

Fichier joint.

Edit : on peut simplifier un peu la partie gauche de la formule si effectivement les frais de dossier ne varient plus après 101 kg :

=105%*(INDEX([COLOR="Red"]A$2:A$5[/COLOR];EQUIV(PLAFOND(C22;10);{[COLOR="red"]0;41;71;101[/COLOR]}))+MAX(PLAFOND(C22;10)%;1)*INDEX(D$2:D$8;EQUIV(PLAFOND(C22;10);{0;41;71;101;301;801;1501})))

A+

 

[job75]

Re : des calculs de couts de transports

Bonjour le fil, le forum,

A force d'utiliser INDEX/EQUIV on oublie la fonction RECHERCHE :

=105%*(RECHERCHE(PLAFOND(C22;10);{0;41;71;101};A$2:A$5)+MAX(PLAFOND(C22;10)%;1)*RECHERCHE(PLAFOND(C22;10);{0;41;71;101;301;801;1501};D$2:D$8))

Nota : si l'on veut paramétrer aussi 105%, le remplacer par (B$2+1)

A+

 

[jeromear]

Re : des calculs de couts de transports

Bonjour job75 et Modeste et merci à vous 2
J'ai les idées un peu plus claires après ce we.
Aussi j'ai commencé par reprendre les formules de Modeste et les ai adaptées dans un tableau plus explicite que le premier et cela fonctionne.
Je cherche maintenant à pouvoir entrer un chiffre en D36 qui me donne directement une réponse en F36 en cherchant dans le tableau la tranche de poids adéquate. Avec des SI cela fait des formules à 7 possibilités, très longues et mon cerveau ne suit pas toutes les parenthèses (nombreux essais infructueux) !
Peut être avec SOMME.PROD en créant un tableau de 0 à 3000 en dessous?
PS : Je suis aussi en train de regarder (et comprendre!) les formules fournies par Job75 (merci Job75)
Merci pour vos aides très précieuses.
Jérôme
Regarde la pièce jointe cout transports.xls
en B13 et B14 il faut lire COUT TRANSPORT B4 et C4 bien sûr
Ce tableau pourra être très utile pour les nombreuses TPE qui travaillent avec le transporteur froid TFE

"Le plus dur pour les pro d'excel, c'est de comprendre les explications pas toujours claires du néophyte" (néophyte=moi)

 

[jeromear]

Re : des calculs de couts de transports

Eh bien voilà ça à l'air de fonctionner avec SOMMEPROD (j'ai appris cette formule avec JCGL la semaine dernière) mais ça prend un peu de place (367ko)
Regarde la pièce jointe couttransporstZIP.zip

 

[Modeste]

Re : des calculs de couts de transports

Bonsoir,

Effectivement, avec les 3000 lignes que tu as ajoutées, le fichier devient sensiblement plus "lourd". En ce qui me concerne, j'aurais tendance à chercher une autre solution ... d'autant que les 3000 formules ne vont pas se mettre à jour correctement, lorsque tu changeras la valeur en B11.
Ceci étant, j'ai tenté de calculer la même chose que toi, sans le SOMMEPROD ... et je n'arrive pas au même résultat: pour 130 kg, tes 3000 formules arrondissent à la dizaine supérieure (140), mais pour 130 ... ne faut-il pas laisser le calcul se faire avec un coefficient de 1.3, plutôt que 1.4 ?? (ce qui donnerait un coût de 60,2532 au lieu de 64,7031)
Comme je ne suis pas certain d'avoir bien compris ('y avait longtemps ) et que je ne connais absolument rien au secteur du transport, je ne fais que (me) poser la question.

 

[jeromear]

Re : des calculs de couts de transports

Bonjour Modeste,
J'ai enlevé le +0,1 dans les formules ARRONDI.SUP(B$11+0,1;-1) et cela marche. ouf. Je ne sais plus pourquoi il était là d'ailleurs. Je refais quelques tests pour valider.
Merci beaucoup pour ton aide.
Regarde la pièce jointe cout transports PROVINCE.zip

PS : Je n'arrive pas à saisir la nuance entre ARRONDI.SUP et PLAFOND pour les formules proposées par de Monsieur Job75. Mais je crois que mes premières explications étaient trop confuses et que les formules ne correspondent pas tout à fait aux critères, qu'il m'excuse.

 

[Modeste]

Re : des calculs de couts de transports

Bonsoir jeromear, le forum,

Bonjour Modeste,
PS : Je n'arrive pas à saisir la nuance entre ARRONDI.SUP et PLAFOND pour les formules proposées par de Monsieur Job75.

Dans le cas présent, PLAFOND(xx;10), utilisé par job75 ... arrondit à la dizaine supérieure ... C'est d'ailleurs en me penchant sur ce petit morceau de sa formule que j'ai découvert le "décalage" évoqué dans mon précédent message.

Mais je crois que mes premières explications étaient trop confuses et que les formules ne correspondent pas tout à fait aux critères.

... Pourtant ses résultats étaient identiques dans la première version du fichier

qu'il m'excuse.

... Je ne sais pas s'il y arrivera un jour

 

[jeromear]

Re : des calculs de couts de transports

... Je ne sais pas s'il y arrivera un jour [/QUOTE]

Eh bien j'espère que si parce qu'il m'a énormément aidé sur ce forum et que ses réponses sont toujours impeccables!