XL 2019 répartition de factures en fonction des dates

mapomme à dit:

Bonjour à tous😉 et bienvenue à @fred996🙂,

J'arrive après la bataille. Donc je propose une toute autre version qui consiste en une fonction VBA personnalisée.
La fonction s'appelle: PourLeMois(....). Cette fonction comporte 4 paramètres:

PourLeMois( xMois As Date , xDebut As Range , xFin As Range , xMontant As Range )​

• xMois est une date ou bien une référence à une cellule contenant une date. A partir de cette date, on en déduit le mois concerné
• xDebut est une référence à une plage en colonne contenant les dates de début de période des factures
• xFin est une référence à une plage en colonne contenant les dates de fin de période des factures
• xMontant est une référence à une plage en colonne contenant les montants des facture des factures

exemple: en cellule B7, la formule =PourLeMois(A7;B$2:B$4;C$2:C$4;D$2:D$4)

• A7 est la cellule contenant une date du mois de novembre 2023
• B$2:B$4 est la plage des dates de début des périodes des factures
• C$2:C$4 est la plage des dates de fin des périodes des factures
• D$2:D$4 est la plage des montants des factures

nota 1: j'ai volontairement distingué les plages de début, de fin et de montant des factures afin de rendre la fonction plus souple vis à vis de la présentation des données. Si on le désire, on peut ne pas le faire. On passerait alors de 4 paramètre à deux paramètres (le mois et la plage des factures). Si nécessaire, me le demander.
nota 2: le résultat dans le classeur a été étendu juste pour l'exemple (de nov-2023 à fév-2026)
nota 3: Une MFC sur la plage du résultat colore le fond des cellules suivant que le mois est pair ou non
nota 4 : Les montants ont le format personnalisé # ##0,00;;; (seuls les montants strictement positif sont visibles)

Dans le fichier joint, deux exemples d'utilisation de la fonction:

• l'une pour un résultat en colonne. Formule en B7 à recopier vers le bas
• l'autre pour un résultat en ligne. Formule en E7 à recopier vers la droite

Le code de la fonction est dans Module1. Il est un peu commenté:

VB:

Function PourLeMois(xMois As Date, xDebut As Range, xFin As Range, xMontant As Range)
Dim tdeb, tfin, tval, debmois As Date, finmois As Date, i&, j&, res#, aux, D As Date, F As Date
   '>>>>>>>> initialisation des variables <<<<<<<<
   tdeb = xDebut.Value      ' array des dates de début des factures   (si élément unique, on le convertit en array)
   If Not IsArray(tdeb) Then aux = tdeb: ReDim tdeb(1 To 1, 1 To 1): tdeb(1, 1) = aux
   tfin = xFin.Value          ' array des dates de fin des factures     (si élément unique, on le convertit en array)
   If Not IsArray(tfin) Then aux = tfin: ReDim tfin(1 To 1, 1 To 1): tfin(1, 1) = aux
   tval = xMontant.Value  ' array des montants des factures         (si élément unique, on le convertit en array)
   If Not IsArray(tval) Then aux = tval: ReDim tval(1 To 1, 1 To 1): tval(1, 1) = aux
   debmois = DateSerial(Year(xMois), Month(xMois), 1)             ' date début du mois
   finmois = DateSerial(Year(xMois), Month(xMois) + 1, 1) - 1     ' date fin du mois
   '>>>>>>>> Calcul de Res  = le total des montants des factures au prorata du mois xMois <<<<<<<<
   For i = 1 To UBound(tdeb)                        ' pour chaque facture
      ' si le mois couvre une partie de la période de la facture
      If Not (debmois > tfin(i, 1) Or finmois < tdeb(i, 1)) Then     ' la période Facture et le mois se recouvrent
         D = tdeb(i, 1): If debmois >= tdeb(i, 1) Then D = debmois   ' borne inférieure de la plage de recouvrement
         F = tfin(i, 1): If finmois <= tfin(i, 1) Then F = finmois   ' borne supérieure de la plage de recouvrement
         res = res + (F - D + 1) * tval(i, 1) / (tfin(i, 1) - tdeb(i, 1) + 1) ' ajout du montant de la période
      End If                                                                  ' (au prorata des jours communs)
   Next i
   PourLeMois = res     ' on indique à la function quoi retourner
End Function

Cliquez pour agrandir...

mapomme à dit:

Pour une formule matricielle :
Regarde la pièce jointe 1221926

nota : sur les dernières versions d'Excel, la validation par les trois touches n'est plus nécessaire. Je ne crois pas qu'Excel 2019 bénéficie de cette fonctionnalité.

Cliquez pour agrandir...

fred996 à dit:

Merci de ton aide. Il faut que je penche sur les formules matricielles que je ne connais pas. J'ai essayé de copier ta formule mais elle reste en format texte et si j'enlève les {}, j'ai un #VALEUR.

Cliquez pour agrandir...

=SOMMEPROD(SI(SI(FIN.MOIS(A7;0)<C$2:C$4;FIN.MOIS(A7;0);C$2:C$4)-SI(A7>B$2:B$4;A7;B$2:B$4)+1>0;SI(FIN.MOIS(A7;0)<C$2:C$4;FIN.MOIS(A7;0);C$2:C$4)-SI(A7>B$2:B$4;A7;B$2:B$4)+1;0)*(D$2:D$4/(DATEDIF(B$2:B$4;C$2:C$4;"d")+1)))

TooFatBoy à dit:

J'ai mis les accolades pour montrer que c'est une formule matricielle, mais il ne faut pas les mettre. Elles s'ajoutent automatiquement quand tu valides la formule en mode matriciel, donc avec <Ctrl>+<Maj>+<Entrée>.

Cliquez pour agrandir...

fred996 à dit:

Re

J'ai fait un

J'ai intégré ton code vba à mon fichier, ça fonctionne merci beaucoup.

Cliquez pour agrandir...

mapomme à dit:

Bonjour à tous😉 et bienvenue à @fred996🙂,

J'arrive après la bataille. Donc je propose une toute autre version qui consiste en une fonction VBA personnalisée.
La fonction s'appelle: PourLeMois(....). Cette fonction comporte 4 paramètres:

PourLeMois( xMois As Date , xDebut As Range , xFin As Range , xMontant As Range )​

• xMois est une date ou bien une référence à une cellule contenant une date. A partir de cette date, on en déduit le mois concerné
• xDebut est une référence à une plage en colonne contenant les dates de début de période des factures
• xFin est une référence à une plage en colonne contenant les dates de fin de période des factures
• xMontant est une référence à une plage en colonne contenant les montants des facture des factures

exemple: en cellule B7, la formule =PourLeMois(A7;B$2:B$4;C$2:C$4;D$2:D$4)

• A7 est la cellule contenant une date du mois de novembre 2023
• B$2:B$4 est la plage des dates de début des périodes des factures
• C$2:C$4 est la plage des dates de fin des périodes des factures
• D$2:D$4 est la plage des montants des factures

nota 1: j'ai volontairement distingué les plages de début, de fin et de montant des factures afin de rendre la fonction plus souple vis à vis de la présentation des données. Si on le désire, on peut ne pas le faire. On passerait alors de 4 paramètre à deux paramètres (le mois et la plage des factures). Si nécessaire, me le demander.
nota 2: le résultat dans le classeur a été étendu juste pour l'exemple (de nov-2023 à fév-2026)
nota 3: Une MFC sur la plage du résultat colore le fond des cellules suivant que le mois est pair ou non
nota 4 : Les montants ont le format personnalisé # ##0,00;;; (seuls les montants strictement positif sont visibles)

Dans le fichier joint, deux exemples d'utilisation de la fonction:

• l'une pour un résultat en colonne. Formule en B7 à recopier vers le bas
• l'autre pour un résultat en ligne. Formule en E7 à recopier vers la droite

Le code de la fonction est dans Module1. Il est un peu commenté:

VB:

Function PourLeMois(xMois As Date, xDebut As Range, xFin As Range, xMontant As Range)
Dim tdeb, tfin, tval, debmois As Date, finmois As Date, i&, j&, res#, aux, D As Date, F As Date
   '>>>>>>>> initialisation des variables <<<<<<<<
   tdeb = xDebut.Value      ' array des dates de début des factures   (si élément unique, on le convertit en array)
   If Not IsArray(tdeb) Then aux = tdeb: ReDim tdeb(1 To 1, 1 To 1): tdeb(1, 1) = aux
   tfin = xFin.Value          ' array des dates de fin des factures     (si élément unique, on le convertit en array)
   If Not IsArray(tfin) Then aux = tfin: ReDim tfin(1 To 1, 1 To 1): tfin(1, 1) = aux
   tval = xMontant.Value  ' array des montants des factures         (si élément unique, on le convertit en array)
   If Not IsArray(tval) Then aux = tval: ReDim tval(1 To 1, 1 To 1): tval(1, 1) = aux
   debmois = DateSerial(Year(xMois), Month(xMois), 1)             ' date début du mois
   finmois = DateSerial(Year(xMois), Month(xMois) + 1, 1) - 1     ' date fin du mois
   '>>>>>>>> Calcul de Res  = le total des montants des factures au prorata du mois xMois <<<<<<<<
   For i = 1 To UBound(tdeb)                        ' pour chaque facture
      ' si le mois couvre une partie de la période de la facture
      If Not (debmois > tfin(i, 1) Or finmois < tdeb(i, 1)) Then     ' la période Facture et le mois se recouvrent
         D = tdeb(i, 1): If debmois >= tdeb(i, 1) Then D = debmois   ' borne inférieure de la plage de recouvrement
         F = tfin(i, 1): If finmois <= tfin(i, 1) Then F = finmois   ' borne supérieure de la plage de recouvrement
         res = res + (F - D + 1) * tval(i, 1) / (tfin(i, 1) - tdeb(i, 1) + 1) ' ajout du montant de la période
      End If                                                                  ' (au prorata des jours communs)
   Next i
   PourLeMois = res     ' on indique à la function quoi retourner
End Function

Cliquez pour agrandir...