XL 2019 répartition de factures en fonction des dates

[fred996]

Bonjour,

Je souhaite créer un fichier permettant de répartir des factures en fonction de dates mais je n'y arrive pas.

Mon fichier se présenterait ainsi :

J'ai essayé avec sommeprod sans succès. La répartition doit être fait en nombre de jour.

Merci de votre aide.

 

[gbinforme]

Bonjour,

Que signifie répartir pour toi car sommeprod est fait pour cumuler.

 

[TooFatBoy]

Bonjour,

Mon fichier se présenterait ainsi :

Ma proposition se présenterait ainsi :

J'ai essayé avec sommeprod sans succès.

Cette proposition utilise SommeProd. Mais est-ce avec succès ???


[edit] Ah ben non, ce n'est pas non plus avec succès car j'ai fais par moi alors qu'il faut faire par jours... 🙁 [/edit]

 

[fred996]

Bonjour,


Ma proposition se présenterait ainsi :
Regarde la pièce jointe 1221823



Cette proposition utilise SommeProd. Mais est-ce avec succès ???


[edit] Ah ben non, ce n'est pas non plus avec succès car j'ai fais par moi alors qu'il faut faire par jours... 🙁 [/edit]

Effectivement je souhaiterais au nombre de jours, la ligne 3 par exemple doit prendre pour janvier 12000x2/367

 

[fred996]

Bonjour,

Que signifie répartir pour toi car sommeprod est fait pour cumuler.

Je souhaiterais avoir le cumul des proratas de chaque facture par mois. En case B7, par exemple, la somme des proratas des 3 factures en fonction du nombre de jours indiqué dans les périodes.

 

[TooFatBoy]

Effectivement je souhaiterais au nombre de jours, la ligne 3 par exemple doit prendre pour janvier 12000x2/367

J'ai bien compris ce que tu veux (du moins, je crois), mais je n'arrive pas à trouver une formule qui donne le bon résultat. 🙁

 

[JHA]

Bonjour à tous,

Peut-être un début de piste avec des colonnes supplémentaires.

JHA

 

[djidji59430]

Bonjour à tous,

en changeant la mise en forme

 

[TooFatBoy]

je n'arrive pas à trouver une formule qui donne le bon résultat. 🙁

J'ai peut-être bien fini par trouver, avec cette magnifique formule matricielle 🤯 :

{=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)))}

 

[mapomme]

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é:

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

 

[fred996]

Bonjour à tous,

Peut-être un début de piste avec des colonnes supplémentaires.

JHA

Merci de ton aide. Mon fichier de travail comprend aujourd'hui des formules supplémentaires, mon but était justement de les supprimer.

 

[fred996]

Bonjour à tous,

en changeant la mise en forme

Merci de ton aide. Mon but étant justement de supprimer des colonnes supplémentaires que j'utilise aujourd'hui.

 

[fred996]

J'ai peut-être bien fini par trouver, avec cette magnifique formule matricielle 🤯 :

{=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)))}

Regarde la pièce jointe 1221875

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.

 

[fred996]

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é:

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

Hello,
Merci pour ton aide.

En regardant ton fichier ça correspond exactement à ce que je voulais.

Pour l'instant je ne suis pas à l'aise avec les fonction VBA donc il faut que je penche dessus, je reviens vers toi si j'ai des questions.

 

[mapomme]

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.

Pour une formule matricielle :

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é.