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