Еден од индикаторите што го опишуваат квалитетот на конструираниот модел во статистиката е коефициентот на определување (R ^ 2), кој исто така се нарекува вредност на доверливост на приближувањето. Со него, можете да го одредите нивото на точноста на прогнозата. Ајде да дознаеме како можете да го пресметате овој индикатор користејќи различни алатки на Excel.
Пресметка на коефициентот на определување
Во зависност од нивото на коефициентот на определување, вообичаено е да се поделат моделите во три групи:
- 0,8 - 1 - модел со добар квалитет;
- 0,5 - 0,8 - модел на прифатлив квалитет;
- 0 - 0,5 - модел на лош квалитет.
Во вториот случај, квалитетот на моделот укажува на неможноста на неговата употреба за прогнозата.
Изборот за тоа како да се пресмета одредената вредност во Excel зависи од тоа дали регресијата е линеарна или не. Во првиот случај, можете да ја користите функцијата KVPIRSON, а во втората ќе треба да користите специјална алатка од пакетот за анализа.
Метод 1: пресметка на коефициентот на определување со линеарна функција
Прво на сите, да дознаете како да го најдете коефициентот на определување за линеарна функција. Во овој случај, овој индикатор ќе биде еднаков на квадратот на коефициентот на корелација. Ние ќе го пресметаме со помош на вградената Excel функција користејќи го примерот на одредена табела, која е прикажана подолу.
- Изберете ја ќелијата каде што ќе биде прикажан коефициентот на определување по неговата пресметка и кликнете на иконата "Вметни ја функцијата".
- Почнува Функционален волшебник. Премести во својата категорија "Статистички" и означете го името KVPIRSON. Следно, кликнете на копчето "Добро".
- Се започнува прозорецот за функции аргументи. KVPIRSON. Овој оператор од статистичката група е дизајниран да го пресмета квадратот на коефициентот на корелација на функцијата Пирсон, односно линеарна функција. И како што се сеќаваме, со линеарна функција, коефициентот на определување е само еднаков на квадратот на коефициентот на корелација.
Синтаксата за оваа изјава е:
= KVPIRSON (познат_, добро познат_x)Така, функцијата има два оператора, од кои едната е листа на вредности на функцијата, а втората е аргумент. Операторите можат да бидат претставени како директно како вредности наведени преку точка-запирка (;), и во форма на линкови до опсезите каде што се наоѓаат. Ова е последната опција што ќе ја користиме во овој пример.
Поставете го курсорот во полето "Познати вредности на Y". Ние извршуваме стегање на левото копче на глувчето и изберете ја содржината на колоната. "Y" маси. Како што можете да видите, адресата на наведената низа на податоци веднаш се прикажува во прозорецот.
Слично пополнете го полето "Познати x". Ставете го курсорот во ова поле, но овој пат одберете вредности на колоната "X".
Откако сите податоци се прикажани во прозорецот со аргументи KVPIRSONкликнете на копчето "Добро"лоциран на самото дно.
- Како што можете да видите, после ова, програмата го пресметува коефициентот на определување и го враќа резултатот во ќелијата која е избрана пред повикот Функција мајстори. Во нашиот пример, вредноста на пресметаниот индикатор се покажа дека е 1. Ова значи дека презентираниот модел е апсолутно сигурен, односно ја елиминира грешката.
Лекција: Волшебник за функции во Microsoft Excel
Метод 2: Пресметка на коефициентот на определување во нелинеарни функции
Но, горната опција за пресметување на саканата вредност може да се примени само за линеарни функции. Што да направите за да ја направите својата пресметка во нелинеарна функција? Во Excel постои таква можност. Тоа може да се направи со алатка. "Регресија"која е дел од пакетот "Анализа на податоци".
- Но, пред да ја користите оваа алатка, треба да го активирате сами. "Анализа пакет"што стандардно е оневозможено во Excel. Премести во табот "Датотека"а потоа оди преку предметот "Опции".
- Во отворен прозорец се преселуваме во делот. Додатоци со навигација низ левото вертикално мени. На дното на десниот панел е поле "Управување". Од листата на достапни под-секции, изберете го името "Додадени додатоци на Excel ..."и потоа кликнете на копчето "Оди ..."наоѓа десно од полето.
- Почнува прозорецот за додатоци. Во централниот дел е листа на достапни додатоци. Проверете го полето до позицијата "Анализа пакет". По ова, кликнете на копчето. "Добро" на десната страна од интерфејсот прозорец.
- Пакет со алатки "Анализа на податоци" во тековниот пример на Excel ќе се активира. Пристапот до него се наоѓа на лентата во јазичето "Податоци". Движете се до наведеното јазиче и кликнете на копчето. "Анализа на податоци" во групата на поставки "Анализа".
- Активиран прозорец "Анализа на податоци" со листа на специјализирани алатки за обработка на информации. Изберете од оваа ставка од листата "Регресија" и кликнете на копчето "Добро".
- Потоа се отвора прозорецот на алатката. "Регресија". Првиот блок на поставувања - "Влез". Тука во двете полиња треба да ги наведете адресите на опсегот каде се наоѓаат вредностите и функциите на аргументот. Ставете го курсорот во полето "Влезен интервал Y" и изберете ја содржината на колоната на листот "Y". По адресата на низата се прикажува во прозорецот "Регресија"стави курсорот во полето "Влезен интервал Y" и на ист начин избирајте ги клетките на колоната "X".
За параметри "Таг" и "Постојана нула" полињата за избор не се поставени. Полето за избор може да се постави во близина на параметарот "Ниво на доверливост" и во полето спротивно, наведете ја саканата вредност на соодветниот индикатор (стандардно 95%).
Во групата "Излезни опции" треба да одредите во која област ќе се прикаже резултатот од пресметката. Постојат три опции:
- Површина на тековниот лист;
- Уште еден лист;
- Друга книга (нова датотека).
Ајде да го запреме изборот на првата опција дека почетните податоци и резултати беа поставени на еден работен лист. Ставете го прекинувачот во близина на параметарот "Излезна растојание". Во полето спротивно од оваа ставка ставете го курсорот. Ние кликнете на левото копче на глувчето на празниот елемент на листот, кој е наменет да стане лева горната ќелија на табелата на резултатите од пресметката. Адресата на овој елемент треба да биде прикажана во прозорецот "Регресија".
Групи на параметри "Останува" и "Нормална веројатност" игнорира, бидејќи тие не се важни за решавање на проблемот. Потоа кликниме на копчето. "Добро"кој се наоѓа во горниот десен агол на прозорецот "Регресија".
- Програмата пресметува врз основа на претходно внесените податоци и го прикажува резултатот во наведениот опсег. Како што можете да видите, оваа алатка покажува на листот прилично голем број на резултати за различни параметри. Но, во контекст на сегашната лекција, ние сме заинтересирани за индикаторот "Р-квадрат". Во овој случај, истиот е еднаков на 0,947664, што го карактеризира избраниот модел како модел со добар квалитет.
Метод 3: коефициент на определување за линијата на трендови
Покрај горенаведените опции, коефициентот на определување може да се прикаже директно за линијата на трендови во графикон изграден на лист на Excel. Ние ќе дознаеме како ова може да се направи со конкретен пример.
- Имаме графикон врз основа на табелата на аргументи и вредности на функцијата што беше користена за претходниот пример. Ајде да направиме тренд линија за тоа. Ние кликнуваме на кое било место во областа на градење на која се наоѓа графикот со левото копче на глувчето. Во исто време, на лентата се појавува дополнителен сет на јазичиња - "Работа со графикони". Одете на јазичето "Распоред". Ние кликнуваме на копчето "Тренд линија"кој се наоѓа во блок на алатки "Анализа". Се појавува мени со избор на тип на тренд линија. Прекинуваме избор на тип кој одговара на одредена задача. За нашиот пример, ајде да избереме "Експоненцијално приближување".
- Excel гради линија на трендови во форма на дополнителна црна крива десно на рамнината на трасирање.
- Сега нашата задача е да се прикаже самиот коефициент на определување. Десен-клик на линијата на трендови. Контекстното мени е активирано. Запрете го изборот во него на ставката "Тренд линија формат ...".
За да направите транзиција кон прозорецот за форматот на тренд линија, можете да направите алтернативно дејство. Изберете ја линијата на трендови со кликнување на неа со левото копче на глувчето. Премести во табот "Распоред". Ние кликнуваме на копчето "Тренд линија" во блок "Анализа". Во листата што се отвора, ќе кликнеме на последната ставка во списокот на акции - "Опции за напредни тренд линии ...".
- По било кое од горенаведените две акции, се промовира прозорец за формат, во кој можете да направите дополнителни поставки. Особено, за да ја извршиме нашата задача, неопходно е да се провери полето до "Ставете го графикот вредноста на точноста на приближувањето (R ^ 2)". Се наоѓа на самото дно на прозорецот. Тоа е, на овој начин го вклучуваме приказот на коефициентот на определување на градежната област. Потоа не заборавајте да го притиснете копчето "Затвори" на дното на тековниот прозорец.
- Вредноста на доверливоста на приближувањето, односно вредноста на коефициентот на определување, ќе биде прикажана на листот во површината на парцелата. Во овој случај, оваа вредност, како што гледаме, е еднаква на 0,9242, што ја карактеризира приближувањето, како модел со добар квалитет.
- Апсолутно точно така можете да го поставите приказот на коефициентот на определување за било кој друг тип на тренд линија. Можете да го смените типот на линијата на трендови со правење транзиција низ копчето на лентата или контекстното мени во прозорецот за неговите параметри, како што е прикажано погоре. Потоа веќе во прозорецот во групата "Градење на тренд линија" може да се префрли на друг тип. Не заборавајте да контролирате така што во близина на точка "Ставете ја на табелата вредноста на точноста на приближувањето" беше проверена. По завршувањето на горните чекори, кликнете на копчето. "Затвори" во долниот десен агол на прозорецот.
- Во случај на линеарен тип, линијата на трендови веќе има вредност на доверливост од приближно 0.9477, што го карактеризира овој модел како уште посигурен од експоненцијалниот тип тренд линија што ја разгледувавме порано.
- Така, преминувањето помеѓу различни типови на тренд линии и споредување на нивните вредности на доверливост на приближување (коефициент на определување), можете да ја најдете варијантата, чиј модел најмногу го опишува претставениот графикон. Варијантата со највисок индекс на определување ќе биде најсигурен. Врз основа на тоа, можете да изградите најточна прогноза.
На пример, за нашиот случај, со експеримент, успеавме да утврдиме дека највисокото ниво на доверба е од полиномниот тип на линијата на трендови на вториот степен. Коефициентот на определување во овој случај е еднаков на 1. Ова сугерира дека овој модел е апсолутно сигурен, што значи целосна елиминација на грешки.
Но, во исто време, ова воопшто не значи дека овој тип на тренд линија исто така ќе биде најсигурен за друга табела. Оптималниот избор на типот на тренд линија зависи од видот на функцијата врз основа на која е изграден графикот. Ако корисникот нема доволно познавања за да ја процени опцијата за највисок квалитет, тогаш единствениот начин да се одреди најдоброто предвидување е само споредба на коефициентите на определување, како што беше покажано во горниот пример.
Видете исто така:
Градење тренд линии во Excel
Екселска апроксимација
Во Excel постојат две главни опции за пресметување на коефициентот на определување: користење на операторот KVPIRSON и алатка за апликација "Регресија" од пакетот алатки "Анализа на податоци". Во овој случај, првата од овие опции е наменета само за обработка на линеарна функција, а друга опција може да се користи во скоро сите ситуации. Покрај тоа, можно е да се прикаже коефициентот на определување за линијата на трендови на графиконите како вредност на доверливост на приближувањето. Користејќи го овој индикатор, можно е да се одреди типот на тренд линија која има најголемо ниво на доверба за одредена функција.
