Excel: optimisation d'une formule
Résolu/Fermé
boatitude
Messages postés
9
Date d'inscription
lundi 23 avril 2012
Statut
Membre
Dernière intervention
15 mai 2012
-
14 mai 2012 à 22:47
eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 - 16 mai 2012 à 00:00
eriiic Messages postés 24570 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 23 avril 2024 - 16 mai 2012 à 00:00
A voir également:
- Excel: optimisation d'une formule
- Liste déroulante excel - Guide
- Formule excel - Guide
- Si et excel - Guide
- Aller à la ligne excel - Guide
- Mise en forme conditionnelle excel - Guide
2 réponses
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 213
Modifié par eriiic le 15/05/2012 à 00:32
Modifié par eriiic le 15/05/2012 à 00:32
Bonsoir,
C'est vrai qu'elle est infâme ta formule, ça devrait être interdit par les syndicats...
K32 est égal à 212 car à la ligne I185 le chiffre valait 59 et 59*(185-32)*1= 212
Tu peux t'expliquer sur 59*(185-32)*1= 212
Sur mon excel ça fait 9027, j'ai mis + à la place...
Un début, en L32 :
=MIN(220;MIN(DECALER($I$1;LIGNE();0;LIGNE()+I32))+EQUIV(MIN(DECALER($I$1;LIGNE();0;LIGNE()+I32));DECALER($I$1;LIGNE();0;LIGNE()+I32);0)*$H$7)
Mais j'ai un delta avec toi en ligne 188. Tu as 158, moi 220 et je n'en décolle plus pendant de nombreuses lignes.
Sans doute une mauvaise interprétation de tes explications de ma part.
Si tu pouvais détailler le calcul sur cette ligne, il me manque qcq chose mais je ne vois pas quoi. De plus il se fait tard.
eric
C'est vrai qu'elle est infâme ta formule, ça devrait être interdit par les syndicats...
K32 est égal à 212 car à la ligne I185 le chiffre valait 59 et 59*(185-32)*1= 212
Tu peux t'expliquer sur 59*(185-32)*1= 212
Sur mon excel ça fait 9027, j'ai mis + à la place...
Un début, en L32 :
=MIN(220;MIN(DECALER($I$1;LIGNE();0;LIGNE()+I32))+EQUIV(MIN(DECALER($I$1;LIGNE();0;LIGNE()+I32));DECALER($I$1;LIGNE();0;LIGNE()+I32);0)*$H$7)
Mais j'ai un delta avec toi en ligne 188. Tu as 158, moi 220 et je n'en décolle plus pendant de nombreuses lignes.
Sans doute une mauvaise interprétation de tes explications de ma part.
Si tu pouvais détailler le calcul sur cette ligne, il me manque qcq chose mais je ne vois pas quoi. De plus il se fait tard.
eric
Raymond PENTIER
Messages postés
58393
Date d'inscription
lundi 13 août 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
17 096
15 mai 2012 à 01:19
15 mai 2012 à 01:19
Merci : ton fichier m'a filé un mal de crâne, rien que d'essayer d'y comprendre quelque chose. Je n'y suis d'ailleurs pas parvenu ...
* Pourquoi A186 et B186 sont en gras ? Et c'est une intervention manuelle ?
* Pourquoi la colonne C n'est-elle renseignée qu'aux lignes 15 à 29 et 186 à 200 ?
* Pourquoi les lignes 1414 à 5631 ne sont-elles renseignées qu'aux colonnes A et B ?
* Que signifie le commentaire placé en L31, qui fait référence à "cette colonne" (on suppose K) ? On ne peut pas la simplifier ...
S'il s'agit de simplifier la formule de la colonne J, qui contient 6283 caractères répartis dans 221 termes additionnés, alors oui, il y a deux interventions à faire !
1) La référence $H$7 qui occupe 4 caractères intervient 221 fois dans la formule ; si on définit le nom "ù" pour la cellule H7, on économise 221 fois 3 soit 663 caractères.
2) La formule comporte des parenthèses superflues : pourquoi écrire
I38+((NB(I37:I38)-1)*$H$7) alors que I38+(NB(I37:I38)-1)*$H$7 suffit ? On économise 221 fois 2, soit 442 caractères.
En résumé, le terme
I38+((NB(I37:I38)-1)*$H$7) doit s'écrire
I38+(NB(I37:I38)-1)*ù
Il faut sélectionner la colonne J et avec la commande Rechercher/Remplacer on va Rechercher ((NB et Remplacer par (NB
et recommencer avec
Rechercher $H$7) et Remplacer par ù
La formule ne fait plus QUE 5178 caractères ...
* Pourquoi A186 et B186 sont en gras ? Et c'est une intervention manuelle ?
* Pourquoi la colonne C n'est-elle renseignée qu'aux lignes 15 à 29 et 186 à 200 ?
* Pourquoi les lignes 1414 à 5631 ne sont-elles renseignées qu'aux colonnes A et B ?
* Que signifie le commentaire placé en L31, qui fait référence à "cette colonne" (on suppose K) ? On ne peut pas la simplifier ...
S'il s'agit de simplifier la formule de la colonne J, qui contient 6283 caractères répartis dans 221 termes additionnés, alors oui, il y a deux interventions à faire !
1) La référence $H$7 qui occupe 4 caractères intervient 221 fois dans la formule ; si on définit le nom "ù" pour la cellule H7, on économise 221 fois 3 soit 663 caractères.
2) La formule comporte des parenthèses superflues : pourquoi écrire
I38+((NB(I37:I38)-1)*$H$7) alors que I38+(NB(I37:I38)-1)*$H$7 suffit ? On économise 221 fois 2, soit 442 caractères.
En résumé, le terme
I38+((NB(I37:I38)-1)*$H$7) doit s'écrire
I38+(NB(I37:I38)-1)*ù
Il faut sélectionner la colonne J et avec la commande Rechercher/Remplacer on va Rechercher ((NB et Remplacer par (NB
et recommencer avec
Rechercher $H$7) et Remplacer par ù
La formule ne fait plus QUE 5178 caractères ...
boatitude
Messages postés
9
Date d'inscription
lundi 23 avril 2012
Statut
Membre
Dernière intervention
15 mai 2012
15 mai 2012 à 18:05
15 mai 2012 à 18:05
Bonjour Raymond,
Merci beaucoup pour tes commentaires.
Pour simplifier, la plupart des réponses se trouvent ci-dessus en réponse au travail réalisé par Eric. Je te propose de jeter un oeil à sa formule et sa réponse qui devrait suivre.
Pour tes questions:
- tout ce qui est en colonne A, B et C n'est pas utile à la question posée, mais je devais les garder pour que l'horrible équation de la colonne J reste intacte. Il faut donc pas en prendre compte.
- mon but n'est pas tant de réduire le nombre de parenthèse (même si je t'accorde qui y en a certains qui ne sont pas utiles), mais de trouver une formule courte, bien plus courte que 5000 ou 6000 caractères, dans le style de ce qu'a trouvé Eric ci-dessus.
Si tu sais comment réduire 5000 caractères en 100-200 caractères, je suis plus que preneur ;-)
Encore merci pour ton aide,
Cordialement.
Merci beaucoup pour tes commentaires.
Pour simplifier, la plupart des réponses se trouvent ci-dessus en réponse au travail réalisé par Eric. Je te propose de jeter un oeil à sa formule et sa réponse qui devrait suivre.
Pour tes questions:
- tout ce qui est en colonne A, B et C n'est pas utile à la question posée, mais je devais les garder pour que l'horrible équation de la colonne J reste intacte. Il faut donc pas en prendre compte.
- mon but n'est pas tant de réduire le nombre de parenthèse (même si je t'accorde qui y en a certains qui ne sont pas utiles), mais de trouver une formule courte, bien plus courte que 5000 ou 6000 caractères, dans le style de ce qu'a trouvé Eric ci-dessus.
Si tu sais comment réduire 5000 caractères en 100-200 caractères, je suis plus que preneur ;-)
Encore merci pour ton aide,
Cordialement.
15 mai 2012 à 09:31
Est-cela ?
eric
15 mai 2012 à 17:58
Je crois qu'on est vraiment pas loin du but. A ce que j'ai compris, la différence provient du fait que tu prends "le minimum en I sur la plage démarrant sur la ligne en cours et d'une hauteur de ligne donnée en I". Cette dernière partie (en gras) n'est pas juste. Le calcul doit se faire sur les 220 données précédentes, ou plus exactement sur le nombre correspondant au nombre de jours d'écart pour le couple de renversement (=220), qui se trouve en cellule E8.
Ceci vient du fait qu'il sert à rien de prendre des données plus anciennes que les 220 derniers jours, car 220*1 = 220 (où 1 = coefficient de retour par jour supplémentaire) et que l'on ne peut avoir un chiffre supérieur à 220.
Donc, pour la cellule K188, j'obtiens bel et bien 158, car en I189, la valeur est de 157, auquel on ajoute 1 (=coefficient de retour), car il n'y a qu'un seul jour de différence. Je ne comprends d'ailleurs pas pourquoi tu obtiendrai 220 en K188, ainsi que pour les jours suivants.
Autre exemple, à la ligne suivante (K189), la valeur est de 216, car à la ligne I208, la valeur est de 197 auquel on ajoute 19*1 (19 = la différence de jours ouverts entre la cellule 189 et 208).
Finalement, pour ce qui est de la formule décrite dans le fichier, oui, tu as entièrement raison, il s'agissait bel et bien d'un + et non d'une multiplication. Il fallait donc lire "59+(185-32)*1=212". Erreur due à la fatigue, je suppose. Bien vu ;-)
Encore merci pour ton aide
Cordialement
Modifié par eriiic le 15/05/2012 à 20:16
C'est corrigé, mais toujours le même problème qui m'a donné du fil à retordre.
Pour J189 tu annonces 216, je trouves 220 toujours.
Pourquoi ?
Je me réfère à la plage I190:I409 où tu prends le mini (197) en I208, hors en I409 (dernière cellule de la plage) j'ai 196
Si je met la hauteur de plage à E8-1 soit 219 je trouve ton résultat jusqu'à cette ligne, mais des non ok encore ensuite.
Actuellement la formule est :
=MIN(220;MIN(DECALER($I$1;LIGNE();;$E$8))+EQUIV(MIN(DECALER($I$1;LIGNE();0;$E$8));DECALER($I$1;LIGNE();0;$E$8);0)*$H$7)
ou avec le -1 :
=MIN(220;MIN(DECALER($I$1;LIGNE();;$E$8-1))+EQUIV(MIN(DECALER($I$1;LIGNE();0;$E$8-1));DECALER($I$1;LIGNE();0;$E$8-1);0)*$H$7)
Si tu pouvais dire ce qu'il faut faire....
Ensuite il faudra contrôler si la prise en compte du coeff est correcte
eric
Modifié par eriiic le 15/05/2012 à 21:17
I mini pris en compte et sa ligne, le delta n° ligne
pour te permettre de me dire les correctifs à faire
https://www.cjoint.com/?BEpvi504QVb
eric
PS2 : il faudra sans doute remplacer le 220 qui reste dans la formule =MIN(220;...) par $E$8.
tu dis...
15 mai 2012 à 21:57
Encore merci pour ton aide très précieuse.
Au vu de tes commentaires, il semble qu'il reste un malentendu. Il s'agit de trouver la valeur qui minimise le résultat de la colonne I (pour la ligne correspondante), en sachant que l'on utilise les valeurs du passés, en y ajoutant un coefficient (=1) pour chaque nouveau jour supplémentaire.
Ceci signifie que pour I409 (la cellule que tu mentionnes qui vaut 196), elle vaudrait 196+(409-189)*1=416, ce qui supérieur à 220. N'oublie pas que chaque jour qui passe, on ajoute automatiquement un coefficient (=1). Donc, avec 220 jours de plus, ce sera 220 de plus.
Enfin, la bonne nouvelle c'est que la réponse a été trouvée (sur un autre forum). Je te la donne ici: En L32, tu peux inscrire la formule (matricielle) suivante:
MIN(I33:I253+(SI(LIGNE(INDIRECT("1:221"))<EQUIV(9^9;I:I;1)-LIGNE();LIGNE(INDIRECT("1:221"));EQUIV(9^9;I:I;1)-LIGNE())*$H$7)), après bien sûr avoir tapé sur ctrl+maj+entrer.
Le problème que j'ai eu avec cette formule est qu'elle faisait quitter l'application Excel... car oui, j'utilise Mac... et Mac et Office, c'est pas le grand amour. D'où des plantages dès que ça devient trop lourd.
Donc, une autre solution pour Mac:
MIN(I33:I253+(LIGNE(INDIRECT("1:221"))*$H$7))
Pour que cette simple formule fonctionne, il faudra toutefois ajouter avant cela la valeur 220 (au moins 220 fois) après la ligne I1200, car les cellules subséquentes sont vides.
Comme tu l'auras remarqué, cette réponse donne les réponses de la colonne J, et non celle de K. Il faudra ensuite appliquer la formule de la colonne K pour caper le maximum à 220, et non à 221.
Et, voilà, le tour est fait. Comme quoi, ma formule de trois kilomètres peut bel et bien s'écrire sur une demi-ligne...
Eric, merci encore mille fois pour ton aide. J'espère que ces réponses te donneront satisfaction ;-)
Cordialement