الانحدار في Excel: أمثلة المعادلة. الانحدار الخطي

تحليل الانحدار - طريقة دراسة إحصائية لإظهار الاعتماد من معلمة واحدة أو أكثر من المتغيرات المستقلة. في عصر ما قبل الكمبيوتر، وقد تم استخدامه من الصعب إلى حد ما، خصوصا عندما يتعلق الأمر كميات كبيرة من البيانات. اليوم، وتعلم كيفية بناء الانحدار في Excel، يمكنك حل مشاكل إحصائية معقدة في غضون دقائق معدودة. وفيما يلي أمثلة محددة من الاقتصاد.

أنواع الانحدار

وقدم هذا المفهوم الرياضيات فرانسيس غالتون في عام 1886. الانحدار هو:

• الخطية.
• مكافئ.
• السلطة؛
• الأسي.
• القطعي.
• الأسي.
• وغاريتمي.

مثال 1

النظر في مشكلة تحديد اعتماد عدد من الاستقالات من أعضاء هيئة التدريس من متوسط الأجر في المشروعات الصناعية 6.

المهمة. وقد حللت ست شركات متوسط الراتب الشهري وعدد من الموظفين الذين يقلعون عن التدخين طوعا. في شكل جدول لدينا:

	A	B	C
1	X	عدد من الاستقالات	راتب
2		ذ	30000 روبل
3	1	60	35000 روبل
4	2	35	40000 روبل
5	3	20	45000 روبل
6	4	20	50000 روبل
7	5	15	55000 روبل
8	6	15	60000 روبل

لمشكلة تحديد تبعية العمال فصل كمية من متوسط الراتب لمدة 6 شركات نموذج الانحدار لديها شكل المعادلة Y = أ ₀ + ₁ × ₁ + ... + _لك س _ك، حيث x _ط - المتغيرات المؤثرة، _وأنا - معاملات الانحدار، حزب العدالة والتنمية - عدد من العوامل.

Y لمهمة معينة - هو مؤشر لاطلاق النار موظف، عاملا مساعدا - الراتب، والذي يرمز التي كتبها X.

تسخير قوة من جدول "إكسل"

يجب أن يسبق تحليل الانحدار في Excel عن طريق تطبيق لبيانات الجدول الموجود المدمج في وظائف. ومع ذلك، لهذه الأغراض فمن الأفضل لاستخدام الوظيفة الإضافية مفيدة جدا "تحليل حزمة". لتمكينه، تحتاج إلى:

• مع علامة التبويب "ملف" انتقل إلى "إعدادات".
• في النافذة التي تفتح، اختر 'إضافات'؛
• انقر على زر "الذهاب"، وتقع في أسفل اليمين من الخط "الإدارة".
• وضع علامة الاختيار بجانب "حزمة أدوات التحليل" وتأكيد العمل الخاص بك عن طريق الضغط على "OK".

إذا فعلت بشكل صحيح، وعلى الجانب الأيمن من علامة التبويب "البيانات"، وتقع فوق ورقة عمل "إكسل"، يظهر الزر المطلوب.

الانحدار الخطي في Excel

الآن أن لديك على يد كل الأدوات الظاهرية اللازمة لإجراء العمليات الحسابية الاقتصاد القياسي، يمكننا أن نبدأ في معالجة مشكلتنا. للقيام بذلك:

• يتم النقر على زر على "تحليل البيانات".
• انقر على زر "تراجع" في نافذة مفتوحة.
• علامة تبويب التي تظهر لإدخال مجموعة من القيم Y (عدد العمال فصل) وX (رواتبهم)؛
• تأكيد أعمالهم عن طريق الضغط على زر «موافق».

ونتيجة لذلك، سيقوم البرنامج تلقائيا ملء تحليل جديد الانحدار البيانات ورقة البيانات. انتبه! في Excel، هناك فرصة لتعيين المكان الذي تفضله لهذا الغرض. على سبيل المثال، قد يكون من نفس الورقة، حيث قيم Y و X، أو حتى كتاب جديد، والمصممة خصيصا لتخزين هذه البيانات.

نتائج تحليل الانحدار لR-مربع

البيانات إكسل تم الحصول عليها في البيانات سبيل المثال تعتبر لديها شكل:

أولا وقبل كل شيء، ينبغي أن نولي الاهتمام لقيمة R-التربيعية. وهو يمثل معامل التحديد. في هذا المثال، R-مربع = 0.755 (75.5٪)، م. E. المعلمات المحسوبة نموذج لشرح العلاقة بين المعلمات بنسبة 75.5٪ اعتبروا. يعتبر ارتفاع قيمة معامل التحديد للنموذج المحدد لتكون أكثر فائدة للقيام بمهام معينة. ويعتقد أن تصف الوضع الحقيقي بشكل صحيح وفقا للقيمة مربع-R فوق 0.8. إذا كان R-مربع <0.5، ثم تحليل الانحدار في Excel لا يمكن أن تعتبر معقولة.

تحليل نسبة

يظهر عدد 64.1428 ماذا سيكون قيمة Y، إذا كان كل شي المتغيرات في نموذجنا سيتم إعادة تعيين. وبعبارة أخرى، يمكن القول أن يتأثر قيمة المعلمة تحليلها من قبل عوامل أخرى غير تلك الموضحة في نموذج محدد.

عامل المقبل -0،16285 تقع في B18 الخلية، ويظهر تأثير هام من X متغير Y. وهذا يعني أن متوسط الراتب للعاملين داخل نموذج يؤثر على عدد من الاستقالات من وزن -0،16285، ر. E. درجة تأثيرها على الإطلاق صغيرة. علامة "-" يشير إلى أن معامل هو سلبي. ومن الواضح، لأننا نعلم جميعا أن أكثر المرتبات في المؤسسة، وأقل الناس قد أعربوا عن رغبتهم في إنهاء عقد العمل أو رفض.

الانحدار المتعدد

تحت هذا المصطلح يشير إلى معادلة التواصل مع العديد من المتغيرات المستقلة في النموذج:

ذ = و (خ ₁ + س ₂ + ... س _م) + ε، حيث ذ - هو نتيجة ميزة (المتغير التابع)، وس _1، س _2، س ... _م - هي عوامل علامات (المتغيرات المستقلة).

تقدير المعلمة

لالانحدار المتعدد (MR) فإنه يتم تنفيذ ذلك باستخدام طريقة المربعات الصغرى (LSM). لالمعادلات الخطية من النموذج Y = أ + ب ₁ × _{1 + ... + ب م خ م +} ε بناء نظام من المعادلات العادية (سم. بالأسفل)

لفهم مبدأ الطريقة، ونحن نعتبر حالة اثنين عامل. ثم قمنا الوضع صفها الصيغة

وبالتالي، نحصل على:

حيث σ - هو الفرق من ميزة منها، يعكسه الرقم القياسي.

MNC تنطبق على المعادلة MR إلى standartiziruemom الحجم. في هذه الحالة، نحصل على المعادلة:

حيث ر _ذ، ر _{س 1، ص} ر _... - standartiziruemye المتغيرات التي متوسط القيم 0؛ بيتا _ط - معاملات الانحدار موحدة والانحراف المعياري - 1.

يرجى ملاحظة أن جميع بيتا _ط في هذه الحالة يعرف بأنه تطبيع وtsentraliziruemye، لذلك المقارنة بين تعتبر صالحة ومقبولة. وبالإضافة إلى ذلك، ومن المتفق عليه لتنفيذ فحص العوامل، ونبذ تلك التي لديها أدنى قيم βi.

المشكلة باستخدام معادلة الانحدار الخطي

افترض أن لديك جدول ديناميات سعر منتج معين N ل 8 أشهر الماضية. ومن الضروري أن تقرر ما إذا كان اكتساب حزبه بسعر 1850 روبل. / T.

	A	B	C
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-تقاطع خط" من ورقة مع نتائج تحليل الانحدار. وهكذا، فإن معادلة الانحدار الخطي (EQ) 3 لهذه المشكلة يمكن أن تكون مكتوبة على النحو التالي:

أسعار السلع N = 11714 * 1727.54 الشهر عدد +.

أو في تدوين جبري

ص = 11714 س + 1727،54

تحليل النتائج

أن تقرر ما إذا كان تلقى معادلة الانحدار الخطي نحو كاف باستخدام معاملات متعددة الارتباط (CMC) وعزم وكذلك اختبار واختبار t فيشر. في الجدول الانحدار "إكسل" مع النتائج التي تعمل تحت أسماء متعددة R، R-ساحة، F-تي الإحصاءات والإحصاءات، على التوالي.

KMC R تمكن من تقدير العلاقة الاحتمالية التقارب بين المتغيرات المستقلة والتابعة. لها قيمة عالية تشير إلى وجود علاقة قوية بما فيه الكفاية بين متغير "عدد شهر" و "N سعر المنتج في روبل لكل 1 طن." ومع ذلك، فإن طبيعة هذه العلاقة غير معروف.

مربع من معامل التحديد R ² (RI) هو سمة رقمية من نسبة إجمالي مبعثر ويظهر مبعثر من جزء البيانات التجريبية، أي، قيم المتغير التابع المقابلة لمعادلة الانحدار الخطي. في هذه المشكلة، هذه القيمة هي 84.8٪، النائب. موصوفة E. الاحصائيات بدرجة عالية من الدقة التي تم الحصول عليها SD.

F-إحصاءات، المعروف أيضا باسم معيار فيشر المستخدمة في تقييم أهمية الاعتماد الخطي أو دحض فرضية تؤكد وجودها.

قيمة إحصاء t (الطالب اختبار ر) يساعد على تقييم أهمية معامل في أي عضو غير معروف الاعتماد الخطي مجانا. إذا كانت قيمة اختبار t> ر _كر، يتم رفض الفرضية من التفاهه معادلة خطية من مصطلح حرية.

في هذه المشكلة لفترة مجانية من خلال الصكوك "إكسل" تبين أن ر = 169،20903، و p = 2،89E-12، ر. E. لديك احتمال الصفر التي المؤمنين سيتم رفض فرضية عدم أهمية على المدى مجانا. لمعامل غير معروف في تي = 5،79405، و p = 0،001158. وبعبارة أخرى، فإن احتمال أن الفرضية الصحيحة رفض والتفاهه معامل للمجهول، هو 0.12٪.

وهكذا، يمكن القول أن الحصول الخطية معادلة الانحدار نحو كاف.

المشكلة في جدوى شراء أسهم

تم إجراء الانحدار المتعدد في Excel باستخدام نفس "تحليل البيانات" الأداة. النظر في تطبيق معين.

دليل شركة «NNN» يجب أن تقرر ما إذا كان لشراء 20٪ من أسهم JSC «MMM». سعر الحزمة (SP) هو 70 مليون دولار امريكى. المتخصصين في «NNN» جمع البيانات عن المعاملات المماثلة. وتقرر لتقييم قيمة الأسهم على هذه المعالم، أعرب بملايين الدولارات الأمريكية، مثل:

• الذمم الدائنة (VK)؛
• حجم مبيعاتها السنوية (VO)؛
• الذمم المدينة (VD)؛
• قيمة الأصول الثابتة (SOF).

وبالإضافة إلى ذلك، استخدم الديون الأجور شركات (V3 U) بآلاف دولارات الولايات المتحدة.

وسائل جدول قرار معالج إكسل

تحتاج أولا إلى إنشاء جدول إدخال البيانات. هو كما يلي:

التالي:

• مربع دعوة "تحليل البيانات".
• المقطع المحدد "الانحدار".
• نافذة "الإدخال الفاصل Y» يديرها مجموعة قيم المتغيرات التي تعتمد من العمود G؛
• انقر على أيقونة مع السهم الأحمر إلى اليمين من النافذة "الإدخال الفاصل X» وعزلها على نطاق ورقة من كل قيم العمود B، C، D، F.

علامة نقطة "ورقة عمل جديدة" وانقر على "موافق".

الحصول على تحليل الانحدار لهذه المهمة.

نتائج الدراسة والاستنتاجات

"اجمع" تقريب من البيانات الواردة أعلاه على الطاولة ورقة اكسل معالج معادلة الانحدار:

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

في الشكل الرياضي أكثر من المعتاد يمكن أن تكون مكتوبة على النحو التالي:

ذ = 0103 * X1 + 0541 * X2 - 0031 * X3 + 0405 * X4 + 0691 * X5 - 265844

بيانات ل «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

استبدالها في معادلة الانحدار، حصل على الرقم 64720000 دولار امريكى. وهذا يعني أن أسهم JSC «MMM» لا ينبغي أن شراء، لأن مبالغ تكلفتها تماما عند 70 مليون دولار امريكى.

كما ترون، واستخدام جداول "إكسل" ومعادلة الانحدار السماح للمن اتخاذ قرار مستنير بشأن جدوى صفقة محددة تماما.

الآن أنت تعرف ما هو الانحدار. الأمثلة على التفوق، التي نوقشت أعلاه، سوف تساعدك في حل المشاكل العملية من الاقتصاد القياسي.