EXCEL-ДА АНЫҚТАУ КОЭФФИЦИЕНТІ

Статистикаға салынған модельдің сапасын сипаттайтын көрсеткіштердің бірі - жақындаған сенімділік мәні деп аталатын анықтау коэффициенті (R ^ 2). Сонымен бірге сіз болжамдардың дұрыстығы деңгейін анықтай аласыз. Әр түрлі Excel құралдарымен осы индикаторды қалай есептеуіңізді білейік.

Белгілеу коэффициентін есептеу

Белгілеу коэффициентінің деңгейіне байланысты модельдерді үш топқа бөлуге болады:

0.8 - 1 - жақсы сапа моделі;
0,5 - 0,8 - қолайлы сапа моделі;
0 - 0,5 - нашар сапа моделі.

Соңғы жағдайда, модельдің сапасы болжамды пайдаланудың мүмкін еместігін көрсетеді.

Excel бағдарламасында көрсетілген мәнді есептеу әдісін таңдау регрессияның сызықтық немесе жоқ екеніне байланысты. Бірінші жағдайда сіз функцияны пайдалана аласыз KVPIRSON, ал екіншісінде талдау бумасынан арнайы құралды пайдалануға тура келеді.

1-әдіс: сызықтық функциямен анықтау коэффициентін есептеу

Ең алдымен сызықты функцияны анықтау коэффициентін қалай табуға болатынын анықтаңыз. Бұл жағдайда бұл көрсеткіш корреляция коэффициентінің квадратына тең болады. Төменде көрсетілген нақты кестенің мысалын пайдаланып кіріктірілген Excel функциясын пайдаланып оны есептеп шығарамыз.

Есептеуден кейін анықтау коэффициенті көрсетілетін ұяшықты таңдаңыз және белгішені басыңыз «Функцияны кірістіру».

Басталады Функция шебері. Санатына көшіңіз «Статистикалық» және атын белгілеңіз KVPIRSON. Бұдан әрі батырманы басыңыз «ОК».

Функция дәлелдері терезесі басталады. KVPIRSON. Статистикалық топтағы осы оператор Pearson функциясының корреляциялық коэффициентінің квадратын есептеуге арналған, яғни сызықтық функция. Естеріңізге сала кетейік, желілік функциямен анықтау коэффициенті корреляция коэффициентінің квадратына тең.
Бұл мәлімдемеге арналған синтаксис:
= KVPIRSON (белгілі_y; белгілі_x)
Осылайша, функция екі операторға ие, олардың бірі функцияның мәндерінің тізбесі, екіншісі - дәлел. Операторлар тікелей нүктелі үтір арқылы берілген мәндер ретінде ұсынылуы мүмкін (;), және олар орналасқан жерлерге байланыстар түрінде. Бұл мысалда біз қолданатын соңғы нұсқа.
Курсорды өріске орнатыңыз «Белгілі Y құндылықтары». Біз сол жақ тінтуірдің қысқышын басып, бағанның мазмұнын таңдаймыз. «Y» кестелер. Көріп отырғаныңыздай, көрсетілген деректер массивінің мекен-жайы бірден терезеде көрсетіледі.
Сондай-ақ өрісті толтырыңыз «Белгілі x». Курсорды осы өріске қойыңыз, бірақ бұл жолы баған мәндерін таңдаңыз «X».
Барлық деректер аргументтер терезесінде көрсетілгеннен кейін KVPIRSONтүймені басыңыз «ОК»оның түбінде орналасқан.

Көріп отырғаныңыздай, осыдан кейін бағдарлама анықтау коэффициентін есептеп, нәтижені қоңыраудан бұрын таңдалған ұяшыққа қайтарады Функцияның шеберлері. Біздің мысалда есептеу индикаторының мәні 1 болды. Бұл дегеніміз, ұсынылған модель өте сенімді, яғни ол қатені жоққа шығарады.

Сабақ: Microsoft Excel бағдарламасындағы жұмыс шебері

2-әдіс: сызықты емес функцияларда анықтау коэффициентін есептеу

Бірақ жоғарыда келтірілген параметрді есептеудің қажетті параметрі сызықтық функцияларға ғана қолданыла алады. Сызықты емес функцияны есептеу үшін не істеу керек? Excel бағдарламасында мұндай мүмкіндік бар. Бұл құралмен жасауға болады. «Регрессия»ол пакеттің бір бөлігі болып табылады «Деректерді талдау».

Бірақ бұл құралды қолданар алдында оны өзіңіз қосуыңыз керек. «Талдау пакеті»ол әдепкіде Excel бағдарламасында өшірілген. Қойындыға жылжыңыз «Файл»содан кейін тармақ арқылы өтіңіз «Параметрлер».

Ашылған терезеде біз секцияға көшеміз. Қосымшалар Сол жақ тік мәзір арқылы шарлау арқылы. Оң жақ бөліктің төменгі жағында өріс бар «Менеджмент». Қол жетімді бөлімшелер тізімінен атауды таңдаңыз «Excel қондырмалары ...»батырмасын басыңыз «Бар ...»даланың оң жағында орналасқан.

Қосымшалардың терезесі басталады. Орталық бөлікте қол жетімді қондырмалар тізімі бар. Орынның жанындағы құсбелгіні қойыңыз «Талдау пакеті». Бұдан кейін түймесін басыңыз. «ОК» интерфейс терезесінің оң жағында.

Құрал бумасы «Деректерді талдау» Excel бағдарламасының ағымдағы данасында белсендіріледі. Оған кіру қойындыдағы таспада орналасқан «Деректер». Белгіленген қойындыға жылжытыңыз және түймесін басыңыз. «Деректерді талдау» баптау тобында «Талдау».

Қосылған терезе «Деректерді талдау» арнайы ақпараттық өңдеу құралдарының тізімі бар. Осы тізім элементінен таңдаңыз «Регрессия» батырмасын басыңыз «ОК».

Содан кейін құрал терезесі ашылады. «Регрессия». Параметрлердің бірінші блогы - «Енгізу». Мұнда екі өрісте аргумент мәндері мен функциялары орналасқан ауқымдардың мекен-жайларын көрсету қажет. Курсорды өріске қойыңыз «Енгізу аралығы Y» парақтағы бағанның мазмұнын таңдаңыз «Y». Жиым адресі терезеде көрсетілгеннен кейін «Регрессия»жүгіргіні өріске қойыңыз «Енгізу аралығы Y» және бағаналы жасушаларды дәл сол жолмен таңдаңыз «X».
Параметрлер туралы «Тег» және «Тұрақты-нөл» құсбелгі қойылмайды. Параметрдің жанында құсбелгі орнатуға болады «Сенімділік деңгейі» ал керісінше сәйкес индикатордың қажетті мәнін көрсетіңіз (әдепкі бойынша 95%).
Топта «Шығару параметрлері» Сіз қай салада есептеу нәтижесі көрсетілетінін көрсетуіңіз керек. Үш опция бар:
- Ағымдағы парақтағы аймақ;
- Басқа парақ;
- Басқа кітап (жаңа файл).
Бастапқы деректер мен нәтижелер бір жұмыс парағына орналастырылған бірінші нұсқадағы таңдауды тоқтатайық. Қосқышты параметрдің жанында орнатыңыз «Шығу аралығы». Осы тармақтың қарама-қарсы жағында жүгіргіні қойыңыз. Есептеу нәтижелерінің кестесінің сол жоғарғы ұяшығына айналуға арналған парақтағы бос элементтің сол жақ батырмасын басамыз. Бұл элементтің мекенжайын терезеде көрсету керек «Регрессия».
Параметр топтары «Қалдықтар» және «Қалыпты ықтималдық» назардан тыс қалдырмаңыз, себебі олар проблеманы шешу үшін маңызды емес. Осыдан кейін біз батырманы басамыз. «ОК»ол терезенің оң жақ жоғарғы бұрышында орналасқан «Регрессия».

Бағдарлама бұрын енгізілген деректер негізінде есептеп, нәтижені көрсетілген ауқымда көрсетеді. Көріп отырғаныңыздай, бұл құрал параққа әртүрлі параметрлер бойынша өте көп нәтижелерді көрсетеді. Бірақ ағымдағы сабақ тұрғысынан біз индикаторға қызығушылық танытқанбыз «R-шаршы». Бұл жағдайда ол 0.947664 мәніне тең, ол таңдалған модельді жақсы сапа моделі ретінде сипаттайды.

3-әдіс: тренд сызығы үшін анықтау коэффициенті

Жоғарыда келтірілген опциялардан басқа, анықтау коэффициенті Excel парағында орнатылған графиктегі тренд сызығына тікелей көрсетілуі мүмкін. Мұны нақты мысалмен қалай жасауға болатынын білеміз.

Бізде алдыңғы мысалда қолданылған функцияның аргументтері мен мәндерінің кестесіне негізделген сызба бар. Оған тренд сызығы жасайық. Біз құрылыс алаңындағы графикті тінтуірдің сол жақ түймешігімен орналастыратын кез келген жерге басамыз. Сонымен қатар, таспада қосымша қойындылар пайда болады - «Диаграммалармен жұмыс істеу». Қойындыға өтіңіз «Орналасу». Біз батырманы басамыз «Trend line»құрал блогында орналасқан «Талдау». Тренд сызығы түрін таңдау арқылы мәзір пайда болады. Таңдауды белгілі бір тапсырма түріне сәйкес тоқтатамыз. Біздің мысалымызды таңдап алайық «Экспоненциалды жуықтау».

Excel кесте жазықтықта қосымша қара қисық сызық түрінде тренд сызығын құрастырады.

Енді біздің міндетіміз - өзін анықтаудың коэффициентін көрсету. Тренд сызығына тінтуірдің оң жағын басыңыз. Мәтінмәндік мәзір белсендірілді. Таңдауды элементте тоқтатыңыз «Trend line форматы ...».
Тренд сызығы пішімі терезесіне өту үшін, балама әрекетті орындауға болады. Тінтуірдің сол жақ батырмасын басу арқылы тренд сызығын таңдаңыз. Қойындыға жылжыңыз «Орналасу». Біз батырманы басамыз «Trend line» блокта «Талдау». Ашылатын тізімде әрекеттер тізіміндегі ең соңғы элементті басамыз - «Кеңейтілген тренд сызығы параметрлері ...».

Жоғарыдағы екі әрекеттен кейін қосымша параметрлерді жасауға болатын пішім терезесі іске қосылады. Атап айтқанда, біздің міндетімізді орындау үшін, жанындағы құсбелгіні тексеру қажет «Диаграмма жақындағаның (R ^ 2) дәлдігінің мәнін салыңыз». Ол терезенің ең төменгі жағында орналасқан. Яғни, осылайша, құрылыс алаңында анықтау коэффициентінің көрінісі кіреді. Содан кейін түймесін басуды ұмытпаңыз «Жабу» ағымдағы терезенің төменгі жағында.

Аппроксимацияның сенімділік мәні, яғни анықталу коэффициентінің мәні сюжеттік аймақта парақта көрсетіледі. Бұл жағдайда, бұл мән, біз көріп отырғандай, жақсы сапа моделі ретінде жуықтауды сипаттайтын 0,9242-ге тең.

Дәлірек айтқанда, тренд сызығының кез-келген басқа түрі үшін анықтау коэффициентінің көрінісін орнатуыңызға болады. Тренд сызығының түрін таспаның немесе мәтінмәндік мәзірдегі параметрлер терезесіндегі көшуді жоғарыда көрсетілгендей өзгерту арқылы өзгертуге болады. Содан кейін топтағы терезеде «Тренд сызығын құру» басқа түрге ауыса алады. Бұл нүктеге жақын болу үшін бақылауды ұмытпаңыз «Диаграммадағы жуықтаудың дәлдігінің мәні» тексерілді. Жоғарыда көрсетілген әрекеттерді аяқтағаннан кейін түймесін басыңыз. «Жабу» терезенің төменгі оң жақ бұрышында.

Сызықтық түрдегі жағдайда тренд сызығы 0.9477 жақындаған сенімділік мәніне ие, бұл осы модельді біз бұрын қараған экспоненталық тренд сызығынан гөрі одан да сенімді деп сипаттайды.

Осылайша тренд сызықтарының әртүрлі типтері арасында ауысу және оларды жақындатудың сенімділігін (сәйкестендіру коэффициенті) салыстыра отырып, ұсынылған графикті неғұрлым дәл сипаттайтын нұсқаны таба аласыз. Ең сенімді индексі бар нұсқа ең сенімді болады. Оның негізінде ең дәл болжам жасай аласыз.
Мысалы, біздің жағдайда эксперимент арқылы сенімнің ең жоғары деңгейі екінші дәрежелі тренд сызығының многодинамикалық түрі болып табылатынын анықтай алдық. Бұл жағдайда анықтау коэффициенті 1 тең. Бұл дегеніміз, бұл модель өте сенімді, бұл қателерді толығымен жою.
Бірақ сонымен бірге, бұл тренд сызығының бұл түрі басқа диаграмма үшін ең сенімді болатынын білдірмейді. Тренд сызығының түрін оңтайлы таңдау графиктің негізінде жасалған функция түріне байланысты болады. Егер пайдаланушы жоғары сапалы опцияны бағалау үшін жеткілікті білімі болмаса, онда ең жақсы болжауды анықтаудың жалғыз жолы - жоғарыдағы мысалда көрсетілгендей, анықтау коэффициенттерін салыстыру.

Сондай-ақ, қараңыз:
Excel бағдарламасында тренд сызықтарын құру
Excel жуықтау

Excel бағдарламасында анықтамалық коэффициентін есептеудің екі негізгі нұсқасы бар: операторды пайдалану KVPIRSON және қолданбалы құрал «Регрессия» құралдар жиынтығынан «Деректерді талдау». Бұл жағдайда осы опциялардың біріншісі тек сызықтық функцияны өңдеуге арналған, ал басқа опция дерлік барлық жағдайларда қолданылуы мүмкін. Сонымен қатар, диаграмманың тренд сызығына сәйкестік коэффициентін жақындаған сенімділік мәні ретінде көрсету мүмкін. Бұл көрсеткішті пайдалана отырып, белгілі бір функция үшін ең жоғары сенімділік деңгейіне ие тренд сызығының түрін анықтауға болады.