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

Orson83

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

Pièces jointes

  • Exemple V1.xlsx
    14.2 KB · Affichages: 4

sylvanu

XLDnaute Barbatruc
Supporter XLD
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 :
1634851385758.png

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

Pièces jointes

  • Exemple V1.xlsx
    12 KB · Affichages: 5

Orson83

XLDnaute Impliqué
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
 
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
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.

1634882597256.png

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

XLDnaute Impliqué
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
 

Orson83

XLDnaute Impliqué
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

XLDnaute Barbatruc
Supporter XLD
Bonjour,
Un essai en PJ avec :
VB:
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
 

Pièces jointes

  • Exemple V1(3).xlsm
    19.4 KB · Affichages: 1

Orson83

XLDnaute Impliqué
Bonjour,
Un essai en PJ avec :
VB:
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

XLDnaute Barbatruc
Supporter XLD
C'est plus simple encore avec :
VB:
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
 

Pièces jointes

  • Exemple V1(4).xlsm
    19.2 KB · Affichages: 2

Orson83

XLDnaute Impliqué
C'est plus simple encore avec :
VB:
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.
 

Discussions similaires

Réponses
0
Affichages
938

Statistiques des forums

Discussions
311 721
Messages
2 081 928
Membres
101 842
dernier inscrit
seb0390