Régression linéaire dans un exemple Excel. Économétrie. Régression linéaire dans MS Excel

10.07.2019

Dans les articles précédents, l'analyse se concentrait souvent sur une seule variable numérique, telle que les rendements des fonds communs de placement, les temps de chargement des pages Web ou la consommation de boissons gazeuses. Dans cette note et les suivantes, nous examinerons les méthodes permettant de prédire les valeurs d'une variable numérique en fonction des valeurs d'une ou plusieurs autres variables numériques.

Le matériel sera illustré par un exemple transversal. Prévision du volume des ventes dans un magasin de vêtements. La chaîne de magasins de vêtements discount Tournesols est en constante expansion depuis 25 ans. Cependant, l’entreprise n’a actuellement pas d’approche systématique pour sélectionner de nouveaux points de vente. Le lieu dans lequel une entreprise a l'intention d'ouvrir un nouveau magasin est déterminé sur la base de considérations subjectives. Les critères de sélection sont des conditions de location avantageuses ou l’idée du gérant quant à l’emplacement idéal du magasin. Imaginez que vous êtes à la tête du service des projets spéciaux et de la planification. Vous avez pour mission d'élaborer un plan stratégique pour l'ouverture de nouveaux magasins. Ce plan doit inclure une prévision des ventes annuelles des magasins nouvellement ouverts. Vous pensez que l'espace de vente au détail est directement lié aux revenus et souhaitez en tenir compte dans votre processus décisionnel. Comment développer un modèle statistique pour prédire les ventes annuelles en fonction de la taille d’un nouveau magasin ?

En règle générale, l'analyse de régression est utilisée pour prédire les valeurs d'une variable. Son objectif est de développer un modèle statistique capable de prédire les valeurs d'une variable dépendante, ou réponse, à partir des valeurs d'au moins une variable indépendante ou explicative. Dans cette note, nous examinerons la régression linéaire simple - une méthode statistique qui vous permet de prédire les valeurs d'une variable dépendante. Oui par valeurs de variables indépendantes X. Les notes suivantes décriront un modèle de régression multiple conçu pour prédire les valeurs d'une variable indépendante Oui basé sur les valeurs de plusieurs variables dépendantes ( X 1, X 2, …, Xk).

Téléchargez la note au format ou, exemples au format

Types de modèles de régression

Où ρ 1 – coefficient d'autocorrélation ; Si ρ 1 = 0 (pas d'autocorrélation), D≈ 2 ; Si ρ 1 ≈ 1 (autocorrélation positive), D≈ 0 ; Si ρ 1 = -1 (autocorrélation négative), D ≈ 4.

En pratique, l'application du critère de Durbin-Watson repose sur la comparaison de la valeur D avec des valeurs théoriques critiques dL Et dU pour un nombre donné d'observations n, nombre de variables indépendantes du modèle k(pour une régression linéaire simple k= 1) et niveau de signification α. Si D< d L , l'hypothèse de l'indépendance des écarts aléatoires est rejetée (il existe donc une autocorrélation positive) ; Si D>dU, l’hypothèse n’est pas rejetée (c’est-à-dire qu’il n’y a pas d’autocorrélation) ; Si dL< D < d U , il n'existe pas de motifs suffisants pour prendre une décision. Lorsque la valeur calculée D dépasse 2, alors avec dL Et dU Ce n'est pas le coefficient lui-même qui est comparé D, et l'expression (4 – D).

Pour calculer les statistiques Durbin-Watson dans Excel, tournons-nous vers le tableau du bas de la Fig. 14 Retrait du solde. Le numérateur de l'expression (10) est calculé à l'aide de la fonction =SUMMAR(array1;array2) et le dénominateur =SUMMAR(array) (Fig. 16).

Riz. 16. Formules de calcul des statistiques de Durbin-Watson

Dans notre exemple D= 0,883. La question principale est la suivante : quelle valeur de la statistique de Durbin-Watson doit être considérée comme suffisamment petite pour conclure à l’existence d’une autocorrélation positive ? Il faut corréler la valeur de D avec les valeurs critiques ( dL Et dU), en fonction du nombre d'observations n et le niveau de signification α (Fig. 17).

Riz. 17. Valeurs critiques des statistiques de Durbin-Watson (fragment de tableau)

Ainsi, dans le problème du volume des ventes dans un magasin livrant des marchandises à domicile, il existe une variable indépendante ( k= 1), 15 observations ( n= 15) et le niveau de signification α = 0,05. Ainsi, dL= 1,08 et dU= 1,36. Depuis D = 0,883 < dL= 1,08, il existe une autocorrélation positive entre les résidus, la méthode des moindres carrés ne peut pas être utilisée.

Tester des hypothèses sur la pente et le coefficient de corrélation

Ci-dessus, la régression a été utilisée uniquement à des fins de prévision. Pour déterminer les coefficients de régression et prédire la valeur d’une variable Oui pour une valeur de variable donnée X La méthode des moindres carrés a été utilisée. De plus, nous avons examiné l’erreur quadratique moyenne de l’estimation et le coefficient de corrélation mixte. Si l'analyse des résidus confirme que les conditions d'applicabilité de la méthode des moindres carrés ne sont pas violées et que le modèle de régression linéaire simple est adéquat, sur la base des données de l'échantillon, on peut affirmer qu'il existe une relation linéaire entre les variables du population.

Applicationt -critères de pente. En testant si la pente de population β 1 est égale à zéro, vous pouvez déterminer s'il existe une relation statistiquement significative entre les variables X Et Oui. Si cette hypothèse est rejetée, on peut affirmer qu'entre les variables X Et Oui il existe une relation linéaire. Les hypothèses nulles et alternatives sont formulées comme suit : H 0 : β 1 = 0 (il n'y a pas de dépendance linéaire), H1 : β 1 ≠ 0 (il y a une dépendance linéaire). Par définition t-la statistique est égale à la différence entre la pente de l'échantillon et la valeur hypothétique de la pente de la population, divisée par l'erreur quadratique moyenne de l'estimation de la pente :

(11) t = (b 1 – β 1 ) / S b 1

Où b 1 – pente de régression directe sur des données d'échantillon, β1 – pente hypothétique de population directe, et statistiques de test t a t-distribution avec n-2 degrés de liberté.

Vérifions s'il existe une relation statistiquement significative entre la taille du magasin et les ventes annuelles à α = 0,05. t-le critère est affiché avec d'autres paramètres lorsqu'il est utilisé Pack d'analyse(option Régression). Les résultats complets du package d’analyse sont présentés dans la Fig. 4, fragment lié aux statistiques t - sur la Fig. 18.

Riz. 18. Résultats de la candidature t

Depuis le nombre de magasins n= 14 (voir Fig. 3), valeur critique t-les statistiques au niveau de signification de α = 0,05 peuvent être trouvées à l'aide de la formule : tL=ETUDIANT.ARV(0,025,12) = –2,1788, où 0,025 est la moitié du niveau de signification et 12 = n – 2; tu=ÉTUDIANT.OBR(0,975,12) = +2,1788.

Depuis t-statistiques = 10,64 > tu= 2,1788 (Fig. 19), hypothèse nulle H 0 rejeté. De l'autre côté, r-valeur pour X= 10,6411, calculé par la formule =1-STUDENT.DIST(D3,12,TRUE), est approximativement égal à zéro, donc l'hypothèse H 0 encore une fois rejeté. Le fait que r-une valeur presque nulle signifie que s'il n'y avait pas de véritable relation linéaire entre la taille du magasin et les ventes annuelles, il serait pratiquement impossible de la détecter à l'aide d'une régression linéaire. Il existe donc une relation linéaire statistiquement significative entre les ventes annuelles moyennes des magasins et la taille des magasins.

Riz. 19. Test de l'hypothèse sur la pente de population à un niveau de signification de 0,05 et 12 degrés de liberté

ApplicationF -critères de pente. Une approche alternative pour tester les hypothèses sur la pente de la régression linéaire simple consiste à utiliser F-critères. Rappelons que F-test est utilisé pour tester la relation entre deux variances (pour plus de détails, voir). Lors du test de l'hypothèse de la pente, la mesure des erreurs aléatoires est la variance d'erreur (la somme des erreurs quadratiques divisée par le nombre de degrés de liberté), donc F-critère utilise le rapport de la variance expliquée par la régression (c'est-à-dire la valeur RSS, divisé par le nombre de variables indépendantes k), à la variance d'erreur ( MSE = SYX 2 ).

Par définition F-la statistique est égale au carré moyen de régression (MSR) divisé par la variance d'erreur (MSE) : F = MSR/ MSE, Où MSR=RSS / k, MSE =ESS/(n– k – 1), k– nombre de variables indépendantes dans le modèle de régression. Statistiques des tests F a F-distribution avec k Et n– k – 1 degrés de liberté.

Pour un niveau de signification α donné, la règle de décision est formulée comme suit : si F>FU, l'hypothèse nulle est rejetée ; sinon, il n'est pas rejeté. Les résultats, présentés sous forme d’un tableau récapitulatif de l’analyse de variance, sont présentés dans la Fig. 20.

Riz. 20. Tableau d'analyse de variance pour tester l'hypothèse sur la signification statistique du coefficient de régression

De même t-critère F-le critère est affiché dans le tableau lorsqu'il est utilisé Pack d'analyse(option Régression). Résultats complets des travaux Pack d'analyse sont montrés sur la Fig. 4, fragment lié à F-statistiques – sur la Fig. 21.

Riz. 21. Résultats de la candidature F-critères obtenus à l'aide du package d'analyse Excel

La statistique F est de 113,23, et r-valeur proche de zéro (cellule ImportanceF). Si le niveau de signification α est de 0,05, déterminez la valeur critique F-des distributions avec un et 12 degrés de liberté peuvent être obtenues en utilisant la formule F U=F.OBR(1-0,05;1;12) = 4,7472 (Fig. 22). Depuis F = 113,23 > F U= 4,7472, et r-valeur proche de 0< 0,05, нулевая гипотеза H 0 est rejeté, c'est-à-dire La taille d'un magasin est étroitement liée à ses ventes annuelles.

Riz. 22. Test de l'hypothèse de la pente de population à un niveau de signification de 0,05 avec un et 12 degrés de liberté

Intervalle de confiance contenant la pente β 1 . Pour tester l'hypothèse d'une relation linéaire entre les variables, vous pouvez construire un intervalle de confiance contenant la pente β 1 et vérifier que la valeur hypothétique β 1 = 0 appartient à cet intervalle. Le centre de l'intervalle de confiance contenant la pente β 1 est la pente de l'échantillon b 1 , et ses limites sont les quantités b 1 ±tn –2 S b 1

Comme le montre la fig. 18, b 1 = +1,670, n = 14, S b 1 = 0,157. t 12 =ÉTUDIANT.ARV(0,975,12) = 2,1788. Ainsi, b 1 ±tn –2 S b 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342, soit + 1,328 ≤ β 1 ≤ +2,012. Ainsi, il existe une probabilité de 0,95 que la pente de la population se situe dans l’intervalle +1,328 à +2,012 (c’est-à-dire entre 1 328 000 $ et 2 012 000 $). Étant donné que ces valeurs sont supérieures à zéro, il existe une relation linéaire statistiquement significative entre les ventes annuelles et la superficie du magasin. Si l’intervalle de confiance contenait zéro, il n’y aurait aucune relation entre les variables. De plus, l’intervalle de confiance signifie que chaque augmentation de la superficie du magasin de 1 000 m². ft. entraîne une augmentation du volume moyen des ventes comprise entre 1 328 000 $ et 2 012 000 $.

Usaget -critères pour le coefficient de corrélation. le coefficient de corrélation a été introduit r, qui est une mesure de la relation entre deux variables numériques. Il peut être utilisé pour déterminer s’il existe une relation statistiquement significative entre deux variables. Notons le coefficient de corrélation entre les populations des deux variables par le symbole ρ. Les hypothèses nulle et alternative sont formulées comme suit : H 0: ρ = 0 (pas de corrélation), H1: ρ ≠ 0 (il existe une corrélation). Vérification de l'existence d'une corrélation :

Où r = + , Si b 1 > 0, r = – , Si b 1 < 0. Тестовая статистика t a t-distribution avec n-2 degrés de liberté.

Dans le problème de la chaîne de magasins Tournesols r2= 0,904, une b1- +1,670 (voir Fig. 4). Depuis b1> 0, le coefficient de corrélation entre les ventes annuelles et la taille du magasin est r= +√0,904 = +0,951. Testons l'hypothèse nulle selon laquelle il n'y a pas de corrélation entre ces variables en utilisant t-statistiques:

Au niveau de signification de α = 0,05, l’hypothèse nulle doit être rejetée car t= 10,64 > 2,1788. Ainsi, on peut affirmer qu’il existe une relation statistiquement significative entre les ventes annuelles et la taille du magasin.

Lorsqu’on discute des inférences concernant la pente de la population, les intervalles de confiance et les tests d’hypothèses sont utilisés de manière interchangeable. Cependant, le calcul de l'intervalle de confiance contenant le coefficient de corrélation s'avère plus difficile, car le type de distribution d'échantillonnage de la statistique r dépend du véritable coefficient de corrélation.

Estimation de l'espérance mathématique et prédiction de valeurs individuelles

Cette section traite des méthodes d'estimation de l'espérance mathématique d'une réponse Oui et prédictions de valeurs individuelles Oui pour des valeurs données de la variable X.

Construire un intervalle de confiance. Dans l'exemple 2 (voir section ci-dessus Méthode des moindres carrés) l'équation de régression a permis de prédire la valeur de la variable Oui X. Dans le problème du choix d'un emplacement pour un point de vente, le volume annuel moyen des ventes dans un magasin d'une superficie de 4000 m². pieds était égal à 7,644 millions de dollars. Cependant, cette estimation des attentes mathématiques de la population générale est ponctuelle. Pour estimer l'espérance mathématique de la population, le concept d'intervalle de confiance a été proposé. De même, nous pouvons introduire le concept intervalle de confiance pour l'espérance mathématique de la réponse pour une valeur de variable donnée X:

Où , = b 0 + b 1 X je– la valeur prédite est variable Ouià X = X je, SYX– erreur quadratique moyenne, n– taille de l'échantillon, Xje- valeur spécifiée de la variable X, µ Oui|X = Xje– espérance mathématique de la variable Ouià X = X je, SSX =

L'analyse de la formule (13) montre que la largeur de l'intervalle de confiance dépend de plusieurs facteurs. À un niveau de signification donné, une augmentation de l'amplitude des fluctuations autour de la droite de régression, mesurée à l'aide de l'erreur quadratique moyenne, entraîne une augmentation de la largeur de l'intervalle. D’un autre côté, comme on pouvait s’y attendre, une augmentation de la taille de l’échantillon s’accompagne d’un rétrécissement de l’intervalle. De plus, la largeur de l'intervalle change en fonction des valeurs Xje. Si la valeur de la variable Oui prévu pour les quantités X, proche de la valeur moyenne , l'intervalle de confiance s'avère plus étroit que lors de la prédiction de la réponse pour des valeurs éloignées de la moyenne.

Disons que lors du choix d'un emplacement de magasin, nous souhaitons construire un intervalle de confiance de 95 % pour les ventes annuelles moyennes de tous les magasins dont la superficie est de 4 000 mètres carrés. pieds:

Ainsi, le volume annuel moyen des ventes dans tous les magasins d'une superficie de 4 000 m². pieds, avec une probabilité de 95%, se situe entre 6,971 et 8,317 millions de dollars.

Calculez l'intervalle de confiance pour la valeur prédite. En plus de l'intervalle de confiance pour l'espérance mathématique de la réponse pour une valeur donnée de la variable X, il est souvent nécessaire de connaître l'intervalle de confiance de la valeur prédite. Bien que la formule permettant de calculer un tel intervalle de confiance soit très similaire à la formule (13), cet intervalle contient la valeur prédite plutôt que l'estimation du paramètre. Intervalle de réponse prévue OuiX = XI pour une valeur de variable spécifique Xje déterminé par la formule :

Supposons que, lors du choix d'un emplacement pour un point de vente, nous souhaitions construire un intervalle de confiance de 95 % pour le volume de ventes annuel prévu pour un magasin dont la superficie est de 4 000 mètres carrés. pieds:

Par conséquent, le volume de ventes annuel prévu pour un magasin d'une superficie de 4 000 m². pieds, avec une probabilité de 95 %, se situe entre 5,433 et 9,854 millions de dollars. Comme nous pouvons le constater, l'intervalle de confiance pour la valeur de réponse prédite est beaucoup plus large que l'intervalle de confiance pour son espérance mathématique. En effet, la variabilité dans la prédiction des valeurs individuelles est beaucoup plus grande que dans l'estimation de l'espérance mathématique.

Pièges et problèmes éthiques associés à l’utilisation de la régression

Difficultés associées à l'analyse de régression :

Ignorer les conditions d’applicabilité de la méthode des moindres carrés.
Évaluation erronée des conditions d’applicabilité de la méthode des moindres carrés.
Choix incorrect de méthodes alternatives lorsque les conditions d'applicabilité de la méthode des moindres carrés ne sont pas respectées.
Application de l'analyse de régression sans connaissance approfondie du sujet de recherche.
Extrapoler une régression au-delà de la plage de la variable explicative.
Confusion entre relations statistiques et causales.

L'utilisation généralisée de feuilles de calcul et de logiciels statistiques a éliminé les problèmes informatiques qui entravaient le recours à l'analyse de régression. Cependant, cela a conduit au fait que l'analyse de régression était utilisée par des utilisateurs qui ne disposaient pas de qualifications et de connaissances suffisantes. Comment les utilisateurs peuvent-ils connaître les méthodes alternatives si beaucoup d’entre eux n’ont aucune idée des conditions d’applicabilité de la méthode des moindres carrés et ne savent pas comment vérifier leur mise en œuvre ?

Le chercheur ne doit pas se laisser emporter par des calculs de chiffres - en calculant le décalage, la pente et le coefficient de corrélation mixte. Il a besoin de connaissances plus approfondies. Illustrons cela avec un exemple classique tiré des manuels scolaires. Anscombe a montré que les quatre ensembles de données présentés dans la Fig. 23, ont les mêmes paramètres de régression (Fig. 24).

Riz. 23. Quatre ensembles de données artificielles

Riz. 24. Analyse de régression de quatre ensembles de données artificielles ; fini avec Pack d'analyse(cliquez sur l'image pour agrandir l'image)

Ainsi, du point de vue de l’analyse de régression, tous ces ensembles de données sont complètement identiques. Si l’analyse s’arrêtait là, nous perdrions beaucoup d’informations utiles. Ceci est démontré par les nuages de points (Figure 25) et les tracés résiduels (Figure 26) construits pour ces ensembles de données.

Riz. 25. Nuages de points pour quatre ensembles de données

Les nuages de points et les tracés résiduels indiquent que ces données diffèrent les unes des autres. Le seul ensemble distribué le long d’une ligne droite est l’ensemble A. Le tracé des résidus calculés à partir de l’ensemble A n’a aucun motif. On ne peut pas en dire autant des ensembles B, C et D. Le nuage de points tracé pour l’ensemble B montre une tendance quadratique prononcée. Cette conclusion est confirmée par le tracé résiduel, qui a une forme parabolique. Le nuage de points et le tracé des résidus montrent que l'ensemble de données B contient une valeur aberrante. Dans cette situation, il est nécessaire d’exclure la valeur aberrante de l’ensemble de données et de répéter l’analyse. Une méthode permettant de détecter et d’éliminer les valeurs aberrantes dans les observations est appelée analyse d’influence. Après avoir éliminé la valeur aberrante, le résultat de la réestimation du modèle peut être complètement différent. Le nuage de points tracé à partir des données de l'ensemble G illustre une situation inhabituelle dans laquelle le modèle empirique dépend de manière significative d'une réponse individuelle ( X8 = 19, Oui 8 = 12,5). De tels modèles de régression doivent être calculés avec une attention particulière. Ainsi, les nuages de points et les diagrammes résiduels sont un outil essentiel pour l’analyse de régression et devraient en faire partie intégrante. Sans eux, l’analyse de régression n’est pas crédible.

Riz. 26. Graphiques résiduels pour quatre ensembles de données

Comment éviter les pièges de l'analyse de régression :

Analyse des relations possibles entre les variables X Et Oui commencez toujours par dessiner un nuage de points.
Avant d'interpréter les résultats de l'analyse de régression, vérifiez les conditions de son applicabilité.
Tracez les résidus par rapport à la variable indépendante. Cela permettra de déterminer dans quelle mesure le modèle empirique correspond aux résultats d'observation et de détecter une violation de la constance de la variance.
Utilisez des histogrammes, des diagrammes à tiges et à feuilles, des diagrammes en boîte et des diagrammes de distribution normale pour tester l'hypothèse d'une distribution d'erreur normale.
Si les conditions d'applicabilité de la méthode des moindres carrés ne sont pas remplies, utilisez des méthodes alternatives (par exemple, des modèles de régression quadratique ou multiple).
Si les conditions d'applicabilité de la méthode des moindres carrés sont remplies, il est nécessaire de tester l'hypothèse sur la signification statistique des coefficients de régression et de construire des intervalles de confiance contenant l'espérance mathématique et la valeur de réponse prédite.
Évitez de prédire les valeurs de la variable dépendante en dehors de la plage de la variable indépendante.
Gardez à l’esprit que les relations statistiques ne sont pas toujours de cause à effet. N'oubliez pas que la corrélation entre les variables ne signifie pas qu'il existe une relation de cause à effet entre elles.

CV. Comme le montre le schéma fonctionnel (Figure 27), la note décrit le modèle de régression linéaire simple, les conditions de son applicabilité et comment tester ces conditions. Considéré t-critère pour tester la signification statistique de la pente de régression. Un modèle de régression a été utilisé pour prédire les valeurs de la variable dépendante. Un exemple est considéré lié au choix de l'emplacement d'un point de vente au détail, dans lequel la dépendance du volume des ventes annuelles sur la superficie du magasin est examinée. Les informations obtenues vous permettent de sélectionner plus précisément un emplacement pour un magasin et de prédire son volume de ventes annuel. Les notes suivantes poursuivront la discussion sur l'analyse de régression et examineront également plusieurs modèles de régression.

Riz. 27. Diagramme de structure des notes

Des documents du livre Levin et al. Statistics for Managers sont utilisés. – M. : Williams, 2004. – p. 792-872

Si la variable dépendante est catégorielle, une régression logistique doit être utilisée.

Régression dans Excel

Le traitement des données statistiques peut également être effectué à l'aide du module complémentaire Analysis Package dans le sous-élément de menu « Service ». Dans Excel 2003, si vous ouvrez SERVICE, nous ne trouvons pas l'onglet ANALYSE DES DONNÉES, puis cliquez sur le bouton gauche de la souris pour ouvrir l'onglet SUPERSTRUCTURES et en face du point FORFAIT ANALYSE Cliquez sur le bouton gauche de la souris pour mettre une coche (Fig. 17).

Riz. 17. Fenêtre SUPERSTRUCTURES

Après cela dans le menu SERVICE l'onglet apparaît ANALYSE DES DONNÉES.

Dans Excel 2007 à installer FORFAIT ANALYSE vous devez cliquer sur le bouton BUREAU dans le coin supérieur gauche de la feuille (Fig. 18a). Ensuite, cliquez sur le bouton PARAMÈTRES EXCEL. Dans la fenêtre qui apparaît PARAMÈTRES EXCEL clic gauche sur l'élément SUPERSTRUCTURES et dans le côté droit de la liste déroulante, sélectionnez l'élément FORFAIT ANALYSE. Cliquez ensuite sur D'ACCORD.

Options Excel Bouton Bureau

Riz. 18.Installation FORFAIT ANALYSE dans Excel 2007

Pour installer le package d'analyse, cliquez sur le bouton ALLER, situé au bas de la fenêtre ouverte. Une fenêtre apparaîtra comme le montre la Fig. 12.Cochez ci-contre FORFAIT ANALYSE. Dans l'onglet DONNÉES un bouton apparaîtra ANALYSE DES DONNÉES(Fig. 19).

Parmi les éléments suggérés, sélectionnez l'élément " RÉGRESSION" et cliquez dessus avec le bouton gauche de la souris. Ensuite, cliquez sur OK.

Une fenêtre apparaîtra comme le montre la Fig. 21

Outil d'analyse " RÉGRESSION» est utilisé pour ajuster un graphique à un ensemble d'observations en utilisant la méthode des moindres carrés. La régression est utilisée pour analyser l'effet sur une seule variable dépendante des valeurs d'une ou plusieurs variables indépendantes. Par exemple, plusieurs facteurs influencent la performance sportive d’un athlète, notamment l’âge, la taille et le poids. Il est possible de calculer dans quelle mesure chacun de ces trois facteurs influence la performance d'un athlète, puis d'utiliser ces données pour prédire la performance d'un autre athlète.

L'outil Régression utilise la fonction LIGNE.

Boîte de dialogue RÉGRESSION

Étiquettes Cochez la case si la première ligne ou la première colonne de la plage d'entrée contient des en-têtes. Décochez cette case s'il n'y a aucun en-tête. Dans ce cas, des en-têtes appropriés pour les données du tableau de sortie seront créés automatiquement.

Niveau de fiabilité Cochez la case pour inclure un niveau supplémentaire dans le tableau récapitulatif des résultats. Dans le champ approprié, saisissez le niveau de confiance que vous souhaitez appliquer, en plus du niveau par défaut de 95 %.

Constante - zéro Cochez la case pour forcer la droite de régression à passer par l'origine.

Plage de sortie Entrez la référence à la cellule supérieure gauche de la plage de sortie. Fournissez au moins sept colonnes pour le tableau récapitulatif des résultats, qui comprendront : les résultats de l'ANOVA, les coefficients, l'erreur type du calcul Y, les écarts types, le nombre d'observations, les erreurs types pour les coefficients.

Nouvelle feuille de calcul Sélectionnez cette option pour ouvrir une nouvelle feuille de calcul dans le classeur et coller les résultats de l'analyse, en commençant dans la cellule A1. Si nécessaire, saisissez un nom pour la nouvelle feuille dans le champ situé en face du bouton radio correspondant.

Nouveau classeur Sélectionnez cette option pour créer un nouveau classeur avec les résultats ajoutés à une nouvelle feuille de calcul.

Résidus Cochez la case pour inclure les résidus dans la table de sortie.

Résidus standardisés Cochez la case pour inclure les résidus standardisés dans le tableau de sortie.

Tracé des résidus Cochez la case pour tracer les résidus pour chaque variable indépendante.

Ajuster le tracé Cochez la case pour tracer les valeurs prévues par rapport aux valeurs observées.

Diagramme de probabilité normale Cochez la case pour tracer un graphique de probabilité normale.

Fonction LIGNE

Pour effectuer les calculs, sélectionnez avec le curseur la cellule dans laquelle on souhaite afficher la valeur moyenne et appuyez sur la touche = du clavier. Ensuite, dans le champ Nom, indiquez la fonction souhaitée, par exemple MOYENNE(Fig. 22).

Riz. 22 Recherche de fonctions dans Excel 2003

Si sur le terrain NOM le nom de la fonction n'apparaît pas, puis faites un clic gauche sur le triangle à côté du champ, après quoi une fenêtre avec une liste de fonctions apparaîtra. Si cette fonction n'est pas dans la liste, faites un clic gauche sur l'élément de la liste AUTRES FONCTIONS, une boîte de dialogue apparaîtra MAÎTRE DE FONCTION, dans lequel, par défilement vertical, sélectionnez la fonction souhaitée, mettez-la en surbrillance avec le curseur et cliquez sur D'ACCORD(Fig. 23).

Riz. 23. Assistant de fonction

Pour rechercher une fonction dans Excel 2007, n'importe quel onglet peut être ouvert dans le menu ; puis pour effectuer des calculs, sélectionnez avec le curseur la cellule dans laquelle on souhaite afficher la valeur moyenne et appuyez sur la touche = du clavier. Ensuite, dans le champ Nom, spécifiez la fonction MOYENNE. La fenêtre de calcul de la fonction est similaire à celle affichée dans Excel 2003.

Vous pouvez également sélectionner l'onglet Formules et faire un clic gauche sur le bouton dans le menu « FONCTION D'INSERTION" (Fig. 24), une fenêtre apparaîtra MAÎTRE DE FONCTION, dont l'apparence est similaire à Excel 2003. Également dans le menu, vous pouvez immédiatement sélectionner une catégorie de fonctions (récemment utilisées, financières, logiques, texte, date et heure, mathématiques, autres fonctions) dans lesquelles nous rechercherons le souhaité fonction.

Autres fonctionnalités Liens et tableaux

Mathématique

Riz. 24 Sélection d'une fonction dans Excel 2007

Fonction LIGNE calcule les statistiques d'une série à l'aide de la méthode des moindres carrés pour calculer la ligne droite qui se rapproche le mieux des données disponibles, puis renvoie un tableau qui décrit la ligne droite résultante. Vous pouvez également combiner la fonction LIGNE avec d'autres fonctions pour calculer d'autres types de modèles linéaires à paramètres inconnus (dont les paramètres inconnus sont linéaires), notamment les séries polynomiales, logarithmiques, exponentielles et entières. Comme elle renvoie un tableau de valeurs, la fonction doit être spécifiée sous forme de formule matricielle.

L'équation d'une droite est :

(en cas de plusieurs plages de valeurs x),

où la valeur dépendante y est fonction de la valeur indépendante x, les m valeurs sont les coefficients correspondant à chaque variable indépendante x, et b est une constante. Notez que y, x et m peuvent être des vecteurs. Fonction LIGNE renvoie un tableau . LIGNE peut également renvoyer des statistiques de régression supplémentaires.

LIGNE(known_values_y ; known_values_x ; const ; statistiques)

Known_y_values - l'ensemble des valeurs y déjà connues pour la relation.

Si le tableau known_y_values a une colonne, alors chaque colonne du tableau known_x_values est traitée comme une variable distincte.

Si le tableau known_y_values a une ligne, alors chaque ligne du tableau known_x_values est traitée comme une variable distincte.

Les valeurs x connues sont un ensemble facultatif de valeurs x déjà connues pour la relation.

Le tableau known_x_values peut contenir un ou plusieurs ensembles de variables. Si une seule variable est utilisée, alors les tableaux known_y_values et known_x_values peuvent avoir n'importe quelle forme - à condition qu'ils aient la même dimension. Si plusieurs variables sont utilisées, alors known_y_values doit être un vecteur (c'est-à-dire un intervalle d'une ligne de haut ou d'une colonne de large).

Si array_known_x_values est omis, alors le tableau (1;2;3;...) est supposé avoir la même taille que array_known_values_y.

Const est une valeur booléenne qui spécifie si la constante b doit être égale à 0.

Si l'argument "const" est VRAI ou omis, alors la constante b est évaluée comme d'habitude.

Si l'argument « const » est FAUX, alors la valeur de b est fixée à 0 et les valeurs de m sont sélectionnées de telle manière que la relation soit satisfaite.

Statistiques : valeur booléenne qui spécifie si des statistiques de régression supplémentaires doivent être renvoyées.

Si les statistiques sont VRAI, LINEST renvoie des statistiques de régression supplémentaires. Le tableau renvoyé ressemblera à ceci : (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Si les statistiques sont FAUX ou omises, LINEST renvoie uniquement les coefficients m et la constante b.

Statistiques de régression supplémentaires.

Ampleur Description se1,se2,...,sen Valeurs d'erreur standard pour les coefficients m1,m2,...,mn. seb Valeur d'erreur standard pour la constante b (seb = #N/A si const est FALSE). r2 Coefficient de déterminisme. Les valeurs réelles de y et les valeurs obtenues à partir de l'équation de la droite sont comparées ; Sur la base des résultats de la comparaison, le coefficient de déterminisme est calculé, normalisé de 0 à 1. S'il est égal à 1, alors il existe une corrélation complète avec le modèle, c'est-à-dire qu'il n'y a pas de différence entre les valeurs réelles et estimées. de y. Dans le cas contraire, si le coefficient de détermination est 0, cela ne sert à rien d'utiliser l'équation de régression pour prédire les valeurs de y. Pour plus d'informations sur la façon de calculer r2, consultez les « Remarques » à la fin de cette section. sey Erreur type pour estimer y. F Statistique F ou valeur F observée. La statistique F est utilisée pour déterminer si la relation observée entre une variable dépendante et indépendante est due au hasard. df Degrés de liberté. Les degrés de liberté sont utiles pour rechercher des valeurs F-critiques dans un tableau statistique. Pour déterminer le niveau de confiance du modèle, vous devez comparer les valeurs du tableau avec la statistique F renvoyée par la fonction LINEST. Pour plus d'informations sur le calcul de df, consultez les « Remarques » à la fin de cette section. Ensuite, l'exemple 4 montre l'utilisation des valeurs F et df. SSREG Somme des carrés de régression. ssrésider Somme résiduelle des carrés. Pour plus d'informations sur le calcul de ssreg et ssresid, consultez les « Notes » à la fin de cette section.

La figure ci-dessous montre l'ordre dans lequel les statistiques de régression supplémentaires sont renvoyées.

Remarques :

Toute ligne droite peut être décrite par sa pente et son intersection avec l'axe des y :

Pente (m) : Pour déterminer la pente d'une ligne, généralement notée m, vous prenez deux points sur la ligne et ; la pente sera égale à .

Interception Y (b) : L'ordonnée à l'origine d'une ligne, généralement notée b, est la valeur y pour le point auquel la ligne coupe l'axe y.

L'équation de la droite a la forme . Si les valeurs de m et b sont connues, alors n'importe quel point de la ligne peut être calculé en substituant les valeurs de y ou x dans l'équation. Vous pouvez également utiliser la fonction TENDANCE.

S'il n'y a qu'une seule variable indépendante x, vous pouvez obtenir la pente et l'ordonnée à l'origine directement en utilisant les formules suivantes :

Pente : INDEX(LINEST(known_y_values; known_x_values); 1)

Interception Y : INDEX(LINEST(known_y_values ; known_x_values); 2)

La précision de l'approximation utilisant la ligne droite calculée par la fonction LINEST dépend du degré de dispersion des données. Plus les données sont proches d'une ligne droite, plus le modèle utilisé par la fonction LINEST est précis. La fonction LINEST utilise les moindres carrés pour déterminer le meilleur ajustement aux données. Lorsqu'il n'y a qu'une seule variable indépendante x, m et b sont calculés à l'aide des formules suivantes :

où x et y sont des moyennes d'échantillon, par exemple x = MOYENNE (x_connus) et y = MOYENNE (y_connus).

Les fonctions d'ajustement LINEST et LGRFPRIBL peuvent calculer la ligne droite ou la courbe exponentielle qui correspond le mieux aux données. Cependant, ils ne répondent pas à la question de savoir lequel des deux résultats est le plus approprié pour résoudre le problème. Vous pouvez également évaluer la fonction TREND(known_y's; known_x's) pour une ligne droite ou la fonction GROW(known_y's; known_x's) pour une courbe exponentielle. Ces fonctions, à moins que de nouvelles valeurs x ne soient spécifiées, renvoient un tableau de valeurs y calculées pour les valeurs x réelles le long d'une ligne ou d'une courbe. Vous pourrez ensuite comparer les valeurs calculées avec les valeurs réelles. Vous pouvez également créer des graphiques pour une comparaison visuelle.

Lors de l'exécution d'une analyse de régression, Microsoft Excel calcule, pour chaque point, le carré de la différence entre la valeur y prévue et la valeur y réelle. La somme de ces carrés des différences est appelée somme résiduelle des carrés (ssresid). Microsoft Excel calcule ensuite la somme totale des carrés (sstotal). Si const = TRUE ou si la valeur de cet argument n'est pas précisée, la somme totale des carrés sera égale à la somme des carrés des différences entre les valeurs y réelles et les valeurs y moyennes. Lorsque const = FALSE, la somme totale des carrés sera égale à la somme des carrés des valeurs y réelles (sans soustraire la valeur y moyenne de la valeur y partielle). La somme des carrés de régression peut alors être calculée comme suit : ssreg = sstotal - ssresid. Plus la somme des carrés résiduelle est petite, plus la valeur du coefficient de détermination r2 est grande, ce qui montre dans quelle mesure l'équation obtenue par analyse de régression explique les relations entre les variables. Le coefficient r2 est égal à ssreg/sstotal.

Dans certains cas, une ou plusieurs colonnes X (que les valeurs Y et X soient dans les colonnes) n'ont aucune valeur prédicative supplémentaire dans les autres colonnes X. En d'autres termes, la suppression d'une ou plusieurs colonnes X peut entraîner le calcul des valeurs Y. avec la même précision. Dans ce cas, les colonnes X redondantes seront exclues du modèle de régression. Ce phénomène est appelé « colinéarité » car les colonnes redondantes de X peuvent être représentées comme la somme de plusieurs colonnes non redondantes. La fonction LINEST vérifie la colinéarité et supprime toutes les colonnes X redondantes du modèle de régression si elle les détecte. Les colonnes X supprimées peuvent être identifiées dans la sortie LINEST par un facteur de 0 et une valeur se de 0. La suppression d'une ou plusieurs colonnes comme redondantes modifie la valeur de df car elle dépend du nombre de colonnes X réellement utilisées à des fins prédictives. Pour plus d'informations sur le calcul de df, voir l'exemple 4 ci-dessous. Lorsque df change en raison de la suppression des colonnes redondantes, les valeurs de sey et F changent également. Il n'est pas recommandé d'utiliser souvent la colinéarité. Cependant, il doit être utilisé si certaines colonnes X contiennent 0 ou 1 comme indicateur indiquant si le sujet de l'expérience appartient à un groupe distinct. Si const = TRUE ou si aucune valeur pour cet argument n'est spécifiée, LINEST insère une colonne X supplémentaire pour modéliser le point d'intersection. S'il existe une colonne avec des valeurs de 1 pour les hommes et 0 pour les femmes, et qu'il existe une colonne avec des valeurs de 1 pour les femmes et 0 pour les hommes, alors la dernière colonne est supprimée car ses valeurs peuvent être obtenues. de la colonne « indicateur masculin ».

Le calcul de df pour les cas où X colonnes ne sont pas supprimées du modèle en raison de la colinéarité se produit comme suit : s'il y a k colonnes known_x et que la valeur const = TRUE ou non spécifiée, alors df = n – k – 1. Si const = FAUX, alors df = n - k. Dans les deux cas, la suppression des colonnes X en raison de la colinéarité augmente la valeur df de 1.

Les formules qui renvoient des tableaux doivent être saisies sous forme de formules matricielles.

Lorsque vous entrez un tableau de constantes comme argument, par exemple known_x_values, vous devez utiliser un point-virgule pour séparer les valeurs sur la même ligne et deux points pour séparer les lignes. Les caractères séparateurs peuvent varier en fonction des paramètres de la fenêtre Langue et paramètres du Panneau de configuration.

Il convient de noter que les valeurs y prédites par l'équation de régression peuvent ne pas être correctes si elles se situent en dehors de la plage des valeurs y utilisées pour définir l'équation.

Algorithme de base utilisé dans la fonction LIGNE, diffère de l'algorithme de la fonction principale INCLINAISON Et COUPER. La différence entre les algorithmes peut conduire à des résultats différents avec des données incertaines et colinéaires. Par exemple, si les points de données de l'argument known_y_values sont 0 et que les points de données de l'argument known_x_values sont 1, alors :

Fonction LIGNE renvoie une valeur égale à 0. Algorithme de fonction LIGNE est utilisé pour renvoyer des valeurs appropriées pour les données colinéaires, auquel cas au moins une réponse peut être trouvée.

Les fonctions SLOPE et LINE renvoient l'erreur #DIV/0! L'algorithme des fonctions PENTE et INTERCEPT permet de trouver une seule réponse, mais dans ce cas il peut y en avoir plusieurs.

En plus de calculer des statistiques pour d'autres types de régression, LINEST peut être utilisé pour calculer des plages pour d'autres types de régression en entrant les fonctions des variables x et y sous forme de séries de variables x et y pour LINEST. Par exemple, la formule suivante :

LIGNEST(valeurs_y, valeurs_x^COLONNE($A:$C))

fonctionne en ayant une colonne de valeurs Y et une colonne de valeurs X pour calculer une approximation cubique (polynôme du 3ème degré) de la forme suivante :

La formule peut être modifiée pour calculer d'autres types de régression, mais dans certains cas, des ajustements des valeurs de sortie et d'autres statistiques sont nécessaires.

Le traitement des données statistiques peut également être effectué à l'aide d'un module complémentaire FORFAIT ANALYSE(Fig. 62).

Parmi les éléments suggérés, sélectionnez l'élément " RÉGRESSION" et cliquez dessus avec le bouton gauche de la souris. Ensuite, cliquez sur OK.

Une fenêtre apparaîtra comme le montre la Fig. 63.

L'outil Régression utilise la fonction LIGNE.

Boîte de dialogue RÉGRESSION

Constante - zéro Cochez la case pour forcer la droite de régression à passer par l'origine.

Nouveau classeur Sélectionnez cette option pour créer un nouveau classeur avec les résultats ajoutés à une nouvelle feuille de calcul.

Résidus Cochez la case pour inclure les résidus dans la table de sortie.

Résidus standardisés Cochez la case pour inclure les résidus standardisés dans le tableau de sortie.

Tracé des résidus Cochez la case pour tracer les résidus pour chaque variable indépendante.

Ajuster le tracé Cochez la case pour tracer les valeurs prévues par rapport aux valeurs observées.

Diagramme de probabilité normale Cochez la case pour tracer un graphique de probabilité normale.

Fonction LIGNE

L'équation d'une droite est :

y=m 1 x 1 +m 2 x 2 +…+b (en cas de plusieurs plages de valeurs x),

où la valeur dépendante y est fonction de la valeur indépendante x, les m valeurs sont les coefficients correspondant à chaque variable indépendante x, et b est une constante. Notez que y, x et m peuvent être des vecteurs. Fonction LIGNE renvoie un tableau(mn;mn-1;…;m 1 ;b). LIGNE peut également renvoyer des statistiques de régression supplémentaires.

LIGNE(known_values_y ; known_values_x ; const ; statistiques)

Known_y-values - l'ensemble des valeurs y déjà connues pour la relation y=mx+b.

Si le tableau known_y_values a une colonne, alors chaque colonne du tableau known_x_values est traitée comme une variable distincte.

Si le tableau known_y_values a une ligne, alors chaque ligne du tableau known_x_values est traitée comme une variable distincte.

Les valeurs x connues sont un ensemble facultatif de valeurs x déjà connues pour la relation y=mx+b.

Si array_known_x_values est omis, alors le tableau (1;2;3;...) est supposé avoir la même taille que array_known_values_y.

Const est une valeur booléenne qui spécifie si la constante b doit être égale à 0.

Si l'argument "const" est VRAI ou omis, alors la constante b est évaluée comme d'habitude.

Si l'argument « const » est FAUX, alors la valeur de b est fixée à 0 et les valeurs de m sont sélectionnées de telle manière que la relation y=mx soit satisfaite.

Statistiques : valeur booléenne qui spécifie si des statistiques de régression supplémentaires doivent être renvoyées.

Si les statistiques sont FAUX ou omises, LINEST renvoie uniquement les coefficients m et la constante b.

Statistiques de régression supplémentaires (tableau 17)

Ampleur	Description
se1,se2,...,sen	Valeurs d'erreur standard pour les coefficients m1,m2,...,mn.
seb	Valeur d'erreur standard pour la constante b (seb = #N/A si const est FALSE).
r2	Coefficient de déterminisme. Les valeurs réelles de y et les valeurs obtenues à partir de l'équation de la droite sont comparées ; Sur la base des résultats de la comparaison, le coefficient de déterminisme est calculé, normalisé de 0 à 1. S'il est égal à 1, alors il existe une corrélation complète avec le modèle, c'est-à-dire qu'il n'y a pas de différence entre les valeurs réelles et estimées. de y. Dans le cas contraire, si le coefficient de détermination est 0, cela ne sert à rien d'utiliser l'équation de régression pour prédire les valeurs de y. Pour plus d'informations sur la façon de calculer r2, consultez les « Remarques » à la fin de cette section.
sey	Erreur type pour estimer y.
F	Statistique F ou valeur F observée. La statistique F est utilisée pour déterminer si la relation observée entre une variable dépendante et indépendante est due au hasard.
df	Degrés de liberté. Les degrés de liberté sont utiles pour rechercher des valeurs F-critiques dans un tableau statistique. Pour déterminer le niveau de confiance du modèle, vous devez comparer les valeurs du tableau avec la statistique F renvoyée par la fonction LINEST. Pour plus d'informations sur le calcul de df, consultez les « Remarques » à la fin de cette section. Ensuite, l'exemple 4 montre l'utilisation des valeurs F et df.
SSREG	Somme des carrés de régression.
ssrésider	Somme résiduelle des carrés. Pour plus d'informations sur le calcul de ssreg et ssresid, consultez les « Notes » à la fin de cette section.

La figure ci-dessous montre l'ordre dans lequel les statistiques de régression supplémentaires sont renvoyées (Figure 64).

Remarques :

Toute ligne droite peut être décrite par sa pente et son intersection avec l'axe des y :

Pente (m) : Pour déterminer la pente d'une ligne, généralement notée m, vous devez prendre deux points sur la ligne (x 1 ,y 1) et (x 2 ,y 2) ; la pente sera égale à (y 2 -y 1)/(x 2 -x 1).

Interception Y (b) : L'ordonnée à l'origine d'une ligne, généralement notée b, est la valeur y pour le point auquel la ligne coupe l'axe y.

L'équation de la droite est y=mx+b. Si les valeurs de m et b sont connues, alors n'importe quel point de la ligne peut être calculé en remplaçant les valeurs de y ou x dans l'équation. Vous pouvez également utiliser la fonction TENDANCE.

S'il n'y a qu'une seule variable indépendante x, vous pouvez obtenir la pente et l'ordonnée à l'origine directement en utilisant les formules suivantes :

Pente : INDEX(LINEST(known_y_values; known_x_values); 1)

Interception Y : INDEX(LINEST(known_y_values ; known_x_values); 2)

où x et y sont des moyennes d'échantillon, par exemple x = MOYENNE (x_connus) et y = MOYENNE (y_connus).

Les formules qui renvoient des tableaux doivent être saisies sous forme de formules matricielles.

Les fonctions SLOPE et LINE renvoient l'erreur #DIV/0! L'algorithme des fonctions PENTE et INTERCEPT permet de trouver une seule réponse, mais dans ce cas il peut y en avoir plusieurs.

LIGNEST(valeurs_y, valeurs_x^COLONNE($A:$C))

fonctionne en ayant une colonne de valeurs Y et une colonne de valeurs X pour calculer une approximation cubique (polynôme du 3ème degré) de la forme suivante :

y=m 1 x+m 2 x 2 +m 3 x 3 +b

La formule peut être modifiée pour calculer d'autres types de régression, mais dans certains cas, des ajustements des valeurs de sortie et d'autres statistiques sont nécessaires.

L'analyse de régression et de corrélation sont des méthodes de recherche statistique. Ce sont les manières les plus courantes de montrer la dépendance d’un paramètre à une ou plusieurs variables indépendantes.

Ci-dessous, à l’aide d’exemples pratiques précis, nous examinerons ces deux analyses très populaires parmi les économistes. Nous donnerons également un exemple d'obtention de résultats en les combinant.

Analyse de régression dans Excel

Montre l'influence de certaines valeurs (indépendantes, indépendantes) sur la variable dépendante. Par exemple, dans quelle mesure le nombre de personnes économiquement actives dépend-il du nombre d'entreprises, des salaires et d'autres paramètres. Ou encore : comment les investissements étrangers, les prix de l’énergie, etc. affectent-ils le niveau du PIB.

Le résultat de l'analyse permet de mettre en évidence les priorités. Et sur la base des principaux facteurs, prévoir, planifier l'évolution des domaines prioritaires et prendre des décisions de gestion.

La régression se produit :

linéaire (y = a + bx) ;
parabolique (y = a + bx + cx 2) ;
exponentiel (y = a * exp(bx));
puissance (y = a*x^b) ;
hyperbolique (y = b/x + a);
logarithmique (y = b * 1n(x) + a);
exponentielle (y = a * b^x).

Examinons un exemple de création d'un modèle de régression dans Excel et d'interprétation des résultats. Prenons le type de régression linéaire.

Tâche. Dans 6 entreprises, le salaire mensuel moyen et le nombre de salariés qui quittent l'entreprise ont été analysés. Il est nécessaire de déterminer la dépendance du nombre de salariés qui quittent leur emploi par rapport au salaire moyen.

Le modèle de régression linéaire ressemble à ceci :

Y = une 0 + une 1 x 1 +…+une k x k.

Où a sont des coefficients de régression, x sont des variables d'influence, k est le nombre de facteurs.

Dans notre exemple, Y est l’indicateur de départ d’employés. Le facteur d'influence est le salaire (x).

Excel possède des fonctions intégrées qui peuvent vous aider à calculer les paramètres d'un modèle de régression linéaire. Mais le module complémentaire « Analysis Package » le fera plus rapidement.

Nous activons un outil analytique puissant :

Une fois activé, le module complémentaire sera disponible dans l'onglet Données.

Faisons maintenant l'analyse de régression elle-même.

Tout d’abord, nous prêtons attention au R-carré et aux coefficients.

R-carré est le coefficient de détermination. Dans notre exemple – 0,755, ou 75,5 %. Cela signifie que les paramètres calculés du modèle expliquent 75,5% de la relation entre les paramètres étudiés. Plus le coefficient de détermination est élevé, meilleur est le modèle. Bon - supérieur à 0,8. Mauvais – moins de 0,5 (une telle analyse peut difficilement être considérée comme raisonnable). Dans notre exemple – « pas mal ».

Le coefficient 64,1428 montre ce que sera Y si toutes les variables du modèle considéré sont égales à 0. Autrement dit, la valeur du paramètre analysé est également influencée par d'autres facteurs non décrits dans le modèle.

Le coefficient -0,16285 montre le poids de la variable X sur Y. Autrement dit, le salaire mensuel moyen dans ce modèle affecte le nombre d'abandons avec un poids de -0,16285 (il s'agit d'un faible degré d'influence). Le signe « - » indique un impact négatif : plus le salaire est élevé, moins il y a de démissions. Ce qui est juste.

Analyse de corrélation dans Excel

L'analyse de corrélation permet de déterminer s'il existe une relation entre les indicateurs dans un ou deux échantillons. Par exemple, entre la durée de fonctionnement d'une machine et le coût des réparations, le prix du matériel et la durée de fonctionnement, la taille et le poids des enfants, etc.

S'il existe un lien, une augmentation d'un paramètre entraîne-t-elle une augmentation (corrélation positive) ou une diminution (négative) de l'autre. L'analyse de corrélation aide l'analyste à déterminer si la valeur d'un indicateur peut être utilisée pour prédire la valeur possible d'un autre.

Le coefficient de corrélation est noté r. Varie de +1 à -1. La classification des corrélations pour différents domaines sera différente. Lorsque le coefficient est égal à 0, il n’existe pas de relation linéaire entre les échantillons.

Voyons comment trouver le coefficient de corrélation à l'aide d'Excel.

Pour trouver des coefficients appariés, la fonction CORREL est utilisée.

Objectif : Déterminer s'il existe une relation entre la durée de fonctionnement d'un tour et le coût de son entretien.

Placez le curseur dans n'importe quelle cellule et appuyez sur le bouton fx.

Dans la catégorie « Statistique », sélectionnez la fonction CORREL.
Argument « Tableau 1 » - la première plage de valeurs – temps de fonctionnement de la machine : A2 : A14.
Argument « Tableau 2 » - deuxième plage de valeurs – coût de réparation : B2:B14. Cliquez sur OK.

Pour déterminer le type de connexion, il faut regarder le nombre absolu du coefficient (chaque domaine d'activité a son propre barème).

Pour l'analyse de corrélation de plusieurs paramètres (plus de 2), il est plus pratique d'utiliser « Data Analysis » (le module complémentaire « Analysis Package »). Vous devez sélectionner la corrélation dans la liste et désigner le tableau. Tous.

Les coefficients résultants seront affichés dans la matrice de corrélation. Comme ça:

Analyse de corrélation et de régression

En pratique, ces deux techniques sont souvent utilisées ensemble.

Exemple:

Désormais, les données de l'analyse de régression sont devenues visibles.

La méthode de régression linéaire nous permet de décrire une droite qui correspond le mieux à une série de paires ordonnées (x, y). L’équation d’une droite, connue sous le nom d’équation linéaire, est donnée ci-dessous :

ŷ est la valeur attendue de y pour une valeur donnée de x,

x est une variable indépendante,

a est un segment sur l'axe y pour une ligne droite,

b est la pente de la droite.

La figure ci-dessous illustre graphiquement ce concept :

La figure ci-dessus montre la droite décrite par l'équation ŷ =2+0,5x. L'ordonnée à l'origine est le point auquel la ligne coupe l'axe y ; dans notre cas, a = 2. La pente de la ligne, b, le rapport entre la montée de la ligne et la longueur de la ligne, a une valeur de 0,5. Une pente positive signifie que la ligne monte de gauche à droite. Si b = 0, la ligne est horizontale, ce qui signifie qu’il n’y a aucune relation entre les variables dépendantes et indépendantes. En d’autres termes, changer la valeur de x n’affecte pas la valeur de y.

ŷ et y sont souvent confondus. Le graphique montre 6 paires ordonnées de points et une ligne, selon l'équation donnée

Cette figure montre le point correspondant à la paire ordonnée x = 2 et y = 4. Notez que la valeur attendue de y selon la droite en X= 2 est ŷ. Nous pouvons le confirmer avec l’équation suivante :

ŷ = 2 + 0,5х =2 +0,5(2) =3.

La valeur y représente le point réel et la valeur ŷ est la valeur attendue de y en utilisant une équation linéaire pour une valeur donnée de x.

L'étape suivante consiste à déterminer l'équation linéaire qui correspond le mieux à l'ensemble des paires ordonnées, nous en avons parlé dans l'article précédent, où nous avons déterminé le type d'équation par .

Utiliser Excel pour définir la régression linéaire

Afin d'utiliser l'outil d'analyse de régression intégré à Excel, vous devez activer le complément Pack d'analyse. Vous pouvez le trouver en cliquant sur l'onglet Fichier -> Options(2007+), dans la boîte de dialogue qui apparaît PossibilitésExceller allez dans l'onglet Modules complémentaires. Dans le champ Contrôle choisir Modules complémentairesExceller et cliquez Aller. Dans la fenêtre qui apparaît, cochez la case à côté de Package d'analyse, cliquez D'ACCORD.

Dans l'onglet Données dans le groupe Analyse un nouveau bouton apparaîtra Analyse des données.

Pour démontrer le travail du complément, nous utiliserons des données où un homme et une fille partagent une table dans la salle de bain. Saisissez les données de notre exemple de salle de bain dans les colonnes A et B de la feuille vierge.

Allez dans l'onglet Données, dans le groupe Analyse cliquez Analyse des données. Dans la fenêtre qui apparaît Analyse des données sélectionner Régression comme indiqué sur la figure et cliquez sur OK.

Définissez les paramètres de régression nécessaires dans la fenêtre Régression comme le montre l'image :

Cliquez D'ACCORD. La figure ci-dessous montre les résultats obtenus :

Ces résultats sont cohérents avec ceux que nous avons obtenus en effectuant nos propres calculs dans .

Régression linéaire dans un exemple Excel. Économétrie. Régression linéaire dans MS Excel

Régression dans Excel

Analyse de régression dans Excel

Analyse de corrélation dans Excel

Analyse de corrélation et de régression

Utiliser Excel pour définir la régression linéaire

Signaler une faute de frappe

Texte qui sera envoyé à nos rédacteurs :

Votre commentaire (facultatif) :