Recherché valeur maximum en fonction de plusieurs formules.

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion cocacola
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
C

cocacola

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

Pièces jointes

Dernière édition:
Trier par date Trier par votes
Re : Recherché valeur maximum en fonction de plusieurs formules.

Salut cocacola et le forum
Pas sûr de comprendre :
E14 :
Code: 
(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 :
Code: 
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+
 
Vote positif 0 Vote négatif
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.

Code: 
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+
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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 :

Code: 
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+
 

Pièces jointes

Vote positif 0 Vote négatif
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+
 

Pièces jointes

Vote positif 0 Vote négatif
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
 

Pièces jointes

Dernière édition:
Vote positif 0 Vote négatif
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 :

Code: 
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+
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

N
  • Résolu(e)
Microsoft 365 Optimisation formule recherche valeur en fonction de plusieurs critères
Réponses
5
Affichages
142
Nounours4173
N
piga25
  • Résolu(e)
XL 2016 En VBA, Automatiser la réponse en fonction de l'état d'une cellule.
Réponses
7
Affichages
527
piga25
piga25
J
  • Résolu(e)
Microsoft 365 [RESOLU] VBA boucle, insertion plusieurs lignes, exécution formules, suppression sur si
Réponses
5
Affichages
576
Phil69970
Phil69970
J
  • Résolu(e)
XL 2021 Répartition volume d'heures sur plusieurs colonnes en fonction de l'entête de la colonne
Réponses
4
Affichages
329
JLE
J
klin89
  • Question Question
Microsoft 365 Fonction Produit sur plages de cellules de différentes longueurs
Réponses
13
Affichages
732
Jean-Eric
Jean-Eric
R
  • Question Question
XL 2010 Copie de plusieurs plages de cellules spécifiques dans le presse-papier
2
Réponses
20
Affichages
593
Dranreb
Dranreb
P
  • Résolu(e)
Microsoft 365 Mise en surbrillance de prix min et max (mise en forme conditionnelle)
Réponses
6
Affichages
229
Pepepichnette69
P
Z
  • Résolu(e)
Microsoft 365 Scinder une ligne en fonction de plusieurs critères
Réponses
10
Affichages
397
Zaadox
Z
S
  • Question Question
Microsoft 365 Protéger des cellules de la feuille en permettant d'agrandir les colonnes
Réponses
6
Affichages
164
Sissou71
S
W
  • Question Question
XL 2016 Mise en forme conditionnelle des cellules
Réponses
2
Affichages
338
mapomme
mapomme
T
  • Résolu(e)
XL 2013 Harmoniser valeur de cellules selon 2 variables
Réponses
4
Affichages
262
Thomas75
T
S
  • Résolu(e)
Microsoft 365 [RESOLU] Excel : Calcul de prix en fonction d'un type
Réponses
4
Affichages
388
JHA
J
D
  • Question Question
Microsoft 365 Calculer l'écart individuel de chaque n° en fonction d'une règle définie
Réponses
4
Affichages
604
drenek35
D
T
  • Résolu(e)
XL 2016 Modification de données en fonction d'une valeur
Réponses
3
Affichages
493
therse
T
P
  • Question Question
XL 2021 Ajout/adaptation de code VBA sur feuille qui en contient déjà
Réponses
3
Affichages
274
Pierreavecunp
P
C
  • Résolu(e)
Microsoft 365 Problème VBA lors de l'affichage de ligne masquée
2
Réponses
21
Affichages
981
Chris Linefield
C
T
  • Résolu(e)
XL 2016 Automatisation de la mise à jour de la date en fonction de l'état de la colonne F
Réponses
4
Affichages
652
telemarrk
T
T
  • Résolu(e)
Microsoft 365 Insérer une image DANS une cellule
Réponses
12
Affichages
1 K
Thomi
T
L
  • Résolu(e)
Microsoft 365 Aide : Colorer une cellule en fonction de sa valeur
Réponses
2
Affichages
476
Leo98
L
F
  • Résolu(e)
Microsoft 365 copier coller des colonnes en fonction de la valeur d'une cellule
Réponses
8
Affichages
879
loloboc
L
Retour