XL 2016 Encadrer des valeurs par des courbes de tendance

pingu

XLDnaute Nouveau
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 :
1641976063624.png


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
 

Pièces jointes

  • Classeur1 - Copie.xlsx
    14 KB · Affichages: 7
Solution
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

XLDnaute Barbatruc
Supporter XLD
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.
 

Pièces jointes

  • Classeur1 - Copie.xlsx
    15.8 KB · Affichages: 4

Sylvain

XLDnaute Occasionnel
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
 

Pièces jointes

  • Classeur1 - Copie.xlsx
    15.9 KB · Affichages: 1

Sylvain

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

Pièces jointes

  • Classeur1 - Copie.xlsx
    16.1 KB · Affichages: 8

pingu

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

Discussions similaires

Statistiques des forums

Discussions
303 823
Messages
2 014 444
Membres
219 929
dernier inscrit
Driceros