simplifier une longue...très longue formule

[halecs93]

Bonjour à toutes et à tous....

Grâce aux conseils donnés sur le forum, j'ai construit ce fichier (pièce-jointe).... mais mes formules sont bien longues....par exemple....

=(NB.SI($D$6;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$4;" ";"");TROUVE("-";SUBSTITUE($D$4;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$4;" ";"");TROUVE("-";SUBSTITUE($D$4;" ";""))-1);1);0)))+(NB.SI($D$10;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$8;" ";"");TROUVE("-";SUBSTITUE($D$8;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$8;" ";"");TROUVE("-";SUBSTITUE($D$8;" ";""))-1);1);0)))+(NB.SI($D$14;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$12;" ";"");TROUVE("-";SUBSTITUE($D$12;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$12;" ";"");TROUVE("-";SUBSTITUE($D$12;" ";""))-1);1);0)))+(NB.SI($D$18;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$16;" ";"");TROUVE("-";SUBSTITUE($D$16;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$16;" ";"");TROUVE("-";SUBSTITUE($D$16;" ";""))-1);1);0)))+(NB.SI($D$22;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$20;" ";"");TROUVE("-";SUBSTITUE($D$20;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$20;" ";"");TROUVE("-";SUBSTITUE($D$20;" ";""))-1);1);0)))+(NB.SI($D$26;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$24;" ";"");TROUVE("-";SUBSTITUE($D$24;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$24;" ";"");TROUVE("-";SUBSTITUE($D$24;" ";""))-1);1);0)))+(NB.SI($D$30;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$28;" ";"");TROUVE("-";SUBSTITUE($D$28;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$28;" ";"");TROUVE("-";SUBSTITUE($D$28;" ";""))-1);1);0)))+(NB.SI($D$34;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$32;" ";"");TROUVE("-";SUBSTITUE($D$32;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$32;" ";"");TROUVE("-";SUBSTITUE($D$32;" ";""))-1);1);0)))+(NB.SI($D$38;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$36;" ";"");TROUVE("-";SUBSTITUE($D$36;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$36;" ";"");TROUVE("-";SUBSTITUE($D$36;" ";""))-1);1);0)))+(NB.SI($D$42;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$40;" ";"");TROUVE("-";SUBSTITUE($D$40;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$40;" ";"");TROUVE("-";SUBSTITUE($D$40;" ";""))-1);1);0)))+(NB.SI($D$46;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$44;" ";"");TROUVE("-";SUBSTITUE($D$44;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$44;" ";"");TROUVE("-";SUBSTITUE($D$44;" ";""))-1);1);0)))+(NB.SI($D$50;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$48;" ";"");TROUVE("-";SUBSTITUE($D$48;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$48;" ";"");TROUVE("-";SUBSTITUE($D$48;" ";""))-1);1);0)))+(NB.SI($D$54;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$52;" ";"");TROUVE("-";SUBSTITUE($D$52;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$52;" ";"");TROUVE("-";SUBSTITUE($D$52;" ";""))-1);1);0)))+(NB.SI($D$58;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$56;" ";"");TROUVE("-";SUBSTITUE($D$56;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$56;" ";"");TROUVE("-";SUBSTITUE($D$56;" ";""))-1);1);0)))+(NB.SI($D$62;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$60;" ";"");TROUVE("-";SUBSTITUE($D$60;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$60;" ";"");TROUVE("-";SUBSTITUE($D$60;" ";""))-1);1);0)))+(NB.SI($D$66;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$64;" ";"");TROUVE("-";SUBSTITUE($D$64;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$64;" ";"");TROUVE("-";SUBSTITUE($D$64;" ";""))-1);1);0)))+(NB.SI($D$70;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$68;" ";"");TROUVE("-";SUBSTITUE($D$68;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$68;" ";"");TROUVE("-";SUBSTITUE($D$68;" ";""))-1);1);0)))+(NB.SI($D$74;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$72;" ";"");TROUVE("-";SUBSTITUE($D$72;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$72;" ";"");TROUVE("-";SUBSTITUE($D$72;" ";""))-1);1);0)))+(NB.SI($D$78;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$76;" ";"");TROUVE("-";SUBSTITUE($D$76;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$76;" ";"");TROUVE("-";SUBSTITUE($D$76;" ";""))-1);1);0)))+(NB.SI($D$82;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$80;" ";"");TROUVE("-";SUBSTITUE($D$80;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$80;" ";"");TROUVE("-";SUBSTITUE($D$80;" ";""))-1);1);0)))+(NB.SI($D$86;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$84;" ";"");TROUVE("-";SUBSTITUE($D$84;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$84;" ";"");TROUVE("-";SUBSTITUE($D$84;" ";""))-1);1);0)))+(NB.SI($D$90;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$88;" ";"");TROUVE("-";SUBSTITUE($D$88;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$88;" ";"");TROUVE("-";SUBSTITUE($D$88;" ";""))-1);1);0)))+(NB.SI($D$94;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$92;" ";"");TROUVE("-";SUBSTITUE($D$92;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$92;" ";"");TROUVE("-";SUBSTITUE($D$92;" ";""))-1);1);0)))+(NB.SI($D$98;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$96;" ";"");TROUVE("-";SUBSTITUE($D$96;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$96;" ";"");TROUVE("-";SUBSTITUE($D$96;" ";""))-1);1);0)))+(NB.SI($D$102;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$100;" ";"");TROUVE("-";SUBSTITUE($D$100;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$100;" ";"");TROUVE("-";SUBSTITUE($D$100;" ";""))-1);1);0)))+(NB.SI($D$106;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$104;" ";"");TROUVE("-";SUBSTITUE($D$104;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$104;" ";"");TROUVE("-";SUBSTITUE($D$104;" ";""))-1);1);0)))+(NB.SI($D$110;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$108;" ";"");TROUVE("-";SUBSTITUE($D$108;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$108;" ";"");TROUVE("-";SUBSTITUE($D$108;" ";""))-1);1);0)))+(NB.SI($D$114;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$112;" ";"");TROUVE("-";SUBSTITUE($D$112;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$112;" ";"");TROUVE("-";SUBSTITUE($D$112;" ";""))-1);1);0)))+(NB.SI($D$118;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$116;" ";"");TROUVE("-";SUBSTITUE($D$116;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$116;" ";"");TROUVE("-";SUBSTITUE($D$116;" ";""))-1);1);0)))+(NB.SI($D$122;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$120;" ";"");TROUVE("-";SUBSTITUE($D$120;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$120;" ";"");TROUVE("-";SUBSTITUE($D$120;" ";""))-1);1);0)))+(NB.SI($D$126;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$124;" ";"");TROUVE("-";SUBSTITUE($D$124;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$124;" ";"");TROUVE("-";SUBSTITUE($D$124;" ";""))-1);1);0)))+(NB.SI($D$130;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$128;" ";"");TROUVE("-";SUBSTITUE($D$128;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$128;" ";"");TROUVE("-";SUBSTITUE($D$128;" ";""))-1);1);0)))+(NB.SI($D$134;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$132;" ";"");TROUVE("-";SUBSTITUE($D$132;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$132;" ";"");TROUVE("-";SUBSTITUE($D$132;" ";""))-1);1);0)))+(NB.SI($D$138;$A140)*(SIERREUR(MOD(STXT(SUBSTITUE($D$136;" ";"");TROUVE("-";SUBSTITUE($D$136;" ";""))+1;8)-GAUCHE(SUBSTITUE($D$136;" ";"");TROUVE("-";SUBSTITUE($D$136;" ";""))-1);1);0)))


Quelqu'un aurait une idée pour les simplifier ?

Grand merci.

 

[Lolote83]

Re : simplifier une longue...très longue formule

Salut HALESC93,
Tout d'abord, FELICITATION pour ces formules. Que c'est difficile de s'y retrouver si une erreur est glissée la dedans.
Pour ma part, je procèderais de la façon suivante.
En insérant une colonne à chaque fois pour y inscrire
-Dans la première cellule = l'heure de début (HD)
-Dans la seconde cellule = l'heure de fin (HF)
-Dans la cellule rajoutée (ici prise sur celle vide), la différence entre HF-HD
Donc en fin de tableau (Recap par semaine) on a plus qu'à faire une somme (formule moins longue)
De plus, cela à l'avantage d'éviter les erreurs de saisie pour y inscrire les tirets entre tes horaires.
Bref, celma va donner pas mal de boulot pour tout reprendre, mais cela en vaut peut être la peine
Voir onglet MODELE2
Bon courage
@+ Lolote83

 

[mapomme]

Re : simplifier une longue...très longue formule

Bonjour halecs93, Lolote83,

Si j'ai bien deviné ce que fait la formule en AW3, on peut raccourcir la formule en utilisant une formule matricielle en AW3:

=SOMMEPROD(($D$2:$AT$2=AW$2)*SIERREUR(SUPPRESPACE(STXT($D4:$AT4;TROUVE("-";$D4:$AT4)+1;9))-SUPPRESPACE(GAUCHE($D4:$AT4;TROUVE("-";$D4:$AT4)-1));0))

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.
Une formule matricielle peut être copiée et coller - la copie sera aussi une formule matricielle

ATTENTION ! Vous utilisez des cellules fusionnées. On ne peut pas saisir de formules matricielles dans des cellules fusionnées. il faut donc :

1. enlever la fusion des cellules AW3 à AZ3
2. coller la formule ci-dessus dans la cellule AW3 et validez par Ctrl+Maj+Entrée
3. copier la cellule AW3 pour faire un collage spécial 'Formule" dans les cellules AX3 à AZ3
4. fusionner à nouveau les cellules AW3 et AW4, AX3 et AX4, ...
5. on peut ensuite copier les cellules AW3:AZ3 pour les coller dans AW7, AW11,..., AW135

 

[halecs93]

Re : simplifier une longue...très longue formule

Merci.... mais c'est volontairement que je ne veux inscrire les horaires que dans une colonne

 

[halecs93]

Re : simplifier une longue...très longue formule

Bonjour...

Je regarderai cela un peu plus tard. Mais ça semble convenir.

Merci