XL 2016 Détection valeur X de la pente

[Quatr_o]

Bonjour à tous,

Je sèche, je fais appel à la communauté car je souhaite détecter la valeur en abscisses des pentes des différentes butées par le calcul ou en automatique afin de me faciliter le travail auriez-vous des idées ?
ci-joint la courbe .
merci d'avance de votre aide qui me sera précieuse.

cordialement.

 

[sylvanu]

Bonsoir Quatr_o,
Quelle différence avec le post :
https://excel-downloads.com/threads/detection-changement-de-pente.20073441/#post-20561088 ??

 

[Dranreb]

Bonsoir.
Pouvez vous donner les valeurs x où se produisent les changements de pentes ?
Parce qu'il est possible de trouver une courbe de tendance en ligne brisée, mais à condition de les connaître …

 

[sylvanu]

Bonsoir Dranreb,
On a déjà traité ce sujet ( ensemble d'ailleurs 🙂 ) en janvier. Voir le lien que j'ai mis.
Il me semble que c'est vraiment le même sujet, d'où mon incompréhension.

 

[Dranreb]

Ah mais oui, en effet !

 

[Dranreb]

Bon je n'ai pas réussi à faire bosser le solveur dessus, alors j'ai écrit une petite procédure Ajuster qui approche petit à petit les valeurs X optimales de changements de pentes.
Remarque: je ne l'ai fait que pour la courbe bleue. A vous de répéter les formules et l'ajustement pour la courbe orange.

 

[Dranreb]

Bonjour.
J'ai refait la manip pour la deuxième courbe.

 

[job75]

Bonjour Quatr_o, sylvanu, Bernard,

Voyez le fichier joint et ses 2 macros, une pour chaque série.

La variable decal définit l'écart par rapport à l'écart-type, elle est fixée par tâtonnement :

Sub Etude_Serie1()
Dim x1, x2, x3, x4, debut&, fin&, a, m, b, seb, decal, i&
x1 = -30
x2 = -20
x3 = -10
x4 = 10
With [A1].CurrentRegion
    '---1ère butée---
    debut = Application.Match(x1, .Columns(1))
    fin = Application.Match(x2, .Columns(1))
    Range(.Cells(debut, 1), .Cells(fin, 1)).Name = "X" 'plage nommée
    Range(.Cells(debut, 2), .Cells(fin, 2)).Name = "Y" 'plage nommée
    a = [LINEST(Y,X,1,1)] 'DROITEREG avec statistiques
    m = Application.Index(a, 1, 1)
    b = Application.Index(a, 1, 2)
    seb = Application.Index(a, 2, 2) 'écart-type sur b
    decal = 5 '5 fois l'écart-type
    For i = fin To .Rows.Count
        If .Cells(i, 2) > m * .Cells(i, 1) + b + decal * seb Then
            MsgBox "1ère butée au point " & i & " d'abscisse " & .Cells(i, 1), , "Série1"
            Exit For
        End If
    Next i
    '---2ème butée---
    debut = Application.Match(x3, .Columns(1))
    fin = Application.Match(x4, .Columns(1))
    Range(.Cells(debut, 1), .Cells(fin, 1)).Name = "X" 'plage nommée
    Range(.Cells(debut, 2), .Cells(fin, 2)).Name = "Y" 'plage nommée
    a = [LINEST(Y,X,1,1)] 'DROITEREG avec statistiques
    m = Application.Index(a, 1, 1)
    b = Application.Index(a, 1, 2)
    seb = Application.Index(a, 2, 2) 'écart-type sur b
    decal = 38 '38 fois l'écart-type
    For i = fin To .Rows.Count
        If .Cells(i, 2) > m * .Cells(i, 1) + b + decal * seb Then
            MsgBox "2ème butée au poinr " & i & " d'abscisse " & .Cells(i, 1), , "Série1"
            Exit For
        End If
    Next i
End With
End Sub

Sub Etude_Serie2()
Dim x1, x2, x3, x4, debut&, fin&, a, m, b, seb, decal, i&
x1 = 30
x2 = 20
x3 = 10
x4 = -10
With [D1].CurrentRegion
    '---1ère butée---
    debut = Application.Match(x1, .Columns(1), -1)
    fin = Application.Match(x2, .Columns(1), -1)
    Range(.Cells(debut, 1), .Cells(fin, 1)).Name = "X" 'plage nommée
    Range(.Cells(debut, 2), .Cells(fin, 2)).Name = "Y" 'plage nommée
    a = [LINEST(Y,X,1,1)] 'DROITEREG avec statistiques
    m = Application.Index(a, 1, 1)
    b = Application.Index(a, 1, 2)
    seb = Application.Index(a, 2, 2) 'écart-type sur b
    decal = 0 '0 fois l'écart-type
    For i = fin To .Rows.Count
        If .Cells(i, 2) < m * .Cells(i, 1) + b - decal * seb Then
            MsgBox "1ère butée au point " & i & " d'abscisse " & .Cells(i, 1), , "Série2"
            Exit For
        End If
    Next i
    '---2ème butée---
    debut = Application.Match(x3, .Columns(1), -1)
    fin = Application.Match(x4, .Columns(1), -1)
    Range(.Cells(debut, 1), .Cells(fin, 1)).Name = "X" 'plage nommée
    Range(.Cells(debut, 2), .Cells(fin, 2)).Name = "Y" 'plage nommée
    a = [LINEST(Y,X,1,1)] 'DROITEREG avec statistiques
    m = Application.Index(a, 1, 1)
    b = Application.Index(a, 1, 2)
    seb = Application.Index(a, 2, 2) 'écart-type sur b
    decal = 16 '16 fois l'écart-type
    For i = fin To .Rows.Count
        If .Cells(i, 2) < m * .Cells(i, 1) + b - decal * seb Then
            MsgBox "2ème butée au poinr " & i & " d'abscisse " & .Cells(i, 1), , "Série2"
            Exit For
        End If
    Next i
End With
End Sub

A+

 

[Quatr_o]

Bonsoir Dranreb,
On a déjà traité ce sujet ( ensemble d'ailleurs 🙂 ) en janvier. Voir le lien que j'ai mis.
Il me semble que c'est vraiment le même sujet, d'où mon incompréhension.

Bonjour effectivement c'est le même sujet mais pas la même courbe je n'ai pas réussi à reproduire la même chose, @sylvanu je tire mon chapeau pour votre mémoire d'éléphant 🙂

 

[Quatr_o]

Hello ,

Job75 et Dranreb vos deux solution son génial je vous remercie à tout les deux pour vos retour.
PS mon seul hic est que par moment je peux avoir une plage de mesure plus longue quel paramètre dois-je changer pour prendre en compte toutes la plage de mesure ?

 

[job75]

Hello ,

quel paramètre dois-je changer pour prendre en compte toutes la plage de mesure ?

Avec ma solution dans chaque macro les 4 variables x1 x2 x3 x4 et bien sûr decal sont à fixer en fonction des 2 premières plages que l'on voit.

 

[Quatr_o]

Avec ma solution dans chaque macro les 4 variables x1 x2 x3 x4 et bien sûr decal sont à fixer en fonction des 2 premières plages que l'on voit.

Pouvez-vous m'en dire plus svp ? ci-joint la plage avec plus de point

 

[Dranreb]

Bon, moi j'ai mis les données sous forme de tableaux en espérant que vous veillerez à ce qu'ils couvrent toujours exactement vos nouvelles données.

 

[job75]

Pouvez-vous m'en dire plus svp ? ci-joint la plage avec plus de point

Avec votre nouveau fichier les valeurs x1 x2 x3 x4 n'ont pas a être modifiées.

Par contre les 4 valeurs de decal doivent être adaptées par tâtonnement comme déjà dit.

En retenant les plus petites valeurs possibles.

On trouve ainsi les valeurs 8 72 0 19.

 

[Quatr_o]

Bon, moi j'ai mis les données sous forme de tableaux en espérant que vous veillerez à ce qu'ils couvrent toujours exactement vos nouvelles données.

Merci, il me manque juste un petit détail pour gagner en précision, que je ne trouves pas. ci-joint les valeurs qui entre dans le tableau