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

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

À propos de l'exécution du dernier type analyse de régression dans Excel, nous en parlerons plus en détail 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- Ce divers facteurs, affectant la 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 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 valeur donnée est égal à 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 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.

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);

· exponentiel (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, la moyenne mensuelle salaire et le nombre d'employés qui sont partis. Il est nécessaire de déterminer la dépendance du nombre de salariés qui quittent leur emploi par rapport au salaire moyen.

Modèle régression linéaire a la forme suivante :

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 :

1. Cliquez sur le bouton « Bureau » et accédez à l'onglet « » Options Excel" "Modules complémentaires".

2. En bas, sous la liste déroulante, dans le champ « Gérer », il y aura une inscription « Compléments Excel » (si ce n'est pas le cas, cliquez sur la case à droite et sélectionnez). Et le bouton « Go ». Cliquez sur.

3. Une liste des modules complémentaires disponibles s'ouvre. Sélectionnez « Package d’analyse » et cliquez sur OK.

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

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

1. Ouvrez le menu de l'outil « Analyse des données ». Sélectionnez "Régression".

2. Un menu s'ouvrira pour sélectionner les valeurs d'entrée et les options de sortie (où afficher le résultat). Dans les champs des données initiales, nous indiquons la plage du paramètre décrit (Y) et le facteur qui l'influence (X). Il n'est pas nécessaire de remplir le reste.

3. Après avoir cliqué sur OK, le programme affichera les calculs sur une nouvelle feuille (vous pouvez sélectionner un intervalle à afficher sur la feuille actuelle ou attribuer la sortie à un nouveau classeur).

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.

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 utiliser Outils Excel trouver le coefficient de corrélation.

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

Objectif : Déterminer s'il existe une relation entre le temps de fonctionnement tour et le coût de son entretien.

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

1. Dans la catégorie « Statistique », sélectionnez la fonction CORREL.
2. Argument « Tableau 1 » - la première plage de valeurs – temps de fonctionnement de la machine : A2 : A14.
3. 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 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.

DANS Exceller il y a un encore plus rapide et moyen pratique construire un graphique de régression linéaire (et même les principaux types de régressions non linéaires, voir ci-dessous). Cela peut être fait comme suit:

1) sélectionnez les colonnes avec des données X Et Oui(ils devraient être dans cet ordre !) ;

2) appeler Assistant Graphique et sélectionnez dans le groupe Taper – Place et appuyez immédiatement sur Prêt;

3) sans désélectionner le schéma, sélectionnez l'élément du menu principal qui apparaît Diagramme, dans lequel vous devez sélectionner l'élément Ajouter une ligne de tendance;

4) dans la boîte de dialogue qui apparaît Ligne de tendance dans l'onglet Taper choisir Linéaire;

5) dans l'onglet Possibilités vous pouvez activer l'interrupteur Afficher l'équation dans le diagramme, ce qui vous permettra de voir l'équation de régression linéaire (4.4), dans laquelle les coefficients (4.5) seront calculés.

6) Dans le même onglet vous pouvez activer le switch Placez la valeur de fiabilité d'approximation (R ^ 2) sur le diagramme. Cette valeur est le carré du coefficient de corrélation (4.3) et montre dans quelle mesure l'équation calculée décrit la dépendance expérimentale. Si R. 2 est proche de l’unité, alors l’équation de régression théorique décrit bien la dépendance expérimentale (la théorie s’accorde bien avec l’expérience), et si R. 2 est proche de zéro, alors cette équation n'est pas adaptée pour décrire la dépendance expérimentale (la théorie n'est pas d'accord avec l'expérience).

Après avoir effectué les actions décrites, vous obtiendrez un diagramme avec un graphique de régression et son équation.

§4.3. Principaux types de régression non linéaire

Régression parabolique et polynomiale.

Parabolique dépendance de la valeur Oui de la taille X s'appelle une dépendance exprimée par une fonction quadratique (parabole du 2ème ordre) :

Cette équation s'appelle équation de régression parabolique Y sur X. Possibilités UN, b, Avec sont appelés coefficients de régression parabolique. Le calcul des coefficients de régression parabolique est toujours fastidieux, il est donc recommandé d'utiliser un ordinateur pour les calculs.

L'équation (4.8) de régression parabolique est un cas particulier d'une régression plus générale appelée polynomiale. Polynôme dépendance de la valeur Oui de la taille X s'appelle une dépendance exprimée par un polynôme n-ème ordre :

où sont les chiffres et moi (je=0,1,…, n) sont appelés coefficients de régression polynomiale.

Régression de puissance.

Pouvoir dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

Cette équation s'appelle équation de régression de puissance Y sur X. Possibilités UN Et b sont appelés coefficients de régression de puissance.

ln = ln un+b· dans X. (4.11)

Cette équation décrit une droite sur un plan avec des axes de coordonnées logarithmiques ln X et ln. Par conséquent, le critère d'applicabilité de la régression de puissance est l'exigence que les points des logarithmes des données empiriques ln x je et ln et jeétaient les plus proches de la ligne droite (4,11).

Régression exponentielle.

Indicatif(ou exponentiel) dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

(ou ). (4.12)

Cette équation s'appelle équation exponentielle(ou exponentiel) régression Y sur X. Possibilités UN(ou k) Et b sont appelés coefficients exponentiels(ou exponentiel) régression.

Si nous prenons le logarithme des deux côtés de l’équation de régression de puissance, nous obtenons l’équation

ln = X dans un+ln b(ou ln = k x+ln b). (4.13)

Cette équation décrit la dépendance linéaire du logarithme d'une quantité ln sur une autre quantité X. Par conséquent, le critère d’applicabilité de la régression de puissance est l’exigence que les points de données empiriques de même valeur x je et logarithmes d'une autre quantité ln et jeétaient les plus proches de la ligne droite (4,13).

Régression logarithmique.

Logarithmique dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

=un+b· dans X. (4.14)

Cette équation s'appelle équation de régression logarithmique Y sur X. Possibilités UN Et b sont appelés coefficients de régression logarithmique.

Régression hyperbolique.

Hyperbolique dépendance de la valeur Oui de la taille X est appelée une dépendance de la forme :

Cette équation s'appelle équation de régression hyperbolique Y sur X. Possibilités UN Et b sont appelés coefficients de régression hyperbolique et sont déterminés par la méthode des moindres carrés. L'application de cette méthode conduit aux formules :

Dans les formules (4.16-4.17) la sommation est effectuée sur l'indice je de un au nombre d'observations n.

Malheureusement, dans Exceller il n'existe aucune fonction qui calcule les coefficients de régression hyperbolique. Dans les cas où l'on ne sait pas que les quantités mesurées sont liées par une proportionnalité inverse, il est recommandé de rechercher une équation de régression en puissance au lieu de l'équation de régression hyperbolique, donc dans Exceller il existe une procédure pour le trouver. Si une dépendance hyperbolique est supposée entre les grandeurs mesurées, alors ses coefficients de régression devront être calculés à l'aide de tables de calcul auxiliaires et d'opérations de sommation à l'aide de formules (4.16-4.17).