[XL-2003] Calcul de prix à partir d'un bordereau

[Yvanou]

Bonjour à tous!!

Je souhaiterais calculer le prix total de fabrication d'un produit "Produit X" à partir du prix unitaire et de la quantité de ses composants constitutifs.

Dans mon cas, les prix des composants sont contenus dans un tableau de prix unitaires, contenus dans une feuille "Base".

Dans une seconde feuille "Calcul", s'effectue le calcul des prix de chacun des prix des produits en utilisant les quantités de composant et en allant chercher par référence (à l'aide d'un RECHERCHEV() ) les prix des composants dans le bordereau (chaque composant possède un code identifiant). Dans cette feuille, mon tableau comporte autant de ligne qu'il y a de produit et autant de colonne qu'il y a de composants possibles dans le produit. Dans ces dernières sont indiquées les quantités respectives des composants pour chacun des produits.

Malheureusement, dans mon cas, la formule du calcul du prix total du produit est très longue car je dois rédiger manuellement la multiplication entre la quantité de composant et la formule de recherche de prix unitaire du composant pour chaque composant.

J'ai bien entendu joint un fichier exemple pour illustrer mon problème et ma question est la suivante:

Quelqu'un saurait-il me donner une (ou des ) idée(s) pour alléger la formule de la colonne "Prix total" (en jaune) de la feuille "Calcul" ?

Je vous remercie par avance et vous souhaite un excellent WE!!

[bbil]

On pourrai voir cette formule qui cause problème !
--------------------------------------------------------------------
Fichiers joints dans les discussions
--------------------------------------------------------------------------

[Yvanou]

Bonjour,

La formule qui pose problème est celle contenue dans la colonne jaune de la feuille "Calcul".

Je souhaiterais alléger cette fonction.

[Yvanou]

La formule est la suivante:

=B4*RECHERCHEV($B$2;Base!$A$4:$C$13;3;0)+C4*RECHERCHEV($C$2;Base!$A$4:$C$13;3;0)+D4*RECHERCHEV($D$2;Base!$A$4:$C$13;3;0)+E4*RECHERCHEV($E$2;Base!$A$4:$C$13;3;0)+F4*RECHERCHEV($F$2;Base!$A$4:$C$13;3;0)+G4*RECHERCHEV($G$2;Base!$A$4:$C$13;3;0)+H4*RECHERCHEV($H$2;Base!$A$4:$C$13;3;0)+I4*RECHERCHEV($I$2;Base!$A$4:$C$13;3;0)+J4*RECHERCHEV($J$2;Base!$A$4:$C$13;3;0)+K4*RECHERCHEV($K$2;Base!$A$4:$C$13;3;0)


Merci d'avance.

[gFZT82]

Bonjour Yvanou,

Il semblerait que ta feuille « Calcul » n’a pas résisté à la règle du forum qui spécifie qu’un fichier ne peut être joint à une discussion qu’en dernier recours, lorsque les explications fournies n’ont pas permis de résoudre le problème.

Tu peux effectuer ton calcul avec la formule suivante (exemple pour le Prix total Produit 1) :

Code :

1
2
 
=SOMMEPROD(Base!$C$4:$C$13;TRANSPOSE(B4:K4))

Formule à valider par ctrl+maj+entrée.

Cordialement.

[Yvanou]

Bonjour gFZT82,

Merci pour cette réponse rapide.

J'ai essayé la formule qui, effectivement, reprend l'esprit de ce que je souhaite faire.

Cependant, mon problème vient du fait que l'un des élément du produit est une valeur que je vais chercher par référence à un bordereau de prix qui se trouve dans une autre feuille du classeur.

Ce bordereau se présente sous la forme suivante:

Référence | Désignation composant | Prix unitaire

Le tableau calcul est de la forme suivante:

Nom produit | Qté Composant 1 | ... | Qté Composant n | Prix total |
|Réf. comp 1 | ... | Réf. comp n |

Mon opération est donc:

Quantité de composant * Prix Unitaire du composant, le prix unitaire du composant étant récupéré via la référence du composant depuis le bordereau.
Je souhaite pouvoir conserver la capacité de faire des recherches par références afin de comparer plusieurs agencement de composants ou tout simplement rajouter des colonnes si je veux rajouter des composants.

[gFZT82]

La simplification que je t’ai proposée repose sur la fonction SOMMEPROD qui permet d’effectuer tes calculs à condition que les données soient ordonnées et que les plages aient la même taille.

Si tu veux garder de la souplesse dans l’agencement de tes données et faire subir une cure d’amaigrissement à ta formule, tu peux utiliser les plages nommées.
Je nomme AEL1 la cellule qui contient le prix du Composant type 1 (35€).
Je nomme AEL2 la cellule qui contient le prix du Composant type 2 (45€).
Etc.

La formule devient :

Code :

= B4*AEL1+C4*AEL2+D4*AEL3+E4*AEL4+F4*AEL5+G4*AEL6+H4*AEL7+I4*AEL8+J4*AEL9+K4*AEL10

Cela reste quand même "piégeux" car il n'existe pas de lien réel entre la référence et le nom de la cellule qui contient le prix.

Cordialement.