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) המערך שיתקבל יכלול חמש שורות. עיין/י במידע נוסף על מערך זה ישר אחרי הדוגמאות.
דוגמאות |
|---|
בהינתן הטבלה הבאה של ערכי-y-ידועים (תאים A2:A6) וערכי-x-ידועים (תאים B2:B6): |
A | B | |
|---|---|---|
1 | Y | X |
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 | נ | 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, המערך שיתקבל יהיה שווה לשורה הראשונה בלבד.