Imbriquer des formules de conditions !!

[philjourney]

Bonjour à tous !! Je reviens à votre secour pour de l'aide : je vais essayé d'être le plus clair possible.

Pour les déclarations URSSAF d'une société aui compte plus de 4000 salariés, je souhaiterais réaliser un tableau qui me calcule automatiquement le montant de l'abattement à retirer à savoir 10% du revenu avec un minimum de 138 € et un maximum de 363€. Pour les revenus supérieurs à 4871€, il n'y a pas d'abattement.
A partir du montant du revenu après abattement, il faudrait qu'excel attribue la tranche, la base de calcule et les cotisations sociales (salariales et patronales).
Pour les département de l'alsace et la moselle (67, 68 et 57) le pourcentage de calcule des cotisations sociales salariales est différent.

Pour plus de simplicité, je vous envoie un fichier Excel regroupant tout cela.

Je vous remercie par avance pour des reponses et solutions même partielle.

 

[philjourney]

Re : Determiner valeur cellules en fonction de plusieurs intervalles + Code Postal

Je me suis peut etre mal expliqué...

 

[philjourney]

Re : Determiner valeur cellules en fonction de plusieurs intervalles + Code Postal

Bonjour,

Pour plus de clarté, je vous renvoie à l'exemple que j'ai mis, j'ai du mal a bien m'expliquer

comme personne ne répond, j'ai essayé de trouver une solution de mon coté. Je suis parvenu quasiment au bout de mon problème mais j'ai encore un soucis : dans le cas où le revenu est superieur à 4871€, il n'y a pas d'abattement et pas de base pour le calcul des cotisations sociale. Elles se calculent directement à partir de celui ci par un pourcentage de 15,5 % sur les cotisations sociales salariales et 17 % en Alsace et Moselle et de 31,4% pour les cot sociales patronales.
Je voudrais rajouter à ma formule ce cas précis : Si le code postal commence par 67 68 ou 57 et que la tranche est >O, alors la cellule est égale à 17% du revenu, si le code postal ne commence pas par 67 68 ou 57 et que la tranche est >O, alors la cellule est egale à 15,5% du revenu. Mon problème c'est que je n'arrive pas à rajouter cette condition dans ma formule, qui pour moi est déjà une prouesse (je suis nul en formule).

 

[philjourney]

Re : Imbriquer des formules de conditions !!

Quelqu'un serait il prêt à m'aider, s'il vous plaît !!

 

[CISCO]

Re : Imbriquer des formules de conditions !!

Bonsoir

Pourrais tu m'expliquer l'utilité du OU juste après le * dans cette formule :

SI(OU(STXT(B3;1;2)="57";STXT(B3;1;2)="67";STXT(B3;1;2)="68");INDEX(Base!H:H;EQUIV(Travail!G3;Base!D:D;0));INDEX(Base!E:E;EQUIV(Travail!G3;Base!D:D;0)))    *OU    ((SI(OU(ET(G3=">O";STXT(B3;1;2)="57");(ET(G3=">O";STXT(B3;1;2)="67"));(ET(G3=">O";STXT(B3;1;2)="68")));17%*E3;15,5%*E3)))

Et pourquoi H2=">0"dans la colonne I ? Il me semble que H2 ne peut jamais être =">0" !

Si tu nous laisses un peu de temps, nous pourrons certainement te proposer un fichier amélioré, mais, il faut... nous laisser un peu de temps ! Ici, il ni a que des bénévoles.

@ plus

 

[philjourney]

Re : Imbriquer des formules de conditions !!

je ne sais pas, en fait j'avais mis ET mais cela ne fonctionnait pas non plus je crois. Tu aurais une idée ?
Oui je comprends... je vais essayer de continuer de chercher de mon côté. Merci d'avance

 

[CISCO]

Re : Imbriquer des formules de conditions !!

Bonsoir

Je ne peux pas me pencher sérieusement sur ton problème maintenant. En attendant, essayes avec, en H2 :

SI(G2=">O";SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");17%;15,5%)*E2;INDEX(SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");Base!H:H;Base!E:E);EQUIV(G2;Base!D:D;0)))

et en I2

SI(G2=">O";SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");17%;15,5%)*F2;INDEX(SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");Base!I:I;Base!F:F);EQUIV(G2;Base!D:D;0)))

Je vérifierai tout cela demain.

@ plus

 

[Oliver77]

Re : Imbriquer des formules de conditions !!

Bonjour,

Vu le grand nombre de condition je te suggère de passer par la fonction "choisir".

Oliver

 

[philjourney]

Re : Imbriquer des formules de conditions !!

Oui pour faire mes formules je me suis inspiré de différents cas, dont l'un d'eux utilisait la fonction CHOISIR mais je ne sais pas comment l'utiliser avec l'ensemble des conditions.

 

[CISCO]

Re : Imbriquer des formules de conditions !!

Bonjour

Il ni a pas tant de conditions que ça. Tu peux aussi faire avec, en H2 :

SI(G2=">O";SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));17%;15,5%)*E2;INDEX(SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));Base!H:H;Base!E:E);EQUIV(G2;Base!D:D;0)))

et en I2

SI(G2=">O";SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));17%;15,5%)*E2;INDEX(SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));Base!I:I;Base!F:F);EQUIV(G2;Base!D:D;0)))

Ces deux formules ne me plaisent pas trop à cause des répétitions ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0)). Si je trouve mieux...
Je n'ai fait que réorganiser les formules que tu avais mis dans ces deux cellules. Par conséquent, si il avait une erreur au début, elle y est toujours. A vérifier donc...

@ plus

 

[philjourney]

Re : Imbriquer des formules de conditions !!

Bonjour Cisco,

Ton code fonctionne bien apparemment, j'ai juste changé en I2 pour les CSP les pourcentages, ils sont identiques à 31,4 % quel que soit le code postal, je pourrait enlevé la condition mais l'année prochaine il y aura des modifications.

et en I2

SI(G2=">O";SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");17%;15,5%)*F2;INDEX(SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");Base!I:I;Base!F:F);EQUIV(G2;Base!D:D;0)))

Cela donne donc:

SI(G2=">O";SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");17%;15,5%)*F2;INDEX(SI(OU(GAUCHE(B2;2)="57";GAUCHE(B2;2)="67";GAUCHE(B2;2)="68");Base!I:I;Base!F:F);EQUIV(G2;Base!D:D;0)))

Je pense que c'est bon. Je te remercie beaucoup. Peux tu me dire ce qu'il y a de mieux avec GAUCHE(B2;2) par rapport à STXT(B2;1;2) ? C'est de la simple curiosité.

 

[CISCO]

Re : Imbriquer des formules de conditions !!

Bonjour

Je trouve qu'on voit mieux ce qu'on fait avec la fonction GAUCHE, dans ce cas, plutot qu'avec la fonction STXT. Autrement, c'est du pareil au même.
Dans ma dernière proposition, j'ai fait avec SI(ESTNUM(EQUIV... juste pour faire plus court, pour ne pas avoir à mettre les trois possibilités GAUCHE()=" ". Là ausi, c'est du pareil au même.

Si j'ai le temps, je regarderai plus précisément ton fichier, au cas où il aurait plus simple.

@ plus

 

[philjourney]

Re : Imbriquer des formules de conditions !!

Je pense que je vais utiliser ceci pour l'instant, merci beaucoup Cisco.

Une dernière question, je vais utiliser ces formules en ajoutant chaque trimestre une liste de salariés, y aurait il un moyen de garder en "mémoire" les fromules de la ligne 2, même peut être de les verouiller (je ne vais travailler dessus à l'avenir et ne voudrais pas qu'il y ait un soucis après suite à une mauvaise manipulation) ou suis-je obligé de laisser la 2eme ligne comme modèle, d'ajouter les salariés en dessous et de tirer la formule? Ce n'est qu'accessoire, le principal étant résolu.

Merci

 

[CISCO]

Re : Imbriquer des formules de conditions !!

Bonsoir

Tu peux très bien, par précaution, verrouiller la ligne 2 et tirer vers le bas à partir de la ligne 3.

Pour te simplifier la vie, une petite astuce : Tu sélectionnes la plage avec les formules qui t'intéressent, par exemple D3:I3. Tu positionnes le curseur (croix blanche) en bas à droite de cette plage, donc en bas à droite de I3 dans mon exemple. Le curseur se transforme en croix noire. Tu double-cliques et hop, toutes les formules sont copiées-collées automatiquement vers le bas.

@ plus

 

[philjourney]

Re : Imbriquer des formules de conditions !!

Bonjour,
je viens de découvrir un problème en cherchant à tester différentes situations. Le formatage des cellules du Code Postal est bien en "Code Postal" mais le 0 devant les code postaux (quand il y en a un) n'est pas pris en considération. Le problème c'est que si on a par exemple un code postal 05700, affiché tel quel mais pris par excel comme 5700, la formule =SI(G2=">O";SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));17%;15,5%)*E2;INDEX(SI(ESTNUM(EQUIV(GAUCHE(B2;2);{"57";"67";"68"};0));Base!H:H;Base!E:E);EQUIV(G2;Base!D😀;0)))
attribuera les valeurs correspondant à l'Alsace Moselle bien que ce ne soit pas le cas.

Auriez-vous une suggestion pour contourner ce pb ?

Je vous remercie