در اینجا نحوه استفاده از ابزارهای داخلی اکسل برای آسان کردن تجزیه و تحلیل آماری آورده شده است.
نمودارهای باقیمانده نقش اساسی در تحلیل رگرسیون دارند. آنها به شما می گویند که خط رگرسیون شما چقدر رابطه بین دو متغیر را نشان می دهد. راهنمای زیر نحوه محاسبه و رسم باقیمانده اکسل را با استفاده از دو روش توضیح می دهد.
اما قبل از اینکه به چیزهای خوب بپردازیم، اجازه دهید به طور خلاصه باقیمانده ها و اهمیت آنها را در تحلیل رگرسیون درک کنیم.
باقیمانده ها چیست و چرا مهم هستند؟
باقیمانده تفاوت بین مقدار مشاهده شده (واقعی) و مقدار پیش بینی شده است. به عبارت ساده تر، باقیمانده یک خطا است. به عنوان مثال، اگر مقدار مشاهده شده 10 باشد و مدل شما مقدار 8 را بدهد، آنگاه مقدار باقیمانده 2 خواهد بود.
از سوی دیگر، نمودار باقیمانده، نمودار پراکنده ای است که در آن متغیر مستقل (سن) در محور افقی و باقیمانده ها (خطاها) در محور عمودی رسم می شوند.
خوب، می دانید باقیمانده ها چیست، اما چرا مهم هستند؟ برای درک آن، مورد زیر را در نظر بگیرید:
ما می خواهیم رابطه بین سن و نرخ پس انداز را درک کنیم. برای آن، یک خط رگرسیون ترسیم می کنیم. با این حال، ما مطمئن نیستیم که آیا خط رگرسیون به درستی رابطه بین دو متغیر را نشان می دهد. اینجاست که باقیمانده ها وارد می شوند. ما از نمودار باقیمانده برای بررسی صحت خط رگرسیون استفاده می کنیم.
اکنون که می دانید باقیمانده ها چیست، بیایید نحوه ایجاد نمودار باقیمانده در اکسل را بیاموزیم:
نحوه ایجاد یک نمودار باقیمانده با استفاده از معادله خط روند
اولین روش ترسیم باقیمانده ها در اکسل از یک معادله خط روند برای محاسبه مقادیر پیش بینی شده برای داده های ما استفاده می کند. هنگامی که آن را بدست آوریم، می توانیم با کم کردن مقادیر پیش بینی شده از مقادیر مشاهده شده، باقیمانده ها را محاسبه کنیم. در نهایت یک نمودار پراکندگی با پیش بینی کننده ها در محور افقی و باقیمانده ها در محور عمودی ایجاد می کنیم.
در حالی که این روش به مراحل بیشتری نسبت به روش دوم نیاز دارد، اما راهی عالی برای درک مفهوم است. هنگامی که به درک مناسبی دست یافتید، می توانید از روش دوم استفاده کنید که به زمان و تلاش کمتری نیاز دارد.
اجازه دهید شما را در طول فرآیند، یک مرحله در یک زمان راهنمایی کنیم:
مرحله 1: داده ها را وارد کنید
با وارد کردن مقادیر پیش بینی کننده (زیر ستون X) و مقادیر مشاهده شده (زیر ستون Y) شروع می کنیم:
مرحله 2: یک نمودار پراکندگی از داده ها ایجاد کنید
اکنون با انجام مراحل زیر یک نمودار پراکندگی از داده ها ایجاد می کنیم:
اگر با نمودارهای پراکندگی آشنا نیستید، در اینجا یک مقاله جامع در مورد ایجاد نمودار پراکندگی در اکسل و ارائه داده های خود آورده شده است. همچنین می توانید یاد بگیرید که از نمودار پراکندگی در اکسل برای پیش بینی رفتار داده ها استفاده کنید.
- داده ها را انتخاب کنید (زیر ستون های X و Y).
- به نوار Excel بروید و روی Insert کلیک کنید.
- در قسمت نمودارها روی فلش زیر گزینه Scatterplot کلیک کنید.
- اولین Scatterplot (پراکندگی فقط با نشانگرها) را انتخاب کنید.
- یک نمودار پراکندگی مشابه نمودار زیر دریافت خواهید کرد:
مرحله 3: یک خط روند اضافه کنید و معادله خط روند را در Scatterplot نمایش دهید
برای افزودن خط روند به نمودار پراکنده مراحل زیر را دنبال کنید:
- روی نمودار پراکندگی کلیک کنید.
- به نوار Excel بروید و روی Layout کلیک کنید.
- در قسمت Background، روی فلش زیر نماد Trendline کلیک کنید.
- گزینه Linear Trendline را انتخاب کنید.
اکنون یک خط (خط روند) را می بینید که نقاط روی نمودار پراکندگی شما را قطع می کند. برای نمایش معادله خط روند در نمودار پراکنده، مراحل زیر را دنبال کنید:
- دوباره به آیکون Trendline بروید و روی فلش زیر آن کلیک کنید.
- روی More Trendline Options کلیک کنید.
- کادر محاوره ای Format Trendline ظاهر می شود.
- گزینه Display Equation on chart را در پایین کادر محاوره ای Format Trendline علامت بزنید. معادله خط روند روی نمودار نشان داده می شود.
مرحله 4: مقادیر پیش بینی شده را محاسبه کنید
برای محاسبه مقادیر پیش بینی شده، ستون دیگری (Z) در کنار داده های خود ایجاد کنید و مراحل زیر را دنبال کنید:
- فرمول خط روند نشان داده شده در نمودار را وارد کرده و آن را در زیر ستون Z (سلول C2) قرار دهید. x را در فرمول با مقادیر زیر ستون X تغییر دهید.
- بنابراین برای اولین مقدار پیش بینی شده، فرمول به صورت زیر داده می شود: =1.5115*A2+23.133
- A2 را با A3 در فرمول خط روند برای دومین مقدار پیش فرض جایگزین کنید.
- برای سومین مقدار پیش‌بینی‌شده، A3 را با A4 جایگزین کنید، و به همین ترتیب، تا زمانی که تمام مقادیر پیش‌بینی‌شده برای پیش‌بینی‌کننده‌های مربوطه را به دست آورید.
مرحله 5: مقادیر باقیمانده را پیدا کنید
اکنون که مقادیر پیش بینی شده را بدست آورده ایم، می توانیم با کم کردن مقادیر پیش بینی شده از مقادیر مشاهده شده (واقعی) در زیر ستون Y، مقادیر باقیمانده را پیدا کنیم. فرمول اولین مقدار باقیمانده =B2-C2 خواهد بود. برای دومی، فرمول =B3-C3 و غیره خواهد بود.
مرحله 6: طرح باقیمانده را ایجاد کنید
برای ایجاد نمودار باقیمانده، به مقادیر پیش‌بینی‌کننده و باقیمانده نیاز داریم. اکنون که هر دو را داریم، مراحل زیر را دنبال کنید:
- ستون های Y و Z را انتخاب کنید.
- راست کلیک کرده و گزینه Hide را انتخاب کنید.
- حالا ستون X و Residuals را انتخاب کنید.
- به تب Insert در نوار Excel بروید.
- روی فلش زیر نماد Scatter کلیک کنید.
- گزینه Scatter with only Markers را انتخاب کنید.
- همانطور که در زیر نشان داده شده است، نمودار باقیمانده خود را دریافت خواهید کرد.
نحوه ایجاد نمودار باقیمانده با Analysis ToolPak در اکسل
برای روش دوم، تنها کاری که باید انجام دهید این است که داده ها را در اختیار اکسل قرار دهید و تمام کارها را انجام می دهد. شما می توانید یک نمودار باقیمانده را تنها با چند کلیک ایجاد کنید. اما برای این کار، باید بسته ابزار تحلیل اکسل را بارگیری کنید. بیا شروع کنیم:
مرحله 1: بسته ابزار تجزیه و تحلیل را بارگیری کنید
- به تب File بروید و روی گزینه ها کلیک کنید.
- کادر محاوره ای Excel Options ظاهر می شود.
- به کادر Manage (در پایین) بروید، Excel Add-ins را انتخاب کنید و روی Go کلیک کنید.
- کادر محاوره ای Add-Ins ظاهر می شود.
- کادر Analysis ToolPak را علامت بزنید و ok را بزنید.
- حالا به نوار Excel رفته و روی Data کلیک کنید.
- نماد تجزیه و تحلیل داده ها را در بخش تجزیه و تحلیل خواهید یافت.
مرحله 2: داده ها را وارد کنید
- ستون های A و D را انتخاب کنید و روی ماوس کلیک راست کنید. روی Unhide کلیک کنید.
- حالا ستون های X و Y را کپی کنید. متوجه خواهید شد که پس از باز کردن ستون ها، نمودار تغییر کرده است. برای برگرداندن آن به نمودار باقیمانده اصلی، باید ستون‌های Y و Z را مخفی کنیم (با انتخاب ستون‌های Y و Z، کلیک راست روی ماوس و انتخاب Hide).
- یک صفحه اکسل جدید باز کنید و ستون های X و Y را جایگذاری کنید.
- روی گزینه تازه ظاهر شده Data Analysis در بخش Analysis در تب Data کلیک کنید.
- یک کادر محاوره ای تجزیه و تحلیل داده ظاهر می شود.
- Regression را در قسمت Analysis Tools پیدا کرده و انتخاب کنید.
- روی OK کلیک کنید. کادر محاوره ای رگرسیون ظاهر می شود.
- مقادیر را در ستون Y (پیش‌بینی‌کننده‌ها، B2:B11) در قسمت Input Y Range وارد کنید.
- محدوده سلولی را در ستون X (متغیرهای مستقل، A2:A11) در قسمت Input X Range وارد کنید.
متوجه خواهید شد که پس از باز کردن ستون ها، نمودار تغییر کرده است. برای برگرداندن آن به نمودار باقیمانده اصلی، باید ستون‌های Y و Z را مخفی کنیم (با انتخاب ستون‌های Y و Z، کلیک راست روی ماوس و انتخاب Hide).
مرحله 3: طرح باقیمانده را ایجاد کنید
در کادر محاوره‌ای رگرسیون، در زیر گزینه‌های خروجی، تیک New Worksheet Ply را بزنید و روی OK کلیک کنید.
اکسل به طور خودکار نمودار باقیمانده را به همراه خروجی های زیر ایجاد می کند که می توانید از آنها برای بررسی قابلیت اطمینان مدل رگرسیونی خود استفاده کنید:
- آمار رگرسیون
- جدول ANOVA
- جدول ضرایب
- خروجی باقیمانده
بسته ابزار تجزیه و تحلیل اکسل را کاوش کنید
مجموعه ابزار تجزیه و تحلیل اکسل طیفی از قابلیت های آماری ارزشمند را ارائه می دهد که می توانید از آنها برای تجزیه و تحلیل مجموعه داده های خود استفاده کنید. پس از ایجاد نمودار باقیمانده از طریق ابزار تجزیه و تحلیل اکسل، ممکن است در حالی که به تمام جداول پیچیده نگاه می کنید، سر خود را خاراندن کنید. اما هنگامی که یاد بگیرید چگونه تجزیه و تحلیل داده های بنیادی را در اکسل انجام دهید، اعداد آنقدر ترسناک به نظر نمی رسند.