Excel бағдарламасында корреляциялық талдау. Корреляциялық талдауды орындаудың мысалы

Корреляциялық талдау – 1-ші шаманың 2-ге тәуелділік деңгейін анықтау үшін қолданылатын кең таралған зерттеу әдісі. Электрондық кестеде зерттеудің бұл түрін жүзеге асыруға мүмкіндік беретін арнайы құрал бар.

Корреляциялық талдаудың мәні

Екі түрлі шама арасындағы байланысты анықтау керек. Басқаша айтқанда, екіншідегі өзгерістерге байланысты мәннің қай бағытта (кіші/үлкен) өзгеретінін ашады.

Корреляциялық талдаудың мақсаты

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

Маңызды! 0-ші коэффициентте шамалар арасында байланыс болмайды.

Корреляция коэффициентін есептеу

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

1-әдіс: Функция шебері арқылы корреляцияны анықтау

CORREL – корреляциялық талдауды жүзеге асыруға мүмкіндік беретін функция. Жалпы нысаны – CORREL(массив1;массив2). Толық нұсқаулық:

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

2. Функция шебері ашылады. Мұнда сіз табуыңыз керек CORREL, оны басыңыз, содан кейін «OK» түймесін басыңыз.

3. Аргументтер терезесі ашылады. «Массив1» жолында мәндердің 1-ші интервалдарының координаталарын енгізу керек. Бұл мысалда бұл сату құны бағаны. Бұл бағандағы барлық ұяшықтарды таңдау керек. Сол сияқты екінші бағанның координаталарын «Массив2» жолына қосу керек. Біздің мысалда бұл «Жарнамалық шығындар» бағаны.

4. Барлық диапазондарды енгізгеннен кейін «OK» түймесін басыңыз.

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

2-әдіс: Analysis ToolPak көмегімен корреляцияны есептеңіз

Корреляцияны анықтаудың тағы бір әдісі бар. Мұнда талдау пакетінде табылған функциялардың бірі пайдаланылады. Оны қолданар алдында құралды іске қосу керек. Егжей-тегжейлі нұсқаулар:

1. «Файл» бөліміне өтіңіз.

2. Жаңа терезе ашылады, онда сіз «Параметрлер» бөлімін басуыңыз керек.
3. «Қосымшалар» түймесін басыңыз.
4. Төменгі жағында «Менеджмент» элементін табамыз. Мұнда контекстік мәзірден «Excel қондырмаларын» таңдап, «ОК» түймесін басу керек.

5. Арнайы қосымшалар терезесі ашылды. «Талдау пакеті» элементінің жанына құсбелгі қойыңыз. Біз «OK» түймесін басамыз.
6. Белсендіру сәтті болды. Енді Деректерге көшейік. «Талдау» блогы пайда болды, онда «Деректерді талдау» түймесін басу керек.
7. Пайда болған жаңа терезеде «Корреляция» элементін таңдап, «ОК» түймесін басыңыз.

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

Соңғы ұпайлар шықты. Нәтиже бірінші әдістегідей – 0,97.

MS Excel-де көп корреляция коэффициентінің анықтамасы және есебі

Бірнеше шамалардың тәуелділік деңгейін анықтау үшін бірнеше коэффициенттер қолданылады. Болашақта нәтижелер корреляция матрицасы деп аталатын жеке кестеде жинақталады.

Толық нұсқаулық:

1. «Деректер» бөлімінде біз бұрыннан белгілі «Талдау» блогын тауып, «Деректерді талдау» түймесін басыңыз.

2. Пайда болған терезеде «Корреляция» элементін басып, «ОК» түймесін басыңыз.
3. «Енгізу аралығы» жолында біз бастапқы кестенің үш немесе одан да көп бағандарының аралығын жүргіземіз. Диапазонды қолмен енгізуге немесе оны LMB арқылы жай таңдауға болады және ол қажетті жолда автоматты түрде пайда болады. «Топтастыру» бөлімінде сәйкес топтастыру әдісін таңдаңыз. «Шығыс параметрінде» корреляция нәтижелері көрсетілетін орынды көрсетеді. Біз «OK» түймесін басамыз.

4. Дайын! Корреляциялық матрицасы құрылды.

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

Excel электрондық кестесінде жұптық корреляция коэффициентін қалай дұрыс салу керектігін анықтайық.

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

Мысалы, сізде x және y мәндері бар.

X - тәуелді айнымалы, ал у - тәуелсіз. Бұл көрсеткіштер арасындағы байланыстың бағыты мен күшін табу керек. Қадамдық нұсқаулық:

1. Функцияның көмегімен орташа мәндерді табайық ЖҮРЕК.

2. Әрқайсысын есептеп көрейік х и xavg, у и Орт «-» операторы арқылы.

3. Есептелген айырмашылықтарды көбейтеміз.

4. Осы бағандағы көрсеткіштердің қосындысын есептейміз. Алым – табылған нәтиже.

5. Айырманың бөлгіштерін есептеңдер х и x-орташа, у и y-орта. Ол үшін квадраттауды орындаймыз.

6. Функцияны пайдалану АВТОСУММА, алынған бағандардағы көрсеткіштерді табыңыз. Біз көбейтуді жасаймыз. Функцияны пайдалану ROOT нәтиженің квадраты.

7. Бөліндіні бөлгіш пен алым мәндерін қолданып есептейміз.

8. CORREL – күрделі есептеулерді болдырмауға мүмкіндік беретін біріктірілген функция. Біз «Функция шеберіне» өтіп, CORREL таңдап, индикаторлар массивтерін көрсетеміз х и у. Алынған мәндерді көрсететін графикті құрастырамыз.

Excel бағдарламасындағы жұптық корреляция коэффициенттерінің матрицасы

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

Қадамдық нұсқаулық:

1. Біз «Деректер» қойындысының «Талдау» блогында орналасқан «Деректерді талдау» бөліміне өтеміз. Пайда болған тізімнен Корреляция тармағын таңдаңыз.
2. Біз барлық қажетті параметрлерді орнаттық. «Енгізу аралығы» – барлық төрт бағанның аралығы. «Шығыс аралығы» – қорытындыларды көрсеткіміз келетін орын. Біз «OK» түймесін басамыз.
3. Таңдалған жерде корреляциялық матрица салынды. Жол мен бағанның әрбір қиылысы корреляция коэффициенті болып табылады. Координаталар сәйкес келген кезде 1 саны көрсетіледі.

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

CORREL – 2 массив арасындағы корреляция коэффициентін есептеу үшін қолданылатын функция. Осы функцияның барлық мүмкіндіктерінің төрт мысалын қарастырайық.

Excel бағдарламасында CORREL функциясын қолдану мысалдары

Бірінші мысал. Онда компания қызметкерлерінің он бір жылдағы орташа жалақысы және доллар бағамы туралы ақпарат жазылған тақтайша бар. Осы екі шама арасындағы байланысты анықтау қажет. Кесте келесідей көрінеді:

Есептеу алгоритмі келесідей көрінеді:

Көрсетілген ұпай 1-ге жақын. Нәтиже:

Әрекеттердің нәтижеге әсер етуінің корреляциялық коэффициентін анықтау

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

Төмендегі формулаларды пайдаланып корреляция коэффициентін есептейміз:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

нәтижелері:

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

Бейне көріністері мен репосттардың корреляциясы бойынша мазмұнның танымалдылығын талдау

Үшінші мысал. YouTube видеохостингінде өз бейнелерін жарнамалау үшін адам арнаны жарнамалау үшін әлеуметтік желілерді пайдаланады. Ол әлеуметтік желілердегі репосттар мен арнадағы көру саны арасында белгілі бір байланыс бар екенін байқайды. Электрондық кесте құралдарының көмегімен болашақ өнімділікті болжауға бола ма? Репосттардың санына байланысты бейнені қарау санын болжау үшін сызықтық регрессия теңдеуін қолданудың негізділігін анықтау қажет. Мәндері бар кесте:

Енді төмендегі формула бойынша 2 көрсеткіштің арасындағы байланыстың болуын анықтау қажет:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Күшті тікелей байланыс”;”Күшті кері байланыс”);”Әлсіз немесе жоқ”)' class='formula'>

Егер алынған коэффициент 0,7-ден жоғары болса, онда сызықтық регрессия функциясын қолданған дұрыс. Бұл мысалда біз:

Енді біз графикті құрастырамыз:

Бұл теңдеуді 200, 500 және 1000 бөлісудегі көру санын анықтау үшін қолданамыз: =9,2937*D4-206,12. Біз келесі нәтижелерді аламыз:

функция БОЛЖАУ егер, мысалы, екі жүз елу репост болса, қазіргі уақытта көру санын анықтауға мүмкіндік береді. Біз қолданамыз: 0,7; БОЛЖАУ(D7;B3:B8;A3:A8);”Мәндер өзара байланысты емес”)' class='formula'>. Біз келесі нәтижелерді аламыз:

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

Бұл функцияның келесі мүмкіндіктері бар:

1. Бос ұяшықтар есепке алынбайды.
2. Логикалық және Мәтін түрі ақпараты бар ұяшықтар есепке алынбайды.
3. «-» қосарлы терістеу сандар түріндегі логикалық мәндерді есепке алу үшін қолданылады.
4. Зерттелетін массивтердегі ұяшықтардың саны сәйкес болуы керек, әйтпесе #N/A хабары көрсетіледі.

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

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

қорытынды

Электрондық кестедегі корреляциялық талдау қарапайым және автоматтандырылған процесс. Оны орындау үшін сізге тек қажетті құралдардың қайда орналасқанын және оларды бағдарлама параметрлері арқылы қалай белсендіру керектігін білу қажет.