یکی از شاخص های توصیف کیفیت مدل ساخته شده در آمار ، ضریب تعیین (R ^ 2) است که به آن مقدار اطمینان تقریبی نیز گفته می شود. با استفاده از آن می توانید میزان دقت پیش بینی را تعیین کنید. بیایید دریابیم که چگونه می توانید این شاخص را با استفاده از ابزارهای مختلف اکسل محاسبه کنید.
محاسبه ضریب تعیین
بسته به سطح ضریب تعیین ، مرسوم است که مدل ها را به سه گروه تقسیم کنیم:
- 0.8 - 1 - مدل با کیفیت خوب؛
- 0.5 - 0.8 - مدل با کیفیت قابل قبول؛
- 0 - 0.5 - مدل بی کیفیت.
در حالت دوم ، کیفیت مدل نشانگر عدم امکان استفاده از آن برای پیش بینی است.
انتخاب نحوه محاسبه مقدار مشخص شده در اکسل به این بستگی دارد که آیا رگرسیون خطی است یا خیر. در حالت اول می توانید از این تابع استفاده کنید KVPIRSON، و در دوم باید از یک ابزار ویژه از بسته آنالیز استفاده کنید.
روش 1: محاسبه ضریب تعیین با یک عملکرد خطی
اول از همه ، ما می یابیم که چگونه ضریب تعیین برای یک عملکرد خطی را پیدا کنیم. در این حالت ، این شاخص برابر با مربع ضریب همبستگی خواهد بود. ما آن را با استفاده از توابع اکسل داخلی به عنوان مثال از یک جدول خاص محاسبه می کنیم ، که در زیر آورده شده است.
- سلولی را انتخاب کنید که ضریب تعیین بعد از محاسبه آن نمایش داده شود و بر روی نماد کلیک کنید "درج عملکرد".
- شروع می کند جادوگر ویژگی. در حال حرکت به دسته خود است "آماری" و نام را علامت گذاری کنید KVPIRSON. بعد روی دکمه کلیک کنید "خوب".
- پنجره آرگومانهای عملکرد شروع می شود. KVPIRSON. این عملگر از گروه آماری برای محاسبه مربع ضریب همبستگی تابع پیرسون ، یعنی یک تابع خطی طراحی شده است. و همانطور که به یاد داریم ، با یک عملکرد خطی ، ضریب تعیین دقیقا برابر با مربع ضریب همبستگی است.
نحو این جمله عبارت است از:
= KVPIRSON (مقادیر معروف_y_ ؛ شناخته شده_x)بنابراین ، یک تابع دارای دو عملگر است که یکی از آنها لیستی از مقادیر عملکرد است و دوم آرگومان. اپراتورها می توانند مستقیماً به عنوان مقادیر شمارش شده از طریق یک قطعه نمایش داده شوند (;) ، و به شکل پیوند به محدوده هایی که در آن قرار دارند. این گزینه دوم است که در این مثال توسط ما استفاده خواهد شد.
مکان نما را در قسمت زمینه تنظیم کنید ارزشهای شناخته شده. دکمه سمت چپ ماوس را نگه می داریم و محتویات ستون را انتخاب می کنیم "Y" جداول همانطور که مشاهده می کنید ، آدرس آرایه داده های مشخص شده بلافاصله در پنجره نمایش داده می شود.
به همین روش ، فیلد را پر کنید ارزشهای شناخته شده x. مکان نما را در این قسمت قرار دهید ، اما این بار مقادیر ستون را انتخاب کنید "X".
بعد از نمایش همه داده ها در پنجره آرگومان ها KVPIRSONبر روی دکمه کلیک کنید "خوب"واقع در پایین آن
- همانطور که مشاهده می کنید ، پس از این برنامه ضریب تعیین را محاسبه می کند و نتیجه را در سلول که حتی قبل از تماس انتخاب شده است نمایش می دهد جادوگران عملکرد. در مثال ما مقدار شاخص محاسبه شده معلوم شده است 1. این بدان معنی است که مدل ارائه شده کاملاً قابل اعتماد است ، یعنی خطا را از بین می برد.
درس: جادوگر ویژه در Microsoft Excel
روش 2: محاسبه ضریب تعیین در توابع غیرخطی
اما گزینه فوق برای محاسبه مقدار مورد نظر فقط برای توابع خطی قابل استفاده است. برای محاسبه آن در یک تابع غیرخطی چه باید کرد؟ در اکسل چنین فرصتی وجود دارد. می توان آن را با ابزار انجام داد. "رگرسیون"که بخشی از بسته بندی است "تجزیه و تحلیل داده ها".
- اما قبل از استفاده از ابزار مشخص شده ، باید خود آن را فعال کنید بسته تحلیل، که به طور پیش فرض در اکسل غیرفعال است. به برگه بروید پروندهو سپس به "گزینه ها".
- در پنجره ای که باز می شود ، به بخش بروید "افزودنیها" با حرکت به منوی عمودی سمت چپ. در قسمت پایین سمت راست پنجره یک میدان وجود دارد "مدیریت". از لیست زیر مجموعه های موجود در آنجا ، نام را انتخاب کنید "افزودنیهای اکسل ..."و سپس بر روی دکمه کلیک کنید "برو ..."واقع در سمت راست میدان.
- پنجره افزودنیها راه اندازی می شوند. در قسمت مرکزی آن لیستی از افزونه های موجود است. کادر کنار را در کنار موقعیت قرار دهید بسته تحلیل. پس از این ، روی دکمه کلیک کنید "خوب" در سمت راست رابط پنجره.
- بسته ابزار "تجزیه و تحلیل داده ها" در نمونه فعلی اکسل فعال خواهد شد. دسترسی به آن روی روبان موجود در زبانه واقع شده است "داده". ما به برگه مشخص شده حرکت می کنیم و روی دکمه کلیک می کنیم "تجزیه و تحلیل داده ها" در گروه تنظیمات "تحلیل".
- پنجره فعال می شود "تجزیه و تحلیل داده ها" با لیستی از ابزارهای تخصصی پردازش اطلاعات. مورد را از این لیست انتخاب کنید "رگرسیون" و روی دکمه کلیک کنید "خوب".
- سپس پنجره ابزار باز می شود "رگرسیون". اولین بلوک تنظیمات است "ورودی". در اینجا در دو قسمت باید آدرس محدوده هایی را که مقادیر آرگومان و عملکرد در آن قرار دارد مشخص کنید. مکان نما را در قسمت میدان قرار دهید "فاصله ورودی Y" و محتوای ستون روی برگه را انتخاب کنید "Y". پس از نمایش آدرس آرایه در پنجره "رگرسیون"مکان نما را در میدان قرار دهید "فاصله ورودی Y" و سلول های ستون را به همان روش انتخاب کنید "X".
درباره پارامترها "برچسب" و صفر ثابت پرچم ها را قرار ندهید. کادر تأیید را می توان در کنار پارامتر تنظیم کرد. "سطح اطمینان" و در قسمت مقابل ، مقدار دلخواه نشانگر مربوطه را نشان دهید (95٪ به طور پیش فرض).
در گروه گزینه های خروجی باید مشخص کنید که نتیجه محاسبه در کدام منطقه نمایش داده می شود. سه گزینه وجود دارد:
- مساحت روی برگه فعلی؛
- ورق دیگر؛
- کتاب دیگری (پرونده جدید).
بگذارید اولین گزینه را انتخاب کنیم تا داده های منبع و نتیجه در همان کاربرگ قرار بگیرند. سوئیچ را نزدیک پارامتر قرار می دهیم "فاصله خروجی". در قسمت مقابل این مورد ، مکان نما را قرار دهید. با کلیک بر روی یک عنصر خالی روی برگه ، که برای تبدیل شدن به سلول فوقانی سمت چپ جدول خروجی محاسبه ، کلیک راست می کند. آدرس این عنصر باید در قسمت پنجره نمایش داده شود "رگرسیون".
گروه های پارامتر "مانده" و "احتمال عادی" نادیده بگیرید ، زیرا آنها برای حل کار مهم نیستند. پس از آن بر روی دکمه کلیک کنید "خوب"واقع در گوشه بالا سمت راست پنجره "رگرسیون".
- این برنامه بر اساس داده های قبلاً وارد شده محاسبه می شود و نتیجه را در دامنه مشخص نشان می دهد. همانطور که مشاهده می کنید ، این ابزار تعداد نسبتاً زیادی از نتایج را در پارامترهای مختلف روی یک صفحه نمایش می دهد. اما در زمینه درس فعلی ما به شاخص علاقه مند هستیم مربع R. در این حالت ، برابر با 0.947664 است ، که مدل انتخاب شده را به عنوان الگوی کیفیت خوب توصیف می کند.
روش 3: ضریب تعیین خط روند
علاوه بر گزینه های فوق ، می توان ضریب تعیین را مستقیماً برای خط روند در گرافیکی که روی کاربرگ اکسل ساخته شده نشان داد. خواهیم فهمید که چگونه می توان با یک مثال خاص این کار را انجام داد.
- ما یک نمودار براساس جدول آرگومان ها و مقادیر عملکرد داریم که برای مثال قبلی استفاده شده است. ما یک خط روند به آن خواهیم ساخت. با کلیک بر روی دکمه سمت چپ ماوس ، روی هر مکان از منطقه ساختمانی که نمودار در آن قرار دارد کلیک می کنیم. در همان زمان ، یک مجموعه اضافی از زبانه ها بر روی روبان ظاهر می شود - "کار با نمودار". برو به برگه "طرح بندی". روی دکمه کلیک کنید خط روندکه در بلوک ابزار واقع شده است "تحلیل". یک منو با انتخاب نوع خط روند ظاهر می شود. ما انتخاب نوع را که مربوط به یک کار خاص است متوقف می کنیم. بیایید یک نمونه برای مثال خود انتخاب کنیم "تقریب نمایی".
- اکسل یک خط روند را به صورت یک منحنی سیاه اضافی درست در نمودار ایجاد می کند.
- حال وظیفه ما نمایش ضریب تعیین خود است. بر روی خط روند راست کلیک کنید. منوی زمینه فعال می شود. ما انتخاب را در آن متوقف می کنیم "قالب خط روند ...".
برای انجام انتقال به پنجره فرمت خط روند ، می توانید یک عمل جایگزین انجام دهید. با کلیک بر روی آن با دکمه سمت چپ ماوس ، روند روند را انتخاب کنید. به برگه بروید "طرح بندی". روی دکمه کلیک کنید خط روند در بلوک "تحلیل". در لیستی که باز می شود ، روی آخرین مورد در لیست اقدامات کلیک کنید - "پارامترهای خط روند اضافی ...".
- پس از هر دو عمل فوق ، یک پنجره فرمت راه اندازی می شود که می توانید تنظیمات دیگری را انجام دهید. به طور خاص ، برای انجام وظیفه خود ، لازم است کادر کنار آن را بررسی کنید "مقدار اطمینان تقریبی (R ^ 2) را روی نمودار قرار دهید". در انتهای پنجره واقع شده است. یعنی در این روش نمایش ضریب تعیین در منطقه ساخت و ساز را فعال می کنیم. سپس فراموش نکنید که روی دکمه کلیک کنید بستن در پایین پنجره فعلی
- مقدار قابلیت اطمینان تقریبی ، یعنی مقدار ضریب تعیین ، در یک صفحه در منطقه ساخت و ساز نمایش داده می شود. در این حالت ، این مقدار ، همانطور که می بینیم ، 0.9242 است ، که تقریب را به عنوان الگویی از کیفیت خوب توصیف می کند.
- کاملاً دقیقاً از این طریق می توانید نمایشگر ضریب تعیین برای هر نوع خط روند دیگر را تنظیم کنید. شما می توانید همانطور که در شکل بالا مشاهده می کنید ، نوع خط روند را با استفاده از دکمه روی روبان یا منوی زمینه در پنجره پارامترهای آن تغییر دهید. سپس در خود پنجره در گروه "ساختن یک خط روند" می توانید به نوع دیگری تغییر دهید. در عین حال ، کنترل این نکته را در اطراف نقطه فراموش نکنید "مقدار اطمینان تقریبی را در نمودار قرار دهید" کادر انتخاب شد. پس از انجام مراحل فوق ، بر روی دکمه کلیک کنید بستن در گوشه سمت راست پایین پنجره.
- با نوع خطی ، خط روند در حال حاضر دارای ارزش اطمینان تقریبی برابر 0.9477 است ، که این مدل را حتی به عنوان مطمئن تر از خط روند از نوع نمایی که قبلاً توسط ما درنظر گرفته شده بود ، توصیف می کند.
- بنابراین ، با جابجایی بین انواع مختلف خطوط روند و مقایسه مقادیر اطمینان تقریبی آنها (ضریب تعیین) ، می توانیم گزینه ای را پیدا کنیم که مدل آن با دقت بیشتری نمودار ارائه شده را توصیف کند. گزینه ای با بالاترین ضریب ضریب تعیین ، قابل اطمینان ترین خواهد بود. بر اساس آن ، شما می توانید دقیق ترین پیش بینی را ایجاد کنید.
به عنوان مثال ، برای مورد ما به طور آزمایشی امکان پذیر بود که نوع چند جمله ای خط روند درجه دوم بالاترین سطح اطمینان را داشته باشد. ضریب تعیین در این مورد 1 است. این نشان می دهد که این مدل کاملاً قابل اعتماد است و این به معنای حذف کامل خطاها است.
اما ، در عین حال ، این به هیچ وجه به معنای این نیست که برای نمودار دیگری ، این نوع خط روند نیز قابل اطمینان ترین است. انتخاب بهینه نوع خط روند بستگی به نوع عملکردی دارد که براساس آن نمودار ساخته شده است. اگر کاربر دانش کافی برای تخمین بهترین نوع کیفیت از طریق چشم نداشته باشد ، تنها راه تعیین بهترین پیش بینی مقایسه ضرایب تعیین است ، همانطور که در مثال بالا نشان داده شده است.
همچنین بخوانید:
ساختن یک خط روند در اکسل
تقریب در اکسل
برای محاسبه ضریب تعیین در اکسل دو گزینه اصلی وجود دارد: استفاده از عملگر KVPIRSON و استفاده از ابزار "رگرسیون" از جعبه ابزار "تجزیه و تحلیل داده ها". علاوه بر این ، اولین گزینه از این گزینه ها فقط برای پردازش یک تابع خطی در نظر گرفته شده است و گزینه دیگر تقریباً در همه شرایط قابل استفاده است. علاوه بر این ، می توان ضریب تعیین برای خط روند نمودارها را به عنوان یک مقدار قابلیت اطمینان از تقریبی نمایش داد. با استفاده از این شاخص می توان نوع خط روند را تعیین کرد که بالاترین سطح اطمینان را برای یک عملکرد خاص داشته باشد.
