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+

 

[gosselien]

Autre version avec zones nommées et index/equiv
Explications plus tard, je dois partir ( @Nino63 )

Edit:
ps: je pense que ceci est néanmoins une solution invivable dès que l'on aura 150 recettes et 200 ingrédients. Les formules sont trop fastidieuses, il faudrait repenser la manière de faire , je laisse ça aux spécialistes, ici j'ai fait les formules "pour le fun" 🙂

 

[bsalv]

je cherche une formule dynamique avec la solution de Chris (#8) pour la 3ième ligne de "recettes".
Maintenant la première est dynamique et les 2 autres dépendent de celle.
Le but est d'éventuellement supprimer les 2 premières (mais autrement elles montrent de l'info supplémentaire) mais surtout d'avoir une formule dynamique dans la cellule C3 avec (Let, Map, Lambda, .... ???) qui s'adapte au tableau, si on ajoute ou supprime des colonnes.

 

[bsalv]

Y-a-t-il une solution plus simple ?
les vieilles lignes sont grisées ...

 

[Nino63]

Bonjour,

Je n'arrive pas à comprendre à quoi sert la partie "SEQUENCE()" dans la formule?
Pourriez-vous m'éclairer?

 

[bsalv]

SEQUENCE(;COLONNES(LesRecettes)) crée un matrice d'une ligne et autant de colonnes que la plage "LesRecettes" comme 1;2;3;....;N (avec N=nombre de colonnes)
comme çà on récupère une ligne de la plage "LesRecettes", par exemple, pour "Crêpe" cela sera C5:J5
voir formule en E18 avec les paramètres E15:E16

 

[ALS35]

Bonjour à tous,
Un peu tard peut-être une autre approche à tester, sur la base du fichier de bsalv, avec une formule unique et dynamique sur la feuille Coût et sans lignes supplémentaires (mais est-ce plus simple ??)

=BYROW(A2#;LAMBDA(y;SOMME(BYCOL(EXCLURE(TBL_Recettes[#En-têtes];;2);LAMBDA(x;SIERREUR(EXCLURE(PRENDRE(TRIER(FILTRE(TBL_Prix;TBL_Prix[Ingrédients]=x;"");;-1);1);;2);0)))*EXCLURE(PRENDRE(TRIER(FILTRE(TBL_Recettes;TBL_Recettes[Recettes]=y);;-1);1);;2))))

L'ajout de recettes et d’ingrédients est possible
Cordialement