Recherché valeur maximum en fonction de plusieurs formules.

[cocacola]

Bonjour à tous,

J'aimerais savoir si il est possible de connaitre la valeur maximum que peut prendre une cellule qui dépend de plusieurs autres cellules contenant des formules avec des variables.
Je m'explique : dans le fichier présent, j'aimerai savoir pour quelle formule des colonnes D et G, la cellule E104 (en vert) est la plus proche de 1.

J'aimerai connaitre la valeur de chacun des intervalles ci-dessous présent dans les formules des colonnes D et G qui me donnerai une cellule E104 la plus proche de 1.

- l'écart entre les deux cellules de la formule de la colonne D (qui débute à D120). sachant que l'intervalle peut varier de 20 à 120 lignes (dans mon fichier il est de 20 lignes. (A100:A120 pour la 1ere ligne du tableau)

- l'écart entre les deux cellules des deux intervalles de la colonne A qui sont dans la formule de la colonne G (qui débute à G120)
- sachant que pour toute les cellules de G, l'intervalle de ligne doit être compris entre 5 et 10. (dans mon fichier l'intervalle est de 5 et 6 pour la première ligne [A120>A125;A120>A126]
- sachant aussi que les deux bornes supérieurs des intervalles doivent être consécutives, et que la borne inférieur doit être celle de la cellule de la colonne G lié. (pour la première ligne (120), la borne inférieur est 120)

En gros je voudrais connaitre quelles sont les intervalles qui me donnerait une cellule E104 la plus proche de 1.

Je ne sais pas si c'est très claire.. j'ai fait de mon mieux
Merci beaucoup,
Cordialement.

 

[Gorfael]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Salut cocacola et le forum
Pas sûr de comprendre :
E14 :

(H120-I120)/(H120+I120)

Si je cherche la valeur maximale qu'elle peut atteindre sur les lignes de 120 à 3060, il me suffit d'un max :

Max((H120:H3060-I120:I3060)/(H120:H3060+I120:I3060))

Comme les additions et soustractions ne sont pas matricielles, il faut valider la formule avec <Ctrl>+<Shift>+<Enter>. Qaund excel la reconnaît comme matricielle, il l'encadre avec {}

N'ayant que la version 2003, elle supporte souvent mal le passage du convertisseur en xls. Donc, pas d'exemple
A+

 

[cocacola]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Salut, merci pour ta réponse.

Ce que j'aimerai avoir est surtout les intervalles en gras pour lesquelles E104 est la plus élevé.

y a t'il une solution svp ?
merci

 

[job75]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Bonjour cocacola, Gorfael,

Problème intéressant, il s'agit d'optimiser la valeur en E104.

Principe de la macro :

- on incrémente les formules en D120 et G120 et on les tire vers le bas

- chaque valeur de E104 est mémorisée ainsi que les formules correspondantes dans un tableau 1000 x 3

- en fin de macro la valeur maximum prise par E104 est déterminée ainsi que les formules correspondantes.

Private Sub CommandButton1_Click()
Dim t#, plage1 As Range, plage2 As Range, tablo(999, 2), i%, j%, F1$, F2$, maxi#
t = Timer 'pour calculer la durée d'exécution
Application.ScreenUpdating = False
Set plage1 = Range("D120:D" & [D65536].End(xlUp).Row)
Set plage2 = Range("G120:G" & [D65536].End(xlUp).Row)
For i = 1 To 100
  F1 = "=MAX(A" & i & ":A120)"
  [D120].Formula = F1
  [D120].AutoFill plage1
  For j = 1 To 10
    F2 = "=IF(E120=2,IF(AND(A120>A" & 120 + j & ",A120>A" & 121 + j & "),1,-1),"""")"
    [G120].Formula = F2
    [G120].AutoFill plage2
    If IsNumeric([E104]) Then tablo(i * j - 1, 0) = [E104]
    tablo(i * j - 1, 1) = F1
    tablo(i * j - 1, 2) = F2
  Next
Next
maxi = Application.Max(tablo)
F1 = Application.VLookup(maxi, tablo, 2, 0)
F2 = Application.VLookup(maxi, tablo, 3, 0)
[D120].Formula = F1
[G120].Formula = F2
[D120].AutoFill plage1
[G120].AutoFill plage2
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.0") & " s"
End Sub

Sur mon ordi avec Excel 2003 (Windows XP) la durée d'exécution est de 28 s.

Fichier joint.

A+

 

[job75]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Re,

Bien que le résultat soit le même, le repérage des éléments du tableau n'était pas correct :

Private Sub CommandButton1_Click()
Dim t#, plage1 As Range, plage2 As Range, tablo(999, 2), i%, j%, F1$, F2$, n%, maxi#
t = Timer 'pour calculer la durée d'exécution
Application.ScreenUpdating = False
Set plage1 = Range("D120:D" & [D65536].End(xlUp).Row)
Set plage2 = Range("G120:G" & [D65536].End(xlUp).Row)
For i = 1 To 100
  F1 = "=MAX(A" & i & ":A120)"
  [D120].Formula = F1
  [D120].AutoFill plage1
  For j = 1 To 10
    F2 = "=IF(E120=2,IF(AND(A120>A" & 120 + j & ",A120>A" & 121 + j & "),1,-1),"""")"
    [G120].Formula = F2
    [G120].AutoFill plage2
    If IsNumeric([E104]) Then tablo(n, 0) = [E104]
    tablo(n, 1) = F1
    tablo(n, 2) = F2
    n = n + 1
  Next
Next
maxi = Application.Max(tablo)
F1 = Application.VLookup(maxi, tablo, 2, 0)
F2 = Application.VLookup(maxi, tablo, 3, 0)
[D120].Formula = F1
[G120].Formula = F2
[D120].AutoFill plage1
[G120].AutoFill plage2
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.0") & " s"
End Sub

Prenez ce fichier (2).

A+

 

[cocacola]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Merci beaucoup, c'est exactement ce que je cherchais

Bon dimanche, à bientôt !

 

[job75]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Re,

Juste une remarque.

Il est assez facile de déterminer le nombre de fois où le maximum maxi = 0,3714 apparaît dans tablo.

Il apparaît 12 fois.

La macro du fichier (2) donne la plage la plus grande en D120 : =MAX(43:120)

Si l'on veut la plage la plus petite, remplacer For i = 1 To 100 par For i = 100 To 1 Step -1

La macro du fichier (3) joint donne alors en D120 : =MAX(48:120)

A+

 

[cocacola]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Merci job75, tu m'es d'une précieuse aide !

peux-tu m'aider à faire la même chose pour le fichier suivant avec d'autres valeurs stp ? je ne sais pas comment appliquer la macro à d'autre forme de calcul..
j'aimerai connaitre les valeurs des cellules E110 et H110 pour optimiser (ou minimiser) cette même cellule E104 de tel sorte qu'elle soit le plus proche de 1 ou de -1 en fonction de ces deux cellules

Merci,
Cordialement

 

[job75]

Re : Recherché valeur maximum en fonction de plusieurs formules.

Re,

Bon OK, juste pour vous montrer le principe.

Le minimum -1 est atteint avec cette macro :

Private Sub CommandButton1_Click()
Dim t#, tablo(9999, 2), i%, j%, n%, mini#
t = Timer 'pour calculer la durée d'exécution
Application.ScreenUpdating = False
For i = 1 To 100
  [E110] = i
  For j = 1 To 100
    [H110] = j
    If IsNumeric([G104]) Then tablo(n, 0) = [G104]
    tablo(n, 1) = CStr(i)
    tablo(n, 2) = CStr(j)
    n = n + 1
  Next
Next
mini = Application.Min(tablo)
[E110] = Application.VLookup(mini, tablo, 2, 0)
[H110] = Application.VLookup(mini, tablo, 3, 0)
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.0") & " s"
End Sub

La durée d'exécution chez moi est d'environ 2 mn.

On peut essayer la même chose pour le maximum...

Fichier joint.

A+