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]

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

Théoriquement :
* la colonne BS me donne le cumul des loyers sur l'année, par collaborateur, uniquement sur chaque dernière ligne
* la colonne BT, le prorata ou montant du mois 1er mois
* la colonne BU, le montant les mois suivants

Merci

 

[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 Cisco
Superbe explication, ça semble avoir fonctionné du premier coup ! Merci
en revanche, j'ai #NOMBRE! qui s'affiche pour les loyers en cours (sans date de fin) et dans les lignes (en préparation) sans date de début ni de fin.
Cdlt, K99

 

[CISCO]

Bonjour

Pour ce qui est du premier pb, à savoir les #NOMBRE sur les lignes sans date de fin.

Bonjour Cisco
Superbe explication, ça semble avoir fonctionné du premier coup ! Merci...
Cdlt, K99

Pas exactement, car j'ai fait une erreur en mettant la formule définissant dernièrelignematr sur le forum, un $ mal placé. Il faut écrire

=MAX(SI('SUIVI VS'!$B4:$B$5='SUIVI VS'!$B5;LIGNE('SUIVI VS'!4:$5)))

avec le $ devant le 5 et pas devant le 4, au début et à la fin
ou plus simplement

=MAX(SI('SUIVI VS'!$B$4:$B4='SUIVI VS'!$B5;LIGNE('SUIVI VS'!$4:4)))

dans les deux cas en ayant sélectionné BF5 avant d'ouvrir le gestionnaire.

Pour ce qui est du second pb, pour les lignes en préparation, ce cas n'ayant pas été envisagé, c'est assez normal qu'il y ait un bug. Pour remédier à cela, toujours dans BF5, il faut rajouter devant la formule un SI($J6="";""; , et rajouter tout à la fin un ). Il faut ensuite tirer cette formule vers la droite et vers le bas.

@ plus

P.S : Je ne te promet rien, mais j'essaye de trouver une formule :
* plus simple
* et/ou permettant l'utilisation de période pas forcément à cheval sur au moins 2 mois
* et/ou ne posant pas de problème lors du passage à l'année suivante, en janvier ou février.

Conclusion : Jette un oeil de temps en temps sur le forum pour voir où j'en suis.

 

[CISCO]

Bonsoir

J'espère que tu as corrigé la formule définissant dernièrelignematr, et que cela fonctionne.

En pièce jointe, une solution utilisant une méthode complètement différente, et acceptant des périodes sur une partie d'un mois.

@ plus

P.S : Les petites périodes incluses dans le mois de février ne sont pas calculées avec la formule durée/30*loyer mensuel, mais avec durée/nbre de jours du mois de février * loyer mensuel. Si cela pose problème, me le dire.

P.S : 24/11/2016. Le fichier sommeprod3 ne contient que le tableau final. La formule utilisée est détaillée, expliquée dans le fichier sommeprod_explications. Cette formule parait compliquée, mais il faut bien comprendre qu'elle est constituée de trois parties presqu'identiques que l'on peut très bien construire en faisant du copier-coller, avec quelques modifications et beaucoup de précautions.

 

[kawi99]

* et/ou ne posant pas de problème lors du passage à l'année suivante, en janvier ou février

Bonjour Cisco,
Toujours et encore, un énorme merci pour ton aide !
J'ai tout repris ce matin, j'ai le cerveau qui fume...

Franchement, je suis vraiment pour une formule plus simple mais je sais encore moins comment la faire.

Concernant le passage N-1 à N et donc les loyers de janvier à février qui peuvent être vides car ne puisent pas les infos dans la même colonne (BF9 par exemple, qui ne trouve aucune valeur car il cherche en AB alors qu'il devrait chercher en Z).

Dans une version précédente, j'avais essayé de créer un onglet supplémentaire qui irait puiser via RechercheV l'année concernée et le montant correspondant (il y en a un "reste" en colonne AI et j'ai fait un basique copier/coller en AJ pour montrer ce que ça donnait).
ça alourdissait encore le tableau mais aurait permis de faire qqchose comme, par exemple, en w5 > =si(annee(AI)<>annee(J5);0;formule).
Comme ça, pas de valeurs superflues liées aux lignes dupliquées comme pour la ligne13 (par exemple) car bien que nous soyons sur une livraison 2016 (et donc valeurs actuelles AA:AB), j'ai néanmoins des montants de W à Z, ce qui est inutile voire incohérent (je verrai ça avec mon collègue).

Ces données seraient reprises en AC:AD (j'ai mis des exemples en AC5:AD14 des résultats attendus).
Et peut-être, qu'à terme, il sera possible de virer W:AB pour simplifier.
Le mois de janvier ne poserait plus souci dans ce cas, non ?

Je te joins le tableau en question en PJ.

Je verrai ensuite pour :
BR = Supprimer cette colonne si elle ne sert à rien (ainsi que toutes les autres qui parasitent le tableau)
BS = somme des montants sur l'année concernée (en D1)
BT = Nouveau loyer sur le mois (prorata)
BU = Montant loyer plein à partir du mois suivant

Merci, @+, K99

 

[CISCO]

Bonjour

Je regarde ton nouveau fichier dès que possible.

@ plus

 

[CISCO]

Bonsoir

Je n'avais pas du tout regardé les colonnes Z à BD de ton fichier, donc, ce n'est pas évident pour moi de tout comprendre.

Si j'ai bien compris, le loyer mensuel dépend, entre autre, de l'année, et la période de location sur une ligne peut très bien couvrir plusieurs années. Donc, en ligne 5, par ex dans BF5 (janvier 2016), la formule devrait faire appel au loyer donné pour décembre 2015, dans BS5 (janvier 2017), elle devrait faire appel au loyer mensuel de décembre 2016, et ainsi de suite.

Pour résoudre ce problème, on peut très bien mettre directement dans la formule, à partir de BF5, la relation permettant de calculer le loyer mensuel correspondant à la date donnée sur la ligne 4 dans la colonne en cours, et non la bonne valeur prise dans la colonne Z, ou Ab, ou AD. J'essaye de te faire un exemple.

@ plus

 

[CISCO]

Rebonsoir

Dans W5, la formule donnant le loyer annuel pour 2014 est (((N5*0,8)*0,2)+1653+645+6040+O5+Q5)*E5.
Dans Y5, pour 2015, c'est (((N5*0,8)*0,2+1017+760+6084+O5+Q5)*E5.
Dans AC5, pour 2016, c'est (((N5*0,8)*0,2)+1653+645+6040+O5+Q5)*E5.

Pourrais tu me dire d'où viennent les valeurs suivantes :
1653, 645 et 6040
1017, 760 et 6084
1653, 645 et 6040 ?

@ plus

 

[kawi99]

Je regarde ton nouveau fichier dès que possible.

Merci bcp Cisco !

 

[kawi99]

Pourrais tu me dire d'où viennent les valeurs suivantes :
1653, 645 et 6040
1017, 760 et 6084
1653, 645 et 6040 ?

Bonsoir,
Ces valeurs changent chaque année. Ce sont des montants a rajouter au prix de base pour calculer les loyers

 

[kawi99]

Si j'ai bien compris, le loyer mensuel dépend, entre autre, de l'année, et la période de location sur une ligne peut très bien couvrir plusieurs années. Donc, en ligne 5, par ex dans BF5 (janvier 2016), la formule devrait faire appel au loyer donné pour décembre 2015, dans BS5 (janvier 2017), elle devrait faire appel au loyer mensuel de décembre 2016, et ainsi de suite.

Oui. En moyenne, un véhicule se conserve 9 mois mais peut être a cheval sur 2 années. dans ce cas, il faut conserver le calcul de base càd qu'un véhicule a cheval sur 2015 et 2016 conserve le mode calcul de 2015 jusqu'à la livraison du véhicule suivant en 2016 qui "utilisera" les nouveaux montant 2016.
Ce qui se traduit par une nouvelle ligne > Qui prend systématiquement en compte (normalement) le calcul de l'année en cours (2014 ou 2015 ou 2016,...).

 

[CISCO]

Bonsoir

Bonsoir,
Ces valeurs changent chaque année. Ce sont des montants a rajouter au prix de base pour calculer les loyers

S'il ni a pas de relation générale f(année), il faudra effectivement faire avec des colonnes intermédiaires (W à AD dans ton dernier fichier) et un RECHERCHEV ou un INDEX(....;EQUIV(...)) ou un DECALER(...) ou...

@ plus

 

[CISCO]

Bonsoir

Oui. En moyenne, un véhicule se conserve 9 mois mais peut être a cheval sur 2 années. dans ce cas, il faut conserver le calcul de base càd qu'un véhicule a cheval sur 2015 et 2016 conserve le mode calcul de 2015 jusqu'à la livraison du véhicule suivant en 2016 qui "utilisera" les nouveaux montant 2016.
Ce qui se traduit par une nouvelle ligne > Qui prend systématiquement en compte (normalement) le calcul de l'année en cours (2014 ou 2015 ou 2016,...).

Est-ce à dire que pour une personne louant un véhicule par ex du 1/10/2015 au 1/3/2016, puis un autre véhicule à partir de 1/3/2016, on utilisera pour le premier véhicule le loyer mensuel de 2015 (même pour janvier et février 2016) ? Et qu'on n'utilisera le loyer mensuel de 2016 qu'à partir de mars 2016 ?

@ plus

 

[kawi99]

Ok, j'essaie de faire qqchose dans ce sens et je renverrais un fichier
En revanche, je ne sais pas utiliser index, equiv et décaler
Merci et bon week-end.

 

[CISCO]

Bonjour

En pièce jointe, deux possibilités pour prendre en compte des loyers mensuels évoluant en fonction de l'année.

J'ai changé le contenu des cellules X4, Z4 et AB4, cellules auxquelles j'ai attribué un format personnalisé "MENSUEL "0.
J'ai aussi changé le contenu de BE4.

La version 1 travaille toujours avec dernièrelignematr. Si tu tries toujours les lignes par matricule, tu peux faire avec la version 2 qui n'utilise plus ce nom. Dans les deux cas, j'ai défini un nom loyermensuel. Cf. sa définition dans le gestionnaire de noms.

Dis moi si les valeurs obtenues sont OK.

Dans ces conditions, je ne vois pas trop l'intérêt des colonnes AC et AD.

Si tu dois rajouter une année 2017, je ne sais trop comment la définition de loyermensuel évoluera automatiquement (comment elle prendra en compte les nouvelles colonnes donnant le loyer annuel et le loyer mensuel correspondant). A voir donc.

Il y a un problème en ligne 191 car il manque au moins une valeur en ligne 190 (date de livraison ?). A voir aussi.

@ plus