Microsoft 365 Calcul de prix moyen pondéré de recettes

[Nino63]

Bonjour,

Je viens vous solliciter pour une aide.
Je vous ai fait un fichier pour illustrer mon problème avec quelques données "simplifiées" par rapport à ma vrai utilisation. Je m'adapterai ensuite.

Mon problème est le suivant:
- j'ai des recettes avec différents ingrédients (onglet "Recettes")
Rq : j'ai différentes date de versions, seule la dernière date la plus récente est à prendre en compte
- j'ai un prix pour chaque ingrédients (onglet "Prix")
- je souhaiterai réaliser une synthèse des calculs de coût de chaque recette

Pour résumer, pour chaque recette je voudrai calculer [le % d'incorporation de chaque ingrédients] x [le prix de chaque ingrédients]

J'imagine qu'il va falloir passer par une formule de type SOMMEPROD, mais je n'arrive pas à la faire tourner.
Je ne souhaite pas utiliser de macro VBA.

-Une contrainte de mon côté, je ne peux pas trop modifier la structure des onglets "Recettes" et "Prix" car ils ne sont pas de ma propriété, ils appartiennent à des collègues.
- Pouvoir ajouter des ingrédients nouveaux (donc des colonnes sur les recettes; et des lignes sur le prix). Idem pour des nouvelles recettes
- Pouvoir gérer le versionning en ne prenant en compte que la dernière recette, ou le dernier prix.

J'espère avoir été assez clair.

 

[chris]

Bonjour

2 tableaux structurés et donc naturellement évolutifs (on peut ajouter lignes aux deux et et colonnes aux recettes) et un calcul par PowerQuery

Quand un des tableaux change il suffit d'actualiser le résultat (clic droit)

 

[bsalv]

bonjour Nino63, un essai
EDIT : Salut Chris

 

[job75]

Bonjour Nino63, chris, bsalv,

Formule très "classique" en Coût!B2 :

=RECHERCHEV(A2;Recettes!B:G;2;0)*RECHERCHEV("Lait";Prix!B:C;2;0)+RECHERCHEV(A2;Recettes!B:G;3;0)*RECHERCHEV("Œufs";Prix!B:C;2;0)+RECHERCHEV(A2;Recettes!B:G;4;0)*RECHERCHEV("Viande";Prix!B:C;2;0)+RECHERCHEV(A2;Recettes!B:G;5;0)*RECHERCHEV("Fromage";Prix!B:C;2;0)+RECHERCHEV(A2;Recettes!B:G;6;0)*RECHERCHEV("Beurre";Prix!B:C;2;0)

Seule contrainte : les tableaux des feuilles "Recettes" et "Prix" doivent être triés par dates des plus récentes aux plus anciennes.

PS : "Beurre" prend 2 "r", j'ai corrigé !

A+

 

[Nino63]

Bonjour,

Un grand merci à tous les 3.
Je vais étudier de plus prêt et intégrer dans mon fichier.

A chaud:
- Chris : j'aime bien l'idée du PQ. Un petit doute par rapport au nom des colonnes d'ingrédients qui pourrait changer (ajout/modification/suppression) dans le temps et nécessiterai d'aller dans le PQ pour le modifier. Je crains que mes collègues aient la compétences pour cela. Mais je me garde la solution sous le coude que j'aime bien et est "propre" car transparente

- bsalv : classique avec des tableaux et des formules. Ca me parait "relativement" simple et évolutif. Une question par rapport à la plage de nom "Ingredients", comment faire si je rajoute une colonne dans le tableau "TBL_Recettes" en colonne H, par exemple "Carottes" pour que cela se prenne en compte automatiquement sans que j'aille modifier la plage manuellement?

- job75 : ça fait le boulot, par contre sauf erreur la formule n'est pas assez évolutive pour moi "automatiquement" et je ne souhaite pas revenir sur la formule dès que mes collègues vont ajouter un ingrédient, le supprimer, changer de nom car ils ont oublié un "R" à beurre ;-) .... En tout cas merci.

Je creuse et transpose dans mon fichier et vous revient pour clore le sujet lorsque c'est OK pour moi.
Encore merci.

 

[chris]

Bonjour

- Chris : j'aime bien l'idée du PQ. Un petit doute par rapport au nom des colonnes d'ingrédients qui pourrait changer (ajout/modification/suppression) dans le temps et nécessiterai d'aller dans le PQ pour le modifier. Je crains que mes collègues aient la compétences pour cela. Mais je me garde la solution sous le coude que j'aime bien et est "propre" car transparente

J'ai fait une seule modif. Ainsi tu peux ajouter ce que tu veux, changer les noms des ingrédients : rien à toucher dans PQ

Le seule contrainte est que les noms d’ingrédients aient la même orthographe dans les 2 tableaux et que les titres de colonnes suivants soient stables :

Date de version, Recettes, Date de prix, Ingrédient, Prix

Dans le solution de bsalv les formules sont également dynamiques : il faut juste étirer à droite la formule située au dessus du tableau des recettes si des colonnes d'ingrédients s'ajoutent... et ajouter le nom de la recettes au tableau des coûts.

 

[gosselien]

Bonjour,

Je viens vous solliciter pour une aide.
Je vous ai fait un fichier pour illustrer mon problème avec quelques données "simplifiées" par rapport à ma vrai utilisation. Je m'adapterai ensuite.

Mon problème est le suivant:
- j'ai des recettes avec différents ingrédients (onglet "Recettes")
Rq : j'ai différentes date de versions, seule la dernière date la plus récente est à prendre en compte
- j'ai un prix pour chaque ingrédients (onglet "Prix")
- je souhaiterai réaliser une synthèse des calculs de coût de chaque recette

Pour résumer, pour chaque recette je voudrai calculer [le % d'incorporation de chaque ingrédients] x [le prix de chaque ingrédients]

J'imagine qu'il va falloir passer par une formule de type SOMMEPROD, mais je n'arrive pas à la faire tourner.
Je ne souhaite pas utiliser de macro VBA.

-Une contrainte de mon côté, je ne peux pas trop modifier la structure des onglets "Recettes" et "Prix" car ils ne sont pas de ma propriété, ils appartiennent à des collègues.
- Pouvoir ajouter des ingrédients nouveaux (donc des colonnes sur les recettes; et des lignes sur le prix). Idem pour des nouvelles recettes
- Pouvoir gérer le versionning en ne prenant en compte que la dernière recette, ou le dernier prix.

J'espère avoir été assez clair.

Bonjour,
J'imagine que la formule devra prendre la date la plus récente pour un même produit trouvé 2 ou 3 fois ?
En supposant que plus récent peut être + ou - cher ... Ce que je n'ai pas su faire dans mes essais...
Pour la lisibilité, j'ai nommé les zones 🙂
P.

 

[chris]

Bonjour

Bonjour,
J'imagine que la formule devra prendre la date la plus récente pour un même produit trouvé 2 ou 3 fois ?
En supposant que plus récent peut être + ou - cher ... Ce que je n'ai pas su faire dans mes essais...
Pour la lisibilité, j'ai nommé les zones

Belle formule.

Comme pour la solution de job75 cela oblige à classer les Prix par ingrédient+date et les Recettes par recette+date

N'utilisant pas des tableaux structurés cela n'accepte pas de nouvelles colonnes ni lignes alors qu'en les utilisant on le pourrait en adaptant tes plages nommées.

On peut dynamiser la liste des recettes sur l'onglet Coût avec une formule 365... mais il restera à étirer la formule du coût...

 

[job75]

Bonjour le forum,

Je ne souhaite pas utiliser de macro VBA.

Tant pis pour vous, cette solution concerne donc ceux que VBA intéresse :

Private Sub Worksheet_Activate()
Dim T1, ncol%, T2, resu(), i&, n&, j%, prix
With Sheets("Recettes").[A1].CurrentRegion
    .Sort .Columns(2), xlAscending, .Columns(1), , xlDescending, Header:=xlYes 'tri sur 2 colonnes
    T1 = .Value 'matrice, plus rapide
    ncol = .Columns.Count
End With
With Sheets("Prix").[A1].CurrentRegion.Resize(, 3)
    .Sort .Columns(2), xlAscending, .Columns(1), , xlDescending, Header:=xlYes 'tri sur 2 colonnes
    T2 = .Columns(2).Resize(, 2) 'matrice, plus rapide
End With
ReDim resu(1 To UBound(T1), 1 To 2)
For i = 2 To UBound(T1)
    If T1(i, 2) <> T1(i - 1, 2) Then
        n = n + 1
        resu(n, 1) = T1(i, 2)
        For j = 3 To ncol
            prix = Application.VLookup(T1(1, j), T2, 2, 0)
            If IsNumeric(T1(i, j)) And IsNumeric(prix) Then resu(n, 2) = resu(n, 2) + T1(i, j) * prix
        Next j
    End If
Next i
'---restitution---
With [A2] '1ère cellule de destination
    If n Then .Resize(n, 2) = resu: .Resize(n, 2).Borders.Weight = xlThin 'avec bordures
    .Offset(n).Resize(Rows.Count - n - .Row + 1, 2).Delete xlUp 'RAZ en dessous
End With
End Sub

La macro est dans le code de la feuille "Coût" et se déclenche quand on active cette feuille.

A+