رگرسیون در اکسل: نمونه معادله است. رگرسیون خطی

تجزیه و تحلیل رگرسیون - روش مطالعه آماری برای نشان دادن وابستگی یک پارامتر از یک یا چند متغیر مستقل. در دوران پیش از کامپیوتر، استفاده از آن شده است و نه دشوار است، به ویژه هنگامی که آن را به حجم زیادی از داده ها است. امروز، یادگیری چگونگی ساخت رگرسیون در اکسل، شما می توانید مسائل آماری پیچیده در فقط چند دقیقه را حل کند. در زیر نمونه های خاصی از اقتصاد است.

انواع رگرسیون

این مفهوم به ریاضیات معرفی شد توسط فرانسیس گالتون در سال 1886. رگرسیون است:

• خطی؛
• سهموی؛
• قدرت؛
• نمایی؛
• هذلولی.
• نمایی؛
• لگاریتمی است.

مثال 1

مسئله تعیین وابستگی تعداد استعفای اعضای هیات از متوسط دستمزد در 6 شرکت های صنعتی در نظر بگیرید.

وظیفه. شش شرکت که متوسط حقوق ماهانه و تعداد کارکنان که به طور داوطلبانه ترک تجزیه و تحلیل قرار داده اند. در فرم جدولی داریم:

		B	C
1	X	تعداد استعفای	حقوق
2		Y	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 = a ₀ + ₁ * ₁ + ... + یک _k X _K، که در آن x _من - متغیرهای موثر، یک _من - ضرایب رگرسیون، AK - تعدادی از عوامل.

Y به صورت یک کار داده شده - حقوق و دستمزد، است که توسط X. نشان داده می شود - آن یک شاخص به آتش یک کارمند، یک عامل موثر است

مهار قدرت از "Excel" صفحه گسترده

تجزیه و تحلیل رگرسیون در اکسل باید توسط یک برنامه به داده های جدول موجود ساخته شده در توابع داشته است. با این حال، برای این منظور بهتر است به استفاده از یک افزودنی در "تجزیه و تحلیل بسته" بسیار مفید است. برای فعال کردن آن، شما نیاز به:

• با تب "پرونده" به "تنظیمات"؛
• در پنجره باز شده، گزینه "افزودنی"؛
• بر روی دکمه "برو"، واقع در سمت راست پایین از خط "مدیریت" کلیک کنید.
• قرار دادن یک علامت چک کنار "تحلیل Toolpak" و عمل خود را اعلام می با فشار دادن "OK".

اگر به درستی انجام شود، در سمت راست از "اطلاعات" تب، واقع شده در بالا ورق کار "اکسل"، دکمه های مورد نظر را نشان می دهد.

رگرسیون خطی در اکسل

حالا که شما را در دست دارند تمام ابزار مجازی لازم برای محاسبات اقتصادسنجی، ما می توانیم شروع به رسیدگی به مشکل ما است. برای این کار:

• دکمه ای در "تجزیه و تحلیل داده ها" را کلیک؛
• با کلیک بر روی دکمه "رگرسیون" در پنجره باز؛
• یک تب است که به نظر می رسد را به شما معرفی طیف وسیعی از مقادیر Y (تعداد کارگران جداسازی) و X (حقوق و دستمزد خود)؛
• اقدامات خود را مجددا تأکید با فشار دادن دکمه «تایید».

در نتیجه، برنامه به طور خودکار تجزیه و تحلیل جدید ورق گسترده رگرسیون داده را پر کنید. توجه کنید! در اکسل، فرصتی است برای تعیین جایی که شما برای این منظور ترجیح می دهند وجود دارد. به عنوان مثال، ممکن است همان برگه، که در آن ارزش های Y و X، و یا حتی یک کتاب جدید، به طور خاص برای ذخیره سازی داده ها از جمله طراحی شده است.

نتایج تحلیل رگرسیون برای تحقیق مربع

داده اکسل به دست آمده در داده به عنوان مثال در نظر گرفته اند به صورت:

اول از همه، ما باید با توجه به ارزش مجذور R پرداخت. این نشان دهنده ضریب تعیین. در این مثال، تحقیق مربع = 0.755 (75.5٪)، متر E. پارامترهای محاسبه شده از مدل برای توضیح رابطه بین پارامترهای توسط 75.5٪ در نظر گرفته. هرچه مقدار از ضریب تعیین، مدل انتخاب شده در نظر گرفته می شود بیشتر برای انجام وظایف خاص مفید است. اعتقاد بر این است به درستی وضعیت واقعی در ارزش R-مربع بالا 0.8 توصیف می کنند. اگر تحقیق مربع <0.5، پس از آن تجزیه و تحلیل رگرسیون در اکسل می تواند معقول در نظر گرفته شود.

تجزیه و تحلیل نسبت

شماره 64.1428 نشان می دهد چه خواهد بود، ارزش Y، اگر همه خی متغیرها در مدل ما پاک می شود. به عبارت دیگر، می توان گفت که مقدار پارامتر مورد تجزیه و تحلیل توسط عوامل دیگر از کسانی که شرح داده شده در مدل خاص تحت تاثیر قرار.

عامل بعدی -0.16285 واقع در B18 همراه، نشان می دهد که مهم تاثیر متغیر X به Y. این بدان معنی است که متوسط حقوق کارکنان در مدل تعداد استعفای تاثیر می گذارد از وزن -0.16285، تی. E. درجه تاثیر آن در همه کوچک است. علامت "-" نشان می دهد که ضریب منفی است. واضح است، چرا که همه ما می دانیم که حقوق و دستمزد در شرکت، مردم کمتر تمایل به فسخ قرارداد استخدام یا اخراج ابراز کرده اند.

رگرسیون چندگانه

بر اساس این اصطلاح اشاره دارد به معادله ارتباطات با چند متغیر مستقل از فرم:

Y = F (X ₁ + × ₂ + ... X _متر) + ε، که در آن y - نمره از ویژگی های (متغیر وابسته) و x _1، X _2، ... X _متر - نشانه عوامل (متغیر مستقل) می باشد.

برآورد پارامتر

برای رگرسیون چندگانه (MR) است با استفاده از یک روش مربع حداقل (LSM). برای معادلات خطی از فرم Y = A + B ₁ * _{1 + ... + ب متر X متر +} ε ساخت یک سیستم معادلات نرمال (سانتی متر در زیر.)

برای درک اصل روش، ما در مورد دو عامل در نظر بگیرید. پس ما باید این وضعیت شرح داده شده توسط فرمول

از این رو، به دست آوریم:

که در آن σ - واریانس از ویژگی های مربوطه است، در شاخص منعکس شده است.

MNC قابل اجرا است به معادله محمدرضا به standartiziruemom مقیاس. در این مورد، ما از معادله:

آن تی _Y، T _{* 1، ...} تی _XM - standartiziruemye متغیر که مقدار متوسط 0؛ بتا _من - ضرایب رگرسیون استاندارد شده و انحراف استاندارد - 1.

لطفا توجه داشته باشید که همه _من بتا در این مورد به عنوان نرمال و tsentraliziruemye تعریف شده است، بنابراین یک مقایسه بین معتبر و قابل قبول در نظر گرفته. علاوه بر این، آن را پذیرفته است به انجام غربالگری از عوامل، دور انداختن آن که دارای کمترین مقدار βi.

مشکل با استفاده از معادله رگرسیون خطی

فرض کنید شما یک جدول از پویایی قیمت یک محصول خاص N برای 8 ماه گذشته است. آن را به تصمیم بگیرد که آیا کسب حزب خود در قیمت 1850 روبل ضروری است. / T.

		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 روبل در هر تن

برای حل این مشکل در پردازنده جدولی "اکسل" مورد نیاز برای استفاده در حال حاضر برای مثال ابزار "تجزیه و تحلیل داده" ارائه شده در بالا شناخته شده است. بعد، بخش "رگرسیون" و پارامترهای تنظیم را انتخاب نمایید. ما باید به یاد داشته باشید که در ورودی فاصله X »" محدوده ورودی Y »باید به یک طیف وسیعی از مقادیر متغیر وابسته (قیمت کالاها در ماه های خاصی از سال در این مورد) و در معرفی" - برای مستقل (تعداد ماه). ما در عمل با کلیک کردن «خوب» اعلام. در یک کاربرگ جدید (اگر چنین نشان داد)، ما به دست آوردن داده ها را برای رگرسیون.

ما در حال ایجاد در آنها معادله خطی از فرم Y = تبر + B، که در آن به عنوان پارامتر a و b ضرایب از تعداد خط از ماه و نام ضرایب و «Y-تقاطع" خط ورق با نتایج حاصل از تجزیه و تحلیل رگرسیون هستند. بنابراین، معادله رگرسیون خطی (EQ) 3 برای مشکل می تواند نوشته شود:

قیمت کالاها N = 11714 * 1727.54 ماه تعداد +.

یا نماد جبری

Y = X + 11714 1727،54

تجزیه و تحلیل نتایج

تصمیم بگیرید که آیا به اندازه کافی معادله رگرسیون خطی با استفاده از ضرایب همبستگی چندگانه (CMC) و عزم و اراده و همچنین آزمون و آزمون t فیشر را دریافت کرد. در جدول "اکسل" رگرسیون با نتایج آنها تحت نام های متعدد R، R-میدان، F-آماره t و آمار، به ترتیب عمل می کنند.

KMC R را قادر می سازد به منظور برآورد نزدیکی رابطه احتمالی بین متغیرهای مستقل و وابسته. ارزش بالای آن را نشان می دهد یک اتصال به اندازه کافی قوی بین متغیر "تعداد ماه" و "N از قیمت محصولات در روبل در هر 1 تن." با این حال، ماهیت این رابطه ناشناخته است.

مربع از ضریب تعیین R ² (RI) یک ویژگی عددی نسبت پراکندگی کل است و نشان می دهد که پراکنده از بخش داده های تجربی، به عنوان مثال، مقادیر متغیر وابسته مربوط به یک معادله رگرسیون خطی. در این مشکل، این مقدار 84.8 درصد است، MP. E. آمار با درجه بالایی از دقت و صحت به دست آمده SD است.

آماره F، همچنین به عنوان معیار فیشر شناخته شده برای ارزیابی اهمیت وابستگی خطی یا رد فرضیه تایید وجود آن استفاده می شود.

ارزش آماره t (آزمون t) کمک می کند تا ارزیابی اهمیت این ضریب در هر یک از اعضای رایگان وابستگی خطی ناشناخته است. اگر مقدار آزمون t> تی _CR، فرضیه از یک معادله خطی ناچیز از مدت رایگان است را رد کرد.

در این مسئله برای یک دوره رایگان از طریق ابزار "اکسل" مشخص شد که T = 169،20903 و p = 2،89E-12، T. E. یک احتمال صفر که وفادار خواهد شد این فرضیه از ناچیز بودن مدت رایگان را رد کرد. برای ضریب مجهول در t = 5،79405 و p = 0،001158. به عبارت دیگر، این احتمال که یک فرضیه درست رد خواهد شد از ضریب برای ناشناخته ناچیز، 0.12 درصد است.

بنابراین، می توان گفت که معادله رگرسیون خطی به دست آمده به اندازه کافی.

مشکل مطلوبیت چیزی بخاطرسودمندی خرید سهام

رگرسیون چندگانه در اکسل با استفاده از همان "تجزیه و تحلیل داده ها" ابزار انجام شد. نرم افزار خاص در نظر بگیرید.

راهنمای شرکت «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.

در قالب ریاضی معمول تر می توان آن را به عنوان نوشته:

Y = 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

جایگزینی آنها را به معادله رگرسیون، به دست آمده یک رقم از 64،720،000 دلار آمریکا است. این به این معنی است که سهام از JSC «MMM» باید خرید نیست، چرا هزینه های خود را کاملا در 70 میلیون دلار آمریکا آسیا.

همانطور که می بینید، استفاده از صفحه گسترده "اکسل" و معادله رگرسیون مجاز به تصمیم گیری آگاهانه در مورد مطلوبیت چیزی بخاطرسودمندی معامله کاملا خاص.

در حال حاضر شما می دانید که چه رگرسیون. نمونه هایی برای اکسل، بحث شده در بالا، شما را در حل مشکلات عملی اقتصاد کمک خواهد کرد.