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

یکی از شاخص هایی که کیفیت مدل ساخته شده در آمار را نشان می دهد ضریب تعیین (R ^ 2) است که همچنین ارزش اطمینان تقریبی نامیده می شود. با این کار می توانید سطح دقت پیش بینی را تعیین کنید. بیایید یاد بگیریم که چگونه این شاخص را با استفاده از ابزارهای مختلف اکسل محاسبه کنیم.

محاسبه ضریب تعیین

بسته به سطح ضریب تعیین، مدل تقسیم به سه گروه تقسیم می شود:

• 0.8 - 1 - مدل با کیفیت خوب؛
• 0.5 - 0.8 - یک مدل کیفیت قابل قبول؛
• 0 - 0.5 - مدل کیفیت پایین.

در مورد دوم، کیفیت مدل نشان دهنده عدم امکان استفاده از آن برای پیش بینی است.

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

روش 1: محاسبه ضریب تعیین با یک تابع خطی

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

1. سلول را انتخاب کنید که در آن ضریب تعیین بعد از محاسبه نمایش داده می شود و بر روی نماد "Insert Function" کلیک کنید.
2. جادوگر تابع شروع می شود. ما به رده "آماری " برویم و نام "KVPIRSON" را علامت بزنیم . بعد، بر روی دکمه "OK" کلیک کنید.
3. پنجره ای از کارکردهای KVPIRSON شروع می شود . این اپراتور از گروه آماری برای محاسبه مربع ضریب همبستگی تابع پیرسون، یعنی یک تابع خطی طراحی شده است. و همانطور که به یاد می آوریم، با یک تابع خطی، ضریب تعیین فقط برابر با مربع ضریب همبستگی است.

   نحو این عبارت:

   =КВПИРСОН(известные_значения_y;известные_значения_x)

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

   مکان نما را در فیلد "شناخته شده Y" تنظیم کنید. ما کلید چپ ماوس را انجام می دهیم و محتویات ستون "Y" را از جدول انتخاب می کنیم. همانطور که می بینید، آدرس آرایه داده مشخص شده بلافاصله در پنجره نمایش داده می شود.

   به طور مشابه، "مقادیر شناخته شده x" را پر کنید . مکان نما را در این زمینه قرار دهید، اما این بار مقادیر ستون "X" را انتخاب کنید .

   پس از اینکه تمام داده ها در پنجره استدلال KVPIRSON نمایش داده شوند ، ما بر روی دکمه "OK" که در پایین آن قرار دارد کلیک می کنیم.

4. همانطور که می بینید، بعد از این برنامه، ضریب تعیین را محاسبه می کند و نتیجه را به سلول منتقل می کند که قبل از فراخوانی به کاراکتر استاد انتخاب شده است . در مثال ما مقدار نشانگر محاسبه شده به 1 تبدیل شده است. این به این معنی است که مدل ارائه شده کاملا قابل اعتماد است، یعنی این خطا را از بین می برد.

درس: جادوگر تابع مایکروسافت اکسل

روش 2: محاسبه ضریب تعیین در توابع غیرخطی

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

1. اما قبل از استفاده از این ابزار، شما باید بسته تجزیه و تحلیل خود را فعال کنید، که به طور پیش فرض در اکسل غیر فعال شده است. به تب "File" بروید و سپس به پارامتر "Parameters" بروید .
2. در پنجره ای که باز می شود، با مرور از طریق منوی عمودی سمت چپ، به بخش "افزودنی ها" حرکت کنید. در قسمت پایین پنجره سمت راست پنجره "کنترل" است. از لیست بخش های موجود در آن، نام "Excel Add-Ins ..." را انتخاب می کنیم و سپس بر روی دکمه "Go ..." واقع در سمت راست فیلد کلیک کنید.
3. پنجره افزودنیها راه اندازی شده است. در قسمت مرکزی فهرستی از افزودنیهای موجود موجود است. کادر کنار کادر "تجزیه و تحلیل بسته" را علامت بزنید. به دنبال این، بر روی دکمه "OK" در سمت راست پنجره رابط کاربری کلیک کنید.
4. جعبه ابزار تجزیه و تحلیل اطلاعات در نمونه فعلی اکسل فعال خواهد شد. دسترسی به آن بر روی روبان در برگه "Data" قرار دارد . به برگه مشخص شده حرکت کنید و روی «تجزیه و تحلیل داده ها» در گروه تنظیمات «تجزیه و تحلیل» کلیک کنید.
5. پنجره داده تجزیه و تحلیل با یک لیست از ابزارهای پردازش اطلاعات تخصصی فعال می شود. گزینه "Regression" را از این لیست انتخاب کنید و روی دکمه "OK" کلیک کنید.
6. سپس پنجره ابزار رگرسیون باز می شود. اولین بلوک تنظیمات - "ورودی" . در اینجا در دو فیلد باید آدرسهایی از محدوده هایی را که در آن مقادیر و توابع آرگومان قرار دارند تعیین کنید. مکان نما را در قسمت "ورودی Y" وارد کنید و محتویات ستون "Y" را در برگه انتخاب کنید. پس از آدرس آرایه در پنجره Regression نمایش داده می شود، مکان نما را در قسمت "ورودی Y فاصله" قرار دهید و سلول های ستون "X" را به همان شیوه انتخاب کنید.

   ما جعبه ها "علامت" و "ثابت-صفر" را بررسی نمی کنیم . کادر انتخاب را می توان در نزدیکی پارامتر "سطح اطمینان" تنظیم کرد و در قسمت مقابل شما می توانید مقدار دلخواه نشانگر مربوطه (به طور پیش فرض 95٪) را نشان می دهد.

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

   • منطقه در ورق کنونی؛
   • ورق دیگری؛
   • کتاب دیگری (فایل جدید).

   گزینه اول را انتخاب کنید که داده های اولیه و نتیجه در یک صفحه کار گذاشته شود. ما سوئیچ را در کنار پارامتر "فاصله خروجی" قرار می دهیم . در قسمت مقابل این آیتم، مکان نما را قرار دهید. ما بر روی دکمه سمت چپ ماوس بر روی عنصر خالی روی صفحه کلیک می کنیم، که در نظر گرفته شده برای تبدیل شدن به سلول بالایی چپ جدول خروجی محاسبه شده است. آدرس این عنصر باید در کادر «رگرسیون» برجسته شود.

   گروه های پارامترهای "باقی مانده" و "احتمال عادی" نادیده گرفته می شوند، زیرا برای حل وظیفه مجموعه مهم نیستند. پس از آن ما دکمه "OK" را که در گوشه بالا سمت راست پنجره "Regression" قرار دارد کلیک می کنیم.

7. برنامه محاسبه می شود بر اساس داده های قبلا وارد شده و نتیجه در محدوده مشخص شده نمایش داده می شود. همانطور که می بینید، این ابزار بر روی صفحه نمایش تعداد زیادی از نتایج در پارامترهای مختلف نمایش می دهد. اما در زمینه درس فعلی، ما به شاخص R-square علاقمندیم. در این مورد، آن برابر با 0.947664 است که مدل انتخاب شده را به عنوان یک مدل از کیفیت خوب مشخص می کند.

روش 3: ضریب تعیین خط روند

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

1. ما یک نمودار بر اساس جدول استدلال ها و مقادیر تابع که برای مثال قبلی استفاده می شود داریم. بیایید خط روند را به آن اضافه کنیم. با کلیک بر روی هر مکان در منطقه ساخت و ساز که در آن نمودار با دکمه سمت چپ ماوس قرار گرفته است. در این مورد، مجموعه ای از زبانه های اضافی روی روبان ظاهر می شود - "کار با نمودار" . به برگه "طرح بندی" بروید. ما بر روی دکمه "خط روند" که در جعبه ابزار "تجزیه و تحلیل" قرار دارد کلیک می کنیم . یک منو با انتخاب نوع خط روند نمایش داده می شود. ما انتخاب را بر اساس نوعی که مربوط به یک کار خاص است متوقف می کنیم. برای مثال ما، گزینه "Exponential approximation" را انتخاب کنید.
2. اکسل یک خط روند را به صورت یک منحنی سیاه و سفید اضافی در سمت چپ راست قرار می دهد.
3. در حال حاضر وظیفه ما این است که ضریب تعیین خود را به نمایش بگذاریم. ما راست کلیک بر روی خط روند. منوی زمینه فعال شده است. ما انتخاب در آن را در مورد "فرمت خط روند ..." متوقف می کنیم.

   برای انتقال به پنجره فرمت خط روند، شما می توانید اقدام دیگری را انجام دهید. خط روند را با کلیک روی آن با دکمه سمت چپ ماوس انتخاب کنید. به برگه «طرح بندی» بروید. ما بر روی دکمه "Trend Line" در بلوک "Analysis" کلیک می کنیم. در فهرستی که باز می شود، روی آخرین مورد در لیست اقدامات کلیک کنید - «پارامترهای اضافی خط روند ...» .

4. پس از هر یک از دو عمل فوق، یک پنجره قالب نمایش داده می شود که در آن می توانید تنظیمات بیشتری را ایجاد کنید. به طور خاص، برای انجام وظیفه ما، لازم است که جعبه مقابل علامت "مقدار اطمینان تقریبی (R ^ 2)" را در نمودار قرار دهید . این در پایین پنجره قرار دارد. یعنی، به این ترتیب ما نمایش ضریب تعیین در منطقه ساخت و ساز را روشن می کنیم. سپس فراموش نکنید که روی دکمه «بستن» در پایین پنجره فعلی کلیک کنید.
5. مقدار اطمینان تقریبی، یعنی مقدار ضریب تعیین، بر روی صفحه در منطقه نشان داده می شود. در این مورد، این مقدار، همانطور که می بینیم، برابر با 0.9242 است، که تقریبی را به عنوان یک مدل از کیفیت خوب توصیف می کند.
6. به طور کامل دقیقا این روش شما می توانید نمایش ضریب تعیین برای هر نوع خط روند دیگر را تنظیم کنید. شما می توانید نوع خط روند را با تغییر یک دکمه روی نوار یا یک منوی زمینه در پنجره پارامتر آن تغییر دهید، همانطور که در بالا نشان داده شده است. سپس، در حال حاضر در پنجره خود را در گروه "ایجاد یک روند خط"، شما می توانید به نوع دیگری تغییر دهید. در عین حال، ما فراموش نکنیم که کنترل جعبه را در نزدیکی مورد بررسی قرار دهیم "مقدار اطمینان تقریبی در نمودار" . پس از اتمام مراحل بالا، روی دکمه «بستن» در گوشه پایین سمت راست پنجره کلیک کنید.
7. در مورد نوع خطی، خط روند در حال حاضر دارای ارزش اطمینان تقریبی 0.9477 است، که این مدل را به عنوان قابل اعتماد تر از خط روند نوع نمایشی که قبلا آن را مورد بررسی قرار دادیم، نشان می دهد.
8. بنابراین، تعویض بین انواع مختلف یک خط روند و مقایسه مقادیر آنها از دقت تقریبی (ضریب تعیین)، می توان نوعی را پیدا کرد که مدل آن دقیقا نمودار ارائه شده را توصیف می کند. نوع با بالاترین شاخص تعیین خواهد شد قابل اطمینان ترین. بر مبنای آن شما می توانید دقیق ترین پیش بینی را بسازید.

   به عنوان مثال، در مورد ما، با آزمایش، ما موفق شدیم که بالاترین سطح اطمینان از نوع چندجملهای خط روند دوم درجه باشد. ضریب تعیین در این مورد 1 است. این نشان می دهد که این مدل کاملا قابل اعتماد است، یعنی حذف کامل خطاها.

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

همچنین ببینید:
خطوط ساختمان ساختمان در اکسل
تقسیم اکسل

در اکسل، دو روش اساسی برای محاسبه ضریب تعیین وجود دارد: استفاده از اپراتور KVPIRSON و استفاده از ابزار رگرسیون از بسته ابزار Data Analysis Tool. در این مورد، اولین از این گزینه ها برای استفاده تنها در پردازش یک تابع خطی در نظر گرفته شده است، و گزینه دیگر می تواند در تقریبا در همه شرایط استفاده شود. علاوه بر این، می توان ضریب تعیین خط روند گراف را به عنوان یک مقدار اطمینان تقریبی نمایش داد. با استفاده از این شاخص، می توان نوع خط روند که دارای بالاترین سطح اطمینان برای یک عملکرد خاص است، تعیین می شود.

• ← تغییر اندازه سلول ها در مایکروسافت اکسل
• تعیین ضریب همبستگی چندگانه در MS Excel →