Problème calcul Prorata

[tiouinch]

Bonjour à tous !

Je refais appel à vous car je bute sur un problème depuis quelques jours.
J’ai un tableau avec trois calculs de prorata possible (mensuel, annuel et trimestriel)
Pour la périodicité mensuelle j’ai trouvé la formule, pour l’annuelle, c’est en cours.

Là où je bute c’est :
1) Si la périodicité est trimestrielle, comment lui faire comprendre qu’en fonction de la période, il faut qu’il prenne le nombre de jours du Trimestre en cours ? (j’insiste sur le « en cours »)
2) sur une manière d’englober ces trois calculs en une seule formule pour la dérouler ensuite vers les autres cellules ?

Il y a des chances pour que je ne prenne pas le problème par le bon bout mais comme j’ai le nez dedans je n’arrive pas à m’en dépêtrer.

J’ai préparé un petit tableau explicatif avec mes « débuts » de réflexion si cela peut aider et me tiens prêt à répondre à vos questions si je ne suis pas assez précis (ce qui risque grandement d’arriver et je m’en excuse par avance)

Je vous remercie d’avance beaucoup et vous souhaite une bonne journée !
Tiouinch.

 

[VDAVID]

Re : Problème calcul Prorata

Salut Tiouinch,

Je vois que tu travailles sous excel 2007 (Selon ton profil) mais c'est un classeur au format 97 - 2003 que tu as donné en pièce jointe.

Du coup je ne peux pas imbriquer la fonction pour calculer le nombre de jours par trimestre dans la formule "magique" qui permettrait de faire les 3 en même temps.

Si tu travailles sous excel 2003, il faudra passer par une cellule intermédiaire reprenant ce calcul (Comme je l'ai mis dans le fichier en P.J. (Sauf formule plus simple auquel je n'ai pas pensé)

Si tu travailles sous excel 2007 ou plus récent, tu pourras tout regrouper dans une seule formule comme je te l'indique ici:

I2*H2/SI(C2="MENSUELLE";JOUR(DATE(ANNEE(B12);MOIS(B12)+1;1)-1);SI(C2="ANNUELLE";DATE(ANNEE(B12)+1;1;1)-DATE(ANNEE(B12);1;1);DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);JOUR(FIN.MOIS(DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);1);0)))-DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)=0;-2;1)-MOD(MOIS(B12);3);1)))

Après c'est un peu barbare comme formule, peut-être que d'autres en auront des simplifiées

La formule pour calculer le nombre de jour dans un trimestre de la date correspondante:

=DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);JOUR(FIN.MOIS(DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);1);0)))-DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)=0;-2;1)-MOD(MOIS(B12);3);1)

N'hésite pas si tu as des questions !
Bonne journée

 

[tiouinch]

Re : Problème calcul Prorata

Bonjour VDAVID !

Effectivement, je travaille sous excel 2007 mais le fichier doit être compatible avec les versions 97-2003.
Je vais éplucher ton fichier et essayer de l'adapter à mon fichier original. Un grand merci à toi en tout cas pour tes propositions !
Même en cherchant deux siècles je n'aurais rien trouvé de semblable.

Je regarde et je te dis ça au plus vite !

A très vite ! Tiouinch.

 

[tiouinch]

Re : Problème calcul Prorata

Re VDAVID,

Après test, niveau calcul ça a l'air d'aller, je n'ai pas poussé le vice loin mais je le ferai ultérieurement. En revanche, je crois que le compteur de jours du trimestre contient une petite erreur. En effet, il me compte un jour de moins (année bissextile compris)
Pour le premier trimestre en année "normale" il me compte 89 jours (90 normalement) et 90 en année bissextile (91 normalement)

Peut-être cela est dû au fait que je souhaite que le jour en cours doit être pris en compte, chose que je ne t'avais sans doute pas précisé et je m'en excuse, cela a pu t'induire en erreur.

 

[VDAVID]

Re : Problème calcul Prorata

Re tiouinch,

Effectivement, j'ai omis ce détail le dernier jour du dernier mois du trimestre n'est pas pris en compte.
Il faut juste rajouter +1 à la fin de la formule et ça marchera année bisextile comprise

 

[tiouinch]

Re : Problème calcul Prorata

Bon... je ne vais pas dire "Je le savais!" mais en fait euhh je l'savais !
Ok je vais tenter cette folle expérience ! Merci beaucoup Vdavid ! Je te tiens au courant, je vais continuer mes tests !

Un grand merci pour ta disponibilité et ta rapidité !

 

[mapomme]

Re : Problème calcul Prorata

Bonjour tyiouinch, VDAVID,

pour le fun, un essai avec une formule en J2 à tirer vers le bas:

=SI(H2="";"";I2*H2 /CHOISIR(EQUIV(GAUCHE(C2;1);{"M";"T";"A"};0); JOUR(DATE(ANNEE(B12);MOIS(B12)+1;0));CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92);365+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)))))

nota: enregistré en excel2003 à partir de excel2010.

 

[tiouinch]

Re : Problème calcul Prorata

Bonjour tyiouinch, VDAVID,

pour le fun, un essai avec une formule en J2 à tirer vers le bas:

=SI(H2="";"";I2*H2 /CHOISIR(EQUIV(GAUCHE(C2;1);{"M";"T";"A"};0); JOUR(DATE(ANNEE(B12);MOIS(B12)+1;0));CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92);365+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)))))

nota: enregistré en excel2003 à partir de excel2010.

Bonjour Mapomme !

Je te remercie pour ta solution. Je n'ai pas encore fini les tests sur la proposition de VDAVID, mais dès que j'en aurais terminé je me pencherai sur la tienne !

Un grand merci en tout cas de ton aide, il est vrai qu'il est appréciable d'avoir deux solutions à un même problème, cependant, je serai bien en peine de déterminer laquelle des deux est la mieux !

Afin de me perfectionner et avoir le plaisir à mon tour d'aider les autres dans la mesure de mes connaissances fraîchement acquises, peux-tu m'expliquer comment fonctionne ta formule.

Cela m'intéresse et si VDAVID pouvait faire de même cela m'aiderait à progresser.

Un grand merci à vous deux en tout cas.

PS : mapomme, j'ai bien lu l'heure de ton poste ????

 

[VDAVID]

Re : Problème calcul Prorata

Bonjour Tiouinch, Mapomme,

Alors cette formule:

DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0; 3;0)-MOD(MOIS(B12);3);JOUR(FIN.MOIS(DATE(ANNEE(B12);MOI S(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);1);0)))-DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)=0;-2;1)-MOD(MOIS(B12);3);1)

Il y'a deux parties. L'idée c'est de soustraire le dernier jour du trimestre avec le premier. Donc pour trouver le dernier jour du trismestre on a:

DATE(ANNEE(B12);MOIS(B12)+SI(MOD(MOIS(B12);3)<>0; 3;0)-MOD(MOIS(B12);3);JOUR(FIN.MOIS(DATE(ANNEE(B12);MOI S(B12)+SI(MOD(MOIS(B12);3)<>0;3;0)-MOD(MOIS(B12);3);1);0)))

La formule date a besoin de trois données. L'année, le mois et le jour de la date voulue.

Pour l'année, pas de problème on sélectionne l'année en cours:

ANNEE(B12)

Pour le mois, on veut le dernier mois du trismestre en cours.
On a alors trois cas:

- Soit le mois en cours est le premier du trimestre
- Soit le mois en cours est le deuxième du trismestre
- Soit le mois en cours est le troisième du trimestre

Pour déterminer laquelle de ces trois siuation est vraie, on utilise la fonction MOD()

La fonction MOD() permet de faire la division Euclidienne d'un nombre par un diviseur. (En termes moins barbare, on prends un nombre, on le divise et MOD() renvoie le dernier reste du résultat entier de la division.) Si je prends 17 et que je le divise par 5, MOD() me renverra 2 soit le reste de 17/5 qui est de 3 pour un résultat entier.

En l'occurence ç'est pratique car en utilisant mod sur le mois avec comme diviseur 3, on sait la position du mois en cours dans le trimestre. Si MOD() renvoie 1:

On sait que c'est le premier mois du trimestre: Exemple Avril ==> Mois 4. 4/3 = 1 Reste 1

Si il renvoie 2:

On sait que c'est le deuxième mois du trimestre: Exemple Novembre ==> Mois 11. 11/3 = 3 Reste 2

Si il renvoie 0:

On sait que c'est le dernier mois du trimestre: Exemple Décembre ==> Mois 12. 12/3 = 4 Reste 0

C'est ce que fait la partie de la formule:
MOD(MOIS(B12);3)

L'idéal était qu'elle renvoie 3 si le mois en cours était le troisième du trimestre. Auquel cas on aurait fait: MOIS(B12) + 3 - MOD(MOIS(B12);3) pour déterminer le mois supérieur.

Mais comme la fonction renvoie 0 lorsque le mois en cours est le dernier mois du trismestre, il faut rajouter une condition:

SI(MOD(MOIS(B12);3)<>0; 3;0)

De cette façon on peut déterminer la position du mois en cours dans le trimestre concerné.

Pour récupérer le JOUR, il faut prendre le dernier jour du dernier mois du trimestre, soit l'utilisation de la fonction FIN.MOIS, combiné à la partie précédente afin de déterminer la aussi le dernier mois du trimestre.

La dernière partie suit la même logique, mais de façon miroir.

Voilà j'espère que c'est suffisamment clair , n'hésite pas dans le cas contraire !
Bonne journée

 

[mapomme]

Re : Problème calcul Prorata

Bonsoir Tiouinch, VDAVID,

On va commencer par calculer les durées en jour de chaque période à partir de B12.

Pour le mois: JOUR(DATE(ANNEE(B12);MOIS(B12)+1;0))
On cherche la date correspondant à l’année de B12, au mois suivant celui de B12 et au jour 0.
DATE(ANNEE(B12);MOIS(B12)+1;0)) En principe, cela renvoie le dernier jour du mois précédent soit le dernier jour du mois de B12. En en prenant le JOUR, on arrive au résultat.

Pour le trimestre: CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92)

Le 1ier trimestre possède 90 jours (31+28+31) augmenté de 1 si l’année est bissextile.
Le 2ème trimestre possède 91 jours (30+31+30).
Le 3ème trimestre possède 92 jours (31+31+30).
Le 4ème trimestre possède 92 jours (31+30+31).

La correction pour année bissextile se base sur l’existence ou non du 29 fév.
On cherche le numéro de série de la date correspondant au 29 fév de l’année de B12 : DATEVAL("29/02/"&ANNEE(B12)) Si cette date existe alors on renvoie le numéro de série sinon une erreur est renvoyée.
On teste si le retour est numérique : ESTNUM(DATEVAL("29/02/"&ANNEE(B12))) Ceci renvoie donc VRAI si le 29 fév existe et renvoie FAUX dans le cas contraire. Quand VRAI et FAUX interviennent dans une addition, VRAI est converti en 1 et FAUX en 0.
Nous avons donc obtenu notre correction en cas d’années bissextile ou non : +ESTNUM(DATEVAL("29/02/"&ANNEE(B12)))

Nous allons maintenant utiliser la fonction CHOISIR qui en fonction d’un index k évalue la kième formule qui suit :
CHOISIR( index k; formule 1; formule 2; formule k; …) sous la forme :
CHOISIR( n° du trimestre; nb jours trim 1; nb jours trim 2; nb jours trim 3; nb jours trim 4)

Il reste à calculer le numéro du trimestre :
Pour cela, on prend le mois de B12 auquel on ôte 1 : (MOIS(B12)-1)
On divise par 3 et on prend la partie entière : ENT((MOIS(B12)-1)/3)
Les mois 1,2,3 renvoient 0, les mois 4,5,6 renvoient 1, les mois 7,8,9 renvoient 2 et les mois 10,11,12 renvoient 3.
Comme l’index de la fonction CHOISIR doit commencer à 1 et non à 0, on rajoute 1 ce qui donne :
1+ENT((MOIS(B12)-1)/3)

La formule des nombres de jours pour le trimestre devient donc :
CHOISIR(1+ENT((MOIS(B12)-1)/3); nb jours trim 1; nb jours trim 2; nb jours trim 3; nb jours trim 4)
soit :
CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92)


Pour l’année :365+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)))
Une année possède 365 jours + 1 si année bissextile. Nous avons vu ci-dessus comment introduire la correction pour les années bissextiles.


Le reste de la formule :
La cellule C2 contient la période. On va isoler le premier caractère de la période en C2 GAUCHE(C2;1)et rechercher sa position dans le tableau {"M";"T";"A"}. On utilise la fonction EQUIV : EQUIV(GAUCHE(C2;1);{"M";"T";"A"};0) qui va renvoyer 1 pour « M », 2 pour « T » et 3 pour « A »

On utilise encore une fois la fonction CHOISIR qui en fonction de la période va retourner le nombre de jouir de la période :
CHOISIR(index période ; nbr jours mois, nbr jours trim, nbr jours année)

On obtient la formule pour le nombre de jours selon la période : CHOISIR(EQUIV(GAUCHE(C2;1);{"M";"T";"A"};0); JOUR(DATE(ANNEE(B12);MOIS(B12)+1;0));CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92);365+ESTNUM(DATEVAL("29/02/"&ANNEE(B12))))

La formule finale s’écrit donc :
=SI(H2="";"";I2*H2 / nombre de jours selon la période )soit

=SI(H2="";"";I2*H2 /CHOISIR(EQUIV(GAUCHE(C2;1);{"M";"T";"A"};0); JOUR(DATE(ANNEE(B12);MOIS(B12)+1;0));CHOISIR(1+ENT((MOIS(B12)-1)/3);90+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)));91;92;92);365+ESTNUM(DATEVAL("29/02/"&ANNEE(B12)))))