رگرسیون خطی – پیدا کردن آلفا و بتا در اکسل + مثال

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

رگرسیون خطی

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

ایده اصلی این است که بسته به تعداد متغیرهای ورودی (ابعاد)، بهترین خط مستقیم را از طریق نقاط داده تعیین کنیم. در ریاضیات، آنها را به صورت زیر بیان می کنیم:

Y = m1 X1 + m2 X2 +…. mn Xn + c

جایی که:

1. Y متغیر وابسته است.
2. m1،…، mn ضرایب شیب (گرادیان) و X متغیرهای مستقل مربوطه است.
3. X1،…، Xn متغیرهای مستقل هستند.
4. c عدد ثابت است.

با توجه به Ys و Xs، رگرسیون خطی به شما می گوید ms (شیب) و c (ثابت) چیست.

من وارد جزئیات دانشگاهی نمی شوم. اگر ریاضی دبیرستان را یاد گرفتید، این فرمول نباید چیزی ناآشنا باشد. بنابر این در اینجا، خواهیم دید که چگونه می توانیم از Excel برای یافتن Alpha و Beta یک نمونه کار یا سهام استفاده کنیم.

مثال رگرسیون (آلفا و بتا)

یافتن آلفا و بتا یک نمونه کار

معادله زیر همان چیزی است که ما می خواهیم حل کنیم.

Rp بازده سهام، Rm بازده بازار و Rf نرخ بدون ریسک است.

بیایید بگوییم بازده ماهانه یک سهام در ایالات متحده را داریم و می خواهیم آلفا و بتا آن را در برابر شاخص S&P 500 بدانیم. برای ساده تر کردن امور، اجازه می دهیم نرخ بدون ریسک، Rf، را به صفر برساند.

روش 1: استفاده از شیب () و ثابت ()

برای حل دو بعدی مسئله مانند پیدا کردن Alpha و Beta یک سهام، می توانید از تابع () SLOPE و () INTERCEPT در اکسل استفاده کنید.

1. برای پیدا کردن Beta از () SLOPE استفاده کنید

SLOPE (سری Y، سری X) 

این تابع شیب بهترین خط را در هنگام ترسیم Y در برابر X محاسبه می کند. در این حالت، Y بازده سهام ماهانه و X بازده ماهانه S&P 500 است.

2. از INTERCEPT () برای یافتن آلفا استفاده کنید

INTERCEPT (سری Y، سری X) 

حالا () SLOPE  را به () INTERCEPT تغییر دهید. ورودی ها یکسان هستند. و در این حالت، آلفا سهام را می گیرید.

روش 2: با استفاده از آنالیز داده ها

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

بر روی آنالیز داده ها کلیک کنید. این یک پنجره کوچک باز می شود.

Regression را انتخاب کنید و OK را بزنید. این پنجره را برای شما فراهم می کند تا پارامترها و گزینه های رگرسیون را پر کنید.

برای ورودی ها، ما بازده نمونه کارهای ماهانه را به عنوان Y و S&P 500 ماهانه به عنوان X انتخاب کرده ایم. تیک “ثابت صفر است” را بر ندارید، زیرا به دنبال این نیستیم که ثابت خط را به صفر برساند. در مورد “سطح اطمینان”، به طور پیش فرض، اکسل همیشه محدوده اطمینان 95٪ را به شما نشان می دهد.

در بخش خروجی ها، می توانیم از اکسل بخواهیم که نتایج را در یک محدوده مشخص، نشان دهد. برای این مورد، من تصمیم دارم چاپ نتایج آنالیز را در شیت جدید با عنوان Regression Analysis چاپ کنم. 

اکنون، بر روی OK کلیک کنید.

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

 

روش 3: تابع LINEST ()

تابع () LINEST می تواند در برابر متغیرهای مختلف مستقل، رگرسیون را انجام دهد. 

LINEST (سری Y، سری X، ثابت، آمار) 

() LINEST ورودیهای مشابه () SLOPE و () INTERCEPT را در اختیار شما قرار می دهد. علاوه بر آن، می توانیم مشخص کنیم که آیا می خواهیم ثابت (c) را محاسبه کنیم و آیا می خواهیم آمار اضافی را به دست بیاوریم. برای رسیدگی به آن، LINEST () آرایه ای را به شکل زیر برمی گرداند:

m1،…، mn-1 شیب Xs متغیرهای مستقل مربوطه هستند. b ثابت است در حالی که بقیه آمارهای اضافی در مورد پارامترهای تخمینی هستند.

برای مورد ما، فقط 2 عنصر مورد علاقه وجود دارد – بتا و آلفا. برای نمایش LINEST هر دو عنصر، ما 2 سلول متوالی I4 تا J4 را انتخاب می کنیم، F2 را فشار دهید تا فرمول وارد شود و Ctrl + Shift + Enter را بزنید.

اگر بخواهیم اعداد به روش های دیگری مانند ستون نمایش داده شود، می توانیم از تابع () INDEX استفاده کنیم. از آنجا که ()LINEST آرایه ای را تولید می کند، ما می توانیم ()LINEST را در ()INDEX لانه کنیم تا عنصری را که می خواهیم استخراج کنیم. بتا در ردیف 1 ستون 1 (1،1) و آلفا در ردیف 1 ستون 2 (1،2) است. ببینید که چگونه در زیر انجام می شود.

نتیجه

اینها چند روش است که با توجه به نوع عملکرد آنها، می توانید آلفا و بتا یک سهام یا نمونه کار را پیدا کنید. ()SLOPE و ()INTERCEPT ساده ترین روش هستند ولی برای موارد با یک متغیر مستقل منفرد کافی هستند. آنالیز آماری قادر به انجام رگرسیون با چندین متغیر مستقل است. اما این روش برای تولید تخمین های رگرسیون مناسب نیست. در آخر، تابع ()LINEST انعطاف پذیری یک تابع اکسل را دارد و روی چندین متغیر مستقل کار می کند و قادر به ارائه اطلاعات آماری در مورد برآوردها است.