בניתוח סטטיסטי, שונות בקרב חברי קבוצת נתונים מראה עד כמה רחוק את נקודות הנתונים הן מקו המגמה, הידוע גם בשם - רגרסיה. ככל שהשונות גבוהה יותר, כך מתפשטים יותר נקודות הנתונים. המחקר של ניתוח השונות מראה אילו חלקים מהשונות ניתנים להסבר לפי מאפייני הנתונים, ואשר ניתן לייחס אותם לגורמים אקראיים. החלק של השונות שאינה ניתנת להסבר נקרא השונות השיורית.
באמצעות גיליונות אלקטרוניים של Excel כדי לחשב שונות שיורית
הנוסחה לחישוב השונות השיורית כוללת חישובים מורכבים רבים. עבור ערכות נתונים קטנות, תהליך חישוב השונות השיורית ביד יכול להיות מייגע. עבור ערכות נתונים גדולות, המשימה יכולה להיות מתישה. באמצעות גיליון אלקטרוני של Excel, אתה רק צריך להזין את נקודות הנתונים ובחר את הנוסחה הנכונה. התוכנית מטפלת בחישובים המורכבים ומספקת תוצאה מהירה.
נקודות מידע
פתח גיליון אלקטרוני חדש של Excel והזן את נקודות הנתונים לשתי עמודות. קווי רגרסיה דורשים שלכל נקודת נתונים יש שני אלמנטים. סטטיסטיקאים בדרך כלל תווית אלה אלמנטים "X" ו "Y." לדוגמה, חברת הביטוח הכללית רוצה למצוא את השונות השיורית של גובה ומשקל עובדיה. המשתנה X מייצג את הגובה והמשתנה Y מייצג את המשקל. הזן את הגבהים לתוך עמודה A ואת המשקולות לתוך עמודה ב '.
מציאת ממוצע
ה מתכוון מייצג את הממוצע עבור כל רכיב במערך הנתונים. בדוגמה זו, ביטוח כללי רוצה למצוא את סטיית התקן הממוצעת ואת השונות המשותפת של 10 עובדים וגבהים. ממוצע הגבהים המופיעים בעמודה A ניתן למצוא על ידי הזנת הפונקציה "= AVERAGE (A1: A10)" לתא F1. ממוצע המשקלים המופיעים בעמודה B ניתן למצוא על ידי הזנת הפונקציה "= ממוצע (B1: B10)" לתא F3.
מציאת סטיית התקן והשונות המשותפת
ה סטיית תקן מודד עד כמה מרוחקים נקודות הנתונים מהממוצע. ה שונות מודד עד כמה שני האלמנטים של נקודת הנתונים משתנים יחד. סטיית התקן של הגבהים נמצאה על ידי הזנת הפונקציה "STDEV = (A1: A10)" לתא F2. סטיית התקן של המשקולות נמצאה על ידי הזנת הפונקציה "= STDEV (B1: B10)" לתא F4. החלוקה בין הגבהים למשקולות נמצאה על ידי הזנת הפונקציה "COVAR (A1: A10, B1: B10)" לתא F5.
מציאת קו רגרסיה
ה - רגרסיה מייצג פונקציה ליניארית בעקבות המגמה של נקודות הנתונים. הנוסחה של קו הרגרסיה נראית כך: Y = aX + b.
המשתמש יכול למצוא את הערכים עבור "a" ו - "b" באמצעות חישובים עבור האמצעים, סטיות תקן ושונות. הערך עבור "b" מייצג את הנקודה שבה קו הרגרסיה מיירט את ציר ה- Y. הערך ניתן למצוא על ידי לקיחת המשותף וחלוקת אותו על ידי ריבוע של סטיית תקן של ערכי X. הנוסחה של Excel נכנסת לתא F6 ונראית כך: = F5 / F2 ^ 2.
הערך עבור "a" מייצג את שיפוע קו הרגרסיה. הנוסחה של Excel נכנס לתא F7 ונראית כך: = F3-F6 * F1.
כדי לראות את הנוסחה עבור קו הרגרסיה, הזן שרשור מחרוזת זה לתא F8:
(= F7; 2)); (= (;); (;
חישוב ערכי Y
השלב הבא כולל חישוב ערכי Y על קו הרגרסיה עבור ערכי X הנתונים במערך הנתונים. הנוסחה כדי למצוא את ערכי Y נכנס בעמודה C ונראה כך:
= $ F $ 6 * A (i) + $ F $ 7
כאשר A (i) הוא הערך עבור עמודה A בשורה (i). הנוסחאות נראות כך בגיליון האלקטרוני:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, וכן הלאה
הערכים בעמודה D מראים את ההבדלים בין הערכים הצפויים לבין הערך הממשי עבור Y. הנוסחאות נראות כך:
= B (i) -C (i), כאשר B (i) ו- C (i) הם הערכים בשורה (i) בעמודות B ו- C, בהתאמה.
מציאת השונות השיורית
ה נוסחה לשונות שיורית נכנס לתא F9 ונראה כך:
= SUMSQ = (D1: D10) / (COUNT (D1: D10) -2)
כאשר ה- SUMSQ (D1: D10) הוא סכום הריבועים של ההבדלים בין ערכי Y ו- Y הצפויים, ו- COUNT (D1: D10) -2 הוא מספר נקודות הנתונים, מינוס 2 עבור דרגות חופש נתונים.
