XL 2016 Encadrer des valeurs par des courbes de tendance

pingu · 12 Janvier 2022

Bonjour à tous,

Nouveau sur le forum mais ayant déja une bonne experience excel/vba, je cherche une petite aide au problème suivant (la solution étant probablement plus mathématique qu'un outil excel lui-même.

J'ai un nuage de points classique auquel je demande l'ajout d'une courbe de tendance linéaire comme ci dessous :

J'obtiens une équation avec le coefficient de corrélation qui me convient.

Je cherche maintenant à encadrer un pourcentage de la population de points (par exemple 90% des points) dans un intevalle de deux droites parallèles tracées en orange à la main ci dessus.
Mes deux droites sont donc similaires, seule l'ordonnée à l'origine change, mais comment la déterminer ?

Je joins un classeur simplifié si cela peut servir

Merci d'avance

Sylvain · 12 Janvier 2022

Autre possibilité, si l'objectif est un pourcentage sans hypothèse sur la normalité, vous pouvez prendre le classeur ci-dessous et utiliser l'outil valeur cible (données/analyse scénarios).
Si vous voulez 70% à l'intérieur vous indiquez
valeur à définir G18
Valeur à atteindre 0,7
Cellule à modifier F16

Et excel va essayer de trouver une valeur d'intervalle répondant à votre souhait d'avoir x% entre les deux droites.

sylvanu · 12 Janvier 2022

Bonjour Pingu, et bienvenu sur XLD.
En PJ un exemple de ce qui peut être fait :
1- On trace la droite typique des données en utilisant Droitereg pour obtenir a et b :

VB:

=A2*DROITEREG($B$2:$B$15;$A$2:$A$15)+INDEX(DROITEREG($B$2:$B$15;$A$2:$A$15);2)

2- On calcule l'écart type des écarts entre typique et données ( cellule H1 ) avec :

Code:

=ECARTYPE(C2:C15-B2:B15)

Formule matricielle, validez par Maj+Ctrl+Entrée
3- On calcule les courbes min et max avec :

Code:

Min=Typique - N écartype
Max=Typique + N écartype

N étant choisi en H2, avec un intervalle de confiance de 68%, 95% ou 99%

Il y a peut être plus simple, mais je ne vois pas comment.

Sylvain · 12 Janvier 2022

Bonjour,

Je propose de faire prix réel-valeur prévue.
puis je fais le calcul de l'écart type de cette nouvelle information.
Enfin, on a 87% de la population entre +- 1.5 * cet écart type.

Cela n'est valable que si les écarts restent comparables quand on part de la gauche vers la droite et suivent une loi normale

Sylvain · 12 Janvier 2022

Autre possibilité, si l'objectif est un pourcentage sans hypothèse sur la normalité, vous pouvez prendre le classeur ci-dessous et utiliser l'outil valeur cible (données/analyse scénarios).
Si vous voulez 70% à l'intérieur vous indiquez
valeur à définir G18
Valeur à atteindre 0,7
Cellule à modifier F16

Et excel va essayer de trouver une valeur d'intervalle répondant à votre souhait d'avoir x% entre les deux droites.

pingu · 12 Janvier 2022

Excellent, merci à vous deux !

La dernière solution de Sylvain correspond effectivement le plus à ma demande initiale mais la 1e solution de Sylvanu me donne aussi des idées quand à la pertinence de mon choix d'analyse (analyse 6 sigma sur la régression initiale).
Et en plus cela me fait découvrir les fonctions prévision et droitereg qui pourront toujours resservir

Bonne soirée

XL 2016 Encadrer des valeurs par des courbes de tendance

pingu

XLDnaute Nouveau

Pièces jointes

Sylvain

sylvanu

XLDnaute Barbatruc

Pièces jointes

Sylvain

XLDnaute Occasionnel

Pièces jointes

Sylvain

XLDnaute Occasionnel

Pièces jointes

pingu

XLDnaute Nouveau

Discussions similaires