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

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

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

نکات کلیدی

• رگرسیون خطی رابطه بین متغیر (های) وابسته و مستقل را مدل می کند.
• اکر متغیرها مستقل باشند، ناهمگنی واریانس وجود نداشته باشد و شرایط خطای متغیرها به یکدیگر همبستگی تداشته باشند آنگاه می توان تحلیل رگرسیون را پیدا کرد.
• مدل سازی رگرسیون خطی در اکسل با افزونه Data Analysis ToolPak آسان تر است.

مفروضات مهم

چند فرضیه مهم باید در مجموعه داده درست باشد تا تحلیل رگرسیون انجام شود:

1. متغیرها باید واقعاً مستقل باشند (استفاده از آزمون Chi-square).
2. داده ها نباید واریانس های خطای متفاوتی داشته باشند (ناهمگنی واریانس).
3. شرایط خطای هر متغیر نباید همبستگی داشته باشند. در غیر اینصورت متغیرها به صورت سریالی به یکدیگر همبستگی دارند.

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

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

اولین قدم برای اجرای آنالیز رگرسیون در اکسل اینه که از نصب افزونه رایگان Data Analysis ToolPak مطمئن شویم. این افزونه محاسبه محدوده آماری را بسیار آسان می کند. نیازی به ترسیم خط رگرسیون خطی ندارد اما ساخت جدول های آماری را ساده تر می کند. برای اطمینان از نصب این افزونه روی تب “Data” در نوار ابزار کلیک کنید. اگر گزینه “Data Analysis” را ببینیم یعنی این ویژگی نصب شده و آماده استفاده است. اگر نصب نشده باشد می توانید روی دکمه Office کلیک کنید و گزینه “Options” را انتخاب و از قسمت Add-ins آن را اضافه کنید.

با استفاده از Data Analysis ToolPak، فقط با چند کلیک می توانید خروجی رگرسیون را بسازید.

متغیر مستقل در محدوده X

می خواهیم بررسی کنیم آیا با توجه به بازدهی شاخص S&P 500 می توانیم قدرت و رابطه بازده سهام Visa (V) را تخمین بزنیم یا نه. داده های سهام Visa به عنوان متغیر وابسته در ستون اول و داده های شاخص S&P 500 به عنوان متغیر مستقل در ستون دوم قرار گرفته اند.

1. روی تب “Data” از نوار ابزار کلیک کنید.
2. گزینه “Data Analysis” را انتخاب کنید. کادر Data Analysis نمایش داده می شود.
3. از منو گزینه “Regression” را انتخاب کرده و روی دکمه “OK” کلیک کنید.
4. در پنجره Regression روی کادر “Input Y Range” کلیک کرده و داده های متغیر وابسته (سهام Visa (V)) را وارد کنید.
5. روی کادر “Input X Range” کلیک و داده های متغیر مستقل (شاخص S&P 500) را وارد کنید.
6. برای اجرای نتایج روی “OK” کلیک کنید.

توضیح نتایج

جدول زیر نتایج را نشان می دهد:

مقدار R² ضریب تعیین است که میزان تنوع در متغیر وابسته را توسط متغیر مستقل اندازه گیری می کند یا در واقع میزان سازگاری مدل رگرسیون را با داده ها نشان می دهد. مقدار R² بین ۰ تا ۱ متغیر است؛ هرچقدر بیشتر باشد، سازگاری بهتری را نشان می دهد. مقدار p یا احتمال هم از ۰ تا ۱ متغیر است و نشان می دهد که آیا آزمون معنی دار بوده یا خیر. برعکس مقدار R²، چون مقدار p همبستگی بین متغیرهای وابسته و مستقل را نشان می دهد پس هرچه کوچکتر باشد بهتر است.

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

با انتخاب داده ها و قرار دادن آن ها در یک نمودار scatter می توانیم یک رگرسیون خطی را در اکسل ترسیم کنیم. برای اضافه کردن خط رگرسیون از منوی “Chart Tools” گزینه “Layout” را انتخاب کنید. در پنجره باز شده “Trendline” و سپس روی “Linear Trendline” را کلیک کنید. برای اضافه کردن مقدار R² گزینه More Trendline Options” را از منوی “Trendline” انتخاب کنید. در نهایت “Display R-squared value on chart” را فعال کنید. تصویر زیر قدرت رابطه را نشان می دهد.

   دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

   شروع به کار نرم افزار

  همه منو/تب های اکسل در نوار ریبون

   ۴۰ کلید میانبر

   نحوه تایپ شدن اتوماتیک اعداد