פונקציות סטטיסטיות ב-Excel:? TREND

תרגומי מאמרים תרגומי מאמרים
Article ID: 828801 - View products that this article applies to.
הרחב הכל | כווץ הכל

On This Page

תקציר

מאמר זה מתאר את הפונקציה TREND ב-Microsoft Office Excel 2003 ובגירסאות מאוחרות יותר של Excel, מדגים כיצד להשתמש בפונקציה ומשווה את תוצאות הפונקציה ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel לתוצאות של TREND בגירסאות מוקדמות יותר של Excel.

הפונקציה TREND מחושבת על ידי קריאה לפונקציה הקשורה אליה, LINEST. מובא סיכום של שינויים נרחבים בנוסחה LINEST ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel ומצוינות השלכותיהם של השינויים על הנוסחה TREND.

מידע אודות Microsoft Excel 2004 עבור Macintosh

הפונקציות הסטטיסטיות ב-Microsoft Excel עבור Macintosh עודכנו באמצעות אותם האלגוריתמים שבאמצעותם עודכנו הפונקציות הסטטיסטיות ב-Excel 2003 ובגירסאות מאוחרות יותר. כל מידע במאמר זה המתאר את אופן פעולת הפונקציה או את השינויים בפונקציה ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel, תקף גם ל-Excel 2004 עבור Macintosh.

מידע נוסף

הפונקציה TREND(known_y's, known_x's, new_x's, constant)? משמשת לביצוע רגרסיה ליניארית. נעשה שימוש בקריטריון הריבועים הפחותים ו-TREND מנסה למצוא את הנתון המתאים ביותר במסגרת קריטריון זה. Known_y's מייצג נתונים ב"משתנה התלוי" ו- known_x's מייצג נתונים ב"משתנה בלתי תלוי" אחד או יותר. קובץ העזרה של TREND מתאר מקרים נדירים שבהם תיתכן השמטה של הארגומט השני או הארגומנט השלישי.

אם הארגומנט האחרון, "constant", מוגדר כ-TRUE, רצוי שמודל הרגרסיה יכלול מקדם לנקודת החיתוך במודל הרגרסיה. אם הארגומנט האחרון מוגדר כ-FALSE, לא יהיו נקודות חיתוך; הרגרסיה המותאמת נאלצת לעבור דרך המקור. הארגומנט האחרון הנו אופציונלי; אם הוא מושמט, הוא מתפרש כ-TRUE.

למען נוחות ההצגה בהמשך המאמר, נניח שהנתונים מסודרים בעמודות באופן ש-known_y's הוא עמודה של נתוני y ו-known_x's הוא עמודה אחת או יותר של נתוני x. מובן שהממדים (אורכים) של כל אחת מעמודות אלה חייבים להיות שווים. כמו כן, נניח כי גם new_x's מסודר בעמודות וכי מספר העמודות של new_x's ומספר העמודות של known_x's חייב להיות שווה. כל המסקנות במאמר זה חלות גם במקרה שהנתונים אינם מסודרים בעמודות, אבל קל יותר לדון במקרה בודד זה (שהוא המקרה השכיח ביותר).

לאחר חישוב מודל הרגרסיה בעל ההתאמה הטובה ביותר (למעשה, על-ידי קריאה לפונקציה LINEST של Excel), הפונקציה TREND מחזירה ערכים חזויים הקשורים ל-new_x's.

במאמר זה מובאות דוגמאות המציגות את היחס בין TREND לבין LINEST והמצביעות על בעיות הקשורות ב-LINEST ב-Microsoft Excel 2002 ובגירסאות מוקדמות יותר של Excel. בעיות אלו מיתרגמות לבעיות ב-TREND. אף שהקוד של TREND לא נכתב מחדש עבור Excel 2003 ועבור גירסאות מאוחרות יותר של Excel, בקוד של LINEST הוכנסו שינויים (ושיפורים) נרחבים.

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

כתוספת למאמר זה, מומלץ לקרוא את המאמר הבא בנושא LINEST. הוא מכיל כמה דוגמאות ומתעד בעיות הקשורות ב-LINEST ב-Excel 2002 ובגירסאות מוקדמות יותר של Excel.

לקבלת מידע נוסף, לחץ על מספר המאמר שלהלן כדי להציגו מתוך מאגר הידע Microsoft Knowledge Base:
828533 תיאור הפונקציה LINEST ב-Excel 2003 וב-Excel 2004 עבור Mac (ייתכן שקישור זה מפנה לתוכן שחלק ממנו או כולו מופיע באנגלית)


מכיוון שמאמר זה מתמקד בבעיות מספריות ב-Excel 2002 ובגירסאות מוקדמות יותר של Excel, הוא אינו כולל דוגמאות מעשיות רבות לאופן השימוש ב-TREND. קובץ העזרה של TREND כולל דוגמאות שימושיות.

תחביר

TREND(known_y's, known_x's, new_x's, constant)?
הארגומנטים known_y's?, known_x's, ו-new_x's חייבים להיות מערכים או טווחי תאים עם ממדים קשורים. אם known_y's הוא עמודה אחת על m שורות, אזי known_x's הוא c עמודות על m שורות, כאשר c גדול או שווה לאחד. שים לב ש-c הוא מספר המשתנים המנבאים. m הוא מספר נקודות הנתונים. אם כן, New_x's חייב להיות c עמודות על r שורות, כאשר r גדול או שווה לאחד (חייבים להישמר יחסים דומים בממדים אם הנתונים ערוכים בשורות במקום בעמודות). ה-constant הוא ארגומנט לוגי שיש להגדיר כ-TRUE או כ-FALSE (או 0 או 1, אשר מפורשים ב-Excel כ-FALSE או כ-TRUE, בהתאמה). שלושת הארגומנטים האחרונים כלפי TREND הם אופציונליים; עיין בקובץ העזרה של TREND כדי לבדוק אפשרויות השמטה של הארגומנט השני, של הארגומנט השלישי או של שניהם. כאשר משמיטים את הארגומנט הרביעי, הוא מתפרש כ-TRUE.

בשימושים הנפוצים ביותר, הפונקציה TREND כוללת שני טווחי תאים המכילים את הנתונים, למשל TREND(A1:A100, B1:F100, B101:F108, TRUE)?. שים לב שמכיוון שבדרך כלל קיימים יותר ממשתנה פרידקטור אחד, הארגומנט השני בדוגמה זו מכיל מספר עמודות. בדוגמה זו קיימים מאה נושאים, ערך משתנה תלוי אחד (known_y) לכל נושא וחמישה ערכי משתנים תלויים (known_x's) לכל נושא. קיימים שמונה נושאים היפותטיים נוספים, כאשר ברצונך להשתמש ב-TREND לחישוב ערכי y חזויים.

דוגמה לשימוש

להמחשת המושגים החשובים הבאים, מובא גיליון עבודה לדוגמה של Excel:
  • כיצד TREND מקיים אינטראקציה עם LINEST
  • בעיות שמתרחשות בגלל known_x's קוליניארי ב-TREND (או ב-LINEST) ב-Excel 2002 ובגירסאות מוקדמות יותר של Excel
דיון נרחב בנקודת התבליט השנייה בהקשר של LINEST מובא במאמר על LINEST.

להמחשת הקוליניאריות של TREND, צור גיליון עבודה ריק של Excel, העתק את הטבלה הבאה, בחר בתא A1 בגיליון העבודה הריק של Excel שיצרת ולאחר מכן הדבק את הערכים כך שהטבלה שהלן תמלא את התאים A1:K35 בגיליון העבודה.
כווץ את הטבלההרחב את הטבלה
y:x's:
1121
2341
3451
4671
5781
new x's:911
1214
TREND בעמודות B?,C:ערכים בגירסאות מוקדמות יותר מ-Excel 2003:ערכים ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel:
????=TREND(A2:A6,B2:C6,B7:C8,TRUE)??#NUM!?6.15789473684211
?=TREND(A2:A6,B2:C6,B7:C8,TRUE)??#NUM!?8.13157894736842
TREND בעמודה B בלבד
??=TREND(A2:A6,B2:B6,B7:B8,TRUE)?6.15789473684216.15789473684211
?=TREND(A2:A6,B2:B6,B7:B8,TRUE)?8.131578947368428.13157894736842
ערכים מותאמים מתוצאות של LINEST ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel
עמודות B?, Cבעמודה B
?= K24*1 + J24*B7 + I24*C7?=J31*1+I31*B7
?=K24*1 + J24*B8 + I24*C8?=J31*1 +I31*B8
LINEST בעמודות B??,C:ערכים בגירסאות מוקדמות יותר מ-Excel 2003:ערכים ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel:
?=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??#NUM!??#NUM!??#NUM!?00.6578947368421050.236842105263158
?=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??#NUM!??#NUM!??#NUM!?00.0438596491228070.206652964726136
?=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??#NUM!??#NUM!??#NUM!?0.9868421052631580.209426954145848?#N/A
?=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??#NUM!??#NUM!??#NUM!?2253?#N/A
?=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??=LINEST(A2:A6,B2:C6,TRUE,TRUE)??#NUM!??#NUM!??#NUM!?9.868421052631580.131578947368421?#N/A
LINEST בעמודה B בלבד
?=LINEST(A2:A6,B2:B6,TRUE,TRUE)??=LINEST(A2:A6,B2:B6,TRUE,TRUE)?0.6578947368421050.2368421052631590.6578947368421050.236842105263158
?=LINEST(A2:A6,B2:B6,TRUE,TRUE)??=LINEST(A2:A6,B2:B6,TRUE,TRUE)?0.04385964912280710.2066529647261360.0438596491228070.206652964726136
?=LINEST(A2:A6,B2:B6,TRUE,TRUE)??=LINEST(A2:A6,B2:B6,TRUE,TRUE)?0.9868421052631580.2094269541458480.9868421052631580.209426954145848
?=LINEST(A2:A6,B2:B6,TRUE,TRUE)??=LINEST(A2:A6,B2:B6,TRUE,TRUE)?224.99999999999932253
?=LINEST(A2:A6,B2:B6,TRUE,TRUE)??=LINEST(A2:A6,B2:B6,TRUE,TRUE)?9.868421052631580.1315789473684219.868421052631580.131578947368421
לאחר שתדביק טבלה זו בגיליון העבודה החדש ב-Excel, לחץ על אפשרויות הדבקה ולאחר מכן לחץ על התאם עיצוב יעד. כאשר הטווח שהודבק עדיין מסומן, בצע אחד מההליכים הבאים, בהתאם לגירסת Excel שבה אתה משתמש:
  • ב-Microsoft Office Excel 2007, לחץ על הכרטיסייה בית, לחץ על עיצוב בקבוצה תאים ולאחר מכן, לחץ על התאם אוטומטית לרוחב עמודה.
  • ב-Excel 2003, הצבע על עמודה בתפריט עיצוב ולאחר מכן לחץ על התאם אוטומטית לבחירה.
הנתונים של TREND נמצאים בתאים A1:C8 (הערכים בתאים D2:D6 אינם חלק מהנתונים, אך הם משמשים להמחשה בהמשך המאמר). התוצאות של TREND לשני מודלים שונים בגירסאות מוקדמות יותר של Excel ובגירסאות מאוחרות יותר של Excel מוצגות בתאים E10:E16 ובתאים I10:I16, בהתאמה. התוצאות בתאים A10:A16 יתאימו לגירסת Excel שבה הנך משתמש. לעת עתה, יתמקד מאמר זה בתוצאות ב-Excel 2003 ובגירסאות מאוחרות יותר, כאשר יבדוק כיצד TREND קוראת ל-LINEST וכיצד TREND משתמשת בתוצאות LINEST.

אפשר להציג את האינטראקציה בין TREND ל-LINEST באופן הבא:
  1. אתה קורא ל-TREND(known_y's, known_x's, new_x's, constant)?.
  2. TREND קורא ל-LINEST(known_y's, known_x's, constant, TRUE?)?.
  3. קריאה זו ל-LINEST משיגה מקדמי רגרסיה; מקדמים אלו מופיעים בשורה הראשונה של טבלת הפלט של LINEST.
  4. לכל שורת new_x's, מחושב ערך y חזוי לפי מקדמי LINEST אלו ולפי ערכי new_x's בשורה זו.
  5. הערך המחושב בשלב 4 מוחזר בתא המתאים בפלט TREND המקביל לאותה שורת new_x's.
כדי ש-TREND יחזיר תוצאות מתאימות, LINEST צריך ליצור את התוצאות המתאימות בשלב 3. בעיות בשלב זה מקורן בעמודות מנבאות קוליניאריות.

עמודות מנבאות (known_x's) הן קוליניאריות אם אפשר לבטא עמודה אחת לפחות, c, כסכום המכפלות של עמודות אחרות, c1,? c2 ושל עמודות אחרות. עמודה c מכונה לעתים קרובות עודפת, מכיוון שאת המידע הכלול בה ניתן לבנות מהעמודות c1?, c2 ומעמודות אחרות. העיקרון היסודי שבהופעת קוליניאריות הוא שהתוצאות אינן צריכות להיות מושפעות מהוספת עמודה עודפת לנתונים המקוריים או מהסרתה מהם. מאחר שב-Excel 2002 ובגירסאות מוקדמות יותר של Excel?, LINEST לא איתר קוליניאריות, עיקרון זה הופר בקלות. עמודות מנבאות הן כמעט קוליניאריות אם אפשר לבטא עמודה אחת לפחות, c, ככמעט שווה לסכום המכפלות של עמודות אחרות, c1,? c2 ועמודות אחרות. במקרה זה "כמעט שווה" פירושו סכום קטן מאוד של ריבועי סטיות של ערכים ב-c מהערכים המתאימים בסכום המשוקלל של c1?, c2 ושל עמודות אחרות; "קטן מאוד" יכול להיות קטן מ-?10^(-12)? לדוגמה.

המודל הראשון, בשורות 10 עד 12, משתמש בעמודות B ו-C כעמודות מנבאות ומבקש מ-Excel לדגם את ה-constant (הארגומנט האחרון מוגדר כ-TRUE). בשלב זה, Excel למעשה מוסיף עמודה פרדיקטורית נוספת הנראית בדיוק כמו התאים D2:D6. קל לראות שהערכים המופיעים בעמודה C בשורות 2 עד 6 שווים במדויק לסכום הערכים המתאימים בעמודות B ו- D. לכן, קיימת קוליניאריות, מכיוון שעמודה C היא סכום המכפלות של:
  • עמודה B
  • עמודת Excel נוספת של ערכי 1, שמתווספת מכיוון שהארגומנט השלישי של LINEST (כמו הארגומנט הרביעי של TREND) הושמט או הוגדר כ-TRUE (המקרה ה"רגיל").
הדבר גורם לבעיות מספריות כאלה, שבעטיין Excel 2002 וגירסאות מוקדמות יותר של Excel לא יכלו לחשב תוצאות ולכן טבלת הפלט של TREND אוכלסה ב-!??#NUM.

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

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

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

ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel, הודעה כזאת אינה מועברת כהתראה או כמחרוזת טקסט, אלא בטבלת הפלט של LINEST. ב-TREND אין כל מנגנון לשליחת הודעה כזאת אליך. בטבלת הפלט של LINEST, מקדם רגרסיה שהוא אפס וששגיאת התקן שלו היא אפס מתאים למקדם של עמודה שהוסרה מהמודל. טבלאות הפלט של LINEST מופיעות בשורות 23 עד 35 בהתאמה לפלט של TREND בשורות 10 עד 16. הערכים בתאים I24:I25 מראים עמודה פרדיקטורית עודפת שהוצאה. במקרה זה, LINEST בחרה להסיר את עמודה C (המקדמים בתאים I24,? J24,? K24 מתאימים לעמודות C,? B, ולעמודת ה-constant של Excel בהתאמה). כאשר קיימת קוליניאריות, ניתן להסיר כל אחת מהעמודות המעורבות והבחירה היא שרירותית.

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

כמו כן, אם תבחן את פלט LINEST ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel בתאים I23:K35, תראה ששלוש השורות האחרונות של טבלאות הפלט זהות ושהערכים בתאים I31:J32 ובתאים J24:K25 חופפים. האמור לעיל מוכיח שמתקבלות תוצאות זהות כאשר עמודה C נכללת במודל אך נמצאת עודפת (פלט בתאים I24:K28) כמו במקרה שבו עמודה C הוצאה לפני הפעלת LINEST (פלט בתאים I31:J35). מסקנה זו תואמת לעיקרון הבסיסי של הקוליניאריות.

בתאים A18:C21, מובאים במאמר זה נתונים מ-Excel 2003 ומגירסאות מאוחרות יותר של Excel כדי להמחיש כיצד TREND לוקח את הפלט של LINEST ומחשב את ערכי y החזויים הרלוונטיים. אם תבדוק את הנוסחאות בתאים A20:A21 ובתאים C20:C21, תראה כיצד המקדמים של LINEST משולבים בנתוני new_x's בתאים B7:C8 בכל אחד מהמודלים (בעמודות B?, C כמנבאות; בעמודה B בלבד כמנבאת).

LINEST מזהה קוליניאריות ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel בגלל גישה שונה לחלוטין לפתרון מקדמי הרגרסיה. גישה זו נקראית 'פירוק QR'. במאמר על LINEST מתואר מהלך הפעולה של האלגוריתם 'פירוק QR' בדוגמה קטנה.

סיכום התוצאות בגירסאות קודמות של Excel

ב-Excel 2002 ובגירסאות מוקדמות יותר, תוצאות TREND מושפעות לרעה מתוצאות בלתי מדויקות של LINEST.

LINEST חושב בשיטה שלא התייחסה כלל לענייני קוליניאריות. קיום הקוליניאריות גרם לשגיאות בעיגול מספרים, לשגיאות תקן לא תקינות של מקדמי רגרסיה ולרמות חופש לא תקינות. לעתים, היו בעיות עיגול המספרים חמורות מאוד ו-LINEST מילא את טבלת הפלט ב-?#NUM!?.

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

סיכום התוצאות ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel

השיפורים ב-LINEST כוללים מעבר לשיטת 'פירוק QR' לצורך קביעת מקדמי הרגרסיה. לשיטת 'פירוק QR' היתרונות הבאים:
  • יציבות מספרית טובה יותר (בדרך כלל שגיאות קטנות יותר בעיגול מספרים)
  • ניתוח של בעיות של קוליניאריות
כל הבעיות הקיימות ב-Excel 2002 ובגירסאות מוקדמות יותר של Excel המומחשות במאמר זה תוקנו ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel.

מסקנות

הביצועים של TREND השתפרו, מכיוון ש-LINEST שופר מאוד ב-Excel 2003 ובגירסאות מאוחרות יותר של Excel. אם אתה משתמש בגירסה קודמת של Excel, ודא שהעמודות המנבאות אינן קוליניאריות, לפני השימוש ב-TREND.

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

בנוסף, השיפורים ב-LINEST משפיעים לטובה גם על כלי הרגרסיה הליניארית של Analysis ToolPak (כלי זה קורא ל-LINEST) ועל שתי פונקציות אחרות של Excel הקשורות לפונקציה LINEST:? LOGEST ו-GROWTH.

מאפיינים

Article ID: 828801 - Last Review: יום שישי 23 פברואר 2007 - Revision: 4.0
המידע במאמר זה חל על:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
מילות מפתח 
kbformula kbexpertisebeginner kbinfo KB828801

ספק משוב

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com