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

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

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

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

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

فرض کنید که یک معادله رگرسیونی به شکل زیر داریم:

y =X b +e

که در آن، y (متغیر وابسته) یک بردار n*1، ماتریس X (حاوی k متغیر توضیحی) یک ماتریس n*k، ماتریس e (جملات خطا) یک بردار n*1 و ماتریس b (ضرایب رگرسیون) یک بردار k*1 حاوی ضرایب مدل است. هدف ما از انجام رگرسیون بدست آوردن ضرایب موجود در بردار b و بررسی معنی داری آنها است. به خاطر داریم که ضرایب رگرسیون را در فرم ماتریسی می توان بر اساس رابطه زیر بدست آورد:

b =(X’X)^-1 X’y

یک رگرسیون با دو متغیر توضیحی و عرض از مبدا در نظر بگیرید. برای اینکه بتوانیم عرض از مبدا را در مدل لحاظ کنیم، یک متغیر تعریف می کنیم که تمامی مقادیر آن برابر یک است. بر این اساس برای یک رگرسیون با دو متغیر توضیحی و عرض از مبدا، ابتدا داده ها را به شکل زیر در اکسل مرتب می کنیم (برای مشاهده تصاویر در سایزبزرگتر روی تصویر راست کلیک کرده و گزینه Open Image in New Tab را کلیک کنید).

در تصویر فوق، ستون A حاوی داده های متغیر وابسته، ستون B، حاوی داده های عرض از مبدا (که همه مقادیر آن برابر یک است)، ستون C و D نیز حاوی داده های متغیرهای توضیحی X1 و X2 است. تعداد مشاهدات نیز برابر 100 است.

---

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

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

MMULT(array1;array2)

که در آن array1 رنج (range) مربوط به ماتریس اول و array2 رنج مربوط به ماتریس دوم است.

دستور TRANPSOSE: این دستور ترانهاده ماتریس را محاسبه می کند. این دستور در فرمول بالا برای محاسبه ‘X ضروری است. استفاده کلی آن در اکسل 2016 به شکل زیر انجام می شود:

Transpose(array)

که در آن array رنج مربوط به ماتریسی است که قرار است ترانهاده آن محاسبه شود.

دستور MINVERSE: این دستور، معکوس ماتریس را در اکسل محاسبه می کند. استفاده کلی این دستور در اکسل 2016 به شکل زیر است:

MINVERSE(array)

که در آن array رنج مربوط به ماتریسی است که قرار است معکوس آن محاسبه شود.

---

همانطور که پیشتر نیز اشاره شد، تعداد مشاهدات موجود برای هر متغیر برابر 100 است و متغیرهای توضیحی X (شامل عرض از مبدا و دو متغیر توضیحی دیگر) در رنج B2:D101 قرار دارند. بر این اساس محاسبه مرحله به مرحله فرمول مربوط به ضرایب به صورت زیر خواهد بود:

X’: ترانهاده ماتریس متغیرهای توضیحی

transpose(B2:D101)

X’X

mmult(transpose(B2:D101);B2:D101)

(X’X)^-1

minverse(mmult(transpose(B2:D101);B2:D101))

(X’X)^-1 X’

mmult(minverse(mmult(transpose(B2:D101);B2:D101));transpose(B2:D101))

(X’X)^-1 X’y

=mmult(mmult(minverse(mmult(transpose(B2:D101);B2:D101));transpose(B2:D101));A2:A101)

فرمول فوق،فرمول نهایی برای محاسبه ضرایب رگرسیون است.

نکته بسیار مهم: در صورتی که فرمول فوق را در یک سلول تایپ کنید و دکمه اینتر (Enter) را بزنید، هیچ محاسبه ای صورت نخواهد گرفت و با ارور مواجه خواهید شد. دلیل آن واضح است. خروجی دستور فوق یک بردار 1*3 است و ما می خواهیم این بردار را در یک سلول جا دهیم که غیر ممکن است. برای این منظور باید به جای انتخاب یک سلول، به تعداد متغیرهای توضیحی سلول ها را به صورت ستونی انتخاب نماییم. در اینجا سه سلول زیر هم انتخاب خواهد شد. توصیه می شود برای اینکه در ادامه محاسبات بتوانید با دستور العمل همراه باشید، رنج F2:F4 را انتخاب کنید. سپس در قسمت نوشتن فرمول (Formula Bar) کلیک کنید و دستور فوق را تایپ کنید. به جای فشردن دکمه Enter برای اجرای دستور باید ترکیب دکمه های Ctrl+Shift+Enter را استفاده کنیدتا محاسبات به صورت ماتریسی انجام شود.

بعد از وارد کردن دستور فوق و فشردن ترکیب دکمه Ctrl+Shift+Enter ضرایب رگرسیون به شکل زیر محاسبه خواهند شد (برای مشاهده تصاویر در سایزبزرگتر روی تصویر راست کلیک کرده و گزینه Open Image in New Tab را کلیک کنید):

بدست آوردن انحراف معیار ضرایب رگرسیون در اکسل با استفاده از روابط جبری

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

t=b/se

که در آن، b، مقدار ضریب و se انحراف معیار مربوطه است. ضرایب را در بالا توانستیم به دست بیاوریم ولی مقدار se مربوط به هر یک از ضرایب را در اختیار نداریم. برای محاسبه انحراف معیار ضرایب رگرسیون می توانیم از فرمول زیر ماتریس واریانس کواریانس ضرایب را محاسبه کنیم و سپس انحراف معیار مربوط به هر ضریب را از داخت آن ماتریس استخراج کنیم:

Var-Cov (b)=σ² (X’X)^-1

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

e=y -X b 

که در آن، X b ، مقادیر توضیح داده شده توسط مدل است و به راحتی از طریق ضرب ماتریس X در ماتریس b قابل محاسبه است. حاصل این ماتریس یک ماتریس 1*100 است. بنابراین لازم است که ابتدا 100 سلول (به نعداد مشاهدات نمونه تان) به صورت ستونی انتخاب شود و سپس دستور ضرب دو ماتریس X و b به شکل زیر انجام شود. در اینجا رنجی که به صورت ستونی برای محاسبه  ماتریس XB انتخاب شده، رنج H2:H102 است.

=MMULT(B2:D101;F2:F4)

باز هم فراموش نکنید که از ترکیب دکمه های Ctrl+Shift+Enter استفاده کنید. خروجی کار به شکل زیر خواهد بود (برای مشاهده تصاویر در سایزبزرگتر روی تصویر راست کلیک کرده و گزینه Open Image in New Tab را کلیک کنید).

حال برای محاسبه مقادیر جملات خطا، مقادیر ستون H را از مقادیر متناظر در ستون A کسر کنید (y-xb). این عملیات را در ستون I انجام دهید. به عنوان مثال، محاسبات سلول I2 به شکل زیر خواهد بود (همین کار برای سایر سطرهای متناظر نیز انجام خواهد شد):

=A2-H2

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

σ²=e’e/n-k

در معادله فوق، n، تعداد مشاهدات (که در این مثال برابر 100 است) و k، تعداد پارامترها یا ابعاد ماتریس b است که در اینجا برابر 3 (یک عرض از مبدا به اضافه 2 ضریب برای دو متغیر توضیحی) است.  خروجی فرمون فوق یک عدد است. بنابراین یک سلول انتخاب کنید (ما سلول k2 را انتخاب می کنیم) و دستور زیر را تایپ کنید:

=MMULT(TRANSPOSE(I2:I101);I2:I101)/(100-3)

فراموش نکنید که از ترکیب دکمه های Ctrl+Shift+Enter استفاده کنید. خروجی کار به این شکل خواهد بود.

حال برای اینکه بتوانیم ماتریس واریانس کواریانس ضرایب را محاسبه کنیم باید عبارت X’X)^-1) را نیز محاسبه کنیم. ماتریس X، یک ماتریس n*k است. بنابراین ترانهاده آن یک ماتریس k*n خواهد بود و در نهایت ماتریس  X’X)^-1)  یک ماتریس k*k خواهد بود. در اینجا k برابر 3 است. بنابراین ماتریس حاصل یک ماتریس 3*3 خواهد بود. از این رو برای اینکه بتوانیم ماتریس  X’X)^-1)  را محاسبه کنیم ابتدا باید یک فضای 3*3 شامل 9 سلول را انتخاب کنیم و سپس دستور زیر را در نوار فرمول تایپ می کنیم:

=MINVERSE(MMULT(TRANSPOSE(B2:D101);B2:D101))

فراموش نکنید که از ترکیب دکمه های Ctrl+Shift+Enter استفاده کنید. خروجی کار یک ماتریس 3*3 خواهد بود.

حال باید واریانس جملات خطای رگرسیون را در تک تک عناصر ماتریس X’X)^-1) ضریب کنیم و یک ماتریس 3*3 جدید بسازیم که همان ماتریس واریانس کواریانس ضرایب است. نتیجه به شکل زیر خواهد بود:

در ماتریس واریانس کواریانس محاسبه شده، عناصر روی قطر نشان دهنده واریانس ضرایب (عرض از مبدا به اضافه دو ضریب متغیرهای توضیحی) و عناصر غیرقطری نشان دهنده کواریانس بین ضرایب هستند. درایه اول روی قطر اصلی نشان دهنده واریانس عرض از مبدا، درایه دوم روی قطر، نشان دهنده واریانس ضریب متغیر x1 و درایه سوم نشان دهنده واریانس ضریب متغیر x2 است.

برای محاسبه آماره آزمون t، ما به جای واریانس ضرایب به انحراف معیار آنها احتیاج داریم. برای این منظور باید از عناصر روی قطر اصلی ماتریس واریانس کواریانس جذر بگیریم. دستور sqrt در اکسل عمل جذر را محاسبه می کند. بنابراین در ستون U، با استفاده از دستور sqrt انحراف معیار ضرایب را محاسبه می کنیم. نتیجه به شکل زیر خواهد بود:

حال از تقسیم کردن مقادیر ستون F (مقادیر ضریب های رگرسیون) بر مقادیر متناظر در ستون U (مقادیر انحراف معیارهای ضرایب) می توانیم مقدار آماره t را برای هر ضریب محاسبه کنیم. نتیجه به شکل زیر خواهد بود.

حال می توانیم مقادیر tبدست آمده برای هر ضریب را با مقادیر بحرانی t (که در جدول t) گزارش می شود مقایسه کنیم. توزیع ضرایب یک توزیع t با 97=3-100 درجه آزادی خواهد بود. اما به جای این کار می توانیم ارزش احتمال مربوط به ضرایب را  (که در نرم افزارهای آماری معمولا گزارش می شود) نیز محاسبه کنیم. دستور T.DIST.2T در اکسل 2016، ارزش احتمال آزمون دو طرفه متناظر با مقدار آماره t را محاسبه می کند.

برای این منظور در ستون W، ارزش احتمال متناظر با آماره t محاسبه شده برای ضرایب را به شکل زیر محاسبه می کنیم:

=T.DIST.2T(V2;97)

=T.DIST.2T(V3;97)

=T.DIST.2T(V4;97)

نتیجه به شکل زیر خواهد بود:

حال می توانیم با استفاده از ارزش احتمال بدست آمده در خصوص معنی داری ضرایب رگرسیون اظهار نظر کنیم. همانطور که مشاهده می فرمایید هر سه ضریب در سطح اطمینان 99 درصد معنی دار هستند.

محاسبه رگرسیون در اکسل با استفاده از ابزار Data Analysis (روش ساده)

برای اینکه بتوانید از ابزار Data Analysis برای محاسبه رگرسیون در اکسل استفاده کنید، باید ابتدا افزونه Data Analysis را نصب کنید. برای نصب افزونه، مراحل زیر را طی کنید:

منوی فایل -> گزینه Options -> گزینه Add-Ins -> دکمه Go در پایین صفحه -> تیک دار کردن گزینه Analysis ToolPak -> کلیک روی دکمه OK

پس از طی مراحل فوق گزینه Data Analysis به منوی DATA اکسل اضافه خواهد شد.

حال به منوی DATA رفته و روی گزینه Data Analysis کلیک می کنیم. در پنجره باز شده گزینه Regression را انتخاب می کنیم. در قسمت Input Y Range رنجی را که داده های متغیر y (وابسته) در آن قرار دارد را وارد می کنیم. در این مثال متغیر y در رنج A2:A101 قرار داشت. می توانیم به جای اینکه رنج را تایپ کنیم، با استفاده از ماوس رنج مورد نظر را انتخاب کنیم. همین کار را برای Input X Range انجام میدهیم. در این مثال دو متغیر توضیحی داشتیم شامل x1 و x2 که داده های آنها در بازه C2:D101 قرار داشتند. لازم به ذکر است که در اینجا برخلاف روش قبلی نیاز نیست که شما متغیری (با مقادیر 1) برای عرض از مبدا تعریف کنید. خود نرم افزار به صورت اتوماتیک این کار را انجام خواهد داد. با این اوصاف، تنظیمات در پنجره باز شده به شکل زیر خواهد بود:

حال روی گزینه OK کلیک کنید. نرم افزار خروجی رگرسیون را با جزئیات کامل در اختیار شما قرار خواهد داد.

---

فیلم آموزشی رگرسیون در اکسل

---