نحوه محاسبه میانگین مربعات خطا (MSE) در مایکروسافت اکسل

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

در این آموزش، ما با جزئیات بیشتری توضیح می‌دهیم که MSE چیست و سپس نحوه محاسبه MSE در اکسل را به شما نشان می‌دهیم.

متوسط ​​خطای مربع چیست؟

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

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

MSE با گرفتن میانگین مجذور اختلاف بین مقادیر پیش‌بینی‌شده و واقعی متغیر هدف محاسبه می‌شود.

فرمول MSE به این صورت است:

کجا:

Σبه معنای مجموع مقادیر است؛

nاندازه نمونه یا تعداد مشاهدات است؛

Yiمقادیر مشاهده شده هستند و;

Ŷiمقادیر پیش‌بینی‌شده هستند.

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

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

نحوه محاسبه میانگین مربعات خطا در Microsoft Excel

دو روش اصلی برای محاسبه MSE در مایکروسافت اکسل وجود دارد: تابع SUMSQ، تابع AVERAGE و فرمول MSE. ما از مثال زیر برای نشان دادن نحوه محاسبه MSE با استفاده از هر یک از این توابع استفاده خواهیم کرد:

در این مثال ساده، به ارزش‌های فروش خیالی برای هر ماه نگاه می‌کنیم (ستون A). مقادیر برآورد شده در ستون B و مقادیر واقعی در ستون C نشان داده شده است..

محاسبه MSE با استفاده از تابع SUMSQ

از تابع SUMSQ در اکسل برای محاسبه مجموع مربعات اعداد در یک محدوده استفاده می شود. در اینجا نحوه استفاده از آن برای محاسبه MSE آمده است:

یک ستون جدید در ستون D به نام "تفاوت" ایجاد کنید. این نشان دهنده تفاوت بین مقادیر مورد انتظار و واقعی است.

از فرمول "C2-B2" برای محاسبه تفاوت نقاط داده در هر ردیف استفاده کنید.

یک سلول جدید را انتخاب کنید و نحو زیر را تایپ کنید: "=SUMSQ(D2:D13/COUNT(D2:D13)". سلول های فرمول را با سلول های صحیح از صفحه گسترده اکسل خود جایگزین کنید. نتیجه نهایی در این سلول است. MSE شما است.

محاسبه MSE با استفاده از تابع AVERAGE

از تابع AVERAGE در MS Excel برای محاسبه میانگین طیفی از اعداد استفاده می شود. این روش همان نتیجه تابع SUMSQ را برمی گرداند، اما هر مرحله به صورت دستی انجام می شود.

مراحل 1 و 2 که در روش SUMSQ توضیح داده شده است را انجام دهید. این تفاوت بین مقادیر پیش‌بینی‌شده و داده‌های واقعی را برمی‌گرداند.

ستون E را "مربع تفاوت‌ها" نامگذاری کنید، سپس از فرمول "=D2^2" برای محاسبه مجذور اختلاف هر مقدار در ستون D استفاده کنید.

یک سلول جدید را انتخاب کنید و مقدار میانگین ستون E را محاسبه کنید. با این کار میانگین مقادیر مربعی که قبلا محاسبه کرده اید را برمی گرداند. برای انجام این کار، "=AVERAGE(E2:E12)" را تایپ کنید و Enterرا فشار دهید. مطمئن شوید که سلول ها را با سلول هایی که در کاربرگ خود استفاده کرده اید جایگزین کنید. مقدار داده به دست آمده MSE شما است.

تحلیل آماری در Microsoft Excel

Microsoft Excel یکی از قدرتمندترین ابزارها برای تجزیه و تحلیل داده ها بوده است. امیدواریم با این آموزش، اکنون بتوانید از Excel برای محاسبه MSE به راحتی استفاده کنید. و از آنجایی که آنها بسیار شبیه هستند، باید بتوانید همان محاسبات را در Google Sheets انجام دهید.

.

پست های مرتبط: