Израчун коефицијента одређивања у Мицрософт Екцелу

Један од индикатора који описује квалитет конструисаног модела у статистици је коефицијент одређивања (Р ^ 2), који се такође назива вредност поузданости апроксимације. Уз помоћ, можете одредити ниво тачности прогнозе. Хајде да сазнамо како израчунати овај индикатор користећи различите Екцел алате.

Израчунавање коефицијента одређивања

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

• 0,8 - 1 - модел добре квалитете;
• 0,5 - 0,8 - модел прихватљивог квалитета;
• 0 - 0,5 - модел лошег квалитета.

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

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

Метод 1: израчунавање коефицијента одређивања за линеарну функцију

Пре свега, хајде да сазнамо како пронаћи коефицијент одређивања за линеарну функцију. У овом случају, овај индикатор ће бити једнак квадрату коефицијента корелације. Ми ћемо га израчунати користећи уграђену Екцел функцију користећи пример одређене табеле, која је приказана у наставку.

1. Изаберите ћелију где ће се коефицијент одређивања изаћи након његовог обрачуна и кликните на икону "Инсерт фунцтион" .
2. Стартује функционални чаробњак . Прелазимо у категорију "Статистички" и обележимо назив "КВПИРСОН" . Затим кликните на дугме "ОК" .
3. Покренут је прозор аргумената функције КВПИРСОН . Овај оператор из статистичке групе користи се за израчунавање квадрата корелационог коефицијента Пеарсонове функције, то јест, линеарне функције. И како се сећамо, са линеарном функцијом, коефицијент одређивања је тачно једнак квадрату коефицијента корелације.

   Синтакса ове изјаве је:

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

   Дакле, функција има два оператора, од којих је једна листа вриједности функције, а друга од аргумената. Оператори могу бити представљени директно у облику вриједности наведених кроз тачку и тачку ( ; ), и као референце на опсеге гдје се налазе. То је друга опција која ће нам користити у овом примеру.

   Поставите курсор у поље "Познати и" . Прикачимо лево дугме миша и изаберите садржај колоне "И" табеле. Као што видите, адреса одређеног поља података се одмах приказује у прозору.

   Слично томе, попуњавамо поље "Познате вредности к" . Поставили смо курсор у ово поље, али овог пута бирањем вредности колоне "Кс" .

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

4. Као што видите, програм затим израчунава коефицијент одређивања и даје резултат целој која је додељена пре позивања чаробњака функције . У нашем примеру, вриједност израчунатог индикатора се испоставила као 1. Ово значи да је представљени модел апсолутно поуздан, тј. Елиминише грешку.

Лекција: Чаробњак за функције у програму Мицрософт Екцел

Метод 2: израчунавање коефицијента одређивања у нелинеарним функцијама

Али горња варијанта израчунавања жељене вриједности може се примијенити само на линеарне функције. Шта можемо да урадимо да је израчунамо у нелинеарној функцији? У Екцел-у постоји таква прилика. То се може урадити помоћу алата "Регресија" , који је саставни део пакета "Анализа података" .

1. Али пре коришћења наведеног алата, требало би да активирате "Пакет анализе" , који је по дефаулту искључен у програму Екцел. Прелазимо на картицу "Датотека" , а затим идите на "Опције" .
2. У прозору који се отвара, прелазимо на део "Додаци" навигацијом кроз леви вертикални мени. Поље "Управљање" налази се у доњем делу десног дела прозора. Из листе доступних подсекција изаберите назив "Додаци за Екцел ..." , а затим кликните на дугме "Иди ..." десно од поља.
3. Покренут је прозор додатка. У средишњем делу налази се листа доступних додатака. Означите поље поред ставке "Аналисис пацкаге" . Након тога, кликните на дугме "ОК" на десној страни интерфејса прозора.
4. Активиран је пакет алата "Анализа података" у тренутној инстанци Екцел-а. Приступ њему се налази на траци на картици "Подаци" . Прелазак на одређену картицу и клик на дугме "Анализа података" у групи за подешавања "Анализа" .
5. Прозор "Анализа података" се активира са листом алата за обраду профила профила. Из ове листе изаберемо ставку "Регресија" и кликните на дугме "ОК" .
6. Затим се отвара прозор алата Регрессион . Први блок поставки је "Инпут дата" . Овде, у две области, морате навести адресе распона где се налазе вриједности аргумента и функције. Поставили смо курсор у поље "Интервал уноса И" и изабрали садржај колоне "И" на листу. Након што се адреса матрице приказује у прозору "Регресија" , поставите показивач у поље "Инпут интервал И" и на истом начин изаберите ћелије колоне "Кс" .

   О параметрима "Лабел" и "Цонстант-зеро" , не постављамо поља за потврду. Потврдни оквир се може поставити у близини параметра "Поузданост" , а у пољу супротно можете одредити жељену вриједност одговарајућег индикатора (95% по подразумеваној вриједности).

   У групи "Излазни параметри", морате навести у којој области ће бити приказан резултат израчунавања. Постоје три опције:

   • Површина на текућем листу;
   • Још један лист;
   • Друга књига (нова датотека).

   Прво ћемо се зауставити, тако да се оригинални подаци и резултат стављају на један радни лист. Поставите прекидач око параметра "Излазни интервал" . У пољу насупрот ове ставке, постављамо курсор. Кликом на леви тастер миша кликните на празан елемент на листи, који је намењен да постане лева горња ћелија излазне табеле резултата израчунавања. Адреса овог елемента треба истакнути у пољу "Регресија" .

   Групе параметара "Ресидуес" и "Нормал Пробабилити" се занемарују, јер нису битне за решавање задатка. Након тога, кликните на дугме "ОК" , који се налази у горњем десном углу прозора "Регресија" .

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

Метод 3: Коефицијент одређивања линије трендова

Поред горе наведених опција, коефицијент одређивања може бити приказан директно за линију трендова у графикону, изграђен на Екцеловом листу. Хајде да сазнамо како се то може учинити са конкретним примјером.

1. Имамо график на основу табеле аргумената и вриједности функције која је кориштена за претходни примјер. Хајде да направимо линију трендова. Кликните на било које место у области грађења на којој се граф налази са левим тастером миша. У овом случају на траци се појављује додатни скуп табова - "Рад са дијаграмима" . Идите на картицу "Лаиоут" . Кликнемо на дугме "Тренд Лине" , која се налази у пољу за анализу "Анализа" . Појављује се мени који приказује врсту линије тренда. Прекидамо избор на тип који одговара конкретном задатку. Хајде да изаберемо опцију "Екпонентиал аппрокиматион" за наш пример.
2. Екцел гради линију трендова директно на равни плочице у облику додатне црне криве.
3. Наш задатак је да прикажемо стварни коефицијент одређивања. Десним тастером миша кликните на линију трендова. Контекстни мени је активиран. Прекидамо избор у њој на ставку "Трендлине формат ..." .

   Да бисте извршили прелазак у прозор формата линије трендова, можете извршити алтернативну акцију. Изаберите линију тренда тако што ћете га кликнути левим тастером миша. Прелазимо на картицу "Лаиоут" . Кликнути на дугме "Тренд Лине" у блоку "Анализа" . На отвореној листи кликните на најновију ставку на листи активности - "Додатни параметри линије тренда ..." .

4. После било које од претходно поменутих акција покренути је прозор формата у којем се могу извршити додатна подешавања. Конкретно, да би се постигао наш задатак, потребно је да потврдимо поље поред "Поставите вредност прецизности апроксимације (Р ^ 2) на дијаграм" . Налази се на самом дну прозора. То јест, на тај начин укључујемо мапирање коефицијента одређивања у грађевинску зону. Затим не заборавите да кликнете на дугме "Затвори" на дну тренутног прозора.
5. Вредност поузданости апроксимације, односно вредности коефицијента одређивања, биће приказана на листу на грађевинском подручју. У овом случају, ова вредност је, како видимо, 0.9242, која карактерише апроксимацију, као модел добре квалитете.
6. Апсолутно на овај начин можете поставити приказ коефицијента одређивања за било коју врсту трендовске линије. Можете променити врсту линије трендова померањем дугмета на траци или контекстуалним менијем у прозору параметара, као што је приказано горе. Затим, можете се пребацити на други тип у групи "Тренд Лине" у самом прозору. Не заборавимо да контролишемо, на примјер, да је означено поље поред ставке "Поставите вриједност прецизности апроксимације" на дијаграму . Након што завршите горе наведене кораке, кликните на дугме "Затвори" у доњем десном углу прозора.
7. Са линеарним типом линија трендова већ има вредност поузданости апроксимације једнаке 0.9477, што карактерише овај модел, чак и поузданије од линије трендова експоненцијалног типа који смо раније разматрали.
8. Стога, пребацивањем између различитих типова трендних линија и упоређивањем њихових вредности поузданости апроксимације (коефицијент одређивања), може се наћи варијанта чији модел најуређеније описује приказани графикон. Варијанта са највишим показатељем коефицијента одређивања ће бити најпоузданија. На основу тога можете направити најтачније прогнозе.

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

   Али, у исто време, уопште не значи да је ова врста линије трендова такође најпоузданија за још један графикон. Оптималан избор врсте линије тренда зависи од врсте функције на основу чега је грађен конструисан. Ако корисник нема довољно знања да би "погодио" најбољу опцију, онда је једини начин да се одреди боља предвиђања упоређивање коефицијента одређивања, као што је приказано у претходном примеру.

Прочитајте такође:
Изградња линије трендова у Екцелу
Апроксимација у Екцелу

У Екцел-у постоје две главне опције за израчунавање коефицијента одређивања: употреба оператора КВПИРСОН и употреба алата "Регресија" из пакета алата "Анализа података" . У овом случају, прва од ових опција је намењена само за процес обраде линијске функције, а друга опција може се користити у готово свим ситуацијама. Поред тога, могуће је приказати коефицијент одређивања линије тренда графова као вриједност поузданости апроксимације. Са овим индикатором могуће је одредити тип линије трендова који има највиши ниво поузданости за одређену функцију.

• ← Промена величине ћелија у програму Мицрософт Екцел
• Одређивање коефицијента вишеструке корелације у МС Екцелу →