Microsoft Excel-де анықтау коэффициентін есептеу

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

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

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

• 0,8 - 1 - жақсы сапа моделі;
• 0,5 - 0,8 - қолайлы сапа моделі;
• 0 - 0,5 - сапасыз модель.

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

Көрсетілген мәнді Excel-де қалай есептеу керектігін таңдау регрессияның сызықты немесе жоқтығына байланысты. Бірінші жағдайда функцияны пайдалануға болады КВПИРСОН, ал екіншісінде талдау пакетінен арнайы құралды пайдалану керек.

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

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

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



2. Басталады Мүмкіндік шебері. Оның санатына көшу «Статистикалық» және атын белгілеңіз КВПИРСОН. Келесі батырманы басыңыз «Жарайды».



3. Функция аргументтері терезесі басталады. КВПИРСОН. Статистикалық топтың бұл операторы Пирсон функциясының корреляция коэффициентінің квадратын, яғни сызықтық функцияны есептеу үшін жасалған. Біз есімізде, сызықтық функцияның көмегімен анықтау коэффициенті корреляция коэффициентінің квадратына дәл келеді.

   Бұл тұжырымның синтаксисі:

   = KVPIRSON (белгілі_ мәндер_; белгілі_x мәндер)

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

   Меңзерді өріске қойыңыз Белгілі y Құндылықтар. Тінтуірдің сол жақ батырмасын басып, бағанның мазмұнын таңдаймыз «Y» кестелер. Көріп отырғаныңыздай, көрсетілген деректер массивінің мекен-жайы дереу терезеде көрсетіледі.

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

   Деректер терезесінде барлық деректер көрсетілгеннен кейін КВПИРСОНтүймесін басыңыз «Жарайды»оның төменгі жағында орналасқан.



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

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

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

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

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



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



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



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



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



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

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

   Топта Шығу параметрлері есептеу нәтижесі қай облыста көрсетілетінін көрсету керек. Үш нұсқа бар:

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

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

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



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

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

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

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



2. Excel диаграммада қосымша қара қисық түрінде тренд сызығын салады.



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

   

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



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



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



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



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



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

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

   

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

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

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