Aide sur une fonction SI complexe

[ILoveZadig]

Rebonjour à tous,

Je profite (dans le bon sens du terme) de vos connaissances, pour que vous m'aidiez à simplifier cette écriture.

J'ai un tableau (ci-joint) dans lequel un montant HT déductible (car toute la TVA n'est pas récupérée) est à calculer en fonction d'un montant TTC et d'un taux de TVA. Jusque là, cela me donne une fonction SI très lourde (à cause des calculs), mais qui fonctionne.

J'ai donc la fonction SI qui calcule le montant HT déductible en fonction du montant de TVA indiquée dans la liste déroulante dans la colonne précédente, et en fonction d'un prorata de TVA qui va changer chaque année.

Le problème, c'est qu'il faut que j'aille rechercher l'année de la facturation en colonne A (=annee(A41) par exemple) pour que le calcul utilise le taux de prorata correspondant (présent en M1:N4). Ma formule n'est bonne pour l'instant que pour 2009, mais dès que le taux de prorata va changer (ou dès qu'il y aura des dépenses pour 2009 et 2010 ensemble...).

Un exemple de calcul :
1196€ TTC à 19,6% en 2009 ne donne pas 1000€ HT mais 1166,60€ car seul 15% des 196€ est récupérable.​

Le problème étant assez complexe, n'hésitez pas à me dire si vous ne comprenez pas tout, moi même je ne suis pas sûr d'être très clair...

Merci beaucoup, et n'hésitez pas à me demander d'autres explications, le problème étant important pour moi.

 

[James007]

Re : Aide sur une fonction SI complexe

Bonjour,

Donc si j'ai compris ...
A tester ...

A +

 

[ILoveZadig]

Re : Aide sur une fonction SI complexe

Merci tout d'abord pour la simplification de la formule.

Cependant, les 1 166.60€ ne sont valables que pour 2009. Si un opérateur saisi une facture avec pour date 03/01/2010, le taux de prorata est différent (20% dans l'exemple), et on passera donc à 1 156.80€ (avec taux de 20%).

Je voudrai donc que le calcul du montant HT déductible se fasse en fonction de la date de l'année saisie (=annee peut-être) pour aller chercher directement le taux de prorata correspondant dans un tableau du type :

2009 | 15%
2010 | 20% ...

 

[James007]

Re : Aide sur une fonction SI complexe

Est-ce-que cela correspond mieux à ton attente ... ???

A +

 

[ILoveZadig]

Re : Aide sur une fonction SI complexe

J'ai essayé, d'après ton fichier, d'ajouter une colonne (dont j'aurai mis la police en blanc, pour ne pas le voir) avec =annee(A41) où A41 contiendrait la date de facture.

Ensuite, j'ai adapté la formule que j'ai retouchée grâce à ton fichier de tout à l'heure :
Formule initiale :

=SI(F45="Pas de TVA";E45;SI(ESTERREUR(E45-($N$2*(E45-(E45/(1+F45)))));"";
E45-($N$2*(E45-(E45/(1+F45))))))

J'ai modifié car lorsque l'opérateur selectionne "Pas de TVA" dans la liste déroulante, ou lorsqu'aucune valeur n'était renseignée dans la celulle, j'avais le #VALEUR assez génant.

La fonction ESTERREUR a résolu le problème, mais, avec la nouvelle formule :

=SI(F41="Pas de TVA";E41;SI(ESTERREUR(E41-(RECHERCHEV(N41;M2:N4;2)*(E41-(E41/(1+F41)))));"";
E41-(RECHERCHEV(N41;M2:N4;2)*(E41-(E41/(1+F41))))))

Cela ne donne rien. Je pensais pouvoir appliquer le taux en utilisant recherchev comme tu l'as fait en l'intégrant dans la formule, mais cela ne fonctionne pas.

Ma problématique est que je ne peux pas dédier une celulle au taux de prorata, comme dans ton fichier, auquel cas l'opération serait moins lourde.

Merci en tout cas pour ton aide

Edit : je suis revenu à la ligne dans les lignes de code, pour plus de clarté, mais cela tient bien sûr sur une ligne.

 

[James007]

Re : Aide sur une fonction SI complexe

Je ne sais pas si on s'est bien compris ...

Perso, je n'ai fait que décortiquer la fonction SI que tu voulais résoudre ...
je n'ai pas ouvert ton fichier et donc pas chercher à incorporer cette logique dans des formules existantes ...

Si tu as des dificultés à le faire, il faut simplement expliciter à quels endroits de ton fichier cette logique devrait être incluse ...

A +

 

[ILoveZadig]

Re : Aide sur une fonction SI complexe

Je comprends que mon problème paraisse abscons, mais en fait, sur mon fichier, il y a 2 tableaux :

• un suivi des dépenses
• un suivi des recettes

C'est dans ce dernier que l'opérateur doit renseigner en colonne A la date de facturation, avec l'année qui sert de base pour le prorata.

En colonne E je crois(je n'ai plus le fichier sous les yeux et n'y ai plus accès), il doit renseigner

• un montant de facture TTC en colonne E donc
• Dérouler une liste avec les différents taux de TVA en colonne F

Et en colonne G, je voudrai que le montant HT déductible (donc les 1166,60€ pour une facture de 1196€ à 19,6%) se calcule directement, en prenant en compte l'année de la date de facturation, et donc le prorata à appliquer.

En colonne G, le montant sera 1166,60€ si la date est en 2009, et 1 156,80€ si en année 2010.

J'espère être assez clair, car ce n'est pas évident à cerner.

 

[James007]

Re : Aide sur une fonction SI complexe

Re ...Nicolas,

Il me semble que la seule complexité qui reste est celle de la date de référence ....

Quand tu sauras exactement où se trouve la date de référence, tu remplaceras dans ta formule A41 par cette nouvelle adresse ...

Cà devrait jouer ...

 

[mikeo]

Re : Aide sur une fonction SI complexe

Bonjour,

Une solution de la part d'un comptable

A+

 

[James007]

Re : Aide sur une fonction SI complexe

Bonjour,
Une solution de la part d'un comptable
A+

... un fichier joint ... totalement vide ...

 

[mikeo]

Re : Aide sur une fonction SI complexe

Bonjour,

Bonjour James, Comment cela ? j'ai pu l'ouvrir.

attention, il y a des formules sur les cases en jaune :
en B41 :
=SI(A41>0;E41/(1+F41)*(1-(RECHERCHEV(ANNEE(A41);prorata;2;0)));"")

et j'ai nommé "prorata" les cellules M2:N4

Reviens s'il y a problème.

Cordialement.

Mikeo

 

[ILoveZadig]

Re : Aide sur une fonction SI complexe

Re ...Nicolas,

Il me semble que la seule complexité qui reste est celle de la date de référence ....

Quand tu sauras exactement où se trouve la date de référence, tu remplaceras dans ta formule A41 par cette nouvelle adresse ...

Cà devrait jouer ...

Bonjour à tous,

Désolé pour cette réponse tardive, mais j'étais absent.

Je tenais à te remercier James, car la formule correspond parfaitement. Encore merci donc, ton aide sera vraiment utile.


mikeo,
je crois qu'il y avait une erreur dans ta formule, car le montant HT déductible donné via ta formule se trouvait inférieur au montant HT lui même. Merci en tout cas de t'être penché sur le problème.

 

[James007]

Re : Aide sur une fonction SI complexe

Bonjour à tous,

Désolé pour cette réponse tardive, mais j'étais absent.

Je tenais à te remercier James, car la formule correspond parfaitement. Encore merci donc, ton aide sera vraiment utile.

Merci pour tes remerciements ... même tardifs ... c'est très appréciable ...
surtout que .... ILoveZadig 2

A +