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.
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 :
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.
Analyse de régression V Microsoft Excel- la plupart guides complets sur l'utilisation de MS Excel pour résoudre des problèmes d'analyse de régression dans le domaine de l'analyse commerciale. Konrad Carlberg explique clairement questions théoriques, dont la connaissance vous aidera à éviter de nombreuses erreurs à la fois lorsque vous effectuez vous-même une analyse de régression et lorsque vous évaluez les résultats de l'analyse effectuée par d'autres personnes. Tout le matériel, depuis les simples corrélations et tests t jusqu'aux analyses multiples de covariance, est basé sur exemples réels et est accompagné Description détaillée procédures étape par étape correspondantes.
Le livre discute des caractéristiques et des controverses associées à Fonctions Excel pour travailler avec la régression, examine les implications de chaque option et de chaque argument, et explique comment appliquer de manière fiable les méthodes de régression dans des domaines allant de la recherche médicale à l'analyse financière.
Konrad Carlberg. Analyse de régression dans Microsoft Excel. – M. : Dialectique, 2017. – 400 p.
Téléchargez la note au format ou, exemples au format
Chapitre 1 : Évaluation de la variabilité des données
Les statisticiens disposent de nombreuses mesures de variation. L'un d'eux est la somme des écarts carrés des valeurs individuelles par rapport à la moyenne. Dans Excel, la fonction SQUARE() est utilisée pour cela. Mais la variance est plus souvent utilisée. La dispersion est la moyenne des carrés des écarts. La variance est insensible au nombre de valeurs dans l'ensemble de données étudié (alors que la somme des carrés des écarts augmente avec le nombre de mesures).
Excel propose deux fonctions qui renvoient la variance : DISP.G() et DISP.V() :
- Utilisez la fonction DISP.G() si les valeurs à traiter forment une population. Autrement dit, les valeurs contenues dans la plage sont les seules valeurs qui vous intéressent.
- Utilisez la fonction DISP.B() si les valeurs à traiter forment un échantillon issu d'une population plus large. On suppose qu'il existe des valeurs supplémentaires dont vous pouvez également estimer la variance.
Si une quantité telle qu’une moyenne ou un coefficient de corrélation est calculée à partir d’une population, on l’appelle un paramètre. Une quantité similaire calculée à partir d’un échantillon est appelée statistique. Comptage des écarts de la moyenne V cet ensemble, vous obtiendrez une somme des écarts au carré qui est plus petite que si vous les comptiez à partir de toute autre valeur. Une affirmation similaire est vraie pour la variance.
Plus la taille de l’échantillon est grande, plus la valeur statistique calculée est précise. Mais il n’existe pas de taille d’échantillon inférieure à la taille de la population pour laquelle vous pouvez être sûr que la valeur statistique correspond à la valeur du paramètre.
Supposons que vous disposiez d'un ensemble de 100 hauteurs dont la moyenne diffère de la moyenne de la population, aussi petite soit-elle. En calculant la variance d'un échantillon, vous obtiendrez une valeur, disons 4. Cette valeur est inférieure à toute autre valeur pouvant être obtenue en calculant l'écart de chacune des 100 valeurs de hauteur par rapport à toute valeur autre que la moyenne de l'échantillon. , y compris par rapport à la moyenne réelle de la population générale. Par conséquent, la variance calculée sera différente et plus petite de la variance que vous obtiendriez si vous découvriez et utilisiez d’une manière ou d’une autre un paramètre de population plutôt qu’une moyenne d’échantillon.
La somme moyenne des carrés déterminée pour l'échantillon fournit une estimation inférieure de la variance de la population. La variance ainsi calculée est appelée déplacéévaluation. Il s'avère que pour éliminer le biais et obtenir une estimation non biaisée, il suffit de diviser la somme des écarts au carré non par n, Où n- la taille de l'échantillon, et n – 1.
Ordre de grandeur n – 1 s'appelle le nombre (nombre) de degrés de liberté. Exister différentes façons calcul de cette quantité, bien que tous impliquent soit de soustraire un certain nombre de la taille de l’échantillon, soit de compter le nombre de catégories dans lesquelles appartiennent les observations.
L'essence de la différence entre les fonctions DISP.G() et DISP.V() est la suivante :
- Dans la fonction VAR.G(), la somme des carrés est divisée par le nombre d'observations et représente donc une estimation biaisée de la variance, la vraie moyenne.
- Dans la fonction DISP.B(), la somme des carrés est divisée par le nombre d'observations moins 1, soit par le nombre de degrés de liberté, ce qui donne une estimation plus précise et impartiale de la variance de la population à partir de laquelle l'échantillon a été tiré.
Écart-type écart-type, SD) – oui Racine carrée de la dispersion :
La mise au carré des écarts transforme l'échelle de mesure en une autre métrique, qui est le carré de l'originale : mètres - en mètres carrés, dollars - en dollars carrés, etc. L'écart type est la racine carrée de la variance et nous ramène donc aux unités de mesure d'origine. Selon ce qui est le plus pratique.
Il est souvent nécessaire de calculer l’écart type après que les données ont été soumises à certaines manipulations. Et bien que dans ces cas les résultats soient sans aucun doute des écarts types, ils sont généralement appelés erreurs types. Il existe plusieurs types d'erreurs types, notamment l'erreur type de mesure, l'erreur type de proportion et l'erreur type de moyenne.
Disons que vous avez collecté des données sur la taille de 25 hommes adultes sélectionnés au hasard dans chacun des 50 États. Ensuite, vous calculez la taille moyenne des hommes adultes dans chaque État. Les 50 valeurs moyennes résultantes peuvent à leur tour être considérées comme des observations. À partir de là, vous pouvez calculer leur écart type, qui est erreur standard de la moyenne. Riz. 1. compare la distribution de 1 250 valeurs individuelles brutes (données de taille pour 25 hommes dans chacun des 50 États) avec la distribution des moyennes des 50 États. La formule pour estimer l'erreur type de la moyenne (c'est-à-dire l'écart type des moyennes, et non des observations individuelles) :
où est l'erreur type de la moyenne ; s– écart type des observations originales ; n– nombre d'observations dans l'échantillon.
Riz. 1. La variation des moyennes d’un État à l’autre est nettement inférieure à la variation des observations individuelles.
En statistique, il existe une convention concernant l'utilisation de lettres grecques et latines pour représenter des quantités statistiques. Il est d'usage de désigner les paramètres de la population générale par des lettres grecques et les échantillons de statistiques par des lettres latines. Par conséquent, lorsque nous parlons de l’écart type de la population, nous l’écrivons sous la forme σ ; si l'on considère l'écart type de l'échantillon, alors on utilise la notation s. Quant aux symboles désignant les moyennes, ils ne s'accordent pas aussi bien entre eux. La moyenne de la population est désignée par la lettre grecque μ. Cependant, le symbole X̅ est traditionnellement utilisé pour représenter la moyenne de l'échantillon.
score z exprime la position d'une observation dans la distribution en unités d'écart type. Par exemple, z = 1,5 signifie que l'observation est à 1,5 écart-type de la moyenne. grandes valeurs. Terme score z utilisé pour les évaluations individuelles, c'est-à-dire pour les mesures attribuées éléments individuels des échantillons. Le terme utilisé pour désigner de telles statistiques (telles que la moyenne de l'État) score z:
où X̅ est la moyenne de l'échantillon, μ est la moyenne de la population, est l'erreur type des moyennes d'un ensemble d'échantillons :
où σ est l'erreur type de la population (mesures individuelles), n- taille de l'échantillon.
Disons que vous travaillez comme instructeur dans un club de golf. Vous avez pu mesurer la distance de vos tirs sur une longue période et savez que la moyenne est de 205 yards et l'écart type est de 36 yards. On vous propose un nouveau club, prétendant qu'il augmentera votre distance de frappe de 10 mètres. Vous demandez à chacun des 81 prochains clients du club de faire un essai avec un nouveau club et d'enregistrer leur distance de swing. Il s'est avéré que la distance moyenne avec le nouveau club était de 215 mètres. Quelle est la probabilité qu’une différence de 10 mètres (215 – 205) soit due uniquement à une erreur d’échantillonnage ? Ou, pour le dire autrement : quelle est la probabilité que, lors de tests plus approfondis, le nouveau club ne démontre pas une augmentation de la distance de frappe par rapport à la moyenne à long terme existante de 205 yards ?
Nous pouvons vérifier cela en générant un z-score. Erreur type de la moyenne :
Puis z-score :
Nous devons trouver la probabilité que la moyenne de l’échantillon soit éloignée de 2,5σ de la moyenne de la population. Si la probabilité est faible, alors les différences ne sont pas dues au hasard, mais à la qualité du nouveau club. Excel ne dispose pas de fonction prête à l'emploi pour déterminer la probabilité du score z. Cependant, vous pouvez utiliser la formule =1-NORM.ST.DIST(z-score,TRUE), où la fonction NORM.ST.DIST() renvoie l'aire sous la courbe normale à gauche du z-score (Figure 2).
Riz. 2. La fonction NORM.ST.DIST() renvoie l'aire sous la courbe à gauche de la valeur z ; Pour agrandir l'image cliquez dessus clic-droit souris et sélectionnez Ouvrir l'image dans un nouvel onglet
Le deuxième argument de la fonction NORM.ST.DIST() peut prendre deux valeurs : TRUE – la fonction renvoie l'aire de l'aire sous la courbe à gauche du point spécifié par le premier argument ; FAUX – la fonction renvoie la hauteur de la courbe au point spécifié par le premier argument.
Si la moyenne de la population (μ) et l'écart type (σ) ne sont pas connus, la valeur t est utilisée (voir détails). Les structures du score z et du score t diffèrent en ce sens que l'écart type s obtenu à partir des résultats de l'échantillon est utilisé pour trouver le score t plutôt que la valeur connue du paramètre de population σ. La courbe normale a une forme unique et la forme de la distribution des valeurs t varie en fonction du nombre de degrés de liberté df. degrés de liberté) de l'échantillon qu'il représente. Le nombre de degrés de liberté de l'échantillon est égal à n – 1, Où n- taille de l'échantillon (Fig. 3).
Riz. 3. La forme des distributions t qui apparaissent dans les cas où le paramètre σ est inconnu diffère de la forme de la distribution normale
Excel a deux fonctions pour la distribution t, également appelée distribution de Student : STUDENT.DIST() renvoie l'aire sous la courbe à gauche d'une valeur t donnée, et STUDENT.DIST.PH() renvoie l'aire à la droite.
Chapitre 2. Corrélation
La corrélation est une mesure de dépendance entre les éléments d'un ensemble de paires ordonnées. La corrélation est caractérisée Coefficients de corrélation de Pearson–r. Le coefficient peut prendre des valeurs comprises entre –1,0 et +1,0.
Où Sexe Et S y– écarts types des variables X Et Oui, S xy– covariance :
Dans cette formule, la covariance est divisée par les écarts types des variables X Et Oui, supprimant ainsi les effets d'échelle liés à l'unité de la covariance. Excel utilise la fonction CORREL(). Le nom de cette fonction ne contient pas les éléments qualificatifs Г et В, qui sont utilisés dans les noms de fonctions telles que STANDARDEV(), VARIANCE() ou COVARIANCE(). Bien que le coefficient de corrélation de l'échantillon fournisse une estimation biaisée, la raison du biais est différente de celle dans le cas de la variance ou de l'écart type.
En fonction de l'ampleur du coefficient de corrélation général (souvent désigné par la lettre grecque ρ ), Coefficient de corrélation r produit une estimation biaisée, l’effet du biais augmentant à mesure que la taille des échantillons diminue. Cependant, nous n'essayons pas de corriger ce biais de la même manière que nous l'avons fait par exemple lors du calcul de l'écart type, lorsque nous avons substitué non pas le nombre d'observations, mais le nombre de degrés de liberté dans la formule correspondante. En réalité, le nombre d’observations utilisé pour calculer la covariance n’a aucun effet sur l’ampleur.
Le coefficient de corrélation standard est destiné à être utilisé avec des variables liées les unes aux autres par une relation linéaire. La présence de non-linéarité et/ou d'erreurs dans les données (valeurs aberrantes) conduit à un calcul incorrect du coefficient de corrélation. Pour diagnostiquer les problèmes de données, il est recommandé de créer des nuages de points. Il s'agit du seul type de graphique dans Excel qui traite à la fois les axes horizontal et vertical comme axes de valeurs. Un graphique linéaire définit l'une des colonnes comme axe des catégories, ce qui déforme l'image des données (Fig. 4).
Riz. 4. Les droites de régression semblent identiques, mais comparez leurs équations entre elles
Les observations utilisées pour construire le graphique linéaire sont disposées à égale distance le long de l'axe horizontal. Les étiquettes de division le long de cet axe ne sont que des étiquettes et non des valeurs numériques.
Même si la corrélation signifie souvent qu’il existe une relation de cause à effet, elle ne peut pas être utilisée pour prouver que tel est le cas. Les statistiques ne sont pas utilisées pour démontrer si une théorie est vraie ou fausse. Pour exclure les explications concurrentes des résultats d'observation, mettez expériences planifiées. Les statistiques sont utilisées pour résumer les informations collectées au cours de telles expériences et pour quantifier la probabilité que la décision prise soit incorrecte compte tenu de la base de preuves disponible.
Chapitre 3 : Régression simple
Si deux variables sont liées l'une à l'autre, de sorte que la valeur du coefficient de corrélation dépasse, disons, 0,5, alors dans ce cas, il est possible de prédire (avec une certaine précision) la valeur inconnue d'une variable à partir de la valeur connue de l'autre. . Pour obtenir des valeurs de prix prévisionnelles sur la base des données présentées dans la Fig. 5, plusieurs peuvent être utilisés moyens possibles, mais vous n'utiliserez certainement pas celui illustré à la Fig. 5. Néanmoins, vous devriez vous familiariser avec elle, car aucune autre méthode ne permet de démontrer aussi clairement que celle-ci le lien entre corrélation et prédiction. En figue. 5 dans la plage B2:C12 montre un échantillon aléatoire de dix maisons et fournit des données sur la superficie de chaque maison (en pieds carrés) et son prix de vente.
Riz. 5. Les valeurs prévues des prix de vente forment une ligne droite
Trouvez les moyennes, les écarts types et le coefficient de corrélation (plage A14 : C18). Calculez les scores z de la zone (E2:E12). Par exemple, la cellule E3 contient la formule : =(B3-$B$14)/$B$15. Calculez les scores z du prix prévisionnel (F2:F12). Par exemple, la cellule F3 contient la formule : =ЕЗ*$В$18. Convertissez les scores z en prix en dollars (H2 : H12). Dans la cellule NZ, la formule est : =F3*$C$15+$C$14.
Notez que la valeur prédite a toujours tendance à se déplacer vers la moyenne de 0. Plus le coefficient de corrélation est proche de zéro, plus le score z prédit est proche de zéro. Dans notre exemple, le coefficient de corrélation entre la superficie et le prix de vente est de 0,67, et le prix prévisionnel est de 1,0 * 0,67, soit 0,67. Cela correspond à un excès d'une valeur au-dessus de la moyenne égal aux deux tiers d'un écart type. Si le coefficient de corrélation était égal à 0,5, alors le prix prévisionnel serait de 1,0 * 0,5, c'est-à-dire 0,5. Cela correspond à un excès d’une valeur au-dessus de la moyenne égal à seulement un demi-écart type. Chaque fois que la valeur du coefficient de corrélation diffère de la valeur idéale, c'est-à-dire supérieur à -1,0 et inférieur à 1,0, le score de la variable prédite doit être plus proche de sa moyenne que le score de la variable prédictive (indépendante) du sien. Ce phénomène est appelé régression vers la moyenne, ou simplement régression.
Excel dispose de plusieurs fonctions pour déterminer les coefficients d'une équation de droite de régression (appelée ligne de tendance dans Excel) y =kx + b. Pour déterminer k remplit une fonction
= PENTE (valeurs_y_connues, valeurs_x_connues)
Ici à est la variable prédite, et X- variable indépendante. Vous devez suivre strictement cet ordre de variables. La pente de la droite de régression, le coefficient de corrélation, les écarts types des variables et la covariance sont étroitement liés (Figure 6). La fonction INTERMEPT() renvoie la valeur interceptée par la droite de régression sur l'axe vertical :
=LIMIT(valeurs_y_connues, valeurs_x_connues)
Riz. 6. La relation entre les écarts types convertit la covariance en coefficient de corrélation et la pente de la droite de régression
Notez que le nombre de valeurs x et y fournies comme arguments aux fonctions SLOPE() et INTERCEPT() doit être le même.
L'analyse de régression utilise un autre indicateur important– R 2 (R au carré), ou coefficient de détermination. Il détermine quelle contribution à la variabilité globale des données est apportée par la relation entre X Et à. Dans Excel, il existe une fonction appelée CVPIERSON(), qui prend exactement les mêmes arguments que la fonction CORREL().
On dit que deux variables avec un coefficient de corrélation non nul entre elles expliquent la variance ou expliquent la variance. La variance généralement expliquée est exprimée en pourcentage. Donc R. 2 = 0,81 signifie que 81 % de la variance (dispersion) de deux variables est expliquée. Les 19 % restants sont dus à des fluctuations aléatoires.
Excel dispose d'une fonction TENDANCE qui facilite les calculs. Fonction TENDANCE() :
- accepte les valeurs connues que vous fournissez X et valeurs connues à;
- calcule la pente de la droite de régression et la constante (ordonnée à l'origine) ;
- renvoie les valeurs prédites à, déterminé en appliquant une équation de régression à des valeurs connues X(Fig.7).
La fonction TREND() est une fonction tableau (si vous n'avez jamais rencontré de telles fonctions auparavant, je vous le recommande).
Riz. 7. L'utilisation de la fonction TREND() vous permet d'accélérer et de simplifier les calculs par rapport à l'utilisation d'une paire de fonctions SLOPE() et INTERCEPT().
Pour saisir la fonction TREND() sous forme de formule matricielle dans les cellules G3:G12, sélectionnez la plage G3:G12, saisissez la formule TREND (NW:S12;V3:B12), appuyez et maintenez les touches
La fonction TREND() a deux arguments supplémentaires : nouvelles_valeurs_x Et const. Le premier permet de faire une prévision pour le futur, et le second peut forcer la droite de régression à passer par l'origine (une valeur TRUE indique à Excel d'utiliser la constante calculée, une valeur FALSE indique à Excel d'utiliser une constante = 0 ). Excel permet de tracer une droite de régression sur un graphique afin qu'elle passe par l'origine. Commencez par dessiner un nuage de points, puis cliquez avec le bouton droit sur l'un des marqueurs de série de données. Sélectionnez dans la fenêtre qui s'ouvre menu contextuel paragraphe Ajouter une ligne de tendance; choisir une option Linéaire; si nécessaire, faites défiler le panneau vers le bas, cochez la case Configurer une intersection; Assurez-vous que sa zone de texte associée est définie sur 0,0.
Si vous avez trois variables et que vous souhaitez déterminer la corrélation entre deux d'entre elles, en éliminant l'influence de la troisième, vous pouvez utiliser corrélation partielle. Supposons que vous soyez intéressé par la relation entre le pourcentage d'habitants d'une ville ayant terminé leurs études universitaires et le nombre de livres dans les bibliothèques de la ville. Vous avez collecté des données pour 50 villes, mais... Le problème est que ces deux paramètres peuvent dépendre du bien-être des habitants d'une ville particulière. Bien entendu, il est très difficile de trouver 50 autres villes caractérisées par exactement le même niveau de bien-être des habitants.
En utilisant des méthodes statistiques pour contrôler l’influence de la richesse sur le soutien financier des bibliothèques et sur l’abordabilité des collèges, vous pourriez obtenir une quantification plus précise de la force de la relation entre les variables d’intérêt, à savoir le nombre de livres et le nombre de diplômés. Une telle corrélation conditionnelle entre deux variables, lorsque les valeurs des autres variables sont fixes, est appelée corrélation partielle. Une façon de le calculer consiste à utiliser l’équation :
Où rC.B. . W- coefficient de corrélation entre les variables Collège et Livres en excluant l'influence (valeur fixe) de la variable Richesse ; rC.B.- coefficient de corrélation entre les variables Collège et Livres ; rCW- coefficient de corrélation entre les variables Collège et Bien-être social ; rB.W.- coefficient de corrélation entre les variables Livres et Bien-être.
D'un autre côté, une corrélation partielle peut être calculée sur la base de l'analyse des résidus, c'est-à-dire différences entre les valeurs prédites et les résultats associés des observations réelles (les deux méthodes sont présentées sur la Fig. 8).
Riz. 8. Corrélation partielle comme corrélation des résidus
Pour simplifier le calcul de la matrice des coefficients de corrélation (B16:E19), utilisez le package d'analyse Excel (menu Données –> Analyse –> L'analyse des données). Par défaut, ce package n'est pas actif dans Excel. Pour l'installer, passez par le menu Déposer –> Possibilités –> Modules complémentaires. En bas de la fenêtre ouverte PossibilitésExceller trouver le terrain Contrôle, sélectionner Modules complémentairesExceller, Cliquez sur Aller. Cochez la case à côté du complément Pack d'analyse. Cliquez sur A l'analyse des données, sélectionnez l'option Corrélation. Spécifiez $B$2:$D$13 comme intervalle de saisie, cochez la case Libellés sur la première ligne, spécifiez $B$16:$E$19 comme intervalle de sortie.
Une autre possibilité consiste à déterminer une corrélation semi-partielle. Par exemple, vous étudiez les effets de la taille et de l’âge sur le poids. Ainsi, vous disposez de deux variables prédictives : la taille et l’âge, et d’une variable prédictive : le poids. Vous souhaitez exclure l'influence d'une variable prédictive sur une autre, mais pas sur la variable prédictive :
où H – Taille, W – Poids, A – Âge ; L'indice du coefficient de corrélation semi-partiel utilise des parenthèses pour indiquer quelle variable est supprimée et de quelle variable. Dans ce cas, la notation W(H.A) indique que l'effet de la variable Âge est supprimé de la variable Taille, mais pas de la variable Poids.
Il peut sembler que la question discutée n’a pas d’importance significative. Après tout, ce qui compte le plus est la précision avec laquelle fonctionne l’équation de régression globale, tandis que le problème des contributions relatives des variables individuelles à la variance totale expliquée semble être d’importance secondaire. Cependant, ce n'est pas le cas. Une fois que vous commencez à vous demander si une variable vaut la peine d’être utilisée dans une équation de régression multiple, la question devient importante. Cela peut influencer l'évaluation de l'exactitude du choix du modèle d'analyse.
Chapitre 4. Fonction LINEST()
La fonction LINEST() renvoie 10 statistiques de régression. La fonction LINEST() est une fonction tableau. Pour le saisir, sélectionnez une plage contenant cinq lignes et deux colonnes, tapez la formule et cliquez sur
LIGNE (B2:B21,A2:A21,VRAI,VRAI)
Riz. 9. Fonction LINEST() : a) sélectionnez la plage D2:E6, b) saisissez la formule comme indiqué dans la barre de formule, c) cliquez sur
La fonction LINEST() renvoie :
- coefficient de régression (ou pente, cellule D2) ;
- segment (ou constante, cellule E3) ;
- erreurs types du coefficient de régression et de la constante (plage D3 : E3 );
- coefficient de détermination R 2 pour la régression (cellule D4) ;
- erreur standard d'estimation (cellule E4);
- Test F pour régression complète (cellule D5) ;
- nombre de degrés de liberté pour la somme des carrés résiduelle (cellule E5) ;
- somme des carrés de régression (cellule D6) ;
- somme résiduelle des carrés (cellule E6).
Examinons chacune de ces statistiques et comment elles interagissent.
Erreur standard dans notre cas, il s’agit de l’écart type calculé pour les erreurs d’échantillonnage. Autrement dit, il s’agit d’une situation dans laquelle la population générale a une statistique et l’échantillon en a une autre. En divisant le coefficient de régression par l'erreur type, vous obtenez une valeur de 2,092/0,818 = 2,559. En d’autres termes, un coefficient de régression de 2,092 est éloigné de deux erreurs types et demie de zéro.
Si le coefficient de régression est nul, alors la meilleure estimation de la variable prédite est sa moyenne. Deux erreurs types et demie sont assez importantes et vous pouvez supposer en toute sécurité que le coefficient de régression pour la population est différent de zéro.
Vous pouvez déterminer la probabilité d'obtenir un coefficient de régression d'échantillon de 2,092 si sa valeur réelle dans la population est de 0,0 à l'aide de la fonction
STUDENT.DIST.PH (critère t = 2,559 ; nombre de degrés de liberté = 18)
En général, le nombre de degrés de liberté = n – k – 1, où n est le nombre d'observations et k est le nombre de variables prédictives.
Cette formule renvoie 0,00987, ou arrondi à 1 %. Cela nous dit que si le coefficient de régression pour la population est de 0 %, alors la probabilité d'obtenir un échantillon de 20 personnes pour lequel le coefficient de régression estimé est de 2,092 est de 1 %.
Le test F (cellule D5 sur la figure 9) remplit les mêmes fonctions en ce qui concerne la régression complète que le test t en ce qui concerne le coefficient de régression simple par paires. Le test F est utilisé pour tester si le coefficient de détermination R 2 pour une régression est suffisamment grand pour rejeter l'hypothèse selon laquelle dans la population il a une valeur de 0,0, ce qui indique qu'il n'y a pas de variance expliquée par le prédicteur et la variable prédite. Lorsqu’il n’y a qu’une seule variable prédictive, le test F est exactement égal au test t au carré.
Jusqu'à présent, nous avons examiné les variables d'intervalle. Si vous disposez de variables pouvant prendre plusieurs valeurs, représentant noms simples, par exemple, Homme et Femme ou Reptile, Amphibien et Poisson, les représentent sous forme de code numérique. Ces variables sont appelées nominales.
Statistiques R2 quantifie la proportion de variance expliquée.
Erreur type d’estimation. En figue. La figure 4.9 présente les valeurs prédites de la variable Poids, obtenues sur la base de sa relation avec la variable Taille. La plage E2:E21 contient les valeurs résiduelles de la variable Poids. Plus précisément, ces résidus sont appelés erreurs – d’où le terme erreur type d’estimation.
Riz. 10. R 2 et l'erreur type de l'estimation expriment tous deux l'exactitude des prévisions obtenues par régression
Plus l'erreur type de l'estimation est petite, plus l'équation de régression est précise et plus vous vous attendez à ce que toute prédiction produite par l'équation corresponde à l'observation réelle. L’erreur type d’estimation permet de quantifier ces attentes. Le poids de 95 % des personnes ayant une certaine taille sera compris dans la fourchette :
(hauteur * 2,092 – 3,591) ± 2,092 * 21,118
Statistique F est le rapport entre la variance entre les groupes et la variance au sein du groupe. Ce nom a été introduit par le statisticien George Snedecor en l'honneur de Sir, qui a développé l'analyse de variance (ANOVA, Analysis of Variance) au début du 20e siècle.
Le coefficient de détermination R 2 exprime la proportion de la somme totale des carrés associée à la régression. La valeur (1 – R 2) exprime la proportion de la somme totale des carrés associée aux résidus - erreurs de prévision. Le test F peut être obtenu à l'aide de la fonction LINEST (cellule F5 de la figure 11), en utilisant des sommes de carrés (plage G10 : J11), en utilisant des proportions de variance (plage G14 : J15). Les formules peuvent être étudiées dans le fichier Excel ci-joint.
Riz. 11. Calcul du critère F
Lors de l'utilisation de variables nominales, un codage fictif est utilisé (Figure 12). Pour coder des valeurs, il convient d'utiliser les valeurs 0 et 1. La probabilité F est calculée à l'aide de la fonction :
F.DIST.PH(K2;I2;I3)
Ici, la fonction F.DIST.PH() renvoie la probabilité d'obtenir un critère F qui obéit à la distribution F centrale (Fig. 13) pour deux ensembles de données avec les nombres de degrés de liberté donnés dans les cellules I2 et I3, dont la valeur coïncide avec la valeur donnée dans la cellule K2.
Riz. 12. Analyse de régression utilisant des variables muettes
Riz. 13. Distribution F centrale à λ = 0
Chapitre 5. Régression multiple
Lorsque vous passez d'une simple régression par paire avec une variable prédictive à une régression multiple, vous ajoutez une ou plusieurs variables prédictives. Stockez les valeurs des variables prédictives dans des colonnes adjacentes, telles que les colonnes A et B dans le cas de deux prédicteurs, ou A, B et C dans le cas de trois prédicteurs. Avant de saisir une formule incluant la fonction LINEST(), sélectionnez cinq lignes et autant de colonnes qu'il y a de variables prédictives, plus une de plus pour la constante. Dans le cas d'une régression avec deux variables prédictives, la structure suivante peut être utilisée :
LIGNE (A2 : A41 ; B2 : C41 ; VRAI)
De même dans le cas de trois variables :
LIGNE (A2:A61,B2:D61,;VRAI)
Supposons que vous souhaitiez étudier les effets possibles de l'âge et de l'alimentation sur les taux de LDL - des lipoprotéines de basse densité, considérées comme responsables de la formation de plaques d'athérosclérose, responsables de l'athérothrombose (Fig. 14).
Riz. 14. Régression multiple
Le R 2 de la régression multiple (reflété dans la cellule F13) est supérieur au R 2 de toute régression simple (E4, H4). La régression multiple utilise simultanément plusieurs variables prédictives. Dans ce cas, R2 augmente presque toujours.
Pour tout simple équation linéaire Dans une régression avec une variable prédictive, il y aura toujours une corrélation parfaite entre les valeurs prédites et les valeurs de la variable prédictive, puisque dans une telle équation, les valeurs prédictives sont multipliées par une constante et une autre constante est ajouté à chaque produit. Cet effet ne persiste pas dans la régression multiple.
Affichage des résultats renvoyés par la fonction LINEST() pour la régression multiple (Figure 15). Les coefficients de régression sont générés dans le cadre des résultats renvoyés par la fonction LINEST() dans l'ordre inverse des variables(G–H–I correspond à C–B–A).
Riz. 15. Les coefficients et leurs erreurs types sont affichés dans l'ordre inverse sur la feuille de calcul.
Les principes et les procédures utilisés dans l'analyse de régression à variable prédictive unique sont facilement adaptés pour tenir compte de plusieurs variables prédictives. Il s’avère qu’une grande partie de cette adaptation dépend de l’élimination de l’influence des variables prédictives les unes sur les autres. Cette dernière est associée à des corrélations partielles et semi-partielles (Fig. 16).
Riz. 16. La régression multiple peut être exprimée par régression par paires des résidus (voir le fichier Excel pour les formules)
Dans Excel, il existe des fonctions qui fournissent des informations sur les distributions t et F. Les fonctions dont les noms incluent la partie DIST, telles que STUDENT.DIST() et F.DIST(), prennent un test t ou un test F comme argument et renvoient la probabilité d'observer une valeur spécifiée. Les fonctions dont les noms incluent la partie OBR, telles que STUDENT.INV() et F.INR(), prennent une valeur de probabilité comme argument et renvoient une valeur de critère correspondant à la probabilité spécifiée.
Pendant que nous recherchons valeurs critiques distributions t qui coupent les bords de ses régions de queue, nous passons 5 % comme argument à l'une des fonctions STUDENT.INV(), qui renvoie la valeur correspondant à cette probabilité (Fig. 17, 18).
Riz. 17. Test t bilatéral
Riz. 18. Test t unilatéral
En établissant une règle de décision pour la région alpha unilatérale, vous augmentez la puissance statistique du test. Si, lorsque vous commencez une expérience, vous êtes sûr d’avoir toutes les raisons de vous attendre à un coefficient de régression positif (ou négatif), vous devez alors effectuer un test unilatéral. Dans ce cas, la probabilité que vous preniez la bonne décision en rejetant l'hypothèse d'un coefficient de régression nul dans la population sera plus élevée.
Les statisticiens préfèrent utiliser le terme essai dirigé au lieu du terme test à une seule queue et terme test non dirigé au lieu du terme test bilatéral. Les termes dirigé et non orienté sont préférés car ils mettent l'accent sur le type d'hypothèse plutôt que sur la nature des queues de distribution.
Une approche pour évaluer l’impact des prédicteurs basée sur la comparaison de modèles. En figue. La figure 19 présente les résultats d'une analyse de régression qui teste la contribution de la variable Alimentation à l'équation de régression.
Riz. 19. Comparer deux modèles en testant les différences dans leurs résultats
Les résultats de la fonction LINEST() (plage H2:K6) sont liés à ce que j'appelle modèle complet, qui régresse la variable LDL sur les variables Régime, Âge et HDL. La plage H9:J13 présente des calculs sans prendre en compte la variable prédictive Alimentation. J'appelle cela le modèle limité. Dans le modèle complet, 49,2 % de la variance de la variable dépendante LDL était expliquée par les variables prédictives. DANS modèle limité seulement 30,8 % des LDL sont expliqués par les variables Âge et HDL. La perte de R 2 due à l'exclusion de la variable Régime alimentaire du modèle est de 0,183. Dans la plage G15:L17, des calculs sont effectués qui montrent qu'il n'y a qu'une probabilité de 0,0288 que l'effet de la variable Régime soit aléatoire. Chez les 97,1 % restants, l’alimentation a un effet sur les LDL.
Chapitre 6 : Hypothèses et mises en garde pour l'analyse de régression
Le terme « hypothèse » n'est pas défini de manière suffisamment stricte, et la façon dont il est utilisé suggère que si l'hypothèse n'est pas satisfaite, alors les résultats de l'ensemble de l'analyse sont pour le moins discutables ou peut-être invalides. Ce n’est pas réellement le cas, même s’il existe certainement des cas où la violation d’une hypothèse change fondamentalement la donne. Hypothèses de base : a) les résidus de la variable Y sont normalement distribués en tout point X le long de la droite de régression ; b) Les valeurs Y dépendent linéairement des valeurs X ; c) la dispersion des résidus est approximativement la même en chaque point X ; d) il n'y a aucune dépendance entre les résidus.
Si les hypothèses ne jouent pas un rôle significatif, les statisticiens affirment que l’analyse résiste à la violation de l’hypothèse. En particulier, lorsque vous utilisez la régression pour tester les différences entre les moyennes des groupes, l'hypothèse selon laquelle les valeurs Y - et donc les résidus - sont normalement distribuées ne joue pas un rôle significatif : les tests sont robustes aux violations de l'hypothèse de normalité. Il est important d'analyser les données à l'aide de graphiques. Par exemple, inclus dans le module complémentaire L'analyse des données outil Régression.
Si les données ne répondent pas aux hypothèses de la régression linéaire, il existe des approches autres que la régression linéaire à votre disposition. L'un d'eux est la régression logistique (Fig. 20). Près des limites supérieure et inférieure de la variable prédictive, la régression linéaire produit des prédictions irréalistes.
Riz. 20. Régression logistique
En figue. La figure 6.8 présente les résultats de deux méthodes d'analyse de données visant à examiner la relation entre le revenu annuel et la probabilité d'acheter une maison. De toute évidence, la probabilité de réaliser un achat augmentera avec l’augmentation des revenus. Les graphiques permettent de repérer facilement les différences entre les résultats que la régression linéaire prédit la probabilité d'acheter une maison et les résultats que vous pourriez obtenir en utilisant une approche différente.
Dans le langage des statisticiens, rejeter l’hypothèse nulle alors qu’elle est vraie est appelé une erreur de type I.
Dans le module complémentaire L'analyse des données offert outil pratique générer nombres aléatoires, permettant à l'utilisateur de spécifier la forme souhaitée de la distribution (par exemple, Normale, Binomiale ou Poisson), ainsi que la moyenne et l'écart type.
Différences entre les fonctions de la famille STUDENT.DIST(). Commençant par Versions Excel 2010, trois formes différentes de la fonction sont disponibles qui renvoient la proportion de la distribution à gauche et/ou à droite d'une valeur de test t donnée. La fonction STUDENT.DIST() renvoie la fraction de l'aire sous la courbe de distribution à gauche de la valeur du test t que vous spécifiez. Supposons que vous ayez 36 observations, donc le nombre de degrés de liberté pour l'analyse est de 34 et la valeur du test t = 1,69. Dans ce cas la formule
ÉTUDIANT.DIST(+1.69,34,TRUE)
renvoie la valeur 0,05, soit 5 % (Figure 21). Le troisième argument de la fonction STUDENT.DIST() peut être VRAI ou FAUX. Si la valeur est TRUE, la fonction renvoie l'aire cumulée sous la courbe à gauche du test t spécifié, exprimée sous forme de proportion. S'il est FAUX, la fonction renvoie la hauteur relative de la courbe au point correspondant au test t. D'autres versions de la fonction STUDENT.DIST() - STUDENT.DIST.PH() et STUDENT.DIST.2X() - prennent uniquement la valeur du test t et le nombre de degrés de liberté comme arguments et ne nécessitent pas d'en spécifier un troisième. argument.
Riz. 21. La zone ombrée la plus foncée dans la queue gauche de la distribution correspond à la proportion de surface sous la courbe à gauche d'une grande valeur positive du test t.
Pour déterminer l'aire à droite du test t, utilisez l'une des formules :
1 — STIODENT.DIST (1, 69;34;VRAI)
ÉTUDIANT.DIST.PH(1.69;34)
L'aire entière sous la courbe doit être de 100 %, donc en soustrayant de 1 la fraction de l'aire à gauche de la valeur du test t que la fonction renvoie donne la fraction de l'aire à droite de la valeur du test t. Vous préférerez peut-être obtenir directement la fraction de surface qui vous intéresse à l'aide de la fonction STUDENT.DIST.PH(), où PH désigne la queue droite de la distribution (Fig. 22).
Riz. Région alpha 22. 5% pour test directionnel
L'utilisation des fonctions STUDENT.DIST() ou STUDENT.DIST.PH() implique que vous ayez choisi une hypothèse de travail directionnelle. L'hypothèse de travail directionnelle combinée au réglage de la valeur alpha à 5 % signifie que vous placez les 5 % dans la queue droite des distributions. Vous n’aurez à rejeter l’hypothèse nulle que si la probabilité de la valeur du test t que vous obtenez est de 5 % ou moins. Les hypothèses directionnelles aboutissent généralement à des tests statistiques plus sensibles (cette plus grande sensibilité est également appelée plus grande puissance statistique).
Dans un test non dirigé, la valeur alpha reste au même niveau de 5 %, mais la distribution sera différente. Étant donné qu’il faut tenir compte de deux résultats, la probabilité d’un faux positif doit être répartie entre les deux queues de la distribution. Il est généralement admis de répartir cette probabilité de manière égale (Fig. 23).
En utilisant la même valeur de test t obtenue et le même nombre de degrés de liberté que dans l'exemple précédent, utilisez la formule
ÉTUDIANT.DIST.2Х(1.69;34)
Sans raison particulière, la fonction STUDENT.DIST.2X() renvoie le code d'erreur #NUM ! si elle reçoit une valeur de test t négative comme premier argument.
Si les échantillons contiennent des quantités de données différentes, utilisez le test t à deux échantillons avec différentes variances inclus dans le package. L'analyse des données.
Chapitre 7 : Utiliser la régression pour tester les différences entre les moyennes de groupe
Les variables qui apparaissaient auparavant sous le nom de variables prédictives seront appelées variables de résultat dans ce chapitre, et le terme variables factorielles sera utilisé à la place du terme variables prédictives.
L'approche la plus simple pour coder une variable nominale est codage factice(Fig. 24).
Riz. 24. Analyse de régression basée sur un codage factice
Lorsque vous utilisez un codage factice de quelque nature que ce soit, les règles suivantes doivent être suivies :
- Le nombre de colonnes réservées aux nouvelles données doit être égal au nombre de niveaux de facteurs moins
- Chaque vecteur représente un niveau de facteur.
- Les sujets d'un des niveaux, qui constitue souvent le groupe témoin, sont codés 0 dans tous les vecteurs.
La formule dans les cellules F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) renvoie des statistiques de régression. A titre de comparaison, sur la Fig. La figure 24 montre les résultats de l'ANOVA traditionnelle renvoyés par l'outil. ANOVA unidirectionnelle modules complémentaires L'analyse des données.
Codage des effets. Dans un autre type de codage appelé codage des effets, La moyenne de chaque groupe est comparée à la moyenne des moyennes du groupe. Cet aspect du codage d'effet est dû à l'utilisation de -1 au lieu de 0 comme code pour le groupe, qui reçoit le même code dans tous les vecteurs de code (Figure 25).
Riz. 25. Codage des effets
Lorsqu'un codage factice est utilisé, la valeur constante renvoyée par LINEST() est la moyenne du groupe auquel aucun code n'est attribué dans tous les vecteurs (généralement le groupe témoin). Dans le cas du codage des effets, la constante est égale à la moyenne globale (cellule J2).
Général modèle linéaire - manière utile conceptualisation des composantes de la valeur de la variable résultante :
Y ij = μ + α j + ε ij
L'utilisation de lettres grecques dans cette formule au lieu de lettres latines souligne le fait qu'elle fait référence à la population à partir de laquelle les échantillons sont tirés, mais elle peut être réécrite pour indiquer qu'elle fait référence à des échantillons tirés d'une population donnée :
Oui ij = Y̅ + a j + e ij
L'idée est que chaque observation Y ij peut être considérée comme la somme des trois composantes suivantes : la moyenne générale, μ ; effet du traitement j, et j ; valeur e ij, qui représente l'écart de l'indicateur quantitatif individuel Y ij par rapport à la valeur combinée de la moyenne générale et jième effet traitement (Fig. 26). Le but de l'équation de régression est de minimiser la somme des carrés des résidus.
Riz. 26. Observations décomposées en composantes d'un modèle linéaire général
Analyse factorielle. Si la relation entre la variable de résultat et deux ou plusieurs facteurs est étudiée simultanément, alors dans ce cas, nous parlons d'utiliser l'analyse factorielle. L'ajout d'un ou plusieurs facteurs à une ANOVA unidirectionnelle peut augmenter la puissance statistique. Dans l'analyse unidirectionnelle de la variance, la variance de la variable de résultat qui ne peut être attribuée à un facteur est incluse dans le carré moyen résiduel. Mais il se pourrait bien que cette variation soit liée à un autre facteur. Cette variation peut alors être supprimée de l'erreur quadratique moyenne, dont une diminution entraîne une augmentation des valeurs du test F, et donc une augmentation de la puissance statistique du test. Superstructure L'analyse des données comprend un outil qui traite deux facteurs simultanément (Fig. 27).
Riz. 27. Outil Analyse de variance bidirectionnelle avec répétitions du package d'analyse
L'outil ANOVA utilisé dans cette figure est utile car il renvoie la moyenne et la variance de la variable de résultat, ainsi que la contre-valeur, pour chaque groupe inclus dans le plan. Dans la table Analyse de variance affiche deux paramètres non présents dans la sortie de la version à facteur unique de l'outil ANOVA. Faites attention aux sources de variation Échantillon Et Colonnes aux lignes 27 et 28. Source de variation Colonnes fait référence au genre. Source de variation Échantillon fait référence à toute variable dont les valeurs occupent diverses cordes. En figue. 27 valeurs pour le groupe KursLech1 se trouvent dans les lignes 2 à 6, le groupe KursLech2 est dans les lignes 7 à 11 et le groupe KursLechZ est dans les lignes 12 à 16.
Le point principal est que les deux facteurs, Sexe (étiquette Colonnes dans la cellule E28) et Traitement (étiquette Échantillon dans la cellule E27), sont inclus dans le tableau ANOVA en tant que sources de variation. Les moyens des hommes sont différents de ceux des femmes, ce qui crée une source de variation. Les moyennes des trois traitements diffèrent également, ce qui constitue une autre source de variation. Il existe également une troisième source, Interaction, qui fait référence à l'effet combiné des variables Genre et Traitement.
Chapitre 8. Analyse de la covariance
L'analyse de covariance, ou ANCOVA (Analyse de covariation), réduit les biais et augmente la puissance statistique. Permettez-moi de vous rappeler que l'un des moyens d'évaluer la fiabilité d'une équation de régression consiste à effectuer des tests F :
F = MS Régression/MS Résidu
où MS (Mean Square) est le carré moyen et les indices de régression et de résidu indiquent respectivement les composantes de régression et de résidu. MS résiduel est calculé à l'aide de la formule :
MS résiduel = SS résiduel / df résiduel
où SS (Somme des carrés) est la somme des carrés et df est le nombre de degrés de liberté. Lorsque vous ajoutez une covariance à une équation de régression, une partie de la somme totale des carrés n'est pas incluse dans SS ResiduaI, mais dans SS Regression. Cela conduit à une diminution des SS résiduels, et donc des MS résiduels. Plus le MS résiduel est petit, plus le test F est grand et plus vous avez de chances de rejeter l'hypothèse nulle d'absence de différence entre les moyennes. En conséquence, vous redistribuez la variabilité de la variable de résultat. En ANOVA, lorsque la covariance n’est pas prise en compte, la variabilité devient une erreur. Mais dans ANCOVA, une partie de la variabilité précédemment attribuée au terme d’erreur est attribuée à une covariable et devient partie de la régression SS.
Prenons un exemple dans lequel le même ensemble de données est analysé d'abord avec ANOVA puis avec ANCOVA (Figure 28).
Riz. 28. L'analyse ANOVA indique que les résultats obtenus à partir de l'équation de régression ne sont pas fiables
L'étude compare les effets relatifs de l'exercice physique, qui développe la force musculaire, et de l'exercice cognitif (faire des mots croisés), qui stimule l'activité cérébrale. Les sujets ont été répartis au hasard en deux groupes afin que les deux groupes soient exposés aux mêmes conditions au début de l'expérience. Après trois mois, les performances cognitives des sujets ont été mesurées. Les résultats de ces mesures sont présentés dans la colonne B.
La plage A2:C21 contient les données source transmises à la fonction LINEST() pour effectuer une analyse à l'aide du codage d'effets. Les résultats de la fonction LINEST() sont donnés dans la plage E2:F6, où la cellule E2 affiche le coefficient de régression associé au vecteur d'impact. La cellule E8 contient le test t = 0,93 et la cellule E9 teste la fiabilité de ce test t. La valeur contenue dans la cellule E9 indique que la probabilité de rencontrer la différence entre les moyennes de groupe observée dans cette expérience est de 36 % si les moyennes de groupe sont égales dans la population. Rares sont ceux qui considèrent ce résultat comme statistiquement significatif.
En figue. La figure 29 montre ce qui se passe lorsque vous ajoutez une covariable à l'analyse. Dans ce cas, j'ai ajouté l'âge de chaque sujet à l'ensemble de données. Le coefficient de détermination R 2 pour l'équation de régression qui utilise la covariable est de 0,80 (cellule F4). La valeur R 2 dans la plage F15:G19, dans laquelle j'ai répliqué les résultats de l'ANOVA obtenus sans la covariable, n'est que de 0,05 (cellule F17). Par conséquent, une équation de régression qui inclut la covariable prédit les valeurs de la variable Cognitive Score avec beaucoup plus de précision que l'utilisation du seul vecteur d'impact. Pour l'ANCOVA, la probabilité d'obtenir par hasard la valeur du test F affichée dans la cellule F5 est inférieure à 0,01 %.
Riz. 29. ANCOVA ramène une image complètement différente
28 Octobre
Bonjour, chers lecteurs du blog ! Aujourd'hui, nous parlerons de régressions non linéaires. Solution régressions linéaires peut être consulté via LIEN.
Cette méthode principalement utilisé dans modélisation économique et les prévisions. Son objectif est d'observer et d'identifier les dépendances entre deux indicateurs.
Les principaux types de régressions non linéaires sont :
- polynôme (quadratique, cubique);
- hyperbolique;
- calme;
- démonstratif;
- logarithmique
Diverses combinaisons peuvent également être utilisées. Par exemple, pour l'analyse de séries chronologiques dans les études bancaires, d'assurance et démographiques, la courbe de Gompzer est utilisée, qui est un type de régression logarithmique.
Dans la prévision par régressions non linéaires, l'essentiel est de connaître le coefficient de corrélation, qui nous montrera s'il existe ou non une relation étroite entre deux paramètres. En règle générale, si le coefficient de corrélation est proche de 1, alors il existe un lien et la prévision sera assez précise. Un de plus élément important les régressions non linéaires sont l'erreur relative moyenne ( UN ), si c'est dans l'intervalle<8…10%, значит модель достаточно точна.
C'est ici que nous terminerons probablement le bloc théorique et passerons aux calculs pratiques.
Nous avons un tableau des ventes de voitures sur une période de 15 ans (notons-le X), le nombre d'étapes de mesure sera l'argument n, nous avons aussi des revenus pour ces périodes (notons-le Y), nous devons prédire quoi les revenus seront dans le futur. Construisons le tableau suivant :
Pour l’étude, nous devrons résoudre l’équation (dépendance de Y à X) : y=ax 2 +bx+c+e. Il s’agit d’une régression quadratique par paires. Dans ce cas, nous appliquons la méthode des moindres carrés pour découvrir les arguments inconnus - a, b, c. Cela conduira à un système d’équations algébriques de la forme :
Pour résoudre ce système, nous utiliserons par exemple la méthode de Cramer. On voit que les sommes incluses dans le système sont des coefficients pour les inconnues. Pour les calculer, nous ajouterons plusieurs colonnes au tableau (D,E,F,G,H) et signerons selon le sens des calculs - dans la colonne D nous placerons x au carré, en E nous le cuberons, en F nous allons multiplier les exposants x et y, dans H nous mettons x au carré et multiplions par y.
Vous obtiendrez un tableau du formulaire rempli avec les éléments nécessaires pour résoudre l'équation.
Formons une matrice UN système constitué de coefficients pour les inconnues sur les côtés gauches des équations. Plaçons-le dans la cellule A22 et appelons-le " UNE=". Nous suivons le système d'équations que nous avons choisi pour résoudre la régression.
Autrement dit, dans la cellule B21, nous devons placer la somme de la colonne où nous avons élevé l'indicateur X à la quatrième puissance - F17. Faisons simplement référence à la cellule - "=F17". Ensuite, nous avons besoin de la somme de la colonne où X a été cubique - E17, puis nous suivons strictement le système. Ainsi, nous devrons remplir toute la matrice.
Conformément à l'algorithme de Cramer, nous taperons une matrice A1, similaire à A, dans laquelle, à la place des éléments de la première colonne, il faudra placer les éléments des côtés droits des équations système. Autrement dit, la somme de la colonne X au carré multipliée par Y, la somme de la colonne XY et la somme de la colonne Y.
Nous aurons également besoin de deux autres matrices - appelons-les A2 et A3 dans lesquelles les deuxième et troisième colonnes seront constituées des coefficients des membres droits des équations. L'image sera comme ça.
Suivant l'algorithme choisi, nous devrons calculer les valeurs des déterminants (déterminants, D) des matrices résultantes. Utilisons la formule MOPRED. Nous placerons les résultats dans les cellules J21:K24.
On calculera les coefficients de l'équation selon Cramer dans les cellules en face des déterminants correspondants à l'aide de la formule : un(dans la cellule M22) - « =K22/K21 » ; b(dans la cellule M23) - « =K23/K21 » ; Avec(dans la cellule M24) - « =K24/K21 ».
Nous obtenons notre équation souhaitée de régression quadratique appariée :
y=-0,074x2 +2,151x+6,523
Évaluons l'étroitesse de la relation linéaire à l'aide de l'indice de corrélation.
Pour calculer, ajoutez une colonne J supplémentaire au tableau (appelons-la y*). Le calcul sera le suivant (d'après l'équation de régression que nous avons obtenue) - "= 22 $ M$*B2*B2+$M$23*B2+$M$24." Plaçons-le dans la cellule J2. Il ne reste plus qu'à faire glisser le marqueur de remplissage automatique vers la cellule J16.
Pour calculer les sommes (moyenne Y-Y) 2, ajoutez les colonnes K et L au tableau avec les formules correspondantes. Nous calculons la moyenne de la colonne Y à l'aide de la fonction MOYENNE.
Dans la cellule K25, nous placerons la formule de calcul de l'indice de corrélation - « =ROOT(1-(K17/L17)) ».
Nous voyons que la valeur de 0,959 est très proche de 1, ce qui signifie qu’il existe une relation non linéaire étroite entre les ventes et les années.
Il reste à évaluer la qualité d'ajustement de l'équation de régression quadratique résultante (indice de détermination). Il est calculé à l'aide de la formule de l'indice de corrélation au carré. Autrement dit, la formule dans la cellule K26 sera très simple : « =K25*K25 ».
Le coefficient de 0,920 est proche de 1, ce qui indique une grande qualité d'ajustement.
La dernière étape consiste à calculer l'erreur relative. Ajoutons une colonne et y entrons la formule : « =ABS((C2-J2)/C2), ABS - module, valeur absolue. Dessinez le marqueur vers le bas et dans la cellule M18, affichez la valeur moyenne (AVERAGE), attribuez le format de pourcentage aux cellules. Le résultat obtenu - 7,79% se situe dans les valeurs d'erreur acceptables<8…10%. Значит вычисления достаточно точны.
Si le besoin s’en fait sentir, nous pouvons construire un graphique en utilisant les valeurs obtenues.
Un fichier d'exemple est joint - LIEN !
Catégories :// du 28/10/2017Régression dans Excel
Le traitement des données statistiques peut également être effectué à l'aide du module complémentaire Analysis Package dans le sous-élément de menu « Service ». Dans Excel 2003, si vous ouvrez SERVICE, nous ne trouvons pas l'onglet L'ANALYSE DES DONNÉES, puis cliquez sur le bouton gauche de la souris pour ouvrir l'onglet SUPERSTRUCTURES et en face du point FORFAIT ANALYSE Cliquez sur le bouton gauche de la souris pour mettre une coche (Fig. 17).
Riz. 17. Fenêtre SUPERSTRUCTURES
Après cela dans le menu SERVICE l'onglet apparaît L'ANALYSE DES DONNÉES.
Dans Excel 2007 à installer FORFAIT ANALYSE vous devez cliquer sur le bouton BUREAU dans le coin supérieur gauche de la feuille (Fig. 18a). Ensuite, cliquez sur le bouton PARAMÈTRES EXCEL. Dans la fenêtre qui apparaît PARAMÈTRES EXCEL clic gauche sur l'élément SUPERSTRUCTURES et dans le côté droit de la liste déroulante, sélectionnez l'élément FORFAIT ANALYSE. Cliquez ensuite sur D'ACCORD.
Riz. 18.Installation FORFAIT ANALYSE dans Excel 2007
Pour installer le package d'analyse, cliquez sur le bouton ALLER, situé au bas de la fenêtre ouverte. Une fenêtre apparaîtra comme le montre la Fig. 12. Mettez une coche devant FORFAIT ANALYSE. Dans l'onglet DONNÉES un bouton apparaîtra L'ANALYSE DES DONNÉES(Fig. 19).
Parmi les éléments suggérés, sélectionnez l'élément " RÉGRESSION" et cliquez dessus avec le bouton gauche de la souris. Ensuite, cliquez sur OK.
Une fenêtre apparaîtra comme le montre la Fig. 21
Outil d'analyse " RÉGRESSION» est utilisé pour ajuster un graphique à un ensemble d'observations en utilisant la méthode des moindres carrés. La régression est utilisée pour analyser l'effet sur une seule variable dépendante des valeurs d'une ou plusieurs variables indépendantes. Par exemple, plusieurs facteurs influencent la performance sportive d’un athlète, notamment l’âge, la taille et le poids. Il est possible de calculer dans quelle mesure chacun de ces trois facteurs influence la performance d'un athlète, puis d'utiliser ces données pour prédire la performance d'un autre athlète.
L'outil Régression utilise la fonction LIGNE.
Boîte de dialogue RÉGRESSION
Étiquettes Cochez la case si la première ligne ou la première colonne de la plage d'entrée contient des en-têtes. Décochez cette case s'il n'y a aucun en-tête. Dans ce cas, des en-têtes appropriés pour les données du tableau de sortie seront créés automatiquement.
Niveau de fiabilité Cochez la case pour inclure un niveau supplémentaire dans le tableau récapitulatif des résultats. Dans le champ approprié, saisissez le niveau de confiance que vous souhaitez appliquer, en plus du niveau par défaut de 95 %.
Constante - zéro Cochez la case pour forcer la droite de régression à passer par l'origine.
Plage de sortie Entrez la référence à la cellule supérieure gauche de la plage de sortie. Fournissez au moins sept colonnes pour le tableau récapitulatif des résultats, qui comprendront : les résultats de l'ANOVA, les coefficients, l'erreur type du calcul Y, les écarts types, le nombre d'observations, les erreurs types pour les coefficients.
Nouvelle feuille de calcul Sélectionnez cette option pour ouvrir une nouvelle feuille de calcul dans le classeur et coller les résultats de l'analyse, en commençant dans la cellule A1. Si nécessaire, saisissez un nom pour la nouvelle feuille dans le champ situé en face du bouton radio correspondant.
Nouveau classeur Sélectionnez cette option pour créer un nouveau classeur avec les résultats ajoutés à une nouvelle feuille de calcul.
Résidus Cochez la case pour inclure les résidus dans la table de sortie.
Résidus standardisés Cochez la case pour inclure les résidus standardisés dans le tableau de sortie.
Tracé des résidus Cochez la case pour tracer les résidus pour chaque variable indépendante.
Ajuster le tracé Cochez la case pour tracer les valeurs prévues par rapport aux valeurs observées.
Diagramme de probabilité normale Cochez la case pour tracer un graphique de probabilité normale.
Fonction LIGNE
Pour effectuer les calculs, sélectionnez avec le curseur la cellule dans laquelle on souhaite afficher la valeur moyenne et appuyez sur la touche = du clavier. Ensuite, dans le champ Nom, indiquez la fonction souhaitée, par exemple MOYENNE(Fig. 22).
Riz. 22 Recherche de fonctions dans Excel 2003
Si sur le terrain NOM le nom de la fonction n'apparaît pas, puis faites un clic gauche sur le triangle à côté du champ, après quoi une fenêtre avec une liste de fonctions apparaîtra. Si cette fonction n'est pas dans la liste, faites un clic gauche sur l'élément de la liste AUTRES FONCTIONS, Une boîte de dialogue apparaîtra MAÎTRE DE FONCTION, dans lequel, par défilement vertical, sélectionnez la fonction souhaitée, mettez-la en surbrillance avec le curseur et cliquez sur D'ACCORD(Fig. 23).
Riz. 23. Assistant de fonction
Pour rechercher une fonction dans Excel 2007, n'importe quel onglet peut être ouvert dans le menu ; puis pour effectuer des calculs, sélectionnez avec le curseur la cellule dans laquelle nous voulons afficher la valeur moyenne et appuyez sur la touche = du clavier. Ensuite, dans le champ Nom, spécifiez la fonction MOYENNE. La fenêtre de calcul de la fonction est similaire à celle affichée dans Excel 2003.
Vous pouvez également sélectionner l'onglet Formules et faire un clic gauche sur le bouton dans le menu « FONCTION D'INSERTION" (Fig. 24), une fenêtre apparaîtra MAÎTRE DE FONCTION, dont l'apparence est similaire à Excel 2003. Également dans le menu, vous pouvez immédiatement sélectionner une catégorie de fonctions (récemment utilisées, financières, logiques, texte, date et heure, mathématiques, autres fonctions) dans lesquelles nous rechercherons le souhaité fonction.
Riz. 24 Sélection d'une fonction dans Excel 2007
Fonction LIGNE calcule les statistiques d'une série à l'aide de la méthode des moindres carrés pour calculer la ligne droite qui se rapproche le mieux des données disponibles, puis renvoie un tableau qui décrit la ligne droite résultante. Vous pouvez également combiner la fonction LIGNE avec d'autres fonctions pour calculer d'autres types de modèles linéaires à paramètres inconnus (dont les paramètres inconnus sont linéaires), notamment les séries polynomiales, logarithmiques, exponentielles et entières. Étant donné qu'un tableau de valeurs est renvoyé, la fonction doit être spécifiée sous forme de formule matricielle.
L'équation d'une droite est :
(en cas de plusieurs plages de valeurs x),
où la valeur dépendante y est fonction de la valeur indépendante x, les m valeurs sont les coefficients correspondant à chaque variable indépendante x, et b est une constante. Notez que y, x et m peuvent être des vecteurs. Fonction LIGNE renvoie un tableau 
. LIGNE peut également renvoyer des statistiques de régression supplémentaires.
LIGNE(known_values_y ; known_values_x ; const ; statistiques)
Known_y_values est l'ensemble des valeurs y déjà connues pour la relation.
Si le tableau known_y_values a une colonne, alors chaque colonne du tableau known_x_values est traitée comme une variable distincte.
Si le tableau known_y_values a une ligne, alors chaque ligne du tableau known_x_values est traitée comme une variable distincte.
Les valeurs x connues sont un ensemble facultatif de valeurs x déjà connues pour la relation.
Le tableau known_x_values peut contenir un ou plusieurs ensembles de variables. Si une seule variable est utilisée, alors les tableaux known_y_values et known_x_values peuvent avoir n'importe quelle forme - à condition qu'ils aient la même dimension. Si plusieurs variables sont utilisées, alors known_y_values doit être un vecteur (c'est-à-dire un intervalle d'une ligne de haut ou d'une colonne de large).
Si array_known_x_values est omis, alors le tableau (1;2;3;...) est supposé avoir la même taille que array_known_values_y.
Const est une valeur booléenne qui spécifie si la constante b doit être égale à 0.
Si l'argument "const" est VRAI ou omis, alors la constante b est évaluée comme d'habitude.
Si l'argument « const » est FAUX, alors la valeur de b est fixée à 0 et les valeurs de m sont sélectionnées de telle manière que la relation soit satisfaite.
Statistiques : valeur booléenne qui indique si des statistiques de régression supplémentaires doivent être renvoyées.
Si les statistiques sont VRAI, LINEST renvoie des statistiques de régression supplémentaires. Le tableau renvoyé ressemblera à ceci : (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).
Si les statistiques sont FALSE ou omises, LINEST renvoie uniquement les coefficients m et la constante b.
Statistiques de régression supplémentaires.
La figure ci-dessous montre l'ordre dans lequel les statistiques de régression supplémentaires sont renvoyées.
Remarques:
Toute ligne droite peut être décrite par sa pente et son intersection avec l'axe des y :
Pente (m) : Pour déterminer la pente d'une ligne, généralement notée m, vous prenez deux points sur la ligne et ; la pente sera égale à 
.
Interception Y (b) : L'ordonnée à l'origine d'une ligne, généralement désignée par b, est la valeur y pour le point auquel la ligne coupe l'axe y.
L'équation de la droite a la forme . Si les valeurs de m et b sont connues, alors n'importe quel point de la ligne peut être calculé en remplaçant les valeurs de y ou x dans l'équation. Vous pouvez également utiliser la fonction TENDANCE.
S'il n'y a qu'une seule variable indépendante x, vous pouvez obtenir la pente et l'ordonnée à l'origine directement en utilisant les formules suivantes :
Pente : INDEX(LINEST(known_y_values; known_x_values); 1)
Interception Y : INDEX(LINEST(known_y_values ; known_x_values); 2)
La précision de l'approximation utilisant la ligne droite calculée par la fonction LINEST dépend du degré de dispersion des données. Plus les données sont proches d'une ligne droite, plus le modèle utilisé par la fonction LINEST est précis. La fonction LINEST utilise les moindres carrés pour déterminer le meilleur ajustement aux données. Lorsqu'il n'y a qu'une seule variable indépendante x, m et b sont calculés à l'aide des formules suivantes :
où x et y sont des moyennes d'échantillon, par exemple x = MOYENNE (x_connus) et y = MOYENNE (y_connus).
Les fonctions d'ajustement LINEST et LGRFPRIBL peuvent calculer la ligne droite ou la courbe exponentielle qui correspond le mieux aux données. Cependant, ils ne répondent pas à la question de savoir lequel des deux résultats est le plus approprié pour résoudre le problème. Vous pouvez également évaluer la fonction TREND(known_y_values; known_x_values) pour une ligne droite ou la fonction GROWTH(known_y_values; known_x_values) pour une courbe exponentielle. Ces fonctions, à moins que de nouvelles valeurs x ne soient spécifiées, renvoient un tableau de valeurs y calculées pour les valeurs x réelles le long d'une ligne ou d'une courbe. Vous pourrez ensuite comparer les valeurs calculées avec les valeurs réelles. Vous pouvez également créer des graphiques pour une comparaison visuelle.
Lors de l'exécution d'une analyse de régression, Microsoft Excel calcule, pour chaque point, le carré de la différence entre la valeur y prévue et la valeur y réelle. La somme de ces carrés des différences est appelée somme résiduelle des carrés (ssresid). Microsoft Excel calcule ensuite la somme totale des carrés (sstotal). Si const = TRUE ou si la valeur de cet argument n'est pas précisée, la somme totale des carrés sera égale à la somme des carrés des différences entre les valeurs y réelles et les valeurs y moyennes. Lorsque const = FALSE, la somme totale des carrés sera égale à la somme des carrés des valeurs y réelles (sans soustraire la valeur y moyenne de la valeur y partielle). La somme des carrés de régression peut alors être calculée comme suit : ssreg = sstotal - ssresid. Plus la somme des carrés résiduelle est petite, plus la valeur du coefficient de détermination r2 est grande, ce qui montre dans quelle mesure l'équation obtenue par analyse de régression explique les relations entre les variables. Le coefficient r2 est égal à ssreg/sstotal.
Dans certains cas, une ou plusieurs colonnes X (que les valeurs Y et X soient dans les colonnes) n'ont aucune valeur prédicative supplémentaire dans les autres colonnes X. En d'autres termes, la suppression d'une ou plusieurs colonnes X peut entraîner des valeurs Y calculées avec la même précision. Dans ce cas, les colonnes X redondantes seront exclues du modèle de régression. Ce phénomène est appelé « colinéarité » car les colonnes redondantes de X peuvent être représentées comme la somme de plusieurs colonnes non redondantes. La fonction LINEST vérifie la colinéarité et supprime toutes les colonnes X redondantes du modèle de régression si elle les détecte. Les colonnes X supprimées peuvent être identifiées dans la sortie LINEST par un facteur de 0 et une valeur se de 0. La suppression d'une ou plusieurs colonnes comme redondantes modifie la valeur de df car elle dépend du nombre de colonnes X réellement utilisées à des fins prédictives. Pour plus d'informations sur le calcul de df, voir l'exemple 4 ci-dessous. Lorsque df change en raison de la suppression des colonnes redondantes, les valeurs de sey et F changent également. Il n'est pas recommandé d'utiliser souvent la colinéarité. Cependant, il doit être utilisé si certaines colonnes X contiennent 0 ou 1 comme indicateur indiquant si le sujet de l'expérience appartient à un groupe distinct. Si const = TRUE ou si aucune valeur pour cet argument n'est spécifiée, LINEST insère une colonne X supplémentaire pour modéliser le point d'intersection. S'il existe une colonne avec des valeurs de 1 pour les hommes et 0 pour les femmes, et qu'il existe une colonne avec des valeurs de 1 pour les femmes et 0 pour les hommes, alors la dernière colonne est supprimée car ses valeurs peuvent être obtenues. de la colonne « indicateur masculin ».
Le calcul de df pour les cas où X colonnes ne sont pas supprimées du modèle en raison de la colinéarité se produit comme suit : s'il y a k colonnes connues_x et que la valeur const = VRAI ou non spécifiée, alors df = n – k – 1. Si const = FAUX, alors df = n - k. Dans les deux cas, la suppression des colonnes X en raison de la colinéarité augmente la valeur df de 1.
Les formules qui renvoient des tableaux doivent être saisies sous forme de formules matricielles.
Lorsque vous entrez un tableau de constantes comme argument, par exemple known_x_values, vous devez utiliser un point-virgule pour séparer les valeurs sur la même ligne et deux points pour séparer les lignes. Les caractères séparateurs peuvent varier en fonction des paramètres de la fenêtre Langue et paramètres du Panneau de configuration.
Il convient de noter que les valeurs y prédites par l'équation de régression peuvent ne pas être correctes si elles se situent en dehors de la plage des valeurs y utilisées pour définir l'équation.
Algorithme de base utilisé dans la fonction LIGNE, diffère de l'algorithme de la fonction principale INCLINAISON Et SEGMENT DE LIGNE. La différence entre les algorithmes peut conduire à des résultats différents avec des données incertaines et colinéaires. Par exemple, si les points de données de l'argument known_y_values sont 0 et que les points de données de l'argument known_x_values sont 1, alors :
Fonction LIGNE renvoie une valeur égale à 0. Algorithme de fonction LIGNE est utilisé pour renvoyer des valeurs appropriées pour les données colinéaires, et dans ce cas, au moins une réponse peut être trouvée.
Les fonctions SLOPE et LINE renvoient l'erreur #DIV/0!. L'algorithme des fonctions PENTE et INTERCEPT permet de trouver une seule réponse, mais dans ce cas il peut y en avoir plusieurs.
En plus de calculer des statistiques pour d'autres types de régression, LINEST peut être utilisé pour calculer des plages pour d'autres types de régression en entrant les fonctions des variables x et y sous forme de séries de variables x et y pour LINEST. Par exemple, la formule suivante :
LIGNEST(valeurs_y, valeurs_x^COLONNE($A:$C))
fonctionne en ayant une colonne de valeurs Y et une colonne de valeurs X pour calculer une approximation cubique (polynôme du 3ème degré) de la forme suivante :
La formule peut être modifiée pour calculer d'autres types de régression, mais dans certains cas, les valeurs de sortie et d'autres statistiques peuvent devoir être ajustées.
Le package MS Excel vous permet d'effectuer la plupart du travail très rapidement lors de la construction d'une équation de régression linéaire. Il est important de comprendre comment interpréter les résultats obtenus. Pour construire un modèle de régression, vous devez sélectionner Outils\Analyse des données\Régression (dans Excel 2007, ce mode se trouve dans le bloc Données/Analyse des données/Régression). Copiez ensuite les résultats dans un bloc pour analyse.
