כיצד ליצור מודל רגרסיה ליניארי קצר ב- Excel?
בחר טווח תאים H3: H22 ועם H3 כתא המודגש הפעיל, הזן ללא ציטוטים של הנוסחה "= (B3-X_BAR) * (C3-Y_BAR)".
Excel עושה עבודה יפה עם סטטיסטיקה, או שיש להם צד שלישי לכתוב את התוספת שלהם, שזמינה בחינם. במאמר זה תלמד כיצד להקרין מגמה באמצעות אקסל וניתוח רגרסיה לינארית.
חלק 1 מתוך 3: המדריך
- 1פתח חוברת עבודה חדשה ב- Excel והכין 3 גליונות עבודה: נתונים, תרשים ושמירה. שמור את חוברת העבודה כרגרסיה לינארית - שיעור קצר, או משהו דומה, בתיקיית קבצים לוגית.
- 2הגדר העדפות: פתח את העדפות בתפריט Excel. הגדרות מומלצות: הגדר את כללי ל- R1C1 כבוי והראה את 10 המסמכים האחרונים; עריכה - הגדר את כל האפשרויות המובילות לבדיקה למעט המרת תאריך אוטומטית. הצג מספר מקומות עשרוני = ריק (עבור מספרים שלמים המועדפים), שמור תצוגת תאריכים והגדר 30 לניתוק המאה ה -21; תצוגה - הצג שורת פורמולה ושורת סטטוס, רחף לתגובות וכל האובייקטים, הצג קווי רשת וכל התיבות שמתחת לאוטומטית או מסומנת; תרשים - הצגת שמות תרשימים וסמני נתונים ברחף. השאר מנוחה לא מסומנת בינתיים; חישוב - באופן אוטומטי וחישוב לפני שמירה, שינוי מקסימלי 0,000000,000000,01 פסיקי ללא חיפושי מטרה נעשים הרבה ושומרים ערכי קישור חיצוניים ומשתמשים במערכת 1904; שגיאת בדיקה - בדוק הכל; שמור - שמור תמונה מקדימה עם קבצים חדשים ושמור שמירה אוטומטית לאחר 5 דקות; סרט - הכל מסומן למעט הסתר כותרות קבוצתיות ומפתח.
- 3בחר בין 1 ו א בפינת הגיליון השמאלי העליונה כדי לבחור את הגיליון כול ולעשות תאי פורמט יישור למרכז אופקי ו גודל גופן 9 או 10, או כול דבר אחר שאתה חש בנוח עם הצגה.
- 4הזן את כותרות העמודות בשורה 1: B1: X; C1: Y; D1: X. E1: Y F1: Y. הזן את כותרות העמודות של שורות 2: A2: סטודנט; B2 SAT; C2: GPA; D2: VARIANCE; E2: VARIANCE; F2: מגמה, G2: שינוי.
- 5ערוך עבור לטווח תאים a3: A22, הזן 1 ועשה ערוך מילוי עמודות סדרות שלב לינארי ערך שלב 1 בסדר, ישנם 20 סטודנטים שרוצים לדעת את המגמה של GPA התלויים שלהם בהתחשב במשתנה העצמאי של ציון הבחינה SAT בעת כניסה קולג 'חדש, כלומר האם ציוניהם עשויים לעלות, לרדת או להישאר בערך זהים?
- 6הזן את הנתונים כדי לענות על שאלה זו. בזוגות {x, y} כפי שמוצג בתמונה למטה שלב 22, או כדלקמן: עבור תלמיד 1, {X, Y} = {935 עבור X או SAT, 2,2 עבור Y או GPA}; תלמיד 2 {12603,1}; ואז {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בחר תא a23 והזן MEAN. הזן את הנוסחה ללא ציטוטים לתא B23" = ממוצע (B3: B22)" והעתק אותה והדבק אותה לתא C23. בחר תא C23 והוסף שם הגדר שם Y_Bar עבור תא $ C17 € בחר תא B23 והוסף שם הגדר שם X_Bar עבור תא $ B17 € בחר טווח תאים B23: C23 ועיצוב תאים צבע גופן אדום ומודגש.
- 8ערוך עבור לטווח התא d3: d22 ועם התא d3 התא הפעיל והמואר, הזן ללא ציטוטים את הנוסחה "= (b3-x_bar) ^ 2" וערוך מילוי למטה. ערוך עבור לתא התא E3: E22 ועם התא E3 התא הפעיל והמואר, הזן ללא ציטוטים את הנוסחה "= (C3-Y_BAR) ^ 2" וערוך מילוי למטה. בחר טווח תאים D3: D23 ועשה עיצוב תאים מספר מספר עשרוני 4.
- 9בחר תא d23 והזן ללא ציטוטים את הנוסחה "= סכום (d3: d22) / (20-1)" והעתק אותה והדבק אותה לתא e23. בחר טווח תאים E3: E23 ועשה עיצוב תאים מספר מספר עשרוני 6. בחר תא A24 והזן VARIANCE ובחר תא B24 והזן את הנוסחה "= D23" והעתק אותו והדבק אותו לתא C24. בחר טווח תאים B24: D25 ועשה עיצוב תאים מספר מספר עשרוני 6.
- 10בחר תא a25 והזן STD DEV. (לסטייה רגילה) ובחר תא B25 והזן ללא ציטוטים הנוסחה "= STDEVPA (B3: B22)" והעתק והדבק אותה לתא C25. בצע עיצוב תאים מספר מספר מקומות עשרוניים 7 לטווח התאים שנבחר B25: C25.
- 11בחר תא a27 והזן FORECAST ובחר תא b27 והזן ללא ציטוטים הנוסחה "= stdevpa (b3: b22)" שהיא נוסחת מערך ולכן עליך ללחוץ על control + shift + enter כדי להזין כראוי את נוסחת המערך עם סוגריים. זו ההקרנה של ה- GPA של סטודנט אחד בהתחשב ב- SAT של 1290 ושאר האוכלוסייה או נתוני מדגם. הוא עשוי לצפות ל- GPA של 3,170409192 לאחר שתפרמט את התאים מספר מספר עשרוני 9 (שתואם בהמשך, בדיוק). לא שיש צורך ב- GPA מדויק כל כך, אבל העניין הוא להוכיח את הנוסחה, וזה דורש דיוק מסוים.
- 12ערוך עבור לטווח התא f3: f22 והזן את הנוסחה ללא ציטוטים "= מגמה (c3: c22, b3: b22,, true)" שהיא נוסחת מערך ולכן עליך ללחוץ על control + shift + enter כדי להיכנס כראוי למערך נוסחה עם סוגריים. זו ההקרנה של ה- GPA של אוכלוסיית הסטודנטים בהתחשב בביצועיהם הקודמים.
- 13ערוך עבור לטווח התאים g3: g22 ועם g3 התא הפעיל המואר, הזן את הנוסחה ללא ציטוטים "= f3-c3" וערוך מילוי למטה. בצע עיצוב מספר תאים מספר מותאם אישית +0,0; -0,0; +0,0.
בחר טווח תאים I3: I22 ועם I3 כתא המודגש הפעיל, הזן ללא ציטוטים את הנוסחה "= (B3-X_BAR) ^ 2".
חלק 2 מתוך 3: תרשימי הסבר, דיאגרמות, תמונות
- 1צור את התרשים (תלוי בנתוני ההדרכה לעיל). בחר טווח תאים B3: C23 ועבור לרצועת הכלים (או בצע אשף תרשימים) ובחר תרשימים, הכל, גלול מטה אל פיזור, פיזור מסומן.. ואז ערוך גזור או העתק את התרשים החדש לגליון העבודה תרשימים. בצע כותרת של תרשים לפריסת תרשים - כותרת מעל תרשים ומלא אותו ב "קו רגרסיה - SAT לעומת GPA" (ללא הצעות מחיר). בחר קווי רשת אנכיים רשתות קווי רשת עיקריות מסומנות. בחר כותרות ציר כותרת ציר אופקית, כותרת כותרת מתחת לציר וערוך ב"ציון SAT "(ללא מרכאות). בחר כותרות ציר כותרת ציר אנכית, כותרת אופקית ועריכה ב- "GPA" (ללא מרכאות). על פי מוסכמה, המשתנה x העצמאי עובר על הציר האופקי התחתון והמשתנה התלוי y עובר על הציר האנכי השמאלי.
- 2אתר את סמן הנתונים עבור הממוצע ב- {1143,752.755} שיקרא "סדרה 1 נקודה 1143,75" (1143,75, 2,8) כאשר אתה מרחף מעליו או לוחץ עליו. לחץ עליו ועשה את התפריט עיצוב, סגנון סמן נקודת נתונים גודל אוטומטי 9, ואז צבע מילוי סמן אדום.
- 3חזור לדף העבודה של הנתונים כדי להבין את קו הרגרסיה של y '= mx + b, כאשר m = השיפוע ו- b = יירוט y. כשמסתכלים על נתוני התרשים עם ערכים נמוכים מתחת ל -10 בצד שמאל וערכים בסביבות 1000 בתחתית, אפשר לצפות לשיפוע עשרוני קל מאוד ומפחת y קרוב ל 0. תרשימים מוקדמים יכולים לרמות לפעמים כמו שיפוע.
- 4בחר תא g1 והזן Y. בחר תא H1 והזן m והעתק את H1 והדבק אותו ל- I1 באמצעות פקודה c, בחר I1, ופקוד v. בחר תא H2 והזן Numerator ובחר תא I2 והזן מכנה. בחר טווח תאים H3: H22 ועם H3 כתא המודגש הפעיל, הזן ללא ציטוטים של הנוסחה "= (B3-X_BAR) * (C3-Y_BAR)". ערוך מילוי למטה. בחר טווח תאים I3: I22 ועם I3 כתא המודגש הפעיל, הזן ללא ציטוטים את הנוסחה "= (B3-X_BAR) ^ 2". ערוך מילוי למטה. בחר עמודות H ו- I ועיצוב תאים מספר מספר עשרוני מקומות 1.
- 5בחר בתא h23 והזן את הנוסחה ללא ציטוטים "= סכום (h3: h22)" ועצב תאים בגבול קו מתאר מודגש בשחור והעתק אותו ל- i23.
- 6בחר בתא h24 והזן m ובצע פורמט תאים צבע גופן אדום. העתק אותו לתא H25 והזן b בתא H25. בחר תא I24 והזן ללא ציטוטים את הנוסחה "= H23 / I23", שהיא השיפוע m, ואז בחר תא I25 והזן את הנוסחה ללא מרכאות "= Y_BAR-I24 * X_BAR", כלומר יירוט y b = Y_Mean-m * X_Mean.
- 7העתק את התא h25 והדבק אותו לתא i26 והזן ל- i26 y '- Mx + b. בחר בתא H27 והזן Forecast ואז הזן את הנוסחה בגרסאות I27 ללא ציטוטים "= I24 * 1290 + I25". התשובה שלך אמורה להיות שווה לתשובת FORECAST ב- B27 ברגע שיש לך עיצוב תאים מספר מספר עשרוני 9.
- 8ערוך את תא העתק i26 לטווח התאים h29: i29. לתא H29 נכנסים לקו X ולתא I29 נכנסים לקו Y. הזן 800 לתא H30 ו 1600 לתא H31. בחר תא I 27 והעתק את הנוסחה שלו למעלה בסרגל הנוסחה - אל תעתיק את התא והדבק אותו - זה לא יסתדר כמו שצריך. בחר תא I30 והדבק בשורת הנוסחאות העריכה את הנוסחה שהעתקת זה עתה. בצע את אותו הדבר עבור תא I31. ערוך את הנוסחה של התא I31 כדי לקרוא "= I24 * H31 + I25" ולחץ על Enter ואז ערוך את הנוסחה של התא I30 בסרגל הנוסחה כדי לקרוא "= I24 * H30 + I25" ולחץ על Enter. התוצאה של I30 צריכה להיות 1,7786108729206 והתוצאה של I31 צריכה להיות 4,05093465957812 (מה שאני מבין שהיא גבוהה יותר מ -4,0 GPA, אבל אנחנו יוצרים קו רגרסיה, כך שזה לא כל כך משנה).
- 9הפעל את גליון העבודה של התרשים ולחץ בתרשים ובתפריט, האם תרשים הוסף נתונים ובתגובה לשאילתת הטווח, חזור לגליון העבודה של הנתונים ובחר טווח תאים h30: i31. עכשיו שלי יוצא לא נכון ואני צריך לערוך את הסדרה. לא עניין גדול. בחר את סמן הנתונים ובסרגל הנוסחאות, ערוך את הסדרה בכדי לקרוא "= SERIES (, Sheet1! $ H22 €: $ H23 €, Sheet1! $ I22 €: $ I230 €)" ולחץ על סמן הנתונים בטלפון {8001, 78} והפוך את הקו לאדום ולמשקל 0,75 נק ', ואז צבע מילוי מרקר אדום ואז סגנון מרקר עגול נקודה בגודל 5.
ערוך עבור לטווח התא d3: d22 ועם התא d3 התא הפעיל והמואר, הזן ללא ציטוטים את הנוסחה "= (b3-x_bar) ^ 2" וערוך מילוי למטה.
חלק 3 מתוך 3: הדרכה מועילה
- 1השתמש במאמרי עוזר בעת המשך הדרכה זו:
- עיין במאמר כיצד ליצור נתיב חלקיקי ספין ספירלי או צורת שרשרת או גבול כדור עבור רשימת מאמרים הקשורים לאומנות אקסל, גיאומטרית ו / או טריגונומטריה, תרשימים / תרשימים וניסוח אלגברי.
- לקבלת תרשימים וגרפים אמנותיים נוספים, ייתכן שתרצה ללחוץ על קטגוריה: תמונות Excel Excel, קטגוריה: מתמטיקה, קטגוריה: גיליונות אלקטרוניים או קטגוריה: גרפיקה כדי להציג דפי עבודה ותרשימים רבים של Excel שבהם הפכו טריגונומטריה, גיאומטריה וחשבון לאמנות, או פשוט לחץ על הקטגוריה כפי שמופיע בחלק הלבן הימני העליון של דף זה, או בפינה השמאלית התחתונה של הדף.
ערוך עבור לתא התא E3: E22 ועם התא E3 התא הפעיל והמואר, הזן ללא ציטוטים את הנוסחה "= (C3-Y_BAR) ^ 2" וערוך מילוי למטה.
- שגיאות: אם יש לך שגיאות או ערכי שגיאה, הגיליון לא שלם וזקוק להזנה נוספת או לטבלאות בדיקה עבור משתנים קריטיים או אולי טעית איפשהו לאורך הקו. אם ההנחיות הושלמו ועדיין יש שגיאות, בחר בתא בעל ערך השגיאה השמאלי ביותר והראשון ביותר. חפש שגיאת הקלדה בנוסחה או בסוגריים שאין שני להם. יתכן ששם מוגדר שגוי - עליהם להזין את הנוסחאות בדיוק כפי שהוגדרו. האם הכנס שם הגדר כדי לבדוק. אם יש לך DIV / 0!, אין לי, אז חפש משתנה שאיכשהו לא התמלא בערך אולי. בכל מקרה, מה שאתה רוצה לעשות הוא לבחור את התא עם השגיאה, ולאחר שבדקת את כל השגיאות האופייניות האלה, בצע כלים לביקורת מעקב אחר תקדימים ו / או שגיאת מעקב.אם תיקון כל השגיאות השמאליות העליונות ביותר אינו מתקן את שאר השגיאות בגליון העבודה שלך, יתכן שתצטרך לעשות זאת בדרך הקשה, מימין למטה כלפי מעלה ואז שמאלה; זו הדרך האיטית אך הבטוחה לתקן את כל השגיאות.
- כמו כן, סביר להניח ששגיאות בנתוני התרשים יעלו כאפסים. זה עשוי להיות מקובל או רצוי אפילו. עם זאת, אם יותר מדי קווים (או עקומות) חוזרים ל -0, זה עשוי להצביע על פגם לוגי בנתונים - או על יותר מדי ערכים זעירים ואז אולי צריך לשנות את קנה המידה של התרשים על ידי בדיקת הצירים האופקיים והאנכיים ושינוים לאפס. בבעיה. העבר את העכבר מעל או לחץ על סמן נתונים בעלילת הסדרה ואז בצע חיפוש בעמודה המתאימה לפי ערך עבור אותו ערך, וזהה את תקדימיו.
- Microsoft Excel. האמור לעיל נעשה עם MicroSoft® Excel® עבור Mac 2011, גרסאות 14,3.1 וגרסאות אחרות תואמות, למעט תוספות Reflection, Shadow, Glow ותוספות אפקטים אחרונות אחרות.
קרא גם: איך לספור עד 99 על האצבעות?
שאלות ותשובות
- מהם המאפיינים של עקומה אקספוננציאלית?עקומה אקספוננציאלית עוברת דרך הנקודה P (0 | 1) והיא אסימפטוטית על פי ציר ה- X.
