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

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

סוגי רגרסיה

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

• ליניארי;
• פרבולית;
• כוח;
• מעריכים;
• היפרבולי;
• מעריכים;
• לוגריתמים.

דוגמא 1

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

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

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

עבור בעיית קביעת התלות של העובדים הפרדות מסכום המשכורת הממוצעת מודל רגרסיה 6 מפעלים יש צורה של המשוואה Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, כאשר x _i - משתנים המשפיעים, a _i - מקדמי רגרסיה, ak - מספר גורמים.

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

רתימת כוחו של גיליון אלקטרוני "אקסל"

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

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

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

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

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

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

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

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

נתוני Excel שהושגו נתונים לדוגמה הנחשבים הם בפורמט הבא:

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

ניתוח יחס

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

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

רגרסיה מרובה

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

y = f (x ₁ + x ₂ + ... x _{מ ')} + ε, כאשר y - הוא ציון תכונה (המשתנה התלוי), ו- x _1, x _2, ... x _מ' - הם גורמים סימנים (משתנים בלתי תלויים).

להערכת פרמטר

עבור רגרסיה מרובה (MR) היא נעשית באמצעות שיטת ריבועים פחותה (LSM). עבור משוואות ליניאריות מהצורה Y = a + b ₁ x _{1 + ... + b m x m +} ε בניית מערכת של משוואות נורמליים (ס"מ. להלן)

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

לפיכך, נקבל:

איפה σ - הוא מהשונות של התכונה בהתאמה, באה לידי ביטוי במדד.

MNC ישימה המשוואה MR כדי standartiziruemom מידה. במקרה זה, אנחנו מקבלים את המשוואה:

שבו t _y, t _{x 1, ...} t _XM - standartiziruemye משתנה עבורו ערכים ממוצעים הם 0; בטא _i - מקדם רגרסיה סטנדרטי וסטיית תקן - 1.

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

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

נניח שיש לך טבלה של הדינמיקה של מחיר של N מוצר מסוים עבור 8 החודשים האחרונים. יש צורך להחליט האם רכישת מפלגתו במחיר של 1850 רובל. / ט

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

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

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

המחיר של הסחורה N = 11,714 * 1727.54 + מספר חודשים.

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

y = 11,714 x + 1727,54

ניתוח של תוצאות

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

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

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

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

הערך של t-נתון (מבחן t של סטודנט) מסייע להעריך את המשמעות של מקדם בכל חבר תלות ליניארית ידוע בחינם. אם הערך של מבחן t> _{CR t,} ההשערה של חוסר משמעות משוואה ליניארית של מונח חינם נדחית.

בבעיה זו לתקופה בחינם באמצעות מכשירים "אקסל" נמצא כי t = 169,20903, ו- p = 2,89E-12, T. E. יש סיכוי אפס כי נאמן יידחו את ההשערה של חוסר המשמעות של המונח בחינם. עבור מקדם ידוע בזמן t = 5,79405, ו- p = 0,001158. במילים אחרות, ההסתברות כי השערה נכונה דחה יהיה חוסר המשמעות של מקדם לבלתי-נודע, הוא 0.12%.

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

הבעיה של כדאיות רכישת מניות

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

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

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

בנוסף, השתמש חובות השכר של המפעלים (V3 U) באלפי דולרים.

מעבד שולחן החלטת אמצעי Excel

ראשית, עליך ליצור טבלה של נתוני קלט. זה הוא כדלקמן:

הבא:

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

סמן את הנקודה "גיליון חדש" ולחץ על "אישור".

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

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

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

SD = 0.103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265,844.

בשינה בצורת המתמטית יותר מהרגילה זה יכול להיות כפי שנכתב:

y = 0103 * X1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * X5 - 265,844

נתונים עבור «MMM» JSC המוצגים בטבלה שלהלן:

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 מיליון דולר ארה"ב. משמעות הדבר היא כי מניות של JSC «MMM» לא צריך לקנות, בגלל העלות שלהם במחיר מופקע למדי ב -70 מיליון דולר ארה"ב.

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

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