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

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 25 Juillet 2023

• #2

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.

 

Pièces jointes

• besoin d'aide.xlsm

  189.7 KB · Affichages: 6

Vote positif 0 Vote négatif

Solution

A

Angrydrizzit

XLDnaute Nouveau

• 25 Juillet 2023

• #3

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

 

Dernière édition: 25 Juillet 2023

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 25 Juillet 2023

• #4

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.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 25 Juillet 2023

• #5

NB : Pour la formule d'estimation :

VB:

=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 :

Code:

=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.

 

Vote positif 0 Vote négatif

A

Angrydrizzit

XLDnaute Nouveau

• 25 Juillet 2023

• #6

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

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 25 Juillet 2023

• #7

Angrydrizzit à dit:

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

Cliquez pour agrandir...

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

 

Pièces jointes

• besoin d'aide V3.xlsm

  188.7 KB · Affichages: 3

Vote positif 0 Vote négatif

A

Angrydrizzit

XLDnaute Nouveau

• 25 Juillet 2023

• #8

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

Merci pour la simplification je prend note!

 

Vote positif 0 Vote négatif

A

Angrydrizzit

XLDnaute Nouveau

• 26 Juillet 2023

• #9

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

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Juillet 2023

• #10

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.

 

Vote positif 0 Vote négatif

A

Angrydrizzit

XLDnaute Nouveau

• 27 Juillet 2023

• #11

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

 

Vote positif 0 Vote négatif

B

bsalv

XLDnaute Occasionnel

• 27 Juillet 2023

• #12

56 avec solveur

 

Pièces jointes

• 1690417220569.png

  11.3 KB · Affichages: 11

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 27 Juillet 2023

• #13

Bonjour,

sylvanu à dit:

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..

Cliquez pour agrandir...

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. )

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 27 Juillet 2023

• #14

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é.

 

Pièces jointes

• besoin d'aide V4.xlsm

  188.9 KB · Affichages: 1

Vote positif 0 Vote négatif

A

Angrydrizzit

XLDnaute Nouveau

• 27 Juillet 2023

• #15

Bonjour,

bsalv à dit:

56 avec solveur

Cliquez pour agrandir...

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

 

Vote positif 0 Vote négatif