Avoir la plus elevée de 2 valeurs, pour une ID+date unique
Bonjour,
je travaille dans l'automobile et sa fameuse taxe sur les véhicules de société.
Cette taxe se calcule trimestre par trimestre, sur le premier trimestre, le deuxième, le troisième...
Il y a une date de début d'usage et une date de fin d'usage. Entre ces deux dates le tableau suivant donne le nombre de trimestre
De 0 à 30 jours : 0 trimestre taxé
De 31 à 90 : 1 trimestre taxé
De 91 à 180 : 2 trimestres taxés
De 181 à 270 jours : 3 trimestres
De 271 à 365 jours : 4 trimestres
J'arrive à calculer le nombre de trimestre avec des SI imbriqués, ça pas de problèmes. Première question : comment, à partir de la date de début d'usage de la voiture et la date de fin d'usage de la voiture, trouver sur quel trimestre on est taxé ?
Exemple : J'utilise une voiture à partir du 01/01 jusqu'au 15 février, soit 45 jours --> 1 trimestre , c'est à dire le T1 (Janv Fev Mars). Je voudrais avoir le T1 trouvé avec des formules...
Deuxième question , bien plus perverse, lors du remplacement d'une voiture par une autre au cours d'un trimestre. Si , au 15 février, je change cette voiture (mettons une CLio) par une 208 que j'utilise du 15 février au 29 mars, j'aurais 1 trimestre de taxe pour la clio et un pour la 208, Le tout sur premier trimestre
Dans ce cas là j'ai le droit de ne garder que la voiture la plus chère des deux, et de ne payer que un trimestre de taxe, au lieu de 2.
Il faut donc que je fasse, à l'intérieur de mon tableau, un "scan" par conducteur identifié par une ID unique et aussi un scan de date, pour savoir si le remplacement a eu lieu au cours d'un même trimestre. Car, si je reprends l'exemple d'avant, si je rends la clio au 15 février mais que je reprends une 208 seulement en novembre, je serai taxé pour la clio au T1 et sur la 208 pour le T4 de l'année.
Bien entendu je peux avoir des multiples remplacements au cours d'un trimestre . Comment arriver, par des formules, à ramener la valeur de "1 trimestre" à "0" en cas de changement d'une voiture par une autre ??
J'ai tout mis dans un fichier... C'est un défi irréaliste ou c'est faisable ? Aucune idée
Si vous y arrivez je vous dois une reconnaissance éternelle !! Merci
Loulou95 a écrit :J'arrive à calculer le nombre de trimestre avec des SI imbriqués, ça pas de problèmes. Première question : comment, à partir de la date de début d'usage de la voiture et la date de fin d'usage de la voiture, trouver sur quel trimestre on est taxé ?
Exemple : J'utilise une voiture à partir du 01/01 jusqu'au 15 février, soit 45 jours --> 1 trimestre , c'est à dire le T1 (Janv Fev Mars). Je voudrais avoir le T1 trouvé avec des formules...
Voici pour les formules du trimestre
Pour la deuxième question, va falloir encore réfléchir !
Bonjour et un immense merci pour cette solution !
J'ai pensé que l'on pourrait peut être fabriquer un truc avec des SI et une identification des doublons
Pour identifier un doublon "=SI(NB.SI($D$3:D3;D3)>1;"Doublon";"")"
D'abord identifier que le conducteur soit bien lui même en doublon en créant une colonne "Conducteur en doublon )
Puis, plus loin, créer 4 colonnes, une par trimestre : T1 (oui ou non), T2 (oui ou non) T3 (oui ou non), T4, selon que la durée qualifie pour T1, T2, T3, T4
Un peu plus loin 4 autres colonnes, une par trimestre en doublon, sous condition que le conducteur soit lui même en doublon
Trim 1 en double (oui ou non), Trim 2 en double (oui ou non )
Et encore un peu plus 4 dernières colonnes, une par trimestre corrigé
Prendre la valeur la plus faible des deux sur Trim 1 corrigé (si val plus faible alors = 0), Trim 2 corrigé, etc
Ca fait bcp de colonnes, mais je peux sacrifier l'élégance à l'efficacité ?
je me demande juste si ça va marcher ?
Un essai qui est un vrai brouillon ...
j'ai un petit problème en J12 car il faudra sans doute refaire la formule des trimestres pour bien séparer les 4 Trimestres
autre problème cela ne marche plus si le conducteur change 2 fois ou plus de voiture à l’intérieur du même trimestre mais ce n'est en pratique quasiment jamais le cas, et si ça arrive ça prend de toutes les façons un montant de taxe...
J'ai essayé d'identifier si le conducteur était en double
Puis si le conducteur est en double et que le trimestre est lui aussi en double, on compare pour ce trimestre le montant avec le montant de la ligne du dessus, si c'est supérieur on garde ce montant, si non on met 0
Un peu plus loin on compare avec la ligne du dessous, si dans la ligne du dessous il y a 0 mettre la valeur courante (qui est donc la plus élevée des 2 valeurs)
Consolider le résultat final en faisant la somme des deux
Si pas de doublon prendre la valeur courante
Refaire la recette pour chacun des 4 Trimestres, sachant que le cout unitaire du trimestre est toujours le même.
La j'ai fait que le premier trimestre.
ET avec un TCD ?
Merci c'est vraiment génial !
On est super proche du but avec cette manière de faire
Il reste un seul dernier petit souci avec la définition d'un trimestre.
Avec la formule d'aujourd'hui pour les 4 trimestres, il suffit qu'il y ait un jour dans un trimestre pour générer un montant de taxe. Ce qui est très proche du barême officiel.
Or, c'est un tout petit peu différent en cas de location à cheval sur plusieurs trimestres, voir ici
Je donne un exemple avec pour le conducteur "Toulouse", date de début d'usage 5 janvier et fin 7 octobre
Il ne devrait pas y avoir que 4 trimestres car la durée totale consécutive d'usage est inférieure à 270
Pareil pour 180 jours et 90 jours, pour 2 et 1 trimestre respectivement.
Par contre je pense qu'on peut négliger le cas de 0 à 30 jours d'usage qui ne doit pas générer de trimestre (par exemple une location qui comme le 1er janvier et qui finit le 5 janvier : dans ta formule ça fait 1 trimestre, alors qu'en théorie ça ne devrait pas en faire, et donc pas de taxe à payer)
Pour deux raisons : d'abord mes locations sont des locations de longue durée (renouvellement tous les 8 mois). Ensuite parce qu'il faudrait comprendre si le remplacement d'une voiture par une autre se fait consécutivement ou non. Par exemple, si je prends une clio du 1er janvier au 15 janvier, puis une Porsche du 15 janvier au 5 février, j'ai pour chaque voiture prise indépendamment moins de 30 jours. Mais un total consécutif de 35 jours qui doit me fabriquer un trimestre : celui de la Porsche (car le plus cher).
A l'inverse, comme le dit l'exemple du site Service Public, si je prends une clio du 1er au 15 janvier puis une Porsche du 1er au 15 février, là ça ne devrait pas faire de trimestre du tout car pas consécutif. Ce cas n'est pas imposable.
Pour simplifier, je propose de ne pas prendre en compte ce cas, je ne sais pas ce que ça donnerait avec Excel. Tant pis si une ou deux voitures se retrouvent dans le cas.... (j'ai en gros 200 voitures)
Loulou95 a écrit :Par contre je pense qu'on peut négliger le cas de 0 à 30 jours d'usage qui ne doit pas générer de trimestre (par exemple une location qui comme le 1er janvier et qui finit le 5 janvier : dans ta formule ça fait 1 trimestre, alors qu'en théorie ça ne devrait pas en faire, et donc pas de taxe à payer)
Pour ce cas là :
en I4, à recopier sur autres cellules :
=SI(ET($E4<=I$2;$F4>=I$1;$F4-$E4>30);$H4;0)mais on ne prend pas en compte dans ce cas le changement de véhicule dans la période des 30j !
Loulou95 a écrit :Or, c'est un tout petit peu différent en cas de location à cheval sur plusieurs trimestres, voir ici
Je donne un exemple avec pour le conducteur "Toulouse", date de début d'usage 5 janvier et fin 7 octobre
Il ne devrait pas y avoir que 4 trimestres car la durée totale consécutive d'usage est inférieure à 270
Pareil pour 180 jours et 90 jours, pour 2 et 1 trimestre respectivement.
Ce srait assez facile s'il n'y avait pas le changement de véhicule en cours de contrat !
Je vais y réfléchir, il faut peut-être changer la façon de calculer/d'organiser les données.
[quote=" Steve Jobs, BusinessWeek, 1998"]"C'est l'un de mes mantras - concentration et simplicité. Faire simple peut être plus difficile que de faire compliqué. Il faut travailler dur pour mettre ses idées au clair et faire simple. Mais ça vaut le coup en fin de compte parce que lorsque vous y parvenez, vous pouvez déplacer des montagnes."[/quote]
Steve Jobs n'est pas ma référence, mais on peut appliquer cette pensée ici.
Alors j'ai essayé de traduire simplement ces élucubrations administratives ...
en I2, à étendre :
=SI(ET($E4<=I$2;$F4>=I$1;$F4-$E4>30;I$2-$E4>30);$H4;0)Regarde si cela couvre tous les cas (en dehors du changement de véhicule à mi-parcours) !
Je ne pourrais jamais te dire assez merci !
Je regarde ce soir quand je rentre à la maison
Ensuite parce qu'il faudrait comprendre si le remplacement d'une voiture par une autre se fait consécutivement ou non. Par exemple, si je prends une clio du 1er janvier au 15 janvier, puis une Porsche du 15 janvier au 5 février, j'ai pour chaque voiture prise indépendamment moins de 30 jours. Mais un total consécutif de 35 jours qui doit me fabriquer un trimestre : celui de la Porsche (car le plus cher).
La je crois que je n'ai pas bien compris le texte de loi...
Je pense que c'est plutôt 30 jours consécutifs par voiture. Donc dans cet exemple, ça fait bien 0 trimestre, rien pour la clio, rien pour la Porsche, nada.
De ce point de vue là,
=SI(ET($E4<=I$2;$F4>=I$1;$F4-$E4>30;I$2-$E4>30);$H4;0)Devrait permettre d'éliminer toutes les périodes de moins de 30 jours (à moi de toutes les tester, j'ai regardé et ça a l'air pas mal).
Merci pour la proposition
Mais il reste un problème, c'est les chevauchements de trimestre.
Je donne un exemple avec pour le conducteur "Toulouse", date de début d'usage 5 janvier et fin 7 octobre
Il ne devrait pas y avoir que 4 trimestres car la durée totale consécutive d'usage est inférieure à 270
Pareil pour 180 jours et 90 jours, pour 2 et 1 trimestre respectivement.
la formule donne le nombre de trimestre à retenir, qui servirait de base à la vérification, avec G4 la durée d'usage
=SI(G4<=30;0;SI(G4<=90;1;SI(G4<=180;2;SI(G4<=270;3;4))))Selon le nombre de trimestres calculés par cette formule et en comparant avec NB.SI qui compterait les trimestres non nuls (T1,T2,T3,T4) du tableau, on devrait pouvoir annuler les trimestres "en trop" si le nombre de vérification est inférieur.
Mais comment décider du trimestre qui doit s'annuler ? Par exemple le dernier en date, le plus vieux autrement dit ?
Ca peut être le T2 en cas de chevauchement T1-T2, le troisième en cas de chevauchement T2-T3, et le 4ème en cas de chevauchement T3-T4.
Ensuite, on a les chevauchement de 3 trimestres avec 2 Possibilités, T1-T2-T3 et T2-T3-T4
Et enfin le chevauchement unique de 4 Trimestres, là c'est facile c'est le dernier qu'il faut annuler.
Je vais affiner, il reste encore quelques petits réglages à faire ...
je pense toucher au but ...
Salut,
Merci encore +++ pour ton aide ;
Je ne connais pas bien les formules que tu emploies et mes tests on montré quelques erreurs. Aussi, sans faire offense à ta proposition dont je te remercie encore, je suis plutôt parti sur un enchainement de SI / ELSE
Donc, le T1 ne peut se chevaucher qu'avec lui même. Facile.
Si les dates sont bien dans le T1 et que la durée d'usage en G4 est sup à 30jours, alors mettre le montant de taxe en H4
Une petite subtilité en cas de début d'usage avant le début d'année, on compte à partir du 1er janvier 2015 et pas à partir de la date de début. Par exemple une voiture qui commence le 30/06/2014 et s'arrête le 3 janvier 2015. Je ne compte pas à partir du 30/06/2014 mais bien à partir du 1er janvier, ça me fait 3 jours, donc inférieur à 30, et pas de trimestre.
le T2 peut se chevaucher avec le T1. Il va falloir rajouter des SI...
Je commence par vérifier que les dates sont dans le T2. Puis si le T1 a donné lieu à un trimestre (si la Taxe du T1 est > à 0), et que la durée d'usage est inférieur à 90, alors je suis dans un cas de chevauchement et je mets 0
Si le T1 a donné lieu à un trimestre mais que la durée est supérieure à 90, alors mettre de la taxe en T2
Et enfin, en cas de début d'usage pendant le T2 (dans ce cas la taxe du T1=0), vérifier que la durée est supérieure à 30 jours et si oui, mettre une taxe en T2.
Alors le T3.... Il peut se chevaucher avec le T1, et le T2 : on aurait T1-T2-T3. Et il peut se chevaucher avec le T2 seulement, ça donnerait T2--T3.
En gros je reprends le même raisonnement que pour le T2, mais je rajoute des branches. Si j'ai T1>0 et T2>0 et T3>0
mais que la durée totale est inférieure à 180, j'élimine le dernier trimestre
Et ainsi de suite, si j'ai T1=0, mais de la taxe en T2 (T2>0), avec une durée inférieure à 90, pas de taxe..
Et on termine avec le cas T1=0, T2=0, T3=0, + durée supérieure à 30 jours qui donnerait un trim isolé.
Le T4 est le plus long. On fait attention à arrêter le compteur au 31/12/2015 et pas au-delà.
Je pense ne rien avoir oublié !!
Encore merci pour m'avoir consacré tant de temps.
Je me demandais juste si dans la présentation du TCD on pouvait mettre la date de début, de fin, le modèle de voiture, mais sans avoir toutes ces lignes "total" qui gâchent la présentation. Seulement le total par conducteur en bas