רגרסיה ב- Excel: משוואה, דוגמאות. רגרסיה ליניארית

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

סוגי רגרסיה

הרעיון עצמו הוכנס למתמטיקה על ידי פרנסיס גאלטון בשנת 1886. רגרסיה מתרחשת:

• ליניארי;
• פרבוליות;
• חוק כוח;
• אקספוננציאלי;
• היפרבולי;
• אינדיקציה;
• לוגריתמי.

דוגמה 1

הבה נבחן את הבעיה של קביעת התלות של מספר חברי בדימוס של קולקטיבית על השכר הממוצע ב 6 מפעלים תעשייתיים.

המשימה. שש חברות ניתחו את השכר החודשי הממוצע ואת מספר העובדים אשר פרשו מרצונם. בצורה טבלאית יש לנו:

	א	ב	ג
1	X	מספר התפטר	שכר
2		y	30000 רובל
3	1	60	35,000 רובל
4	2	35	40000 רובל
5	3	20	45,000 רובל
6	4	20	50,000 רובל
7	5	15	55,000 רובל
8	6	15	60000 רובל

עבור הבעיה של קביעת התלות של מספר העובדים שעזבו את השכר הממוצע ב 6 ארגונים, מודל רגרסיה יש את הטופס של המשוואה Y =₀ + א₁x₁ + ... + a_kx_k, שם x_i - משפיעים על משתנים, א_i - מקדמי רגרסיה ו - k - מספר גורמים.

עבור משימה זו, Y הוא אינדיקטור של העובדים שעזבו, ואת הגורם המשפיע הוא השכר, אשר אנו מציינים על ידי X.

שימוש במעבד Excel Table

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

• מתוך הכרטיסייה "קובץ" עבור אל הקטע "אפשרויות";
• בחלון שנפתח בחר את השורה "תוספות";
• לחץ על כפתור "Go" הממוקם למטה, משמאל לקו "ניהול";
• שים סימן ביקורת ליד השם "חבילת ניתוח" ואשר את הפעולות שלך על ידי לחיצה על "אישור".

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

רגרסיה ליניארית ב - Excel

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

• לחץ על כפתור "ניתוח נתונים";
• בחלון פתוח לחץ על כפתור "רגרסיה";
• כרטיסייה שמופיעה להציג מגוון של ערכי Y (מספר עובדי פרדות) ו- X (המשכורת שלהם);
• אנו מאשרים את הפעולות שלנו על ידי לחיצה על הלחצן "אישור".

כתוצאה מכך, התוכנית תתמלא באופן אוטומטיחדש טבלאי בטבלה מעבד נתונים ניתוח רגרסיה - -. שימו לב! ב- Excel, יש הזדמנות להגדיר באופן עצמאי את המקום שאתה מעדיף למטרה זו. לדוגמה, זה יכול להיות אותו גיליון כמו Y ו- X ערכים, או אפילו ספר חדש שתוכנן במיוחד כדי לאחסן נתונים כאלה.

ניתוח תוצאות רגרסיה עבור ריבוע R

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

קודם כל, יש צורך לשים לבאת הערך של הכיכר R. זהו מקדם הנחישות. בדוגמה זו, R = 0.755 (75.5%), כלומר הפרמטרים המחושבים של המודל מסבירים את התלות בין הפרמטרים הנחשבים על ידי 75.5%. ככל שהערך של מקדם הקביעה גבוה יותר, המודל הנבחר נחשב ליישומי יותר עבור משימה מסוימת. הוא האמין כי הוא מתאר נכונה את המצב האמיתי עם ערך ריבועי R מעל 0.8. אם ריבוע R הוא <0.5, אז ניתוח כזה של רגרסיה ב- Excel לא יכול להיחשב סבירה.

ניתוח המקדמים

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

המקדם הבא הוא -0.166285, הממוקם בB18 תא מראה את השפעת המשקל של X משתנה י משמעות הדבר היא כי השכר הממוצע של העובדים בתוך המודל משפיע על מספר ההתפטרויות מן המשקל של -0.16285, t. E. מידת ההשפעה שלה הוא קטן למדי. הסימן "-" מציין כי מקדם שלילי. ברור, שהרי כולנו יודעים כי המשכורת יותר בארגון, האנשים פחות הביעו רצון להפסיק את חוזה עבודה או פוטר.

רגרסיה מרובה

לפי מונח זה אנו מתכוונים למשוואת הקשר עם מספר משתנים בלתי תלויים של הטופס:

y = f (x₁+ x₂+ ... x_מ) + ε, כאשר y היא התכונה וכתוצאה מכך (משתנה תלוי) ו- x₁, x₂, x_מ - אלה הם סימני גורמים (משתנים בלתי תלויים).

הערכת פרמטרים

עבור רגרסיה מרובה (MP), היא מבוצעת בשיטת ריבועים פחות (OLS). עבור משוואות לינאריות של הטופס Y = a + b₁x₁ + ... + b_מx_מ+ ε אנו בונים מערכת של משוואות נורמליות (ראה להלן)

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

לפיכך אנו מקבלים:

כאשר σ הוא השונות של המאפיין המקביל המשתקף במדד.

OLS חל על משוואת MP בסולם סטנדרטי. במקרה זה נקבל את המשוואה:

שבו t_y, t_{x1, ...}t_xm - המשתנים הסטנדרטיים בהם הממוצעים הם 0; β_i - מקדמי רגרסיה סטנדרטיים וסטיית התקן היא 1.

שים לב כי כל β_i במקרה זה, הם ניתנים כפי מנורמל וריכוזית, ולכן ההשוואה ביניהם נחשבת נכונה ומקובלת. בנוסף, נהוג לסנן את הגורמים, להשליך את אלה שיש להם את הערכים הנמוכים ביותר של βi.

הבעיה באמצעות משוואת רגרסיה לינארית

נניח שיש טבלה של הדינמיקה מחיר של סחורה מסוימת N בתוך 8 החודשים האחרונים. יש צורך לקבל החלטה על התועלת של רכישת אצווה שלו במחיר של 1,850 רובל לטון.

	א	ב	ג
1	חודש	שם החודש	מחיר לנפש
2	1	ינואר	1750 רובל לטון
3	2	פברואר	1755 רובל לטון
4	3	מרץ	1767 רובל לטון
5	4	אפריל	1760 רובל לטון
6	5	מאי	1770 רובל לטון
7	6	יוני	1790 רובל לטון
8	7	יולי	1810 רובל לטון
9	8	אוצר	1840 רובל לטון

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

אנו בונים עליהם משוואה ליניארית של הצורה y = ax + b, שםהפרמטרים של a ו- b הם מקדמי השורה עם שם מספר החודש והמקדמים והקווים "Y- צומת" מהגיליון עם תוצאות ניתוח הרגרסיה. לפיכך, משוואת הרגרסיה הליניארית (VR) לבעיה 3 נכתבת כך:

מחיר הסחורה N = 11,714 * מספר החודש + 1727,54.

או בסימון אלגברי

y = 11.714 x + 1727.54

ניתוח התוצאות

להחליט אם המשוואה המתקבלתרגרסיה ליניארית, מקדמי המתאמים המרובים (KMC) וקביעתם, כמו גם קריטריון פישר ומבחן הסטודנטים. בטבלת Excel עם תוצאות רגרסיה הם מופיעים תחת שמות של R מרובים, R-squared, F- סטטיסטיקה וסטטיסטיקה, בהתאמה.

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

ריבוע מקדם הקביעה R²(RI) הוא מאפיין מספרימנתח את התפלגות סך כל ומראה את הפיזור של איזה חלק של נתונים ניסיוניים, כלומר. הערכים של המשתנה התלוי תואמים את משוואת הרגרסיה הליניארית. בבעיה הנדונה, ערך זה הוא 84.8%, דהיינו, הנתונים הסטטיסטיים מתוארים ברמה גבוהה של דיוק על ידי SD שהתקבלו.

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

ערך הסטטיסטיקה (מבחן הסטודנט) עוזר להעריך את משמעות המקדם עבור מונח לא ידוע או חופשי של התלות הליניארית. אם הערך של מבחן t> t_style, אז ההשערה של חוסר המשמעות של המונח החופשי של המשוואה ליניארית נדחית.

בבעיה הנדונה לתקופה החופשיתבאמצעות כלי ה- Excel נמצא כי t = 169.20903 ו- p = 2.89E-12, כלומר, יש לנו את ההסתברות אפס כי ההשערה הנכונה על חוסר חשיבות של המונח חינם יידחו. עבור מקדם לא ידוע = 5.79405, ו p = 0.001158. במילים אחרות, ההסתברות שההשערה הנכונה בדבר חוסר המשמעות של המקדם עבור הלא ידוע תידחה 0.12%.

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

הבעיה של תועלתיות של קניית בלוק של מניות

רגרסיה מרובה ב- Excel מבוצעת באמצעות אותו כלי "ניתוח נתונים". הבה נבחן בעיה ספציפית.

חברת ניהול "NNN" צריך לקבל החלטהעל כדאיות רכישת נתח של 20% MMM. עלות החבילה (SP) הוא 70 מיליון דולר ארה"ב. מומחי "NNN" אספו נתונים על עסקאות דומות. הוחלט להעריך את שווי האחזקה בפרמטרים האמורים, המתבטא במיליוני דולרים, כדלקמן:

• חשבונות זכאים (VK);
• נפח מחזור שנתי (VO);
• חשבונות חייבים (VD);
• ערך הנכסים הקבועים (SOF).

בנוסף, פרמטר שכר arrears הארגון (V3 P) משמש אלפי דולרים.

פתרון באמצעות גיליון אלקטרוני של Excel

קודם כל, אתה צריך ליצור טבלה של נתוני קלט. יש לה את הטופס הבא:

Post

• התקשר לחלון "ניתוח נתונים";
• בחר את הקטע "רגרסיה";
• בתיבה "Input Interval Y" הזן את טווח הערכים של המשתנים התלויים מעמוד G;
• לחץ על הסמל עם החץ האדום מימין לחלון "מרווח קלט X" ובחר את טווח כל הערכים מהעמודות B, C, D, F על הגיליון.

סמן את הפריט "גליון עבודה חדש" ולחץ על "אישור".

ניתוח רגרסיה מתקבל למשימה זו.

תוצאות מחקר ומסקנות

"איסוף" מהנתונים המעוגלים המוצגים לעיל בגליון גיליון אלקטרוני של Excel, משוואת הרגרסיה:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

בצורה מתמטית מוכרת יותר, ניתן לרשום אותה כך:

y = 0.103 * x1 + 0.541 * x2 - 0.031 * x3 + 0.405 * x4 + 0.691 * x5 - 265.844

הנתונים עבור MMM הם כדלקמן:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45,2	41,5	21,55	64,72

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

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

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