XL 2016 Trouver un résultat maximum en faisant varier 14 variable

[Angrydrizzit]

Bonjour à tous.

Je requière votre aide, je cherche a faire varier 14 événements différents, ceux-ci doivent varier entre 1 ou -1 uniquement et sont située en (E1:E14) dans le fichier excel joint.
L'objectif est d'avoir la valeur maximal en Q12, et se rapprocher le plus possible du max possible située en Q14 (94 dans se tableau).

Je pense que réaliser une automatisation est possible mais uniquement en VBA malheureusement je n'ai aucune connaissance en VBA...

Merci d'avance pour votre aide.

Cordialement

 

[sylvanu]

Bonjour Angrydrizzit, et bienvenu sur XLD,
Difficile d'appréhender votre problème de façon mathématique, ou par itération.
Je n'ai trouvé aucune loi mathématique gérant l'ensemble.
Donc en PJ la bonne vieille méthode de "bourrin", tester les 16384 possibilités et en retenir que la combinaison max. En VBA bien évidemment.
Je trouve un max de 59.
Mais c'est très long. Sur mon vieux PC et XL2007 ça met 68 secondes. J'ai mis la progression dans le StatusBar en bas de l'écran ... pour faire patienter.

 

[Angrydrizzit]

Bonjour Sylvanu, incroyable la rapidité pour résoudre mon problème.
Je ne voyais pas d'autre solution que la possibilité "bourrin", après avoir longuement chercher sur internet une solution autre que VBA.
Et franchement 68 sec c'est extrêmement rapide... quand on regarde le temps passé en manuel haha

Par contre comment détermine tu les 16384 ?

Merci beaucoup

 

[sylvanu]

Bonjour,
On a 14 variables donc 2^14 possibilités soit 16384 ( avec pour chaque variable que deux valeurs -1 et +1 )
Donc je construit une table allant de 0 à 16384 en binaire, chaque 0 valant -1, chaque 1, 1.
( macro RemplitTableau )
Puis je met chaque combinaison en place en E1:E14. Je regarde Q12, s'il est supérieur au Q12 précédent je mémorise la combinaison.
A la fin, je restitue la combinaison ayant donné le Q12 max.

 

[sylvanu]

NB : Pour la formule d'estimation :

=SI(D15=$D$1;K15*$E$1;SI(D15=$D$2;K15*$E$2;SI(D15=$D$3;K15*$E$3;SI(D15=$D$4;K15*$E$4;SI(D15=$D$5;K15*$E$5;SI(D15=$D$6;K15*$E$6;SI(D15=$D$7;K15*$E$7;SI(D15=$D$8;K15*$E$8;SI(D15=$D$9;K15*$E$9;SI(D15=$D$10;K15*$E$10;SI(D15=$D$11;K15*$E$11;SI(D15=$D$12;K15*$E$12;SI(D15=$D$13;K15*$E$13;SI(D15=$D$14;K15*$E$14;""))))))))))))))

Je pense qu'on peut faire :

=SIERREUR(INDEX($E$1:$E$14;EQUIV(D15;$D$1:$D$14;0))*K15;"")

Ce qui donne les mêmes résultats.
Ca ne change pas le temps de calcul, mais est plus simple comme formule.

 

[Angrydrizzit]

Ok j'ai compris pour les 16384, je commence à analyser le code, bien que je sois pas expert, c'est bien pensé.

Je ne comprend pas par contre les formules que tu as ajouter dans ton dernier message, ou est-ce qu'elles doivent être appliqué ?

Cordialement

 

[sylvanu]

Je ne comprend pas par contre les formules que tu as ajouter dans ton dernier message, ou est-ce qu'elles doivent être appliqué ?

Comme indiqué, pour la colonne Estimation, de E15 à E1217; Voir PJ.

 

[Angrydrizzit]

ahhhh d'accord j'ai compris, on vois l'expert qui parle

Merci pour la simplification je prend note!

 

[Angrydrizzit]

Rebonsoir, et si je veux faire une recherche sur une plage par exemple pas juste -1 ou 1 mais tous les chiffres entre 1 et 10 par exemple ?

Je sais que le calcul serait beaucoup beaucoup plus long 10^14=100000000000000 mais je serait fortement intéressé par le code VBA.

Merci infiniment si tu sais coder cela !

Cordialement

 

[sylvanu]

Bonsoir,
Le code est architecturé sur le fait que chaque variable peut avoir deux valeurs -1 et +1, donc un peu comme en binaire, en remplaçant 0/1 par -1/+1..

Dans votre hypothèse le nombre de combinaisons sur 14 variables de 10^14 valeurs est hors de ce code.
Qui plus est sur mon PC qui met 70s pour 16384 combinaisons, cela donne 4.272ms par boucle.
Donc dans votre cas cela ferait 221 816 millénaires (7*10^15 s), donc je ne pourrais pas le tester.

 

[Angrydrizzit]

Bonsoir,
Je comprend, c'est dommage que le processeur ne soient pas prix à 100%.

Du coup pour réduire le nombre de combinaison, est-il possible de toujours envisager une plage mais cette fois-ci avec la possibilité de choisir un incrément par exemple entre 0 et 15 mais incrément de 5, ce qui ferais qu'il n'y aurais que le 0, 5, 10 et 15. Donc 4^14, ça resterais long mais envisageable.

Et pour tester si le code VBA fonctionne, le tester que sur 5 événements d'ailleurs, je rajoute que j'ai des événements beaucoup plus prioritaire que d'autre, que je modifierais l'ordre des évènements pour ne tester que le strict minimum, pour le moment j'en verrais entre 5 et 7 qui valent le coup de faire cela.

Donc si on par sur cette possibilité, on passe à 4^5 => 1024 possibilité.

Mais pour coder cela il faut rajouter deux colonnes, par exemple :
E = plage mini
F = incrément
G = plage maxi

Après c'est peut etre dur et lourd a coder.. désolé pour le dérangement en tout cas merci encore pour les réponses très constructives.

En espérant être claire dans ce que je propose.

Cordialement

 

[bsalv]

56 avec solveur

 

[sylvanu]

Bonjour,

Le code est architecturé sur le fait que chaque variable peut avoir deux valeurs -1 et +1, donc un peu comme en binaire, en remplaçant 0/1 par -1/+1..

Dans mon algo chaque variable ne peut prendre que deux valeurs Min ou Max.
Donc votre approche n'est pas compatible. Il faut trouver autre chose mais je ne sais pas comment.
Il existe une méthode de résolution lorsque le nombre de variables est élevée, c'est la méthode Taguchi qui permet drastiquement de réduire le nombre de combinaisons avec un plan d'expérience. ( Lien )
Je l'ai déjà utilisé mais via des outils ( et un expert pour guider ), je serais incapable de mettre ça sous forme de macro.
( sous XL vous avez Xlstat ( Lien ), ou encore des ex XL sur ce site : Lien , mais c'est assez complexe à approcher quand on ne connait pas. )

 

[sylvanu]

Re,
L'autre approche, mais bien moins déterministe, est de donner à chaque variable une valeur aléatoire compris entre deux bornes. ( valeurs entières )
En PJ un essai. On fixe pour chaque variable le min et le max (tableau orange) et on fixe le temps max de calcul.
L'outil fera des tirages en conséquence pendant tout le temps demandé et mémorisera le max.
Vous n'aurez absolument pas le max de la configuration, mais le max qu'il a trouvé.

 

[Angrydrizzit]

Bonjour,

56 avec solveur

C'est bien beau de donner un résultat Bsalv mais sans le moyen d'arriver à celui-ci ce n'est pas très constructif.
Pourrais-tu expliquer comment tu es arrivé a se résultat ?

Merci pour ton retour Sylvanu, je vais aller regarder les sources que tu proposes.

Et la manière aléatoire est un bon début même si sa ne permet pas d'avoir le meilleur résultat.
C'est déjà très intéressant

Cordialement