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
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 :
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)
une plage donnant le profil de commande à la création d'un magasin (en général toujours la même plage)
le mois de l'année pour lequel on retourne le total des commandes prévisibles
Par exemple, cela donne pour la cellule D28:
la plage de création mois par mois des magasins de l'enseigne 1 ==> D14:O14
la plage du profil de commande ==> D24:O24
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.
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 !!
Encore un grand merci !
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 :
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)
une plage donnant le profil de commande à la création d'un magasin (en général toujours la même plage)
le mois de l'année pour lequel on retourne le total des commandes prévisibles
Par exemple, cela donne pour la cellule D28:
la plage de création mois par mois des magasins de l'enseigne 1 ==> D14:O14
la plage du profil de commande ==> D24:O24
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.
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).
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 !
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...
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.
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.