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

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

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

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

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

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

معادله رگرسیون خطی چیست؟

معادله «Y= Bx + A + ε» یک معادله رگرسیون خطی است. در معادله ذکر شده، «X» متغیر مستقل، «Y» متغیر وابسته، «A»  مقدار معادله در حالتی که میزان «X» صفر است، «B » شیب خط رگرسیون و «ε» خطای تصادفی می‌باشد. در محاسبه رگرسیون خطی، همیشه احتمال خطا وجود دارد؛ زیرا، در دنیای واقعی، هیچ چیز را نمی‌توان به صورت دقیق پیش‌بینی کرد.

 

خوشبختانه بعد از آموزش رگرسیون در اکسل و استفاده اکسل، درصد خطا از بین خواهد رفت و معادله رگرسیون به صورت «Y= Bx + A» در اکسل نوشته خواهد شد.

 

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

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

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

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

راه‌های متفاوتی برای پیدا کردن مقادیر A و B و پیش‌بینی معادله رگرسیونی وجود دارد که این روش‌ها به شرح زیر می‌باشد:

• استفاده از نمودارفرمول معادله رگرسیون.
• استفاده از نمودار Scatter با خط Trendline
• استفاده از ابزار Regresion

 

 

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

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

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

۱) آموزش رگرسیون در اکسل با استفاده از فرمول:

خوشبختانه اکسل، توافع متفاوتی جهت بررسی شاخص‌های رگرسیون در اختیار ما گذاشته است. این توابع شامل  Slope, Intercept, Linest و Corel می‌شود.

با استفاده از تابع  Linest Function می‌توانید خط رگرسیونی را با استفاده از حداقل مربعات، رسم کنید. بعد از استفاده از تابع، مجموعه‌ای از جواب‌ها را بدست خواهید آورد.

جهت پیدا کردن مقادیر  A و B از طریق تابع  Linest Function ابتدا یک محدوده از سلول‌ها را انتخاب کرده و بعد از آن، محدوده داده‌ها را انتخاب و ثبت کنید. بعد از بسته شدن پرانتز، عبارت « Ctrl+Shift+Enter» بزنید تا مقادیر دو سلول ظاهر شود.

مقادیر بدست آمده، همان مقادیر A و B معادله خط رگرسیون هستند. می‌توانید با جایگذاری آن‌ها در معادله، معادلی اصلی رگرسیونی را بدست آورید.

در صورتی که تمایل به فرمول نویسی در اکسل ندارید، می‌توانید از توابع پیش فرض اکسل برای محاسبه شیب خط «Slope» و مقدار عرض از مبدا « Intercept» استفاده کنید.

 

 

محاسبه توابع SLOPE و INTERCEPT برای محاسبه رگرسیون:

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

ابتدا مقادیر دو تابع  SLOPE و INTERCEPT اندازه‌ می‌گیریم و براساس مقادیر به دست آمده، معادله رگرسیونی را محاسبه خواهیم کرد. با استفاده از تایع «SLOPE» شیب تابع رگرسیون و با استفاده از تابع «INTERCEPT» قادر به محاسبه عرض از مبدا معادله رگرسیون خطی خواهید بود.

همچنین با استفاده از توابع زیر، می‌توانید در محاسبات‌تان دقیق‌تر عمل کنید:

 تابع Forecast: جهت پیش‌بینی y متناظر با یک x جدید در معادله رگرسیون خطی.

۲) آموزش رگرسیون در اکسل با استفاده از ابزار Regresion:

ابتدا به قسمت «Data» مراجعه کنید و گزینه «Data Analysis» در قسمت «Analysis» انتخاب نمایید. سپس، گزینه «Regression» انتخاب کرده و محدوده متغیرهای Y و X را انتخاب کنید. (توجه فرمایید که این ستون‌ها باید با یکدیگر متصل باشند.

 

در گام بعدی، گزینه «Labels» را فعال کنید. به قسمت « Output Range» مراجعه کرده و یک سلول را انتخاب کنید. سپس، تیک گزینه «Residuals» فعال کنید و گزینه «OK» را انتخاب نمایید.

در آخر، شاهد خروجی اکسل خواهید بود.

۳) آموزش رگرسیون در اکسل با استفاده از نمودار Scatter با خط Trendline:

رسم نمودار در اکسل، یکی از مهم‌ترین مراحل آموزش رگرسیون در اکسل است. برای ترسیم رگرسیون خطی در اکسل باید داده‌ها را در یک نمودار scatter قرار دهید.سپس از منوی «Chart Tools» گزینه «Layout» انتخاب نمایید.

گزینه «Trendline» در پنجره جدید انتخاب کرده و بر روی گزینه «Linear Trendline» کلیک نمایید.

جهت مشاهده مقدار R² از منوی «Trendline» گزینه «More Trendline Options» انتخاب کنید و در آخر، گزینه  «Display R-squared value on chart» فعال نمایید. در این مرحله شاهد میزان قدرت رابطه خواهید بود.

نکته:

نقاط نمودار را انتخاب کرده و راست کلیک کنید. سپس گزینه «add trendline» انتخاب کرده و بعد از نمایان شدن پنچره «format trendline» به بخش « trend/regression type » مراجعه نمایید و نوع رگرسیونی که می‌خواهید را انتخاب کنید.

در این قسمت از آموزش رگرسیون در اکسل، قصد داریم تا شما را با انواع رگرسیون آشنا کنیم:

• Linear:این گزینه همان رگرسیون خطی است که در مطالل فوق ،به طور کامل، در رابطه با آن صحبت کرده‌ایم.
• Polynomial: رگرسیونی با تابع تخمین چند جمله‌ای از درجه n ام است. مقدار n را می‌توانید در کادر «period» وارد کنید.
• Exponential :رگرسیونی با تابع تخمین نمایی از نوع e^x  است.

نکته: در صورت منفی ای ۰ بودن داده‌های Y، داده‌ها غیرقابل استفادهخواهند بود. همچنین، در صورت منفی یا ۰ بودن داده‌ها X، شاهد ظاهر شدن خطا خواهید بود.

• Power:رگرسیونی با تابع تخمین از نوع تابع توانی «Y=C.x^b» است.
• Logarithmic: یک تابع رگرسیونی با تابع تخمین لگاریتمی «log x » می‌باشد.
• Moving average:از این گزینه می‌توانید برای محاسبه متغیرهایی که به صورت سری زمانی هستند، استفاده کنید.

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

توجه داشته باشید که قبل از تحلیل رگرسیون در اکسل باید افزونه « Data Analysis ToolPak » نصب نمایید. با استفاده از این افزونه می‌توانید به راحتی قادر به محاسبه محدوده آماری خواهید بود و دیگر نیازی به رسم خط رگرسیون ندارید.

جهت نصب افزونه « Data Analysis ToolPak » به تب «Data» مراجعه کنید و گزینه « Data Analysis » انتخاب نمایید. در صورتی که نوار ابزارتان فاقد گزینه «Data Analysis» باشد، باید به عبارت « Office » مراجعه کرده و گزینه «Options» انتخاب کنید و در آخر، از قسمت «Add-ins» گزینه مورد نظر را اضافه نمایید.

بعد از نصب افزونه «Data Analysis ToolPak » با چند کلیک ساده می‌توانید خروجی معادلات را مشاهده نمایید.

توضیح نتایج در رگرسیون:

R² نشان دهنده میران تغییرات متغیر وابسته در مقابل تغییرات متغییر مستقل است که آن را با عنوان ضریب تعیین «R Square» می‌شناسیم. ضریب تعیین نشان‌دهنده میزان سازگاری مدل رگرسیونی با داده‌های معادله است.

محدوده جواب‌های R² بین ۰ تا ۱ متغییر است و هرچه به ۱ نزدیک‌تر باشد، مدل سازگاری بیشتری خواهد داشت. یکی دیگر مقادیری که در آموزش رگرسیون در اکسل باید با آن آشنا باشید، مقدار P است که در محدوده ۰ تا ۱۰ متغیر می‌باشد و نشان‌دهنده معنادار بودن آزمون می‌باشد.

همانطور که متوجه شدید، P میزان همبستگی متغیرهای مستقل و وابسته را نشان می‌دهد و برعکس R² ، هرچه مقدار P کمتر باشد، بهتر خواهد بود.

رعکس مقدار R²، چون مقدار p همبستگی بین متغیرهای وابسته و مستقل را نشان می دهد پس هرچه کوچکتر باشد بهتر است.

F، عبارت دیگری است که قصد دارم در این بخش از آموزش رگرسیون در اکسل به شما معرفی نمایم. برای آگاهی از قابل اعتماد بودن نتایج، باید مقدار F را محاسبه کنید. اگر مقدار به دست آمده کمتر از ۰/۰۵ باشد، می‌توانید به نتایج به دست آمده اعتماد کنید.