Proratiser des montants en fonction des dates

[ug.one]

Bonjour,

Je cherche une méthode pour proratiser des montants en fonction des dates.
je suis comptable et mon logiciel de comptabilité me permet d'indiquer une périodicité pour les montants saisis.
Ensuite, j'exporte mes écritures sous excel et je souhaite pouvoir faire en sorte que lorsque je saisi une plage de date, il me calcul la somme qui correspond à la période saisie.
Par exemple, si je veux faire une situation du 01/01/12 au 31/03/12:
J'ai une charge de 300€ qui va du 01/03/12 au 31/05/12, il me calcul 100€ de charge pour la période du 01/03/12 au 31/03/12.
Charge de 1200€ qui va du 01/10/2011 au 30/09/2012, charge de 300€ pour la période.

J'ai essayé avec des fonctions si mais ça se complique lorsque ma période sélectionnée est comprise dans la période de la charge.

J'espère avoir été assez clair et que vous pourrez m'aider.

D'avance merci


Voici un modèle de se que je souhaite obtenir au final.
En sachant que toutes les charges n'ont pas une périodicité

 

[Misange]

Re : Proratiser des montants en fonction des dates

Bonjour
Regarde ma proposition sur la feuille écriture(2).
En revanche si tu n'as pas de dates d'utilisation sur cette feuille je ne peux pas inventer... il faut que tu estimes sur quelle période porte cette dépense, personne ne peut le faire à ta place.
j'ai juste utilisé datedif pour faire le prorata au nombre de jours.
cette fonction n'est pas documenté dans excel mais tu trouveras les explications nécessaires ici
Ce lien n'existe plus

 

[ug.one]

Re : Proratiser des montants en fonction des dates

Merci Misange pour ton aide.
Mais il y a un problème car ta formule me calcul une moyenne et me l'applique sur la période.
moi ce que je souhaite c'est que ne soit pris en prorata que le montant de la période, par exemple pour les 300€, qu'il ne compte que 100€ et non pas 300e comme dans ton exemple.
De plus si il n'y a pas de périodicité, il n'y a pas besoin de proratiser et c'est la date de saisie (la première du tableau) qui dit si oui ou non le montant est compris dans la période.
Je ne sais pas si je suis assez clair...

 

[ROGER2327]

Re : Proratiser des montants en fonction des dates

Bonjour à tous

Un essai...​

ROGER2327
#5662

Vendredi 27 Pédale 139 (Saint Valens, frère onirique - fête Suprême Tierce)
1er Germinal An CCXX, 7,2849h - primevère
2012-W12-3T17:29:02Z

 

[Misange]

Re : Proratiser des montants en fonction des dates

Merci Misange pour ton aide.
Mais il y a un problème car ta formule me calcul une moyenne et me l'applique sur la période.
moi ce que je souhaite c'est que ne soit pris en prorata que le montant de la période, par exemple pour les 300€, qu'il ne compte que 100€ et non pas 300e comme dans ton exemple.
De plus si il n'y a pas de périodicité, il n'y a pas besoin de proratiser et c'est la date de saisie (la première du tableau) qui dit si oui ou non le montant est compris dans la période.
Je ne sais pas si je suis assez clair...

oui tu as raison j'ai fait ça vite et je n'avais pas fait attention que certaines périodes commençaient au 1/3
La soluce de Roger devrait te convenir.

 

[ug.one]

Re : Proratiser des montants en fonction des dates

Merci à vous 2 pour votre aide.
Effectivement, la méthode de Roger colle quasiment avec ce que je voulais mais il y a juste un petit bémol.
Vous faites le datedif avec le mois mais il me le faudrait au jour car il se peut que certains montants ne couvrent pas une fin de mois mais aillent jusqu'au 10,15 ou 20 du mois par exemple.
J'ai essayé en changeant juste le "m" en "d" (j'ai lu le très bon lien de Misange). Pour les montants ayant une périodicité cela fonctionne mais par contre pour les autre il me note #NOMBRE! (J'ai aussi ajouté un ARRONDI.AU.MULTIPLE de 10 au début de la formule)
Auriez vous une solution svp?

 

[ROGER2327]

Re : Proratiser des montants en fonction des dates

Re...

(...)
Vous faites le datedif avec le mois
(...)

Les jours étant rarement des douzièmes d'années, il me semble difficile de comprendre votre demande autrement lorsque vous écrivez :

Assurance 300 soit 1200 *3/12

(...)
il me le faudrait au jour car il se peut que certains montants ne couvrent pas une fin de mois mais aillent jusqu'au 10,15 ou 20 du mois par exemple.
(...)

... ce qu'aucun des exemples de votre classeur ne laisse entendre...

(...)
J'ai essayé en changeant juste le "m" en "d" (j'ai lu le très bon lien de Misange). Pour les montants ayant une périodicité cela fonctionne mais par contre pour les autre il me note #NOMBRE!
(...)

Difficile de voir ce qui coince dans une formule qu'on ne voit pas...

(...)
(J'ai aussi ajouté un ARRONDI.AU.MULTIPLE de 10 au début de la formule)
(...)

... ce qui change des choses...

N'eût-il pas été plus judicieux et convivial, comme on dit de nos jours, de poser d'emblée le véritable problème à résoudre ? Nous aurions évité de perdre notre temps à en résoudre un autre...

(...)
Auriez vous une solution svp?

À tout hasard, et à l'aveuglette :

=SI(A2;ARRONDI.AU.MULTIPLE(SI(DATEDIF(E2;F2;"D");D2*DATEDIF(MAX(E2;Feuil1!$B$1);MIN(F2+1;Feuil1!$D$1+1);"D")/DATEDIF(E2;F2+1;"D");(Feuil1!$B$1<=A2)*(A2<=Feuil1!$D$1)*D2);10);)

en H2 de la feuille Feuil1, à recopier vers le bas autant que de besoin.

Si ça ne colle pas, donnez des exemples de données récalcitrantes... Merci d'avance !​

ROGER2327
#5664

Samedi 28 Pédale 139 (Dédicace du Tripode - Vacuation)
2 Germinal An CCXX, 3,7678h - platane
2012-W12-4T09:02:34Z

 

[ug.one]

Re : Proratiser des montants en fonction des dates

Désolé pour mes explications pas très claires et le manque de justesse de mes informations.
Il est vrai que j'avais grossi la chose en faisant des /12, j'aurais dû mettre des /360.
Je me disais que si j'avais une base je pourrais l'ajuster, erreur...

Par contre en cherchant un peu et en modifiant uniquement le 2ème DATEDIF en "d" et en appliquant l'ARRONDI.AU.MULTIPLE uniquement sur la formule VRAI du SI, ça a l'air de "coller".

Votre Méthode fonctionne aussi très bien, mais (j'avais également oublié de le préciser), je souhaite si il n'y a pas de périodicité, prendre le montant exact de la cellule, c'est pourquoi j'ai décaler l'arrondi...

Merci encore en tout cas pour votre aide précieuse. J'espère un jour pouvoir vous rendre la pareil.

 

[ROGER2327]

Re : Proratiser des montants en fonction des dates

Re...

Parfait !
On a donc finalement :

=SI(A2;SI(DATEDIF(E2;F2;"D");ARRONDI.AU.MULTIPLE(D2*DATEDIF(MAX(E2;Feuil1!$B$1);MIN(F2+1;Feuil1!$D$1+1);"D")/DATEDIF(E2;F2+1;"D");10);(Feuil1!$B$1<=A2)*(A2<=Feuil1!$D$1)*D2);)

Bonne continuation.


ROGER2327
#5665

Samedi 28 Pédale 139 (Dédicace du Tripode - Vacuation)
2 Germinal An CCXX, 3,9209h - platane
2012-W12-4T09:24:36Z

 

[ug.one]

Re : Proratiser des montants en fonction des dates

C'est exactement ça!
Merci encore et j'espère que ma demande servira à d'autres personnes.

A une prochaine fois peut-être (maintenant que je connais votre efficacité, je risque de revenir... en étant plus clair sur ma demande!) 😉