formules sommprod avec plusieurs critères

[KIM]

Bonjour le forum, bonjour les ami(e)s?
Trouver le prix selon un poids minimal et un poids maximal et calculer le prix de la commande selon le poids est résolu avec la fonction SOMMEPROD. J'ai besoin de votre aide pour rajouter d'autres critère à ce calcul:
1- En général, le prix de base indiqué dans la feuille 'Tarifs_Trf" est un prix à la tonne sauf pour le poids de 1550 à 2550 Kg, le prix est un forfait. Quelques exeptions ou le prix pour un poids entre 1550 et 2550 est à la tonne.
Comment intégrer ces 2 conditions dans une seule formule. J'ai rajouté une col supplémentaire dans la feuille 'Tarifs_Tr' pour indiquer que c'est un forfait. Est-ce que cela peut aider. Je suis ouvert à toute proposition de modification de la feuille 'Tarifs_Tr'.

2- Une autre condition que je n'arrive pas à traiter est:
Seulement pour une même DR, et si pour le même jour, il y a 2 commandes une pour DA=421 et une autre pour DA=422 et si la somme du poids de ces 2 commande est <= à la somme max indiquée dans la feuille 'Tarifs_Trf' correspondant au max de la ligne DA=421 et DR concernée alors le prix de la tonne à prendre en compte ou le forfait est celui de la somme des poids des 2 commandes concernant DA=421 et DA = 422.
Quelle formule peut me résoudre cette condition?
Fichier joint pour plus d'explications
Je vous remercie d'avance de votre aide
Amicalement
KIM

 

[Monique]

Re : formules sommprod avec plusieurs critères

Bonjour,

à essayer en G16 puis copier-coller vers le bas :

=SOMMEPROD((LDA=D16)*(LDR=E16)*(Pmin<=F16)*(Pmax>=F16)*LPr*((LFr<>"F")*F16/1000+(LFr="F")))

LFr, c'est le nom de la plage contenant les "F"
Le critère F ou pas F dans la plage LFr est dans cette partie :
*LPr*((LFr<>"F")*F16/1000+(LFr="F"))

ça équivaut à :
*LPr*SI(LFr<>"F";F16/1000;1)

On peut aussi écrire la formule comme ça :
=SOMMEPROD((LDA=D16)*(LDR=E16)*(Pmin<=F16)*(Pmax>=F16)*LPr*SI(LFr<>"F";F16/1000;1))

Question 2 : je n'ai pas compris. Il faudrait aussi regrouper d'autres lignes, non ?

 

[Compte Supprimé 979]

Re : formules sommprod avec plusieurs critères

Bonjour,

Je vois avec grand plaisir ma chère Monique,
que ce week-end ne t'a pas fatigué les neurones

Wouhaouuu, ca c'est de la formule ...
Faudra vraiment que tu nous dises un jour, comment tu fais ?

Bises à toi

A+

 

[Monique]

Re : formules sommprod avec plusieurs critères

Bonjour,

Si, je suis KO, je stoppe là.

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonjour Monique, Bonjour le forum,
Mille mercis, la formule pour le cas 1 marche tres bien. Je ne connaissais pas ces possibilités élargies de sommeprod.

Pour le cas 2, effectivement le coût se fera après avoir additionné les poids des 2 comandes EX: pour une même DR (ex 478), pour une même DATE (ex 01-sept), DA=421 & DA=422 faire la somme des poids (999+400= 1399) appliquer ainsi le cout de la feuille Tarifs_Trf dont DA=421, DR=478 et la tranche dont le poids de 1399 est entre le min et le max. Je ne sais pas si c'est faisable ou peut etre faut-il réfléchir autrement, rajouter une col ou?
Merci d'avance
Amicalement
KIM

 

[Monique]

Re : formules sommprod avec plusieurs critères

Bonjour,

Je ne comprends pas pourquoi tu groupes seulement les lignes 6 et 7
Les DA et DR des lignes 8 et 9 sont les mêmes qu'à la ligne 6 (421 et 478)
Pourquoi 421 et 422 vont ensemble, je ne comprends pas non plus.
Du coup, j'ai groupé 4 lignes du 1er septembre

Pour les commandes du 2 septembre, si on groupe, c'est gratuit parce que le poids maximum est dépassé… tout simplement.

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonsoir Monique, & le forum
Je viens de découvrir ta réponse, Je n'ai pas été averti instantanément par email.
Je regarde tes formules demain et te tiendrai au courant
Merci d'avance
KIM

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonjour Monique & le forum,
Effectivement, il faut rgrouper jusqu'à la limite du poids maximum. Mais si cela dépasse le poids max, il faut indiquer laquelle ne doit pas être prise en compte ou l'inverse, peut-etre faut-il rajouter un indicateur de groupage dans une col supplementaire. Je vais essayer de discuter avec le gestionnaire.
Merci encore et bonne journée
Amicalement
KIM

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonjour Monique et le forum,
En regardant de nouveau le fichier, j'ai oublié de rajouter le nom du TR correspondant aux DA et DR dans la col M. J'ai utlisé SOMMEPROD mais il me sort une erreur car dans la feuille Tarifs_Trf il existe plusieurs lignes avec les mêmes références DA, DR et TR. Comment modifier cette formule ou une autre pour ne pas prendre en compte les doublons de l'ensemble DA-DR-TR.
Je n'ai pas ouvert une autre discussion, mais s'il le faut merci de me le dire et je présente toutes mes excuses, mais j'ai estimé que ce problème fait partie de la même discussion.
Merci d'avance de ton aide et de celle du forum
Amicalement
KIM

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonjour Monique, Bonjour le Forum,
J'ai essayé plusieurs solutions et je n'arrive pas à m'en sortir. J'ai besoin de votre aide et vous en remercie d'avance. Dans les 3 colonnes intitulées DA, DR et TR de la feuille Tarifs_Trf j'ai des doublons et je n'arrive pas à rechercher dans la feuille Base_W le nom du TR correspondant à un DA et DR donné (voir fichier joint) J'ai testé plusieurs solutions avec sommeprod et index/equiv. J'ai même créé manuellement dans la feuille Tarifs_Trf un référentiel unique de l'ensemble DA; DR et TR sans resultat.
Merci de votre aide
Bien amicalement
KIM

 

[Monique]

Re : formules sommprod avec plusieurs critères

Bonjour,

J’ai un temps de retard et je n’ai pas tout regardé.
Une partie de réponse pour l’instant.

Index Equiv dans sa forme classique ne renvoie qu'une seule valeur
SommeProd ne renvoie pas du texte, mais peut renvoyer un n° de ligne
Et Index(Plage ; n° de ligne) donne le résultat
Mais s'il y a des doublons, SommeProd renvoie la somme des n° de lignes

A partir des données du mini tableau :
En N5 de la feuille "Base" :
=INDEX(LTR2;SOMMEPROD((LDA2=D5)*(LDR2=E5);LIGNE(LTR2)-4))

Ou bien (en O5 par exemple)
=INDEX(LTR2;EQUIV(D5&E5;LDA2&LDR2;0))
La 2ème est à valider par ctrl, maj et entrée

 

[KIM]

Re : formules sommprod avec plusieurs critères

Bonjour Monique & le Forum,
Monique Merci, tes 2 formules fonctionnent à condition que je créé manuellement un référentiel unique sans doublons à partir de la liste complète dans Tarifs_Trf.
Je vais le faire ou solliciter le forum pour automatiser via vba ce referentiel unique DA;DR;TR pour appliquer ta formule sommeprod.
Si entretemps tu as une autre solution je t'en remercie d'avance.
En attendant je vais utiliser ta 1ere formule sommeprod et t'en remercie vivement.
Bien amicalement
KIM

 

[Monique]

Re : formules sommprod avec plusieurs critères

Re,

Par formules, le tableau de références
(Formules matricielles, à valider par ctrl, maj et entrée)

 

[KIM]

Re : formules sommprod avec plusieurs critères

Merci Monique,
Merci aussi à Pierrejean et Bebere, Je sais que je peux compter sur vous, et l'ensemble des acteurs de ce forum, Grace à vous j'ai pu finaliser ce dossier.
Merci encore et bonne continuation.
NB: J'ai un souci, Je ne reçois plus des notifications instatanées par email. Il faut que je vois ce probleme avec le webmaster du forum
Bon WE
Bien amicalement
KIM