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

رتبه: 5 ار 1 رای SSSSS

نویسنده: سریع آسان زمان مطالعه 7 دقیقه آخرین بروزرسانی: ۱۴۰۵/۰۵/۱۴

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

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

پس متوجه می شویم که انجام رگرسیون خطی (Linear Regression) در اکسل بسیار ساده است و نیازی به فرمول‌های پیچیده ریاضی ندارد. اکسل ابزارهای مختلفی برای این کار دارد؛ از روش‌های سریع با فرمول‌های آماری گرفته تا ابزار پیشرفته‌ای که تمام خروجی‌های رگرسیون (مانند ضریب همبستگی، خطای استاندارد و نمودار) را یک‌جا به شما تحویل می‌دهد.

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

اگر می‌خواهید روند داده‌ها را روی نمودار ببینید و معادله خط رگرسیون ( $y = mx + b$ ) را مستقیماً روی نمودار داشته باشید:

آماده‌سازی داده‌ها: اطلاعات خود را در دو ستون وارد کنید (مثلاً ستون A مقدار متغیر مستقل $X$ مثل تبلیغات، و ستون B مقدار متغیر وابسته $Y$ مثل فروش).
رسم نمودار (Scatter Plot):
- هر دو ستون داده‌ها را با هم انتخاب کنید.
- به تب Insert در بالای صفحه بروید.
- در بخش Charts، روی آیکون نمودار نقطه‌ای یا پراکندگی (Scatter - نموداری که فقط نقاط را نشان می‌دهد، بدون خط اتصال) کلیک کنید.
اضافه کردن خط روند (Trendline):
- روی یکی از نقاط روی نمودار راست‌کلیک کنید.
- گزینه Add Trendline را انتخاب کنید.
- در پنل باز شده در سمت راست، مطمئن شوید که گزینه Linear (خطی) انتخاب شده است.
نمایش معادله خط و ضریب همبستگی ( $R^2$ ):
- در همان پنل سمت راست، به پایین بروید و تیک دو گزینه زیر را بزنید:
  - Display Equation on chart: معادله خط رگرسیون را روی نمودار چاپ می‌کند.
  - Display R-squared value on chart: مقدار ضریب تعیین ( $R^2$ ) را نشان می‌دهد که قدرت رابطه بین متغیرها را می‌سنجد (هر چه به عدد ۱ نزدیک‌تر باشد، دقت مدل بهتر است).

روش دوم: استفاده از ابزار تحلیل آماری (Regression Tool)

اگر به یک گزارش کامل آماری شامل ضرایب، خطاها، مقادیر P-value و تحلیل‌های کامل رگرسیونی نیاز دارید، باید از افزونه داخلی اکسل استفاده کنید:

گام اول: فعال کردن افزونه Analysis ToolPak (اگر از قبل فعال نیست)

از منوی File به بخش Options بروید.
روی گزینه Add-ins در منوی سمت چپ کلیک کنید.
در پایین پنجره، مقابل قسمت Manage، مطمئن شوید که روی Excel Add-ins قرار دارد و روی دکمه Go کلیک کنید.
در پنجره کوچک باز شده، تیک گزینه Analysis ToolPak را بزنید و روی OK کلیک کنید. (حالا اگر به تب Data بروید، در سمت راست گزینه‌ای به نام Data Analysis اضافه شده است).

گام دوم: اجرای رگرسیون

به تب Data بروید و روی گزینه Data Analysis کلیک کنید.
از لیست باز شده، گزینه Regression را انتخاب کرده و روی OK کلیک کنید.
در پنجره تنظیمات رگرسیون:
- Input Y Range: سلول‌های مربوط به متغیر وابسته (خروجی/هدف) را انتخاب کنید.
- Input X Range: سلول‌های مربوط به متغیر مستقل (ورودی/دلیل) را انتخاب کنید.
- اگر در انتخاب سلول‌ها، عنوان ستون‌ها را هم گرفته‌اید، تیک گزینه Labels را فعال کنید.
- در قسمت Output Options مشخص کنید که گزارش نهایی در همین شیت نمایش داده شود (New Worksheet Ply) یا در یک فایل/شیت جدید.
روی OK کلیک کنید.

اکسل یک جدول کامل و مفصل از تحلیل رگرسیون به شما می‌دهد که شامل عرض از مبدأ، شیب خط (Coefficients)، مقادیر آماری و خطاهای استاندارد است که به راحتی می‌توانید از آن‌ها برای پیش‌بینی‌های آینده استفاده کنید.

3 نکته کلیدی

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

آموزش گام به گام تصویری و نکات بیشتر
مفروضات مهم

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

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

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

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

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

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

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

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

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

توضیح نتایج

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

توضیح نتایج

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

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

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

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