Microsoft 365 Lissage du budget sur nombre de jours du mois

aba2s

XLDnaute Junior
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
Code:
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
 

mapomme

XLDnaute Barbatruc
Supporter XLD
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.
 
Dernière édition:

Dranreb

XLDnaute Barbatruc
Bonjour.
Là je dirais, en AC2, à propager sur 4 lignes et 12 colonnes :
Code:
=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)
 
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour mapomme, Bernard,

On peut en effet se passer des dates et bien sûr du VBA, formule en AC2 :
Code:
=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+
 

Pièces jointes

  • Data(1).xlsx
    13.2 KB · Affichages: 10

mapomme

XLDnaute Barbatruc
Supporter XLD
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 :
VB:
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
 

Pièces jointes

  • aba2s- repartir budget- v1.xlsm
    24.7 KB · Affichages: 11
Dernière édition:

job75

XLDnaute Barbatruc
Ah tiens la conversion avec -- n'est pas nécessaire, fichier (2) avec en AC2 :
Code:
=SIERREUR(EXP(LN(MIN($X2;FIN.MOIS("1/"&COLONNES($AC:AC);0))-MAX($W2;"1/"&COLONNES($AC:AC))+1))*$V2/($X2-$W2+1);"")
 

Pièces jointes

  • Data(2).xlsx
    13.2 KB · Affichages: 6

job75

XLDnaute Barbatruc
A mon avis une fonction VBA est sans intérêt mais si l'on y tient on peut utiliser :
VB:
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
 

Pièces jointes

  • VBA(1).xlsm
    18.4 KB · Affichages: 15

aba2s

XLDnaute Junior
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.
 

Discussions similaires

Réponses
3
Affichages
359
Haut Bas