Excel бағдарламасындағы анықтау коэффициенті

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

Анықтама коэффициентін есептеу

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

• 0,8 - 1 - сапалы модель;
• 0,5 - 0.8 - қолайлы сапаның моделі;
• 0 - 0.5 - сапасыздықтың моделі.

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

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

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

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

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



2. Магистрлер басталады. Біз оның «статистикалық» санатына және «Квгирсон» атауын атап өтеміз. Келесі, «ОК» батырмасындағы саз.



3. Quilinsson функциясының аргументтері терезесі басталады. Статистика тобынан осы оператор Пирсон функциясының корреляциялық коэффициентінің квадратын есептеуге арналған, яғни, сызықтық функция. Есіңіздегенде, сызықтық функциямен анықтау коэффициенті корреляциялық коэффициенттің квадратына тең.

   Бұл оператордың синтаксисі:

   = Cvgirson (белгілі_stations_y; белгілі_name_x)

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

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

   Сол сияқты, «белгілі X» өрісін толтырыңыз. Біз жүгіргіні осы салаға қойдық, бірақ бұл жолы «X» бағанының мәндерін бөледі.

   Барлық деректер CVGIRSON Дәлелдер терезесінде көрсетілгеннен кейін, оның түбінде орналасқан «OK» түймесіндегі саз.



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

Сабақ: Microsoft Excel-дегі функциялардың шебері

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

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

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



2. Ашылған терезеде сол жақ тік мәзірдегі шарлауды пайдаланып «Қосымшаны» бөліміне жылжытыңыз. Оң жақтағы терезенің төменгі жағында басқару өрісі орналасқан. Қол жетімді бөлімдердің тізімінен «Қосымша қондырма ...», содан кейін өрістің оң жағында орналасқан «Go ...» түймесін басыңыз.



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



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



5. «Деректерді талдау» терезесі ақпаратты өңдеу құралдарының тізімімен қосылады. Біз осы тізімдегі «Регрессион» және «ОК» батырмасында сазды бөлдік.



6. Содан кейін «Регрессия» құралы терезесі ашылады. Бірінші параметрлер блогы - «Кіріс деректері». Мұнда екі өрісте сіз аргумент пен функцияның мәні орналасқан диапазондардың мекенжайын көрсетуіңіз керек. Біз курсорды «кіріс аралық y» өрісіне қойдық және параққа «Y» бағанының мазмұнын бөлдік. Массивтің мекен-жайы регрессион терезесінде пайда болғаннан кейін, біз жүгіргіні «кіріс аралық y» өрісіне қойып, «X» бағанының ұяшықтарын бірдей етіп орналастырдық.

   «Тег» және «Тұрақты-Нөл» параметрлерінің жанында қоймайды. Құсбелгіні «Сенімділік» параметрінің жанында және қарама-қарсы өріске орнатуға болады, және сәйкес индикатордың қажетті мәнін (әдепкі бойынша 95%) көрсетеді.

   «Шығару параметрлері» тобында сіз есептеу нәтижесі қай аймақта көрсетілетінін көрсетуіңіз керек. Үш опция бар:

   • Ағымдағы парақта аудан;
   • Басқа парақ;
   • Басқа кітап (жаңа файл).

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

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



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

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

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

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



2. Excel қосымша қара қисық сызық түрінде тренд сызығын салудың жазықтығына тікелей салады.



3. Енді біздің міндетіміз - нақты анықтау коэффициентін көрсету. Тренд сызығын оң жақ түймесімен нұқыңыз. Контекстік мәзір іске қосылды. Trend Line Formation нүктесінде таңдауды бастаңыз.

   

   Trend Line Format терезесіне көшу үшін сіз балама әрекетті жасай аласыз. Біз тінтуірдің сол жақ батырмасымен тренд сызығын бөліп алдық. «Орналасу» қойындысына өтіңіз. «Талдау» блогындағы «Trend Line» батырмасындағы саз. Саздар тізімінің тізімінде Әрекеттер тізімінің ең жақсы нүктесіндегі - «Трендтің қосымша параметрлері ...».



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



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



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



7. Сызықтық түрі бар, тренд сызығы жақындау дәлдігінің мәні 0,9477-ге тең, ол осы модельді біз қарастырған экспоненциалды типтегі тенденцияның сызығынан гөрі көбірек сипаттайды.



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

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

   

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

Сондай-ақ қараңыз:

Excel-де тренд сызығын салу

Excel бағдарламасындағы жақындау

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