LINEST (הפונקציה LINEST)

מאמר זה מתאר את תחביר הנוסחה של הפונקציה LINEST ואת השימוש בה ב- Microsoft Excel. חפש קישורים למידע נוסף אודות יצירת תרשימים וביצוע ניתוח רגרסיה בסעיף למידע נוסף.

תיאור

הפונקציה LINEST מחשבת סטטיסטיקה עבור קו מסוים בשיטת "הריבועים הפחותים" כדי להגיע לקו הישר התואם ביותר לנתונים, ולאחר מכן מחזירה מערך המתאר את הקו. ניתן גם לשלב את LINEST עם פונקציות אחרות כדי לחשב את הסטטיסטיקה של סוגים אחרים של מודלים ליניאריים בפרמטרים הלא ידועים, כולל סדרות פולינומיאליות, סדרות לוגריתמיות, סדרות מעריכיות וסדרות של חזקות. כיוון שפונקציה זו מחזירה מערך של ערכים, יש להזין אותה כנוסחת מערך. ההוראות מופיעות לאחר הדוגמאות במאמר זה.

המשוואה עבור הקו היא:

y = mx + b

-לחלופין-

y = m1x1 + m2x2 + ... + b

אם קיימים טווחים מרובים של ערכי x, כאשר ערכי y התלויים הם פונקציה של ערכי x הבלתי תלויים. ערכי m הם המקדמים התואמים לכל ערך x, ואילו b הוא ערך קבוע. שים לב ש- y,‏ x ו- m עשויים להיות וקטורים. המערך שהפונקציה LINEST מחזירה הוא {mn,mn-1,...m1,b}. כמו כן, הפונקציה LINEST יכולה להחזיר סטטיסטיקת רגרסיה נוספת.

תחביר

LINEST(known_y's, [known_x's], [const], [stats])‎

תחביר הפונקציה LINEST מכיל את הארגומנטים הבאים:

תחביר

  • known_y's    נדרש. ערכה של ערכי y שכבר ידועים בקשר הגומלין y = mx + b.

    • אם הטווח של known_y's נמצא בעמודה בודדת, כל עמודה של known_x's תתפרש כמשתנה נפרד.

    • אם הטווח של known_y's נמצא בשורה בודדת, כל שורה של known_x's תתפרש כמשתנה נפרד.

  • known_x's    אופציונלי. ערכה של ערכי x שייתכן שכבר ידועים בקשר הגומלין y = mx + b.

    • הטווח של known_x's יכול לכלול ערכה אחת או יותר של משתנים. אם נעשה שימוש במשתנה אחד בלבד, known_y's ו- known_x's יכולים להיות טווחים בכל צורה שהיא, כל עוד יש להם ממדים שווים. אם נעשה שימוש ביותר ממשתנה אחד, known_y's חייב להיות וקטור (כלומר, טווח בגובה של שורה אחת או ברוחב של עמודה אחת).

    • אם known_x's מושמט, המערכת מניחה כי הוא המערך ‎{1,2,3,...}‎ באותו הגודל כמו known_y's.

  • const    אופציונלי. ערך לוגי המציין אם לכפות על הקבוע b להיות שווה ל- 0.

    • אם const הוא TRUE או מושמט, b מחושב כרגיל.

    • אם const הוא FALSE, ‏b מוגדר כשווה ל- 0 וערכי m מותאמים לקשר הגומלין y = mx.

  • stats    אופציונלי. ערך לוגי המציין אם להחזיר סטטיסטיקת רגרסיה נוספת.

    • אם stats הוא TRUE, הפונקציה LINEST מחזירה את ססטיסטיקת הרגרסיה הנוספת; כתוצאה מכך, המערך המוחזר הוא {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • אם stats הוא FALSE או מושמט, הפונקציה LINEST מחזירה רק את מקדמי m ואת הקבוע b.

      סטטיסטיקת הרגרסיה הנוספת היא:

נתון סטטיסטי

תיאור

se1,se2,...,sen

ערכי שגיאת התקן עבור המקדמים m1,m2...,mn.

seb

ערך השגיאה הרגיל עבור הקבוע b ‏(seb = #N/A כאשר const הוא FALSE).

r2

מקדם הקביעה. השוואה בין ערכי y משוערים וממשיים, וכן טווחים בערכים החל מ- 0 ועד 1. אם הערך הוא 1, קיים מתאם מושלם במדגם - אין הבדל בין ערך y המשוער לבין ערך y הממשי. מנגד, אם מקדם הקביעה הוא 0, נוסחת הרגרסיה אינה שימושית לחיזוי ערך y. לקבלת מידע אודות אופן חישוב המקדם r2, עיין בסעיף "הערות" בהמשך נושא זה.

sey

שגיאת התקן עבור y המשוער.

F

הנתון הסטטיסטי F, או ערך F שנצפה. השתמש בנתון הסטטיסטי F כדי לקבוע אם קשר הגומילן שנצפה בין המשתנים התלויים והבלתי תלויים הוא מקרי.

df

דרגות החופש. השתמש בדרגות החופש כדי לחפש ערכי F קריטיים בטבלה סטטיסטית. השווה את הערכים שנמצאו בטבלה לנתון הסטטיסטי F שהחזירה הפונקציה LINEST כדי לקבוע רמת מהימנות למודל. לקבלת מידע אודות אופן החישוב של df, עיין בסעיף "הערות" בהמשך נושא זה. דוגמה 4 מציגה את השימוש ב- F וב- df.

ssreg

סכום הרגרסיה של ריבועים.

ssresid

סכום ריבועי השאריות. לקבלת מידע אודות אופן החישוב של ssreg ו- ssresid, עיין בסעיף "הערות" בהמשך נושא זה.

האיור הבא מציג את הסדר שבו מוחזרת סטטיסטיקת הרגרסיה הנוספת.

מפתח של סטטיסטיקת רגרסיה

הערות

  • באפשרותך לתאר כל קו ישר באמצעות השיפוע וחיתוך y:

    Slope (m)‎:
    כדי למצוא את השיפוע של הקו, המצוין לעתים קרובות בתור m, קח שתי נקודות בקו (x1,y1) ו- (x2,y2); השיפוע שווה ל- ‎(y2 - y1)/(x2 - x1)‎.

    Y-intercept (b)‎:
    חיתוך y של הקו, המצוין לעתים קרובות בתור b, הוא הערך של y בנקודת החיתוך של הקו עם ציר y.

    המשוואה של קו ישר היא y = mx + b. לאחר שהערכים של m ושל b ידועים, תוכל לחשב כל נקודה בקו על-ידי הצבת ערך y או ערך x במשוואה זו. ניתן גם להשתמש בפונקציה TREND.

  • כאשר יש משתנה x בלתי תלוי אחד בלבד, ניתן לחשב את ערכי השיפוע וחיתוך y ישירות באמצעות הנוסחאות הבאות:

    Slope:
    ‎=INDEX(LINEST(known_y's,known_x's),1)‎

    Y-intercept:
    ‎=INDEX(LINEST(known_y's,known_x's),2)‎

  • מידת הדיוק של הקו שהפונקציה LINEST מחשבת תלויה בדרגת הפיזור של הנתונים. ככל שהנתונים ליניאריים יותר, כך מידת הדיוק של מודל LINEST גבוהה יותר. הפונקציה LINEST משתמשת בשיטת הריבועים הקטנים ביותר לקביעת ההתאמה הטובה ביותר לנתונים. כאשר יש משתנה x בלתי תלוי אחד בלבד, החישוב של m ו- b מבוסס על הנוסחאות הבאות:

    משוואה

    משוואה

    כאשר x ו- y הם ממוצעי מדגמים; כלומר, ‏x = AVERAGE(known x's‎)‎ ו- y = AVERAGE‎(‎known y'‎s)‎.

  • הפונקציות להתאמת קו ועקומה, LINEST ו- LOGEST, יכולות לחשב את הקו הישר או העקומה המעריכית הטובים ביותר והמתאימים ביותר לנתונים. עם זאת, עליך להחליט איזו משתי התוצאות תואמת בצורה הטובה ביותר לנתונים. באפשרותך לחשב את TREND(known_y's,known_x's)‎ לקבלת קו ישר, או את GROWTH(known_y's, known_x's)‎ לקבלת עקומה מעריכית. פונקציות אלה, ללא הארגומנט new_x's, מחזירות מערך של ערכי y שנחזו לאורך הקו או העקומה בנקודות הנתונים הממשיות. כעת תוכל להשוות בין הערכים החזויים והערכים הממשיים. מומלץ להציג אותם כתרשים לצורך השוואה חזותית.

  • בניתוח רגרסיה, Excel מחשב עבור כל נקודה את ריבוע ההפרש בין ערך y המשוער של אותה נקודה לבין ערך y הממשי. סכום ריבועי ההפרשים נקרא סכום ריבועי השאריות, ssresid. לאחר מכן, Excel מחשב את הסכום הכולל של הריבועים, sstotal. כאשר הארגומנט const שווה ל- TRUE או מושמט, הסכום הכולל של הריבועים הוא סכום ריבועי ההפרשים בין ערכי y הממשיים לבין ממוצע ערכי y. כאשר הארגומנט const שווה ל- FALSE, סכום הריבועים הכולל הוא סכום הריבועים של ערכי y הממשיים (מבלי לחסר את ערך y הממוצע מכל ערך y בודד). לאחר מכן, סכום הרגרסיה של הריבועים, ssreg, ניתן לחישוב באמצעות: ssreg = sstotal - ssresid. ככל שסכום ריבועי השאריות קטן בהשוואה לסכום הריבועים הכולל, כך גדל ערך מקדם הדטרמינציה, r2, המציין עד כמה המשוואה המתקבלת מניתוח הרגרסיה מיטיבה להסביר את קשרי הגומלין בין המשתנים. הערך של r2 שווה ל- ssreg/sstotal.

  • במקרים מסוימים, ייתכן שלעמודת X אחת או יותר (נניח שמשתני Y ו- X נמצאים בעמודות) אין ערך חיזוי נוסף בנוכחות עמודות X האחרות. במילים אחרות, הוצאת עמודת X אחת או יותר עשויה להוביל לערכי Y חזויים מדויקים באותה מידה. במקרה זה, יש להשמיט עמודות X עודפות אלה ממודל הרגרסיה. תופעה זו מכונה "קוליניאריות" משום שניתן לבטא כל עמודת X עודפת כסכום מכפלות של עמודות X שאינן עודפות. הפונקציה LINEST בודקת קוליניאריות ומסירה עמודות X עודפות ממודל הרגרסיה כשהיא מזהה אותן. ניתן לזהות עמודות X שהוסרו בפלט LINEST כבעלות 0 מקדמים, בנוסף ל- 0 ערכי se. אם מתבצעת הסרה של עמודה עודפת אחת או יותר, df מושפע כיוון ש- df תלוי במספר עמודות X המשמשות בפועל למטרות חיזוי. לקבלת פרטים אודות חישוב df, ראה דוגמה 4. אם df משתנה משום שעמודות X עודפות הוסרו, ערכי sey ו- F מושפעים גם הם. בפועל, קוליניאריות אמורה להיות נדירה יחסית. עם זאת, מקרה אחד שבו הסבירות שלה עולה הוא כאשר עמודות X מסוימות מכילות רק ערכי 0 ו- 1 כמחוונים לגבי היותו או אי-היותו של משתתף בניסוי חבר בקבוצה מסוימת. אם const שווה ל- TRUE או מושמט, הפונקציה LINEST מוסיפה עמודת X נוספת של כל ערכי 1 כדי להדגים את נקודת החיתוך. אם יש עמודה עם ערך 1 עבור כל משתתף כדי לציין שהוא זכר, או 0 אם לא, וכן אם יש עמודה עם ערך 1 עבור כל משתתף כדי לציין שהוא נקבה, או 0 אם לא, עמודה אחרונה זו עודפת כיוון שניתן להשיג את הערכים שבה על-ידי חיסור הערך בעמודה "מחוון זכר" מהערך בעמודה הנוספת של כל ערכי 1 שנוספו על-ידי הפונקציה LINEST.

  • הערך של df מחושב באופן הבא כאשר לא מתבצעת הסרה של עמודות X כלשהן מהמודל בשל קוליניאריות: אם יש k עמודות של known_x’s ו- const הוא TRUE או מושמט, df = n – k – 1. אם const הוא FALSE, ‏df = n - k. בשני המקרים, כל עמודת X שהוסרה בשל קוליניאריות מגדילה את הערך של df ב- 1.

  • יש להזין נוסחאות שמחזירות מערכים בתור נוסחאות מערך.

    הערה: ב- Excel Online לא ניתן ליצור נוסחאות מערך.

  • בעת הזנת קבוע מערך (כגון known_x's) כארגומנט, השתמש בפסיקים כדי להפריד בין ערכים הכלולים באותה שורה ובתווי נקודה-פסיק כדי להפריד בין שורות. תווי ההפרדה עשויים להשתנות בהתאם להגדרות האזוריות.

  • שים לב שייתכן שערכי y שמנבאת משוואת הרגרסיה לא יהיו בתוקף אם הם נמצאים מחוץ לטווח ערכי y שבו השתמשת לקביעת המשוואה.

  • האלגוריתם המשמש כבסיס בפונקציה LINEST שונה מהאלגוריתם המשמש כבסיס בפונקציות SLOPE ו- INTERCEPT. ההבדל בין אלגוריתמים אלה עשוי להוביל לתוצאות שונות כאשר הנתונים אינם ידועים וקוליניאריים. לדוגמה, אם נקודות הנתונים של הארגומנט known_y's הן 0 ונקודות הנתונים של הארגומנט known_x's הן 1:

    • הפונקציה LINEST מחזירה ערך 0. האלגוריתם של הפונקציה LINEST נועד להחזיר תוצאות סבירות עבור נתונים קוליניאריים, ובמקרה זה ניתן למצוא לפחות תשובה אחת.

    • הפונקציות SLOPE ו- INTERCEPT מחזירות שגיאת ‎#DIV/0!‎‏. האלגוריתם של הפונקציות SLOPE ו- INTERCEPT נועד לחפש תשובה אחת בלבד, ובמקרה זה יש יותר מתשובה אפשרית אחת.

  • בנוסף לשימוש ב- LOGEST לחישוב סטטיסטיקה עבור סוגי רגרסיה אחרים, באפשרותך להשתמש ב- LINEST לחישוב טווח של סוגי רגרסיה אחרים על-ידי הזנת פונקציות של משתני x ו- y כסדרות x ו- y עבור LINEST. לדוגמה, הנוסחה הבאה:

    ‎=LINEST(yvalues, xvalues^COLUMN($A:$C))‎

    פועלת כאשר יש עמודה יחידה של ערכי y ועמודה יחידה של ערכי x לחישוב התוצאה המעוקבת (פולינומיאלית מסדר 3) בקירוב של התבנית:

    y = m1*x + m2*x^2 + m3*x^3 + b

    באפשרותך להתאים נוסחה זו לחישוב סוגים אחרים של רגרסיה, אך במקרים מסוימים יש צורך להתאים את ערכי הפלט וסטטיסטיקה אחרת.

  • קיים הבדל בין ערכי מבחן F שמחזירה הפונקציה LINEST לערכי מבחן F שמחזירה הפונקציה FTEST. הפונקציה LINEST מחזירה את הנתון הסטטיסטי F, בעוד שהפונקציה FTEST מחזירה את ההסתברות.

דוגמאות

דוגמה 1 - שיפוע וחיתוך Y

העתק את נתוני הדוגמה מהטבלה שלהלן והדבק אותם בתא A1 בגליון עבודה חדש של Excel. כדי שהנוסחאות יציגו תוצאות, בחר אותן, הקש F2 ולאחר מכן הקש Enter. אם יש צורך, באפשרותך להתאים את רוחב העמודות כדי לראות את כל הנתונים.

y ידוע

x ידוע

1

0

9

4

5

2

7

3

תוצאה (שיפוע)

תוצאה (נקודת חיתוך y)

2

1

נוסחה (נוסחת מערך בתאים A7:B7)

‎=LINEST(A2:A5,B2:B5,,FALSE)‎

דוגמה 2 - רגרסיה ליניארית פשוטה

העתק את נתוני הדוגמה מהטבלה שלהלן והדבק אותם בתא A1 בגליון עבודה חדש של Excel. כדי שהנוסחאות יציגו תוצאות, בחר אותן, הקש F2 ולאחר מכן הקש Enter. אם יש צורך, באפשרותך להתאים את רוחב העמודות כדי לראות את כל הנתונים.

חודש

מכירות

1

₪3,100

2

₪4,500

3

₪4,400

4

₪5,400

5

₪7,500

6

₪8,100

נוסחה

תוצאה

‎=SUM(LINEST(B1:B6, A1:A6)*{9,1})‎

₪11,000

חישוב הערכת המכירות בחודש התשיעי, בהתבסס על המכירות בחודשים 1 עד 6.

דוגמה 3 - רגרסיה ליניארית רבת-משתנים

העתק את נתוני הדוגמה מהטבלה שלהלן והדבק אותם בתא A1 בגליון עבודה חדש של Excel. כדי שהנוסחאות יציגו תוצאות, בחר אותן, הקש F2 ולאחר מכן הקש Enter. אם יש צורך, באפשרותך להתאים את רוחב העמודות כדי לראות את כל הנתונים.

שטח קומה (‎x1)

משרדים (‎x2)

כניסות (‎x3)

גיל (‎x4)

אומדן ערך (y)

2310

2

2

20

₪142,000

2333

2

2

12

₪144,000

2356

3

1.5

33

₪151,000

2379

3

2

43

₪150,000

2402

2

3

53

₪139,000

2425

4

2

23

₪169,000

2448

2

1.5

99

₪126,000

2471

2

2

34

₪142,900

2494

3

3

23

₪163,000

2517

4

4

55

₪169,000

2540

2

3

22

₪149,000

‎-234.2371645‎

13.26801148

0.996747993

459.7536742

1732393319

נוסחה (נוסחת המערך שהוזנה ב- A14:A18)

‎=LINEST(E2:E12,A2:D12,TRUE,TRUE)‎

דוגמה 4 - שימוש בסטטיסטיקת F ו- r2

בדוגמה הקודמת, ערך מקדם הקביעה, או r2, הוא 0.99675 (ראה תא A17 בפלט של הפונקציה LINEST), מה שמצביע על קשר הדוק בין המשתנים הבלתי תלויים לבין מחיר המכירה. באפשרותך להשתמש בנתון הסטטיסטי F כדי לקבוע אם תוצאות אלה, כאשר ערך r2 כה גבוה, הן מקריות.

נניח לרגע שלמעשה אין קשר בין המשתנים, אלא שהמדגם בן 11 בנייני המשרדים שנבחר הוא מדגם נדיר הגורם לניתוח הסטטיסטי להראות קשר הדוק. המונח "Alpha" משמש לציון ההסתברות של היסק שגוי שלפיו יש קשר.

ערכי F ו- df בפלט הפונקציה LINEST יכולים לשמש להערכת הסבירות להתרחשות של ערך F גבוה יותר באופן מקרי. ניתן להשוות את F לערכים קריטיים בטבלאות התפלגות F שפורסמו, או שהפונקציה FDIST ב- Excel יכולה לשמש לחישוב הסבירות של התרחשות ערך F גדול יותר באופן מקרי. התפלגות F המתאימה כוללת את דרגות החופש v1 ו- v2. אם n הוא מספר נקודות הנתונים ו- const שווה ל- TRUE או מושמט, v1 = n – df – 1 ו- v2 = df. (אם const שווה ל- FALSE, אז v1 = n – df ו- v2 = df.) הפונקציה FDIST - עם התחביר FDIST(F,v1,v2)‎ - תחזיר את הסבירות לערך F גבוה יותר באופן מקרי. בדוגמה זו, df = 6 (תא B18) ו- F = 459.753674 (תא A18).

בהנחה שערך Alpha הוא 0.05, v1 = 11 – 6 – 1 = 4 ו- v2 = 6, הרמה הקריטית של F היא 4.53. כיוון ש- F = 459.753674 גבוה בהרבה מ- 4.53, אין זה סביר שערך F גבוה כל כך התרחש באופן מקרי. (כאשר Alpha = 0.05, ההשערה שאין קשר בין known_y’s ו- known_x’s נדחית כאשר F חורג מהרמה הקריטית, 4.53.) באפשרותך להשתמש בפונקציה FDIST ב- Excel להשגת הסבירות שערך F גבוה כל כך התרחש באופן מקרי. לדוגמה, FDIST‎(459.753674, 4, 6) = 1.37E-7, סבירות נמוכה ביותר. ניתן להסיק, בין אם על-ידי חישוב הרמה הקריטית של F בטבלה או באמצעות הפונקציה FDIST, שמשוואת הרגרסיה שימושית לחיזוי אומדן הערך של בנייני משרדים באזור זה. זכור שחשוב להשתמש בערכים הנכונים של v1 ו- v2 שחושבו בפיסקה הקודמת.

דוגמה 5 - חישוב נתוני t

מבחן השערה נוסף קובע אם כל אחד ממקדמי השיפוע שימושי לאומדן הערך המשוער של בניין משרדים מדוגמה 3. לדוגמה, כדי לבדוק מובהקות סטטיסטית במקדם הגיל, חלק את -234.24 (מקדם שיפוע הגיל) ב- 13.268 (שגיאת התקן המשוערת למקדמי גיל בתא A15). הנה ערך t שנצפה:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

אם הערך המוחלט של t גבוה מספיק, ניתן להסיק שמקדם השיפוע שימושי באומדן הערך המשוער של בניין משרדים בדוגמה 3. הטבלה הבאה מציגה את הערכים המוחלטים של 4 ערכי t שנצפו.

אם תעיין בטבלה במדריך סטטיסטיקה, תגלה שהערך של t קריטי ודו-זנבי, עם 6 דרגות חופש ו- Alpha = 0.05 הוא 2.447. ניתן למצוא ערך קריטי זה גם באמצעות הפונקציה TINV ב- Excel. TINV(0.05,6) = 2.447. כיוון שהערך המוחלט של t ‏(17.7)‎ גדול מ- 2.447, גיל הוא משתנה חשוב באומדן של הערך המשוער של בנייני משרדים. ניתן לבחון את המובהקות הסטטיסטית של כל אחד מהמשתנים הבלתי תלויים האחרים באופן דומה. להלן ערכי t שנצפו עבור כל אחד מהמשתנים הבלתי תלויים.

משתנה

ערך t שנצפה

שטח קומה

5.1

מספר משרדים

31.3

מספר כניסות

4.8

גיל

17.7

לכל הערכים האלה יש ערך מוחלט גדול מ- 2.447; לפיכך, כל המשתנים המשמשים במשוואת הרגרסיה מועילים לחיזוי השווי המוערך של בנייני משרדים באזור זה.

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×