Variation de paramètres pour maximiser un résultat

[Wylsonn]

Bonjour,

Je dispose d'une base de données (2500 lignes), sur laquelle j'effectue sur chaque ligne un calcul, dont le résultat est 0 ou +1. Ce calcul est dépendant de 5 paramètres (indépendants) qui peuvent prendre leurs valeurs dans une plage assez large (-1000 à + 1000 par exemple).

Je cherche à maximiser la somme de mes résultats (les +1) en faisant varier chacun des 5 paramètres dans leurs plages respectives, et ainsi trouver la meilleure combinaison. Pour l'instant je le fais manuellement, intuitivement, mais je passe à côté de beaucoup d'autres combinaisons qui seraient certainement plus fructueuses.

Le "Gestionnaire de Scénario" serait l'outil adapté, si ce n'était le nombre de paramètres et la quantité astronomique de valeurs qu'ils peuvent prendre. Il me faudrait créer des millions de scénarii...

Avez-vous une idée sur la méthode et sur l'outil ?

Merci,

Wylsonn

 

[JCGL]

Re : Variation de paramètres pour maximiser un résultat

Bonjour à tous,
Bienvenue à Wylsonn ,

Un petit fichier de ce que tu as serait, lui aussi, le bienvenu...

Pas tout le fichier, juste une vingtaine de lignes "anonymisées" et le fichier zippé

A+ à tous

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Voilà le fichier, avec une (toute petite) partie de la base de données : j'essaye de maximiser la SOMME TOTALE en faisant varier les 5 paramètres (en haut à gauche), pour trouver la meilleure combinaison possible.

Peut-être par itération ou avec une boucle VBA ? mais je sèche...

Bonne soirée à tous

 

[CISCO]

Re : Variation de paramètres pour maximiser un résultat

Bnsoir

je ne répond pas à ta demande, mais bon, au passage...

J'ai l'impression que tu peux alléger ta colonne H en remplaçant tes formules par, par ex en H45 :
=SI(ET((G45/(1+$E$6))>G44;SOMME(INDIRECT("G"&LIGNE()+1-E$7):G45)<$E$8);1;"")

@ plus

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Effectivement, c'est un sacré gain de place... surtout que la formule est répétée 2500 x !

Merci et A+

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Bonjour à tous,

J'ai beau réfléchir, je pense que le problème est ardu, même pour Excel : le nombre de combinaison est trop important.

Simplifions à 2 paramètres (par exemple le n°1 et le n°3 qui sont des entiers bornés) : comment créer un tableau avec en ligne les valeurs du paramètres n°1 (soit 1 à 6) et en colonne celles du n°2 (soit 1 à 29); à l'intersection de chaque ligne et colonne, s'afficherait le résultat SOMME TOTALE, combinaison des 2 paramètres identifiés (voir tableau à droite dans le fichier joint)

Après, je n'aurais plus qu'à rechercher la place du maximum dans le tableau, mais ça j'y arrive...

Merci pour votre aide,

Wylsonn

 

[CISCO]

Re : Variation de paramètres pour maximiser un résultat

Bnjour

Même idée en colonne G. Par exemple en G45
=ARRONDI(PENTE(INDIRECT("F"&LIGNE()-$E$4):F45;INDIRECT("B"&LIGNE()-$E$4):B45);4).

Apparemment, ici, il ni a pas besoin du +1 après LIGNE(). A vérifier.

@ plus

 

[CISCO]

Re : Variation de paramètres pour maximiser un résultat

Rebonjour

En réalité, le résultat de ton calcul n'est pas 0 ou 1, mais FAUX ou VRAI.

Autrement dit, si tu nous expliquais l'origine des conditions à remplir, peut être qu'un des Xliens du forum trouvera une astuce...

@ plus

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Je ne connaissais pas INDIRECT; j'avais donc modifié aussi la colonne G après ta première remarque.

Tu as raison sur le résultat : c'est bien un Vrai (+1), qui déclenche un calcul sur la base de données, dont le résultat est atteint (Résultat +) ou non (Résultat -). La SOMME TOTALE est l'addition du tout. En gros, c'est donc un test statistique qui vérifie, chaque fois que la condition est remplie, que l'objectif quantifié est bien atteint. En modifiant la combinaison des paramètres, je cherche à maximiser le nombre de fois où la condition est remplie et l'objectif fixé atteint, c-à-d à obtenir la plus grosse SOMME TOTALE.

Merci pour ta contribution et bonne fin de week-end !

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Même pas une petite piste ?

Il s'agit juste de faire prendre à la cellule E4 la valeur 1 à 6 et à la cellule E7 la valeur 1 à 29, et d'inscrire les résultats successifs pris par la cellule I8 dans un tableau à double entrée... ça doit être faisable, non ?

Bonne soirée à tous

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Dernière petite chose : quelqu'un a-t-il un conseil pour un bon bouquin Excel 2003 & VBA ?

Merci

 

[JCGL]

Re : Variation de paramètres pour maximiser un résultat

Bonjour à tous,

J'ai bien réussi à générer les nombres...
J'ai bien réussi à trouver la formule...

Mais malgré le Application.Wait, le défilement est trop rapide...

Sans doute un début de piste pour les VBAîstes avertis...

Clique sur le GO

A+ à tous

 

[Wylsonn]

Re : Variation de paramètres pour maximiser un résultat

Un grand merci JCGL, c'est un très beau résutat ! (j'ai fait tourner la macro en même temps qu'était ouvert un autre classeur très lourd en formules, ce qui a permis de ralentir le défilement à un rythme visible...)

L'Application.Wait n'a effectivement aucune incidence sur le déroulement, je me suis donc permis de l'enlever.

Reste à trouver le moyen de maintenir l'affichage du résultat dans chacune des cellules du tableau ?!

Très bonne soirée à tous

 

[JCGL]

Re : Variation de paramètres pour maximiser un résultat

Bonjour à tous,

L'Application.Wait devrait fonctionner... si je savais l'utiliser à bon escient...

Notre ami BHBH étant sur le Forum, je lui lance un SOS....

A+ à tous

 

[Cousinhub]

Re : Variation de paramètres pour maximiser un résultat

Bonsoir,

Hi, JC....

Euh, pour faire défiler les résultats de manière visible, modifie ainsi ton code :

Sub Compteurs()
Dim X As Byte, Y As Byte
Dim NewHour As Date, NewMinute As Date, NewSecond As Date
Dim WaitTime As Date
For X = 1 To 6
    For Y = 1 To 29
        NewHour = Hour(Now())
        NewMinute = Minute(Now())
        NewSecond = Second(Now()) + 1
        WaitTime = TimeSerial(NewHour, NewMinute, NewSecond)
        If Range("Compteur1").Value > 6 Then Range("Compteur1").Value = 1
        If Range("Compteur2").Value > 29 Then Range("Compteur2").Value = 1
        Range("Compteur1").Value = X
        Range("Compteur2").Value = Y
        Application.Wait WaitTime
    Next Y
    Application.Wait WaitTime
Next X
Application.Wait WaitTime
End Sub

Note la déclaration des variables, WaitTime est une date, et non un Integer, ainsi que NewHour....

Pas tout compris au fichier, mais avec ce code, les résultats déroulent toutes les secondes.....

Au plaisir