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

תיאור

הפונקציה 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; r^2,sey; F,df; ssreg,ssresid}.

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

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

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

הערות

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

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

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

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

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

  מדרון:
  =INDEX(LINEST(known_y's,known_x's),1)

  יירוט Y:
  =INDEX(LINEST(known_y's,known_x's),2)

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

  

  

  כאשר x ו- y הם אמצעי מדגם; that is, x = AVERAGE(known x's) and y = AVERAGE(known_y's).

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

• בניתוח רגרסיה, Excel מחשב עבור כל נקודה את ריבוע ההפרש בין ערך y המשוער של אותה נקודה לבין ערך y הממשי. סכום ריבועי ההפרשים נקרא סכום ריבועי השאריות, ssresid. לאחר מכן, Excel מחשב את הסכום הכולל של הריבועים, sstotal. כאשר הארגומנט const שווה ל- TRUE או מושמט, הסכום הכולל של הריבועים הוא סכום ריבועי ההפרשים בין ערכי y הממשיים לבין ממוצע ערכי y. כאשר הארגומנט const שווה ל- FALSE, סכום הריבועים הכולל הוא סכום הריבועים של ערכי y הממשיים (מבלי לחסר את ערך y הממוצע מכל ערך y בודד). לאחר מכן, סכום הרגרסיה של הריבועים, ssreg, ניתן לחישוב באמצעות: ssreg = sstotal - ssresid. הסכום השיריד של הריבועים קטן יותר בהשוואה לסכום הריבועים הכולל, כך גדל הערך של מקדם הקביעה, r², שהוא סימן לאופן שבו המשוואה המתרחבת כתוצאה מניתוח הרגרסיה מסבירה את הקשר בין המשתנים. הערך של r² שווה ל- 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.

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

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

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

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

  • הפונקציות SLOPE ו- INTERCEPT מחזירות #DIV/0! שגיאת ‎#REF!‎. האלגוריתם של הפונקציות 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 מחזירה את ההסתברות.

דוגמאות