LIMITATION Somme avec des SI imbriques

[Babou]

Bonjour,
J'ai des formules qui sont du type : SUM(IF(IF(IF(IF.....
Or, je viens de remarquer qu'il y a une limitation dans le nombre de IF que je peux imbriquer : 6 !
Si je rajoute un 7eme critere, un message d'erreur lorsque je valide la formule a l'aide de la sequence CTRL-SHIFT-ENTREE...
Quelqu'un aurait-il une idee ? Comment contourner cette limitation ?
Merci de votre aide.
Babou.

 

[Gérard POEZEVARA]

Bonjour,
plusieurs solutions

tu utilises des "noms" où tu mets des calculs (ou des références) dans "fait référence à" et tu utilises les noms dans ton calcul à la place des cellules

tu utilises des sous - calculs

La fonction SOMME.SI est peut être ta solution

Gérard

 

[Babou]

Bonjour Gerard,
Quand tu dis references, tu penses a des zones de nommages, ou alors s'agit-il de calculs intermediaires stp ?
Quant au SOMME.SI, j'y ai pense, mais je n'ai qu'un critere de choix possible, alors que j'en ai au-moins 7... Peut-etre ai-je mal compris ta reponse ?
J'attends de tes nouvelles.
Merci de ton aide.
Babou.

 

[Monique]

Bonsoir,

On peut utiliser jusqu'à 7 conditions et, à l'intérieur de chaque condition, mettre des sous-conditions.
Voici un exemple de formule conditionnelle avec seulement 3 conditions principales
mais avec 7 sous-conditions et même une sous-sous-condition, tant qu'à faire.

=SI(ET(A2>=B2;A2<=debut_tarif;A2<>0;B2<>0);MOD(fin_tarif-debut_tarif;1)-SI(B2<=fin_tarif;fin_tarif-B2)+SI(A2<=fin_tarif;fin_tarif-A2);SI(ET(A2>=B2;A2>debut_tarif;A2<>0;B2<>0);MOD(fin_tarif-debut_tarif;1)-(A2-debut_tarif)+SI(B2>=debut_tarif;B2-debut_tarif)-SI(B2<fin_tarif;fin_tarif-B2);SI(ET(A2<B2;ESTNUM(A2);B2<>0);0+SI(ET(A2<=fin_tarif;B2<=fin_tarif);B2-A2)+SI(ET(A2<=fin_tarif;B2>fin_tarif);fin_tarif-A2)+SI(B2>=debut_tarif;B2-A2-SI(A2<=debut_tarif;debut_tarif-A2));""/"")))

Calcul du nombres d'heures (de nuit, par exemple), comprises dans une jounée de travail < ou = à 24 heures. En A2 : début de la journée de travail, en B2 : fin de la journée de travail.

 

[Monique]

Bonsoir,

présentation revue :

=SI(ET(A2>=B2;A2<=debut_tarif;A2<>0;B2<>0);MOD(fin_tarif-debut_tarif;1)
-SI(B2<=fin_tarif;fin_tarif-B2)
+SI(A2<=fin_tarif;fin_tarif-A2);
SI(ET(A2>=B2;A2>debut_tarif;A2<>0;B2<>0);
MOD(fin_tarif-debut_tarif;1)-(A2-debut_tarif)
+SI(B2>=debut_tarif;B2-debut_tarif)
-SI(B2<fin_tarif;fin_tarif-B2);
SI(ET(A2<B2;ESTNUM(A2);B2<>0);0
+SI(ET(A2<=fin_tarif;B2<=fin_tarif);B2-A2)
+SI(ET(A2<=fin_tarif;B2>fin_tarif);fin_tarif-A2)
+SI(B2>=debut_tarif;B2-A2
-SI(A2<=debut_tarif;debut_tarif-A2));
""/"")))

 

[Jean-Marie]

Bonsoir Babou,

Heuuuuu! j'ai pas tout compris, cependant il me semble que la solution passe par l'utilisation de la fonction somme.si(), associèe à la concaténation des données.

Momo l'imbrication de tes si est sans doute génial mais difficile à comprendre et surtout à modifier. Désolé pour cette critique .... et avec une zone matricielle ?

Babou un petit fichier d'exemple joint serait utile...

@+Jean-Marie

 

[Babou]

Monique, Jean-Marie,
Desole pour la reponse tardive masi ai eu quelques pb de connexions depuis ce matin 😱(
Voici un fichier concocte par mes soins avec un exemple (voir la feuille calcul). J'aimerais rajouter un 7eme critere de test et c'est la ou je suis coince...
Merci encore de votre aide.
J'attends de vos nouvelles avec impatience !
Babou.

 

[Monique]

Bonjour,

J'ai essayé d'ajouter une 7è condition : pas moyen.
Avec la fonction =SOMMEPROD(SI((plage a=critère 1)*(plage b=critère 2)*(etc)*(etc);1)) on n'est pas coincé par le nombre de conditions.
En nommant les plages au lieu d'utiliser la fonction INDIRECT(), ça peut donner :
=SOMMEPROD(SI((col_a>0)*(col_a<=300)*(col_b="toto")*(col_d="jm")*(col_e>100)*(col_e<=300)*(col_f>5)*(col_f<=10);1))
Formule matricielle à valider en appuyant simultanément sur ctrl + maj + entrée.
Les plages doivent avoir une taille identique.

 

[michel]

bonsoir,
je viens de lire votre message de réponse, et la formule que vous donnez est très intéressante quand on veut calculer des heures. par contre je cherche une formule me permettant de calculer des heures de jours et des heures de nuits (et, pour compliquer un peu), il y a 45 mn de repas a déduire. quand on utilise votre formule et que l'on change les > en < et que l'on colle votre formule + la nouvelle, on arrive à pouvoir passer des horaires de jour a ceux de la nuit. mais il me manque les 45mn de repas que je ne sais pas où coller. Pouvez vous m'aider?

 

[Babou]

Que penses-tu d'une condition du style "ET" ?
Babou

 

[Monique]

Bonjour,

La fonction SOMMEPROD(SI(()*()*()*();1)) inclut à elle toute seule autant de conditions que l'on veut.
Cette fonction ne fait pas le produit de la somme de quoi que ce soit.
Elle additionne selon conditions, en fait.
Le nombre de conditions n'est pas limité : c'est appréciable.
Je pense que l'on peut dire que le signe * remplace le "ET" des formules conditionnelles classiques.
Si tu veux utiliser "ET" dans ce genre de calcul, je ne vois pas la solution.
Liens qui te seront peut-être utiles :
<http://www.excel-downloads.com/html/French/forum/messages/1_7801_7801.htm>
<http://www.excel-downloads.com/html/French/forum/messages/1_7344_7344.htm>
Bonne journée.

 

[Babou]

Merci Monique. Je vais esayer avec la fonction SOMMEPROD. La seule chose qui me genait etait cette multiplication matricielle. Mais peut-etre ne suis-je pas alle au fin fond de la fonction... Je vais tester et reviens vers toi.
Merci encore.
Babou.

 

[Babou]

Et voila...
Ca marche du feu de Dieu cette fonction SOMMEPROD. Je l'ai adaptee a mon exemple, avec l'utilisation de formules INDIRECT car, vu ma quantite de donnees, je ne peux pas gerer des zones nommees...
Je le mets en ligne au cas quelqu'un en aurait l'utilite !
J'hesite un peu a l'appliquer aux 100 autres formules que j'ai deja ecrites (principalement des SUM(IF(IF....
Je pense que je vais attendre de rencontrer ce probleme avec ces worksheets avant de me lancer 😱)
Merci encore Monique pour ta solution !
Babou, Heureux 😱)

 

[Monique]

Bonjour,
Réponse tardive : je n'ai pas pu me connecter hier après-midi.
Pour calculer la durée du temps de travail, il y a quand même plus simple.
Cette formule calcule la durée d'une plage horaire incluse à l'intérieur d'une autre
plage horaire.
(avec possibilité de commencer à n'importe quelle heure du jour ou de la nuit,
dans la limite d'une amplitude de 24 heures)
Voici des formules plus courtes, l'une calculant l'amplitude,
l'autre calculant la durée du temps de travail, une fois ôtées le temps de repas.
(Avec toujours l'éventualité de commencer et de finir à n'importe quelle heure)
Bon courage.

 

[michel]

monique, bonjour,
je vous remercie de votre aide très précieuse. je vais pouvoir me jeter à corp perdu pour construire ce tableau, qui, cette année était d'une simplicité enfantine (+ -...). puis-je, si le besoin se fait sentir, faire appel à vos connaissances?
je vous souhaite une agréable journée et vous remercie encore

Michel