13.11 : Comment utiliser Microsoft Excel® pour l'analyse de régression

Cette section de ce chapitre est là pour reconnaître que ce que nous demandons aujourd'hui nécessite bien plus qu'un simple calcul rapide d'un ratio ou d'une racine carrée. En effet, l'utilisation de l'analyse de régression était pratiquement inexistante avant le milieu du siècle dernier et n'est devenue un outil vraiment répandu qu'à la fin des années 1960 et au début des années 1970. Même à cette époque, la capacité de calcul des plus grandes machines IBM est risible selon les normes actuelles. Au début, les programmes étaient développés par les chercheurs et partagés. Il n'y avait pas de marché pour ce que l'on appelle les « logiciels » et certainement rien pour les « applications », un nouveau venu sur le marché il y a seulement quelques années.

Avec l'avènement des ordinateurs personnels et l'explosion d'un marché vital des logiciels, nous avons le choix entre un certain nombre de progiciels de régression et d'analyse statistique. Chacune a ses mérites. Nous avons choisi Microsoft Excel en raison de sa disponibilité généralisée sur les campus universitaires et sur le marché post-universitaire. Stata est une alternative et possède des fonctionnalités qui seront importantes pour des études économétriques plus avancées si vous choisissez de suivre cette voie. Des packages encore plus avancés existent, mais nécessitent généralement que l'analyste effectue une quantité importante de programmation pour effectuer son analyse. L'objectif de cette section est de montrer comment utiliser Excel pour exécuter une régression, puis de le faire à l'aide d'un exemple de version simple d'une courbe de demande.

La première étape pour effectuer une régression à l'aide d'Excel consiste à charger le programme sur votre ordinateur. Si vous utilisez Excel, vous disposez de l'Analysis ToolPak, mais il se peut qu'il ne soit pas activé. Le programme demande beaucoup d'espace et n'est donc pas chargé automatiquement.

Pour activer l'Analysis ToolPak, procédez comme suit :

Cliquez sur « Fichier » > « Options » > « Compléments » pour faire apparaître un menu de l'extension « ToolPaks ». Sélectionnez « Analysis ToolPak » et cliquez sur « GO » à côté de « Gérer : compléments Excel » en bas de la fenêtre. Cela ouvrira une nouvelle fenêtre dans laquelle vous cliquerez sur « Analysis ToolPak » (assurez-vous que la case est cochée en vert), puis sur « OK ». Il devrait maintenant y avoir un onglet Analyse dans le menu des données. Ces étapes sont présentées dans les captures d'écran suivantes.

Figurine\(\PageIndex{17}\)

Figurine\(\PageIndex{18}\)

Figurine\(\PageIndex{19}\)

Figurine\(\PageIndex{20}\)

Cliquez sur « Données » puis sur « Analyse des données », puis sur « Régression » et « OK ». Félicitations, vous êtes arrivé à la fenêtre de régression. La fenêtre vous demande vos entrées. Cliquez sur la case à côté\(Y\) des\(X\) plages et pour utiliser la fonction cliquer-glisser d'Excel pour sélectionner vos plages d'entrée. Excel a une particularité étrange : la fonction de cliquer-déposer nécessite que les variables indépendantes, les\(X\) variables, soient toutes réunies, ce qui signifie qu'elles forment une matrice unique. Si vos données sont configurées avec la\(Y\) variable entre deux colonnes de\(X\) variables, Excel ne vous autorisera pas à utiliser le glisser-déplacer. Par exemple, disons que la colonne A et la colonne C sont des variables indépendantes et que la colonne B est la\(Y\) variable, la variable dépendante. Excel ne vous permet pas de cliquer et de déposer les plages de données. La solution consiste à déplacer la colonne contenant la\(Y\) variable vers la colonne A, puis à cliquer et à faire glisser. Le même problème se pose à nouveau si vous souhaitez exécuter la régression avec certaines\(X\) variables uniquement. Vous devez configurer la matrice de manière à ce que toutes les\(X\) variables que vous souhaitez régresser se trouvent dans une matrice étroitement formée. Ces étapes sont présentées dans les plans de scène suivants.

Figurine\(\PageIndex{21}\)

Graphique 13.22

Une fois que vous avez sélectionné les données pour votre analyse de régression et indiqué à Excel laquelle est la variable dépendante (\(Y\)) et laquelle sont les objets\(X\) de valeur indépendants, vous avez plusieurs choix quant aux paramètres et à la manière dont la sortie sera affichée. Reportez-vous à la capture d'écran Figure\(\PageIndex{22}\) dans la section « Entrée ». Si vous cochez la case « labels », le programme placera l'entrée dans la première colonne de chaque variable en tant que nom dans la sortie. Vous pouvez saisir un nom réel, tel que prix ou revenu dans une analyse de la demande, dans la première ligne de la feuille de calcul Excel pour chaque variable et il sera affiché dans la sortie.

Le niveau de signification peut également être défini par l'analyste. Cela ne modifiera pas la statistique t calculée, appelée t stat, mais modifiera la valeur p de la statistique t calculée. Cela modifiera également les limites des intervalles de confiance pour les coefficients. Un intervalle de confiance de 95 % est toujours présenté, mais si vous le modifiez, vous obtiendrez également d'autres niveaux de confiance pour les intervalles.

Excel vous permettra également de supprimer l'interception. Cela force le programme de régression à minimiser la somme résiduelle des carrés à condition que la droite estimée passe par l'origine. Cela se fait dans les cas où le modèle n'a aucune signification à une valeur autre que zéro, zéro pour le début de la ligne. Un exemple est une fonction de production économique qui est une relation entre le nombre d'unités d'un intrant, par exemple les heures de travail, et la production. Il n'y a aucun sens de production positive avec zéro travailleur.

Une fois les données saisies et les choix effectués, cliquez sur OK et les résultats seront envoyés dans une nouvelle feuille de calcul séparée par défaut. Le résultat d'Excel est présenté d'une manière typique des autres programmes de progiciels de régression. Le premier bloc d'informations donne les statistiques générales de la régression : multiple\(R\),\(R\) quadratique et\(R\) carré ajusté en fonction des degrés de liberté, c'est-à-dire celui que vous souhaitez signaler. Vous obtenez également l'erreur type (de l'estimation) et le nombre d'observations dans la régression.

Le deuxième bloc d'informations s'intitule ANOVA, qui signifie Analyse de la variance. Notre intérêt pour cette section est la colonne marquée\(F\). Il s'agit des\(F\) statistiques calculées pour l'hypothèse nulle selon laquelle tous les coefficients sont égaux à zéro ou pour l'alternative selon laquelle au moins un des coefficients n'est pas égal à zéro. Ce test d'hypothèse a été présenté dans la section 13.4 sous la rubrique « Quelle est la qualité de l'équation ? » La colonne suivante indique la valeur p pour ce test sous le titre « Signification F ». Si la valeur de p est inférieure, disons, à 0,05 (la\(F\) statistique calculée se trouve dans la queue), nous pouvons affirmer avec un degré de certitude de 90 % que nous ne pouvons pas accepter les hypothèses nulles selon lesquelles tous les coefficients sont égaux à zéro. C'est une bonne chose : cela signifie qu'au moins un des coefficients est significativement différent de zéro, ce qui a un effet sur la valeur de\(Y\).

Le dernier bloc d'informations contient les tests d'hypothèse pour chaque coefficient. Les coefficients estimés, l'intersection et les pentes sont d'abord listés, puis chaque erreur type (du coefficient estimé) suivie de la statistique t (statistique t calculée par l'étudiant pour l'hypothèse nulle selon laquelle le coefficient est égal à zéro). Nous comparons l'état t et la valeur critique du t de l'étudiant, en fonction des degrés de liberté, et déterminons si nous avons suffisamment de preuves pour rejeter la valeur nulle sur laquelle la variable n'a aucun effet\(Y\). N'oubliez pas que nous avons défini l'hypothèse nulle comme étant le statu quo et que notre affirmation selon laquelle nous savons ce qui a provoqué le changement\(Y\) se trouve dans l'hypothèse alternative. Nous voulons rejeter le statu quo et y substituer notre version du monde, l'hypothèse alternative. La colonne suivante contient les valeurs p pour ce test d'hypothèse, suivies des limites supérieure et inférieure estimées de l'intervalle de confiance du paramètre de pente estimé pour les différents niveaux de confiance que nous avons définis au début.

Estimation de la demande de roses

Voici un exemple d'utilisation du programme Excel pour exécuter une régression pour un cas particulier : estimation de la demande de roses. Nous essayons d'estimer une courbe de demande qui, d'après la théorie économique, devrait affecter certaines variables sur la quantité d'un bien que nous achetons. La relation entre le prix d'un bien et la quantité demandée est la courbe de demande. Au-delà de cela, nous avons la fonction de demande qui inclut d'autres variables pertinentes : le revenu d'une personne, le prix des produits de substitution et peut-être d'autres variables telles que la saison de l'année ou le prix des produits complémentaires. La quantité demandée sera notre\(Y\) variable, et le prix des roses, le prix des œillets et le revenu seront nos variables indépendantes, les\(X\) variables.

Pour toutes ces variables, la théorie nous indique la relation attendue. Pour le prix du bien en question, les roses, la théorie prédit une relation inverse, la courbe de demande à pente négative. La théorie prédit également la relation entre la quantité demandée pour un produit, ici les roses, et le prix d'un substitut, les œillets dans cet exemple. La théorie prédit qu'il devrait s'agir d'une relation positive ou directe ; à mesure que le prix du substitut baisse, nous abandonnons les roses au substitut le moins cher, les œillets. Une baisse du prix du substitut entraîne une diminution de la demande pour le bien analysé, des roses ici. La réduction engendre la réduction est une relation positive. Pour les produits normaux, la théorie prédit également une relation positive ; à mesure que nos revenus augmentent, nous achetons davantage de biens, des roses. Nous nous attendons à ces résultats parce que c'est ce que prédisent cent ans de théorie et de recherche économiques. Nous testons essentiellement ces hypothèses centenaires. Les données recueillies ont été déterminées par le modèle testé. Cela devrait toujours être le cas. On ne fait pas de statistiques inférentielles en jetant une montagne de données dans un ordinateur et en demandant à la machine une théorie. La théorie d'abord, le test suit.

Ces données sont les prix moyens nationaux et le revenu est le revenu personnel par habitant du pays. La quantité demandée correspond au total des ventes annuelles nationales de roses. Il s'agit de séries chronologiques annuelles ; nous suivons le marché des roses aux États-Unis de 1984 à 2017, 33 observations.

En raison de la manière originale dont Excel demande la saisie des données dans le package de régression, il est préférable de placer les variables indépendantes, le prix des roses, le prix des œillets et le revenu côte à côte sur la feuille de calcul. Une fois que vos données sont entrées dans la feuille de calcul, il est toujours bon de les consulter. Examinez la plage, les moyennes et les écarts types. Utilisez votre compréhension des statistiques descriptives dès la toute première partie de ce cours. Dans les grands ensembles de données, vous ne pourrez pas « scanner » les données. L'Analysis ToolPac permet d'obtenir facilement la plage, la moyenne, les écarts types et les autres paramètres des distributions. Vous pouvez également obtenir rapidement les corrélations entre les variables. Examinez les valeurs aberrantes. Passez en revue l'historique. Il s'est passé quelque chose ? S'agit-il d'une grève du travail, d'une modification des frais d'importation, ce qui rend ces observations inhabituelles ? Ne prenez pas les données sans poser de questions. Il y a peut-être eu une faute de frappe quelque part, qui sait sans qu'on s'y attarde.

Accédez à la fenêtre de régression, entrez les données, sélectionnez un niveau de confiance de 95 % et cliquez sur « OK ». Vous pouvez inclure les étiquettes dans la plage de saisie si vous avez placé un titre en haut de chaque colonne, mais assurez-vous de cliquer sur la case « étiquettes » sur la page de régression principale si vous le faites.

Le résultat de la régression doit s'afficher automatiquement sur une nouvelle feuille de travail.

Figurine\(\PageIndex{23}\)

Les premiers résultats présentés sont le R-Square, une mesure de la force de la corrélation entre\(Y\) et\(X_1\)\(X_2\), et\(X_3\) prise en groupe. Notre carré R de 0,699, ajusté pour tenir compte des degrés de liberté, signifie que 70 % de la variation de Y, la demande de roses, peut s'expliquer par des variations de\(X_1\), et\(X_2\)\(X_3\), du prix des roses, du prix des œillets et du revenu. Il n'existe aucun test statistique pour déterminer la « signification » d'un\(R^2\). Bien entendu, une valeur plus élevée\(R^2\) est préférable, mais c'est vraiment la signification des coefficients qui déterminera la valeur de la théorie testée et qui fera partie de toute discussion politique s'il est démontré qu'ils sont significativement différents de zéro.

En regardant le troisième panneau de sortie, nous pouvons écrire l'équation comme suit :

\[Y=b_{0}+b_{1} X_{1}+b_{2} X_{2}+b_{3} X_{3}+e\nonumber\]

où\(b_0\) est l'intersection,\(b_1\) est le coefficient estimé sur le prix des roses, et b ₂ est le coefficient estimé sur le prix des œillets,\(b_3\) est l'effet estimé du revenu et e est le terme d'erreur. L'équation est écrite en lettres romaines indiquant qu'il s'agit des valeurs estimées et non des paramètres\(\beta\) de population.

Notre équation estimée est la suivante :

\[\text { Quantity of roses sold }=183,475-1.76 \text { Price of roses }+1.33 \text { Price of carnations }+3.03 \text { Income }\nonumber\]

Nous observons d'abord que les signes des coefficients sont ceux attendus de la théorie. La courbe de demande est inclinée vers le bas avec un signe négatif pour le prix des roses. En outre, les signes du prix des œillets et des coefficients de revenu sont positifs, comme on peut s'y attendre d'après la théorie économique.

L'interprétation des coefficients peut nous indiquer l'ampleur de l'impact d'une modification de chaque variable sur la demande de roses. C'est cette capacité qui fait de l'analyse de régression un outil si précieux. Les coefficients estimés nous indiquent qu'une augmentation du prix des roses d'un dollar entraînera une réduction de 1,76 du nombre de roses achetées. Le prix des œillets semble jouer un rôle important dans la demande de roses, car nous constatons qu'une augmentation du prix des œillets d'un dollar augmenterait la demande de roses de 1,33 unité, les consommateurs préférant se tourner vers les œillets désormais plus chers. De même, l'augmentation du revenu par habitant d'un dollar entraînera une augmentation de 3,03 unités de roses achetées.

Ces résultats sont conformes aux prédictions de la théorie économique concernant les trois variables incluses dans cette estimation de la demande de roses. Il est important de disposer d'abord d'une théorie qui prédit la signification ou au moins la direction des coefficients. Sans théorie à tester, cet outil de recherche n'est guère plus utile que les coefficients de corrélation que nous avons découverts précédemment.

Nous ne pouvons toutefois pas nous arrêter là. Nous devons d'abord vérifier si nos coefficients sont statistiquement significatifs à partir de zéro. Nous avons établi une hypothèse de :

\[H_{0} : \beta_{1}=0\nonumber\]

\[H_{\mathrm{a}} : \beta_{1} \neq 0\nonumber\]

pour les trois coefficients de la régression. Rappelons que nous ne serons pas en mesure de dire avec certitude que notre estimation\(b_1\) est la population réelle réelle de\(\beta_1\), mais seulement qu'avec un\((1-\alpha) \%\) niveau de confiance nous ne pouvons pas rejeter l'hypothèse nulle selon laquelle notre estimation\(\beta_1\) est significativement différente de zéro. L'analyste affirme que le prix des roses a un impact sur la quantité demandée. En effet, chacune des variables incluses a un impact sur la quantité de roses demandée. L'allégation se trouve donc dans les hypothèses alternatives. Il faudra une très grande probabilité, 0,95 dans ce cas, pour renverser l'hypothèse nulle, le statu quo, cela\(\beta = 0\). Dans tous les tests d'hypothèse de régression, l'affirmation est alternative et l'affirmation est que la théorie a trouvé une variable qui a un impact significatif sur la\(Y\) variable.

La statistique de test pour cette hypothèse suit la formule de normalisation familière qui compte le nombre d'écarts types\(t\), selon laquelle la valeur estimée du paramètre est éloignée de la valeur hypothétisée\(\beta_0\), qui est nulle dans ce cas :\(b_1\)

\[t_{c}=\frac{b_{1}-\beta_{0}}{S_{b_{1}}}\nonumber\]

L'ordinateur calcule cette statistique de test et la présente sous la forme « t stat ». Vous pouvez trouver cette valeur à droite de l'erreur type de l'estimation du coefficient. L'erreur type du coefficient pour\(b_1\) se trouve\(S_{b_1}\) dans la formule. Pour parvenir à une conclusion, nous comparons cette statistique du test\(t\) à la valeur critique des degrés de liberté de l'étudiant\(n-3-1 =29\), et alpha = 0,025 (seuil de signification de 5 % pour un test bilatéral). Notre\(t\) statistique pour\(b_1\) est d'environ 5,90, ce qui est supérieur à 1,96 (la valeur critique que nous avons recherchée dans la table t), nous ne pouvons donc pas accepter nos hypothèses nulles d'absence d'effet. Nous concluons que le prix a un effet significatif car la valeur t calculée se trouve dans la queue. Nous effectuons le même test pour b2 et b3. Pour chaque variable, nous constatons que nous ne pouvons pas accepter l'hypothèse nulle d'absence de relation, car les statistiques t calculées se situent à la fin de chaque cas, c'est-à-dire supérieures à la valeur critique. Il a été déterminé que toutes les variables de cette régression avaient un effet significatif sur la demande de roses.

Ces tests nous indiquent si un coefficient individuel est significativement différent de zéro, mais ils ne tiennent pas compte de la qualité globale du modèle. Nous avons vu que le R carré ajusté en fonction des degrés de liberté indique que ce modèle avec ces trois variables explique 70 % de la variation de la quantité de roses demandée. Nous pouvons également effectuer un deuxième test du modèle pris dans son ensemble. Il s'agit du\(F\) test présenté dans la section 13.4 de ce chapitre. Comme il s'agit d'une régression multiple (plus d'un X), nous utilisons le\(F\) -test pour déterminer si nos coefficients influent collectivement\(Y\). L'hypothèse est la suivante :

\[H_{0} : \beta_{1}=\beta_{2}=\ldots=\beta i=0\nonumber\]

\[H_a: "\text{at least one of the} \beta_i \text{ is not equal to 0}"\nonumber\]

Dans la section ANOVA de la sortie, nous trouvons la\(F\) statistique calculée pour ces hypothèses. Pour cet exemple, la\(F\) statistique est 21,9. Encore une fois, la comparaison de la\(F\) statistique calculée avec la valeur critique compte tenu du niveau de signification et des degrés de liberté souhaités nous permettra de tirer une conclusion.

La meilleure façon de tirer une conclusion pour ce test statistique est d'utiliser la règle de comparaison des valeurs de p. La valeur de p est la surface de la queue, sur la base de la\(F\) statistique calculée. Essentiellement, l'ordinateur trouve la\(F\) valeur dans le tableau pour nous et calcule la valeur de p. Dans le résultat du résumé, sous « signification F » se trouve cette probabilité. Pour cet exemple, il est calculé à 2,6\(X\) 10-5, ou 2,6, puis en déplaçant la décimale de cinq places vers la gauche. (.000026) Il s'agit d'un niveau de probabilité presque infinitésimal qui est certainement inférieur à notre niveau alpha de 0,05 pour un niveau de signification de 5 %.

En ne pouvant accepter les hypothèses nulles, nous concluons que cette spécification de ce modèle est valide car au moins un des coefficients estimés est significativement différent de zéro. Puisque\(F\) -calculated est supérieur à\(F\) -critique, nous ne pouvons pas accepter H0, ce qui signifie que\(X_1\),\(X_2\) et\(X_3\) ensemble, cela a un effet significatif sur\(Y\).

Le développement de machines informatiques et de logiciels utiles à la recherche universitaire et commerciale a permis de répondre à des questions que nous n'arrivions même pas à formuler il y a quelques années. Les données sont disponibles sous forme électronique et peuvent être mises en place à des fins d'analyse d'une manière et à une vitesse inimaginables il y a dix ans. L'ampleur des ensembles de données qui peuvent aujourd'hui être utilisés pour la recherche et l'analyse nous permet d'obtenir des résultats de meilleure qualité que par le passé. Même avec une seule feuille de calcul Excel, nous pouvons effectuer des recherches de très haut niveau. Cette section vous donne les outils nécessaires pour mener certaines de ces recherches très intéressantes, la seule limite étant votre imagination.