[Résolu][Calc]optimisation de formules via fonctions perso

Discussions et questions sur tout ce qui concerne la programmation tous langages et tous modules confondus.

Modérateur : Vilains modOOs

Règles du forum
:alerte: Balisage obligatoire dans cette section !
Aidez-nous à vous aider au mieux en balisant correctement votre question : reportez-vous sur les règles de cette section avant de poster !
mullinski
Membre lOOyal
Membre lOOyal
Messages : 32
Inscription : 09 août 2009 01:43

[Résolu][Calc]optimisation de formules via fonctions perso

Message par mullinski »

Bonjour à tous,

avant tout, je tiens à m'excuser si je n'écris pas ce message au bon endroit, sachant qu'il traite à la fois de formules dans calc ET de programmation.

Pour diverses raisons, je me retrouve avec une formule de plus de 3000 caractères avec entre autres des SI imbriquées et des INDIRECT(ADRESSE(...)) vers une feuille contenant des constantes ou vers des feuilles précédentes. Cette formule se retrouve dans chaque cellule d'une colonne de plus de 300 lignes. Cette colonne se retrouve elle-même dans une quarantaine de groupe de colonnes. Et tout ceci se trouve sur une feuille. Selon différentes conditions, la formule récupère des valeurs de la feuille courante ou de la feuille précédente. Une fois que j'aurai fini mes tests, la dernière feuille sera dupliquée autant de fois que nécessaire, et sera capable de récupérer des valeurs de la feuille précédente, en fonction des valeurs des autres colonnes. Tout ceci marche parfaitement bien... mais ça rame, ce qui bien sûr ne me surprend pas :oops: .

Je précise que j'utilise des plages nommées autant que possible, mais les calculs se faisant principalement en fonction des valeurs précédentes ou adjacentes à la cellule, je ne vois pas comment en utiliser plus.

En outre, j'ai écris diverses macros qui ne modifient pas le contenu des cellules mais qui simplement me permettent d'afficher ou masquer des colonnes selon mes besoins (saisis ou consultation d'informations). Ces macros désactivant et réactivant le calcul automatique le temps de leur exécution afin de les rendre plus rapide.

Mon objectif actuel consiste à simplifier/optimiser cette formule complexe, afin :
  • d'une part qu'elle soit plus lisible (actuellement je travaille dessus dans notepad++ en la mettant en forme et avec des commentaires pour qu'elle soit lisible puis je supprime la mise en forme et les commentaires et je la colle pour la tester :oops: )
  • et d'autre part qu'elle prenne moins de temps de calcul.
Voilà pourquoi, j'aurai trois une questions d'ordre général :
  1. Est-ce que rajouter des colonnes contenant des calculs intermédiaires pourrait réduire le temps de traitement ? Pour la lisibilité, éventuellement pourquoi pas, mais je crains de me retrouver avec quinze colonnes supplémentaires pour chaque groupe de colonnes :roll: .
  2. Je sais à priori que je peux passer pas une fonction personnalisée pour réduire cette formule dans calc et la rendre plus lisible via des commentaires dans l'éditeur basic de l'IDE. Mais pour une question de praticité et d'efficacité en temps de traitement, je ne sais pas s'il vaut mieux que je la récrée totalement en basic, ou si je dois appeler en basic les fonctions utilisés dans cette formule à l'aide de la fonction callFunction. J'espère que j'arrive à me faire comprendre... J'avoue appréhender cette méthode, sachant que j'ai l'impression que cette fonction personnalisée risque d'avoir de nombreux arguments à faire passer, ce qui au final ne rendra pas forcément plus lisible la formule dans calc... D'ailleurs il me faudrait peut-être plutôt utiliser plusieurs fonctions personnalisées au lieu d'une seule.
  3. La longueur de la formule tient entre autre au fait que je teste des valeurs qui selon leur résultats seront ou non utilisées comme résultat. Autrement dit, du genre SI(valeur1>valeur2;valeur1+x;valeur2+y). Sachant que valeur1 et valeur2 contiennent elle-mêmes d'autres fonctions. Du coup je me retrouve a écrire et calculer plusieurs fois valeur 1 et/ou valeur 2 dans la formule. Je me dis que je gagnerais en lisibilité et en longueur de formule si je pouvais écrire quelques chose du genre SI(valeur1>valeur2;$0+x;$1+y) ou $0 ferait référence à valeur1 et $1 à valeur2, un peu comme dans une expression régulière. J'ai cherché, mais ça ne semble pas possible. Je me dis aussi que ça réduirait le temps de traitement dans le cas des SI imbriqués, puisque d'après ce que j'ai compris, dans le cas d'un SI, tous les arguments sont testés, même si seuls ceux correspondant aux critères sont renvoyés. Finalement la seule solution semble de créer deux colonnes dans lesquelles seraient calculées valeur1 et valeur2... En somme je réponds moi-même à deux questions :marto: . Mais ça vient probablement du fait que j'ai initialement tout fait pour réduire au maximum le nombre de colonne...
Voilà, j'espère avoir été suffisamment clair. J'ai préféré de ne pas en écrire davantage, mes questionnements étant d'ordre général, et pour éviter de vous noyer avec trop de détails. Si besoin, je vous en donnerais.

Et pour ceux qui en douterait, je précise que ma signature est à jour :D
Dernière modification par mullinski le 17 sept. 2019 15:16, modifié 3 fois.
LibreOffice 7.5.7.1 Windows 64-bit
Avatar de l’utilisateur
Beetle
MOOdérateur
MOOdérateur
Messages : 15
Inscription : 23 mars 2018 22:42

Re: [calc] généralité : optimisation de formules via macro ?

Message par Beetle »

Bonjour

Plusieurs remarques à votre message:
  • Veuillez rectifier la balise précédent votre titre indiquant le module employé: il s'agit de [Calc] avec une majuscule, et non de [calc].
  • Le terme "macro" dans ce titre est implicite puisque vous vous trouvez en section... macro et API.
    Là aussi, il faudra modifier votre titre pour en trouver un plus pertinent, afin de permettre aux autres utilisateurs du forum ayant la même problématique et qui effectueraient une recherche par mots-clefs, trouvent aisément réponse à leurs questions.
    Voici des astuces pour avoir un titre optimum : http://forum.openoffice.org/fr/forum/vi ... 1002#p1002
    Merci donc de le modifier au plus tôt. Si cela n'est pas fait avant votre prochain message, le sujet sera fermé.
  • mullinski a écrit :j'aurai trois questions
    Rappel de la règle n°7: une seule question par fil. A vous de diviser en autant de sujets, pour éviter le "fouillis" dans les réponses que les bénévoles pourraient vous apporter.
  • mullinski a écrit :je précise que ma signature est à jour
    mullinski a écrit :LibreOffice 5.4.7.2
    Nous vous rappelons qu'il est important d'être à jour de la dernière version. Aujourd'hui, la dernière version "stable" est la 6.2. disponible ici.
    Si vous n'avez pas la possibilité de télécharger la dernière version, il vous appartient de mentionner "obligation de version" dans votre signature.
Cordialement
Avatar de l’utilisateur
Dude
IdOOle de la suite
IdOOle de la suite
Messages : 25145
Inscription : 03 mars 2006 07:45
Localisation : 127.0.0.1

Re: [Calc] optimisation de formules via fonctions personnali

Message par Dude »

Salut,
mullinski a écrit :je me retrouve avec une formule de plus de 3000 caractères avec entre autres des SI imbriquées et des INDIRECT(ADRESSE(...))
Comme toujours, avant de partir dans de la programmation, on regarde si ta formule ne peut pas être simplifiée.
Donc, tu fournis un document exhaustif illustrant ton problème afin qu'on puisse l'étudier.
mullinski
Membre lOOyal
Membre lOOyal
Messages : 32
Inscription : 09 août 2009 01:43

Re: [Calc] optimisation de formules via fonctions personnali

Message par mullinski »

Bonjour Dude,

moi je veux bien, mais justement je ne comptais pas prendre autant de temps pour expliquer tout le document et réciproquement à ne pas vous faire perdre du temps pour l'analyser.

Pour l'instant, je vais essayé d'illustrer ma question.

Si je prends l'exemple de cette simple formule :

Code : Tout sélectionner

=SI(INDIRECT(ADRESSE(plageNommee1;COLONNE();2))>plageNommee2-7*4;E1;E2)
Si je la remplace par

Code : Tout sélectionner

=fonctionPersonnalisee(plageNommee1;COLONNE();plageNommee2;E1;E2)
Je me demande simplement quelle est la meilleure méthode pour écrire la fonction personnalisée :
  1. en récupérant les cellules à tester avec des fonctions de l'API
  2. ou en récupérant les cellules à tester avec la fonction callFunction qui appellera les fonctions INDIRECT et ADRESSE
Je précise que je n'ai pas fait d'essai et peut-être qu'en essayant je me rendrais compte que seule l'une des méthodes est possible. D'où ma question très générale et peut-être idiote...

S'il me faut changer le titre du sujet par "[Calc] méthode de récupération de valeur de cellule", prévenez-moi. Cela tient au fait que mes trois questions initiales portaient sur l'utilisation de plusieurs fonctions : celles de libreOffice et celles personnalisées.
LibreOffice 7.5.7.1 Windows 64-bit
Avatar de l’utilisateur
Dude
IdOOle de la suite
IdOOle de la suite
Messages : 25145
Inscription : 03 mars 2006 07:45
Localisation : 127.0.0.1

Re: [Calc] optimisation de formules via fonctions personnali

Message par Dude »

Bis repetitae :
Dude a écrit :Donc, tu fournis un document exhaustif illustrant ton problème afin qu'on puisse l'étudier.
Car nous donner les formules sans aucune donnée, comment penses-tu qu'on puisse comprendre ?
C'est comme donner une recette sans préciser les ingrédients. :roll:
mullinski
Membre lOOyal
Membre lOOyal
Messages : 32
Inscription : 09 août 2009 01:43

Re: [Calc] optimisation de formules via fonctions personnali

Message par mullinski »

Dude a écrit :Car nous donner les formules sans aucune donnée, comment penses-tu qu'on puisse comprendre ?
C'est comme donner une recette sans préciser les ingrédients.
Bien sûr Dude, je comprends ton argument. Mais en fait, mon objectif n'est pas de vous faire perdre votre temps (et le mien) à vous faire comprendre le pourquoi de ma formule et de mes besoins. il s'agit plutôt de techniques à appliquer pour l'optimiser.

Je vous donne un autre exemple pour illustrer cela :

Code : Tout sélectionner

=SI(argument1<argument2;argument1;argument2+diversCalculs)
Imaginons que argument1=INDIRECT(ADRESSE(ligneCalculée1;colonneCalculée1);2)) et argument2=INDIRECT(ADRESSE(ligneCalculée2;colonneCalculée2);2))
ce qui donne la formule déjà complexe :

Code : Tout sélectionner

=SI(INDIRECT(ADRESSE(ligneCalculée1;colonneCalculée1);2))<INDIRECT(ADRESSE(ligneCalculée2;colonneCalculée2);2));INDIRECT(ADRESSE(ligneCalculée1;colonneCalculée1);2));INDIRECT(ADRESSE(ligneCalculée2;colonneCalculée2);2))+diversCalculs)
est-ce que par hasard il existerait une solution pour ne pas écrire deux fois dans la formule argument1 et argument2 mais quelques chose du genre :

Code : Tout sélectionner

=SI(argument1<argument2;$0;$1+diversCalcul)
un peu comme dans les expressions régulières en fait :D . Même si je comprends bien que c'est différent. A priori je ne pense pas que ce soit possible. Et dans le cas contraire, je ne vois pas comment procéder.

Cet exemple aurait selon moi deux avantages :
  1. réduire le nombre de caractère de la formule
  2. ne calculer argument1 et argument2 qu'une seule fois dans la formule
Si ce n'est pas possible, est-ce que je n'aurais pas intérêt à écrire une fonction personnalisée et l'appeler de la façon suivante :

Code : Tout sélectionner

=fontionPersonnalisée(argument1;argument2)
Vous voyez ce que je veux dire :) ? Mes demandes sont plus d'ordre théorique en fait : connaître des théories/règles d'optimisation ou d'écriture, que je pourrais ensuite mettre en pratique :super: .

Après, si malgré tout cela je sèche, alors, oui : je pourrais vous faire passer le fichier en prenant le temps de le documenter pour que vous essayiez de trouver des solutions alternatives/complémentaires à mes formules actuelles.

Désolé si cela vous paraît frustrant ou aberrant...
LibreOffice 7.5.7.1 Windows 64-bit
Avatar de l’utilisateur
OOotremer971
ManitOOu
ManitOOu
Messages : 2744
Inscription : 16 avr. 2010 13:31

Re: [Calc] optimisation de formules via fonctions personnali

Message par OOotremer971 »

Bonjour,
mullinski a écrit :mon objectif n'est pas de vous faire perdre votre temps (et le mien) à vous faire comprendre le pourquoi de ma formule et de mes besoins.
Et pourtant c'est bien ce que tu fais en nous obligeant à lire et relire le même discours stérile dont tu n'obtiendras la réponse qu"après analyse de ton fichier. :evil:

A+
En principe, toujours à jour des dernières versions dites stables
AOO
LibreOffice
Debian 10 et 11
mullinski
Membre lOOyal
Membre lOOyal
Messages : 32
Inscription : 09 août 2009 01:43

Re: [Calc] optimisation de formules via fonctions personnali

Message par mullinski »

OOotremer971 a écrit :Bonjour,
mullinski a écrit :mon objectif n'est pas de vous faire perdre votre temps (et le mien) à vous faire comprendre le pourquoi de ma formule et de mes besoins.
Et pourtant c'est bien ce que tu fais en nous obligeant à lire et relire le même discours stérile dont tu n'obtiendras la réponse qu"après analyse de ton fichier. :evil:

A+
Ok. Je vois. Je suis désolé. Je reviendrais peut-être quand je serais prêt. Ne pouvant pas supprimer le sujet, je le balise en [Résolu] ([HS] serait peut-être mieux ? Je n'en sais rien). Dans l'idéal supprimez-le : si je reviens, je créerai plutôt un nouveau sujet. Merci.
LibreOffice 7.5.7.1 Windows 64-bit
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 09:40
Localisation : France

Re: [Résolu][Calc]optimisation de formules via fonctions per

Message par Jeff »

Bonjour à tous,

Il faudrait a minima un classeur-test :wink:

A priori, avec une formule de 3000 caractères, soit les fonctions employées ne le sont pas à bon escient, soit il manque des cellules intermédiaires, soit Calc n’est pas l’outil adapté :aie:

A +