رگرسیون در اکسل – آموزش به زبان ساده + نحوه رسم نمودار

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

رگرسیون در اکسل چیست و انواع آن کدامند؟

تحلیل رگرسیون می‌تواند برای بررسی روابط بین یک متغیر وابسته و یک یا چند منغیر مستقل مورد استفاده قرار بگیرد. می‌توان از رگرسیون برای ارزیابی رابطه بین متغیرها و همچنین چگونگی ارتباط آن‌ها در آینده استفاده کرد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel

اینجا کلیک کنید

مثال ساده‌ای که در ادامه آمده را در نظر بگیرید. فرض کنید یک موسسه آموزشی قصد دارد تعداد افرادی که سال آینده در دوره‌های آموزشی برنامه‌نویسی این مجموعه (متغیر وابسته) شرکت می‌کنند را پیش‌بینی کند. رگرسیون می‌تواند با استفاده از تعداد دانشجویان سال قبل، افزایش یا کاهش بازار کار برنامه‌نویسی، میزان رضایت مشتریان قبلی و ... (متغیرهای مستقل)، یک نمودار قابل تحلیل و ارزیابی را ارائه دهد.

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

انواع تحلیل رگرسیون در علم آمار

به طور کلی، ۳ مدل تحلیل رگرسیون در علم آمار مورد استفاده قرار می‌گیرند:

• تحلیل رگرسیونی خطی ساده: ارتباط میان یک متغیر وابسته و یک متغیر مستقل
• تحلیل رگرسیونی خطی چندگانه: ارتباط میان یک متغیر وابسته و چند متغیر مستقل
• تحلیل رگرسیونی غیرخطی: برای ارتباط پیچیده از داده‌های متعدد که در آن‌‌ها، ارتباط میان متغیر وابسته و متغیرهای وابسته، غیرخطی است.

ما در این مقاله، به بررسی تحلیل رگرسیونی خطی ساده (یک متغیر مستقل و یک متغیر وابسته) می‌پردازیم.

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

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

$$Y=a+bX+\epsilon$$

در این فرمول، متغیرها به صورت زیر تعریف می‌شوند:

فیلم آموزش رایگان اکسل Excel — سریع و آسان در ۱۸۰ دقیقه

اینجا کلیک کنید

• Y: متغیر وابسته
• X: متغیر مستقل
• a: مقدار معادله خط در صورت 0 بودن X
• b: ضریب تغییر متغیر وابسته نسبت به متغیر مستقل
• $$\epsilon$$: مقدار خطا

مقدار خطا یا همان $$\epsilon$$ در پس‌زمینه برنامه اکسل محاسبه می‌شود. بنابراین نیازی نیست آن را در فرمول دخیل کنیم.

$$Y=a+bX$$

روش‌های مختلفی برای به دست آوردن رگرسیون در برنامه اکسل و تحلیل این فرمول وجود دارند که در ادامه، به دو روش اشاره خواهیم کرد.

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

رگرسیون یک تحلیل فنی است و هدف این است که به معادله خطی برسیم (a و b در فرمول بالا) و با وارد کردن یک متغیر (X)، مقدار متغیر وابسته (Y) را به دست آوریم.

فیلم آموزش اکسل Microsoft Excel 2013

اینجا کلیک کنید

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

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

• ابزار Analysis ToolPak
• نمودار پراکندگی با خط روند
• استفاده از فرمول

ما در این مقاله، 2 روش اول را بررسی می‌کنیم. روش سوم نیازمند وارد کردن فرمول و انجام محاسبات دستی است.

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

همان‌طور که ملاحظه می‌کنید، یک نمودار خطی برای ارتباط میان قد و وزن افراد (متغیرهای مستقل و وابسته) قابل مشاهده است.

تابع رگرسیون در اکسل با استفاده از Analysis ToolPak

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

در صفحه نتایج، روی «Regression» کلیک کنید.

بخش Regression مانند تصویر زیر باز می‎‌شود:

این ورودی‌ها را مطابق راهنمای زیر تنظیم کنید:

• «Input Y Range»: در این قسمت باید تمام سلول‌هایی که شامل متغیر وابسته شما هستند را اتنخاب کنید
• «Input X Range»: در این قسمت باید تمام سلول‌هایی که شامل متغیر مستقل شما هستند را انتخاب کنید.
• اگر داده‌های شما دارای نام ستون هستند، کادر «Labels» را تیک بزنید (در این مثال، ما برای ستون‌‌ها اسم تعیین کرده‌ایم؛ پس آن را تیک می‌زنیم).
• گزینه «confidence level» به صورت پیش‌فرض روی %95 تنظیم شده است که بر اساس نیاز کاربر، می‌تواند تغییر کند.
• در قسمت «Output options» می‌توانید محل نمایش خروجی تحلیل رگرسیون در اکسل را انتخاب کنید. در این مثال، می‌خواهیم خروجی را در همان شیت ببینیم.
• در زیر گزینه «Residuals»، بعضی از ورودی‌های اختیاری مثل «Residuals، Residual Plots«، «Standardized Residuals» و «Line Fit Plots» وجود دارند که می‌توانید بنا به نیاز خود آن‌ها را انتخاب کنید. پیشنهاد می‌کنیم فقط چک‌باکس Residuals را علامت بزنید تا بتوانید پراکندگی بین مقادیر پیش‌بینی شده و واقعی را در خروجی ببینید.
• در زیر گزینه «Normal Probability»، می‌توانید «Normal Probability Plots» را انتخاب کنید. انتخاب این گزینه باعث می‌شود، پیش‌بینی‌های انجام شده به سطح نرمال و طبیعی، نزدیک‌تر شود.
• در نهایت، روی OK کلیک کنید.

بعد از این‌که روی Ok کلیک کردید، برنامه اکسل تحلیل رگرسیون را در کمتر از 1 ثانیه، برای شما انجام می‌دهد.

این‌جای کار، همه چیز آسان به نظر می‌رسد. ولی باید بدایند که تفسیر این نتایج و به دست آوردن یک تحلیل منطقی از آن‌ها، به شناخت دقیق از چند خروجی این تابع بستگی دارد. یکی از بخش‌های مهم این خروجی، «ضریب تعیین» «R Square» است که در پایین جدول «SUMMARY OUTPUT» قرار دارد. این قسمت، دقت رگرسیون به دست آمده را نشان می‌دهد. در این مثال، مقدار R Square برابر 0.9547 است که نشان می‌دهد این مدل، دارای دقت خوب و قابل قبول %95.47 است.

بخش مهم دیگر از این خروحی، جدول ضرایب (Coefficients) است. همان‌طور که گفتیم هدف ما از رگرسیون، به دست آوردن دو ضریب a و b در فرمول Y=a+bX است. دو ضریبی که در این بخش (مانند تصویر زیر) می‌بینید، به ترتیب، a و b خواهند بود.

اکنون تابع رگرسیون ما به صورت زیر تعریف می‌شود:

$$W=0.6746\times H-38.45508$$

نکته: مقدار شیب برای ارتفاع یا همان b برابر با 0.6746 و برای قطع کردن یا a، مساوی 38.45508 است.

پس شما اکنون فرمولی در اختیار دارید که تنها با وارد کردن ارتفاع یا قد افراد مختلف (H)، می‌توانید وزن آن‌ها (W) را پیش‌بینی کنید. این همان هدفی است که در این مثال دنبال کردیم.

نمودار رگرسیون در اکسل با استفاده از Scatterplot با Trendline

در این روش قصد داریم معادله یا همان فرمول رگرسیون را روی خود نموادر پراکندگی داده‌ها و به صورت نمودار رگرسیون در اکسل به دست بیاوریم. برای این منظور، باید ابتدا کل داده‌های دو ستونی خود (از جمله هدر) را انتخاب کنید. همان‌طور که در تصویر زیر نشان داده شده، روی Insert کلیک کرده و در قسمت «Graphs»، می‌توانید «Scatter Plot» را انتخاب کنید.

یک نمودار خروجی را در زیر مشاهده می‌کنید:

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

در پایین بخش «Format Trendline»، کادر مربوط به «Display Equation on Chart» را علامت بزنید.

در نهایت می‌توانید معادله رگرسیون در اکسل را بر اساس داده‌هایی که برای متغیرهای وزن (وابسته) و ارتفاع (مستقل)، روی نمودار مشاهده نمایید.

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

سخن پایانی

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