Microsoft 365 Lissage du budget sur nombre de jours du mois

[aba2s]

Bonjour la communauté,

Je galère sur une tâche sur Excel/VBA sans succès. Je souhaite lisser le budget sur le nombre de jours du mois sur le nombre total de jours. Je souhaite avoir le même résultat que celui qui se trouve à la ligne 4 de la colonne AC à la colonne AN.

Exemple : Start Date = 24/04/2020
End Date = 31/10/2020
Total Budget = 245.00 €

On devrait avoir sur :

- avril : 245 * 7/ (End Date - Start Date +1)
- mai : 245*31/ (End Date - Start Date +1)
- Juin : 245*30/(End Date - Start Date +1)
- Juillet : 245*31/(End Date - Start Date +1)
- Août : 245*31/(End Date - Start Date +1)
- Sept : 245*30/(End Date - Start Date +1)
- Oct : 245*31/(End Date - Start Date +1)

Merci beaucoup pour votre

Public Function DailyBudgetForecast(L As Integer, C As Integer)


    'Dim i As Byte
    'Dim FeDataPipe As Worksheet: Set eDataPipe = ThisWorkbook.Worksheets("Data - Pipe")
    Dim month1, month2 As Integer
    Application.Volatile
    
    sDate = Cells(L, 23)
    eeDate = Cells(L, 24)
    
    month1 = Month(Cells(L, 23))                 ' Start month extract
    month2 = Month(Cells(L, 24))                 ' End month extract

    If (C - 28) >= month1 And (C - 28) <= month2 Then ' Month segment definition
        
        repart = Day(DateSerial(Year(sDate), Month(sDate) + 1, 0)) - Month(sDate)
        DailyBudgetForecast = repart * Round(Cells(L, 22) / (eeDate - sDate + 1), 2) ' Average value distribution
    Else
        DailyBudgetForecast = ""
    End If
    If DailyBudgetForecast = "" Then DailyBudgetForecast = ""
    
End Function

aide

 

[job75]

Bonjour aba2s,

Dans le code remplacez :

Repart = Evaluate("EXP(LN(MIN(" & dat2 & ",EOMONTH(""" & col & "/1"",0))-MAX(" & dat1 & ",""" & col & "/1"")+1))*" & Total & "/(" & dat2 - dat1 & "+1)")

par :

Repart = Evaluate("(MIN(" & dat2 & ",EOMONTH(""" & col & "/1"",0))-MAX(" & dat1 & ",""" & col & "/1"")+1)*" & Total & "/(" & dat2 - dat1 & "+1)")

Vous comprendrez que EXP(LN(xxx)) permet d'éliminer les valeurs négatives (ou nulles).

A+

 

[Dranreb]

Bonjour.
1/(1/xxx) devrait être tout aussi efficace.
ou presque …
Il devrait y avoir aussi du Sqr(xxx)^2 dedans. Où faire un truc plus clair que ce genre de choses …

 

[job75]

Re, salut Bernard,

En fait on peut supprimer EXP(LN et éliminer les valeurs négatives comme ceci :

Function Repart(Total As Double, dat1 As Long, dat2 As Long, col As Byte)
Repart = Evaluate("(MIN(" & dat2 & ",EOMONTH(""" & col & "/1"",0))-MAX(" & dat1 & ",""" & col & "/1"")+1)*" & Total & "/(" & dat2 - dat1 & "+1)")
If Repart <= 0 Then Repart = ""
End Function

A+

 

[aba2s]

Merci @job75 et @Dranreb ,

J'ai dû mal à comprendre ce bout de code (la division par 1). "" & col & "/1"". On ne peut pas s'y passer?

Merci

 

[job75]

Ave col = 4 par exemple.

""" & col & "/1"" donne "4/1" c'est à dire le 1er avril de l'année en cours en notation US.

 

[job75]

Cela dit pour les mois en ligne 1 il vaut mieux des dates au lieu des textes avec ce code du fichier (2) :

Function Repart(Total#, dat1&, dat2&, datmois&)
Repart = Evaluate("(MIN(" & dat2 & ",EOMONTH(" & datmois & ",0))-MAX(" & dat1 & "," & datmois & ")+1)*" & Total & "/(" & dat2 - dat1 & "+1)")
If Repart <= 0 Then Repart = ""
End Function

 

[Hasco]

Bonjour,

Proposition avec la formule :

=$V2/($X2-$W2)*(MEDIANE($X2;DATEVAL("1/"&COLONNES($A:A));FIN.MOIS("1/"&COLONNES($A:A);0)+1)-MEDIANE($W2;DATEVAL("1/"&COLONNES($A:A));FIN.MOIS("1/"&COLONNES($A:A);0)+1))

bonne soirée

 

[aba2s]

Merci beaucoup @job75

 

[aba2s]

Merci beaucoup @job75
J'ai un dernier point promis 🙂
Nous nous approchons de la fin d'année et j'ai inseré des colonnes 2021(Jan, Fev, Mars, Avril).
Quand je tire la formule, la macro ne prend pas en compte ces nouvelles colonnes 2021.

Je ne vois pas ou modifier?

Merci beaucoup d'avance pour ton aide.

 

[job75]

Bonjour aba2s,

Nous nous approchons de la fin d'année et j'ai inseré des colonnes 2021(Jan, Fev, Mars, Avril).
Quand je tire la formule, la macro ne prend pas en compte ces nouvelles colonnes 2021.

Bien sûr que si, à condition d'utiliser le fichier (2) du post #21.

C'est justement pour ça que je vous ai dit de mettre des dates en ligne 1.

A+

 

[aba2s]

Merci beaucoup @job75 ,

C'est parfait.