Адным з паказчыкаў, якія апісваюць якасць пабудаванай мадэлі ў статыстыцы, з'яўляецца каэфіцыент дэтэрмінацыі (R ^ 2), які яшчэ называюць велічынёй дакладнасці апраксімацыі. З яго дапамогай можна вызначыць узровень дакладнасці прагнозу. Давайце даведаемся, як можна правесці разлік гэтага паказчыка з дапамогай розных інструментаў праграмы Excel.
Вылічэнне каэфіцыента дэтэрмінацыі
У залежнасці ад узроўню каэфіцыента дэтэрмінацыі, прынята падзяляць мадэлі на тры групы:
- 0,8 - 1 - мадэль добрага якасці;
- 0,5 - 0,8 - мадэль прымальнай якасці;
- 0 - 0,5 - мадэль дрэннай якасці.
У апошнім выпадку якасць мадэлі кажа аб немагчымасці яе выкарыстання для прагнозу.
Выбар спосабу вылічэнні названага значэння ў Excel залежыць ад таго, ці з'яўляецца рэгрэсія лінейнай ці не. У першым выпадку можна выкарыстоўваць функцыю КВПИРСОН, А ў другім прыйдзецца скарыстацца адмысловай прыладай з пакета аналізу.
Спосаб 1: вылічэнне каэфіцыента дэтэрмінацыі пры лінейнай функцыі
Перш за ўсё, высветлім, як знайсці каэфіцыент дэтэрмінацыі пры лінейнай функцыі. У гэтым выпадку гэты паказчык будзе раўняцца квадрату каэфіцыента карэляцыі. Вырабім яго разлік з дапамогай убудаванай функцыі Excel на прыкладзе канкрэтнай табліцы, якая прыведзена ніжэй.
- Вылучаем вочка, дзе будзе праведзены выснову каэфіцыента дэтэрмінацыі пасля яго разліку, і пстрыкаем па піктаграме "Уставіць функцыю".
- запускаецца майстар функцый. Перамяшчаемся ў яго катэгорыю "Статыстычныя" і адзначаем найменне "КВПИРСОН". Далей клацает па кнопцы "OK".
- Адбываецца запуск вокны аргументаў функцыі КВПИРСОН. Дадзены аператар з статыстычнай групы прызначаны для вылічэння квадрата каэфіцыента карэляцыі функцыі Пірсана, гэта значыць, лінейнай функцыі. А як мы памятаем, пры лінейнай функцыі каэфіцыент дэтэрмінацыі як раз роўны квадрату каэфіцыента карэляцыі.
Сінтаксіс гэтага аператара такі:
= КВПИРСОН (известные_значения_y; известные_значения_x)Такім чынам, функцыя мае два аператара, адзін з якіх уяўляе сабой пералік значэнняў функцыі, а другі - аргументаў. Аператары могуць быць прадстаўлены, як непасрэдна ў выглядзе значэнняў, пералічаных праз кропку з коскай (;), Так і ў выглядзе спасылак на дыяпазоны, дзе яны размешчаны. Менавіта апошні варыянт і будзе выкарыстаны намі ў дадзеным прыкладзе.
Усталёўваем курсор у полі "Вядомыя значэння y". Выконваем заціск левай кнопкі мышкі і вырабляем вылучэнне змесціва слупка "Y" табліцы. Як бачым, адрас названага масіва дадзеных тут жа адлюстроўваецца ў акне.
Аналагічным чынам запаўняем поле "Вядомыя значэння x". Ставім курсор у дадзенае поле, але на гэты раз вылучаем значэння слупка "X".
Пасля таго, як усе дадзеныя былі адлюстраваны ў акне аргументаў КВПИРСОН, Клацает па кнопцы "OK", Размешчанай у самым яго нізе.
- Як бачым, услед за гэтым праграма вырабляе разлік каэфіцыента дэтэрмінацыі і выдае вынік у тую вочка, якая была выдзелена яшчэ перад выклікам майстры функцый. У нашым прыкладзе значэнне вылічае паказчыка атрымалася роўным 1. Гэта значыць, што прадстаўленая мадэль абсалютна дакладная, гэта значыць, выключае хібнасць.
Урок: Майстар функцый у Microsoft Excel
Спосаб 2: вылічэнне каэфіцыента дэтэрмінацыі ў нелінейных функцыях
Але згаданы вышэй варыянт разліку шуканага значэння можна ўжываць толькі да лінейным функцый. Што ж рабіць, каб вырабіць яго разлік у нелінейнай функцыі? У Эксэля маецца і такая магчымасць. Яе можна ажыццявіць з дапамогай прылады "Рэгрэсія", Які з'яўляецца складовай часткай пакета "Аналіз дадзеных".
- Але перш, чым скарыстацца названым інструментам, варта актываваць сам "Пакет аналізу", Які па змаўчанні ў Эксэля адключаны. Перамяшчаемся ва ўкладку "Файл", А затым пераходзім па пункце "Параметры".
- У якое адкрылася акне вырабляем перасоўванне ў раздзел "Надбудовы" пры дапамозе навігацыі па левым вертыкальным меню. У ніжняй частцы правай вобласці акна размяшчаецца поле "Упраўленне". Са спісу даступных там падраздзелаў выбіраем найменне "Надбудовы Excel ...", А затым пстрыкаем па кнопцы "Перайсці ...", Размешчанай справа ад поля.
- Вырабляецца запуск вокны надбудоў. У цэнтральнай яго частцы размешчаны спіс даступных надбудоў. Ўсталёўваем сцяжок каля пазіцыі "Пакет аналізу". Услед за гэтым патрабуецца пстрыкнуць па кнопцы "OK" у правай частцы інтэрфейсу вокны.
- пакет інструментаў "Аналіз дадзеных" у бягучым экзэмпляры Excel будзе актываваны. Доступ да яго размяшчаецца на стужцы ва ўкладцы "Дадзеныя". Перамяшчаемся складнікаў азначанай ўкладку і клацает па кнопцы "Аналіз дадзеных" у групе налад "Аналіз".
- актывуецца акенца "Аналіз дадзеных" са спісам профільных інструментаў апрацоўкі інфармацыі. Вылучаем з гэтага пераліку пункт "Рэгрэсія" і клацает па кнопцы "OK".
- Затым адкрываецца акно інструмента "Рэгрэсія". Першы блок налад - "Уваходныя дадзеныя". Тут у двух палях трэба паказаць адрасы дыяпазонаў, дзе знаходзяцца значэння аргументу і функцыі. Ставім курсор у полі "Уваходны інтэрвал Y" і вылучаем на лісце змесціва калонкі "Y". Пасля таго, як адрас масіва адлюстраваўся ў акне "Рэгрэсія", Ставім курсор у полі "Уваходны інтэрвал Y" і сапраўды такім жа чынам вылучаем ячэйкі слупка "X".
каля параметраў "Пазнака" і "Канстанта-нуль" сцяжкі не ставім. Сцяжок можна ўсталяваць каля параметру "Узровень надзейнасці" і ў полі насупраць паказаць жаданую велічыню адпаведнага паказчыка (па змаўчанні 95%).
У групе "Параметры вываду" трэба паказаць, у якой вобласці будзе адлюстроўвацца вынік вылічэнні. Існуе тры варыянты:
- Вобласць на бягучым лісце;
- Іншы ліст;
- Іншая кніга (новы файл).
Спынім свой выбар на першым варыянце, каб зыходныя дадзеныя і вынік размяшчаліся на адным працоўным аркушы. Ставім перамыкач каля параметру "Выхадны інтэрвал". У полі насупраць гэтага пункта ставім курсор. Пстрыкаем левай кнопкай мышы па пустым элементу на лісце, які закліканы стаць левай верхняй ячэйкай табліцы вываду вынікаў разліку. Адрас дадзенага элемента павінен высвяціў ў поле акна "Рэгрэсія".
групы параметраў "Рэшткі" і "Нармальная верагоднасць" ігнаруем, так як для вырашэння пастаўленай задачы яны не важныя. Пасля гэтага клацает па кнопцы "OK", Якая размешчана ў правым верхнім куце акна "Рэгрэсія".
- Праграма вырабляе разлік на аснове раней уведзеных дадзеных і выводзіць вынік у названы дыяпазон. Як бачым, дадзеная прылада выводзіць на ліст даволі вялікая колькасць вынікаў па розных параметрах. Але ў кантэксце бягучага урока нас цікавіць паказчык "R-квадрат". У дадзеным выпадку ён роўны 0,947664, што характарызуе выбраную мадэль, як мадэль добрага якасці.
Спосаб 3: каэфіцыент дэтэрмінацыі для лініі трэнду
Акрамя названых вышэй варыянтаў, каэфіцыент дэтэрмінацыі можна адлюстраваць непасрэдна для лініі трэнду ў графіку, пабудаваным на лісце Excel. Высветлім, як гэта можна зрабіць на канкрэтным прыкладзе.
- Мы маем графік, пабудаваны на аснове табліцы аргументаў і значэнняў функцыі, якая была выкарыстаная для папярэдняга прыкладу. Вырабім пабудова да яго лініі трэнду. Клікаем па любым месцы галіне пабудовы, на якой размешчаны графік, левай кнопкай мышы. Пры гэтым на стужцы з'яўляецца дадатковы набор укладак - "Праца з дыяграмамі". Пераходзім ва ўкладку "Макет". Клацает па кнопцы "Лінія трэнду", Якая размешчана ў блоку інструментаў "Аналіз". З'яўляецца меню з выбарам тыпу лініі трэнду. Спыняем выбар на тым тыпе, які адпавядае канкрэтнай задачы. Давайце для нашага прыкладу абярэм варыянт "Экспанентны набліжэнне".
- Эксэля будуе прама на плоскасці пабудовы графіка лінію трэнду ў выглядзе дадатковай чорнай крывой.
- Цяпер нашай задачай з'яўляецца адлюстраваць уласна каэфіцыент дэтэрмінацыі. Клікаем правай кнопкай мышы па лініі трэнду. Актывуецца кантэкстнае меню. Спыняем выбар у ім на пункце "Фармат лініі трэнду ...".
Для выканання пераходу ў акно фармату лініі трэнду можна выканаць альтэрнатыўнае дзеянне. Вылучаем лінію трэнду клікам па ёй левай кнопкі мышы. Перамяшчаемся ва ўкладку "Макет". Клацает па кнопцы "Лінія трэнду" ў блоку "Аналіз". У якое адкрылася спісе клацает па самым апошнім пункце пераліку дзеянняў - "Дадатковыя параметры лініі трэнду ...".
- Пасля любога з двух вышэйзгаданых дзеянняў запускаецца акенца фармату, у якім можна вырабіць дадатковыя налады. У прыватнасці, для выканання нашай задачы неабходна ўсталяваць сцяжок насупраць пункта "Змясціць на дыяграму велічыню дакладнасці апраксімацыі (R ^ 2)". Ён размешчаны ў самым нізе акна. Гэта значыць, такім чынам мы ўключаем адлюстраванне каэфіцыента дэтэрмінацыі на вобласці пабудовы. Затым не забываем націснуць на кнопку "Зачыніць" унізе бягучага акна.
- Значэнне пэўнасці апраксімацыі, гэта значыць, велічыня каэфіцыента дэтэрмінацыі, будзе адлюстравана на лісце ў галіне пабудовы. У дадзеным выпадку гэта велічыня, як бачым, роўная 0,9242, што характарызуе апраксімацыю, як мадэль добрага якасці.
- Абсалютна дакладна такім чынам можна ўсталёўваць паказ каэфіцыента дэтэрмінацыі для любога іншага тыпу лініі трэнду. Можна мяняць тып лініі трэнду, вырабячы пераход праз кнопку на стужцы ці кантэкстнае меню ў акно яе параметраў, як было паказана вышэй. Затым ужо ў самым акне ў групе "Пабудова лініі трэнду" можна пераключыцца на іншы тып. Не забываем пры гэтым кантраляваць, каб каля пункта "Змясціць на дыяграму велічыню дакладнасці апраксімацыі" быў усталяваны сцяжок. Завяршыўшы вышэйпаказаныя дзеянні, пстрыкаем па кнопцы "Зачыніць" у ніжнім правым куце акна.
- Пры лінейным тыпе лінія трэнду ўжо мае значэнне пэўнасці апраксімацыі роўнае 0,9477, што характарызуе гэтую мадэль, як яшчэ больш пэўную, чым разгляданую намі раней лінію трэнду экспанентна тыпу.
- Такім чынам, перамыкаючыся паміж рознымі тыпамі лініі трэнду і параўноўваючы іх значэння дакладнасці апраксімацыі (каэфіцыент дэтэрмінацыі), можна знайсці той варыянт, мадэль якога найбольш дакладна апісвае прадстаўлены графік. Варыянт з самым высокім паказчыкам каэфіцыента дэтэрмінацыі будзе найбольш пэўным. На яго аснове можна будаваць самы дакладны прагноз.
Напрыклад, для нашага выпадку дасведчаным шляхам атрымалася ўсталяваць, што самы высокі ўзровень дакладнасці мае паліномны тып лініі трэнду другой ступені. Каэфіцыент дэтэрмінацыі ў дадзеным выпадку роўны 1. Гэта сведчыць аб тым, што названая мадэль абсалютна дакладная, што азначае поўнае выключэнне хібнасцяў.
Але, у той жа час, гэта зусім не значыць, што для іншага графіка таксама найбольш верагодным апынецца менавіта гэты тып лініі трэнду. Аптымальны выбар тыпу лініі трэнду залежыць ад тыпу функцыі, на падставе якой быў пабудаваны графік. Калі карыстальнік не валодае дастатковым аб'ёмам ведаў, каб "на вока" прыкінуць найбольш якасны варыянт, то адзіным выхадам вызначэння лепшага прагнозу з'яўляецца як раз параўнанне каэфіцыентаў дэтэрмінацыі, як было паказана на прыкладзе вышэй.
Чытайце таксама:
Пабудова лініі трэнду ў Excel
Апраксімацыя ў Excel
У Эксэля існуюць два асноўныя варыянты вылічэнні каэфіцыента дэтэрмінацыі: выкарыстанне аператара КВПИРСОН і прымяненне інструмента "Рэгрэсія" з пакета інструментаў "Аналіз дадзеных". Пры гэтым першы з гэтых варыянтаў прызначаны для выкарыстання толькі ў працэсе апрацоўкі лінейнай функцыі, а другі варыянт можна выкарыстоўваць практычна ва ўсіх сітуацыях. Акрамя таго, існуе магчымасць адлюстравання каэфіцыента дэтэрмінацыі для лініі трэндаў графікаў у якасці велічыні дакладнасці апраксімацыі. З дапамогай гэтага паказчыка маецца магчымасць вызначыць тып лініі трэнду, які размяшчае самым высокім узроўнем дакладнасці для канкрэтнай функцыі.
