Solutions pour une "formule trop longue"

Le 19/03/2015 à 15:57

Bonjour Tout le monde,

Est ce que de visu comme ça quelqu'un peut me dire/proposer une solution pour racourcir ces 2 formules car elles ne rentrent pas dans une cellule ==> Message d'erreur.

Remarque 1 : La préparation d'un fichier anonymisé sous excel me demandera bcp de temps car je travaille sous calc mais s'il le faut je le ferai et le posterai.

Remarque 2 : Bienque ce soit des formules issues du tableur Calc, je poste mon message ici afin d'avoir plus de chances d'obtenir une réponse étant donné que la réduction/contraction dont il est question ici n'est pas spécifique à calc. Juste l'idée. Après je l'adapterai.... ( enfin je pense)

Remarque 3 Le nommage ne repond pas aux besoins car il s'agit des Mises en Forme Conditionnelle dans un tableau dont le contenu des cellules varie d'une part et d'autre part, le nombre de lignes et de colonnes est fluctuant. C'est la raison d'être meme de cet outil. En clair, avec les nommages, la MFC ne se met pas à jour lorsqu'on insère par exemple de nouvelles lignes dans le tableau.

Il s'agit des formules que je vais encapsuler avec la fonction style() de calc qui n'existe pas sous excel. Bref.

Formule 1 ==> TempsGras

SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); DONNEES.$I$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*");  "ST-TempsFond-C_Gris40-Gras" ;          
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);  DONNEES.$AP$6; DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*");   "ST-TempsFond-C_Gris40-Gras" ;  
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);  DONNEES.$BO$6; DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*");   "ST-TempsFond-C_Gris40-Gras" ;
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);  DONNEES.$BT$6; DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*");   "ST-TempsFond-C_Gris40-Gras" ;
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);DONNEES.$AD$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*"); "ST-TempsFond-C_Turquoise2-Gras";
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);DONNEES.$AI$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*"); "ST-TempsFond-C_Turquoise2-Gras";
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);DONNEES.$AO$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*"); "ST-TempsFond-C_GrisBleu-Gras";
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);DONNEES.$AJ$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*"); "ST-TempsFond-C_GrisBleu-Gras";
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);DONNEES.$AC$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*"); "ST-TempsFond-C_Bleu8-Gras";  
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10);  DONNEES.$CA$6; DECALER(DONNEES.$D$7;COLONNE()-2;0);".*Temps.*");   "ST-TempsFond-C_Gris40-Gras" ; )))))))

Formule 2 ==> TempsBlanc

SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$I$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AP$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$BO$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$BT$6;DECALER(DONNEES.$D$7;COLONNE()-2;0);
".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AD$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AI$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AO$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AJ$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$AC$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc"; 
SI(NBSIENS(DECALER(DONNEES.$E$6;0;LIGNE()-10); "<>"& DONNEES.$CA$6;DECALER(DONNEES.$D$7;COLONNE()-2;0); ".*Temps.*"); "ST-TempsFondBlanc";  )))))))

Des idées/Pistes?

Marci...

Le 19/03/2015 à 16:23

Cela rentre ... avec quelques corrections de syntaxe !

=SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$I$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Gris40-Gras";SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AP$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Gris40-Gras";SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$BO$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Gris40-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$BT$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Gris40-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AD$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Turquoise2-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AI$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Turquoise2-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AO$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_GrisBleu-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AJ$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_GrisBleu-Gras";
SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$AC$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Bleu8-Gras";SI(NB.SI.ENS(DECALER(DONNEES!$E$6;0;LIGNE()-10);DONNEES!$CA$6;DECALER(DONNEES!$D$7;COLONNE()-2;0);".*Temps.*");"ST-TempsFond-C_Gris40-Gras";
))))))))))

mais il vaut mieux passer par un calcul matriciel plus compact et plus lisible ... à condition de nous expliquer ! car justement ce n'est pas lisible.

Le 19/03/2015 à 19:31

Salut Steelson, ( Et aux autres)

Et merci de t'interesser à ma requête...

Euh.. j'ai bien regardé ton code mais à part sa conversion en version excel ( remplacement de NBSIENS par NB.SI.ENS et de DONNEES.$.. par DONNEES!$.. ) j'ai pas trouvé à quel niveau se trouve la contraction... Peut être que j'ai mal regardé! ^^

Plutôt que d'expliquer ce que je veux faire.. c'est relativement plus simple et bénéfique de mettre un fichier. Justement, ce fichier est en cours de preparation. En fait je suis en train d'adapter la version calc à la version excel. ( une vraie galère...)

Je prends le risque, en attendant la version excel, de poster ici cette version calc. On ne sait jamais... peut être que quelqu'un possède Excel et Calc sur son ordi.

Mon Problème: Le fichier comporte 3 feuilles: DONNEES, RECAP, ATTENDU

=>DONNEES évolue quotidiennement sur une année. (Ah oui l'utilisateur ne veut pas d'ACCESS/BASE)

=>RECAP Permet d'extraire les infos souhaitées en modifiant les dates.

=>ATTENDU est là pour vous montrer ce que je souhaite avoir comme mise en forme conditionnelle ( mfc). Le tout sans macro/vba pour 2 raisons majeurs: -La Lenteur du fichier; -Pour l'utilisateur c'est considéré comme une usine à gaz ...lors des éventuelle maintenance.

Mon souci se situe au niveau de RECAP. Plus précisement au niveau de l'automatisation de la mise en forme de RECAP. Coté Formule de Calcul, tout est parfait. Tout fonctionne bien.

Ce qui est fait :

L'idée est d'utiliser la fonction Style()car je n'ai pas réussi à la MFC via le menu Format>Formatage Conditionnel.

1- J'ai créée plusieurs styles (Cf Format > Style et Formatage)

2- J'ai réussi à automatiser la colonne RECAP.A . cf la formule de cette colonne. ( J'aporterai des améliorations aux caractères discrimants)

3- J'ai écrit les formules pour ces Mises en forme mais elles sont "trop longues".

L'idéal serait d'insérer dans chacune des cellules du tableau RECAP les formules 2,3,4,5,6 et 7!!!

En résumé, je cherche à automatiser RECAP pour avoir ce qui est dans ATTENDU.

222015-03-19-forumxl-stats.ods (154.68 Ko)
Le 19/03/2015 à 21:08
Samoterell a écrit :

Salut Steelson, ( Et aux autres)

Et merci de t'interesser à ma requête...

Euh.. j'ai bien regardé ton code mais à part sa conversion en version excel ( remplacement de NBSIENS par NB.SI.ENS et de DONNEES.$.. par DONNEES!$.. ) j'ai pas trouvé à quel niveau se trouve la contraction... Peut être que j'ai mal regardé! ^^

Non en effet j'ai fait les corrections excel et ajouté une parenthèse manquante je pense ... la "contraction" devrait pourvoir venir d'une formule matricielle compacte.

Le 20/03/2015 à 00:28

Bonjour,

Vue l'heure on va faire le minimum syndical.

Tu pourrais mettre les morceaux de formule comme DECALER(DONNEES!$E$6;0;LIGNE()-10) dans des noms.

  • ça raccourci et facilite la lecture.
  • gain de temps. Elle est évaluée 1 fois au lieu de 10 à 15 fois par formule.
C'est tout benef.

Et sur open office tu ne peux pas enregistrer au format xlsx ?

eric

Le 20/03/2015 à 06:40

Au-delà de l'avis pertinent d'Eriiiic, la simplification de la formule ira de pair avec une structuration différente des données éparses dans le fichier;

Le 23/03/2015 à 16:15

Hello Eric/ Hello Steelson/ Hello les autres lecteurs...

@==> Eriiic:

Proposition interessante ... mais cette solution n'est pas adaptée car le nombre de colonnes dans DONNEES étant ammené à varier (exple : lors d'un ajout d'une nouvelle personne), le nommage ne permet pas cette variation. Pourquoi? Par ce que les references des cellules à l'intérieur des formules nommées ne bougent pas. résultat ==> Pas de MAJ des formules nommées.

Juste pour exemple ..si toutefois un lecteur voudrait essayer le nommage conseillé par Eric, voici la formule:

SI(NBSIENS(DecaLigne; DONNEES.$I$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Gris40-Gras" ; 
SI(NBSIENS(DecaLigne; DONNEES.$AP$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Gris40-Gras" ;   
SI(NBSIENS(DecaLigne; DONNEES.$BO$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Gris40-Gras" ;   
SI(NBSIENS(DecaLigne; DONNEES.$BT$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Gris40-Gras" ;   
SI(NBSIENS(DecaLigne; DONNEES.$AD$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Turquoise2-Gras";   
SI(NBSIENS(DecaLigne; DONNEES.$AI$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Turquoise2-Gras"; 
SI(NBSIENS(DecaLigne; DONNEES.$AO$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_GrisBleu-Gras"; 
SI(NBSIENS(DecaLigne; DONNEES.$AJ$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_GrisBleu-Gras";
SI(NBSIENS(DecaLigne; DONNEES.$AC$6;DecaColonne; "<>"& ".*Temps.*");  "ST-Nb-StdFond-C_Bleu8-Gras"; 
SI(NBSIENS(DecaLigne; DONNEES.$CA$6;DecaColonne; "<>"& ".*Temps.*"); "ST-Nb-StdFond-C_GrisBleu-Gras";))))))))))

@==>Steelson: Le fait que les données soit aussi clairsemée vient des données disponibles au jour J. Cette dispo varie selon les jours. Du coup l'utilisateur a souhaité avoir un grand tableaux afin de pouvoir saisir toutes les donnes susceptibles d'être disponibles.

Une autre solution/ piste?

Sous un autre angle: Je laisse tomber l'utilisation de la fonction style() et je passe par la MFC classique.

Est ce que quelqu'un peut m'aider à passer par le menu Format > Formatage Conditionnel >Condition pour obenir ce qui est dans la feuille ATTENDU? (Cf le fichier que joint plus haut)

Merci...

Rechercher des sujets similaires à "solutions formule trop longue"