SI ET imbriqué [Résolu]

Signaler
-
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
-
Bonjour,

Je souhaite insérer une formule pour automatiser un calcul d'une fiche de paie. Il y a 6 calculs et résultats différents suivant la situation soumis a une double condition. Je m'explique : 1ère condition Brut <ou >3428, 2ème condition nb salariés (<11, 11-50 ou >50). J'ai donc 6 possibilités de calcul final.
Voici la formule que j'ai insérée mais celle-ci ne semble pas juste lorsque je vérifie manuellement. Il me semble que la formule SI(ET imbriquée est faisable...Mes cours sont un peu loin...Je dois faire une erreur mais je ne sais où...Merci pour votre aide.

=SI(ET(E9<3428;H2<11);E9*(0,3+0,1+0,016)%;SI(ET(3428<E9;H2<11);E9*0,003+3428*0,001+E9*0,016;SI(ET(E9<3428;11<H2<50);E9*(0,3+0,1+2+0,016)%+(D15+D16)*0,08;SI(ET(3428<E9;11<H2<50);E9*0,003+3428*0,001+(D15+D16)*0,08+E9*0,02+E9*0,00016;SI(ET(E9<3428;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016;SI(ET(3428<E9;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016))))))

Configuration: Macintosh / Safari 13.1

11 réponses

Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758
Bonjour,

Que fais-tu des valeurs pivots 3428, 11, 50 : c'est soit <= ou >=
exemple E9<=3428 ou E9>=3428

et on ne peut pas mettre 11<H2<50 ; il faut séparer les deux conditions H2>11;H2<50

Cordialement
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758
re;
C'est ça il faut ajouter une troisième condition dans le ET() la valeur H2 est comprise entre deux valeur mais on écrit H2>11 et non pas 11<H2

SI(ET(E9<=3428;H2>11;H2<50)


Cordialement
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758
Re:

J'ai fait une réduction de ta formule pour éviter les ET(), en espérant qu'elle est correcte

=SI(E9<=3428;SI(H2<11;E9*(0,3+0,1+0,016)%;SI(H2<50;E9*(0,3+0,1+2+0,016)%+(D15+D16)*0,08;E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016));SI(H2<11;E9*0,003+3428*0,001+E9*0,016;SI(H2<50;E9*0,003+3428*0,001+(D15+D16)*0,08+E9*0,02+E9*0,00016;E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016)))


https://www.cjoint.com/c/JIxqKTykGVP

Cordialement
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
14 341
Bonsoir.

" il faut que la cellule soit toujours devant ? "
Non Demiz, mon ami PapyLuc51 a fait un peu d'excès de zèle :
Dans les tableurs Excel, on peut écrire indifféremment H2>11 ou 11<H2 à l'intérieur d'une formule.

" je vais également devoir changer les valeurs E9 et 3428 ? " Non, inutile ...

Dans une formule aussi longue que la tienne, tout caractère économisé est une bonne chose ; cela s'appelle optimiser une formule ; tu devrais donc remplacer :
E9*(0,3 + 0,1 + 0,016)% par E9*0,416% et
E9*(0,3+0,1+2+0,016)% par E9*2,416%
Tu gagnes 10 caractères chaque fois !
Par ailleurs, pourquoi écrire à 3 reprises
E9*0,02+E9*0,00016 alors que tu pourrais te contenter de
E9*0,02016 (économie de 8 caractères chaque fois) ?
... et E9*0,003+3428*0,001+E9*0,016 s'écrit E9*0,019+3,428

Au final, avec la correction de PapyLuc51 consistant à remplacer à 2 reprises
;11<H2<50);
par
;11<H2;H2<50);
,
ta formule de 381 caractères
=SI(ET(E9<3428;H2<11);E9*(0,3+0,1+0,016)%;SI(ET(3428<E9;H2<11);E9*0,003+3428*0,001+E9*0,016;
SI(ET(E9<3428;11<H2<50);E9*(0,3+0,1+2+0,016)%+(D15+D16)*0,08;
SI(ET(3428<E9;11<H2<50);E9*0,003+3428*0,001+(D15+D16)*0,08+E9*0,02+E9*0,00016;
SI(ET(E9<3428;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016;
SI(ET(3428<E9;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016))))))
devient la formule suivante, avec 323 caractères :
=SI(ET(E9<3428;H2<11);E9*0,416%;SI(ET(3428<E9;H2<11);E9*0,019+3,428;
SI(ET(E9<3428;11<H2;H2<50); E9*2,416%+(D15+D16)*0,08;
SI(ET(3428<E9;11<H2;H2<50);E9*0,003+3,428+(D15+D16)*0,08+E9*0,02016;
SI(ET(E9<3428;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02016;
SI(ET(3428<E9;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02016))))))
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758
Salutations;

Tu as raison Raymond je n'avais pas testé la chose ; j'étais resté sur mes acquis ; je m'en excuse auprès de Demiz

En respectant les optimisations que tu as faites pour réduire le nombre de caractères, ma formule donnée dans ma réponse n°5 peut encore être réduite à :

=SI(E9<3428;SI(H2<11;E9*0,416%;SI(H2<50;E9*2,416%+(D15+D16)*0,08;E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02016));SI(H2<11;E9*0,019+3,428;SI(H2<50;E9*0,003+3,428+(D15+D16)*0,08+E9*0,02016;E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02016)))

Cordialement
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758 >
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020

J'ajoute car je viens de le remarquer:

Dans la dernière partie de la formule initiale
"""SI(ET(3428<E9;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016"""
l'opération est identique que dans cette partie
"""SI(ET(E9<3428;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016"""

est-ce normal ?

Cordialement
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
14 341 >
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020

Non, PapyLuc, ce n'est pas normal du tout, et je m'en veux de ne pas l'avoir remarqué !
Du coup, je viens de faire ce que j'aurais dû effectuer dès le départ, c'est-à-dire décortiquer la formule de Demiz, en faire l'ordinogramme pour en vérifier la cohérence, dresser la "table de vérité" (vérifier que tous les cas de figure sont pris en compte), en déduire un ordinogramme mieux structuré en diminuant le nombre de fonctions SI.
Simultanément, simplifier les formules.

J'ai terminé ce travail sur papier.
Mais il me faudra un certain temps pour dessiner les ordinogrammes et rédiger ma réponse. Je ferai cela après être passé à table (il est midi chez moi).
Je voulais simplement te rassurer, te remercier, et vous demander de ne pas tenir compte de mon message #6 qui est incomplet et partiellement inexact ...
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
14 341
Salut Demiz.

J'ai pris note de tes précisions du post #9 (que tu aurais pu fournir dès ton message initial ...).
Je pense que tu as pris en compte la correction de syntaxe signalée par PapyLuc51 dans sa première intervention ?
Il te reste à terminer le deuxième test par l'argument ;"" avant les 6 parenthèses.
Ta formule devient donc
=SI(ET(E9<3428;H2<11);E9*(0,3+0,1+0,016)%;
SI(ET(3428<E9;H2<11);E9*0,003+3428*0,001+E9*0,016;
SI(ET(E9<3428;11<H2
;H2 
<50);E9*(0,3+0,1+2+0,016)%+(D15+D16)*0,08;
SI(ET(3428<E9;11<H2
;H2 
<50);E9*0,003+3428*0,001+(D15+D16)*0,08+E9*0,02+E9*0,00016;
SI(ET(E9<3428;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016;
SI(ET(3428<E9;50<H2);E9*0,003+E9*0,5+(D15+D16)*0,08+E9*0,02+E9*0,00016
;""
))))))

Si j'appelle R1, R2 ...R6 les 6 réponses possibles, la structure actuelle de ta formule est :
=SI(ET(E9<3428;H2<11);R1;
SI(ET(3428<E9;H2<11);R4;
SI(ET(E9<3428;11<H2;H2<50);R2;
SI(ET(3428<E9;11<H2;H2<50);R5;
SI(ET(E9<3428;50<H2);R3;
SI(ET(3428<E9;50<H2);R6;""))))))
On peut (et on doit) optimiser la logique des tests avec une structure plus simple :

=SI(E9<3428;SI(H2<11;R1;SI(H2<50;R2;R3));
SI(H2<11;R4;SI(H2<50;R5;R6)))

ce qui donne en toutes lettres
=SI(E9<3428;SI(H2<11;E9*0,416%;SI(H2<50;E9*2,416%+(D15+D16)*0,08; E9*0,52316+(D15+D16)*0,08));SI(H2<11;E9*0,019+3,428;SI(H2<50; E9*0,02316+3,428+(D15+D16)*0,08;E9*0,52316+(D15+D16)*0,08)))

Cordialement.
Messages postés
2828
Date d'inscription
dimanche 3 mai 2009
Statut
Membre
Dernière intervention
21 octobre 2020
758
Salut Raymond

Tu confirmes donc ma formule donnée en réponse #7 (encore mieux optimisée) mais selon toute logique, il faudrait mettre +3,428 à la R6, sinon elle est la copie de la R3.

Cordialement
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
14 341



Je suppose, en effet ...

Seul Demiz peut nous dire ce qu'il en est !

Et je confirme bien entendu ta formule ...

Pour ceux qui voudraient visualiser la différence entre la formule finale et la formule initiale de Demiz, voici l'ordinogramme correspondant à celle-ci :


Amicales salutations.
Merci beaucoup ! C'est vrai je voulais rajouter le "=" et j'ai zappé !
Pour la valeur de H2 Si je ne peux pas prendre l'intervalle et suis obligée de séparer ces deux conditions je vais devoir créer un SI(ET avec 3 conditions c'est faisable ? Ainsi j'aurai SI(ET(E9<=3428;11<H2;H2<50);mon calcul...
Nous sommes d'accord ? Désolée mais cela fait longtemps que j'ai utilisé les formules...Je vais le tester mais je voulais quand même vérifier la faisabilité
Merci à vous
Cordialement
Merci ! Je vais tester mais du coup il me semblait dans mes vieux souvenirs que le signe devait rester le même en l'occurence < et que nous devions interchanger les valeurs sans changer le signe pour que la formule fonctionne.
D'après votre réponse il faut que la cellule soit toujours devant ? Auquel cas je vais également devoir changer les valeurs E9 et 3428 ? C'est bien ça ? Merci encore
Merci beaucoup à vous 2 ! Je vais tester cela dès que possible, là je suis sur autre chose, en effet je fais plusieurs tâches en même temps, ce n'est pas toujours le plus judicieux et parfois source d'erreur, la preuve... Je vais reprendre tranquillement vos réponses mais je tenais vraiment à vous remercier pour votre retour et grande réactivité.
Pour la formule, je n'ai pas voulu la simplifier en regroupant les écritures pour retrouver si besoin la logique et le détail du calcul pour les relier à la base et aux % cotisations comptées.
J'ai aussi en effet utilisé 2 fois le même calcul car je redoutais que cela ne fonctionne pas si toutes les situations n'étaient pas intégrées à la formule, en l'occurence le cas du salarie brut <3428 et entreprise <50 salariés, je ne sais pas si je suis très claire bref...Merci encore
Bonjour, j'ai testé toutes les formules, quelques réponses sont différentes selon les formules. J'ai comparé l'exactitude des résultats et n ai pas noté les valeurs, j'ai donc certaines réponses fausses où d'autres sont justes et inversement...
Merci pour votre aide, mon problème est résolu. J'avais en effet oublié d'intégrer les bornes, et m'étaient trompées sur la saisies des valeurs intermédiaires qu'il fallait dissocier. Pour ma troisième erreur c'est à force d'analyser les différentes formules et de comparer avec la mienne que je me suis rendu compte que j'avais oublié les % à 3 valeurs d'où mes résultats non concordant avec mes calculs manuels.
Tout est résolu merci beaucoup pour votre aide !

Cordialement
Messages postés
52467
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
21 octobre 2020
14 341
Content que ton affaire soit réglée !

Pour tes prochains fichiers, veille à te montrer très rigoureuse et très logique.
Je te recommande vivement d'utiliser les ordinogrammes pour y voir clair, ne rien oublier et simplifier tes formules.

Cordialement.