Extraire l'algorithme d'une macro

Bonjour,

Je dispose d'un fichier excel qui calcul automatiquement différents indices pratiques au cyclisme après avoir cliqué sur Ctrl + n. Les éléments à entrer sont en rouge (Puissance en watt, poids du cycliste + vélos, etc).

Pièce jointe 601807

Une colonne (entourée en bleu) issue d'un calcul Macro me pose problème, car je n'arrive pas à déterminer comment elle est calculée. Voici le code VBA que j'ai pu extraire avec Alt + F12, dans "Module 2".

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Sub Macro1() ' ' Macro1 Macro ' Macro enregistrée le xx/xx/xxxx par xx ' ' Touche de raccourci du clavier: Ctrl+n ' Range("J11").Select ActiveCell.FormulaR1C1 = "500" Range("J12").Select ActiveCell.FormulaR1C1 = "500" Range("J13").Select ActiveCell.FormulaR1C1 = "500"   Range("K11").GoalSeek Goal:=0, ChangingCell:=Range("J11") Range("K12").GoalSeek Goal:=0, ChangingCell:=Range("J12") Range("K13").GoalSeek Goal:=0, ChangingCell:=Range("J13")   End Sub

---

Je ne vois aucun calcul ni équation. Existe-il une autre manière de voir comment une colonne est calculée ? Je met à disposition le fichier dans mon drive :

https://drive.google.com/file/d/1o9I...ew?usp=sharing

Un grand merci.

Bonjour,

Si tu regardes les cellules K11 à K13, tu verras qu'elles comportent des formules qui font références aux cellules J11 à J13.

Ta macro utilise la fonction du solveur pour définir la valeur des cellules K11 à K13, via l'instruction GoalSeek, en faisant varier les cellules J11 à J13.
Comme ça passe par le solveur, tu ne vois donc aucun calcul, juste le résultats donné par le solveur.

Tu obtiendras certainement plus de réponse en regardant le solveur.
On peut voir que le but est de maximiser la valeur en colonne K en faisant varier la colonne J.

Merci pour cette piste ! Je ne connaissais pas le solveur sur Excel.

En l'ouvrant et en sélectionnant une des cellules, celui-ci reste vide. Comment avoir plus d'infos sur le calcul ?

Pièce jointe 601856

Merci !

Le principe du solveur, c'est de faire varier une ou plusieurs cellules pour atteindre une certaine cible, en testant plein de combinaisons possibles.
Je t'invite à lire l'aide Microsoft Excel pour avoir plus d'informations sur son utilisation.

Dans ton cas, prennons par exemple l'instruction suivante :

Code:

---

Range("K11").GoalSeek Goal:=0, ChangingCell:=Range("J11")

---

Cela signifie qu'on veut que la cellule K11 soit le plus proche de 0 (pour rappel c'est une formule qui dépend de J11 entre autre)
Et pour y arriver, le solveur va faire varier la cellule J11 jusqu'à trouver la valeur optimale.

Si on utilisait manuellement le solveur, cela reviendrait à le paramétrer comme ceci :
Pièce jointe 601859

Un grand merci pour ces explications. Cela risque donc d'être dur de récupérer l'algorithme utilisé pour obtenir la valeur des cellules en K pour le reproduire indépendamment d'un fichier Excel ...
Merci.

Bonjour,

Il existe de nombreuses méthodes pour effectuer une optimisation (c'est-à-dire ce que fait le solveur : optimiser la valeur d'une grandeur en jouant sur la valeur d'une autre grandeur). Mais pour choisir la bonne, il nous faudrait plus d'infos.

Pourriez-vous nous fournir les formules Excel utilisées en colonne K (cellule K11 par exemple) ?

Merci pour votre réponse !

Bien sur. La formule risque d'être un peu compliquée, car le fichier excel tente d'automatiser des équations mathématiques.
Voici la formule située en K11.

Code:

---

1 2	=SI(B11<>0;ABS($D$2-((1+E11)*$D$3*J11/36+0,2*250/11664*J11*J11*(J11-$K$3)));0)

---

La formule que vous utilisez est un polynôme de degré 3. Il est possible de calculer la valeur qui convient sans avoir besoin de passer par un algorithme d'optimisation.

Dans votre code (ou dans des cellules Excel), calculez les quantités suivantes :

Code:

---

1 2 3 4 5 6 7 8

A = -50 / 11664 B = 50 * $K$3 / 11664 C = -(1+E11) * $D$3 / 36 D = $D$2 E = B^2 - 3*A*C F = 2*B^3 - 9*A*B*C + 27*A^2*D G = ((F + (F^2 - 4*E^3)^0,5) / 2)^(1/3) J = Si(B11 = 0; 500; (B + G + E/G)/(-3*A))

---

Notez bien que tout ce qui est lié à ligne 11 dans le code ci-dessous doit en fait être lié à la ligne courante, quelle qu'elle soit.
Si vous trouvez que la lisibilité des formules ci-dessus n'est pas bonne, vous pouvez retrouver tout ceci sur cette page web.

Cdt

Un immense merci pour votre aide. Effectivement, avec ce jeu des variables, j'arrive à calculer la pente et donc à me trouver un algorithme !! :)