LINEST
הפונקציה LINEST מחשבת מערך של סטטיסטיקות עבור הקו הישר שמתאים ביותר לנתונים באמצעות שיטת הריבועים הפחותים.
LINEST(ערכי-y-ידועים, ערכי-x-ידועים, נק׳-חיתוך-y-שאינה-אפס, עוד-נתונים-סטטיסטיים)
ערכי-y-ידועים: אוסף המכיל את ערכי ה-y הידועים. על ערכי-y-ידועים להכיל ערכים מספריים או ערכי תאריך ושעה. אם יש אוסף אחד בלבד של ערכי x ידועים, ערכי-y-ידועים יכולים להיות בכל גודל. אם יש יותר מאוסף אחד של ערכי x ידועים, ערכי-y-ידועים יכולים להיות או עמודה אחת שמכילה את הערכים או שורה אחת שמכילה את הערכים, אך לא שתיהן.
ערכי-x-ידועים: אוסף אופציונלי המכיל את ערכי ה-x הידועים. על ערכי-x-ידועים להכיל ערכים מספריים או ערכי תאריך ושעה. אם הוא מושמט, המערכת תניח שהוא קבוצה שגודלה זהה לערכי-y-ידועים, ושמתחילה במספר 1, - למשל, 1, 2, 3 אם יש שלושה ערכי-y-ידועים. אם יש קבוצה אחת בלבד של ערכי x ידועים, על ערכי-x-ידועים, אם הוא מוגדר, להיות באותו הגדול כמו ערכי-y-ידועים. אם יש קבוצה אחת בלבד של ערכי x ידועים, כל שורה/עמודה של ערכי-x-ידועים נחשבת כקבוצה אחת ועל הגודל של כל שורה/עמודה להיות זהה לגודל של השורה/עמודה בערכי-y-ידועים.
נק׳-חיתוך-y-שאינה-אפס: ערך מודאלי אופציונלי המגדיר כיצד יש לחשב את נקודת החיתוך עם ציר ה-y (הקבוע b).
רגיל (1, TRUE או מושמט): יש לחשב את נקודת החיתוך עם ציר ה-y (הקבוע b) בצורה רגילה.
השתמש ב-0 (0, FALSE): יש לקבוע שנקודת החיתוך עם ציר ה-y (הקבוע b) היא 0.
עוד-נתונים-סטטיסטיים: ערך מודאלי אופציונלי הקובע אם יש לחשב נתונים סטטיסטיים נוספים.
אין נתונים נוספים (0, FALSE או מושמט): אל תצרף נתוני רגרסיה נוספים למערך המחושב.
נתונים נוספים (1, TRUE): צרף נתוני רגרסיה נוספים למערך המחושב.
הערות
הערכים שהפונקציה תחזיר כלולים במערך. דרך אחת לקרוא את הערכים במערך היא להשתמש בפונקציה INDEX. ניתן לשלב את הפונקציה LINEST עם הפונקציה INDEX. =INDEX(LINEST(ערכי-y-ידועים, ערכי-x-ידועים, נק׳-חיתוך-y, עוד נתונים סטטיסטיים), y, x) כאשר y ו-x הם העמודה ומדד השורה של הערך הרצוי.
אם נתונים סטטיסטיים נוספים אינם מוחזרים (עוד-נתונים-סטטיסטיים הוא FALSE), המערך שיתקבל יהיה בעל עומק של שורה אחת. מספר העמודות יהיה שווה למספר הקבוצות של ערכי-x-ידועים ועוד 1. הוא יכיל את שיפועי הקווים (ערך אחד עבור כל שורה/עמודה של ערכי x) בסדר הפוך (הערך הראשון יתייחס לשורה/עמודה האחרונה של ערכי x) ולאחר מכן את הערך עבור b, נקודת החיתוך.
אם נתונים סטטיסטיים נוספים מצורפים (עוד-נתונים-סטטיסטיים הוא TRUE) המערך שיתקבל יכלול חמש שורות. עיין/י במידע נוסף על מערך זה ישר אחרי הדוגמאות.
דוגמאות |
|---|
בהינתן הטבלה הבאה של ערכי-x-ידועים (תאים A2:A6) וערכי-y-ידועים (תאים B2:B6): |
A | B | |
|---|---|---|
1 | X | Y |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=INDEX(LINEST(A2:A6, B2:B6,1,0),1) תחזיר את הערך 0.752707581227437 בקירוב, בהינתן ערך רגיל (1) עבור נק׳-חיתוך-y-שאינה-אפס. זהו השיפוע של הקו המתאים ביותר מאחר שציינו שברצוננו שהערך הראשון מהמערך יוחזר על-ידי הפונקציה INDEX וציינו קבוצה אחת בלבד של ערכי-x-ידועים. =INDEX(LINEST(A2:A6, B2:B6, 1, 0),2) תחזיר את הערך 0.0342960288808646 בקירוב, שהוא הערך b, נקודת החיתוך של הקו המתאים ביותר. נקודת החיתוך התקבלה מאחר שציינו שברצוננו שהערך השני מהמערך יוחזר על-ידי הפונקציה INDEX, כאשר מדובר בערך השני מכיוון שציינו רק קבוצה אחת של ערכי-x-ידועים. |
התוכן של מערך הנתונים הסטטיסטיים הנוספים
הפונקציה LINEST יכולה לכלול מידע סטטיסטי נוסף במערך שהפונקציה מחזירה. לצורך הדיון הבא, נניח שיש חמש קבוצות של ערכי x ידועים, בנוסף לערכי y הידועים. כמו כן, נניח שערכי-x-הידועים נמצאים בחמש שורות או בחמש עמודות בטבלה. בהתאם לכך, המערך שיחושב על-ידי הפונקציה LINEST יכיל את הערכים הבאים.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | F | DF | ||||
5 | R1 | R2 |
שורה 1, עמודה 1 מכילה את S5 (השיפוע עבור הסדרה החמישית של ערכי-x-ידועים) ממשיכה דרך עמודה 5, שתכיל את S1 (השיפוע עבור הסדרה הראשונה של ערכי-x-ידועים). שים/י לב שהשיפועים שקשורים לכל אחת מהקבוצות של ערכי-x-ידועים מתקבלים בסדר הפוך.
התא האחרון בשורה 1 מכיל את b, נקודת החיתוך עם ציר ה-y עבור ערכי x הידועים. בדוגמא שלנו, זה יהיה התא בשורה 1, עמודה 6.
שורה 2, עמודה 1 מכילה את SE5 (השגיאה הסטנדרטית עבור המקדם המשויך לסדרה החמישית של ערכי-x-ידועים) ממשיכה דרך עמודה 5, שתכיל את SE1 (השגיאה הסטנדרטית עבור המקדם המשויך לסדרה הראשונה של ערכי-x-ידועים). ערכים אלה יתקבלו בסדר הפוך; כלומר, אם יש חמש סדרות של ערכי x ידועים, הערך עבור הסדרה החמישית יתקבל ראשון במערך. שיטה זו זהה לזו שבה מוחזרים ערכי השיפוע.
התא האחרון בשורה 2 מכיל את SEb, השגיאה הסטנדרטית המשויכת לערך נק׳ החיתוך של y (b). בדוגמא שלנו, זה יהיה התא בשורה 2, עמודה 6.
התא בשורה 3, עמודה 1 מכיל את מ׳, מקדם התוקף. נתון זה משווה בין האומדנים של ערכי y וערכי y בפועל. אם הוא 1, אין הבדל בין האומדן של ערך y והערך בפועל. מצב זה נקרא מתאם מושלם. אם מקדם המובהקות הוא 0, לא קיים מתאם ונוסחת הרגרסיה הנתונה לא תועיל בניבוי ערך y.
שורה 3, עמודה 2 מכילה את SEy, השגיאה הסטנדרטית המשויכת לערך המשוער של y.
התא בשורה 4, עמודה 1 מכיל את F, ערך F הנצפה. ניתן להשתמש בערך F הנצפה כדי לסייע בקביעה אם הקשר שנצפה בין המשתנים התלויים והבלתי-תלויים הוא מקרי.
התא בשורה 4, עמודה 2 מכיל את DF, דרגות החופש. השתמש/י בסטטיסטיקה של דרגות החופש כדי לסייע בקביעת רווח בר-סמך.
שורה 5, עמודה 1 מכילה את R1, סכום הרגרסיה של ריבועים.
שורה 5, עמודה 2 מכילה את R2, סכום השארית של ריבועים.
להלן מספר דברים שכדאי לזכור בנוגע למערך של נתונים סטטיסטיים נוספים:
לא משנה אם ערכי x הידועים וערכי y הידועים נמצאים בשורות או בעמודות. בכל מקרה, המערך שמתקבל מסודר לפי שורות כפי שמתואר בטבלה.
הדוגמא מניחה שיש חמש קבוצות של ערכי x ידועים. אם היו יותר או פחות מחמש קבוצות, מספר העמודות במערך שהיה מתקבל היה משתנה בהתאם (הוא תמיד יהיה שווה למספר הקבוצות של ערכי x ידועים ועוד 1), אך מספר השורות היה נשאר קבוע.
אם לא צוינו נתונים סטטיסטיים נוספים בארגומנטים של הפונקציה LINEST, המערך שיתקבל יהיה שווה לשורה הראשונה בלבד.