Régression dans Excel: Exemples d'équations. régression linéaire

L'analyse de régression – une méthode d'étude statistique pour montrer la dépendance d'un paramètre d'une ou plusieurs variables indépendantes. Dans l'ère pré-informatique, son utilisation a été assez difficile, surtout quand il est venu à de gros volumes de données. Aujourd'hui, apprendre comment construire une régression dans Excel, vous pouvez résoudre des problèmes statistiques complexes en seulement quelques minutes. Voici des exemples d'économie spécifiques.

types de régression

Ce concept a été introduit aux mathématiques par Francis Galton en 1886. La régression est:

• linéaire;
• parabolique;
• puissance;
• exponentielle;
• hyperbolique;
• exponentielle;
• logarithmique.

EXEMPLE 1

Considérons le problème de la détermination de la dépendance à l'égard du nombre de démissions des membres du personnel du salaire moyen dans les 6 entreprises industrielles.

Tâche. Six entreprises ont analysé le salaire mensuel moyen et le nombre d'employés qui quittent volontairement. Sous forme de tableau, nous avons:

Pour le problème de la détermination de la dépendance des séparations montant travailleurs du salaire moyen d' un modèle de régression de 6 entreprises a la forme d'équation Y = a ₀ + a ₁ x ₁ + … + a _k x _k, où x _i – grandeurs d' influence, un _i – coefficients de régression, ak – nombre de facteurs.

Y pour une tâche donnée – il est un indicateur de congédier un employé, un facteur – le salaire, qui est désignée par X.

Exploiter la puissance de calcul « Excel »

L'analyse de régression dans Excel doit être précédée d'une application aux données de table existantes fonctions intégrées. Cependant, à ces fins, il est préférable d'utiliser un outil très utile add-in « analyse de paquets ». Pour cela, vous devez activer:

• avec l'onglet « Fichier » Allez dans « Paramètres »;
• dans la fenêtre qui apparaît, sélectionnez « Add-ons »;
• cliquez sur le bouton « Go », situé en bas à droite de la ligne « gestion »;
• mettre une coche à côté de « l'utilitaire d'analyse » et confirmez votre choix en appuyant sur « OK ».

Si cela est fait correctement, le côté droit de l'onglet « Données », situé au-dessus de la feuille de travail « Excel », indique le bouton désiré.

Régression linéaire dans Excel

Maintenant que vous avez sous la main tous les outils virtuels nécessaires pour les calculs économétriques, nous pouvons commencer à résoudre notre problème. Pour ce faire:

• bouton est cliqué sur le « Analyse des données »;
• cliquez sur le bouton « régression » dans la fenêtre ouverte;
• un onglet qui semble présenter une plage de valeurs Y (le nombre de travailleurs de séparations) et X (leur salaire);
• réaffirmer leurs actions en appuyant sur le bouton «Ok».

En conséquence, le programme remplira automatiquement la nouvelle analyse de régression des données de feuille de feuille. Faites attention! Dans Excel, il est possible de définir l'endroit que vous préférez à cette fin. Par exemple, il peut être la même feuille, où les valeurs Y et X, ou même un nouveau livre, spécialement conçu pour le stockage de ces données.

résultats de l'analyse de régression pour R-carré

Les données Excel obtenues dans les données d'exemple considérés ont la forme:

Tout d'abord, nous devons prêter attention à la valeur de R-carré. Il représente le coefficient de détermination. Dans cet exemple, R-carré = 0,755 (75,5%), m. E. Les paramètres calculés du modèle pour expliquer la relation entre les paramètres pris en compte par 75,5%. Plus la valeur du coefficient de détermination, le modèle choisi est considéré comme plus utile pour des tâches particulières. On croit décrire correctement la situation réelle à la valeur R-carré au-dessus de 0,8. Si le R-carré <0,5, puis une analyse de régression dans Excel ne peut pas être considéré comme raisonnable.

analyse des ratios

Nombre 64,1428 montre ce que sera la valeur du Y, si toutes les variables xi dans notre modèle sera remis à zéro. En d'autres termes, on peut dire que la valeur du paramètre analysé est influencée par d'autres facteurs que ceux décrits dans le modèle spécifique.

Le prochain facteur -0,16285 situé dans cellule B18, montre l'influence de la variable X à Y. Cela signifie que le salaire moyen des employés au sein du modèle affecte le nombre de démissions du poids de -0,16285, t. E. Le degré de l'impact du tout petit. Le signe « – » indique que le coefficient est négatif. Il est évident, puisque nous savons tous que le plus de salaire dans l'entreprise, moins les gens ont exprimé le désir de mettre fin au contrat de travail ou licenciés.

régression multiple

Sous ce terme se réfère à l'équation de communication avec plusieurs variables indépendantes de la forme:

y = f (x ₁ + x ₂ + … x _m) + ε, où Y – représente un indice de caractéristique (la variable dépendante), et x _1, x _2, … x _m – sont des facteurs de signes (variables indépendantes).

estimation des paramètres

Par régression multiple (MR), il est effectué en utilisant une méthode des moindres carrés (LSM). Pour les équations linéaires de la forme Y = a + b ₁ x _{1 + … + b m x m +} ε la construction d' un système d'équations normales (cm. Ci – dessous)

Pour comprendre le principe de la méthode, nous considérons le cas à deux facteurs. Ensuite, nous avons la situation décrite par la formule

, On obtient donc:

où σ – est la variance de la fonction respective, reflétée dans l'indice.

MNC est applicable à l'équation MR à standartiziruemom échelle. Dans ce cas, nous obtenons l'équation:

dans lequel t _y, t _{x 1, …} t _xm – standartiziruemye variables pour lesquelles les valeurs moyennes sont 0; ß _i – coefficients de régression normalisés et l' écart type – 1.

S'il vous plaît noter que tous les _i p dans ce cas défini comme le tsentraliziruemye normalisé et, par conséquent, une comparaison entre une considérée comme valide et acceptable. En outre, il est accepté d'effectuer le dépistage des facteurs, en écartant ceux qui ont les valeurs les plus faibles de ßi.

Le problème avec l'utilisation de l'équation de régression linéaire

Supposons que vous ayez une table de la dynamique du prix d'un produit N particulier pour les 8 derniers mois. Il est nécessaire de décider si l'acquisition de son parti au prix de 1850 roubles. / T.

Pour résoudre ce problème dans le processeur sous forme de tableau « Excel » nécessaire pour utiliser déjà connu par exemple l'outil « Analyse des données » présentées ci-dessus. Ensuite, choisissez la section « régression » et les paramètres définis. Il faut se rappeler que dans la « plage d'entrée Y» devrait être introduit dans une plage de valeurs de la variable dépendante (dans ce cas, le prix des marchandises en certains mois de l'année) et dans le « intervalle d'entrée X» – pour un organisme indépendant (le mois). Nous confirmons l'action en cliquant sur «Ok». Dans une nouvelle feuille de calcul (si indiqué), on obtient les données de la régression.

Nous misons sur les équations linéaires de la forme y = ax + b, comme les paramètres a et b sont les coefficients du numéro de ligne du mois et le nom des coefficients et la ligne «Y-intersection » de la feuille avec les résultats de l'analyse de régression. Ainsi, l'équation de régression linéaire (EQ) 3 pour le problème peut être écrit:

Le prix des marchandises N = 11714 * 1727,54 nombre + mois.

ou en notation algébrique

y = 11714 x + 1727,54

analyse des résultats

Pour décider si le reçu de manière adéquate équation de régression linéaire en utilisant les coefficients de corrélation multiple (CMC) et déterminer ainsi que le test et le test t de Fisher. Dans le tableau « Excel » régression avec les résultats qu'ils agissent sous les noms multiples R, R-carrés, statistiques F-t-et statistiques, respectivement.

KMC R permet d'estimer la relation probabiliste de rapprochement entre les variables indépendantes et dépendantes. Sa valeur élevée indique une connexion assez forte entre la variable « Nombre de mois » et « N Prix de produit en roubles pour 1 tonne. » Cependant, la nature de cette relation est inconnue.

Le carré du coefficient de détermination R ² (RI) est une caractéristique numérique de la proportion de la dispersion totale et présente une dispersion de la partie de données expérimentales, à savoir, valeurs de la variable dépendante correspondant à une équation de régression linéaire. Dans ce problème, cette valeur est de 84,8%, point de fusion. E. Statistiques avec un haut degré de précision obtenu sont décrits SD.

F-statistiques, aussi connu comme critère Fisher utilisé pour évaluer l'importance de la dépendance linéaire ou d'infirmer l'hypothèse confirmant son existence.

La valeur de la statistique t (test t de Student) permet d'évaluer l'importance du coefficient à tout membre de la dépendance linéaire inconnue libre. Si la valeur de test t> _{cr t,} l'hypothèse d'une équation linéaire insignifiance de libre terme est rejeté.

Dans ce problème pour une période libre au moyen d'instruments « Excel » il a été constaté que t = 169,20903, et p = 2,89E-12, t. E. une probabilité zéro que les fidèles sera rejeté l'hypothèse de l'insignifiance du terme libre. Pour coefficient inconnu à t = 5,79405, et p = 0,001158. Autrement dit, la probabilité qu'une hypothèse correcte rejetée sera l'insignifiance du coefficient pour l'inconnu, est de 0,12%.

Ainsi, on peut affirmer que la manière adéquate équation de régression linéaire obtenue.

Le problème de l'opportunité d'acheter des actions

La régression multiple a été réalisée dans Excel en utilisant le même outil « Analyse des données ». Considérons l'application spécifique.

Guide de l'entreprise «NNN» doit décider d'acheter 20% des actions de JSC «MMM». Prix du forfait (SP) est de 70 millions de dollars américains. Les spécialistes de «NNN» ont recueilli des données sur les transactions similaires. Il a été décidé d'évaluer la valeur des actions sur ces paramètres, exprimés en millions de dollars américains, tels que:

• dettes (VK);
• volume du chiffre d'affaires annuel (VO);
• recevoir (VD);
• La valeur des immobilisations (SOF).

En outre, utiliser les dettes salariales des entreprises (V3 U) en milliers de dollars des États-Unis.

Le processeur de la table de décision des moyens de calcul Excel

D'abord, vous devez créer un tableau de données d'entrée. Il se présente comme suit:

suivant:

• call box "d'analyse de données";
• choisi section « régression »;
• la fenêtre « Intervalle d'entrée Y» administrée valeurs de variable dépendante de la gamme de la colonne G;
• cliquer sur l'icône avec une flèche rouge à droite de la fenêtre « intervalle d'entrée X» et isolé sur une plage de feuille de toutes les valeurs de la colonne B, C, D, F.

Marquer le point « Nouvelle feuille de calcul » et cliquez sur « Ok ».

Obtenez une analyse de régression pour cette tâche.

Les résultats de l'étude et les conclusions

« Récupérer » arrondi à partir des données présentées ci-dessus sur la table à feuilles équation de régression de processeur Excel:

SD = 0,103 * SOF + 0541 * VO – 0031 * VK + 0405 * VD + 0691 * VZP – 265844.

Sous la forme mathématique plus habituelle, il peut être écrit:

y = x1 + 0103 * 0541 * x2 – x3 + 0031 * 0405 * 0691 * x4 + x5 – 265844

Les données de JSC «MMM» présentées dans le tableau ci-dessous:

de les remplacer dans l'équation de régression, a obtenu un chiffre de 64,72 millions de dollars américains. Cela signifie que les actions de JSC «MMM» ne devraient pas acheter, parce que leur coût est assez cher à 70 millions de dollars américains.

Comme vous pouvez le voir, l'utilisation de tableur « Excel » et l'équation de régression ont permis de prendre une décision éclairée quant à l'opportunité transaction tout à fait spécifique.

Maintenant, vous savez ce qu'est une régression. Exemples à Excel, décrites ci-dessus, vous aider à résoudre des problèmes pratiques de l'économétrie.