Discussion :

[Jeremy.ch]

Bonjour, après moultes heures passé à tenter de résoudre mon problème, en passant par des NB.SI, SOMME.SI.ENS et autres je n'arrive pas à avancer.

En effet mon tableaux présente les prix des produits A à Z selon 4 entreprises. J'ai réussit à mettre en surbrillance les prix Min et Max proposés par les enseignes pour chaque produit ceci afin de déterminer un écart par rapport au prix moyen.

Néanmoins je cherche maintenant, pour chaque enseigne, à additionner les prix pour chaque produit respectant un critère:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI(B2:E2;"B2<=C2:E2";$B$2:$B$25)

En fait je souhaite que la somme s'effectue pour l'enseigne It par exemple si le prix A(It)<= prixA(les 3 autres enseignes)
Ce qui me donnera le total le plus avantageux pour chaque enseigne, sachant que j'ai quelques prix équivalents à toutes les enseignes pour certains produits.

Merci d'avance de me donner votre avis, une piste, une autre formule sinon je vais passer une journée EXCEL-lente demain

Voici le fichier joint:
comparaison prix1.xls

[Pierre Fauconnier]

Salut.

Tu ne peux pas t'en sortir avec SOMME.SI.ENS car les critères ne peuvent porter que sur les valeurs ou parties de valeurs contenues dans les cellules. Tu ne peux donc pas poser le critère sur le résultat d'une formule opérant sur les cellules.

Tu dois donc te tourner vers les matricielles ou vers SOMMEPROD.

Voici une formule que tu peux placer en B31 et recopier jusqu'en E31

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(B2:B25*(B2:B25<=$B2:$B25)*(B2:B25<=$C2:$C25)*(B2:B25<=$D2:$D25)*(B2:B25<=$E2:$E25))

Tu remarqueras au passage que j'ai repris la colonne du calcul dans le test ((B2:B25<=$B2:$B25), ce qui est inutile au niveau du calcul car toujours vrai, pour pouvoir recopier la formule dans les autres colonnes.

[Jeremy.ch]

Merci beaucoup Pierre j'ai bien compris la formule ça m'aide beaucoup, du coup je l'ai un peu modifier pour avoir parfaitement ce que je désirais ce qui donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SOMMEPROD(B2:B25*(B2:B25<$C2:$C25)*(B2:B25<$D2:$D25)*(B2:B25<$E2:$E25))

que je colle de B31 à E31 à la suite de ma ligne "le moins cher pour chaque entreprise" en A31

Maintenant mon problème est que cela ne me compte pas les prix qui sont équivalent à au moins deux enseignes.
Ainsi j'ai ajouté une ligne A32 "le moins cher en commun" qui va s'étendre de B32 à E32 suivant la consigne suivante:

J'effectue la somme de B2:B25 si B2:B25=C2:C25 OU B2:B25=D225 OU B2:B25=E2:25 à la condition que B31>C31 ET B31>D31 ET B31>E31

Donc j'ai tenté ceci mais ça me met une erreur #NOM? :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=somme.prod(B2:B25*(SOMMEPROD(B2:B25*(B2:B25=$C2:$C25)*(B2:B25=$D2:$D25)*(B2:B25=$E2:$E25)))*SI(B31>"*&*(C31;D31;E31)";B31;"-"))

Sinon j'ai pensé imbriquer une fonction SOMME.SI.ENS mais je m'y perd :s

Merci pour votre aide

[Pierre Fauconnier]

Pour le problème de nom, note que sommeprod est en un mot (sans point).

Je ne comprends pas ta remarque: ne compte pas les prix équivalents entre deux enseignes. Le test <= prend en compte le prix identique dans plusieurs sociétés (comme s'il était le prix minimum). Un prix identique à deux sociétés est donc compté dans chacune des deux sociétés pour obtenir le total le moins cher...

Autre chose: avec Sommeprod, tu n'as a priori pas besoin de mettre un SI, puisque les tests entre parenthèses simulent un SI qui renverrait VRAI ou FAUX.

Cela dit, je n'ai pas compris ce que tu souhaitais obtenir en ligne 32

[Jeremy.ch]

D'accord j'ai bien modifié en prenant en compte tes remarques donc ma formule devient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SOMMEPROD(B2:B25*(B2:B25=$C2:$C25)*(B2:B25=$D2:$D25)*(B2:B25=$E2:$E25)*(B31>(C31;D31;E31)))

mais là ça me met un problème VALEUR#.

Je te met ci-joint le dossier pour aider à comprendre comparaison prix2.xls

Les valeurs B4; C4; D4 sont équivalente et les plages b18:b24 ; C18:C24; D1824; E18:E24
Ainsi ils ne sont pas comptabilisé dans ma formule "le moins cher pour chaque entreprise" car ils sont égaux et pas "<".

Néanmoins je veux les compter une fois dans mon total final, je pourrais ajouter à G31 une

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(B18:B24;B4)

mais moi je veux ajouter automatiquement à l'enseigne chez qui j'effectue l'achat le plus gros soit en C4.

Autre solution je passe par NB.SI?

[Pierre Fauconnier]

Je ne comprends pas ce que tu veux dire par

[...]
Ainsi ils ne sont pas comptabilisé dans ma formule "le moins cher pour chaque entreprise" car ils sont égaux et pas "<".[...]

puisque le test est <= (dans ma formule)

[Jeremy.ch]

Justement je suis passé de ta formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(B2:B25*(B2:B25<=$B2:$B25)*(B2:B25<=$C2:$C25)*(B2:B25<=$D2:$D25)*(B2:B25<=$E2:$E25))

à

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SOMMEPROD(B2:B25*(B2:B25<$C2:$C25)*(B2:B25<$D2:$D25)*(B2:B25<$E2:$E25))

Donc j'ai changé <= par < car je veux appliquer un critère spécial lorsque le prix est le même (=) pour 2,3, ou les 4 enseignes.


Entreprise : It Cas Chr Cr
Somme des prix les moins cher: 3,59 14,59 6,3 12,63

L'entreprise Cas est celle chez qui je vais acheter le plus car elle propose une majorité de prix les moins chers, ainsi je veux que ce soit chez elle que j'achète aussi les produit dont le prix est équivalent à d'autres entreprises pour effectuer un achat groupé afin par la suite d'obtenir un rabais.

Je sais pas si j'ai réussit à clarifier le problème, ta technique me donne un total de 104 et en faisant comme je souhaite j'arrive à 54.
Je joint le fichier où j'ai ajouter ta technique ça me semble plus clair à comprendre comparaison prix2.xls

Merci encore pour ta patience

[Pierre Fauconnier]

L'entreprise Cas est celle chez qui je vais acheter le plus car elle propose une majorité de prix les moins chers
[...]

Je ne suis pas d'accord avec ça. La formule donne le total des P.U. les moins chers, pas le nombre d'articles les moins chers.

Ainsi, sur la sommes des P.U, CAS est gagnante, mais si tu commandes majoritairement des articles L, tu es perdant... ^^

Il pourrait être alors intéressant de calculer le nombre d'articles les moins chers, en supprimant simplement cette portion de la formule.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((B2:B25<=$B2:$B25)*(B2:B25<=$C2:$C25)*(B2:B25<=$D2:$D25)*(B2:B25<=$E2:$E25))

te donnera le nombre d'articles les moins chers... il se fait que sur base de tes données, c'est également CAS qui l'emporte avec 6 articles (contre 1 pour IT), mais tu pourrais avoir une autre réalité. Si tu ajoutes l'égalité à ton test, tu passes à 15 articles moins chers ou à prix équivalent pour CAS contre 10 pour IT...

il serait donc utile de pouvoir pondérer cela par le nombre de chaque article que tu budgétises, de manière à approcher une réalité plus consistante, car se baser uniquement sur le P.U. sans tenir compte des quantités me semble plus qu'hasardeux.

Ainsi, si, sur base de ton constat de CAS le moins cher, tu achètes tu achètes 1 article I et 10 articles L, tu paieras 49.14 (le plus cher des quatre) contre 39.19 chez IT (le moins cher) alors que ton analyse en ligne 31 donnait CAS comme "le meilleur" et IT comme "le pire"...

Je n'ai pas compris le sens de ta formule en ligne 32, mais elle n'est pas bien rédigée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(B31>(C31;D31;E31)

n'est pas une syntaxe correcte.

si tu veux exprimer le B31>C31 ET B31 > D31 Et B31>E31, tu dois simplement multiplier par les termes correspondants (principe même de la formule matricielle)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

...*(B31>C31)*(B31>D31)*(B31*E31)

Si tu veux dire B31>C31 OU B31>D31..., tu dois alors utiliser, toujours selon le principe de la formule matricielle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

...*(((B31>C31)+(B31>D31)+(B31>E31))>0)

Ainsi, >0 renvoie VRAI dès que B31 est plus grand qu'une des autres valeurs. Si tu veux tester que B31 est plus grand que au moins deux valeurs, tu utilises >1, etc...

Tu vois que les matricielles te permettent tous les cas de figure...

Bon travail!

[Jeremy.ch]

Ahhhhh oui tes formules matricielles sont très intéréssantes du coup j'ai mis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(B3:B26*(B3:B26=$C3:$C26)*(B3:B26=$D3:$D26)*(B3:B26=$E3:$E26)*(((B32>C32)+(B32>D32)+(B32>E32))>2))

Je suis arrivé à ce que je voulais maintenant il ne me reste plus qu'à bidouiller lors qu'il y a seulement 3 prix en commun et ça sera parfait.
Je te remercie Pierre, ton aide m'a été précieuse !