Discussion :

[termor]

Bonjour,

Je vous poste un autre sujet en attendant d'être aidée comme vous le faites si bien.

Je dois créer un fichier pour calculer la marge commerciale:

- 1 tableau achats avec des colonnes pour les fournisseurs, les codes article, la dénomination article, la quantité achetée en kg et le prix de revient.
- 1 tableau ventes aves des colonnes pour les clients, les codes article, la dénomination article, la quantité vendue et le prix de vente.

Le même article peut être acheté à differents fournisseurs à des prix differents et vendu à des clients à des prix differents.

En saissisant le code on obtiendra la denomination pour le tableau des ventes. Formule RechercheV?

On aura besoin de:
- connaitre le prix moyen/denomination ou code pour les achats,
- conaitre la marge commerciale par article et par client


Je ne sait pas si je me suis bien expliquée. Je répondrai à toutes vos questions.

Merci en avance

Cordialement

termor

[Pierre Fauconnier]

Bonjour

Il va falloir jouer avec SOMMEPROD, que tu ne connais peut-être pas...

En gros, SOMMEPROD est une formule "matricielle" qui va, comme son nom l'indique, réaliser la somme des produits, ligne par ligne...

Pour simplifier, j'ai imaginé les données sur la même feuille
En colonnes A à D, ligne1, les données Fournisseurs (Fournisseur, article, Qté, P.R. (prix revient total)), lignes 2 à 10, les données

En colonnes F à I, même structure avec les clients, lignes de 2 à 10

Dans une cellule nommée Article, le code de l'article via une liste de validation sur une feuille de données Articles et un RechercheV pour retrouver l'intitulé.

On place une autre cellule nommée Client avec le nom du client (idem avec liste de validation et recherchev éventuels)

Choisir ou saisir les valeurs x et c dans les cellules Article et Client

Pour avoir la valeur totale de l'article x pour le client c, on peut utiliser ceci

Code :

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

SOMMEPROD((F2:F10=Client)*(G2:G10=Article)*I2:I10)

Pour connaître le nombre d'articles x vendus pour le client C, on utilisera

Code :

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

SOMMEPROD((F2:F5=Client)*(G2:G5=Article)*H2:H5)

Pour récupérer le prix moyen d'achat de l'article, on utilisera

Code :

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

SOMMEPROD((B2:B5=Article)*(D2:D5))/SOMME.SI(B2:B5;Article;C2:C5)

On récupérera donc la marge pour les articles x et le client c avec l'utilisation de tout ce qui est dit plus haut

Code :

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

=SOMMEPROD((F2:F10=Client)*(G2:G10=Article)*I2:I10)-(SOMMEPROD((F2:F5=Client)*(G2:G5=Article)*H2:H5)*SOMMEPROD((B2:B5=Article)*(D2:D5))/SOMME.SI(B2:B5;Article;C2:C5))

Voici le fichier joint qui explique cela. Il reste à le mettre en oeuvre dans ton classeur.

Reviens au besoin...

[termor]

Bonjour,

Je prend en compte vos remarques et je vais essayer de vous communiquer mes lacunes en excel que je n'utilise plus depuis un moment

Voici mon tableau des ACHATS QUOTIDIENS:

- Colonne A: fournisseurs
- Colonne B: code article
- Colonne C: designation article =RECHERCHEV(H3;$B$3:$E$12;2;0)
- Colonne D: quantité
- Colonne D: prix revient

Pour commencer je ne sait pas si je doit renommer la liste des produits et codes que je connais. Doit je le faire avec INSERTION-NOM?
Le nombre des fournisseurs et des produit n'est pas limité.

J'ai mis une formule RECHERCHEV pour pouvoir rentrer seulement le code dans le tableau des ventes. Je crois que ça marche! RECHERCHEV(H3;$B$3:$E$12;2;0)

Je cherche la formule pour avoir une liste avec le prix moyen de revient de chaque produit, qui peut etre vendu par plusieurs fournisseurs:
Exemple: code produit X = ((quantité produit fournisseur 1*prix revient fournisseur 1)+(quantité produit fournisseur 2*prix revient fournisseur 2))/(SOMME POIDS).


Ici le tableau des VENTES QUOTIDIENNES:

- Colonne G: clients
- Colonne H: code article
- Colonne I: designation article
- Colonne J: quantité
- Colonne K: prix revient

Je veux arriver à obtenir la marge de chaque article par client en rapport avec le prix moyen d'achat de chaque produit

Je ne sait pas si je me suis bien expliquée. Je sais que je demande beaucoup, mais je vous etes capables de me tendre une perche...

Bon, au plaisir de vous relire


Termor

[Pierre Fauconnier]

Bonjour

Regarde et essaie de comprendre les formules données dans le fichier joint.

La formule SOMMEPROD permet de regrouper des données en fonction de plusieurs critères...

J'ai détaillé ces formules dans ma précédente réponse. Je propose que tu les regardes, que tu essaies de les comprendre et que tu reviennes pour des explications supplémentaires si elles te semblent nébuleuses...

Ok?

[alsimbad]

Exemple: code produit X = ((quantité produit fournisseur 1*prix revient fournisseur 1)+(quantité produit fournisseur 2*prix revient fournisseur 2))/(SOMME POIDS).

juste pour appuyer ce que dis pierre la formule de ton code X est pratiquement une somme prod que tu as trouvé tout seul

Code :

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

code produit X = sommeprod((quantité produit = fournisseur 1)*(prix revient = fournisseur 1)+(quantité produit = fournisseur 2)*(prix revient fournisseur 2))/(SOMME POIDS).

ne reve pas elle ne marchera pas comme ça, c'est la solution

[termor]

Bonjour Pierre,

Merci pour tes explications et conseils.
J'ai adapté un peu le tableau (j'ai inclus la colonne "designation") mais j'arrive pas à adapter la formule pour récupérer le prix moyen d'achat de l'article, qui me donne toujours un mauvais résultat. Je dois prendre en compte le prix moyen des differents fournisseurs à qui ont achete leurs produits.

Je dépose ici mon fichier. J'aurais aimé que tu le regarde pour savoit où il y a le problème. Je n'arrive pas!

je reviendrai vers toi une fois que j'ai finis de tester (pas beaucoup de temps aujourd'hui)

Merci beaucoup

Pour avoir la valeur totale de l'article x pour le client c, on peut utiliser ceci

Code :

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

SOMMEPROD((F2:F10=Client)*(G2:G10=Article)*I2:I10)

Pour connaître le nombre d'articles x vendus pour le client C, on utilisera

Code :

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

SOMMEPROD((F2:F5=Client)*(G2:G5=Article)*H2:H5)

Pour récupérer le prix moyen d'achat de l'article, on utilisera

Code :

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

SOMMEPROD((B2:B5=Article)*(D2:D5))/SOMME.SI(B2:B5;Article;C2:C5)

On récupérera donc la marge pour les articles x et le client c avec l'utilisation de tout ce qui est dit plus haut

Code :

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

=SOMMEPROD((F2:F10=Client)*(G2:G10=Article)*I2:I10)-(SOMMEPROD((F2:F5=Client)*(G2:G5=Article)*H2:H5)*SOMMEPROD((B2:B5=Article)*(D2:D5))/SOMME.SI(B2:B5;Article;C2:C5))

Voici le fichier joint qui explique cela. Il reste à le mettre en oeuvre dans ton classeur.

Reviens au besoin...

[alsimbad]

ben évidement
si tu écris

Code :

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

SOMMEPROD(F2:F10=Client)*(G2:G10=Article)*j2:j10

c'est un exemple
si tu écris

Code :

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

=SOMMEPROD((F2:F10="ccc")*(G2:G10="FR074")*J2:J10)

c'est que tu as adapté l'exemple
et tu ne devrais pas garder la colonne vide entre tes deux tableau
oops. j'hésite a effacer ce que j'ai écris ou a assumer
j'assume : je te fais mes excuses les plus plates pour la pointe de moquerie, je n'avais pas vu les zones nommées client et articles

[alsimbad]

pour me faire pardonner, je te renvopie le fichier corrigé
ton pb viens du fait que dans un sommeprod toute les plages doivent etre identiques. tu ne peux pas avoir j2:j10 et h2:h8 dans une meme formule.
quand je dis corrigé, je ne suis pas sur du résultat pour ton calcul de marge. un résultat apparait, mais je n'ai pas cherché a savoir ce qu'était le calcul.

[termor]

Bonjour,

Tout d'abord des excuses pour avoir laissé du côté un peu ce sujet, mais finalement j'ai adapté le fichier et marche super bien.

Pierre, Alsimbad, merci pour votre aide, sans laquelle je ne serait jamis arrivé à faire ce fichier tout seule.

Bon courage à vous!!!

Amicalement

Teresa