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

Nino63 à dit:

- 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

Cliquez pour agrandir...

Nino63 à dit:

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.

Cliquez pour agrandir...

gosselien à dit:

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

Cliquez pour agrandir...

Nino63 à dit:

Je ne souhaite pas utiliser de macro VBA.

Cliquez pour agrandir...

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