Comment créer un modèle de régression linéaire bref dans Excel?

Entrez sans guillemets la formule "=(B3-X_BAR)*(C3-Y_BAR)" — Sélectionnez la plage de cellules H3:H22 et avec H3 comme cellule active en surbrillance, entrez sans guillemets la formule "=(B3-X_BAR)*(C3-Y_BAR)".

Excel fait du bon travail avec les statistiques, ou ils demandent à un tiers d'écrire leur module complémentaire, qui est disponible gratuitement. Dans cet article, vous apprendrez à projeter une tendance à l'aide d'Excel et de l' analyse de régression linéaire.

Partie 1 sur 3: le tutoriel

1

Ouvrez un nouveau classeur dans Excel et créez 3 feuilles de calcul: données, graphique et enregistrements. Enregistrez le classeur en tant que Régression linéaire - Brève leçon, ou quelque chose de similaire, dans un dossier de fichiers logique.
2

Définir les préférences: ouvrez Préférences dans le menu Excel. Paramètres recommandés: définissez Général sur R1C1 désactivé et affichez les 10 documents les plus récents; Modifier - définissez toutes les options principales sur cochées, à l'exception de la conversion automatique du système de date. Afficher le nombre de décimales = vide (pour les entiers préférés), conserver l'affichage des dates et définir 30 pour la coupure du 21e siècle; Affichage - affiche la barre de formule et la barre d'état, survolez pour les commentaires et tous les objets, Afficher le quadrillage et toutes les cases en dessous de celui-ci automatique ou coché; Graphique - affiche les noms des graphiques et les marqueurs de données en survol. Laissez le repos sans contrôle pour le moment; Calcul - Automatiquement et calc avant l'enregistrement, changement maximum 0,000000,000000,01 sans virgules car la recherche d'objectifs se fait beaucoup et enregistre les valeurs des liens externes et utilise le système 1904; Vérification des erreurs - tout vérifier; Enregistrer - enregistrez l'image d'aperçu avec les nouveaux fichiers et enregistrez la récupération automatique après 5 minutes; Ruban - tous cochés sauf Masquer les titres de groupe et Développeur.
3

Sélectionnez entre le 1 et le A dans le coin supérieur gauche de la feuille de calcul pour sélectionner la feuille entière et formatez l'alignement des cellules au centre horizontal et la taille de police 9 ou 10, ou tout ce que vous êtes à l'aise avec la visualisation.
4

Saisissez les en-têtes de colonne dans la ligne 1: B1: X; C1: O; D1: X. E1: Y F1: Y. Saisissez les en-têtes de colonne des lignes 2: A2: Étudiant; B2 SAT; C2: AMP; D2: VARIATION; E2: VARIATION; F2: TENDANCE, G2: Changement.
5

Modifier allez à la plage de cellules a3: A22, entrez 1, et faites Modifier les colonnes de la série Remplir la valeur de l'étape linéaire 1 OK, il y a 20 étudiants qui veulent connaître la tendance de leurs GPA dépendants étant donné la variable indépendante du score de l'examen SAT lors de la saisie d'un nouvelle université, c'est-à-dire que leurs notes sont susceptibles d'augmenter, de baisser ou de rester à peu près les mêmes?
6

Entrez les données pour répondre à cette question. En paires {x,y} comme indiqué dans l'image ci-dessous étape 22, ou comme suit: pour l'élève 1, {X,Y} = {935 pour X ou SAT, 2,2 pour Y ou GPA}; étudiant 2 {12603,1}; puis {11052,6}; {13203,3}; {14503,8}; {9602,2; {13603,2}; {9002,2}; {10202,2}; {13803,6}; {9402, 3}; {11902,8}; {10002,2}; {9452,3}; {9902,4}; {10002,2}; {10402,3}; {15703,9}; {15303,8}; {9802,4}.
7

Sélectionnez la cellule a23 et entrez MEAN. Saisissez la formule sans guillemets dans la cellule B23" = MOYENNE (B3:B22)" et copiez-la et collez-la dans la cellule C23. Sélectionnez la cellule C23 et insérez le nom Définissez le nom Y_Bar pour la cellule $C17€ Sélectionnez la cellule B23 et insérez le nom Définissez le nom X_Bar pour la cellule $B17€ Sélectionnez la plage de cellules B23:C23 et Formatez la couleur de police des cellules Rouge et Gras.
8

Modifier allez à la plage de cellules d3:d22 et avec la cellule d3 la cellule active et en surbrillance, entrez sans guillemets la formule "=(b3-x_bar)^2" et modifiez le remplissage. Modifier Aller à la plage de cellules E3:E22 et avec la cellule E3 la cellule active et en surbrillance, entrez sans guillemets la formule "=(C3-Y_BAR)^2" et Modifier Fill Down. Sélectionnez la plage de cellules D3:D23 et faites Format Cells Number Number Décimales 4.
9

Sélectionnez la cellule d23 et entrez sans guillemets la formule "=sum(d3:d22)/(20-1)" et copiez-la et collez-la dans la cellule e23. Sélectionnez la plage de cellules E3:E23 et faites Format Cells Number Number Décimales 6. Sélectionnez la cellule A24 et entrez VARIANCE et sélectionnez la cellule B24 et entrez la formule "=D23" et copiez-la et collez-la dans la cellule C24. Sélectionnez la plage de cellules B24:D25 et faites Format Cells Number Number Décimales 6.
10

Sélectionnez la cellule a25 et entrez STD DEV. (pour l'écart type) et sélectionnez la cellule B25 et entrez sans guillemets la formule "=STDEVPA(B3:B22)" et copiez-la et collez-la dans la cellule C25. Effectuez le formatage des cellules Nombre Nombre décimales 7 pour la plage de cellules sélectionnée B25:C25.
11

Sélectionnez la cellule a27 et entrez PREVISION et sélectionnez la cellule b27 et entrez sans guillemets la formule "=stdevpa(b3:b22)" qui est une formule matricielle, vous devez donc appuyer sur Ctrl+Maj+Entrée pour entrer correctement la formule matricielle avec crochets. Il s'agit de la projection de la moyenne cumulative d'un étudiant avec un SAT de 1290 et du reste de la population ou des données de l'échantillon. Il / elle peut s'attendre à un GPA de 3,170409192 une fois que vous formatez le nombre de cellules avec les décimales 9 (qui correspond exactement plus tard). Non pas qu'un GPA aussi précis soit nécessaire, mais le but est de prouver la formule, et cela nécessite une certaine précision.
12

Modifier allez à la plage de cellules f3:f22 et entrez la formule sans guillemets "=trend(c3:c22,b3:b22,,true)" qui est une formule matricielle, vous devez donc appuyer sur Ctrl+Maj+Entrée pour entrer correctement dans le tableau formule entre parenthèses. C'est la projection de l'AMP de la population étudiante compte tenu de leurs performances antérieures.
13

Modifiez, accédez à la plage de cellules g3: g22 et avec g3 la cellule active en surbrillance, entrez la formule sans guillemets "=f3-c3" et modifiez le remplissage. Do Format Cells Number Number Custom +0,0;-0,0;+0,0.

Entrez sans guillemets la formule "=(B3-X_BAR)^2" — Sélectionnez la plage de cellules I3:I22 et avec I3 comme cellule active en surbrillance, entrez sans guillemets la formule "=(B3-X_BAR)^2".

Partie 2 sur 3: tableaux explicatifs, schémas, photos

1

Créez le graphique (en fonction des données du didacticiel ci-dessus). Sélectionnez la plage de cellules B3:C23 et accédez au ruban (ou effectuez l'Assistant Graphique) et sélectionnez Graphiques, Tout, faites défiler jusqu'à Scatter, Marked Scatter.. Ensuite, modifiez, coupez ou copiez le nouveau graphique dans la feuille de calcul Graphiques. Faites la mise en page du graphique Titre du graphique - Titre au-dessus du graphique et remplissez-le avec "Régression de ligne - SAT vs GPA" (sans les guillemets). Sélectionnez Quadrillage Quadrillage vertical Quadrillage principal coché. Sélectionnez Titres de l'axe Titre de l'axe horizontal, Titre du titre sous l'axe et modifiez-le dans « Score SAT» (sans les guillemets). Sélectionner les titres des axes Titre de l'axe vertical, Titre horizontal et édition en "GPA" (sans les guillemets). Par convention, la variable indépendante x va sur l'axe horizontal inférieur et la variable dépendante y va sur l'axe vertical gauche.
2

Localisez le marqueur de données pour la moyenne à {1143,752,755} qui indiquera "série 1 point 1143,75" (1143,75, 2,8) lorsque vous le survolez ou avez cliqué dessus. Cliquez dessus et faites le menu Format, Style de marqueur de point de données Taille automatique 9, puis Couleur de remplissage du marqueur Rouge.
3

Revenez à la feuille de calcul des données pour déterminer la droite de régression de y' = mx + b, où m = la pente et b = l'ordonnée à l'origine. En regardant les données du graphique avec des valeurs basses inférieures à 10 à gauche et des valeurs d'environ 1000 en bas, on s'attendrait à une très légère pente décimale et à une ordonnée à l'origine proche de 0. Les graphiques à l'échelle peuvent parfois être trompeurs quant à la pente.
4

Sélectionnez la cellule g1 et entrez Y. Sélectionnez la cellule H1 et entrez m et copiez H1 et collez-la dans I1 via la commande c, sélectionnez I1 et la commande v. Sélectionnez la cellule H2 et entrez Numerator et sélectionnez la cellule I2 et entrez Denominator. Sélectionnez la plage de cellules H3:H22 et avec H3 comme cellule active en surbrillance, entrez sans guillemets la formule "=(B3-X_BAR)*(C3-Y_BAR)". Modifier Remplir vers le bas. Sélectionnez la plage de cellules I3:I22 et avec I3 comme cellule active en surbrillance, entrez sans guillemets la formule "=(B3-X_BAR)^2". Modifier Remplir vers le bas. Sélectionnez les colonnes H et I et formatez les cellules Nombre Nombre Décimales 1.
5

Sélectionnez la cellule h23 et entrez la formule sans guillemets "=sum(h3:h22)" et formatez la bordure des cellules en gras noir et copiez-la dans i23.
6

Sélectionnez la cellule h24 et entrez m et formatez la couleur de police des cellules en rouge. Copiez-le dans la cellule H25 et entrez b dans la cellule H25. Sélectionnez la cellule I24 et entrez sans guillemets la formule "=H23/I23", qui est la pente m, puis sélectionnez la cellule I25 et entrez la formule sans guillemets "=Y_BAR-I24*X_BAR", c'est-à-dire le à l'origine b = Y_Moyenne-m*X_Moyenne.
7

Copiez la cellule h25 et collez-la dans la cellule i26 et entrez dans i26 y' - Mx + b. Sélectionnez la cellule H27 et entrez Forecast, puis entrez la formule dans I27 sans guillemets "=I24*1290+I25". Votre réponse devrait être exactement égale à la réponse PRÉVUE dans B27 une fois que vous avez Format Cells Number Number Decimal Places 9.
8

Modifiez la copie de la cellule i26 dans la plage de cellules h29: i29. Dans la cellule H29, entrez la ligne X et dans la cellule I29, entrez la ligne Y. Entrez 800 dans la cellule H30 et 1600 dans la cellule H31. Sélectionnez la cellule I 27 et copiez sa formule dans la barre de formule - ne copiez pas la cellule et ne la collez pas - cela ne fonctionnera pas correctement. Sélectionnez la cellule I30 et collez dans la barre de formule d'édition la formule que vous venez de copier. Faites de même pour la cellule I31. Modifiez la formule de la cellule I31 pour lire "=I24*H31+I25" et appuyez sur Entrée, puis modifiez la formule de la cellule I30 dans la barre de formule pour lire "=I24*H30+I25" et appuyez sur Entrée. Le résultat de I30 devrait être 1,7786108729206 et le résultat de I31 devrait être 4,05093465957812 (ce qui, je le réalise, est supérieur à 4,0 GPA, mais nous créons une ligne de régression, donc cela n'a pas tellement d'importance).
9

Activez la feuille de calcul du graphique et cliquez dans le graphique et dans le menu, ajoutez des données au graphique et en réponse à la requête de plage, revenez à la feuille de calcul des données et sélectionnez la plage de cellules h30:i31. Maintenant le mien sort mal et je dois éditer la série. Pas grave. Sélectionnez le marqueur de données et dans la barre de formule, modifiez la série pour lire "=SERIES(,Sheet1!$H22€:$H23€,Sheet1!$I22€:$I230€)" et cliquez sur le marqueur de données à {8001,78} et définissez la ligne en rouge et le poids 0,75 pt, puis la couleur de remplissage du marqueur en rouge, puis la taille du point rond de style de marqueur 5.

Partie 3 sur 3: conseils utiles

1
Utilisez des articles d'aide lorsque vous suivez ce didacticiel:
- Voir l'article Comment créer un chemin de particules de spin en spirale ou une forme de collier ou une bordure sphérique pour une liste d'articles liés à Excel, à l' art géométrique et/ou trigonométrique, aux graphiques/diagrammes et à la formulation algébrique.
- Pour plus de tableaux et de graphiques artistiques, vous pouvez également cliquer sur Catégorie: images Microsoft Excel, Catégorie: mathématiques, Catégorie: feuilles de calcul ou Catégorie: graphiques pour afficher de nombreuses feuilles de calcul et graphiques Excel où la trigonométrie, la géométrie et le calcul ont été transformés en art, ou cliquez simplement sur la catégorie telle qu'elle apparaît dans la partie blanche en haut à droite de cette page, ou en bas à gauche de la page.

Entrez sans guillemets la formule "=(C3-Y_BAR)^2" — Modifier Aller à la plage de cellules E3:E22 et avec la cellule E3 la cellule active et en surbrillance, entrez sans guillemets la formule "=(C3-Y_BAR)^2" et Modifier Fill Down.

Mises en garde

ERREURS: Si vous avez des erreurs ou des valeurs d'erreur, soit la feuille est incomplète et nécessite une entrée supplémentaire ou des tables de recherche pour les variables critiques ou peut-être que vous avez fait une erreur quelque part le long de la ligne. Si les instructions sont terminées et qu'il y a encore des erreurs, sélectionnez la cellule qui a la valeur d'erreur la plus à gauche et la plus en haut en premier. Recherchez une faute de frappe dans une formule ou des parenthèses sans correspondance. Peut-être qu'un nom défini est erroné - ils doivent être entrés dans les formules exactement comme ils ont été définis. Insérez le nom défini pour vérifier. Si vous avez DIV/0!, je n'en ai pas, alors recherchez une variable qui n'a peut-être pas été renseignée avec une valeur. Dans tous les cas, ce que vous voulez faire est de sélectionner la cellule avec l'erreur, et après avoir vérifié toutes ces erreurs typiques, faites Outils Auditer les précédents de trace et/ou Tracer l'erreur.Si la correction de toutes les erreurs les plus à gauche ne corrige pas le reste de vos erreurs sur votre feuille de calcul, vous devrez peut-être le faire à la dure, du bas à droite vers le haut puis vers la gauche; c'est le moyen lent mais sûr de corriger toutes les erreurs.
De plus, les erreurs dans les données de votre graphique seront très probablement représentées par des zéros. Cela peut être acceptable ou même souhaitable. Cependant, si trop de lignes (ou de courbes) reviennent à 0, cela peut indiquer un défaut logique dans les données - ou trop de valeurs minuscules, puis il est peut-être nécessaire de redimensionner le graphique en inspectant les axes horizontaux et verticaux et en les modifiant à zéro. dans le problème. Survolez ou cliquez sur un marqueur de données sur le graphique de la série, puis effectuez une recherche dans la colonne appropriée par valeur pour cette valeur et identifiez ses précédents.

Choses dont vous aurez besoin

Microsoft Excel. Ce qui précède a été fait avec MicroSoft® Excel® pour Mac 2011, v 14,3.1 et les autres versions sont compatibles, à l'exception de Reflection, Shadow, Glow et d'autres ajouts d'effets plus récents.

Lisez aussi: Comment résoudre les relations de récurrence?