Introduction à la régression multiple dans Microsoft Excel

■■ Notre usine fabrique trois produits. Comment puis-je prédire le coût de fonctionnement de l’usine en fonction du nombre d’unités produites?

■■ Quelle est la précision de mes prévisions pour prévoir le coût mensuel en fonction des unités produites?

■■ Je sais utiliser la commande Analyse du solveur pour exécuter une régression multiple. Existe-t-il un moyen d’exécuter la régression sans utiliser cette commande et de placer les résultats de la régression dans la même feuille de calcul que les données?

Réponses aux questions de cet article

Cette section fournit les réponses aux questions répertoriées au début de cet article.

 

Notre usine fabrique trois produits. Comment puis-je prédire le coût de fonctionnement de l’usine en fonction du nombre d’unités produites?

Les article 53 à 55 décrivent comment utiliser la courbe de tendance dans Microsoft Excel 2013 pour prédire une variable (appelée y ou la variable dépendante) à partir d’une autre variable (appelée x ou la variable indépendante). Cependant, vous souhaitez souvent utiliser plusieurs variables indépendantes (appelées variables indépendantes x1, x2, … xn) pour prédire la valeur d’une variable dépendante. Dans ces cas, vous pouvez utiliser l’option de régression multiple dans la fonction d’analyse de données Excel ou la fonction DROITEREG pour estimer la relation souhaitée.

La régression multiple suppose que la relation entre y et x1, x2,. . . xn a la forme suivante:

Y = Constante + B1x1 + B2x2 + … Bnxn

Excel calcule les valeurs de Constant, B1, B2,. . . Bn pour faire les prédictions de cette équation

aussi précis (dans le sens de minimiser la somme des erreurs quadratiques) que possible. L’exemple suivant illustre le fonctionnement de la régression multiple.

La feuille de calcul Data du fichier Mrcostest.xlsx (voir figure 1) contient le coût de fonctionnement d’une usine sur 19 mois ainsi que le nombre d’unités de produit A, produit B et produit C produites chaque mois.

FIGURE 1 Il s’agit des données permettant de prévoir les coûts d’exploitation mensuels.

Vous souhaitez trouver les meilleures prévisions de coûts d’exploitation mensuels sous la forme suivante (appelé formulaire 1):

Coût d’exploitation mensuel = Constante + B1 * (Unités A produites) + B2 * (Unités B produites) + B3 * (unités C produites)

La fonction d’analyse des données Excel peut trouver l’équation de ce formulaire qui correspond le mieux à vos données.

Cliquez sur Analyse des données dans le groupe Analyse de l’onglet Données, puis sélectionnez Régression. Remplissez la Boîte de dialogue de régression, comme illustré à la figure 2.

        Remarque Si vous n’avez pas précédemment installé Analyse du solveur, cliquez sur l’onglet Fichier, cliquez sur Cliquez sur Options, puis sur Compléments. Avec les compléments Excel sélectionnés dans la boîte de dialogue Gérer, cliquez sur OK, activez la case à cocher Analyse du solveur , puis cliquez sur OK.

FIGURE 2 Il s’agit de la boîte de dialogue de régression.

■■ La plage d’entrée Y (B3: B22 dans cet exemple) contient la variable ou les données dépendantes (y Étiquette de coût) que vous souhaitez prévoir.

■■ La plage d’entrée X (C3: E22) contient les données ou les variables indépendantes (y compris les étiquettes A Made, B Made et C Made) que vous souhaitez utiliser dans la prédiction. Excel a une limite de 15 variables indépendantes, qui doivent être dans des colonnes adjacentes.

■■ Étant donné que les plages Input X et Input Y incluent des étiquettes, cochez la case Étiquettes.

■■ La sortie a été placée dans une nouvelle feuille de calcul, intitulée Régression.

■■ Si vous cochez la case Résiduels, Excel répertorie, pour chaque observation, la prédiction du formulaire 1 et le résiduel, ce qui correspond au coût observé moins le coût prévu.

Après avoir cliqué sur OK dans la boîte de dialogue de régression, vous obtenez la sortie illustrée à la figure 3 et Figure 4. (Voir la feuille de calcul de régression.)

FIGURE 3 Voici la sortie de régression multiple d’origine.

FIGURE 4 Voici la sortie résiduelle de régression multiple d’origine.

 

Quelle est la meilleure équation de prédiction?

 Vous trouverez dans la colonne Coefficients (colonne B de la sortie récapitulative) que la meilleure équation du formulaire 1 qui peut être utilisée pour prédire le coût mensuel est la suivante:

Coût mensuel prévu = 35102,90 + 2,07 (AMade) +4,18 (BMade) +4,79 (CMade)

Une question naturelle est laquelle de vos variables indépendantes sont utiles pour prédire le coût mensuel? Après tout, si vous choisissez le nombre de matchs gagnés par les Mariners de Seattle au cours d’une période d’un mois comme variable indépendante, vous vous attendez à ce que cette variable ait peu d’effet sur la prévision des coûts d’exploitation mensuels. Lorsque vous exécutez une régression, chaque variable indépendante a une valeur p  entre 0 et 1. Toute variable indépendante avec une valeur de p (voir colonne E) inférieure ou égale à 0,05 est considérée comme utile pour prédire la variable dépendante. Ainsi, plus la valeur de p est petite, plus le pouvoir prédictif de la variable indépendante est élevé. Vos trois variables indépendantes ont des valeurs de p de 0,23 (pour A Made), 0,025 (pour B Made) et 0,017 (pour C Made). Ces valeurs de p peuvent être interprétées comme suite:

■■ Lorsque vous utilisez B Made et C Made pour prévoir les coûts d’exploitation mensuels, vous  (1 – 0,23) que A Made ajoute une puissance prédictive.

■■ Lorsque vous utilisez A Made et C Made pour prévoir les coûts d’exploitation mensuels, il y a 97,5% de chances (1 – 0,025) que B Made ajoute une puissance prédictive.

■■ Lorsque vous utilisez A Made et B Made pour prévoir les coûts d’exploitation mensuels, il y a 98,3% de chances (1 – 0,017) que C Made ajoute une puissance prédictive.

Vos valeurs de p indiquent que A Made n’ajoute pas beaucoup de pouvoir prédictif à B Made et C Made, ce qui signifie que si vous connaissez B Made et C Made, vous pouvez prédire les coûts d’exploitation mensuels à peu près aussi bien que si vous incluez A Made comme variable indépendante. Par conséquent, vous pouvez choisir de supprimer A Made en tant que variable indépendante et d’utiliser uniquement B Made et C Made pour votre prédiction. Copiez les données dans la feuille de calcul A supprimé et supprimez la colonne A Made (colonne C). Ajustez la plage d’entrée X être C3: D22. Dans la feuille de calcul NoA, vous pouvez voir la sortie de régression illustrée à la figure 5 et Figure 6.

FIGURE 5 Sortie de régression multiple avec suppression des données A Made en tant que variable indépendante.

FIGURE 6 Il s’agit de la sortie résiduelle calculée lorsque les données A Made sont supprimées en tant que variable indépendante.

Vous pouvez voir que B Made et C Made ont des valeurs de p très faibles (0,002 et 0,007, respectivement). Ces valeurs indiquent que ces deux variables indépendantes ont un pouvoir prédictif utile. En utilisant le nouveaux coefficients dans la colonne B, vous pouvez désormais prévoir les coûts d’exploitation mensuels en utilisant les éléments suivants équation:

Coût d’exploitation mensuel prévu = 35 475,3 + 5,32 (BMade) +5,42 (CMade)

Quelle est la précision de mes prévisions pour prévoir le coût mensuel en fonction des unités produites?

Dans la sortie de régression de la cellule B5 de la feuille de calcul NoA (voir figure 57-5), R2 est égal à 0,61. Un R2 une valeur telle que celle-ci signifie qu’ensemble, B Made et C Made expliquent 61 pourcent des coûts d’exploitation mensuels. Notez que dans la régression d’origine, qui comprenait A Made comme variable indépendante, R2 est égal à 0,65. Cela indique que l’ajout de A Made en tant que variable indépendante n’explique que 4% de variation supplémentaire des coûts d’exploitation mensuels. Une telle différence mineure est cohérente avec la décision de supprimer A Made comme variable indépendante.

Dans la sortie de régression de la cellule B7 de la feuille de calcul NoA, l’erreur standard pour la régression avec B Made et C Made en tant que variables indépendantes est de 1274. Vous vous attendez à ce qu’environ 68% de vos prévisions de régression multiple soient exactes avec une erreur standard et 95% avec deux erreurs standard. Toute prévision qui diffère de la valeur réelle de plus de deux normes les erreurs sont considérées comme des valeurs aberrantes. Ainsi, si votre coût d’exploitation prévu est erroné de plus de 2 548 $ (2 * 1274), vous considérez cette observation comme une valeur aberrante.

Dans la partie résiduelle de la sortie, illustrée précédemment, dans la figure 57-6, vous êtes donné pour chaque observation le coût prévu et le résiduel, qui est égal au coût réel moins le coût prévu. Pour la première observation, par exemple, le coût prévu est de 43 381,10 $. Le résidu de 1 057,95 $ indique que la prévision du coût réel était trop faible de 1 057,95 $.

 

Je sais comment utiliser la commande Analyse des données pour exécuter une régression multiple. Existe-t-il un moyen d’exécuter la régression sans utiliser cette commande et de placer les résultats de la régression dans la même feuille de calcul que les données?

La fonction Excel DROITEREG peut être utilisée pour insérer les résultats d’une analyse de régression directement dans un classeur. La fonction DROITEREG est un exemple de fonction de tableau. (Voir L’article 87, «Formules et fonctions de tableau», pour plus de détails sur les fonctions et les formules de tableau.) Pour l’instant, voici une introduction aux fonctions de tableau:

■■ Avant de saisir une fonction de matrice, vous devez toujours sélectionner la plage de cellules dans laquelle vous souhaitez placer les résultats de la fonction de matrice.

■■ Au lieu d’appuyer sur Entrée pour effectuer le calcul, vous devez appuyer sur Ctrl + Maj + Entrée pour terminer la saisie d’une fonction de tableau.

■■ Après avoir entré une fonction de tableau, vous voyez un crochet entre les barres dans la barre de formule lorsque vous sélectionnez une cellule dans laquelle se trouvent les résultats de la fonction de tableau. Ce crochet indique que les résultats dans la cellule ont été calculés avec une fonction de tableau.

■■ Vous ne pouvez pas modifier les données dans aucune partie d’une plage créée par une fonction de tableau.

Pour utiliser la fonction DROITEREG lorsqu’il y a m variables indépendantes, commencez par sélectionner une plage de cellules vides composée de cinq lignes et m + 1 colonnes où vous souhaitez que DROITEREG dépose les résultats. Dans la feuille de calcul A supprimé, la plage F5: H9 a été utilisée.

La syntaxe de la fonction DROITEREG est DROITEREG (KnownYs, KnownXs, Vrai, Vrai). Si le troisième argument est remplacé par False, Excel estime l’équation sans terme constant. Si vous changez le quatrième argument en Faux, la fonction DROITEREG omet de nombreux calculs de régression et renvoie uniquement l’équation de régression multiple.

Avec la cellule supérieure gauche de la plage cible sélectionnée (F5 dans cet exemple), sélectionnez la plage de la taille souhaitée (dans ce cas, la plage de cellules F5: H9) et   puis entrez la formule = DROITEREG. (B4: B22, C4: D22, Vrai, Vrai). Parce que DROITEREG est une fonction de tableau à ce stade, maintenez d’abord Ctrl + Maj enfoncées, puis appuyez sur Entrée pour que la fonction fonctionne correctement. Après avoir utilisé cette combinaison de touches, vous obtenez les résultats indiqués dans la figure 7. (Voir la feuille de calcul A supprimé.)

FIGURE 7 Utilisez la fonction DROITEREG pour calculer une régression multiple.

À la ligne 5, vous trouverez l’équation de prédiction (coefficients lus de droite à gauche, en commençant par l’interception) de Coût mensuel prévu = 35 475,3 + 5,32 (B Made) + 5,43 (C Made). La ligne 6 contient des erreurs standard pour chaque estimation de coefficient, mais celles-ci ne sont pas trop pertinentes. La cellule F7 contient la valeur R2 de 0,61, et la cellule G7 contient l’erreur standard de régression de 1274. Les lignes 8 et 9 contiennent des informations (statistique F, degrés de liberté, régression de la somme des carrés et somme des carrés résiduels) qui sont moins importantes.

        Remarque Les problèmes avec lesquels vous pouvez travailler pour en savoir plus sur la régression multiple sont disponibles à la fin du chapitre 59, «Modélisation des non-linéarités et des interactions».

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Fonctions Excel

Macro VBA Utiles

Excel Pratique

Programmation VBA

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x
()
x