Dur Dur a trouver la formule Résolu/Fermé

Question

Bonjour,

Je suis sur la conception d'un tableau et je me confronte a une formule qui me résiste, donc me voila sur se forum pour trouver des personnes super doué sur Excel qui eux pourrons m'aider a résoudre mon gros probléme.

En vous remerciant d'avance pour l'aide qui me seras apporter.

Voisi se que je cherche a faire:

=SI(C3=ETT;SI(D3+E3+F3+G3+H3+I3>=2;"135€";"0€");SI(D3+E3+F3+G3+H3+I3>=4;"360€";"135€");SI(D3+E3+F3+G3+H3+I3>=6;"720€";"360€");SI(D3+E3+F3+G3+H3+I3>=8;"900€";"720€");SI(D3+E3+F3+G3+H3+I3>=10;"1260€";"900€");SI(D3+E3+F3+G3+H3+I3>=12;"1530€";"1260€");SI(D3+E3+F3+G3+H3+I3>=14;"1800€";"1530€");SINON(C3=ETL;SI(D3+E3+F3+G3+H3+I3>=3;"180€";"0€");SI(D3+E3+F3+G3+H3+I3>=5;"630€";"180€");SI(D3+E3+F3+G3+H3+I3>=7;"900€";"630€");SI(D3+E3+F3+G3+H3+I3>=10;"1260€";"900€");SI(D3+E3+F3+G3+H3+I3>=12;"1530€";"1260€");SI(D3+E3+F3+G3+H3+I3>=15;"1800€";"1530€");SI(D3+E3+F3+G3+H3+I3>=20;"2250€";"1800€");SI(D3+E3+F3+G3+H3+I3>=25;"2700€";"2250€");SI(D3+E3+F3+G3+H3+I3>=30;"3150€";"2700€");SI(D3+E3+F3+G3+H3+I3>=15;"3600€";"3150€");"0€")

Et voila mon big probléme.
Et félicitation a celui qui me arrive a me dépatrouiller de cette formule.

Gord21 · Answer

Bonsoir,
Tu aurais pu faire un effort de mise en page pour faciliter la compréhension.
Est-ce une fonction que tu utilises plusieurs fois dans ton classeur ? La création d'une fonction personnalisée serait peut-être judicieuse.

Devriendt · Answer

Désolé pour la mise en page je suis pas abitué a poster sur les forums.

Pour ta question et bien oui je l'utilise a toute les lignes de mon tableur.

Et pour la personnaliser sa vas pas étre facil de le faire car sa fait déjà 4 jours que j'essaye de trouver l'astuce pour qu'elle fonctionne et j'en suis toujour au méme point.

Raymond PENTIER · Answer

Bonjour.
On va essayer de t'expliquer, pas à pas, tout ce qui ne va pas dans ta formule, qui ne risquait donc pas de marcher un jour ...

Raymond PENTIER · Answer

1) Sur quelle version d'Excel travailles-tu ? 
Il y a des fonctions dans la version 2007 qui n'existaient pas avant, et surtout on peut imbriquer plus de 7 fonctions SI dans une formule.
2) En remplaçant D3+E3+F3+G3+H3+I3 par SOMME(D3:I3) ta formule prendrait moins de place.
3) Si tu mettais des valeurs numériques dans ta formule, et le résultat au format cellule "Monétaire", tu aurais le même résultat, et tu pourrais, en prime, effectuer des calculs sur les résultats trouvés, ce qui n'est pas le cas avec ta formule pleine de guillemets.
4) En appliquant les remarques 2 et 3 ci-dessus, ta formule 
=SI(C3=ETT;SI(D3+E3+F3+G3+H3+I3>=2;"135€";"0€");SI(D3+E3+F3+G3+H3+I3>=4;"360€";"135€");SI(D3+E3+F3+G3+H3+I3>=6;"720€";"360€");SI(D3+E3+F3+G3+H3+I3>=8;"900€";"720€");SI(D3+E3+F3+G3+H3+I3>=10;"1260€";"900€");SI(D3+E3+F3+G3+H3+I3>=12;"1530€";"1260€");SI(D3+E3+F3+G3+H3+I3>=14;"1800€";"1530€");SINON(C3=ETL;SI(D3+E3+F3+G3+H3+I3>=3;"180€";"0€");SI(D3+E3+F3+G3+H3+I3>=5;"630€";"180€");SI(D3+E3+F3+G3+H3+I3>=7;"900€";"630€");SI(D3+E3+F3+G3+H3+I3>=10;"1260€";"900€");SI(D3+E3+F3+G3+H3+I3>=12;"1530€";"1260€");SI(D3+E3+F3+G3+H3+I3>=15;"1800€";"1530€");SI(D3+E3+F3+G3+H3+I3>=20;"2250€";"1800€");SI(D3+E3+F3+G3+H3+I3>=25;"2700€";"2250€");SI(D3+E3+F3+G3+H3+I3>=30;"3150€";"2700€");SI(D3+E3+F3+G3+H3+I3>=15;"3600€";"3150€");"0€") 
s'écrit déjà
=SI(C3=ETT;SI(SOMME(D3:I3)>=2;135;0);SI(SOMME(D3:I3)>=4;360;135);SI(SOMME(D3:I3)>=6;720;360);SI(SOMME(D3:I3)>=8;900;720);SI(SOMME(D3:I3)>=10;1260;900);SI(SOMME(D3:I3)>=12;1530;1260);SI(SOMME(D3:I3)>=14;1800;1530);SINON(C3=ETL;SI(SOMME(D3:I3)>=3;180;0);SI(SOMME(D3:I3)>=5;630;180);SI(SOMME(D3:I3)>=7;900;630);SI(SOMME(D3:I3)>=10;1260;900);SI(SOMME(D3:I3)>=12;1530;1260);SI(SOMME(D3:I3)>=15;1800;1530);SI(SOMME(D3:I3)>=20;2250;1800);SI(SOMME(D3:I3)>=25;2700;2250);SI(SOMME(D3:I3)>=30;3150;2700);SI(SOMME(D3:I3)>=15;3600;3150);0)
on a quand même gagné 191 caractères ...
5) Où as-tu découvert la fonction SINON ?

Raymond PENTIER · Answer

Revenons à du plus technique :

=SI(C3=ETT;SI(SOMME(D3:I3)>=2;135;0);SI(SOMME(D3:I3)>=4;360;135);SI(SOMME(D3:I3)>=6;720;360);SI(....
ne respecte pas la syntaxe de la fonction, qui donnerait plutôt
=SI(C3=ETT;SI(SOMME(D3:I3)>=2;135;SI(SOMME(D3:I3)>=4;360;SI(SOMME(D3:I3)>=6;720;SI.... )))).

De plus
=SI(C3=ETT;SI(SOMME(D3:I3)>=2;135;0);SI(SOMME(D3:I3)>=4;360;135);SI(SOMME(D3:I3)>=6;720;360);SI(....
n'est pas logique ! Il faut raisonner dans le bon sens :
=SI(C3=ETT;SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI( .... )))))

Devriendt · Answer

Bonjour a toi Raymond,

Pour commencer je te remerci de répondre a ma demande d'aide, pour répondre a tes questions je travail sur la version Excel 2003 et pour la formule SINON je les mis pensant qu'elle excister ;)

Je viens d'essayer tes formules en ayant compléter le reste qui a donner:
=SI(C3=ETT;SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI(SOMME(D3:I3)<8;720;SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260;SI(SOMME(D3:I3)<14;1530;SI(SOMME(D3:I3)>14;1800)))))))))

Qui ne veut non plus fonctionner.

Et comment embriquer la suite avec:
=SI(C3=ETL;SI(SOMME(D3:I3)<3;0;SI(SOMME(D3:I3)<5;180;SI(SOMME(D3:I3)<7;630;SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260;SI(SOMME(D3:I3)<15;1530;SI(SOMME(D3:I3)>20;1800;SI(SOMME(D3:I3)>25;2250;SI(SOMME(D3:I3)>30;2700;SI(SOMME(D3:I3)>35;3150;SI(SOMME(D3:I3)>40;3600;SI(SOMME(D3:I3)<40;4500)))))))))

Gord21 · Answer

Bonjour,
Dans ta formule, tu as toujours le soucis soulevé par Raymond (bonjour) à savoir il faut raisonner dans le bon sens :

SI(SOMME(D3:I3)>30;2700;SI(SOMME(D3:I3)>35;3150;...

Veut dire si la somme est supérieure à 30, la valeur est 2700 ; sinon si la somme est supérieure à 35, la valeur est 3150 ; ... Or si la valeur est supérieure à 35, elle est nécessairement supérieure à 30. D'où l'écriture :

SI(SOMME(D3:I3)<30;0;SI(SOMME(D3:I3)<35;2700;...

Juste en complément de mon post, lorsque je parle de fonction personnalisée, il s'agit juste d'un code VBA qui te permettrait créer une fonction du type :

Ma_fonction(Valeur_testée ; plage_de_données)

qui serait plus facile à éditer. Dans ton cas, elle ressemblerait à :

=Ma_fonction(C3;D3:I3)

Si ça t'intéresse, fait moi signe.

Raymond PENTIER · Answer

Quand tu écris
=SI(C3=ETT;SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI(SOMME(D3:I3)<8;720;SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260;SI(SOMME(D3:I3)<14;1530;SI(SOMME(D3:I3)>14;1800))))))) ) )
cela signifie pour Excel que ETT est le nom que tu as donné à une cellule ou à une plage.
Je crois plutôt qu'il s'agit d'une donnée texte contenue dans C3 ; dans ce cas il faut mettre cette valeur entre guillemets =SI(C3="ETT";SI...
Au départ, tu en avais donc mis là où il fallait pas (valeurs numériques), mais pas où il en fallait (saisies alphabétiques).
La fin de ta formule n'est pas logique non plus :
...;SI(SOMME(D3:I3)<14;1530;SI(SOMME(D3:I3)>14;1800))))))) ) )
en effet d'une part ton dernier SI ne propose de valeur_si_faux, et d'autre part l'avant dernier SI ayant épuisé tous les cas où SOMME(D3:I3) était <14, il n'y a pas lieu de faire un dernier test logique SI(SOMME(D3:I3)>14 ; donc ta formule se termine ainsi
...;SI(SOMME(D3:I3)<14;1530;1800))))))) )
Enfin ton tout premier SI, lorsque le test logique C3="ETT" est vrai, déclenche toute une imbrication d'autres SI ; mais tu dois, là encore, ajouter tout à la fin la valeur_si_faux, soit
...;SI(SOMME(D3:I3)<14;1530;1800))))))) ;"rien")

Et comme je te l'ai indiqué au 1) du post 4, ce n'est qu'à partir de la version 2007 qu'Excel autorise plus de 7 imbrications de SI !

Albert · Answer

Bonjour

J'aurais d'abord créé une cellule somme, par exemple :

A1 = D3+E3+F3+G3+H3+I3

J'aurais ensuite 2 petits tableaux faciles à modifier dans un coin, par exemple :

        A     B       C     D      E       F       G        H
2:     0     2       4      6      8      10      12      14
3:     0    135  360   720   900   1260  1530  1800 


        A     B       C     D       E        F       G        H       I       J       K
5:     0     3       5      7      10      12      15      20     25     30     35
6:     0    180  630   900   1260   1530  1800  2250  2700  3150  3600


Et j'aurais utilisé la formule :

=SI(C3=ETT;RECHERCHEH(A1;A2:H3;2;1);SI(C3=ETL;RECHERCHEH(A1;A5:K6;2;1);0))

Devriendt · Answer

Et bien la formule de Raymond fonctionne: 

=SI(C3="ETT";SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI(SOMME(D3:I3)<8;720;SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260))))));"rien")

Mes voila comme Raymond tu me la signalé sa fonctionne que sur un embriquement de 7 SI
Et tant que je n'aurais pas la version Excel 2007 sa ne fonctionneras pas pour moi.

Alors voila albert tu commence a m'intéresser avec ta formule ;) 
Peut tu m'en dire plus.

Encore merci a vous pour l'entraide.

Albert · Answer

Pas grand chose à ajouter, à part les espaces supprimés lors de l'envoi,
pour une meilleure compréhension, et mettre les références en absolu.

A1 = SOMME(D3:I3)

........A........ B........ C........ D........ E........ F........ G........ H.........I.........J.........K
1: = SOMME(D3:I3)
2:.... 0 ........2 .........4 ........6 ........8 .......10...... 12 .......14 
3: ....0 ......135 .....360 .....720..... 900... 1260 ...1530 ...1800 
4:
5: ....0 ........3 ........5 ........7 ........10 ......12 ......15 .......20 ......25...... 30 .....35 
6: ....0 ......180 ....630 .....900 .....1260 ..1530 ...1800... 2250... 2700 ...3150.. 3600 


=SI(C3=ETT;RECHERCHEH($A$1;$A$2:$H$3;2;1);SI(C3=ETL;RECHERCHEH($A$1;$A$5:$K$6;2;1);0))

Devriendt · Answer

Je viens de faire mon petit tableau avec mes féférences et j'ai fait la formule que voici:

=SI(C3="ETT";RECHERCHEH('$'!A1:A;'$'!A2:H3;2;1);SI(C3="ETL";RECHERCHEH('$'!A1;'$'!A5:L6;2;1);SI(C3="TC";RECHERCHEH('$'!A1;'$'!A8:J9;2;1);"0")))

Et elle fontionne fonctionne mes me marque erreure des que je rentre les donnés !!!!!!

Albert · Answer

C3 est compris dans le premier tableau A2:H3
Je ne connais pas le symbole $ entre ' ?, comme indiqué, cela fonctionne bien.
Pourqoi 0 en texte à la fin ?

Devriendt · Answer

C3 et compris dans le premier tableau A2:H3 ? 
Je ne vois pas se que tu veut dire ?

Le symbole $ et le nom de la feuille ou j'ai mis le petit tableau avec toute les source pour la formule 

J'ai mis 0 en fin pour finir la formule

Devriendt · Answer

Raymond je viens d'essayer de faire la formule 
=SI(C3="ETT";SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI(SOMME(D3:I3)<8;720;SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260;SI(SOMME(D3:I3)<14;1530;180)))))));"rien")
Sur le pc d'un amis qui a la version de Excel 2007 et il me dit que j'ai dépasser la limite d'embriquement.

Albert · Answer

A2:H3 comprend :
A2,B2,C2,D2,E2,F2,G2,H2
A3,B3,<gras>C3</gras>,D3,E3,F3,G3,H3

C3 ne peut pas contenir à la fois une valeur alphabétique : ETT,ETL ou TC
et une valeur numérique du tableau, 360 dans ce cas.

Ok pour le nom de la feuille, mais évites ce genre de nom qui peut prêter à confusion
et ça ne te dispense pas des symboles $ d'adresse absolue car le tableau est le même pour tous les calculs éventuels.

Ok pour le O qui ne devrait jamais être atteint, sauf si ta chaine test n'est ni ETT, ni ETL, ni TC.
Une validation de cette chaine serait peut-être utile.

Devriendt · Answer

Albert, 

Je crois que la je suis arriver a mes limite ;)

Pour la feuille avec le nom $ c pas grave je pourais la changer plus tard pour le 0 de fin de formule sa ne dérange pas 

mes explique toi pour pour le C3 je n'arrive pas voir de quoi l'on parle

Raymond PENTIER · Answer

Bonjour à tous les deux.

Je me suis appliqué, dans mes premiers posts, à signaler à Devriendt quelles étaient les erreurs de conception et d'écriture de sa formule, ainsi que les limitations d'Excel. Et à la lecture de son post 11, je suis heureux de constater qu'il a compris.

Albert a bien voulu prendre le relais pour proposer une autre approche au post 10, en substituant à la fonction SI la fonction RECHERCHEH, ce qui est effectivement beaucoup mieux.

Devriendt a adapté au post 13 la formule donnée par Albert au post 10, sans préciser quelle formule il a mise en "$"!A1 ; il faudrait nous envoyer ton fichier pour qu'on vérifie comment tu as disposé tes données. Mais déjà, tu te faciliterais la vie en donnant des noms à tes zones : "ref" pour A1, "MatETT" pour A2:H3, "MatETL" pour A5:L6 et "MatTC" pour A8:J9, ce qui te donnerait pour formule
=SI(C3="ETT";RECHERCHEH(ref;MatETT;2;1);SI(C3="ETL";RECHERCHEH(ref;MatETL;2;1);SI(C3="TC";RECHERCHEH(ref;MatTC;2;1);0)))

Albert · Answer

Ok si tu travailles sur 2 feuilles, le C3 du test est sur une feuille et la tableau sur une autre.
Excuses moi.

Devriendt · Answer

Je veut bien vous envoyer mon fichier pour que vous compreniez mieux le probléme. 

Voici mon adresse mail pour me faire la demande et que la votre d'adresse ne soit pas visible ici.

devriendt.jeansebastien@hotmail.fr

Gord21 · Answer

Bonsoir,
Juste une petite info, tu peux contourner la limite des 7 imbrications en utilisant une syntaxe du type :

=SI(C3="ETT";SI(SOMME(D3:I3)<2;0;SI(SOMME(D3:I3)<4;135;SI(SOMME(D3:I3)<6;360;SI(SOMME(D3:I3)<8;720;0))))+SI(SOMME(D3:I3)<10;900;SI(SOMME(D3:I3)<12;1260;SI(SOMME(D3:I3)<14;1530;180)));"rien")

Merci à Mike-31

Devriendt · Answer

Je viens d'éssayer cette formule mes des que je met ETT dans C3 elle me donne 900 et si je rentre un nombre dans D3,E3,F3,G3,H3 elle se met en ######

Met je la garde pour regarder se qu'il cloche elle devrait pouvoir fonctionner.

Merci a toi.

Mike-31 · Answer

Salut,

J'ai repris le post 1, il semblerait qu'il y ai une erreur dans la fin des explications de Devriendt, >=30=3150 et >= 15=3600, je pense qu'il voulait dire >=35!
SI(D3+E3+F3+G3+H3+I3>=30;"3150€");SI(D3+E3+F3+G3+H3+I3>=35;"3600€");"0€") 

sur ces bases, la formule serait

=(SI(C3="ETT";SI(SOMME(D3:I3)>=14;1800;SI(SOMME(D3:I3)>=12;1530;SI(SOMME(D3:I3)>=10;1260;SI(SOMME(D3:I3)>=8;900;0))))+SI(SOMME(D3:I3)>=8;0;SI(SOMME(D3:I3)>=6;720;SI(SOMME(D3:I3)>=4;360;SI(SOMME(D3:I3)>=2;135;0))));"")&SI(C3="ETL";SI(SOMME(D3:I3)>=35;3600;SI(SOMME(D3:I3)>=30;3150;SI(SOMME(D3:I3)>=25;2700;SI(SOMME(D3:I3)>=20;2250;SI(SOMME(D3:I3)>=15;1800;SI(SOMME(D3:I3)>=12;1530;0)))))+SI(SOMME(D3:I3)>=12;0;SI(SOMME(D3:I3)>=10;1260;SI(SOMME(D3:I3)>=7;900;SI(SOMME(D3:I3)>=5;630;SI(SOMME(D3:I3)>=3;180;0))))));""))*1

souvent les formules importantes se parasitent voir sur le fichier joint en C10 si c'est le but recherché

https://www.cjoint.com/?ddwQpGxXCg

Mike-31 · Answer

Re,

#VALEUR! est un message d'erreur, lorsque la formule teste la cellule C3 à la recherche de texte, si elle ne trouve rien elle traduit ce message d'erreur. il est possible de l'éviter avec la fonction ESTERREUR.

Il est possible d'augmenter les imbrications jusqu'à 40 conditionnelles mai ça devient du sport pour l'écrire

donnes tous les éléments pour ne pas avoir à la recommencer

Mike-31 · Answer

Re,

Tu n'as pas mis de lien pour récupérer ton fichier.
Clic sur le lien ci-dessous, puis parcourir/Sélectionner le fichier/Créer le lien/Autoriser l'accès et colles dans un post le lien généré en haut de la feuille. il se peut que ton fichier soit trop lourd, dans ce cas donnes moi ton fournisseur d'accès voir si on peut procéder différemment

https://www.cjoint.com/

Mike-31 · Answer

Re,

J'ai revu tes formules en J3, J4, J5 et J6
également en L et N3, L et N4, L et N5, et L et N6. Il convient de les écrire comme cela afin de les écourter et gagner un peu en poids de fichier surtout que les formules en K sont conséquentes

Ainsi que les formules 
K3, K4 et K5

https://www.cjoint.com/?dft6VNwwVx

Gord21 · Answer

Bonsoir,
Simplement pour info, j'ai repris le fichier de Mike-31 en remplaçant par une formule personnalisée pour que tu vois ce que ça donne.
http://www.cijoint.fr/cjlink.php?file=cj201003/cij6BzNxD9.xls

@+

Yvon · Answer

Bonsoir

Autre version extrapolée des propositions de Raymond Pentier et Albert en p1.

http://www.cijoint.fr/cjlink.php?file=cj201003/cijuIZwyBi.xls