halecs93
XLDnaute Occasionnel
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.
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.