Intersection entre deux courbes EXCEL

[tatane]

Bonjour,

Je demande de l'aide pour trouver une méthode de calcul capable de définir automatiquement le point d'intersection entre deux courbes : voir document joint.

Si quelqu'un peut m'aider... je suis preneur.

Merci d'avance pour vos conseils.

 

[CISCO]

Re : Intersection entre deux courbes EXCEL

Bonsoir

Une possibilité semi-automatique :
* déterminer l'équation des courbes de tendance passant correctement dans l'ensemble des points définissant les deux courbes.
* Ecrire les équations trouvées précédemment en F8 et G8
* Ecrire en H8 =F8-G8
* Minimiser cette différence avec l'outil valeur cible (Données -->Analyse de scénarios -->Valeur cible).

@ plus

P.S : Tu peux aussi jouer sur les graduations principales et sur les quadrillages correspondant, et en zoomant sur la zone intéressante, déterminer les coordonnées de l'intersection, du point de fonctionnement de ton installation.

 

[Dranreb]

Re : Intersection entre deux courbes EXCEL

Bonsoir
Une valeur cible de la différence des deux fonctions est suffisante.
En O4 :

=-0,0054*PUISSANCE(O3;2)-0,1701*O3+8,3121

En O5 :

=$B$9+((47,8*PUISSANCE($B$7;-4,75)*PUISSANCE((O3*1000);1,75))*$B$8/100)

En O6 :

=O5-O4

Chercher O3 pour que O6 vaille 0.

Ça peut s'automatiser par cette procédure dans le module de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.[B12].FormulaR1C1 = "=-0.0054*POWER(R[-1]C,2)-0.1701*R[-1]C+8.3121"
Me.[B13].FormulaR1C1 = "=R9C2+((47.8*POWER(R7C2,-4.75)*POWER((R[-2]C*1000),1.75))*R8C2/100)"
Me.[B14].FormulaR1C1 = "=R13C-R12C"
On Error Resume Next
Range("B14").GoalSeek Goal:=0, ChangingCell:=Range("B11")
Application.EnableEvents = True
End Sub

On peut même se passer du GoalSeek tout en ayant un calcul plus précis :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim J&, CelCbl As Range, CelSrc As Range
Set CelCbl = Me.[B14]: Set CelSrc = Me.[B11]
On Error Resume Next
Application.EnableEvents = False
Me.[B12].FormulaR1C1 = "=8.3121-(0.0054*R[-1]C+0.1701)*R[-1]C"
Me.[B13].FormulaR1C1 = "=R9C2+47.8*R7C2^-4.75*(R[-2]C*1000)^1.75*R8C2/100"
CelCbl.FormulaR1C1 = "=R13C-R12C"
CelSrc.Value = 12
'CelCbl.GoalSeek Goal:=0, ChangingCell:=CelSrc
For J = 1 To 5: ApprocherMieux CelCbl, 0, CelSrc, (J Mod 2 - 0.5) * 2# ^ (-5 * J): Next J
Application.EnableEvents = True
End Sub

Sub ApprocherMieux(ByVal CelCbl As Range, ByVal Valeur As Double, ByVal CelSrc As Range, ByVal Ajout As Double)
Dim X1 As Double, Y1 As Double, X2 As Double, Y2 As Double
X1 = CelSrc.Value: On Error GoTo Resto: Y1 = CelCbl.Value
X2 = X1 + Ajout: CelSrc.Value = X2: Y2 = CelCbl.Value
If Y2 <> Y1 Then
   CelSrc.Value = X1 + (X2 - X1) * (Valeur - Y1) / (Y2 - Y1)
   If Abs(CelCbl.Value - Valeur) < Abs(Y1 - Valeur) Then Exit Sub
   End If
Resto: CelSrc.Value = X1
End Sub

 

[ROGER2327]

Re : Intersection entre deux courbes EXCEL

Bonjour à tous.

Un autre essai (par dichotomie).​

Bonne journée.


ℝOGER2327
#8164

Dimanche 8 Décervelage 143 (Saint Bordure, Capitaine - fête Suprême Tierce)
15 Nivôse An CCXXIV, 1,0269h - lapin
2016-W01-2T02:27:53Z

 

[tatane]

Re : Intersection entre deux courbes EXCEL

Bonsoir à tous !!

Merci beaucoup pour vos solutions !!!
- Cisco j'ai réussi ta démonstration mais je n'arrive pas à l'automatiser. En effet à chaque qu'une valeur change je dois relancer une valeur cible... et je recherche une fonction automatique. SI tu as une solution je suis preneur. Encore merci.
- Dranreb, désolé mais je ne sais pas programmé .... je suis à une année lumière de ton niveau mais je te remercie de tes explications.
- ROGER2327, c'est parfait. La solution est automatique dès que je change des paramètres... ça se re-calcule directement. MAIS le problème est que je n'ai surement pas le bon niveau d'EXCEL car je n'arrive pas à reproduire ta formule sur ma base de données complète. Pourrais-tu m'aider ?? Merci d'avance.

Tatane

 

[Dranreb]

Re : Intersection entre deux courbes EXCEL

Bonsoir.
Il n'y avait pourtant qu'a reproduire ce code tel quel dans le module de la feuille (et non dans un module standard).

 

[tatane]

Re : Intersection entre deux courbes EXCEL

Bonsoir DRANREB,

Je ne voulais pas dénigrer votre solution... mais je n'ai pas votre niveau sur EXCEL. Pouvez-vous SVP alors m'expliquer plus précisément comment rentrer ce code sur le module EXCEL ?
Merci d'avance
Tatane.

 

[Dranreb]

Re : Intersection entre deux courbes EXCEL

Clic droit sur l'onglet de la feuille, Visualiser le code, puis collez ce code.

 

[CISCO]

Re : Intersection entre deux courbes EXCEL

Bonsoir

Malheureusement, je ne sais pas faire tout cela en automatique.

Pour te faciliter le travail, une solution un peu plus automatisée en pièce jointe. Les courbes sont modélisées automatiquement avec un polynôme du troisième degré. Comme précédemment, en F14 et G14 sont données les valeurs de la HMT correspondant au débit donné en E14. En H14 est écrit la différence F14-G14. Pour trouver l'intersection des deux courbes, il faut minimiser la valeur de cette différence avec l'outil "valeur cible".

Si tu changes des valeurs dans C3:M3, C4:K4 ou C5:M5, les coefficients seront automatiquement modifiés. Il ni a qu'à réutiliser l'outil "valeur cible" pour trouver l'abscisse de la nouvelle intersection.

@plus

 

[Dranreb]

Re : Intersection entre deux courbes EXCEL

Il n'y a pas à utiliser autre chose que les fonctions qui donnent déjà les points dans les deux courbes.

 

[CISCO]

Re : Intersection entre deux courbes EXCEL

Bonjour

Suite de mon post #9.

Si tu ne veux pas utiliser à chaque changement l'outil "valeur cible", on peut aussi faire automatiquement par dichotomie (comme le proposait Roger2327) dans des cellules (E22:H70). Parfois, il peut y avoir besoin de changer les valeurs initiales en E20 (Q juste avant l'intersection) et E21 (Q juste après l'intersection). Le résultat s'affiche automatiquement dans E16.

@ plus

 

[Dranreb]

Re : Intersection entre deux courbes EXCEL

Bonjour.
L'algorithme appliqué par ma Sub ApprocherMieux est plus rapide qu'une recherche dichotomique pour des fonctions continues.

 

[ROGER2327]

Re : Intersection entre deux courbes EXCEL

Re...

(...)
- ROGER2327, c'est parfait. La solution est automatique dès que je change des paramètres... ça se re-calcule directement. MAIS le problème est que je n'ai surement pas le bon niveau d'EXCEL car je n'arrive pas à reproduire ta formule sur ma base de données complète. Pourrais-tu m'aider ?? Merci d'avance.

Tatane

Voici le code avec quelques commentaires.​

Dim e#, h#, p()
Const a# = -0.0054
Const b# = -0.1701
Const c# = 8.3121
Const d# = 0.478

Sub param()
    p = Feuille01.[B7:B9].Value 'À adapter selon
' l'endroit où se trouvent les paramètres.
    e = d * p(2, 1) * p(1, 1) ^ -4.75 * 1000 ^ 1.75
    h = p(3, 1)
End Sub

Function f#(x#)
' Écrire ici l'équation de la première fonction.
' Dans cet exemple, la première fonction s'écrit
' =-0,0054*PUISSANCE(C3;2)-0,1701*C3+8,3121
' en formule Excel. La variable est C3. L'équation
' est celle d'une parabole dont l'équation s'écrit
' f(x)= a*x^2+b*x+c
' avec a=-0.0054, b=-0.1701, c=8.3121.
' (ou f(x)= c+x*(b+x*a) en appliquant le schéma de
' Hörner. Sous cette forme, le calcul ne nécessite
' que deux multiplications et deux additions ;
' il n'y a pas d'élévation à la puissance deux :
' c'est beaucoup plus rapide et souvent plus précis.)
' Par commodité, j'ai déclaré ces paramètres comme
' constantes en tête du module.
    f = c + x * (b + x * a)
End Function

Function g#(x#)
' Écrire ici l'équation de la deuxième fonction.
' Dans cet exemple, elle s'écrit
' =$B$9+47,8*PUISSANCE($B$7;-4,75)*PUISSANCE((C3*1000);1,75)*$B$8/100
' en formule Excel. La variable est C3. L'équation
' est celle d'une fonction puissance dont, après
' simplification,l'équation s'écrit
' g(x)= e*x^1.75+h
' avec e=47,8*PUISSANCE($B$7;-4,75)*PUISSANCE(1000;1,75)*$B$8/100
' et h=$B$9.
' Comme la fonction g est utilisée à plusieurs reprises,
' j'ai déclaré les paramètres e et h en tête du
' module et je les calcul une seule fois dans la procédure
' 'param()'. Cela accélére les calculs.
    g = h + e * x ^ 1.75
End Function

' Il n'y a rien à modifier dans ce qui suit.

Private Function Fonction#(x#)
    Fonction = f(x) - g(x)
End Function
'
Function AnnuleFonction(inf#, sup#)
Application.Volatile 'Facultatif, en fonction de l'usage.
' Les paramètres inf et sup indique les bornes
' entre lesquelles on cherche une solution.
Dim x0#, x1#, f0#, f1#, h#, k#
    param
    f0 = Fonction(CDbl(inf))
    f1 = Fonction(CDbl(sup))
    If f0 = 0 Then
        AnnuleFonction = inf
    ElseIf f1 = 0 Then
        AnnuleFonction = sup
    ElseIf f1 * f0 > 0 Then
        AnnuleFonction = Evaluate("=NA()")
    Else
        If f1 > f0 Then k = 1 Else k = -1
        x0 = inf
        x1 = sup
        h = x1 - x0
        Do
            f1 = Fonction(x1)
            h = h / 2
            x0 = x1
            If f1 > 0 Then x1 = x1 - k * h Else x1 = x1 + k * h
            f0 = f1
        Loop While h > 1E-20 And f0 <> 0
        AnnuleFonction = x0
    End If
End Function

S'il en faut plus (des commentaires), dites-le.​

Bonne soirée.


ℝOGER2327
#8167

Lundi 9 Décervelage 143 (Dormition de Jacques Vaché, interprète - fête Suprême Quarte)
16 Nivôse An CCXXIV, 7,0130h - silex
2016-W01-3T16:49:53Z

 

[CISCO]

Re : Intersection entre deux courbes EXCEL

Bonsoir

Bonjour.
L'algorithme appliqué par ma Sub ApprocherMieux est plus rapide qu'une recherche dichotomique pour des fonctions continues.

Ca, je veux bien le croire... Je fais avec mes connaissances avec des formules...

@plus

 

[CISCO]

Re : Intersection entre deux courbes EXCEL

Bonjour

Si on ne veut pas procéder par dichotomie, si on modélise les courbes avec des polynômes du second degré, on peut tout simplement résoudre l'équation du second degré correspondant Hmt réseau modélisé - Hmt pompe modélisé = 0. Cf. en pièce jointe.

@ plus

P.S : Avec des équations du 3ème degré, j'obtiens un discriminant négatif...