Rotation future des stocks

[hansdoug]

Bonjour !

J'ai créé un fichier donnant l'exemple de ce que je voulais. En gros, j'ai un stock actuel en euros, et les Chiffres d'Affaires de l'année passée par mois. Ainsi, je veux pouvoir prévoir, en fonction du stock actuel que je possède, combien de mois je peux couvrir.

J'ai beau essayé avec des fonctions "Si", je ne trouve pas la bonne formule...
Je ne voulais pas poster tout de suite car je veux apprendre par moi-même, mais là j'ai bien besoin d'un raccourcis !

Merci d'avance à tous ceux qui jetteront un coup d'oeil !

François.

 

[Victor21]

Re : Rotation future des stocks

Bonjour, hansdoug.

Une proposition en pj, en attendant mieux.
Restriction : si le stock dépasse la durée indiquée.

 

[hansdoug]

Re : Rotation future des stocks

Merci Patrick, ça a été très rapide !
J'ai regardé rapidement, c'est très bien pensé. Je ne sais pas encore si cela correspond exactement à ce que je veux, mais je vais regarder ça tout de suite !

 

[Papou-net]

Re : Rotation future des stocks

Bonjour hansdoug, Victor21,

Ci-joint une solution par macro événementielle, qui calcule le nombre de mois avec 2 décimales, comme demandé dans l'exemple. La modification de la date dans la cellule B11 lance aussitôt la macro et met à jour la cellule H11.

Espérant avoir contribué.

Cordialement.

 

[hansdoug]

Re : Rotation future des stocks

Bonjour et merci !

Je souhaite réellement progresser avec Excel, c'est pourquoi j'aimerai vous poser 2-3 questions. Je ne comprends pas à quoi correspond la valeur inscrite en B11 (40755). Pourriez-vous m'expliquer d'où elle vient ainsi que son utilité ?

Pourquoi les formules ""Si" ligne 7 n'apparaissent pas lorsque la valeur est 1 ? Elle devrait quand même apparaître dans la barre de formule, non ?

et enfin, qu'est-ce qu'une macro évènementielle ? (Là je peux peut-être trouver la réponse tout seul !)

Merci en tout cas ça a l'air d'être EXACTEMENT ce que je recherchais !

 

[Robert]

Re : Rotation future des stocks

Bonjour Hansdoug, bonjour le forum,

Par formule je ne sais pas faire... Je te propose une solution VBA avec la macro événementielle Change qui réagit au changement dans les plages A6:L6 (CA's mensuel), B11 (la date) et C11 (le stock à date). Donc si tu modifies la valeur d'une de ces cellules le calcul se fait automatiquement et le résultat s'affiche en H11.

Le code :

Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim m As String 'déclare la variable m (Mois)
Dim col As Byte 'déclare la variable col (COLonne)
Dim x As Byte 'déclare la variable x (incrément de colonne)
Dim t As Long 'déclare la variable t (Total)
Dim ent As Integer 'déclare la variable ent (ENTier)
Dim dec As Double 'déclare la variable dec (DECimal)
Dim y As Integer 'déclare la variable y (incrément)
Dim test As Boolean 'déclare la variable test
 
If Application.Intersect(Application.Union(Range("B11:C11"), Range("A6:L6")), Target) Is Nothing Then Exit Sub 'si le changement à lieu ailleurs qu'en B11 ou C11, sort de la procédure
m = CStr(Format(Range("B11"), "mmmm")) 'définit le mois de base m
col = Range("A5:L5").Find(m, , xlValues, xlWhole).Column + 1 'définit la colonne de départ
For x = col To 12 'boucle sur toutes des colonnes de la colonne de départ à décembre
    test = False 'définit la variable test
    t = t + Cells(6, x) 'définit le total t
    If t > Range("C11").Value Then 'condition : si le total dépasse le stock à date
        ent = y 'définit la variable ent
        dec = Round(CDbl(Range("C11").Value - (t - Cells(6, x).Value)) / Cells(6, x), 2) 'définit la variable dec
        Exit For 'sort de la boucle
    Else 'sinon
        test = True 'redéfinit la variable test
        ent = 12 - col
    End If 'fin de la condition
    y = y + 1 'incrément y
Next x 'prochaine colonne de la boucle
Range("H11").Value = IIf(test = True, ">= " & ent, CDbl(ent + dec)) 'place le résultat en H11
End Sub

le fichier :

[Édition]
Oooops ! 'ach'ment en retard moi... Bonjour Victor et Papou-net...

 

[Papou-net]

Re : Rotation future des stocks

Bonjour et merci !

Je souhaite réellement progresser avec Excel, c'est pourquoi j'aimerai vous poser 2-3 questions. Je ne comprends pas à quoi correspond la valeur inscrite en B11 (40755). Pourriez-vous m'expliquer d'où elle vient ainsi que son utilité ?

Pourquoi les formules ""Si" ligne 7 n'apparaissent pas lorsque la valeur est 1 ? Elle devrait quand même apparaître dans la barre de formule, non ?

et enfin, qu'est-ce qu'une macro évènementielle ? (Là je peux peut-être trouver la réponse tout seul !)

Merci en tout cas ça a l'air d'être EXACTEMENT ce que je recherchais !

Re :

La valeur 40755 correspond au numéro de série de la date inscrite dans la cellule B11 : apparemment, cette cellule est au format standard, alors que chez moi, elle est au format date. Tu dois donc vérifier le format de cette cellule.

Les formules en ligne 7 ont été effacées par erreur, il suffit donc de les rétablir.

Une macro événementielle est une macro qui est lancée automatiquement lorsqu'un événement se produit dans un objet quelconque de ton classeur. En l'occurence, la macro que j'ai écrite se situe dans le module1, et est appelée dans le module Feuil1, lors de tout changement effectué dans cette feuille :

Feuil1

Private Sub Worksheet_Change(ByVal Target As Range)
Reste
End Sub

Module1

Sub Reste()
Dim Mois As Byte, Rotation As Double
Dim Solde As Currency, NSolde As Currency

Mois = Month(Range("B11").Value)
Solde = Range("C11").Value
NSolde = Solde
Rotation = 0
For c = Mois + 1 To 12
  NSolde = NSolde - Cells(6, c)
  If NSolde < 0 Then Exit For
Next
Rotation = Format(c - Mois - 1 + (Abs(NSolde) / Cells(6, c)), "0.00")
Range("H11") = Rotation & " mois. Il restera " & NSolde & "€"
End Sub

Pour lancer VBA, tu cliques sur ALT-F11 et tu trouveras les modules cités dans la fenêtre Projet - VBA Project. Un double-clic sur l'un de ces modules affichera le code de la macro dans la fenêtre principale.

Espérant avoir répondu.

Cordialement.

Edit : bonjour Robert

 

[Victor21]

Re : Rotation future des stocks

Re,

40755 est le n° de série de la date en B11 (Un jour pour Excel =1). donc le 40755° jour après le 0/1/1900, date correspondant à 0 pour Excel. (A noter que pour Excel, un jour étant égal à 1, une heure est égale à 1/24, et une minute à 1/1440)
Pour t'en convaincre, modifie le format de B11, (Format, nombre, date)

Les formules si(...) en ligne 7 renvoient un 1 si les conditions sont respectées, et un "" (chaine vide) si les conditions ne sont pas respectées :
Si(conditions respectées;1;"")

Une macro événementielle réagit à un événement, exemples :
Worksheet_Change(ByVal Target As Range) à la modification d'une cellule incluse dans Target -à définir.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) au déplacement de la cellule active dans une cellule incluse dans Target -à définir.

Edit : Non seulement je suis à la bourre, mais en plus j'oublie de saluer papou et robert !

 

[hansdoug]

Re : Rotation future des stocks

Merci beaucoup à tous pour votre aide et vos explications.

Robert, vous m'avez mis toutes les explications dans le code.... c'est vraiment gentil !!
Merci pour les explications que vous m'avez fourni et les réponses à mes questions.

Je vais essayer d'appliquer les différentes solutions pour mon fichier professionnel, je vous remercie d'avance, et je posterai pour vous dire si tout marche bien comme il faut sur mon fichier pro !

merci encore.
bien cordialement,

Hansdoug