Excelде корреляциялык анализ. Корреляциялык анализ жүргүзүүнүн мисалы

Корреляциялык анализ – 1-баанын 2-ге көз карандылык деңгээлин аныктоо үчүн колдонулган кеңири таралган изилдөө ыкмасы. Электрондук таблицада изилдөөнүн бул түрүн ишке ашырууга мүмкүндүк берген атайын курал бар.

Корреляциялык анализдин маңызы

Эки түрдүү чоңдуктун ортосундагы байланышты аныктоо зарыл. Башкача айтканда, экинчидеги өзгөрүүлөргө жараша маани кайсы багытта (кичи/чоңураак) өзгөрөрүн ачып берет.

Корреляциялык анализдин максаты

Корреляция коэффициентин идентификациялоо башталганда көз карандылык белгиленет. Бул ыкма регрессиялык анализден айырмаланат, анткени корреляциянын жардамы менен эсептелген бир гана көрсөткүч бар. Интервал +1ден -1ге чейин өзгөрөт. Эгерде ал оң болсо, анда биринчи маанинин жогорулашы 2-нин көбөйүшүнө өбөлгө түзөт. Эгерде терс болсо, анда 1-баанын өсүшү 2-нин төмөндөшүнө өбөлгө түзөт. Коэффициент канчалык жогору болсо, бир маани экинчисине ошончолук күчтүү таасир этет.

Маанилүү! 0-коэффицентинде чоңдуктардын ортосунда эч кандай байланыш жок.

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

Келгиле, бир нече үлгүлөр боюнча эсептөөнү талдап көрөлү. Мисалы, таблица маалыматтары бар, анда жарнаманы жылдыруу жана сатуунун көлөмү айлар боюнча өзүнчө тилкелерде сүрөттөлөт. Таблицага таянып, биз сатуу көлөмүнүн жарнамалык илгерилетүү үчүн сарпталган акчага көз карандылык деңгээлин аныктайбыз.

Метод 1: Функция устасы аркылуу корреляцияны аныктоо

CORREL – корреляциялык анализди ишке ашырууга мүмкүндүк берүүчү функция. Жалпы формасы - CORREL(massiv1;massiv2). Толук нускамалар:

1. Эсептөөнүн натыйжасын көрсөтүү пландаштырылган уячаны тандоо керек. Формула киргизүү үчүн текст талаасынын сол жагында жайгашкан "Функцияны киргизүү" баскычын чыкылдатыңыз.

2. Функция чебери ачылат. Бул жерде сиз табышыңыз керек CORREL, аны басып, андан кийин "OK" баскычын басыңыз.

3. Аргументтер терезеси ачылат. “Массив1” сабына маанилердин 1-аралыктарынын координаталарын киргизүү керек. Бул мисалда бул Сатуу наркы тилкеси. Сиз жөн гана бул тилкедеги бардык уячаларды тандоо керек. Ошо сыяктуу эле, экинчи тилкенин координаттарын "Аррай2" сызыгына кошуу керек. Биздин мисалда, бул "Жарнамалык чыгымдар" тилкеси.

4. Бардык диапазондорду киргизгенден кийин, "OK" баскычын чыкылдатыңыз.

Биздин аракеттерибиздин башында көрсөтүлгөн уячада коэффициент көрсөтүлдү. Алынган натыйжа 0,97. Бул көрсөткүч биринчи маанинин экинчисинен жогорку көз карандылыгын көрсөтөт.

Метод 2: Analysis ToolPak аркылуу корреляцияны эсептеңиз

Корреляцияны аныктоонун дагы бир ыкмасы бар. Бул жерде талдоо пакетинде табылган функциялардын бири колдонулат. Аны колдонуудан мурун куралды активдештирүү керек. Толук нускамалар:

1. "Файл" бөлүмүнө өтүңүз.

2. Жаңы терезе ачылат, анда сиз "Орнотуулар" бөлүмүн басышыңыз керек.
3. "Кошумчаларды" чыкылдатыңыз.
4. Биз ылдый жагында "Менеджмент" элементин табабыз. Бул жерде сиз контексттик менюдан "Excel кошумчаларын" тандап, "OK" баскычын чыкылдатыңыз.

5. Атайын кошумча терезе ачылды. "Анализ пакети" элементинин жанына белги коюңуз. Биз "OK" баскычын басыңыз.
6. Жандандыруу ийгиликтүү болду. Эми Берилиштерге баралы. "Анализ" блогу пайда болду, анда сиз "Маалыматтарды талдоо" баскычын басышыңыз керек.
7. Пайда болгон жаңы терезеде "Корреляция" элементин тандап, "OK" баскычын басыңыз.

8. Экранда талдоо орнотуулары терезеси пайда болду. "Киргизүү аралыгы" сабында талдоого катышкан бардык мамычалар диапазонун киргизүү керек. Бул мисалда "Сатуу наркы" жана "Жарнамалык чыгымдар" тилкелери. Чыгуу дисплей орнотуулары алгач Жаңы иш барагына коюлат, бул натыйжалар башка баракта көрсөтүлөт дегенди билдирет. Кошумча, сиз натыйжанын чыгыш ордун өзгөртө аласыз. Бардык орнотууларды жасагандан кийин, чыкылдатыңыз "OK".

Акыркы упайлар чыкты. Натыйжа биринчи ыкмадагыдай эле - 0,97.

MS Excel программасында көп корреляция коэффициентин аныктоо жана эсептөө

Бир нече чоңдуктун көз карандылык деңгээлин аныктоо үчүн бир нече коэффициенттер колдонулат. Келечекте жыйынтыктар корреляция матрицасы деп аталган өзүнчө таблицада жалпыланат.

Толук маалымат:

1. "Маалымат" бөлүмүндө биз буга чейин белгилүү болгон "Анализ" блогун таап, "Маалыматтарды талдоо" баскычын чыкылдатыңыз.

2. Пайда болгон терезеде, "Корреляция" элементин чыкылдатып, "OK" баскычын чыкылдатыңыз.
3. "Киргизүү интервалы" сабында биз булак таблицасынын үч же андан көп мамычалары үчүн интервалды айдайбыз. Диапазон кол менен киргизилиши же жөн гана LMB менен тандалышы мүмкүн жана ал автоматтык түрдө керектүү сапта пайда болот. "Топтоодо" ылайыктуу топтоо ыкмасын тандаңыз. "Чыгуу параметринде" корреляциянын натыйжалары көрсөтүлө турган жерди белгилейт. Биз "OK" баскычын басыңыз.

4. Даяр! Корреляциялык матрицасы түзүлдү.

Excelдеги жуп корреляция коэффициенти

Келгиле, Excel электрондук жадыбалында жуп корреляция коэффициентин кантип туура чийүүгө болорун карап көрөлү.

Excel программасында жуп корреляция коэффициентин эсептөө

Мисалы, сизде х жана у маанилери бар.

X көз каранды өзгөрмө жана у көз карандысыз. Бул көрсөткүчтөрдүн ортосундагы байланыштын багытын жана күчүн табуу зарыл. Этап-этабы менен нускама:

1. Функциянын жардамы менен орточо маанилерди табалы ЖҮРӨК.

2. Келгиле, ар бирин эсептеп көрөлү х и xavg, у и Орточо «-» операторун колдонуу.

3. Биз эсептелген айырмачылыктарды көбөйтөбүз.

4. Бул тилкедеги көрсөткүчтөрдүн суммасын эсептейбиз. Нумератор табылган натыйжа.

5. Айырманын бөлүүчүлөрүн эсептеңиз х и x-орточо, ж и y-орто. Бул үчүн биз чарчылоону аткарабыз.

6. Функцияны колдонуу AUTOSUMMA, алынган графалардагы көрсөткүчтөрдү табыңыз. Биз көбөйтүүнү жасайбыз. Функцияны колдонуу ЖАЮУУДА натыйжаны квадрат.

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).

Results:

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

Видео көрүүлөрдүн жана репосттордун корреляциясы боюнча контенттин популярдуулугун талдоо

Үчүнчү мисал. YouTube видеохостингинде өз видеолорун жарнамалоо үчүн адам каналды жарнамалоо үчүн социалдык тармактарды колдонот. Ал социалдык тармактардагы репосттордун саны менен каналдагы көрүүлөрдүн санынын ортосунда кандайдыр бир байланыш бар экенин байкайт. Электрондук жадыбал куралдарын колдонуу менен келечектеги өндүрүмдүүлүктү алдын ала айтуу мүмкүнбү? Репосттордун санына жараша видео көрүүлөрдүн санын болжолдоо үчүн сызыктуу регрессия теңдемесин колдонуунун негиздүүлүгүн аныктоо зарыл. Маанилүү таблица:

Эми төмөндөгү формула боюнча 2 көрсөткүчтүн ортосундагы байланыштын бар экендигин аныктоо керек:

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

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

Азыр биз графикти куруп жатабыз:

Биз бул теңдемени 200, 500 жана 1000 бөлүшүү боюнча көрүүлөрдүн санын аныктоо үчүн колдонобуз: =9,2937*Д4-206,12. Биз төмөнкү натыйжаларды алабыз:

милдети Болжолдоо Эгер, мисалы, эки жүз элүү репост бар болсо, учурда көрүүлөрдүн санын аныктоого мүмкүндүк берет. Биз кайрылабыз: 0,7; PREDICTION(D7;B3:B8;A3:A8);”Баалар бири-бирине байланышпайт”)' class='formula'>. Биз төмөнкү натыйжаларды алабыз:

Excel программасында CORREL функциясын колдонуунун өзгөчөлүктөрү

Бул функция төмөнкү өзгөчөлүктөргө ээ:

1. Бош уячалар эсепке алынбайт.
2. Логикалык жана Текст түрүндөгү маалыматты камтыган уячалар эсепке алынбайт.
3. Кош жокко чыгаруу "-" сандар түрүндөгү логикалык маанилерди эсепке алуу үчүн колдонулат.
4. Изилденген массивдердеги уячалардын саны дал келиши керек, антпесе #N/A билдирүүсү көрсөтүлөт.

Корреляция коэффициентинин статистикалык маанисин баалоо

Корреляция коэффициентинин маанилүүлүгүн текшерүүдө индикатор 0 маанисине ээ, ал эми альтернатива жок деген нөл гипотеза болуп саналат. Текшерүү үчүн төмөнкү формула колдонулат:

жыйынтыктоо

Электрондук таблицадагы корреляциялык анализ жөнөкөй жана автоматташтырылган процесс. Аны аткаруу үчүн сиз керектүү шаймандар кайда жайгашканын жана программанын жөндөөлөрү аркылуу аларды кантип активдештирүү керек экенин билишиңиз керек.