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

 

[aba2s]

help please !

 

[job75]

Bonjour aba2s,

Vous êtes inscrit depuis plus d'un an et vous ne voyez pas qu'il faudrait le fichier ?

De plus ceci n'a guère de sens :

Je souhaite lisser le budget sur le nombre de jours du mois sur le nombre total de jours.

A+

 

[aba2s]

Bonjour @job75 ,

Sorry, je pensais avoir le fichier, en effet c'est plus compréhensible avec.

Merci

 

[mapomme]

Bonjour @aba2s, @job75 ,

@JHA a concocté une formule qui permet de trouver le nombre de jour d'un mois donné qui sont dans un intervalle de temps entre une date de début et une date de fin. Voir ICI.
Il suffit ensuite de multiplier cette formule par la quantité journalière de budget : $V2 / ($X2-$W2+1)

Attention : Vos dates (ligne 1) ne sont pas des dates mais du texte. Transformez vos dates en véritables dates au sens d'Excel.
La formule de @Dranreb fonctionne si les dates (en ligne1) sont les premiers des mois.
Quand dans Excel, on veut calculer sur des dates, on utilise des dates et non du texte.

 

[Dranreb]

Bonjour.
Là je dirais, en AC2, à propager sur 4 lignes et 12 colonnes :

=MAX(MIN(DATE(2020;COLONNE()-27;1);$X2+1)-MAX(DATE(2020;COLONNE()-28;1);$W2);0)*$V2/($X2+1-$W2)

Mais je me demande si je ne préfèrerais pas aussi une fonction perso …
Ou bien; attendez, il y a peut être une formule plus simple …
À +
Édition: non je crois que je n'ai pas plus simple (je réfléchissais à un moyen de calculer tout du début jusqu'à la date de la colonne et d'y retrancher la somme des colonnes précédentes, mais … je laisse tomber l'idée)

 

[job75]

Bonjour mapomme, Bernard,

On peut en effet se passer des dates et bien sûr du VBA, formule en AC2 :

=SIERREUR(EXP(LN(MIN($X2;FIN.MOIS("1/"&COLONNES($AC:AC);0))-MAX($W2;--("1/"&COLONNES($AC:AC)))+1))*$V2/($X2-$W2+1);"")

Fonctionne quelle que soit l'année en cours.

A+

 

[mapomme]

d'y retrancher la somme des colonnes précédentes

Si on arrondi les montants intermédiaires (souvent le cas avec des valeurs monétaires) il faudra faire ce calcul pour le dernier mois si on veut que la somme des montants distribués soit égale à la somme initiale.

 

[Dranreb]

Oui, c'est aussi une des raisons qui me ferait préférer une fonction perso.

 

[aba2s]

Merci beaucoup à vous tous @job75 @Dranreb @mapomme

@job75 ta formule marche parfaitement bien

@Dranreb ta formule marche mais pas comme je le voudrai. Je pense si je l'adapte elle marchera parfaitement

@mapomme merci pour les mises en garde et le lien. Je regarderai ça en détails. ça me servira sûrement.

Vous êtes au top.

Bonne journéé à vous tous!

 

[mapomme]

Re,
Bonjour @job75

Oui, c'est aussi une des raisons qui me ferait préférer une fonction perso

Un exemple de ce qu'on pourrait faire. La fonction est générale et prend des dates en paramètre.
Le dernier paramètre est facultatif. S'il est présent, alors on l'affiche quand le résultat de la fonction est nul. S'il est absent, on retourne 0 si le résultat de la fonction est nul.

3 exemples d'utilisation de la fonction:

• Le premier sans aucun arrondi.
• Le second en arrondissant les résultats de la formule à deux chiffres après la virgule (la somme des répartitions n'est pas toujours le montant initial)
• Le troisième en modifiant la formule (à partir de la colonne février) pour assurer que la somme des répartitions est strictement égale au montant initial.

La fonction :

Function NbrJourPeriode1dans2(ByVal D1 As Date, ByVal F1 As Date, ByVal D2 As Date, ByVal F2 As Date, Optional SiZero)
' nombre de jour de la période n°1 [D1, F1] dans la période n°2 [D2, F2]
' SiZero est ce qu'on affiche si le résultat est la valeur 0
' si Sizero est manquant alors on affiche la valeur 0 quand le résultat est 0
Dim n&, d As Date, f As Date
   If F1 < D2 Then
      n = 0
   ElseIf D1 > F2 Then
      n = 0
   Else
      If D1 <= D2 Then d = D2 Else d = D1
      If F1 >= F2 Then f = F2 Else f = F1
      n = f - d + 1
   End If
   NbrJourPeriode1dans2 = n
   If (n = 0) Then NbrJourPeriode1dans2 = IIf(IsMissing(SiZero), 0, SiZero)
End Function

 

[job75]

Ah tiens la conversion avec -- n'est pas nécessaire, fichier (2) avec en AC2 :

=SIERREUR(EXP(LN(MIN($X2;FIN.MOIS("1/"&COLONNES($AC:AC);0))-MAX($W2;"1/"&COLONNES($AC:AC))+1))*$V2/($X2-$W2+1);"")

 

[job75]

A mon avis une fonction VBA est sans intérêt mais si l'on y tient on peut utiliser :

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

 

[aba2s]

Merci à tous pour les fonctions ça convient également et marchent parfaitement bien.

Merci

 

[aba2s]

Bonjour @job75 ,

Peux-tu stp m'expliquer que fait cette partie de la macro?

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

Pourquoi tu as utilisé la combinaison des fonctions exponentielles et logarithmique?
Ne pourrions - nous pas se servir de cette formule (exp(ln(x)) = x ) et utiliser directement
MIN(" & dat2 & ",EOMONTH(""" & col & "/1"",0)) ?

Merci d'avance pour ton aide. Je suis entrain de documenter la macro.