Problème formule "SI"

[Crumpy]

Bonjour tout le monde,
j'ai un problème que je n'arrive vraiment pas à résoudre avec une formule que j'ai faite pour calculer la ventilation de la TVA sur mes formules (de produits) comprenant des produits à des taux différents.

Voici la formule, elle ne marche pas :
SI(B13="";0;RECHERCHEV(B13;B44: D106;3)*0,055)+SI(E13="";SI(D13="";0;((H13-G13)*(RECHERCHEV(D13;$B$44:$C$83;2)/(RECHERCHEV(D13;$B$44:$C$83;2)+RECHERCHEV(C13;$B$44:$C$83;2))))*0,055;((H13-G13)*((RECHERCHEV(D13;$B$44:$C$83;2)+RECHERCHEV(E13;$B$44:$C$83;2))/(RECHERCHEV(D13;$B$44:$C$83;2)+RECHERCHEV(E13;$B$44:$C$83;2)+RECHERCHEV(C13;$B$44:$C$83;2))))*0,055))

(J'ai fait un espace volontaire avant le "D" parce que ça affichait un smiley 🙁)
Je suppose qu'il y a une règle qui m’échappe...

Merci à ceux qui voudront bien m'éclairer.

PS : Sinon pour info, H13=20.90, G13=rien et les recherchev de B13=rien, C13=5.99, D13=0.99, E13=0.99, H13=20.90, G13=rien.

 

[C@thy]

Re : Problème formule "SI"

Bonjour Crumpy et bienvenue sur XLD,

je pense que tu as dû lire la chartre : si tu veux que l'on puisse te répondre efficacement, il serait bon de joindre un fichier.

Il est bon aussi d'être clair et précis. Peux-tu nous expliquer ce qui ne marche pas? (message d'erreur? si oui lequel? ou bien ne fonctionne pas comme tu voudrais?).
Edit : je pense qu'au lieu de SI(E13="";SI(D13="";0; tu devrais écrire si(et(E13="";D13="");0)

@ te lire,

C@thy

 

[Crumpy]

Re : Problème formule "SI"

Merci.

Le mess d'erreur c'est "Err : 504".
Ce qui est étrange c'est que mon ancienne formule fonctionnait mais j'ai voulu la modifier après avoir constaté une erreur de résultat. (Dans la case K13 vous pouvez voir l'ancienne formule pour le taux à 19.6%).
Je viens de comprendre que la première partie de ma formule ne donnerait pas le résultat correcte donc on ne tient plus compte qu'à partir du deuxième "si" après le "+". (Ce qui nous donne : SI(B14="";0;RECHERCHEV(B14;B45😀107;3)*0,055)+SI(E14="";SI(D14="";0;((H14-G14)*(RECHERCHEV(D14;$B$44:$C$83;2)/((RECHERCHEV(D14;$B$44:$C$83;2)+RECHERCHEV(C14;$B$44:$C$83;2)))))*0,055);((H14-G14)*((RECHERCHEV(D14;$B$44:$C$83;2)+RECHERCHEV(E14;$B$44:$C$83;2))/(RECHERCHEV(D14;$B$44:$C$83;2)+RECHERCHEV(E14;$B$44:$C$83;2)+RECHERCHEV(C14;$B$44:$C$83;2))))*0,055) )

Je vous joint la feuille...

Ce que je veux c'est ventiler la TVA par rapport aux prix final (c'est à dire H-G) . On peut vendre le produit à 19.6% (colonne C) seul ou en formule accompagné d'un produit à 5,5% (colonne D) ou deux (Colonne E).
Si on vend un produit à 5.5% non compris dans la formule ou en plus on le met dans la colonne B mais dans une autre ligne du coup.

Avec ma formule je cherche en gros dans un premier temps, à essayer de faire : si il n'y à pas de 2ieme produit (en "E") à 5.5% dans la formule, alors si il n'y a pas non plus de premier produit à 5.5% (en "D") alors 0, sinon (donc si il y a qu'un seul produit à 5.5% en "D"), on fait le prix total (H-G) fois (le produit à 5.5% ("D") divisé par (le produit à 19.6% ("C") + le produit à 5,5% ("D))) et on multiplie le résultat obtenu par le taux de TVA soit 0.055.
Ensuite si il y a deux produits ("D" et "E") la même chose en additionnant l'autre avec le premier...

Quand je le fais moi même le calcul pour l'exemple qu'il y a sur la feuille jointe, je trouve 3,44€ pour le taux à 19.6% (donc qui devrait être en "K13") et 0.16 pour la TVA à 5.5% (qui devrait être en "I13")

Voilà j'ai essayé d'être le plus claire possible mais c'est pas facile...

Merci pour votre aide en tout cas...

 

[Tibo]

Re : Problème formule "SI"

Bonjour,

Un début de solution (après avoir passé un bout de temps à traduire ton discours) :

en I13 :

=$H13/SOMMEPROD((ESTNUM(EQUIV($B$44:$B$106;$C13:$F13;0))*$C$44:$C$106))*RECHERCHEV($D13;$B$44:$C$106;2;0)*0.055

A recopier vers la droite en adaptant le taux de TVA et le $D13 en $E13 et $F13 dans le RECHERCHEV

C'est un début, il reste à ajouter quelques tests pour gérer les cellules vides. A gérer également les deux colonnes à 5.5

Je te laisse tester

@+

 

[Crumpy]

Re : Problème formule "SI"

Désolé je ne m'en sort pas ^^
C'est très gentil à vous de me venir en aide et ça m'embarrasse un peu de ne pas comprendre ce que vous avez essayé de me conseiller de faire. Il y a des formules dans ce que vous m'avez donné que je ne connais pas donc ce n'est pas très simple pour moi.
Sinon la colonne "F" n'a pas besoin d'être inclue dans la formule, parce que je n'ai pas de lot de produits comprenant des produits à 7% (il sont en supplément donc pas de ventilation).
Sinon vous voyez ce que c'est la "ventilation de la TVA" ?

J'espère que vous pardonnerez ma nullité. Suite à ce poste je rétrograde mon niveau de maîtrise d'excel indiqué sur ce forum ^^.

Quoi qu'il en soit, je me reconnecterai en fin d'après-midi pour expliquer ce que je cherche à faire correctement.

Merci beaucoup en tout cas !

 

[Tibo]

Re : Problème formule "SI"

re,

N'hésite pas à revenir avec un fichier contenant plusieurs lignes d'exemples et mets-nous manuellement les résultats atetndus.

Connaissant les résultats, il sera certainement plus facile pour nous de comprendre.

@+

 

[Crumpy]

Re : Problème formule "SI"

Re, merci c'est ce que je vais faire.

Voilà l'explication de la méthode de ventilation que j'utilise. Avec les calculs :

Je compte répartir le taux de tva proportionnellement au prix d'achat des marchandises contenues dans le lot.
Par exemple : Nous achetons un produit A 5.99€ (taux tva 19.6%) et un produit B 1.01€ (taux 5.5%) que nous vendons respectivement à 18.5€ et 2€ à l'unité hors promotions. Le lot serait à 19,5€. Nous allons donc appliquer les formules suivantes :
Pour le calcul de la TVA à 19.6% : ((5.99/(5.99+1.01))*19.5)*0.196. Et pour le taux à 5.5% donc : ((1.01/(5.99+1.01))*19.5)*0.055
Si le lot contient un produit à 5.5% en plus (donc 1 produit à 19.5% et 2 à 5.5%), il sera vendu à 20.5€ et ça donnera :
Pour le calcul de la TVA à 19.6% : ((5.99/(5.99+1.01+1.01))*20.5)*0.196.
Et pour le taux à 5.5% donc : (((1.01+1.01)/(5.99+1.01+1.01))*20.5)*0.055.

Avec la feuille que je vous joins, je voudrais que les taux se calculent automatiquement sur la formule, en colonne "I" pour le taux à 5.5% et en colonne "K" pour le taux à 19.6%.

La fonction recherchev sert à, en fonction de la référence donné pour le produit à 19.6 en colonne "C" et la ou les références données, en colonne "D" ou en colonnes "D" et "E", leur prix d'achat.
Pour facilité les chose on peut enlever cette recherchev dans la formule et remplacer les références produits par leur prix directement. Mais si vous me donnez une formule toute faite, incluez la rechercheV svp parce que moi je suis perdu après^^.

Dans l'exemple de la feuille que je vous joint, la ligne 13 correspond à une formule avec 2 produits à 5.5% (ref données en "D13" et "E13" donc, avec bien évidemment la ref du produit à 19.6% donnée en "C13").
La ligne 14 corresponds à une formule avec 1 seul produit à 5.5% (ref donnée en "D14" donc).
La ligne 15 corresponds à la vente d'un unique produit à 19.6% (ref donné en "C15"), donc il n'y a pas de lot pour celle là, donc pas de ventilation, donc dans la colonne "I" pour le calcul de la TVA à 5.5% doit figurer "0" et en "K" la TVA à 19.6%.

Si je fais le calcul moi-même, j'obtiens en "I13" : 0.28 et en "K13" : 3.00.
En "I14" : 0.16 et en "K14" : 3.44.
En "I15" : 0.00 et en "K15" : 3.63.

J'espère que ce coup çi j'ai été clair 🙂

Merci à vous tous.

 

[Crumpy]

Re : Problème formule "SI"

Un moyen plus simple d'expliquer ce que je souhaite vient de me venir à l'esprit.

Ce que je veux c'est que "I13" applique ces formules:
SI il ne figure qu'une référence en "C13" alors : 0
Si il ne figure qu'une référence en "C13" et en "D13" alors : ((D13/(C13+D13))*H13)*0.055
Si il figure une référence en "C13","D13" et "E13" alors : (((D13+E13)/(C13+D13+E13))*H13)*0.055

Et en "K13" que s'applique la formule :
SI il ne figure qu'une référence en "C13" alors : C13*0.196
Si il ne figure qu'une référence en "C13" et en "D13" alors : ((C13/(C13+D13))*H13)*0.196
Si il figure une référence en "C13","D13" et "E13" alors : (((C13)/(C13+D13+E13))*H13)*0.196

dans tous les cas, les cases "B13" et "F13" ne sont pas à intégrer dans les formules (enfin "B13" si mais je saurai le faire).

Voilà 🙂 merci à vous tous !

 

[JHA]

Re : Problème formule "SI"

Bonjour à tous,

En appliquant tes formules de #8, à essayer,

JHA

 

[C@thy]

Re : Problème formule "SI"

Arf Tibo, JHA, comme j'ai rien compris aux explications lumineuses de notre ami Crumpy, j'ai abandonné le sujet, le laissant entre vos mains expertes...

Bises

C@thy

 

[Crumpy]

Re : Problème formule "SI"

Désolé 🙁

Merci JHA je crois que c'est la bonne formule mais les résultats sont pas les bon parce que je pense que tu n'as pas pris les bon prix qui correspondent aux références. Je vois en P1,2,3 et Q1,2,3 :
C13 12,51
D13 0,74
E13 0,74
Ca c'est la marge que je me fais en fait, en vendant à la carte. Dans le tableau des références c'est les montants indiqués en colonne "E", or les bon montants à prendre sont ceux indiqués en colonne "C" (qui correspond aux prix d'achats). J'aurais du penser à intituler les colonnes désolé.
Les bons montants étant :
C13 5.99
D13 1.01
E13 1.01
Les résultats recherchés étant :
I13 0.28 K13 3.01
I14 0.16 K14 3.44
I15 0 K15 3.63

Je suppose que ça doit pouvoir se rectifier rapidement mais je ne connais pas les fonctions EQUIV et DECALER. Donc je ne peux pas le faire moi même...

Sinon j'ai fais une petite erreur dans mon précédent poste :
"SI il ne figure qu'une référence en "C13" alors : C13*0.196" C'est en fait H13*0.196.

Merci à ceux qui ont le courage de m'aider 🙂

 

[JHA]

Re : Problème formule "SI"

Bonsoir à tous,

Tu dois pouvoir rectifier de toi même
dans la fonction décaler tu as le chiffre 3 au bout pour prendre la 3 ème colonne, tu mets 1 au lieu de 3.
=(DECALER($B$43;EQUIV(C13;$B$44:$B$106;0);1))

pour la formule en "k13"
=SI(NBVAL($C13:$E13)=0;"";SI(NBVAL($C13:$E13)=3;((((DECALER($B$43;EQUIV(C13;$B$44:$B$106;0);1)))/((DECALER($B$43;EQUIV(C13;$B$44:$B$106;0);1))+(DECALER($B$43;EQUIV(D13;$B$44:$B$106;0);1))+(DECALER($B$43;EQUIV(E13;$B$44:$B$106;0);1))))*H13)*0,196;SI(NBVAL($C13:$E13)=2;(((DECALER($B$43;EQUIV(C13;$B$44:$B$106;0);1))/((DECALER($B$43;EQUIV(C13;$B$44:$B$106;0);1))+(DECALER($B$43;EQUIV(D13;$B$44:$B$106;0);1))))*H13)*0,196;(DECALER($B$43;EQUIV(H13;$B$44:$B$106;0);1))*0,196)))

JHA

 

[Crumpy]

Re : Problème formule "SI"

Je comprends pas ça ne donne toujours pas le bon résultat 🙁 ...

En parcourant le forum j'ai appris l’existence de la fonction esttexte() et j'ai donc refais une formule. Mais le resultat n'est pas bon non plus, on dirait que la formule ne prend pas en compte l'ensemble des calculs à faire. Même en la coupant et en en prenant qu'une partie : pour le calcul de "I13" : (((RECHERCHEV(D13;$B$44:$C$83;2)+RECHERCHEV(E13;$B$44:$C$83;2))/(RECHERCHEV(C13;$B$44:$C$83;2)+RECHERCHEV(D13;$B$44:$C$83;2)+RECHERCHEV(E13;$B$44:$C$83;2)))*H13)*0,055
Ca me donne 0.75 au lieu de 0.28. Et je ne sais pas si c'est une coïncidence mais 0.75 correspond à une partie de la formule, 75% c'est la part des produits à 5.5% dans le lot.

Je commence à perdre espoir, et je m'embrouille dans la gestion et la compta à cause de ce problème du coup. Moi qui pensais être un pro de la fonction "Si"...