Microsoft 365 Sumproduct inversé" Besoin d'aide pour mon Business Plan, j'ai rdv lundi..

S

sugathom

XLDnaute Nouveau
Bonjour à tous,

Je me suis lancé dans un Business Plan un peu trop complexe pour moi a priori ! J'ai un Excel à 1000 lignes avec plusieurs onglets de type base de données, listant 1000 enseignes en ligne et je veux aboutir au nombre d'unités commandé mois par mois par enseigne.
Tout est expliqué dans l'Excel ci-joint (il me faudrait une solution automatisée, voire le code pour une macro VBA, bien que je n'en ai jamais faite et donc je préfèrerais faire une formule Excel). En gros, je crois que le problème revient à faire un "sumproduct à l'envers".

J'ai un rdv lundi avec mon banquier...
Un grand merci par avance pour votre aide !
Romain
 

Pièces jointes

  • Besoin d'aide sur Excel.xlsx
    11.3 KB · Affichages: 30
Dernière modification par un modérateur:
Trier par date Trier par votes
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @sugathom et bienvenue sur XLD :),

Une piste via une fonction personnalisée en VBA. Le code n'est pas optimisé pour rester très simple d'utilisation.

Attention! C'est à vous de vérifier tous les résultats. La fonction est donnée telle quelle sans aucune certitude de bon fonctionnement.

Cette fonction utilise trois arguments en entrée :
  1. une plage donnant le nombre de magasins créés chaque mois de janvier à décembre (et non pas le nombre total de magasins - ce serait faisable facilement)
  2. une plage donnant le profil de commande à la création d'un magasin (en général toujours la même plage)
  3. le mois de l'année pour lequel on retourne le total des commandes prévisibles
Par exemple, cela donne pour la cellule D28:
  1. la plage de création mois par mois des magasins de l'enseigne 1 ==> D14:O14
  2. la plage du profil de commande ==> D24:O24
  3. le mois pour lequel on veut la valeur ==> 1
ce qui abouti à la formule : =Cmde12mois(D14:O14;D24:O24;1)

En fait, la formule est un peu plus compliquée car on désire une seule formule en D28 qu'on puisse tirer/copier la formule vers la droite et vers le bas.
  • La plage des créations de magasins est "fixée" en colonne ==> $D14:$O14
  • La plage du profil de commande est "fixée" en ligne et colonne ==> $D$24:$O$24
  • La valeur 1 est incrémentée quand on tire vers la droite par ==> COLONNES($A:A)
ce qui aboutit à la formule ==> =Cmde12mois($D14:$O14;$D$24:$O$24;COLONNES($A:A))

Le code de la fonction est dans Module1 :

VB: 
Function Cmde12mois(xPlageNouveau As Range, xPlageParMois, Nmois As Long)
Dim tNouv, tParMoid, CmdTot(1 To 12), i&, k&
  tNouv = xPlageNouveau.Value
  tParMoid = xPlageParMois.Value
  For i = 1 To 12
    For k = 1 To 12
      If i + k - 1 > 12 Then Exit For
      CmdTot(i + k - 1) = CmdTot(i + k - 1) + tNouv(1, i) * tParMoid(1, k)
    Next k
  Next i
  Cmde12mois = CmdTot(Nmois)
End Function

nota: dans la "bataille", les formules du tableau en jaune ont disparu. Il faudra les y remettre.
 

Pièces jointes

  • sugathom- evol Cmdes- v1.xlsm
    20.2 KB · Affichages: 18
Dernière édition:
Vote positif 0 Vote négatif
S

sugathom

XLDnaute Nouveau
mapomme à dit:
Bonjour @sugathom et bienvenue sur XLD :),

Une piste via une fonction personnalisée en VBA. Le code n'est pas optimisé pour rester très simple d'utilisation.

Attention! C'est à vous de vérifier tous les résultats. La fonction est donnée telle quelle sans aucune certitude de bon fonctionnement.

Cette fonction utilise trois arguments en entrée :
  1. une plage donnant le nombre de magasins créés chaque mois de janvier à décembre (et non pas le nombre total de magasins - ce serait faisable facilement)
  2. une plage donnant le profil de commande à la création d'un magasin (en général toujours la même plage)
  3. le mois de l'année pour lequel on retourne le total des commandes prévisibles
Par exemple, cela donne pour la cellule D28:
  1. la plage de création mois par mois des magasins de l'enseigne 1 ==> D14:O14
  2. la plage du profil de commande ==> D24:O24
  3. le mois pour lequel on veut la valeur ==> 1
ce qui abouti à la formule : =Cmde12mois(D14:O14;D24:O24;1)

En fait, la formule est un peu plus compliquée car on désire une seule formule en D28 qu'on puisse tirer/copier la formule vers la droite et vers le bas.
  • La plage des créations de magasins est "fixée" en colonne ==> $D14:$O14
  • La plage du profil de commande est "fixée" en ligne et colonne ==> $D$24:$O$24
  • La valeur 1 est incrémentée quand on tire vers la droite par ==> COLONNES($A:A)
ce qui aboutit à la formule ==> =Cmde12mois($D14:$O14;$D$24:$O$24;COLONNES($A:A))

Le code de la fonction est dans Module1 :

VB: 
Function Cmde12mois(xPlageNouveau As Range, xPlageParMois, Nmois As Long)
Dim tNouv, tParMoid, CmdTot(1 To 12), i&, k&
  tNouv = xPlageNouveau.Value
  tParMoid = xPlageParMois.Value
  For i = 1 To 12
    For k = 1 To 12
      If i + k - 1 > 12 Then Exit For
      CmdTot(i + k - 1) = CmdTot(i + k - 1) + tNouv(1, i) * tParMoid(1, k)
    Next k
  Next i
  Cmde12mois = CmdTot(Nmois)
End Function

nota: dans la "bataille", les formules du tableau en jaune ont disparu. Il faudra les y remettre.
Cliquez pour agrandir...

Super, un grand merci pour votre aide et pour votre réactivité ! Cela fonctionne parfaitement ! Il faut simplement que je l'adapte à mon Excel un peu plus complexe et ça devrait le faire !! J'ai toutefois encore un peu de mal, en fait, mon exemple était peut-être pas pertinent car la trajectoire de commandes par magasin (la deuxième plage de données) dépend de chaque enseigne et n'est pas tout le temps la même. Comment adapter votre macro ? (désolé j'aurais dû faire un meilleur exemple, vous trouverez le fichier exemple revu ci-joint).

Un immense merci !
 

Pièces jointes

  • Besoin d'aide sur Excel_v2.xlsx
    11.8 KB · Affichages: 9
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Même formule, mais on ne "fixe" que les colonnes dans le profil des commandes par mois soit: pour D32 :
VB: 
=Cmde12mois($D14:$O14;$D24:$O24;COLONNES($A:A))
Le code de la fonction personnalisée n'a pas changé.
 

Pièces jointes

  • sugathom- evol Cmdes- v2.xlsm
    17.7 KB · Affichages: 17
Vote positif 0 Vote négatif
S

sugathom

XLDnaute Nouveau
mapomme à dit:
Re,

Même formule, mais on ne "fixe" que les colonnes dans le profil des commandes par mois soit: pour D32 :
VB: 
=Cmde12mois($D14:$O14;$D24:$O24;COLONNES($A:A))
Le code de la fonction personnalisée n'a pas changé.
Cliquez pour agrandir...
mapomme à dit:
Re,

Même formule, mais on ne "fixe" que les colonnes dans le profil des commandes par mois soit: pour D32 :
VB: 
=Cmde12mois($D14:$O14;$D24:$O24;COLONNES($A:A))
Le code de la fonction personnalisée n'a pas changé.
Cliquez pour agrandir...

Super merci beaucoup ! En effet, j'avais mal compris le code désolé, ma question était un peu idiote. J'ai réussi à l'adapter sur mon fichier. Un immense merci pour votre aide ! :)
excellent week-end !
 
Vote positif 0 Vote négatif
chris

chris

XLDnaute Barbatruc
Bonjour

J'avais de mon côté réalisé une solution PowerQuery mais je ne saisis pas le lien entre le tableau des lignes 14-18 et celui de la ligne 24 devenu 24-28.

Pour moi c'était 2 types d'enseignes avec chacun des règles différentes... Pourquoi la multiplication ?

Juste pour comprendre...

Autre question : pour moi 10+5% font 10,5 et non 12 : donc ce tableau m'interroge à double titre...
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Re :),
chris à dit:
En D14 100
en D24 100

En D32 ta fonction donne 10000
Cliquez pour agrandir...
La fonction renvoie un résultat juste (me semble-t-il).
  • Il y a 100 nouveaux magasins en janvier pour l'enseigne 1 (cellule D14)
  • Quand un magasin de l'enseigne 1 se crée, il commande la première fois 100 produits (cellule D24), deux mois plus tard il commandera 12 produits (cellule F24), etc.
  • Donc les 100 nouveaux magasins commandent bien 100 mag * 100 produits = 10000 produits en janvier.

En fait il y a sans doute une erreur dans la cellule D24. Ce devrait être la constante 10 (probablement) et certainement pas la formule qui y traine.
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @chris ;)

chris à dit:
Non finalement le second mois on devrais avoir les mêmes commandes que le 1er + l'évolution nb magasin * évolution qté

La logique m'échappe...
Cliquez pour agrandir...

Ce que j'en ai compris personnellement.

Le tableau ligne 14 donne les créations de magasin pour la chaine 1 (création vis à vis du fournisseur).
  • En janvier, 100 nouveaux magasins vont être créés.
  • En février, 10 nouveaux magasins vont être créés.
  • En mars, 10 autres nouveaux magasins vont être créés.
  • En avril, 10 autres nouveaux magasins vont être créés.
  • etc.
Quand un nouveau magasin est créé, il va commander des quantités selon le schéma de la ligne 24.
  • le mois de sa création il va commander 100 unités
  • le mois M+1 par rapport à sa création, il va commander 0 unité
  • le mois M+2 par rapport à sa création, il va commander 12
  • le mois M+3 par rapport à sa création, il va commander 0
  • le mois M+4 par rapport à sa création, il va commander 14
  • et ainsi de suite...

100 magasins sont créés en janvier. Ils vont commander:
  • 100 * 100 unités en janvier soit 10 000
  • 100 * 0 unités en février soit 0
  • 100 * 12 unités en mars soit 1 200
  • 100* 0 unités en avril soit 0
  • 100 * 14 unités en mai soit 1 400
  • etc.
10 magasins entrent dans la danse en février. Ils vont commander:
  • 10 * 100 unités en février soit 1 000
  • 10 * 0 unités en mars soit 0
  • 10 * 12 unités en avril soit 120
  • 10 * 0 unités en mai soit 0
  • 10 * 14 unités en juin soit 140
  • etc.
10 autres magasins entrent dans la danse en mars. Ils vont commander:
  • 10 * 100 unités en mars soit 1 000
  • 10 * 0 unités en avril soit 0
  • 10 * 12 unités en mai soit 120
  • 10 * 0 unités en juin soit 0
  • 10 * 14 unités en juillet soit 140
  • etc.
10 nouveaux magasins entrent dans la danse en avril. Ils vont commander:
  • 10 * 100 unités en avril soit 1 000
  • 10 * 0 unités en mai soit 0
  • 10 * 12 unités en juin soit 120
  • 10 * 0 unités en juillet soit 0
  • 10 * 14 unités en août soit 140
  • etc.
donc
  • en janvier, la chaine 1 commandera 10 000 unités (10 000+0+0)
  • en février, la chaine 1 commandera 1 000 unités (0+1000+0)
  • en mars, la chaine 1 commandera 2 200 unités (1200+0+1000)
  • en avril, la chaine 1 commandera 1 120 unités (0+120+0+1000)
  • ....
 
Vote positif 0 Vote négatif
chris

chris

XLDnaute Barbatruc
RE

Merci mapomme de ta patience et de tes explications

Moi j'avais cru comprendre que pour les quantités c'était comme pour les magasins : le magasin commande chaque mois plus que le mois précédent ou (le bimestre précédent pour enseigne 1)

Ce qui du coup complique en particulier pour cet exemple car
le premier mois 100 magasins commandent 100
le second mois 10 magasins commandent 100
le troisième mois 100+10+10 magasins vont commander respectivement 112, 0, 100
le quatrième mois 100+10+10+10 magasins commandent 0, 112, 0, 100


Ta solution semblant satisfaire le demandeur c'est le principal.
 
Vote positif 0 Vote négatif

Membres actuellement en ligne

23 membres et 397 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 094
Messages
2 085 231
Membres
102 828
dernier inscrit
cdupire