КОЕФИЦИЕНТ НА ОПРЕДЕЛУВАЊЕ ВО EXCEL

Send

Еден од индикаторите што го опишува квалитетот на конструираниот модел во статистиката е коефициентот на определување (Р ^ 2), кој исто така се нарекува вредност на приближување на довербата. Со него, можете да го одредите нивото на точноста на прогнозата. Ајде да откриеме како можете да го пресметате овој индикатор користејќи различни алатки на Excel.

Пресметка на коефициентот на определување

Во зависност од нивото на коефициентот на определување, вообичаено е моделите да се поделат во три групи:

0,8 - 1 - модел со добар квалитет;
0,5 - 0,8 - модел со прифатлив квалитет;
0 - 0,5 - модел со слаб квалитет.

Во вториот случај, квалитетот на моделот укажува на невозможноста на неговата употреба за предвидување.

Изборот за тоа како да се пресмета одредената вредност во Excel зависи од тоа дали регресијата е линеарна или не. Во првиот случај, можете да ја користите функцијата КВПИРСОН, а во втората треба да користите специјална алатка од пакетот за анализа.

Метод 1: пресметување на коефициентот на определување со линеарна функција

Како прво, ќе откриеме како да го најдеме коефициентот на определување за линеарна функција. Во овој случај, овој индикатор ќе биде еднаков на квадратот на коефициентот на корелација. Ние ќе ја пресметаме со помош на вградената функција Excel на пример на специфична табела, што е дадена подолу.

Изберете ја ќелијата каде што ќе се прикаже коефициентот на определување по неговото пресметување и кликнете на иконата "Вметни функција".

Започнува Волшебник на карактеристики. Преселба во неговата категорија „Статистика“ и означете го името КВПИРСОН. Следниот кликнете на копчето „ОК“.

Започнува прозорецот за аргументи на функцијата. КВПИРСОН. Овој оператор од статистичката група е дизајниран да го пресмета квадратот на коефициентот на корелација на функцијата Пирсон, односно линеарна функција. И како што се сеќаваме, со линеарна функција, коефициентот на определување е точно еднаков на квадратот на коефициентот на корелација.
Синтаксата за оваа изјава е:
= KVPIRSON (познати_и вредности_; познати_х вредности)
Така, една функција има два оператори, од кои едниот е список на вредности на функцијата, а вториот е аргумент. Операторите можат да бидат претставени директно како вредности наброени преку записник (;), и во форма на врски до опсегот каде што се наоѓаат. Тоа е последната опција што ќе ја користиме од нас во овој пример.
Поставете го покажувачот на полето Познати y вредности. Го држиме левото копче на глувчето и ја избираме содржината на колоната „Ј“ табели. Како што можете да видите, адресата на одредената низа на податоци веднаш се прикажува во прозорецот.
На ист начин, пополнете го полето Познати x вредности. Ставете го покажувачот во ова поле, но овој пат изберете ги вредностите на колоната „Х“.
Откако ќе се прикажат сите податоци, во прозорецот со аргументи КВПИРСОНкликнете на копчето „ОК“лоциран на самото дно.

Како што можете да видите, после ова програмата пресметува коефициент на определување и го прикажува резултатот во ќелијата што е избрана уште пред повикот Волшебници за функции. Во нашиот пример, вредноста на пресметаниот индикатор се покажа како 1. Ова значи дека презентираниот модел е апсолутно сигурен, односно ја елиминира грешката.

Лекција: Волшебник за одлики во Microsoft Excel

Метод 2: пресметување на коефициентот на определување во нелинеарни функции

Но, горенаведената опција за пресметување на посакуваната вредност може да се примени само за линеарни функции. Што да направите за да се пресмета во нелинеарна функција? Во Excel постои таква можност. Може да се направи со алатката. „Регресија“што е дел од пакетот "Анализа на податоци".

Но, пред да ја користите наведената алатка, мора сами да ја активирате Пакет за анализа, што е стандардно оневозможено во Excel. Преместете на јазичето Датотекаа потоа оди до "Опции".

Во прозорецот што се отвора, преминете на делот "Додатоци" со навигација во левото вертикално мени. На дното на десната страна на прозорецот е поле "Управување". Од списокот на подгрупи што се достапни таму, изберете го името "Додатоци на Excel ..."и потоа кликнете на копчето "Оди ..."лоциран десно од полето.

Прозорецот за додатоци е лансиран. Во неговиот централен дел е список на достапни додатоци. Поставете го полето за избор веднаш до позицијата Пакет за анализа. Следејќи го ова, кликнете на копчето „ОК“ на десната страна од прозорецот интерфејс.

Пакет со алатки "Анализа на податоци" во тековниот пример на Excel ќе се активира. Пристапот до него се наоѓа на лентата во јазичето „Податоци“. Ние се префрламе на одредено јазиче и кликнуваме на копчето "Анализа на податоци" во групата за поставки „Анализа“.

Прозорецот е активиран "Анализа на податоци" со список на специјализирани алатки за обработка на информации. Изберете ставка од оваа листа „Регресија“ и кликнете на копчето „ОК“.

Потоа се отвора прозорецот со алатки „Регресија“. Првиот блок на поставки е "Влез". Тука во две полиња треба да ги наведете адресите на опсегите каде што се наоѓаат вредностите на аргументот и функцијата. Ставете го курсорот на полето "Влезен интервал Y" и изберете ја содржината на колоната на листот „Ј“. Откако адресата на низата ќе се прикаже во прозорецот „Регресија“ставете го курсорот на полето "Влезен интервал Y" и одберете ги ќелиите на колоната точно на ист начин „Х“.
За параметрите „Етикета“ и Постојан нула не ставајте знамиња. Полето може да се постави веднаш до параметарот. "Ниво на сигурност" и во спротивното поле, означете ја посакуваната вредност на соодветниот индикатор (95% по дифолт).
Во групата Избори за излез треба да одредите во која област ќе се прикаже резултатот од пресметката. Постојат три опции:
- Областа на тековниот лист;
- Друг лист;
- Друга книга (нова датотека).
Дозволете ни да ја избереме првата опција така што изворните податоци и резултатот да бидат поставени на истиот работен лист. Прекинувачот го ставаме близу до параметарот "Интервал на излез". Во полето спротивно од оваа ставка, ставете го курсорот. Лево кликнете на празен елемент на листот, кој е дизајниран да стане горната лева ќелија на табелата за излез од пресметката. Адресата на овој елемент треба да биде прикажана во полето за прозорец „Регресија“.
Параметарски групи „Остатоци“ и "Нормална веројатност" Игнорирајте, бидејќи тие не се важни за решавање на задачата. После тоа, кликнете на копчето „ОК“лоциран во горниот десен агол на прозорецот „Регресија“.

Програмата се пресметува врз основа на претходно внесените податоци и го прикажува резултатот во наведениот опсег. Како што можете да видите, оваа алатка прикажува прилично голем број на резултати на различни параметри на лист. Но, во контекст на тековната лекција, ние сме заинтересирани за индикаторот Р-плоштад. Во овој случај, тоа е еднакво на 0.947664, што го карактеризира избраниот модел како модел со добар квалитет.

Метод 3: коефициент на определување за тренд линија

Покрај горенаведените опции, коефициентот на определување може да се прикаже директно за трендовата линија во графикон изграден на работниот лист на Excel. Willе дознаеме како тоа може да се направи со конкретен пример.

Имаме графикон заснован на табела со аргументи и вредности на функција што се користеше за претходниот пример. Buildе изградиме тренд линија до тоа. Кликнуваме на кое било место од областа за градба на која е поставена табелата, со левото копче на глувчето. Во исто време, на лентата се појавува дополнителен пакет на јазичиња - "Работа со графикони". Одете на јазичето "Распоред". Кликнете на копчето Тренд линијашто се наоѓа во блокот со алатки „Анализа“. Се појавува мени со избор на видот на линијата на трендови. Ние го запреме изборот на типот што одговара на одредена задача. Ајде да избереме опција за нашиот пример "Експоненцијално приближување".

Excel гради тренд линија во форма на дополнителна црна крива веднаш на табелата.

Сега нашата задача е да го прикажеме самиот коефициент на определување. Кликнете со десното копче на линијата за трендови. Контекстното мени е активирано. Ние ја запираме селекцијата во него на "Форматот на трендовата линија ...".
За да ја извршите транзицијата кон прозорецот за формат на тренд линија, можете да извршите алтернативно дејство. Изберете ја линијата на трендови со кликнување на неа со левото копче на глувчето. Преместете на јазичето "Распоред". Кликнете на копчето Тренд линија во блок „Анализа“. Во списокот што се отвора, кликнете на последната ставка во списокот со активности - "Дополнителни параметри на трендовите ...".

После една од горенаведените две дејства, се стартува прозорец со формат во кој можете да направите дополнителни поставки. Особено, за да ја завршиме нашата задача, неопходно е да го проверите полето до "Ставете ја вредноста на довербата за приближување (R ^ 2) на дијаграмот". Се наоѓа на самиот крај на прозорецот. Тоа е, на овој начин овозможуваме прикажување на коефициентот на определување на градежната површина. Потоа, не заборавајте да кликнете на копчето Затвори на дното на тековниот прозорец.

Вредноста на веродостојноста на приближувањето, односно вредноста на коефициентот на определување, ќе биде прикажана на лист во областа на градежништвото. Во овој случај, оваа вредност, како што гледаме, е 0,9242, што ја карактеризира приближувањето како модел со добар квалитет.

Апсолутно точно на овој начин можете да поставите приказ на коефициентот на определување за кој било друг вид тренд линија. Можете да го промените типот на линијата за трендови со правење транзиција преку копчето на лентата или контекстното мени во прозорецот на неговите параметри, како што е прикажано погоре. Потоа во самиот прозорец во групата "Градење на тренд линија" Може да се префрлите на друг вид. Во исто време, не заборавајте да го контролирате тоа околу точката "Ставете ја вредноста на довербата за приближување на дијаграмот" се проверуваше полето за избор. По завршувањето на горенаведените чекори, кликнете на копчето Затвори во долниот десен агол на прозорецот.

Со линеарниот тип, тренд линијата веќе има доверба вредност за приближување еднаква на 0.9477, што го карактеризира овој модел како уште посигурен од трендовата линија од експоненцијалниот тип што се сметаше порано од нас.

Така, префрлајќи се меѓу различни типови трендови, и споредувајќи ги нивните вредности за приближување на довербата (коефициент на определување), можеме да ја најдеме опцијата чиј модел најпрецизно го опишува презентираниот графикон. Опцијата со највисок коефициент на коефициент на определување ќе биде најсигурна. Врз основа на тоа, може да се изгради најточна прогноза.
На пример, за нашиот случај експериментално беше можно да се утврди дека полиномскиот вид тренд линија од втор степен има највисоко ниво на доверба. Коефициент на определување во овој случај е 1. Ова укажува дека посочениот модел е апсолутно сигурен, што значи целосно исклучување на грешките.
Но, во исто време, тоа воопшто не значи дека за друга табела овој вид линија на трендови исто така ќе биде најсигурен. Оптималниот избор на видот на тренд-линијата зависи од видот на функцијата врз основа на која е изградена табелата. Ако корисникот нема доволно знаење за да ја процени опцијата за најдобар квалитет, тогаш единствениот начин да се утврди најдобрата прогноза е да ги споредите коефициентите на определување, како што е прикажано во примерот погоре.

Прочитајте исто така:
Изградба на тренд линија во Excel
Приближување во Excel

Постојат две главни опции за пресметување на коефициентот на определување во Excel: користење на операторот КВПИРСОН и употреба на алатки „Регресија“ од алатникот "Анализа на податоци". Покрај тоа, првата од овие опции е наменета за употреба само при обработка на линеарна функција, а другата опција може да се користи во скоро сите ситуации. Покрај тоа, можно е да се прикаже коефициентот на определување за трендната линија на графиконите како вредност на веродостојноста на приближувањето. Користејќи го овој индикатор, можно е да се одреди типот на тренд-линијата што има најголемо ниво на доверба за одредена функција.

Send