Analyse de régression est l’une des méthodes de recherche statistique les plus populaires. Il peut être utilisé pour établir le degré d’influence des variables indépendantes sur la variable dépendante. En fonctionnalité Microsoft Excel Il existe des outils conçus pour effectuer ce type d’analyse. Voyons ce qu'ils sont et comment les utiliser.

Connexion du package d'analyse

Mais, pour utiliser la fonction qui vous permet d'effectuer une analyse de régression, vous devez d'abord activer le package d'analyse. Ce n'est qu'alors que les outils nécessaires à cette procédure apparaîtront sur le ruban Excel.

1. Accédez à l'onglet « Fichier ».
2. Allez dans la section « Paramètres ».
3. Une fenêtre s'ouvre Paramètres Excel. Accédez à la sous-section « Modules complémentaires ».
4. Tout en bas de la fenêtre qui s'ouvre, déplacez le commutateur du bloc « Gestion » vers la position « Compléments Excel », s'il se trouve dans une position différente. Cliquez sur le bouton « Aller ».
5. Une fenêtre des compléments Excel disponibles s’ouvre. Cochez la case à côté de « Package d'analyse ». Cliquez sur le bouton « OK ».

Maintenant, lorsque nous allons dans l'onglet "Données", sur le ruban du bloc d'outils "Analyse", nous verrons un nouveau bouton - "Analyse des données".

Types d'analyse de régression

Il existe plusieurs types de régressions :

• parabolique;
• calme;
• logarithmique;
• exponentiel;
• démonstratif;
• hyperbolique;
• régression linéaire.

Nous parlerons plus en détail de l’exécution du dernier type d’analyse de régression dans Excel plus tard.

Régression linéaire dans Excel

Ci-dessous, à titre d'exemple, un tableau indiquant la température quotidienne moyenne de l'air extérieur et le nombre de clients du magasin pour la journée de travail correspondante. Découvrons, à l'aide de l'analyse de régression, comment les conditions météorologiques, sous forme de température de l'air, peuvent affecter la fréquentation d'un établissement de vente au détail.

L'équation générale de régression linéaire est la suivante : Y = a0 + a1x1 +…+ akhk. Dans cette formule, Y désigne la variable sur laquelle on cherche à étudier l'influence de facteurs. Dans notre cas, il s'agit du nombre d'acheteurs. La valeur de x est divers facteurs, affectant la variable. Les paramètres a sont les coefficients de régression. Autrement dit, ce sont eux qui déterminent l’importance d’un facteur particulier. L'indice k désigne le nombre total de ces mêmes facteurs.

Analyse des résultats de l'analyse

Les résultats de l'analyse de régression sont affichés sous forme de tableau à l'endroit précisé dans les paramètres.

L’un des principaux indicateurs est le R au carré. Il indique la qualité du modèle. Dans notre cas, ce coefficient est de 0,705 soit environ 70,5 %. Il s'agit d'un niveau de qualité acceptable. Une dépendance inférieure à 0,5 est mauvaise.

Un autre indicateur important situé dans la cellule à l'intersection de la ligne d'origine Y et de la colonne Coefficients. Cela indique quelle valeur Y aura, et dans notre cas, il s’agit du nombre d’acheteurs, tous les autres facteurs étant égaux à zéro. Dans ce tableau valeur donnée est égal à 58,04.

La valeur à l'intersection des colonnes « Variable X1 » et « Coefficients » montre le niveau de dépendance de Y par rapport à X. Dans notre cas, il s'agit du niveau de dépendance du nombre de clients du magasin à la température. Un coefficient de 1,31 est considéré comme un indicateur d'influence assez élevé.

Comme nous pouvons le constater, en utilisant Programmes Microsoft Excel est assez simple pour créer un tableau d’analyse de régression. Mais seule une personne formée peut travailler avec les données de sortie et en comprendre l'essence.

Nous sommes heureux d'avoir pu vous aider à résoudre le problème.

Posez votre question dans les commentaires, en décrivant l'essence du problème en détail. Nos spécialistes s'efforceront de répondre dans les plus brefs délais.

Cet article vous a t'il aidé?

Méthode 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 de équation linéaire, est présenté ci-dessous :

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

x - variable indépendante,

a - 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é la forme de l'équation en utilisant la méthode des moindres carrés.

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. Sur le terrain 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 sur D'ACCORD.

Dans l'onglet Données en groupe Analyse apparaîtra nouveau bouton L'analyse des données.

Pour démontrer le fonctionnement du complément, utilisons les données d'un article précédent, dans lequel un homme et une fille partagent une table dans la salle de bain. Saisissez les données de notre exemple de baignoire dans les colonnes A et B de la feuille vierge.

Allez dans l'onglet Données, en groupe Analyse Cliquez sur L'analyse des données. Dans la fenêtre qui apparaît L'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 la photo :

Cliquez sur 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 l'article précédent.

L'analyse de régression est une méthode de recherche statistique qui permet de montrer la dépendance d'un paramètre particulier à une ou plusieurs variables indépendantes. À l’ère pré-informatique, son utilisation était assez difficile, surtout lorsqu’il s’agissait de gros volumes de données. Aujourd'hui, après avoir appris à créer une régression dans Excel, vous pouvez résoudre des problèmes statistiques complexes en quelques minutes seulement. Ci-dessous sont exemples spécifiques du domaine de l’économie.

Types de régression

Ce concept lui-même a été introduit en mathématiques par Francis Galton en 1886. La régression se produit :

• linéaire;
• parabolique;
• calme;
• exponentiel;
• hyperbolique;
• démonstratif;
• logarithmique.

Exemple 1

Considérons le problème de la détermination de la dépendance du nombre de membres de l'équipe qui ont démissionné salaire moyen dans 6 entreprises industrielles.

Tâche. Dans six entreprises, le salaire mensuel moyen et le nombre de salariés qui ont démissionné volontairement ont été analysés. Sous forme de tableau, nous avons :

Pour déterminer la dépendance du nombre de travailleurs qui quittent leur emploi par rapport au salaire moyen dans 6 entreprises, le modèle de régression a la forme de l'équation Y = a0 + a1×1 +…+аkxk, où хi sont les variables d'influence, ai sont les coefficients de régression et k est le nombre de facteurs.

Pour cette tâche, Y est l'indicateur des salariés qui ont démissionné, et le facteur d'influence est le salaire, que nous désignons par X.

Utiliser les capacités du tableur Excel

L'analyse de régression dans Excel doit être précédée de l'application de fonctions intégrées aux données tabulaires existantes. Cependant, à ces fins, il est préférable d'utiliser le module complémentaire très utile « Analysis Pack ». Pour l'activer il vous faut :

• depuis l'onglet « Fichier », allez dans la section « Options » ;
• dans la fenêtre qui s'ouvre, sélectionnez la ligne « Modules complémentaires » ;
• cliquez sur le bouton « Go » situé en bas, à droite de la ligne « Gestion » ;
• cochez la case à côté du nom « Package d'analyse » et confirmez vos actions en cliquant sur « Ok ».

Si tout est fait correctement, le bouton requis apparaîtra sur le côté droit de l'onglet « Données », situé au-dessus de la feuille de calcul Excel.

Régression linéaire dans Excel

Maintenant que tu as tout ce dont tu as besoin à portée de main instruments virtuels pour effectuer des calculs économétriques, nous pouvons commencer à résoudre notre problème. Pour ça:

• Cliquez sur le bouton « Analyse des données » ;
• dans la fenêtre qui s'ouvre, cliquez sur le bouton « Régression » ;
• dans l'onglet qui apparaît, saisissez la plage de valeurs pour Y (le nombre d'employés démissionnaires) et pour X (leurs salaires) ;
• Nous confirmons nos actions en appuyant sur le bouton « Ok ».

En conséquence, le programme remplira automatiquement une nouvelle feuille processeur de table données d’analyse de régression. Note! Excel vous permet de définir manuellement l'emplacement que vous préférez à cet effet. Par exemple, il pourrait s'agir de la même feuille où se trouvent les valeurs Y et X, ou même d'un nouveau classeur spécialement conçu pour stocker ces données.

Analyse des résultats de régression pour le R-carré

DANS Données Excel obtenus lors du traitement des données de l'exemple considéré ont la forme :

Tout d’abord, vous devez faire attention à la valeur R au carré. Il représente le coefficient de détermination. DANS dans cet exemple R carré = 0,755 (75,5 %), c'est-à-dire que les paramètres calculés du modèle expliquent la dépendance entre les paramètres considérés à 75,5 %. Plus la valeur du coefficient de détermination est élevée, le modèle sélectionné est considéré comme plus applicable pour tâche spécifique. On considère que la situation réelle est correctement décrite lorsque la valeur R-carré est supérieure à 0,8. Si R au carré est tcr, alors l'hypothèse de l'insignifiance du terme libre de l'équation linéaire est rejetée.

Dans le problème considéré pour le terme libre, à l'aide des outils Excel, il a été obtenu que t = 169,20903 et p = 2,89E-12, c'est-à-dire que nous avons une probabilité nulle que l'hypothèse correcte sur l'insignifiance du terme libre soit rejetée . Pour le coefficient pour l'inconnu t=5,79405 et p=0,001158. En d'autres termes, la probabilité que l'hypothèse correcte sur l'insignifiance du coefficient pour une inconnue soit rejetée est de 0,12 %.

Ainsi, on peut affirmer que l’équation de régression linéaire qui en résulte est adéquate.

Le problème de la faisabilité de l'achat d'un bloc d'actions

La régression multiple dans Excel est effectuée à l'aide du même outil d'analyse de données. Considérons un problème d'application spécifique.

La direction de la société NNN doit décider de l'opportunité d'acquérir une participation de 20 % dans MMM JSC. Le coût du package (SP) est de 70 millions de dollars américains. Les spécialistes de NNN ont collecté des données sur des transactions similaires. Il a été décidé d'évaluer la valeur du bloc d'actions selon des paramètres, exprimés en millions de dollars américains, tels que :

• comptes créditeurs (VK);
• volume de chiffre d'affaires annuel (VO) ;
• comptes clients (VD);
• coût des immobilisations (COF).

De plus, le paramètre des arriérés de salaires de l'entreprise (V3 P) en milliers de dollars américains est utilisé.

Solution utilisant un tableur Excel

Tout d'abord, vous devez créer un tableau de données sources. Cela ressemble à ceci :

• appeler la fenêtre « Analyse des données » ;
• sélectionnez la section « Régression » ;
• Dans la case « Intervalle de saisie Y », saisissez la plage de valeurs des variables dépendantes de la colonne G ;
• cliquez sur l'icône de flèche rouge à droite de la fenêtre « Plage d'entrée X » et mettez en surbrillance sur la feuille la plage de toutes les valeurs de colonnes B,C,D,F.

Marquez l'élément « Nouvelle feuille de calcul » et cliquez sur « Ok ».

Obtenez une analyse de régression pour un problème donné.

Etude des résultats et conclusions

Nous « collectons » l'équation de régression à partir des données arrondies présentées ci-dessus sur la feuille de calcul Excel :

SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

Sous une forme mathématique plus familière, cela peut s’écrire :

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Les données pour MMM JSC sont présentées dans le tableau :

En les remplaçant dans l'équation de régression, nous obtenons un chiffre de 64,72 millions de dollars américains. Cela signifie que les actions de MMM JSC ne valent pas la peine d'être achetées, car leur valeur de 70 millions de dollars américains est assez gonflée.

Comme vous pouvez le constater, l'utilisation du tableur Excel et de l'équation de régression a permis de prendre une décision éclairée quant à la faisabilité d'une transaction bien précise.

Vous savez maintenant ce qu'est la régression. Les exemples Excel discutés ci-dessus vous aideront à résoudre des problèmes pratiques dans le domaine de l'économétrie.

Pour les territoires de la région, des données pour 200X sont fournies.

Numéro de région	Salaire vital moyen par habitant et par jour d'une personne valide, frotter., x	Salaire journalier moyen, frotter., y
1	78	133
2	82	148
3	87	134
4	79	154
5	89	162
6	106	195
7	67	139
8	88	158
9	73	152
10	87	162
11	76	159
12	115	173

Exercice:

1. Construisez un champ de corrélation et formulez une hypothèse sur la forme de la connexion.

2. Calculer les paramètres de l'équation de régression linéaire

4. À l'aide du coefficient d'élasticité moyen (général), donnez une évaluation comparative de la force de la relation entre le facteur et le résultat.

7. Calculez la valeur prédite du résultat si la valeur prédite du facteur augmente de 10 % par rapport à son niveau moyen. Déterminez l’intervalle de confiance prévu pour le niveau de signification.

Solution:

Décidons cette tâche en utilisant Excel.

1. En comparant les données disponibles x et y, par exemple, en les classant par ordre croissant du facteur x, on peut observer la présence d'une relation directe entre les caractéristiques, lorsqu'une augmentation du niveau de subsistance moyen par habitant augmente le niveau quotidien moyen salaire. Sur cette base, nous pouvons supposer que la relation entre les caractéristiques est directe et peut être décrite par une équation en ligne droite. La même conclusion est confirmée sur la base d’une analyse graphique.

Pour créer un champ de corrélation, vous pouvez utiliser Excel PPP. Saisissez les données initiales dans l'ordre : d'abord x, puis y.

Sélectionnez la zone de cellules contenant des données.

Alors choisi: Insérer / Nuage de points / Nuage de points avec marqueurs comme le montre la figure 1.

Figure 1 Construction du champ de corrélation

L'analyse du champ de corrélation montre la présence d'une dépendance proche de la rectiligne, puisque les points sont situés presque en ligne droite.

2. Pour calculer les paramètres de l'équation de régression linéaire
utilisons le intégré fonction statistique LIGNE.

Pour ça:

1) Ouvrir un fichier existant contenant les données analysées ;
2) Sélectionnez une zone 5x2 de cellules vides (5 lignes, 2 colonnes) pour afficher les résultats des statistiques de régression.
3) Activer Assistant de fonction: dans le menu principal sélectionnez Formules / Insérer une fonction.
4) Dans la fenêtre Catégorie tu prends Statistique, dans la fenêtre de fonction - LIGNE. Cliquez sur le bouton D'ACCORD comme le montre la figure 2 ;

Figure 2 Boîte de dialogue Assistant de fonction

5) Remplissez les arguments de la fonction :

Valeurs connues pour

Valeurs connues de x

Constante- une valeur logique qui indique la présence ou l'absence d'un terme libre dans l'équation ; si Constant = 1, alors le terme libre est calculé de la manière habituelle, si Constant = 0, alors le terme libre est 0 ;

Statistiques- une valeur logique qui indique s'il faut afficher ou non des informations complémentaires sur l'analyse de régression. Si Statistiques = 1, alors Informations Complémentaires s'affiche, si Statistiques = 0, alors seules les estimations des paramètres de l'équation sont affichées.

Cliquez sur le bouton D'ACCORD;

Figure 3 Boîte de dialogue Arguments de fonction LINEST

6) Le premier élément du tableau final apparaîtra dans la cellule supérieure gauche de la zone sélectionnée. Pour ouvrir tout le tableau, appuyez sur la touche , puis à la combinaison de touches ++ .

Des statistiques de régression supplémentaires seront générées dans l'ordre indiqué dans le diagramme suivant :

Valeur du coefficient b	Coefficient une valeur
Erreur type b	Erreur standard a
Erreur type y
Statistique F
Somme des carrés de régression

Figure 4 Résultat du calcul de la fonction LINEST

Nous avons obtenu le niveau de régression :

Nous concluons : Avec une augmentation du niveau de subsistance moyen par habitant de 1 frotter. le salaire journalier moyen augmente en moyenne de 0,92 rouble.

Signifie une variation de 52 % salaires(y) s'explique par la variation du facteur x - le niveau de subsistance moyen par habitant, et 48% - par l'action d'autres facteurs non inclus dans le modèle.

A l'aide du coefficient de détermination calculé, le coefficient de corrélation peut être calculé : .

La connexion est considérée comme étroite.

4. À l’aide du coefficient d’élasticité moyen (général), nous déterminons la force de l’influence du facteur sur le résultat.

Pour une équation en ligne droite, nous déterminons le coefficient d'élasticité moyen (total) à l'aide de la formule :

Nous trouverons les valeurs moyennes en sélectionnant la zone de cellules avec des valeurs x et en sélectionnant Formules / Somme automatique / Moyenne, et nous ferons de même avec les valeurs de y.

Figure 5 Calcul des valeurs moyennes de la fonction et argument

Ainsi, si le coût de la vie moyen par habitant varie de 1 % par rapport à sa valeur moyenne, le salaire journalier moyen variera en moyenne de 0,51 %.

Utiliser un outil d'analyse de données Régression disponible:
- les résultats des statistiques de régression,
- les résultats de l'analyse de variance,
- résultats des intervalles de confiance,
- graphiques d'ajustement des résidus et des droites de régression,
- résidus et probabilité normale.

La procédure est la suivante :

1) vérifier l'accès à Pack d'analyse. Dans le menu principal, sélectionnez : Fichier/Options/Modules complémentaires.

2) Dans la liste déroulante Contrôle sélectionner un article Compléments Excel et appuyez sur le bouton Aller.

3) Dans la fenêtre Modules complémentaires coche la case Pack d'analyse puis cliquez sur le bouton D'ACCORD.

Si Pack d'analyse pas dans la liste des champs Modules complémentaires disponibles, appuie sur le bouton Revoir pour effectuer une recherche.

Si vous recevez un message indiquant que le package d'analyse n'est pas installé sur votre ordinateur, cliquez sur Oui pour l'installer.

4) Dans le menu principal, sélectionnez : Données / Analyse des données / Outils d'analyse / Régression puis cliquez sur le bouton D'ACCORD.

5) Remplissez la boîte de dialogue des paramètres d'entrée et de sortie des données :

Intervalle de saisie Y- plage contenant les données de l'attribut résultant ;

Intervalle de saisie X- plage contenant les données de la caractéristique factorielle ;

Mots clés- un flag qui indique si la première ligne contient ou non des noms de colonnes ;

Constante - zéro- un drapeau indiquant la présence ou l'absence d'un terme libre dans l'équation ;

Intervalle de sortie- indiquez simplement celui de gauche cellule supérieure gamme future;

6) Nouvelle feuille de calcul - vous pouvez spécifier un nom arbitraire pour la nouvelle feuille.

Cliquez ensuite sur le bouton D'ACCORD.

Figure 6 Boîte de dialogue permettant de saisir les paramètres de l'outil de régression

Les résultats de l'analyse de régression pour les données problématiques sont présentés à la figure 7.

Figure 7 Résultat de l'utilisation de l'outil de régression

5. Évaluons la qualité des équations en utilisant l'erreur d'approximation moyenne. Utilisons les résultats de l'analyse de régression présentés dans la figure 8.

Figure 8 Résultat de l'utilisation de l'outil de régression « Retrait du reste »

Composons nouveau tableau comme le montre la figure 9. Dans la colonne C, nous calculons l'erreur d'approximation relative à l'aide de la formule :

Figure 9 Calcul de l'erreur d'approximation moyenne

L'erreur d'approximation moyenne est calculée à l'aide de la formule :

La qualité du modèle construit est jugée bonne, puisqu'elle ne dépasse pas 8 à 10 %.

6. À partir du tableau des statistiques de régression (Figure 4), nous notons la valeur réelle du test F de Fisher :

Parce que le à un niveau de signification de 5 %, on peut alors conclure que l'équation de régression est significative (la relation a été prouvée).

8. Nous évaluerons la signification statistique des paramètres de régression à l’aide des statistiques t de Student et en calculant l’intervalle de confiance de chaque indicateur.

Nous émettons l'hypothèse H 0 sur une différence statistiquement insignifiante entre les indicateurs et zéro :

.

pour le nombre de degrés de liberté

La figure 7 présente les valeurs réelles de la statistique t :

Le test t pour le coefficient de corrélation peut être calculé de deux manières :

Méthode I :

Où - erreur aléatoire du coefficient de corrélation.

Nous prendrons les données pour le calcul du tableau de la figure 7.

Méthode II :

Les valeurs réelles de la statistique t dépassent les valeurs du tableau :

Par conséquent, l'hypothèse H 0 est rejetée, c'est-à-dire que les paramètres de régression et le coefficient de corrélation ne diffèrent pas de zéro par hasard, mais sont statistiquement significatifs.

L'intervalle de confiance pour le paramètre a est défini comme

Pour le paramètre a, les limites de 95 % indiquées sur la figure 7 étaient :

L'intervalle de confiance pour le coefficient de régression est défini comme

Pour le coefficient de régression b, les limites de 95 % indiquées dans la figure 7 étaient :

L'analyse des limites supérieure et inférieure des intervalles de confiance conduit à la conclusion qu'avec probabilité les paramètres a et b, étant dans les limites spécifiées, n'acceptent pas valeurs nulles, c'est à dire. ne sont pas statistiquement insignifiants et significativement différents de zéro.

7. Les estimations obtenues de l'équation de régression permettent de l'utiliser à des fins de prévision. Si le coût de la vie prévu est :

Alors la valeur prédite du coût de la vie sera :

Nous calculons l'erreur de prévision à l'aide de la formule :

Où

Nous calculerons également la variance à l'aide d'Excel PPP. Pour ça:

1) Activer Assistant de fonction: dans le menu principal sélectionnez Formules / Insérer une fonction.

3) Remplissez la plage contenant les données numériques de la caractéristique factorielle. Cliquez sur D'ACCORD.

Figure 10 Calcul de la variance

Nous avons obtenu la valeur de la variance

Pour calculer la variance résiduelle par degré de liberté, nous utiliserons les résultats de l'analyse de variance comme le montre la figure 7.

Les intervalles de confiance pour prédire les valeurs individuelles de y avec une probabilité de 0,95 sont déterminés par l'expression :

L'intervalle est assez large, principalement en raison du faible volume d'observations. En général, les prévisions du salaire mensuel moyen se sont révélées fiables.

La condition du problème est tirée de : Atelier d'économétrie : Proc. allocation / I.I. Eliseeva, S.V. Kurysheva, N.M. Gordeenko et autres ; Éd. I.I. Eliseeva. - M. : Finances et Statistiques, 2003. - 192 p. : ill.

L'analyse de régression est l'une des méthodes de recherche statistique les plus populaires. Il peut être utilisé pour établir le degré d’influence des variables indépendantes sur la variable dépendante. Microsoft Excel dispose d'outils conçus pour effectuer ce type d'analyse. Voyons ce qu'ils sont et comment les utiliser.

Mais, pour utiliser la fonction qui vous permet d'effectuer une analyse de régression, vous devez d'abord activer le package d'analyse. Ce n'est qu'alors que les outils nécessaires à cette procédure apparaîtront sur le ruban Excel.

Maintenant, quand nous allons à l'onglet "Données", sur le ruban dans la boîte à outils "Analyse" nous verrons un nouveau bouton - "L'analyse des données".

Types d'analyse de régression

Il existe plusieurs types de régressions :

• parabolique;
• calme;
• logarithmique;
• exponentiel;
• démonstratif;
• hyperbolique;
• régression linéaire.

Nous parlerons plus en détail de l’exécution du dernier type d’analyse de régression dans Excel plus tard.

Régression linéaire dans Excel

Ci-dessous, à titre d'exemple, un tableau indiquant la température quotidienne moyenne de l'air extérieur et le nombre de clients du magasin pour la journée de travail correspondante. Découvrons, à l'aide de l'analyse de régression, comment les conditions météorologiques, sous forme de température de l'air, peuvent affecter la fréquentation d'un établissement de vente au détail.

L'équation générale de régression linéaire est la suivante : Y = a0 + a1x1 +…+ akhk. Dans cette formule Oui désigne une variable, l'influence des facteurs sur lesquels nous essayons d'étudier. Dans notre cas, il s'agit du nombre d'acheteurs. Signification X sont les différents facteurs qui influencent une variable. Possibilités un sont des coefficients de régression. Autrement dit, ce sont eux qui déterminent l’importance d’un facteur particulier. Indice k désigne le nombre total de ces mêmes facteurs.

Analyse des résultats de l'analyse

Les résultats de l'analyse de régression sont affichés sous forme de tableau à l'endroit précisé dans les paramètres.

L’un des principaux indicateurs est R Carré. Il indique la qualité du modèle. Dans notre cas, ce coefficient est de 0,705 soit environ 70,5 %. Il s'agit d'un niveau de qualité acceptable. Une dépendance inférieure à 0,5 est mauvaise.

Un autre indicateur important est situé dans la cellule à l'intersection de la ligne « intersection en Y » et colonne "Chances". Cela indique quelle valeur Y aura, et dans notre cas, il s’agit du nombre d’acheteurs, tous les autres facteurs étant égaux à zéro. Dans ce tableau, cette valeur est 58,04.

Valeur à l'intersection du graphique "Variable X1" Et "Chances" montre le niveau de dépendance de Y vis-à-vis de X. Dans notre cas, il s'agit du niveau de dépendance du nombre de clients du magasin à la température. Un coefficient de 1,31 est considéré comme un indicateur d'influence assez élevé.

Comme vous pouvez le constater, à l'aide de Microsoft Excel, il est assez simple de créer un tableau d'analyse de régression. Mais seule une personne formée peut travailler avec les données de sortie et en comprendre l'essence.

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. Sur le terrain 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 sur D'ACCORD.

Dans l'onglet Données en groupe Analyse un nouveau bouton apparaîtra L'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 baignoire dans les colonnes A et B de la feuille vierge.

Allez dans l'onglet Données, en groupe Analyse Cliquez sur L'analyse des données. Dans la fenêtre qui apparaît L'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 la photo :

Cliquez sur 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 .

La droite de régression est un reflet graphique de la relation entre les phénomènes. Vous pouvez très clairement construire une droite de régression dans Excel.

Pour ce faire, vous avez besoin de :

1.Ouvrez Excel

2.Créez des colonnes de données. Dans notre exemple, nous allons construire une ligne de régression, ou une relation, entre l'agressivité et le doute de soi chez les élèves de première année. 30 enfants ont participé à l'expérience, les données sont présentées dans le tableau Excel :

1 colonne - numéro de sujet

2 colonnes - agressivité en points

3 colonnes - méfiance en points

3.Ensuite, vous devez sélectionner les deux colonnes (sans le nom de la colonne), cliquez sur l'onglet insérer , choisir place , et choisissez le tout premier parmi les mises en page proposées pointer avec des marqueurs .

4. Nous avons donc un modèle pour la droite de régression - ce qu'on appelle - nuage de points. Pour accéder à la droite de régression, cliquez sur le chiffre obtenu et appuyez sur la touche de tabulation constructeur, trouver sur le panneau présentations de graphiques et choisissez M UN ket9 , ça dit aussi f(x)

5. Nous avons donc une droite de régression. Le graphique montre également son équation et le carré du coefficient de corrélation

6. Il ne reste plus qu'à ajouter le nom du graphique et le nom des axes. De plus, si vous le souhaitez, vous pouvez supprimer la légende, réduire le nombre lignes horizontales grilles (onglet mise en page , alors filet ). Les modifications et paramètres de base sont effectués dans l'onglet Mise en page

La droite de régression a été construite dans MS Excel. Vous pouvez maintenant l'ajouter au texte de l'œuvre.