XL 2010 Optimiser la valeur d'un opérateur automatiquement

[Orson83]

Bonsoir à tous,
Je cherche à optimiser la valeur d'un opérateur en I4 (il calcule une moyenne) afin d'obtenir le meilleur résultat en pied de colonne K27.
Le but étant de forcer Excel à trouver le meilleur résultat de façon automatique.
Attention ! L'opérateur en I4 doit se situer entre 2 et 20.
Je joins un fichier exemple dans ce ticket. Pas de VBA svp.
Merci pour votre aide.

 

[sylvanu]

Bonsoir Tchotchodu31,
Optimiser K27 signifie t-il obtenir la valeur la plus élevée en fonction des contraintes ?
Si oui, on peut utiliser le Solveur ( Données/Solveur ) avec la configuration suivante :

Mettre I4 à 0 et lancer le solveur.
On trouve un max à 56.98 avec Opérateur = 2.6.

 

[Orson83]

Bonsoir Tchotchodu31,
Optimiser K27 signifie t-il obtenir la valeur la plus élevée en fonction des contraintes ?
Si oui, on peut utiliser le Solveur ( Données/Solveur ) avec la configuration suivante :
Regarde la pièce jointe 1119010
Mettre I4 à 0 et lancer le solveur.
On trouve un max à 56.98 avec Opérateur = 2.6.

Bonsoir sylvanu, le forum,
Heureux de te revoir (te relire)
Merci pour cette solution sympa.
Oui, K27 devra donner la plus grande valeur possible.
En creusant un peu, 2,6 n'est pas optimal mais plutôt 12,39 qui donne 115,02.
J'ai trouvé ce résultat avec "l'objectif valeur" à 1000.
Bizarrement, la boite de dialogue m'indique qu'il n'y a pas de solution réalisable. Ce résultat me parait pourtant cohérent.
Si toutefois il y avait une procédure plus aboutie, je prends, sinon cela fera l'affaire.
Bonne soirée.
François

 

[sylvanu]

Bonjour,
Effectivement, le solveur ne sait pas chercher le maximum maximorum en automatique.
Il faut l'aider. Comme j'ai initialisé I4 à 0, il recherche le maximum le plus proche, il trouve autour de 2.6. Si c'est initialisé vers 12 il trouvera 12.39.
Et j ne sais pas comment faire car on ne maitrise pas sa façon de chercher.
La seule solution que je connaisse ... est le VBA. Désolé !
Ci dessous la courbe K27=f(I4) et on voit bien deux maximums autour de 2 et autour de 12.
Mais sans passer par cette courbe je ne vois d'autre solution pour initialiser le solveur.

D'ailleurs on voit que les valeurs changent sur des pas entiers. Que la valeur soit 12.01 ou 12.99 on a toujours la même valeur de K27 : 121.49.
Donc en fait il n'existe pas une valeur de I4 qui donne le maximum mais une plage de I4.

 

[Orson83]

Bonjour,
Effectivement, le solveur ne sait pas chercher le maximum maximorum en automatique.
Il faut l'aider. Comme j'ai initialisé I4 à 0, il recherche le maximum le plus proche, il trouve autour de 2.6. Si c'est initialisé vers 12 il trouvera 12.39.
Et j ne sais pas comment faire car on ne maitrise pas sa façon de chercher.
La seule solution que je connaisse ... est le VBA. Désolé !
Ci dessous la courbe K27=f(I4) et on voit bien deux maximums autour de 2 et autour de 12.
Mais sans passer par cette courbe je ne vois d'autre solution pour initialiser le solveur.

Regarde la pièce jointe 1119024
D'ailleurs on voit que les valeurs changent sur des pas entiers. Que la valeur soit 12.01 ou 12.99 on a toujours la même valeur de K27 : 121.49.
Donc en fait il n'existe pas une valeur de I4 qui donne le maximum mais une plage de I4.

Bonjour sylvanu,
Merci pour cette explication très claire.
Du coup, sans vouloir trop en demander, pourrais-tu me proposer la solution en VBA ?
Merci pour ton aide.
François

 

[sylvanu]

Bonjour,

pourrais-tu me proposer la solution en VBA

Pour tracer la courbe ou trouver le maximum ?
De plus le maximum est pour une plage et non pour une valeur. Que doit on remonter, une valeur prise au hasard ou une plage ?

 

[Orson83]

Bonjour,

Pour tracer la courbe ou trouver le maximum ?
De plus le maximum est pour une plage et non pour une valeur. Que doit on remonter, une valeur prise au hasard ou une plage ?

La courbe m'importe peu.
L'idée c'est d'obtenir le meilleur résultat en K27 avec un nombre entier.
Concernant l'opérateur en I4, s'agissant de jours, donc de nombres entiers, le nombre entier donnant le meilleur résultat est 13 (K27 = 112,14).
J'espère avoir été assez explicite...

 

[sylvanu]

Bonjour,
Un essai en PJ avec :

Sub calcule()
Dim Maxi, Opérateur, i
Maxi = 0: Opérateur = 0
[N15:N16].ClearContents
For i = 2 To 20             ' De 2 à 20
    [I4] = i                ' Initialiser Opérateur
    If [K27] >= Maxi Then   ' Si Gain > Gain maxi
        Maxi = [K27]        ' Mémoriser Gain
        Opérateur = i       ' et Opérateur
    End If
    [I4] = i + 0.1          ' Même opération avec i+0.1
    If [K27] >= Maxi Then
        Maxi = [K27]
        Opérateur = i + 0.1
    End If
Next i
[I4] = Opérateur            ' Restituer le résultat
[N15] = Maxi
[N16] = Opérateur
End Sub

 

[Orson83]

Bonjour,
Un essai en PJ avec :

Sub calcule()
Dim Maxi, Opérateur, i
Maxi = 0: Opérateur = 0
[N15:N16].ClearContents
For i = 2 To 20             ' De 2 à 20
    [I4] = i                ' Initialiser Opérateur
    If [K27] >= Maxi Then   ' Si Gain > Gain maxi
        Maxi = [K27]        ' Mémoriser Gain
        Opérateur = i       ' et Opérateur
    End If
    [I4] = i + 0.1          ' Même opération avec i+0.1
    If [K27] >= Maxi Then
        Maxi = [K27]
        Opérateur = i + 0.1
    End If
Next i
[I4] = Opérateur            ' Restituer le résultat
[N15] = Maxi
[N16] = Opérateur
End Sub

Super travail, bravo !
Juste un soucis au niveau du résultat de I4.
En effet, le meilleur résultat du nombre entier serait 13 (pas de décimales), 12 ne donnant pas le meilleur résultat.
La difficulté serait d'afficher un nombre entier en I4 en ayant le meilleur résultat en K27.
Cela est-il possible ?

 

[sylvanu]

C'est plus simple encore avec :

Sub calcule()
Dim Maxi, Opérateur, i
Maxi = 0: Opérateur = 0
[N15:N16].ClearContents
For i = 2 To 20             ' De 2 à 20
    [I4] = i                ' Initialiser Opérateur
    If [K27] >= Maxi Then   ' Si Gain > Gain maxi
        Maxi = [K27]        ' Mémoriser Gain
        Opérateur = i       ' et Opérateur
    End If
Next i
[I4] = Opérateur            ' Restituer le résultat
[N15] = Maxi
[N16] = Opérateur
End Sub

 

[Orson83]

C'est plus simple encore avec :

Sub calcule()
Dim Maxi, Opérateur, i
Maxi = 0: Opérateur = 0
[N15:N16].ClearContents
For i = 2 To 20             ' De 2 à 20
    [I4] = i                ' Initialiser Opérateur
    If [K27] >= Maxi Then   ' Si Gain > Gain maxi
        Maxi = [K27]        ' Mémoriser Gain
        Opérateur = i       ' et Opérateur
    End If
Next i
[I4] = Opérateur            ' Restituer le résultat
[N15] = Maxi
[N16] = Opérateur
End Sub

Super, merci !
Question : dans mon fichier d'origine j'ai 6090 lignes à traiter. Y a t'il une solution pour qu'il mouline plus vite ?
Je ne suis pas spécialement pressé mais en faisant un calcul simple il mettrait 25 minutes pour traiter les lignes.

 

[sylvanu]

Là cela devrait aller un peu plus vite.

 

[Orson83]

Là cela devrait aller un peu plus vite.

Merci, effectivement, le temps de calcul sur mon fichier de travail est très correct.
Merci pour cette contribution qui, j'en suis certain, pourra aussi intéresser d'autres lecteurs.
Très bon week-end.
François