Pourriez vous m'aider simplifier cette formule?

Le 05/03/2016 à 14:24

Excel ne permet pas plus de 64 niveaux de nestings, pourriez vous m'aider simplifier cette formule? Merci!!

=IF(AND(H40<=4,H40>=1),H40-H42,

IF(AND(H40<=8,H40>=5,H41<=4),4-H41,

IF(AND(H40<=8,H40>=5,H41>=5,H41<9),H40-H41,

IF(AND(H40<=12,H40>=9,H41<=4),4-H41,

IF(AND(H40<=12,H40>=9,H41>=5,H41<9),8-H41,

IF(AND(H40<=12,H40>=9,H41>=9,H41<13),H40-H41,

IF(AND(H40<=16,H40>=13,H41<=4),4-H41,

IF(AND(H40<=16,H40>=13,H41>=5,H41<9),8-H41,

IF(AND(H40<=16,H40>=13,H41>=9,H41<13),12-H41,

IF(AND(H40<=16,H40>=13,H41>=13,H41<17),H40-H41,

IF(AND(H40<=20,H40>=17,H41<=4),4-H41,

IF(AND(H40<=20,H40>=17,H41>=5,H41<9),8-H41,

IF(AND(H40<=20,H40>=17,H41>=9,H41<13),12-H41,

IF(AND(H40<=20,H40>=17,H41>=13,H41<17),16-H41,

IF(AND(H40<=20,H40>=17,H41>=17,H41<21),H40-H41,

IF(AND(H40<=24,H40>=21,H41<=4),4-H41,

IF(AND(H40<=24,H40>=21,H41>=5,H41<9),8-H41,

IF(AND(H40<=24,H40>=21,H41>=9,H41<13),12-H41,

IF(AND(H40<=24,H40>=21,H41>=13,H41<17),16-H41,

IF(AND(H40<=24,H40>=21,H41>=17,H41<21),20-H41,

IF(AND(H40<=24,H40>=21,H41>=21,H41<25),H40-H41,

IF(AND(H40<=28,H40>=25,H41<=4),4-H41,

IF(AND(H40<=28,H40>=25,H41>=5,H41<9),8-H41,

IF(AND(H40<=28,H40>=25,H41>=9,H41<13),12-H41,

IF(AND(H40<=28,H40>=25,H41>=13,H41<17),16-H41,

IF(AND(H40<=28,H40>=25,H41>=17,H41<21),20-H41,

IF(AND(H40<=28,H40>=25,H41>=21,H41<25),24-H41,

IF(AND(H40<=28,H40>=25,H41>=25,H41<29),H40-H41,

IF(AND(H40<=32,H40>=29,H41<=4),4-H41,

IF(AND(H40<=32,H40>=29,H41>=5,H41<9),8-H41,

IF(AND(H40<=32,H40>=29,H41>=9,H41<13),12-H41,

IF(AND(H40<=32,H40>=29,H41>=13,H41<17),16-H41,

IF(AND(H40<=32,H40>=29,H41>=17,H41<21),20-H41,

IF(AND(H40<=32,H40>=29,H41>=21,H41<25),24-H41,

IF(AND(H40<=32,H40>=29,H41>=25,H41<29),28-H41,

IF(AND(H40<=32,H40>=29,H41>=29,H41<33),H40-H41,

IF(AND(H40<=36,H40>=33,H41<=4),4-H41,

IF(AND(H40<=36,H40>=33,H41>=5,H41<9),8-H41,

IF(AND(H40<=36,H40>=33,H41>=9,H41<13),12-H41,

IF(AND(H40<=36,H40>=33,H41>=13,H41<17),16-H41,

IF(AND(H40<=36,H40>=33,H41>=17,H41<21),20-H41,

IF(AND(H40<=36,H40>=33,H41>=21,H41<25),24-H41,

IF(AND(H40<=36,H40>=33,H41>=25,H41<29),28-H41,

IF(AND(H40<=36,H40>=33,H41>=29,H41<33),32-H41,

IF(AND(H40<=36,H40>=33,H41>=33,H41<37),H40-H41,

IF(AND(H40<=40,H40>=37,H41<=4),4-H41,

IF(AND(H40<=40,H40>=37,H41>=5,H41<9),8-H41,

IF(AND(H40<=40,H40>=37,H41>=9,H41<13),12-H41,

IF(AND(H40<=40,H40>=37,H41>=13,H41<17),16-H41,

IF(AND(H40<=40,H40>=37,H41>=17,H41<21),20-H41,

IF(AND(H40<=40,H40>=37,H41>=21,H41<25),24-H41,

IF(AND(H40<=40,H40>=37,H41>=25,H41<29),28-H41,

IF(AND(H40<=40,H40>=37,H41>=29,H41<33),32-H41,

IF(AND(H40<=40,H40>=37,H41>=33,H41<37),36-H41,

IF(AND(H40<=40,H40>=37,H41>=37,H41<41),H40-H41,

IF(AND(H40<=44,H40>=41,H41<=4),4-H41,

IF(AND(H40<=44,H40>=41,H41>=5,H41<9),8-H41,

IF(AND(H40<=44,H40>=41,H41>=9,H41<13),12-H41,

IF(AND(H40<=44,H40>=41,H41>=13,H41<17),16-H41,

IF(AND(H40<=44,H40>=41,H41>=17,H41<21),20-H41,

IF(AND(H40<=44,H40>=41,H41>=21,H41<25),24-H41,

IF(AND(H40<=44,H40>=41,H41>=25,H41<29),28-H41,

IF(AND(H40<=44,H40>=41,H41>=29,H41<33),32-H41,

IF(AND(H40<=44,H40>=41,H41>=33,H41<37),36-H41,

IF(AND(H40<=44,H40>=41,H41>=37,H41<41),40-H41,

IF(AND(H40<=44,H40>=41,H41>=41,H41<45),H40-H41,

IF(AND(H40<=48,H40>=45,H41<=4),4-H41,

IF(AND(H40<=48,H40>=45,H41>=5,H41<9),8-H41,

IF(AND(H40<=48,H40>=45,H41>=9,H41<13),12-H41,

IF(AND(H40<=48,H40>=45,H41>=13,H41<17),16-H41,

IF(AND(H40<=48,H40>=45,H41>=17,H41<21),20-H41,

IF(AND(H40<=48,H40>=45,H41>=21,H41<25),24-H41,

IF(AND(H40<=48,H40>=45,H41>=25,H41<29),28-H41,

IF(AND(H40<=48,H40>=45,H41>=29,H41<33),32-H41,

IF(AND(H40<=48,H40>=45,H41>=33,H41<37),36-H41,

IF(AND(H40<=48,H40>=45,H41>=37,H41<41),40-H41,

IF(AND(H40<=48,H40>=45,H41>=41,H41<45),44-H41,

IF(AND(H40<=48,H40>=45,H41>=45,H41<49),H40-H41,

IF(AND(H40<=52,H40>=49,H41<=4),4-H41,

IF(AND(H40<=52,H40>=49,H41>=5,H41<9),8-H41,

IF(AND(H40<=52,H40>=49,H41>=9,H41<13),12-H41,

IF(AND(H40<=52,H40>=49,H41>=13,H41<17),16-H41,

IF(AND(H40<=52,H40>=49,H41>=17,H41<21),20-H41,

IF(AND(H40<=52,H40>=49,H41>=21,H41<25),24-H41,

IF(AND(H40<=52,H40>=49,H41>=25,H41<29),28-H41,

IF(AND(H40<=52,H40>=49,H41>=29,H41<33),32-H41,

IF(AND(H40<=52,H40>=49,H41>=33,H41<37),36-H41,

IF(AND(H40<=52,H40>=49,H41>=37,H41<41),40-H41,

IF(AND(H40<=52,H40>=49,H41>=41,H41<45),44-H41,

IF(AND(H40<=52,H40>=49,H41>=45,H41<49),48-H41,

IF(AND(H40<=52,H40>=49,H41>=49,H41<53),H40-H41,

))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Le 05/03/2016 à 15:01

Bonjour Amaia,

je crois qu'une petite explication, dans une belle phrase, du but de ta formule illustrée par un bout de fichier nous aiderait grandement!

Un petit peu de VBA, peut-être ?

A+

Le 05/03/2016 à 15:40

Bonjour,

C'est déjà un exploit d'y être arrivé

et je plussoie.... un fichier exemple aiderait , c'est écrit dans l’accueil

P.

Le 05/03/2016 à 15:41

Bonjour,

A vérifier :

=SI(H40>0;SI(H40>4;MIN(ENT((H41+3)/4)*4;H40)-H41;H40-H42);"")

Cordialement.

Le 05/03/2016 à 16:08

Bonjour -encore-:)

Merci pour vos réponses. Je n'ai pas inclus de fichiers car Excel me met l'erreur qu'il y a trop de nestings dans ma formule, donc j'ai choisi de la copier coller ici.

La formule fonctionne parfaitement si je vais jusqu'a 64 nestings, mais est lourde et inélégante.

Pardon pour le manque d'explications.

H40 et H41 sont les 2 seules variables sauf pour le premier IF, et peuvent prendre la valeur de 1 a 52. (Pour info, il s'agit de semaines).

La valeur maximale de H41 est égale a H40, donc pour m'autoparaphraser, H41 ne peut jamais excéder H40.

Merci pour votre aide

Le 05/03/2016 à 16:11

Complétée par des conditions non prises dans la précédente car j'ai le "sentiment" qu'il s'agit de cas qui ne devraient pas se produire, sauf peut-être H40>4 alors que H41 serait toujours à 0...

Elles ne sont pas forcément bien placées si elles sont à considérer, car la formule initiale était incomplète sur quelques points.

=SI(OU(H40>0;H41<=H40);SI(ET(H40>4;H41>0);MIN(ENT((H41+3)/4)*4;H40)-H41;H40-H42);"")

Je m'explique : si H40 est à 0 c'est qu'on n'a pas démarré l'année, si H41>H40 il y a erreur quelque par, donc dans ces cas pas d'affichage.

Le cas où H41=0 alors que H40>4 : dans ce cas mes formules vont renvoyer H40-H42. Si il faut que dans ce cas elles renvoient 4-H41 (soit 4), il faut rectifier ainsi :

=SI(OU(H40>0;H41<=H40);SI(H40>4;MIN(MAX(ENT((H41+3)/4)*4;4);H40)-H41;H40-H42);"")

A suivre...

J'étais dans la voie donc : une condition qui peut être supprimée (sauf toutefois, si résulte d'une saisie qui peut toujours être erronée...).

Reste donc le cas H41=0...

Et une question subsidiaire : en semaine 53 que se passe-t-il ?

Le 05/03/2016 à 16:32

MFerrand merci pour ta reponse. Ta ligne est superbe

H41 n'est jamais égale a 0 car la 1ere semaine est= a 1.

Pour le cas supérieur a 52, j'ai considéré qu il ne se produirait pas. En fait il pourrait se produire, mais je me suis arrêtée a un an (52 semaines), car déjà Excel refusait, alors je me suis dit, essayons de trouver une formule qui marche pour un an, ensuite, on avisera. Si ta formule marche pour au-delà, c'est encore mieux...

Le 05/03/2016 à 16:41

Si les conditions que j'ai repris ensuite ne sont pas à prendre en compte, tu peux en rester à la 1re formule, elle renverra 53-H41. De toute façon la dernière semaine 53 s'est terminée le 3 janvier dernier. Tu as quelques années pour faire face à la prochaine.

Bonne soirée.

Le 05/03/2016 à 16:43

MFerrand, le roi de la formule... et ce n'est pas qu'une formule... de respect!

Chapeau pour avoir compris sans quasi d'explication!

A+

Le 05/03/2016 à 16:53

haha oui il est fort! Mais il est a la Réunion ca compte pas. 8)

Mais ta formule MFerrand remplace quelle partie de la mienne? L’intégralité ?

Je viens de copier

=IF(OR(H40>,H41<=H40),IF(H40>4,MIN(MAX(ENT((H41+3)/4)*4,4),H40)-H41,H40-H42),"")

dans Excel qui m'indique un message d'erreur..., même message avec le point virgule au lieu de la virgule...

Le 05/03/2016 à 17:02

Essaye avec des SI et des OU si ton Excel est en français!

Il suffit parfois d'une bêtise!

A+

Le 05/03/2016 à 17:02

Manque un 0 au début : H40>0

Mais pourquoi donc La Réunion ne compterait pas ! ?

Bonsoir à tous

Le 05/03/2016 à 17:03

Bonjour,

Pas cherché à analyser, mais oui pour les virgules...

A minima il manque le 0 (zéro)

=IF(OR(H40>0,H41<=H40),IF(H40>4,MIN(MAX(ENT((H41+3)/4)*4,4),H40)-H41,H40-H42),"")

Oups... Sorry ! Salut Ferrand !

A+

Le 05/03/2016 à 17:09

Hello galopin ! T'étais en vacances ?

Le 05/03/2016 à 17:29

La Réunion? Parce que habiter un endroit si beau doit rendre créatif, et pas seulement sur Excel non?

Alors merci encore pour la réponse, et j'inclus le complément de galopin, mais j'ai peur de vous dire que maintenant, j'ai cette horrible erreur #name quand je tape:

=IF(OR(H40>0,H41<=H40),IF(H40>4,MIN(MAX(ENT((H41+3)/4)*4,4),H40)-H41,H40-H42),"")

Et pas de volcan a proximité pour jeter l'ordi dedans..............

Le 05/03/2016 à 17:49

Bonjour Amaia,

je te répète ce que je t'ai dit tout à l'heure!

Je vois deux "langues" dans la formule : des IF anglais et un ENT français.

Si je mélange dans une de mes formules chez moi, ça coince aussi!

Si ton Excel est en français, essaie : SI, OU, ENT

En anglais : IF, OR, INT

A+

Le 05/03/2016 à 17:55

Bonjour Curulis,

merci je devenais chèvre, je ne l avais pas vu!!! Je crois que j'ai besoin d'air...

Je vais tester tout de suite

Le 05/03/2016 à 18:24

Merci ça marche, c'est fantastique d'avoir pu résumer cette formule en une ligne si superbe.

Bravo MFerrand et merci.

Merci aussi a Curulis et Galopin pour leur aide précieuse quant a la mise en œuvre la solution de MFerrand.

Le 06/03/2016 à 10:33

Désolé d'avoir lâché la discussion hier... Il m'arrive de dormir

Bon dimanche à tous.

Rechercher des sujets similaires à "pourriez simplifier cette formule"