XL 2016 détection changement de pente

[Quatr_o]

Bonjour,

Je souhaiterais déterminer automatique par calcul, les points de changement de pente P1,P2,P3 et P4 de la courbe du fichier excel joint.
Les résultats proche attendu sont en H2 et H3.
J'ai trouvé dans le forum un sujet qui y ressemble, mais je n'ai pas réussi à m'en inspiré. ( https://excel-downloads.com/threads/traitement-de-donnees-resolu.225997/)

merci d'avance de votre aide qui me sera précieuse et me facilitera dans mes analyses future

 

[sylvanu]

Bonjour FaysalH,
Je ne pense pas que l'exemple donné soit applicable à vos séries de données.
Je pense qu'une solution est de passer par les droites de régression, les écarts obtenus sont très faibles.
Voir ce lien, le fichier ayant un peu "grossi", il ne passe plus en PJ : https://www.cjoint.com/c/MArxIMtOAks

 

[Dranreb]

Bonsoir.
Connait-on les valeurs X des points de changements de pentes ?
Dans cette hypothèse un début d'étude de la courbe ALLER avec un tout petit peu de fonctions perso en VBA, dont on pourrait à la rigueur se passer, mais alors avec plus de formules posées dans la feuille.

 

[Quatr_o]

Bonjour à tous,

Tout d'abord, je vous remercie pour votre retour REMARQUABLE et rapide, j'ai modifié mon fichier source en retirant quelque point afin d'alléger le fichier.
La proposition de @Dranreb me parait plus plausible sur l'attendu, reste à déterminer le point de changement de pente car non on ne connait pas les valeurs X des points de changements de pentes.
Peut-on se passer dans un premier temps des fonctions VBA afin de mieux comprendre la méthode, je ne suis pas très à l'aise en VBA ?

@sylvanu je remarque que la courbe passé par des droites de régression c'est légèrement décaler sur l'axe en X cela a-t-il un impact sur la valeur trouvé ?

 

[sylvanu]

Bonjour,

je remarque que la courbe passé par des droites de régression

Non, elles ne sont là que pour la représentation graphique. Les données sont extraites des formules.
Mais au vu de vos courbes, les ruptures de pente se font dans un intervalle assez large. Je pense que la solution des pentes de régression donne un résultat probant.

 

[Dranreb]

Si on ne connait pas les valeurs X des changements de pente il ne reste plus qu'à les chercher avec le solveur en cherchant à minimiser la somme des carrés des écarts.
Ma fonction Termes2Pts(X, X1, X2) renvoie 4 colonnes: X, Abs(X-X1), Abs(X-X2) et enfin 1 en vue d'appliquer une constante.
Pour le calculer sans VBA il faut donc préparer au moins 2 colonne supplémentaires pour les ABS(X - …) afin d'y appliquer le DROITEREG (il gère la constante s'il en faut une)
Ma fonction SolMoindresCarrés est claire, non ?
Elle renvoie donc ici un coefficient appliquée à X, deux autres appliqués à ABS(X - X1) et ABS(X - X2) et enfin une constante globale.

 

[Quatr_o]

Bonjour,

Non, elles ne sont là que pour la représentation graphique. Les données sont extraites des formules.
Mais au vu de vos courbes, les ruptures de pente se font dans un intervalle assez large. Je pense que la solution des pentes de régression donne un résultat probant.

Dans cette méthodes on prend en compte tout les points >0 ou <0 pour les pentes de régression, je souhaiterais ne pas prendre en compte les points de déflection qui pourrait biaiser la mesure. j'espère être explicite

 

[Quatr_o]

Si on ne connait pas les valeurs X des changements de pente il ne reste plus qu'à les chercher avec le solveur en cherchant à minimiser la somme des carrés des écarts.
Ma fonction Termes2Pts(X, X1, X2) renvoie 4 colonnes: X, Abs(X-X1), Abs(X-X2) et enfin 1 en vue d'appliquer une constante.
Pour le calculer sans VBA il faut donc préparer au moins 2 colonne supplémentaires pour les ABS(X - …) afin d'y appliquer le DROITEREG (il gère la constante s'il en faut une)
Ma fonction SolMoindresCarrés est claire, non ?
Elle renvoie donc ici un coefficient appliquée à X, deux autres appliqués à ABS(X - X1) et ABS(X - X2) et enfin une constante globale.

Tout à fait j'aimerai d'une façon automatique par calcul déterminer les valeurs en X. ceci me permettra d'avoir la valeur en Y

 

[Dranreb]

Je ne sais pas calculer ces valeurs X. Mais le Solveur le pourrait.

Il le peut en effet: j'ai cherché avec le Solveur à minimiser P13 en faisant varier O3 et O4.

 

[sylvanu]

Bonsoir Faysalh, Dranreb,

Dans cette méthodes on prend en compte tout les points >0 ou <0 pour les pentes de régression, je souhaiterais ne pas prendre en compte les points de déflection qui pourrait biaiser la mesure.

Regardez ce graphique, en vert vos échantillons, en rouge avec tous les X>0, en bleu la même sans les points de déflection. On voit que les deux droites de régression sont très proches l'une de l'autre.
La première donnerait un point d'inflexion à 3.05 la seconde à 3.11.

J'ai l'impression que vous cherchez des valeurs utopiques, ce n'est pas parce que XL vous donnera 5 chiffres après la virgule que cela sera la réalité.
Vos échantillons ne montrent pas de points d'inflexion, mais seulement des zones d'inflexion.
regardez la courbe verte : quel est le point d'inflexion ? Vous avez le choix entre 2.5 et 3.5. La zone d'inflexion est quelque part pour 0<X<0.3. Toute précision plus poussée ne serait qu'une vue de l'esprit d'un point de vue physique.
NB: En électronique, comme on ne sait pas à quel moment le point d'inflexion se trouve, on prend arbitrairement 50% de la valeur établie. Dans votre cas, arbitrairement cela ferait 1.555 pour X=0.038.
On sait que c'est absurde d'un point de vue physique, mais au moins tout le monde parle la même langue.

 

[Quatr_o]

Bonsoir Faysalh, Dranreb,

Regardez ce graphique, en vert vos échantillons, en rouge avec tous les X>0, en bleu la même sans les points de déflection. On voit que les deux droites de régression sont très proches l'une de l'autre.
La première donnerait un point d'inflexion à 3.05 la seconde à 3.11.
Regarde la pièce jointe 1160734
J'ai l'impression que vous cherchez des valeurs utopiques, ce n'est pas parce que XL vous donnera 5 chiffres après la virgule que cela sera la réalité.
Vos échantillons ne montrent pas de points d'inflexion, mais seulement des zones d'inflexion.
regardez la courbe verte : quel est le point d'inflexion ? Vous avez le choix entre 2.5 et 3.5. La zone d'inflexion est quelque part pour 0<X<0.3. Toute précision plus poussée ne serait qu'une vue de l'esprit d'un point de vue physique.
NB: En électronique, comme on ne sait pas à quel moment le point d'inflexion se trouve, on prend arbitrairement 50% de la valeur établie. Dans votre cas, arbitrairement cela ferait 1.555 pour X=0.038.
On sait que c'est absurde d'un point de vue physique, mais au moins tout le monde parle la même langue.

Bonjour,
Après reflexion il est vrai que le point d'inflexion peut varié suivant l'intérprétation de chacun c'est pour cela que je vais passé par l'interception de équation de droite. pour l'aller j'ai réussi à avoir ce que je voulais mais pour le retour je n'ai pas réussi à trouver la formule les cellules en texte rouge colonne J les erreurs avez vous une idée ?
je souhaiterai également automatiser l'affichage de courbe des cellules en couleurs avez un code pour cela ?

 

[Dranreb]

À tout hasard je joins quand même ma dernière version où j'avais aussi fait le calcul pour le retour.

 

[Quatr_o]

À tout hasard je joins quand même ma dernière version où j'avais aussi fait le calcul pour le retour.

Merci votre fichier m’aidera aussi dans mes analyses.

 

[sylvanu]

Bonjour Quatr_o, Dranreb,

mais pour le retour je n'ai pas réussi

En PJ un essai sur une approche avec la fonction EQUIV au lieu de RECHERCHE qui évite les #N/A.
de plus le retour s'effectue sur les colonne DE et non AB.

je souhaiterai également automatiser l'affichage de courbe des cellules en couleurs avez un code pour cela ?

Qu'appelez vous un code ? Du VBA ?

 

[Quatr_o]

Bonjour Sylvanu et Dranreb,

Un grand merci poru votre aide et implication qui m'est précieuse.
Je ne vais pas abuser de votre gentillesses quand je dis "code" oui du VBA ou une méthodes pour automatiser l'affichage visuel des courbes des celules en couleurs avec l'affichage des courbes de tendances.

PS : merci pour la petite coquille sur le retour.