روش های رگرسیون در اکسل

محاسبه رگرسیون در اکسل

فرض کنید میخواهیم میزان فروش یک شرکت رو برای سال آینده پیش بینی کنیم. داده های فروش چند سال گذشته به همراه متغیرهای مختلف رو در اختیار داریم. اما نمیدونیم کدوم متغیر بیشترین تاثیر رو روی فروش داره. یک راه اینه که همبستگی متغیرهای مختلف با میزان فروش رو بررسی کنیم و هرکدوم که همبستگی بیشتری داشت، میتونه معیار پیش بینی ما باشه. پس میتونیم با استفاده از رگرسیون در اکسل، متوجه بشیم که کدوم فاکتور در رابطه با میزان فروش، بیشتری اثر رو داره.

رگرسیون در اکسل – مبانی رگرسیون

در مدلسازی آماری، تحلیل رگرسیونی برای تشخیص ارتباط بین دو یا چند متغیر استفاده میشه:

متغیر مستقل Independent: معیاری که ممکنه روی متغیر وابسته اثر بذاره.

متغیر وابسته Dependent: معیاری که میخواهیم ارتباطش رو با سایر متغیرها کشف کنیم و پیش بینی کنیم.

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

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

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

به عنوان یک مثال، داده های مربوط به فروش چتر در ۲۴ ماه گذشته و میانگین بارندگی در این ماه ها رو در نظر میگیریم. وقتی نمودا رمربوط به داده ها رو رسم کنیم، خط رگرسیون رابطه بین متغیر مستقل (میزان بارندگی) و متغیر وابسته (چترهای فروش رفته) رو نشون میده:

شکل ۱ – رگرسیون خطی

معادله رگرسیون خطی

معادله ریاضی یک رگرسیون خطی بصورت زیر هست:

Y= Bx + A + ε

در این معادله:

• X متغیر مستقل هست.
• Y متغیر وابسته هست.
• A مقداری که خط رگرسیون محور Y رو قطع میکنه. یعنی مقدار معادله خط در صورت صفر بودن X.
• B شیب خط رگرسیون هست. در واقع ضریب تغییر متغیر وابسته نسبت به متغیر مستقل.
• ε خطای تصادفی هست که اختلاف بین مقدار پیش بینی شده و مقدار واقعی متغیر وابسته هست

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

Y= Bx + A

مثلا برای مثال بالا، معادله رگرسیون خطی فروش چتر و بارندگی به صورت زیر خواهد بود:

= چتر فروخته شده  B* میزان بارندگی + A

راه های مختلفی برای پیدا کردن A و B وجود داره. سه روش اصلی که در اکسل برای تحلیل رگرسیون خطی استفاده میشه به شرح زیر هست:

• ابزار Regresion در افزونه Analysis Toolpak
• نمودار Scatter با خط Trendline
• استفاده از فرمول رگرسیون

در ادامه این سه روش رو شرح میدیم:

روش اول: محاسبه رگرسیون خطی با استفاده از افزونه Analysis Toolpak

در ادامه نحوه محاسبه رگرسیون خطی با استفاده از افزونه رو خواهیم دید:

فعال کردن افزونه Analysis Toolpak در اکسل

افزونه Analysis Toolpak در همه ورژن های اکسل از ۲۰۰۳ تا ۲۰۱۹ وجود داره ولی فعال نیست و باید بریم فعالش کنیم. برای این کار از مسیر زیر مطابق با شکل ۲ روی گزینه Go کلیک میکنیم:

File/ Options/ Add Ins

شکل ۲ –مسیر اضافه کردن افزونه های اکسل

حالا از پنجره باز شده گزینه Analysis Toolpak رو انتخاب میکنیم و OK رو میزنیم. (شکل ۳)

شکل ۳ –اضافه کردن افزونه Analysis Toolpak

با این کار گزینه Data Analysis در تب Data اکسل اضافه میشه.

اجرای تحلیل رگرسیون با استفاده از افزونه

در این مثال میخواهیم یک رگرسیون ساده خطی رو در اکسل اجرا کنیم. داده هایی که داریم، میانگین بارش و میانگین فروش چتر در ۲۴ ماه گذشته هست. میانگین بارش، متغیر مستقل و چتر فروخته شده، متغیر وابسته هست. البته ممکنه عوامل دیگه ای هم وجود داشته باشه اما در اینجا روی یک عامل تمرکز میکنیم. برای اجرای رگرسیون روی این داده ها، طبق مراحل زیر عمل میکنیم:

• از تب Data در گروه Analysis روی گزینه Data Analysis کلیک میکنیم.

شکل ۴- تب Data

• از پنجره باز شده، روی گزینه Regression کلیک میکنیم.

شکل ۵- انتخاب ابزار رگرسیون از افزونه Data Analysis

• در پنجره مربوط به رگرسیون، تنظیمات رو مطابق با شکل ۶ انجام میدهیم:
• در قسمت Input Y Range محدوده مربوط به متغیر وابسته رو تعیین میکنیم. در این مثال یعنی میزان فروش چتر، (C1:C25)
• در قسمت Input X Range محدوده مربوط به متغیر مستقل رو تعیین میکنیم. در این مثال یعنی میزان بارندگی، (B1:B25)
• چون سرستون ها عنوان دارند، گزینه Labels رو تیک میزنیم.
• در قسمت Output Option تنظیمات دلخواه رو انتخاب میکنیم. مثلا نتایج رو در یک شیت جدید نمایش بده.
• اگر گزینه Residuals رو تیک بزنیم، اختلاف های بین مقدار واقعی و مقدار پیش بینی شده رو نمایش خواهد داد.

شکل ۶- انجام تنظیمات مربوط به مدل رگرسیون خطی

• روی Ok کلیک کرده و نتایج رو در شیت جدید ایجاد شده بررسی میکنیم.

تفسیر نتایج رگرسیون

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

تفسیر Summary Output

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

شکل ۷- تحلیل نتایج رگرسیون

Multiple R: ضریب همبستگی. این شاخص میزان همبستگی بین داده ها رو نشون میده، هرچقدر بزرگتر، همبستگی بیشتر. مقدار این شاخص از -۱ تا ۱ میتونه باشه و هر چقدر به سمت یک نزدیکتر باشه، یعنی همبستگی بین داده ها بیشتر هست.

-۱ به این معنی هست که همبستگی منفی بین داده ها وجود داره، یعنی هر چه متغیر مستقل بیشتر میشه، متغیر وابسته کمتر میشه یا برعکس

۰ به این معنی هست که هیچ ارتباطی بین داده ها وجود نداره

۱ به این معنی هست که همبستگی مثبت بی داده ها وجود داره, یعنی هر چه متغیر مستقل بیشتر میشه، متغیر وابسته هم بیشتر میشه و برعکس.

R Square: ضریب دترمینان. این شاخص به عنوان خوبی برازش استفاده میشه. یعنی به ما نشون میده که چند تا از نقطه ها روی خط رگرسیون قرار میگیرند. R^۲ جمع مربعات انحرافات داده ها از میانگین داده ها است.

در مثال ما مقدار R^۲  برابر است با ۰.۹۷ (تا دو رقم اعشار). این به این معنی هست که ۹۷ درصد داده ها در این مدل رگرسیون، قرار میگیرند. بعبارتی ۹۷ درصد متغیرهای وابسته توسط متغیرهای مستقل، تعریف میشن. بصورت کلی مجذور R بیش از ۹۵% به عنوان یک برازش خوب در نظر گرفته میشه.

Adjusted R Square: همان مجذور مربعات هست که برای تعداد متغیر مستقل در مدل استفاده میشه. در تحلیل رگرسیون چندگانه میتونیم از این شاخص بجای R^۲ استفاده کنیم.

Standard Error: خطای استاندارد. این هم یک شاخص برای نمایش خوبی برازش مدل به روی داده ها هست و نشون میده که آنالیز ما چقدر دقیق بوده. این شاخص، هر چقدر کوچکتر باشه، دقت معادله مدل رگرسیون بالاتر خواهد بود. R^۲ درصد اختلاف متغیرهای وابسته که توسط مدل برازش میشن رو نشون میده و خطای استاندار، مقداری هست که میانگین فاصله بین نقاط از خط رگرسیون رو نمایش میده.

Observations: تعداد مشاهدات در مدل (تعداد داده ها).

اینجا تمرکز بر روش های اکسلی و اجرای مدلسازی هست. تفسیر دقیق و مفهومی نتایج در مجامع علمی آماری قابل بحث و یادگیری هست.

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

شکل ۸- تعیین مقادیر مجهول معادله خط برازش شده

همونطور که در بالا گفتیم، معادله خطی در رگرسیون خطی بر داده ها برازش میشه Y=Bx+A هست. مقدار B یعنی شیب خط (ضریب میزان بارندگی) و مقدار A معادل Intercept و یا مکانی که محور عمودی قطع میشه هست. پس با توجه به شکل ۸، معادله خط بصورت زیر خواهد بود:

Y= 0.55 X – ۳۶

تفسیر معنی این خط به این صورت هست که مثلا اگر میزان بارندگی ۸۰ میلیمتر باشه، پیش بینی تعداد چتر فروخته شده از معادله زیر بدست میاد:

۳۶ – ۰.۵۵ *(۸۰) = 8

یعنی در صورت بارندگی ۸۰ میلیمتر، تعداد چتر فروش رفته ۸ عدد خواهد بود.

روش دوم: محاسبه رگرسیون خطی با استفاده از نمودار Scatter

اگر بخوایم رابطه بین دو متغیر رو در قالب نمودار نمایش بدیم، میتونیم از نمودار Scatter استفاده میکنیم:

1. دو ستون داده ها (میزان بارندگی و فروش چتر) رو انتخاب میکنیم.
2. از تب Insert و از قسمت نمودارها، گزینه Scatter رو انتخاب میکنیم. (مطابق شکل ۹)

شکل ۹- رسم نمودار نقطه ای

با این کار نمودار نقطه ای داده ها رسم میشه.

3. حالا باید خطی که از بین داده عبور میکنه رو به نمودار اضافه کنیم. برای این کار باید Trendline رو اضافه کنیم. یک راه برای اضافه کردن Trendline اینه که با کلیک راست روی یکی از نقطه های نمودار گزینه Add Trendline رو انتخاب کنیم. شکل ۱۰

شکل ۱۰ – رگرسیون – اضافه کردن خط برازش

4. حالا تنظیمات خط برازش رو مطابق شکل ۱۱ انجام میدیم.

شکل ۱۱- رگرسیون – اضافه کردن خط برازش و نمایش معادله خط روی نمودار

وقتی تیک گزینه Display Equation On Chart رو میزنیم معادله خط برازش روی نمودار نمایش داده میشه. همونطور که مشخصه، معادله نمایش داده شده، دقیقا همون معادله محاسبه شده در روش اول و با استفاده از افزونه هست.

5. حالا با انجام تنظیمات گرافیکی نمودار رو به شکل ۱۲ تغییر میدیم.
   • رنگ و نوع خط برازش شده رو تغییر دادیم. (خط رو انتخاب کرده و از کلیک راست، Format Trendline رو انتخاب میکنیم و همه تنظیمات دلخواه رو اعمال میکنیم)
   • محور افقی نمودار رو محدود کردیم که فضای خالی نداشته باشیم. (روی محور افقی کلیک راست کرده و از قسمت Format Axis تنظیمات مربوط به حداقل و حد اکثر مقادیر رو تعیین میکنیم)

شکل ۱۲- رگرسیون – تنظیمات گرافیکی روی نمودار

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

نرم افزار اکسل توابع مختلفی برای محاسبه شاخص های رگرسیون خطی در اختیار ما قرار داده، توابعی مثل Slope, Intercept, Linest و Corel.

تابع Linest Function از روش رگرسیون حداقل مربعات استفاده میکنه که خط مستقیمی که بر داده ها برازش میشه رو پیدا کنه. این تابع مجموعه ای از جواب ها رو در اختیار ما میذاره و بصورت آرایه ای یعنی Ctrl+Shift+Enter عمل میکنه.

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

محدوده E2:F2 رو انتخاب کرده و تابع ,LINEST(C2:C25,B2:B25) رو ثبت میکنیم و بعد از بستن پرانتز، کلید ترکیبی Ctrl+Shift+Enter رو میزنیم. مقادیری که در دو سلول نمایش داده میشه مقادیر A و B معادله خط رگرسیون خواهند بود و با این مقادیر میتونیم معادله خط رو تشکیل بدیم.

شکل ۱۳- رگرسیون- محاسبه مجهولات معادله خط با استفاده از تابع Linest

اگر مایل به استفاده از فرمول نویسی آرایه ای نباشیم، میتونیم از توابع دیگه ای برای محاسبه این مجهولات استفاده کنیم. تابع Slope شیب خط و تابع Intercept مقدار عرض از مبدا یعنی محل قطع محور عمودی رو محاسبه میکنه.

محاسبه مقدار عرض از مبدا

=INTERCEPT(C2:C25,B2:B25)

محاسبه مقدار شیب خط:

=SLOPE(C2:C25,B2:B25)

محاسبه ضریب همبستگی بین داده ها:

=CORREL(B2:B25,C2:C25)

در ادامه و در تصویر ۱۴، همه این محاسبات رو مشاهده میکنید:

شکل ۱۴- رگرسیون – رگرسیون با استفاده از توابع

اگر به شاخص های بیشتری نیاز باشه باید از حالت های مختلف تابع Linest استفاده کنیم که در مقاله ای جداگانه باید به آن پرداخته شود.

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

دانلود فایل این آموزش

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

ثبت ایمیل جهت دانلود فایل

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...