Dans l'article Comment créer un modèle de régression linéaire bref dans Excel, ce qui n'a pas été montré, c'est comment inclure une ellipse entourant les données, c'est-à-dire créer une limite de données équitable, lequel problème a été résolu. Pour les nouveaux arrivants, les étapes 1 à 3 de l'étape 10 seront répétées ici, puis les nouvelles étapes de modification commenceront par la partie 3, étape 11 (de sorte que si le travail est déjà fait dans l'article précédent, veuillez commencer à la partie 3). Il y aura de nouvelles données plus étalées en termes de variance, pour mieux illustrer l'utilité d'une approche elliptique - que de nouvelles données soient incluses dans l'image ci-dessous.

Première partie de trois:
Le tutoriel

  1. 1 Ouvrez un nouveau classeur dans Excel et créez 3 feuilles de calcul: Données, graphique et sauvegardes Enregistrez le classeur en tant que régression linéaire - leçon modifiée, ou quelque chose de similaire, dans un dossier de fichiers logique.
  2. 2 Définir les préférences: Ouvrez les préférences dans le menu Excel. Paramètres recommandés: définissez Général sur R1C1 et affichez les 10 documents les plus récents; Modifier - définissez toutes les options principales à cocher, sauf Convertir automatiquement le système de dates. Afficher le nombre de décimales = vide (pour les nombres entiers préférés), conserver l'affichage des dates et définir 30 pour la limite du 21ème siècle; Affichage: affichez la barre de formule et la barre d'état, survolez les commentaires et tous les objets, affichez le quadrillage et toutes les cases situées en dessous de cette option automatique ou cochée; Graphique: affiche les noms des cartes et les marqueurs de données lors du survol. Laisser le repos non contrôlé pour l'instant; Calcul - Automatiquement et calcule avant sauvegarde, changement maximum de 0,000,000,000,000,01 sans virgule car la recherche d'objectifs se fait beaucoup et sauvegarde les valeurs de liens externes et utilise le système 1904; Vérification des erreurs - tout vérifier; Enregistrer - enregistrer la photo d'aperçu avec les nouveaux fichiers et enregistrer la récupération automatique après 5 minutes; Ruban - tous cochés sauf Masquer les titres de groupe et Développeur.
  3. 3Sélectionnez entre le 1 et le A dans le coin supérieur gauche de la feuille de travail pour sélectionner la feuille entière et formatez le Centre horizontal d'alignement des cellules et la taille de police 9 ou 10, ou ce que vous êtes à l'aise avec l'affichage.
  4. 4 Entrez les en-têtes de colonne dans la ligne 1: B1: X; C1: Y; D1: X. E1: Y F1: Y. Entrez les en-têtes de colonne des lignes 2: A2: Etudiant; B2 SAT; C2: GPA; D2: VARIANCE; E2: VARIANCE; F2: TREND, G2: Changement.
  5. 5 Modifier Aller à la plage de cellules A3: A22, entrez 1 et faites Modifier les colonnes de la série de remplissage Linear Step Value 1 OK, 20 étudiants veulent connaître la tendance de leurs GPA dépendants, étant donné la variable indépendante du score de l'examen SAT lors de leur entrée dans un nouveau collège. susceptible de monter, de tomber ou de rester à peu près le même?
  6. 6 Entrez les données pour répondre à cette question. Dans les paires x, y, comme indiqué sur l'image ci-dessous, à l'étape 22, ou comme suit: pour l'élève 1, X, Y = 935 pour X ou SAT, 2,2 pour Y ou GPA; élève 2 1260,3,1; puis 1105,2,6; 1320,3.3; 1450,3.8; 960,2.2; 1360,3.2; 900,2.2; 1020,2.2; 1380,3,6; 940,2.3; 1190,2.8; 1000,2.2; 945,2.3; 990,2.4; 1000,2.2; 1040,2.3; 1570,3.9; 1530,3,8; 980,2.4.
  7. 7 Sélectionnez la cellule A23 et entrez MEAN. Entrez les formules sans les guillemets dans la cellule B23 "= AVERAGE (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 $ C $ 23. Sélectionnez la cellule B23 et insérez le nom Définissez le nom X_Bar pour la cellule $ B $ 23. Sélectionnez la plage de cellules B23: C23 et Format Cells Couleur de police Rouge et Gras.
  8. 8 Editer Aller à la plage de cellules D3: D22 et avec la cellule D3 la cellule active et la cellule surlignée, entrez w / o entre guillemets la formule "= (B3-X_BAR) ^ 2" et Editer Fill Down. Modifier Aller à la plage de cellules E3: E22 et avec la cellule E3, la cellule active et la cellule surlignée, entrez w / o entre guillemets la formule "= (C3-Y_BAR) ^ 2" et Edit Fill Down. Sélectionnez la plage de cellules D3: D23 et faites Formatage des cellules Nombre Nombre de décimales 4.
  9. 9 Sélectionnez la cellule D23 et entrez w / o entre 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 Formatage des cellules Nombre Nombre de décimales 6. Sélectionnez la cellule A24 et entrez VARIANCE et sélectionnez la cellule B24 et entrez la formule "= D23", copiez-la et collez-la dans la cellule C24. Sélectionnez la plage de cellules B24: D25 et faites Formatage des cellules Nombre Nombre de décimales 6.
  10. 10 Sélectionnez la cellule A25 et entrez STD DEV. (pour l'écart type) et sélectionnez la cellule B25 et entrez w / o entre guillemets la formule "= STDEVPA (B3: B22)" et copiez-la et collez-la dans la cellule C25. Do Format Cells Nombre Nombre de décimales 7 pour la plage de cellules sélectionnée B25: C25.
  11. 11 Sélectionnez la cellule A27 et entrez FORECAST et sélectionnez la cellule B27 et entrez w / o guillemets la formule "= PREVISION (1290, C4: C23, B4)" qui est une formule de tableau de sorte que vous devez appuyer sur CONTROL + SHIFT + ENTER formule de tableau avec des crochets. C'est la projection de la moyenne pondérée cumulative d'un élève en fonction d'un SAT de 1290 et du reste de la population ou des données de l'échantillon. Il / elle peut s'attendre à une GPA de 3.170409192 une fois que vous aurez formaté les décimales 9 (ce qui correspond exactement plus tard). Ce n'est pas qu'une telle GPA soit nécessaire, mais le but est de prouver la formule, et cela nécessite une certaine précision.
  12. 12Editer Aller à la plage de cellules F3: F22 et entrer la formule sans les guillemets "= TREND (C3: C22, B3: B22, TRUE)" qui est une formule matricielle vous devez appuyer sur CONTROL + SHIFT + ENTER pour entrer correctement le tableau formule avec des crochets. C'est la projection des MPC de la population étudiante compte tenu de leurs performances antérieures.
  13. 13 Modifier Aller à la plage de cellules G3: G22 et avec G3 la cellule surlignée active, entrez la formule sans les guillemets "= F3-C3" et Modifier le remplissage. Do Format Cells Number Number Numéro personnalisé +0.0; -0.0; +0.0.

Deuxième partie de trois:
Tableaux explicatifs, diagrammes, photos

  • (en fonction des données du tutoriel ci-dessus)
  1. 1 Créez le graphique. Sélectionnez la plage de cellules B3: C23 et accédez au Ruban (ou à l'Assistant Graphique) et sélectionnez Graphiques, Tous, faites défiler jusqu'à Dispersion, Scatter marqué… puis Modifier ou copier le nouveau graphique dans la feuille de travail Graphiques. Faites le graphique de mise en forme du diagramme - Titre ci-dessus et remplissez-le avec "Line Regression - SATs vs. GPAs" (sans les guillemets). Sélectionnez Gridlines Lignes de quadrillage verticales Le quadrillage principal est coché. Sélectionnez Axis Titres Titre de l'axe horizontal, Titre du titre sous l'axe et modifiez-le dans "Score SAT" (sans les guillemets). Sélectionnez Axis Titles Titre de l'axe vertical, Titre horizontal et éditez dans "G.P.A." (sans citations). 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. 2 Localisez le marqueur de données pour la moyenne à 1143.75,2.755 qui lira "Series 1 Point 1143.75" (1143.75, 2.8) lorsque vous le survolerez ou que vous aurez cliqué dessus. Cliquez dessus et faites le menu Format, Pointeur de point de données Style automatique Taille 9, puis Couleur de remplissage du marqueur rouge.
  3. 3 Retournez à la feuille de travail Données pour déterminer la ligne 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 faibles inférieures à 10 sur la gauche et des valeurs autour de 1000 sur le bas, on peut s'attendre à une très légère pente décimale et à une ordonnée proche de 0. Les cartes échelonnées peuvent parfois induire une pente.
  4. 4 Sélectionnez la cellule G1 et entrez Y. Sélectionnez la cellule H1 et entrez m et copiez H1 et collez-le dans I1 via la commande c, sélectionnez I1 et commande v. Sélectionnez la cellule H2 et entrez Numérateur et sélectionnez la cellule I2 et entrez le dénominateur. Sélectionnez la plage de cellules H3: H22 et avec H3 comme cellule en surbrillance active, entrez w / o entre guillemets la formule "= (B3-X_BAR) * (C3-Y_BAR)". Modifier Remplir Sélectionnez la plage de cellules I3: I22 et avec I3 comme cellule en surbrillance active, entrez w / o entre guillemets la formule "= (B3-X_BAR) ^ 2". Modifier Remplir Sélectionnez les colonnes H et I et Format des cellules Nombre Nombre de décimales 1.
  5. 5Sélectionnez la cellule H23 et entrez la formule sans les guillemets "= SOMME (H3: H22)" et Formater les cellules Bordure en noir en gras et le copier dans I23.
  6. 6 Sélectionnez la cellule H24 et entrez m et faites Formater la couleur de la police des cellules Rouge. Copiez-le dans la cellule H25 et entrez b dans la cellule H25. Sélectionnez la cellule I24 et entrez w / o entre guillemets "= H23 / I23", qui est la pente m, puis sélectionnez la cellule I25 et entrez la formule sans les guillemets "= Y_BAR-I24 * X_BAR", c'est-à-dire le interception y b = Y_Mean-m * X_Mean.
  7. 7 Copier la cellule H25 et la coller dans la cellule I26 et entrer dans I26 Y '- mX + b. Sélectionnez la cellule H27 et entrez Forecast, puis entrez la formule dans I27 sans les guillemets "= I24 * 1290 + I25". Votre réponse doit être exactement la même que la réponse à PRÉVISION en B27 une fois que vous avez mis en forme les nombres décimaux du nombre de cellules 9.
  8. 8 Editez la cellule de copie 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 27 et copiez sa formule dans la barre de formule - ne copiez pas la cellule et collez-la - cela ne fonctionnera pas correctement. Sélectionnez la cellule I30 et collez-la dans la barre de formule que vous venez de copier. Faites la même chose 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 que je réalise est supérieur à une GPA 4.0, mais nous créons une ligne de régression, donc peu importe.
  9. 9 Activez la feuille de calcul Graphique et cliquez dans le graphique et dans le menu, sélectionnez Ajouter des données de graphique et, en réponse à la requête d'intervalle, revenez à la feuille de calcul Données et sélectionnez la plage de cellules H30: I31. Maintenant le mien sort mal et je dois éditer la série. Pas un gros problème. Sélectionnez le marqueur de données et dans la barre de formule, modifiez la série pour qu'elle se lise "= SERIES (, Sheet1! $ H $ 30: $ H $ 31, Sheet1! $ I $ 30: $ I $ 31,2)" et cliquez sur le marqueur de données à 800,1.78 et marquez Rouge ligne et Poids 0.75 pt, puis Couleur de remplissage des marqueurs Rouge, puis Taille des points ronds Style de marqueur 5. Vos données et votre tableau doivent ressembler à ceci:
  10. 10 Maintenant, pour les modifications, entrez d'abord les nouvelles données appariées SAT-GPA de l'étudiant:
    • Saisissez les données pour répondre à la question. Dans les paires x, y comme indiqué dans l'image tout en haut de cet article, ou comme suit: pour les étudiants 1, X, Y dans les colonnes B et C = 990 pour X ou SAT dans B3, 2,2 pour Y ou GPA IN C3; étudiant 2 1150,3.2; puis 1080,2.6; 1100,3.3; 1280,3,8; 990,2.2; 1110,3.2; 920, 2,0; 1000,2.2; 1200,3,6; 1000, 2,2; 1200, 3,6; 1000, 2.1; 1150, 2,8; 1070, 2,2; 1120, 2.1; 1250, 2,4; 1550,3.9; 1480,3,8; 1010, 2.0.
  11. 11 Sélectionnez la plage de cellules A24: C24 et Insérer des cellules - Décaler les cellules vers le bas. Dans la cellule A24, entrez Midpoint. Dans la cellule B24, entrez la formule sans les guillemets "= xCenter" et sélectionnez la cellule C24 et entrez sans la formule "= yCenter". Sélectionnez la cellule H32 et insérez le nom Définissez le nom xCenter dans la cellule $ H $ 32 et sélectionnez I32 et insérez le nom définir Nom yCenter à la cellule $ I $ 32. Dans H32, entrez la formule sans les guillemets "= (H30 + H31) / 2" et entrez dans I32 la formule sans les guillemets "= (I30 + I31) / 2".
  12. 12 Effectuer une recherche d'objectif sur la plage de valeurs de la ligne XY. Dans la cellule J30, entrez la formule "= 2-I30" et faites Outils Objectif Recherche Définissez la cellule J30 sur 0 en modifiant la valeur dans la cellule: H30. H30 devrait devenir env. 920.690991; allez dans la cellule J31, entrez la formule "= 4-I30" et faites Outils Goal Seek Définissez la cellule J311 à la valeur 0 en modifiant la valeur dans la cellule: H31. H31 devrait devenir env. 1212.61866
  13. 13 Les informations suivantes ne sont pas encore utilisées. Cela est dû au fait que lorsqu'une ligne est ajoutée à un cercle, le résultat est une ellipse. Sélectionnez la cellule I33 et entrez le rayon et alignez le centre et le soulignement. Sélectionnez la cellule H34 et entrez Dist of Line a, sélectionnez la cellule I34 et entrez la formule sans les guillemets "= SQRT ((H31-H32) ^ 2 + (I31-I32) ^ 2)" - c'est le segment long ou ligne a. Il devrait = env. 291.9293847
  14. 14 Dans la ligne 1, entrez les noms des variables définies par Ellipse. K1: Aj_X; L1: Aj_Y; M1: m_Slope_; N1: Stretch_x; O1: Stretch_y; P1: intercepter
  15. 15 Entrez les valeurs suivantes dans la ligne 2: K2: 1200; L2: 4,15; M2: "= m"; N2: 0,0024950665406049 (atteint par la recherche d'objectifs); O2: "= m-0.0005"; P2: "= b". Insérer un nom Définir un nom m pour la cellule I24 et insérer un nom Définissez le nom de b pour la cellule I25.
  16. 16Sélectionnez la plage de cellules K1: P2 et Insérer des noms Créez des noms dans la ligne supérieure, OK.
  17. 17 Modifier Aller à la plage de cellules K4: K54 et dans la cellule supérieure, entrez la formule "= -2 * PI ()" puis appuyez sur Maj + tab pour accéder à la cellule inférieure et entrez "= 2 * PI ()". Avec la plage de cellules K4: K54 sélectionnée, sélectionnez Modifier la série de remplissage, colonnes linéaires, acceptez la valeur de l'étape proposée ou appuyez sur Tendance si la valeur de l'étape proposée est incorrecte = 1, OK.
  18. 18Sauter une colonne et Modifier Aller à la plage de cellules M4: M54 et avec M4 la cellule active, entrez la formule sans les guillemets "= SIN (K4)" et Modifier le remplissage.
  19. 19Editer Aller à la plage de cellules N4: N54 et avec N4 la cellule active, entrez la formule sans les guillemets "= m_Slope_ * SIN ((K4-1))" et Editer Fill Down.
  20. 20Editer Aller à la plage de cellules O4: O54 et avec O4 la cellule active, entrez la formule sans les guillemets "= (M4) / Stretch_x + Aj_X" et Editer Fill Down.
  21. 21Modifier Aller à la plage de cellules P4: P54 et avec P4 la cellule active, entrez la formule sans les guillemets "= (N4) / Stretch_y + Aj_Y + Intercept" et Editer Fill Down.
  22. 22 Faites un petit tableau de feuille de calcul. Sélectionnez la plage de cellules O4: P54 et, à l'aide de l'assistant graphique ou du ruban, faites tous les graphiques et faites défiler jusqu'à Scatter Smoothed Line Scatter et un petit graphique apparaîtra sur vos données. Important: les valeurs x doivent être comprises entre 800 et 1600 et les valeurs y doivent être comprises entre 1,8 et 4,1. Si ce n’est pas le cas, recherchez un point décimal mal placé dans les premières lignes 2 des variables définies ou différence entre les calculs de m et b. Sinon, il s'agit d'une erreur de saisie de données sur une ligne, à vérifier une colonne à la fois. Voir aussi la section Avertissements ci-dessous sur les erreurs.
  23. 23 Cliquez sur le tracé de la série du nouveau petit diagramme sur la feuille de données et cliquez sur Commande + c pour le copier, puis accédez à la feuille de travail Carte et cliquez dans le graphique et cliquez sur Commande + v pour le coller. Si cela fonctionne comme le mien, c'est FUBAR et il faut corriger une série à la fois.
  24. 24 Si la ligne de régression est toujours affichée, éditez ses séries dans la barre de formule à droite de la première parenthèse en entrant avec des guillemets "Ligne de régression" avant la virgule. Ensuite, appuyez sur la flèche vers le bas de votre clavier pour accéder à la série 1 et modifiez la ligne (pas de ligne), le style de marqueur rouge 5, le remplissage de marqueur - rouge et modifiez la série entre guillemets. , "SAT-GPA Pairs" avant la première virgule.
  25. 25 Appuyez sur OK, puis sur la touche Entrée et la touche Bas une ou deux fois pour accéder à la série 3, qui est l’ellipse. Effectuer la sélection du format de présentation du graphique, le remplissage du marqueur bleu-vert, le marquage automatique de la ligne avec la ligne lissée, OK.
  26. 26 Activez la feuille de calcul Données et sélectionnez la plage de cellules B23: C24 et copiez et activez la feuille de calcul Graphique et Commande + v collez la plage dans. Cela ne fonctionne pas bien pour moi et je dois aussi l'éditer et supprimer une série supplémentaire. Ce que vous voulez finir avec une série qui se lit dans la barre de formule, "= SERIES (" Mean and Midpoint ", Data! $ B) $ 23: $ B $ 24, Données! $ C $ 23: $ C $ 24,4) ", Ligne sans marqueur, Point rond à style marqueur, Taille 9, Bleu foncé ou Violet.
  27. 27Aller à Insérer une image Word Art pour créer un en-tête pour le graphique qui a un certain éclat.
  28. Ellipse pour ajuster les données de régression 28
    Terminé!    ! Bon travail. Dans un prochain article, on peut étudier comment analyser les différents secteurs de l'ellipse. Pour l’instant, l’ensemble de données a été atteint, ce qui était l’objectif. Pour ceux qui ont hâte de se lancer dans cette analyse, un indice: lors du calcul de la surface d'un secteur d'ellipse incliné: Regardez, faites comme si vous étiez dans le plan où il n'est pas incliné et n'est qu'une ellipse normale. C'est l'approche recommandée par mon mentor. Astuce # 2: Regardez vos données pour trouver la longueur de la ligne b. Dans Astuces, vous trouverez les données et le petit graphique pour l'ellipse oblique non ajustée que vous pouvez utiliser comme point de départ dans vos analyses.

Troisième partie de trois:
Conseils utiles

  1. 1 Utilisez les articles d'aide lorsque vous parcourez ce tutoriel:
    • Voir l'article Comment créer un chemin de particules, une forme de collier ou une bordure sphérique spiralée pour une liste d'articles liés à Excel, à l'art géométrique et / ou trigonométrique, à la représentation graphique et à la formulation algébrique.
    • Pour plus de tableaux et de graphiques, vous pouvez également cliquer sur Catégorie: Imagerie Microsoft Excel, Catégorie: Mathématiques, Catégorie: Tableurs ou Catégorie: Graphiques pour afficher de nombreuses feuilles de calcul et graphiques Excel dans lesquels 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.