XL 2010 calcul automatique et prorata / mois

[kawi99]

Bonjour à tous,

Essayant de m'avancer dans mon travail, je tente (en vain) de finaliser un tableau de calcul automatique (montants liés à des locations).

Je m'explique (c'est compliqué -enfin pour moi... N'hésitez pas à me dire si je ne suis pas clair) :
une personne loue un véhicule.
* Si c'est une première location, le 1er mois il ne paie rien, le mois suivant, il paie le prorata (nombre de jour d'utilisation. 3jours*loyer journalier s'il l'a récupéré le 28/09 par exemple), les mois suivant (jusqu'à restitution), il paie la totalité du loyer.
* Si ce n'est pas la première location, le premier mois, il paie son ancien loyer, le mois suivant il paie un prorata de l'ancien et du nouveau loyer (exemple : restitution/livraison le 05/09 > 5J*ancien loyer journalier + 25J*nouveau loyer/Jour) en M+1 (Octobre). Les mois suivant, la totalité,...

Je vous joins un tableau qui devrait être plus explicite. Les informations recherchées sont entre les colonnes AF et BL (colorié en vert fluo dans le tableau).
AO:AZ : Mois (de changement sympbolisé par une croix "X").
BA:BL : Montants
Ca fait 3h que j'essaie sans succès. Pour faciliter les calculs, j'ai recrée des colonnes AF:AK + AN mais je n'y arrive pas ;(

Pour faciliter ces explications, je vous donne un exemple des résultats attendus :
Monsieur Nicolas VVVV (lignes 25/26) a un 1er véhicule du 26/02 au 16/09 puis un autre du 16/09 jusqu'à....
Les résultats attendus devraient être :
Mars : 2j*(342.90/30) = 22.86€
Avril>Sept : 342.90€
Octobre : 16j*11.43€ + 14j*11,77€ = 347.63€
Novembre et suite : 353.03€

! Attention : 2 particularités :
1* Il y a un mois de décalage en Paie en la livraison/restitution (exemple, un changement en septembre sera pris en compte en octobre)
2* le coût journalier (AF) se calcule en 30e (en nom pas en fonction de jours sur le mois).

Voilà! Merci d'avance de toute l'aide que vous pourrez m'apporter en cette jolie journée car là je n'y arrive pas et je dois donner ce tableau demain à la première heure...

Bien à vous, Kawi

 

[kawi99]

Bonsoir Cisco,

Merci pour ton aide
j'ai difficilement accès a internet depuis qq jours, je regarde ça du travail et te dirai ça demain..

* les formules matricielles ne donnent pas toujours le même résultat que toi

Ah? J'ai peut-être mal rédigé mes formules (ce qui est très probable)

* cela ne fonctionne pas bien si un véhicule est utilisé pendant moins d'un mois, dans le même mois, du 15/01 au 25/01 par ex.

Il est quasi impossible qu'un véhicule soit restitué au bout de si peu de temps. si c'est le cas, je le calculerai mano (pas grave)

16 * 400/30 = 213,33 (puisqu'il y a 31 jours en janvier)
17 * 400/30 = 226,67 (si on prend aussi en compte le 15) ?

Hummm... Disons qu'un véhicule restitué ou 1ere acquisition le 15/01 > Février : 17 jours. (si c’était un "échange", prorata début de mois 14j : 15-1 pour ne pas avoir 32 jours au final). c'est logique ?

Grand merci

K99

 

[kawi99]

Bonjour Cisco,

Je viens de regarder tout ça... certaines sommes semblent se calculer correctement d'autres non..

* En BF5 et BF7, ne devrait-on pas avoir 400€ (mois complet) ?

* En BJ8, selon ce qu'on s'est dit avec changement au 15/06 (mois de juin et montant en juillet) : 14/30*400 + 16/30*600 = 506.67€ (et non 520€) ?

* Idem en BH10 : 14/30*400 + 16/30*500 = 453.34€ (et non 466.67€)?

* Idem BM11 : 553.33€ <> 570€

* Idem BJ6 : (14/30*400 + 16/30*300 = ) 346.67€ <> 360€

Le probleme doit venir de ce flou de jours pris en compte et sur les mois concernés càd juillet concerne juin donc 30jours...
Et il semble plus simple de conserver la même formule que ce soit une première livraison ou un écchange (càd si un véhicule est livré ou échangé un 15/09, ne pas calculer 16j en cas de première livraison (du 15 au 30/09) et 15j en cas d'échange (15j/15j)...

Par ailleurs, j'ai fait des coquilles en AO, BR et BS car je ne trouve pas les résultats attendus :
* AO devrait donner le mois "de changement" càd soit le "nouveau" montant - soit le prorata (1er véhicule ou en cas de changement) soit la régularisation en cas de départ (double montant).
* BR et BS ne devraient afficher qu'un montant que pour les mois qui m'intéressent - donc théoriquement AO en BR et AO+1mois en BS si cela se calculait bien (mois visibles en BR4 / BS4) et non systématiquement tous les montants.

Je vais donc continuer à creuser ça aussi...

Merci beaucoup, K99

 

[CISCO]

Bonsoir

Je regarde étape par étape tes remarques:

Bonjour Cisco,

Je viens de regarder tout ça... certaines sommes semblent se calculer correctement d'autres non..

* En BF5 et BF7, ne devrait-on pas avoir 400€ (mois complet) ?

Oui, mais le mois de février ne comportant que 29 jours, cela donne un peu mois que le loyer mensuel pour 30 jours.

@ plus

 

[CISCO]

Rebonsoir

Bonjour Cisco,
* En BJ8, selon ce qu'on s'est dit avec changement au 15/06 (mois de juin et montant en juillet) : 14/30*400 + 16/30*600 = 506.67€ (et non 520€) ?
* Idem en BH10 : 14/30*400 + 16/30*500 = 453.34€ (et non 466.67€)?
* Idem BM11 : 553.33€ <> 570€
* Idem BJ6 : (14/30*400 + 16/30*300 = ) 346.67€ <> 360€
...
Merci beaucoup, K99

C'était un problème de prise en compte du dernier jour de la période précédente. Les calculs étaient faits avec 15 jours, et pas 14, pour la dernière partie de la ligne précédente. C'est corrigé dans le fichier ci-joint.

@ plus

 

[kawi99]

Bonsoir Cisco,
Merci beaucoup, je regarde ca demain...
Ps : pour moi, un mois, c'est un mois. donc un loyer complet que le mois (entier) fasse 28, 29, 30 ou 31 jours. Non?
Bonne fin de soirée, k99

 

[CISCO]

Bonjour

Bonsoir Cisco,
Merci beaucoup, je regarde ça demain...
Ps : pour moi, un mois, c'est un mois. donc un loyer complet que le mois (entier) fasse 28, 29, 30 ou 31 jours. Non?
Bonne fin de soirée, k99

Oui, effectivement, mais comme il faut faire un calcul au prorata pour les périodes inférieures à un mois, donc en fonction du nombre de jours...
Je change la formule dès que possible pour que cela fonctionne correctement pour le mois de février, à payer fin mars. Et à mon avis, elle sera encore un peu plus compliquée !!!!

@ plus

 

[kawi99]

Bonjour Cisco,

Je change la formule dès que possible pour que cela fonctionne correctement pour le mois de février, à payer fin mars. Et à mon avis, elle sera encore un peu plus compliquée !!!!

AH zut ! :/

Encore merci pour votre aide

bonne journée, K99

 

[CISCO]

Bonsoir

Cf. en pièce jointe la formule modifiée pour résoudre le pb signalé en colonne BF. Est-ce que tu pourrais faire d'autres tests pour voir si cela tourne correctement ?

@ plus

 

[kawi99]

Bonsoir Cisco,
Tout d'abord, toutes mes excuses pour mon manque de News, je croule littéralement sous le boulot!!!
Je te remercie vivement pour tout le mal que tu t'es donné pour m'aider.
Je regarde ca tres vite et te tiens au courant
Encore merci et à bientot
Bonne fin de soirée

 

[kawi99]

Bonjour Cisco,

Me revoilà... Je suis sur ton tableau. J'ai recopié la formule dans mon propre tableau mais au lieu des montants, j'ai des "#NOM?".
La formule étant archi trop complexe pour moi, je ne vois pas d'où viens le problème.
Il semble que ce soit lié à la colonne BC / Colonne à laisser vide (devenue BE dans mon tableau). A quoi correspond-elle ? Y'a tilm qqchose derrière ?
Sinon, je te joins ma véritable version de tableau. Ce sera surement plus simple de travailler directement dessus sachant que je n'ai pas réussi à transposer la formule. A terme, je squeezerai toutes les colonnes parasites inutilisées. On y gagnera en clarté car la multitude de colonnes n'aident pas à la compréhension.
Merci beaucoup, bon week-end.

 

[CISCO]

Bonsoir

Si il ni a pas d'erreur dans la formule, il suffit de définir le nom dernièrelignematr dans le gestionnaire de noms.
Pour cela tu sélectionnes BF5, tu ouvres le Gestionnaire de noms dans l'onglet haut Formules. Dans la fenêtre qui s'ouvre, tu cliques sur Nouveau, tu écris le nom dernièrelignematr , puis tu écris en bas la formule =MAX(SI('SUIVI VS'!$B$4:$B5='SUIVI VS'!$B5;LIGNE('SUIVI VS'!$4:5))) en dessous de Fait référence à.

@ plus

 

[CISCO]

Rebonsoir

Que veux-tu avoir dans la colonne BS ? Pour le moment, elle ne donne que des #VALEUR!

@ plus

 

[CISCO]

Bonsoir

Si tu classes systématiquement les données en regroupant les matricules en colonne B, comme tu l'a fais dans les fichiers exemples, tu peux faire sans le nom dernièrelignematr. Cela donne une formule un tout petit peu plus simple. Cf. en pièce jointe.

@ plus

P.S : La formule avec le nom dernièrelignematr permet de travailler avec des matricules dans la colonne B non triés.

 

[CISCO]

Rebonsoir

N'oublies pas que les formules proposées précédemment ne fonctionnent pas nickel dans tous les cas, par exemple pour des périodes non à cheval sur au moins deux mois, et que le cas de changement d'année n'a pas été envisagé (Rajoutes janvier, février... 2017, puis mets des dates en colonne K en 2017. Qu'est-ce que cela donne ?)

@ plus

 

[kawi99]

Si il ni a pas d'erreur dans la formule, il suffit de définir le nom dernièrelignematr dans le gestionnaire de noms.
Pour cela tu sélectionnes BF5, tu ouvres le Gestionnaire de noms dans l'onglet haut Formules. Dans la fenêtre qui s'ouvre, tu cliques sur Nouveau, tu écris le nom dernièrelignematr , puis tu écris en bas la formule =MAX(SI('SUIVI VS'!$B$4:$B5='SUIVI VS'!$B5;LIGNE('SUIVI VS'!$4:5))) en dessous de Fait référence à.

Bonjour,
Merci pour ces précisions. j'essaie de transposer ça demain directement sur mon tableau final car à force de faire des copier/coller, je m'embrouille!
Je te dirai si j'ai réussi.
Merci et bonne fin de week-end
@+