Що таке коефіцієнт кореляції в Excel. Приклад обчислення кореляції, побудови лінійної регресії та перевірки гіпотези залежності двох СВ нашим сервісом

У сьогоднішній статті йтиметься про те, як змінні можуть бути пов'язані один з одним. За допомогою кореляції ми зможемо визначити, чи існує зв'язок між першою та другою змінною. Сподіваюся, це заняття здасться вам не менш цікавим, ніж попередні!

Кореляція вимірює потужність та напрямок зв'язку між x та y. На малюнку представлені різні типи кореляції як графіків розсіювання упорядкованих пар (x, y). За традицією змінна х розміщається на горизонтальній осі, а y – на вертикальній.

Графік А є прикладом позитивної лінійної кореляції: при збільшенні х також збільшується у, причому лінійно. Графік В показує нам приклад негативної лінійної кореляції, на якому при збільшенні х лінійно зменшується. На графіку З бачимо відсутність кореляції між х і у. Ці змінні аж ніяк не впливають одна на одну.

Зрештою, графік D – це приклад нелінійних відносин між змінними. У міру збільшення x у спочатку зменшується, потім змінює напрямок і збільшується.

Решта статті присвячена лінійним взаємозв'язкам між залежною та незалежною змінними.

Коефіцієнт кореляції

Коефіцієнт кореляції, r, надає нам як силу, так і напрямок зв'язку між незалежною та залежною змінними. Значення r знаходяться в діапазоні між 1.0 і + 1.0. Коли r має позитивне значення, зв'язок між х і у є позитивним (графік A малюнку), а коли значення r негативно, зв'язок також негативна (графік У). Коефіцієнт кореляції, близький до нульового значення, свідчить про те, що між х та у зв'язку не існує графік С).

Сила зв'язку між х і у визначається близькістю коефіцієнта кореляції до - 1.0 або + - 1.0. Вивчіть наступний малюнок.

Графік A показує ідеальну позитивну кореляцію між х та у при r = + 1.0. Графік В - ідеальна негативна кореляція між х та у при r = - 1.0. Графіки З і D - приклади слабших зв'язків між залежною та незалежною змінними.

Коефіцієнт кореляції, r, визначає, як силу, так і напрямок зв'язку між залежною та незалежною змінними. Значення r знаходяться в діапазоні від - 1.0 (сильна негативний зв'язок) до + 1.0 (сильний позитивний зв'язок). При r= 0 між змінними х і немає ніякого зв'язку.

Ми можемо обчислити фактичний коефіцієнт кореляції за допомогою наступного рівняння:

Ну і ну! Я знаю, що це рівняння виглядає як страшне нагромадження незрозумілих символів, але перш ніж ударятися в паніку, давайте застосуємо до нього приклад з екзаменаційною оцінкою. Припустимо, я хочу визначити, чи існує зв'язок між кількістю годин, присвячених студентом вивченню статистики, та фінальною екзаменаційною оцінкою. Таблиця, наведена нижче, допоможе нам розбити це рівняння на кілька нескладних обчислень і зробити їх більш керованими.

Як бачите, між кількістю годин, присвячених вивченню предмета, та екзаменаційною оцінкою існує дуже сильна позитивна кореляція. Викладачі будуть дуже раді дізнатися про це.

Яка вигода встановлювати зв'язок між подібними змінними? Чудове питання. Якщо виявляється, що зв'язок існує, ми можемо передбачити екзаменаційні результати на основі певної кількості годин, присвячених вивченню предмета. Простіше кажучи, що сильніший зв'язок, то точнішим буде наше передбачення.

Використання Excel для обчислення коефіцієнтів кореляції

Я впевнений, що, глянувши на ці жахливі обчислення коефіцієнтів кореляції, ви відчуєте справжню радість, дізнавшись, що програма Excelможе виконати за вас всю цю роботу за допомогою функції КОРРЕЛ з наступними характеристиками:

КОРРЕЛ (масив 1; масив 2),

масив 1 = діапазон даних для першої змінної,

масив 2 = діапазон даних для другої змінної.

Наприклад, на малюнку показано функцію КОРРЕЛ, яка використовується при обчисленні коефіцієнта кореляції для прикладу з екзаменаційною оцінкою.

ЛАБОРАТОРНА РОБОТА

КОРЕЛЯЦІЙНИЙ АНАЛІЗ ВEXCEL

1.1 Кореляційний аналізу MS Excel

Кореляційний аналіз полягає у визначенні ступеня зв'язку між двома випадковими величинами X і Y. Як захід такого зв'язку використовується коефіцієнт кореляції. Коефіцієнт кореляції оцінюється за вибіркою обсягу п пов'язаних пар спостережень (x i , y i) із спільної генеральної сукупності X та Y. Для оцінки ступеня взаємозв'язку величин X та Y, виміряних у кількісних шкалах, використовується коефіцієнт лінійної кореляції(Коефіцієнт Пірсона), що передбачає, що вибірки X і Y розподілені за нормальним законом.

Коефіцієнт кореляції змінюється від -1 (сувора зворотна лінійна залежність) до 1 (сувора пряма пропорційна залежність). За значення 0 лінійної залежності між двома вибірками немає.

Загальна класифікація кореляційних зв'язків (за Івантером Е.В., Коросову А.В., 1992):

Існує кілька типів коефіцієнтів кореляції, що залежить від змінних Х і Y, які можуть бути виміряні у різних шкалах. Саме цей факт і визначає вибір відповідного коефіцієнта кореляції (див. табл. 13):

У MS Excel для обчислення парних коефіцієнтів лінійної кореляції використовується спеціальна функція Корел (масив1; масив2),

№ випробуваних

де масив1 – посилання діапазон осередків першої вибірки (X);

Приклад 1: 10 школярам було дано тести на наочно-образне та вербальне мислення. Вимірювалося середній час вирішення завдань тесту в секундах. Дослідника цікавить питання: чи існує взаємозв'язок між часом вирішення цих завдань? Змінна X - позначає середній час розв'язання наочно-подібних, а змінна Y - середній час розв'язання вербальних завдань тестів.

Р рішення:Для виявлення ступеня взаємозв'язку насамперед необхідно ввести дані в таблицю MS Excel (див. табл., рис. 1). Потім обчислюється значення коефіцієнта кореляції. Для цього курсор встановіть у комірку C1. На панелі інструментів натисніть кнопку Вставка функції (fx).

У діалоговому вікні Майстер функцій виберіть категорію Статистичніта функцію Корел, після чого натисніть кнопку ОК. Вказівником миші введіть діапазон даних вибірки Х у полі масив1 (А1: А10). У полі масив2 введіть діапазон даних вибірки (В1:В10). Натисніть кнопку ОК. У комірці С1 з'явиться значення коефіцієнта кореляції – 0,54119. Далі необхідно подивитися на абсолютну кількість коефіцієнта кореляції та визначити тип зв'язку (тісний, слабкий, середній і т.д.)

Мал. 1. Результати обчислення коефіцієнта кореляції

Таким чином, зв'язок між часом вирішення наочно-образних та вербальних завдань тесту не доведений.

Завдання 1.Є дані щодо 20 сільськогосподарських господарств. Знайти коефіцієнт кореляціїміж величинами врожайності зернових культур та якістю землі та оцінити його значимість. Дані наведені у таблиці.

Таблиця 2. Залежність урожайності зернових культур від якості землі

Номер господарства	Якість землі, бал	Врожайність, ц/га

Завдання 2.Визначте, чи є зв'язок між часом роботи спортивного тренажера для фітнесу (тис. годин) та вартість його ремонту (тис. руб.):

Час роботи тренажера (тис. годин)	Вартість ремонту (тис. руб.)

1.2 Множинна кореляція в MS Excel

При великому числіспостережень, коли коефіцієнти кореляції необхідно послідовно обчислювати для кількох вибірок, для зручності одержувані коефіцієнти зводять таблиці, звані кореляційними матрицями.

Кореляційна матриця- це квадратна таблиця, у якій перетині відповідних рядків і стовпців перебувають коефіцієнт кореляції між відповідними параметрами.

У MS Excel для обчислення кореляційних матриць використовується процедура Кореляціяз пакета Аналіз даних.Процедура дозволяє отримати кореляційну матрицю, яка містить коефіцієнти кореляції між різними параметрами.

Для реалізації процедури необхідно:

1. виконати команду Сервіс - Аналіз даних;

2. у списку Інструменти аналізувибрати рядок Кореляціята натиснути кнопку ОК;

3. у діалоговому вікні, що з'явилося. Вхідний інтервалтобто ввести посилання на комірки, що містять аналізовані дані. Вхідний інтервал повинен містити не менше двох стовпців.

4. у розділі Угрупованняперемикач встановити відповідно до введених даних (по стовпцям або рядкам);

5. вказати вихідний інтервалтобто ввести посилання на комірку, починаючи з якої будуть показані результати аналізу. Розмір вихідного діапазону буде визначено автоматично, і на екрані буде виведено повідомлення у разі можливого накладання вихідного діапазону на вихідні дані. Натиснути кнопку ОК.

У вихідний діапазон буде виведено кореляційну матрицю, в якій на перетині кожних рядки та стовпця знаходиться коефіцієнт кореляції між відповідними параметрами. Осередки вихідного діапазону, що мають збігаються координати рядків і стовпців, містять значення 1, оскільки кожен стовпець у вхідному діапазоні повністю корелює сам із собою

приклад 2.Є щомісячні дані спостережень за станом погоди та відвідуваністю музеїв та парків (див. табл. 3). Необхідно визначити, чи існує взаємозв'язок між станом погоди та відвідуваністю музеїв та парків.

Таблиця 3. Результати спостережень

Число ясних днів	Кількість відвідувачів музею	Кількість відвідувачів парку

Рішення. Для кореляційного аналізу введіть у діапазон A1:G3 вихідні дані (рис. 2). Потім у меню Сервісвиберіть пункт Аналіз данихі далі вкажіть рядок Кореляція. У діалоговому вікні вкажіть Вхідний інтервал(А2: С7). Вкажіть, що дані розглядаються стовпцями. Вкажіть вихідний діапазон (Е1) та натисніть кнопку ОК.

На рис. 33 видно, що кореляція між станом погоди та відвідуваністю музею дорівнює -0,92, а між станом погоди та відвідуваністю парку – 0,97, між відвідуваністю парку та музею – 0,92.

Таким чином, в результаті аналізу виявлено залежності: сильний ступінь зворотного лінійного взаємозв'язку між відвідуваністю музею та кількістю сонячних днів та практично лінійний (дуже сильний прямий) зв'язок між відвідуваністю парку та станом погоди. Між відвідуваністю музею та парку є сильний зворотний зв'язок.

Мал. 2. Результати обчислення кореляційної матриці прикладу 2

Завдання 3. 10 менеджерів оцінювалися за методикою експертних оцінок психологічних характеристик особистості керівника. 15 експертів проводили оцінку кожної психологічної характеристики за п'ятибальною системою (див. табл. 4). Психолога цікавить питання, у якому взаємозв'язку перебувають ці характеристики керівника між собою.

Таблиця 4. Результати дослідження

Піддослідні п/п	тактовність	вимогливість	критичність

Кореляційний аналіз – популярний метод статистичного дослідження, який використовується виявлення ступеня залежності одного показника від іншого. У Microsoft Excelє спеціальний інструментпризначений для виконання цього типу аналізу. Давайте з'ясуємо, як користуватися цією функцією.

Суть кореляційного аналізу

Призначення кореляційного аналізу зводиться до виявлення наявності залежності між різними факторами. Тобто визначається, чи впливає зменшення або збільшення одного показника на зміну іншого.

Якщо залежність встановлена, визначається коефіцієнт кореляції. На відміну від регресійного аналізу, це єдиний показник, який розраховує цей метод статистичного дослідження. Коефіцієнт кореляції варіюється в діапазоні від +1 до -1. За наявності позитивної кореляції збільшення одного показника сприяє збільшенню другого. При негативної кореляції збільшення одного показника спричиняє зменшення іншого. Чим більший модуль коефіцієнта кореляції, тим помітніша зміна одного показника відбивається на зміні другого. При коефіцієнті 0 залежність між ними відсутня повністю.

Розрахунок коефіцієнта кореляції

Тепер давайте спробуємо порахувати коефіцієнт кореляції на конкретному прикладі. Маємо таблицю, у якій помісячно розписано окремих колонках витрата рекламу і величина продажів. Нам належить з'ясувати рівень залежності кількості продажів від суми грошових коштівяка була витрачена на рекламу.

Спосіб 1: визначення кореляції через Майстер функцій

Одним із способів, за допомогою якого можна провести кореляційний аналіз, є використання функції Корел. Сама функція має загальний виглядКорел (масив1; масив2).

1. Виділяємо комірку, в якій має виводитися результат розрахунку. Клацаємо по кнопці «Вставити функцію», яка розміщується ліворуч від рядка формул.
2. У списку, який представлений у вікні Майстра функцій, шукаємо та виділяємо функцію КОРРЕЛ. Тиснемо на кнопку «OK».
3. Відкриється вікно аргументів функції. У полі «Масив1» вводимо координати діапазону осередків одного із значень, залежність якого слід визначити. У нашому випадку це будуть значення у колонці «Величина продажу». Для того, щоб внести адресу масиву в поле, просто виділяємо всі осередки з даними у вказаному вище стовпці.

   У полі Масив2 потрібно внести координати другого стовпця. У нас це витрати на рекламу. Так само, як і в попередньому випадку, заносимо дані в поле.

   Тиснемо на кнопку «OK».

Як бачимо, коефіцієнт кореляції у вигляді числа з'являється в заздалегідь вибраному комірці. У даному випадкувін дорівнює 0,97, що є дуже високою ознакою залежності однієї величини від іншої.

Спосіб 2: обчислення кореляції за допомогою пакета аналізу

Крім того, кореляцію можна обчислити за допомогою одного з інструментів, представленого в пакеті аналізу. Але насамперед нам потрібно цей інструмент активувати.

1. Переходимо у вкладку "Файл".
2. У вікні переміщуємося в розділ «Параметри».
3. Далі переходимо до пункту «Надбудови».
4. В нижній частині наступного вікнау розділі "Управління" переставляємо перемикач у позицію "Надбудови Excel", якщо він знаходиться в іншому положенні. Тиснемо на кнопку «OK».
5. У вікні надбудов встановлюємо галочку біля пункту «Пакет аналізу». Тиснемо на кнопку «OK».
6. Після цього пакет аналізу активовано. Переходимо у вкладку «Дані». Як бачимо, тут на стрічці з'являється новий блок інструментів – «Аналіз». Тиснемо на кнопку «Аналіз даних», яка розташована в ньому.
7. Відкривається список з різними варіантамианалізу даних. Вибираємо пункт "Кореляція". Клацаємо по кнопці «OK».
8. Відкривається вікно із параметрами кореляційного аналізу. На відміну від попереднього способу, у полі "Вхідний інтервал" ми вводимо інтервал не кожного стовпця окремо, а всіх стовпців, які беруть участь у аналізі. У нашому випадку це дані у стовпцях «Витрати на рекламу» та «Величина продажу».

   Параметр «Групування» залишаємо без змін – «Стовпцями», тому що у нас групи даних розбиті саме на два стовпці. Якби вони були розбиті рядково, тоді слід було б переставити перемикач у позицію «По рядках».

   У параметрах виводу за замовчуванням встановлено пункт «Новий робочий лист», тобто дані виводитимуться на іншому аркуші. Можна змінити місце, переставивши перемикач. Це може бути поточний лист (тоді ви повинні вказати координати осередків виведення інформації) або нова робоча книга (файл).

   Коли всі налаштування встановлені, натискаємо на кнопку «OK».

Оскільки місце виведення результатів аналізу залишили за замовчуванням, ми переміщуємося на новий лист. Як бачимо, тут вказано коефіцієнт кореляції. Звичайно, він той самий, що і при використанні першого методу - 0,97. Це тим, що обидва варіанти виконують одні й самі обчислення, просто зробити їх можна різними способами.

Як бачимо, програма Ексель пропонує відразу два способи кореляційного аналізу. Результат обчислень, якщо ви все зробите правильно, буде цілком ідентичним. Але, кожен користувач може вибрати зручніший для нього варіант здійснення розрахунку.

Ми раді, що змогли допомогти Вам у вирішенні проблеми.

Задайте своє питання у коментарях, детально розписавши суть проблеми. Наші фахівці намагатимуться відповісти максимально швидко.

Чи допомогла вам ця стаття?

Регресійний та кореляційний аналіз – статистичні методидослідження. Це найпоширеніші способи показати залежність будь-якого параметра від однієї чи кількох незалежних змінних.

Нижче на конкретних практичні прикладирозглянемо ці два дуже популярні серед економістів аналізу. А також наведемо приклад отримання результатів при їх об'єднанні.

Регресійний аналіз у Excel

Показує вплив одних значень (самостійних, незалежних) на залежну змінну. Наприклад, як залежить кількість економічно активного населення кількості підприємств, величини зарплати та інших. властивостей. Або як впливають іноземні інвестиції, ціни на енергоресурси та ін на рівень ВВП.

Результат аналізу дає змогу виділяти пріоритети. І ґрунтуючись на головних факторах, прогнозувати, планувати розвиток пріоритетних напрямків, ухвалювати управлінські рішення.

Регресія буває:

• лінійної (у = а + bx);
• параболічній (y = a + bx + cx2);
• експоненційною (y = a * exp (bx));
• статечної (y = a * x ^ b);
• гіперболічної (y = b/x + a);
• логарифмічної (y = b * 1n(x) + a);
• показовою (y = a * b^x).

Розглянемо з прикладу побудова регресійної моделі в Excel і інтерпретацію результатів. Візьмемо лінійний тип регресії.

Завдання. На 6 підприємствах було проаналізовано середньомісячну заробітня платаі кількість співробітників, що звільнилися. Необхідно визначити залежність кількості співробітників, що звільнилися, від середньої зарплати.

Модель лінійної регресіїмає такий вигляд:

У = а0 + а1х1 + ... + Акхк.

Де а – коефіцієнти регресії, х – що впливають змінні, до – число чинників.

У нашому прикладі як У виступає показник працівників, що звільнилися. фактор, що впливає - заробітна плата (х).

У Excel існують інтегровані функції, з допомогою яких можна розрахувати параметри моделі лінійної регресії. Але найшвидше це зробить надбудова «Пакет аналізу».

Активуємо потужний аналітичний інструмент:

1. Натискаємо кнопку «Офіс» та переходимо на вкладку «Параметри Excel». "Надбудови".
2. Внизу, під випадаючим списком, у полі «Управління» буде напис «Надбудови Excel» (якщо його немає, натисніть прапорець праворуч і виберіть). І кнопка "Перейти". Тиснемо.
3. Відкривається список доступних надбудов. Вибираємо «Пакет аналізу» та натискаємо ОК.

Після активації надбудова буде доступна на вкладці "Дані".

Тепер візьмемося безпосередньо регресійним аналізом.

1. Відкриваємо меню інструмента «Аналіз даних». Вибираємо "Регресія".
2. Відкриється меню для вибору вхідних значень та параметрів виводу (де відобразити результат). У полях для вихідних даних вказуємо діапазон описуваного параметра (У) і фактора (Х), що впливає на нього. Решту можна не заповнювати.
3. Після натискання ОК програма відобразить розрахунки на новому аркуші (можна вибрати інтервал для відображення на поточному аркуші або призначити виведення в нову книгу).

Насамперед звертаємо увагу на R-квадрат та коефіцієнти.

R-квадрат – коефіцієнт детермінації. У прикладі – 0,755, чи 75,5%. Це означає, що розрахункові параметримоделі на 75,5% пояснюють залежність між параметрами, що вивчаються. Що коефіцієнт детермінації, то якісніша модель. Добре – понад 0,8. Погано – менше 0,5 (такий аналіз навряд можна вважати резонним). У нашому прикладі - "непогано".

Коефіцієнт 64,1428 показує, яким буде Y, якщо всі змінні в моделі, що розглядається, будуть рівні 0. Тобто на значення аналізованого параметра впливають і інші фактори, не описані в моделі.

p align="justify"> Коефіцієнт -0,16285 показує вагомість змінної Х на Y. Тобто середньомісячна заробітна плата в межах даної моделі впливає на кількість звільнених з вагою -0,16285 (це невеликий ступінь впливу). Знак «-» вказує на негативний вплив: чим більша зарплата, тим менше звільнених Що слушно.

Кореляційний аналіз у Excel

Кореляційний аналіз допомагає встановити, чи між показниками в одній або двох вибірках є зв'язок. Наприклад, між часом роботи верстата та вартістю ремонту, ціною техніки та тривалістю експлуатації, зростанням та вагою дітей тощо.

Якщо зв'язок є, то чи тягне збільшення одного параметра підвищення (позитивна кореляція) чи зменшення (негативна) іншого. Кореляційний аналіз допомагає аналітику визначитися, чи можна за величиною одного показника передбачити можливе значення іншого.

Коефіцієнт кореляції позначається r. Варіюється в межах від +1 до -1. Класифікація кореляційних зв'язків для різних сфербуде відрізнятись. При значенні коефіцієнта 0 лінійної залежності між вибірками немає.

Розглянемо як за допомогою засобів Excelвизначити коефіцієнт кореляції.

Для знаходження парних коефіцієнтів застосовується функція Корел.

Завдання: Визначити, чи є взаємозв'язок між часом роботи токарного верстатата вартістю його обслуговування.

Ставимо курсор у будь-яку комірку і натискаємо кнопку fx.

1. У категорії «Статистичні» вибираємо функцію КОРРЕЛ.
2. Аргумент "Масив 1" - перший діапазон значень - час роботи верстата: А2: А14.
3. Аргумент "Масив 2" - другий діапазон значень - вартість ремонту: В2: В14. Тиснемо ОК.

Щоб визначити тип зв'язку, потрібно подивитися абсолютну кількість коефіцієнта (для кожної сфери діяльності є своя шкала).

Для кореляційного аналізу кількох параметрів (більше 2) зручніше застосовувати "Аналіз даних" (надбудова "Пакет аналізу"). У списку потрібно вибрати кореляцію та позначити масив. Всі.

Отримані коефіцієнти відобразяться у кореляційній матриці. На кшталт такий:

Кореляційно-регресійний аналіз

Насправді ці дві методики часто застосовуються разом.

1. Будуємо кореляційне поле: "Вставка" - "Діаграма" - "Точкова діаграма" (дає порівнювати пари). Діапазон значень – усі числові дані таблиці.
2. Клацаємо лівою кнопкою миші по будь-якій точці на діаграмі. Потім правою. У меню вибираємо «Додати лінію тренда».
3. Призначаємо параметри лінії. Тип - "Лінійна". Внизу – "Показати рівняння на діаграмі".
4. Тиснемо «Закрити».

Тепер стали помітні й дані регресійного аналізу.

1.Відкрити програму Excel

2.Створити стовпці з даними. У прикладі ми вважатимемо взаємозв'язок, чи кореляцію, між агресивністю і невпевненістю у собі в дітей-першокласників. В експерименті брали участь 30 дітей, дані представлені в таблиці ексель:

1 стовпчик - № випробуваного

2 стовпчик - агресивність у балах

3 стовпчик - невпевненість у собі в балах

3. Потім необхідно вибрати порожню комірку поряд з таблицею і натиснути на значок f(x)у панелі Excel

4. Відкриється меню функцій, серед категорій необхідно вибрати Статистичні, а потім серед списку функцій за абеткою знайти Корелта натиснути ОК

5. Потім відкриється меню аргументів функції, яке дозволить вибрати потрібні нам стовпчики з даними. Для вибору першого стовпчика Агресивністьпотрібно натиснути на синю кнопочку біля рядка Масив1

6. Виберемо дані для Масиву1зі стовпчика Агресивністьі натиснемо на синю кнопку в діалоговому вікні

7. Потім аналогічно Масиву 1 натиснемо на синю кнопочку біля рядка Масив2

8. Виберемо дані для Масиву2- стовпчик Невпевненість в собіі знову натиснемо синю кнопку, потім ОК

9.От, коефіцієнт кореляції r-Пірсона порахований і записаний у вибраній комірці. У нашому випадку він позитивний і приблизно дорівнює. Це говорить про помірною позитивноюзв'язки між агресивністю та невпевненістю у собі у дітей-першокласників

Таким чином, статистичним висновкомексперименту буде: r = 0,225, виявлено помірний позитивний взаємозв'язок між змінними агресивністьі невпевненість в собі.

У деяких дослідженнях потрібно вказувати рівень значущості коефіцієнта кореляції, проте програма Excel, на відміну від SPSS, не надає такої можливості. Нічого страшного, є таблиці критичних значень кореляцій (А.Д. Спадкоємців).

Також Ви можете побудувати в ексель лінію регресії та докласти її до результатів дослідження.

Обчислимо коефіцієнт кореляції та коваріацію для різних типіввзаємозв'язків випадкових величин

Коефіцієнт кореляції(критерій кореляції Пірсона, анг. Pearson Product Moment correlation coefficient)визначає ступінь лінійноївзаємозв'язку між випадковими величинами

Як випливає з визначення, для обчислення коефіцієнта кореляціїпотрібно знати розподіл випадкових величин Х та Y. Якщо розподіли невідомі, то для оцінки коефіцієнта кореляціївикористовується вибірковий коефіцієнткореляціїr (ще він позначається як R xy або r xy) :

де S x - стандартне відхиленнявибірки випадкової величиних, що обчислюється за формулою:

Як видно з формули для розрахунку кореляції, знаменник (твір стандартних відхилень) просто нормує чисельник таким чином, що кореляціявиявляється безрозмірним числом від –1 до 1. Кореляціяі коваріаціянадають одну і ту ж інформацію (якщо відомі стандартні відхилення ), але кореляцієюзручніше скористатися, т.к. вона є безрозмірною величиною.

Розрахувати коефіцієнт кореляціїі підступність вибіркив MS EXCEL не становить труднощів, тому що для цього є спеціальні функції КОРРЕЛ() і КОВАР(). Набагато складніше розібратися, як інтерпретувати набуті значення, більшість статті присвячена саме цьому.

Теоретичний відступ

Нагадаємо, що кореляційним зв'язкомназивають статистичний зв'язок, який полягає в тому, що різним значеннямоднієї змінної відповідають різні середнізначення інший (зі зміною значення Х середнє значення Y змінюється закономірним чином. Передбачається, що обидвізмінні Х і Y є випадковимивеличинами і мають якийсь випадковий розкид щодо них середнього значення.

Примітка. Якщо випадкову природу має лише одна змінна, наприклад, Y, а значення інший є детермінованими (задаються дослідником), можна говорити лише регресії.

Таким чином, наприклад, при дослідженні залежності середньорічної температури не можна говорити про кореляціїтемператури та року спостереження та, відповідно, застосовувати показники кореляціїз відповідною їхньою інтерпретацією.

Кореляційний зв'язокміж змінними може виникнути кількома шляхами:

1. Наявність причинної залежності між змінними. Наприклад, кількість інвестицій у наукові дослідження (змінна Х) та кількість отриманих патентів (Y). Перша змінна виступає як незалежна змінна (фактор), друга - залежна змінна (результат). Необхідно пам'ятати, що залежність величин обумовлює наявність кореляційного зв'язку між ними, але не навпаки.
2. Наявність сполученості (загальної причини). Наприклад, зі зростанням організації зростає фонд оплати праці (ФОП) та витрати на оренду приміщень. Очевидно, що неправильно припускати, що оренда приміщень залежить від ФОП. Обидві цих змінних у часто лінійно залежать від кількості персоналу.
3. Взаємовплив змінних (при зміні однієї, друга змінна змінюється, і навпаки). За такого підходу допустимі дві постановки завдання; Будь-яка змінна може бути як у ролі незалежної змінної й у ролі залежної.

Таким чином, показник кореляціїпоказує, наскільки сильна лінійний взаємозв'язокміж двома факторами (якщо вона є), а регресія дає змогу прогнозувати один фактор на основі іншого.

Кореляція, як і будь-який інший статистичний показник, при правильному застосуванніможе бути корисною, але вона також має обмеження щодо використання. Якщо показує чітко виражену лінійну залежністьабо повна відсутність взаємозв'язку, то кореляціячудово це відобразить. Але якщо дані показують нелінійний взаємозв'язок (наприклад, квадратичний), наявність окремих груп значень або викидів, то обчислене значення коефіцієнта кореляціїможе ввести в оману (див. файл прикладу).

Кореляціяблизька до 1 або -1 (тобто близька за модулем до 1) показує сильний лінійний взаємозв'язок змінних, значення близьке до 0 показує відсутність взаємозв'язку. Позитивна кореляціяозначає, що зі зростанням одного показника інший у середньому збільшується, а при негативному – зменшується.

Для обчислення коефіцієнта кореляції потрібно, щоб змінні, що зіставляються, задовольняли наступним умовам:

• кількість змінних має дорівнювати двом;
• змінні мають бути кількісними (наприклад, частота, вага, ціна). Обчислене середнє значення цих змінних має зрозуміле значення: Середня цінаабо Середня вагапацієнта. На відміну від кількісних, якісні (номінальні) змінні набувають значення лише з кінцевого набору категорій (наприклад, стать або група крові). Цим значенням умовно зіставлені числові значення (наприклад, жіноча стать – 1, а чоловіча – 2). Зрозуміло, що в цьому випадку обчислення середнього значення, яка потрібна для знаходження кореляції, некоректно, а значить некоректно і обчислення самої кореляції;
• змінні повинні бути випадковими величинами та мати .

Двовимірні дані можуть мати різну структуру. Для роботи з деякими з них потрібні певні підходи:

• Для даних з нелінійним зв'язком кореляціюпотрібно використовувати з обережністю. Для деяких завдань буває корисно перетворити одну або обидві змінні так, щоб отримати лінійний взаємозв'язок (для цього потрібно зробити припущення про вид нелінійного зв'язку, щоб запропонувати потрібний тип перетворення).
• За допомогою діаграми розсіюванняу деяких даних можна спостерігати нерівну варіацію (розкид). Проблема різної варіації полягає в тому, що місця з високою варіацією не лише надають найменш точну інформацію, але й надають найбільший впливпри розрахунку статистичних показників. Цю проблему часто вирішують за допомогою перетворення даних, наприклад, за допомогою логарифмування.
• У деяких даних можна спостерігати поділ на групи (clustering), що може свідчити необхідність поділу сукупності на частини.
• Викид (різно відхиляється) може спотворити обчислене значення коефіцієнта кореляції. Викид може бути причиною випадковості, помилки при зборі даних або можуть справді відображати певну особливість взаємозв'язку. Так як викид сильно відхиляється від середнього значення, то він робить великий внесок при розрахунку показника. Часто розрахунок статистичних показників виробляють з і без урахування викидів.

Використання MS EXCEL для розрахунку кореляції

Як приклад візьмемо 2 змінні Хі Yі відповідно, вибіркущо складається з кількох пар значень (Х i ; Y i). Для наочності побудуємо.

Примітка: Докладніше про побудову діаграм див. статтю. У файлі прикладу для побудови діаграми розсіюваннявикористана, т.к. ми тут відступили від вимоги випадковості змінної Х (це полегшує генерацію різних типіввзаємозв'язків: побудова трендів та заданий розкид). У разі реальних даних необхідно використовувати діаграму типу Точкова (див. нижче).

Розрахунки кореляціїпроведемо для різних випадківвзаємозв'язку між змінними: лінійної, квадратичноїі при відсутність зв'язку.

Примітка: У файлі прикладу можна задати параметри лінійного тренду (нахил, перетин з віссю Y) і ступінь розкиду щодо цієї лінії тренду. Також можна налаштувати параметри квадратичної залежності.

У файлі прикладу для побудови діаграми розсіюванняу разі відсутності залежності змінних використано діаграму типу Точкова. І тут точки на діаграмі розташовуються як хмари.

Примітка: Зверніть увагу, що змінюючи масштаб діаграми по вертикальній або горизонтальній осі, хмарі точок можна надати вигляду вертикальної або горизонтальній лінії. Зрозуміло, що змінні залишаться незалежними.

Як було сказано вище, для розрахунку коефіцієнта кореляціїв MS EXCEL існує функцій Корел (). Також можна скористатися аналогічною функцією PEARSON(), яка повертає той самий результат.

Для того, щоб переконатися, що обчислення кореляціївиробляються функцією КОРРЕЛ() за вищезгаданими формулами, у файлі прикладу наведено обчислення кореляціїза допомогою більш докладних формул:

=КОВАРІАЦІЯ.Г(B28:B88;D28:D88)/СТАНДОТКЛОН.Г(B28:B88)/СТАНДОТКЛОН.Г(D28:D88)

=КОВАРІАЦІЯ.В(B28:B88;D28:D88)/СТАНДОТКЛОН.В(B28:B88)/СТАНДОТКЛОН.В(D28:D88)

Примітка: Квадрат. коефіцієнта кореляції r дорівнює коефіцієнт детермінації R2, який обчислюється при побудові лінії регресії за допомогою функції КВПІРСОН(). Значення R2 також можна вивести на діаграмі розсіювання, побудувавши лінійний трендза допомогою стандартного функціоналу MS EXCEL (виділіть діаграму, виберіть вкладку Макет, потім у групі Аналізнатисніть кнопку Лінія трендута виберіть Лінійне наближення). Докладніше про побудову лінії тренду див., наприклад, в .

Використання MS EXCEL для розрахунку коваріації

Коваріаціяблизька за змістом з (також є мірою розкиду) з тією відмінністю, що вона визначена для 2-х змінних, а дисперсія- Для однієї. Тому cov(x;x)=VAR(x).

Для обчислення коваріації в MS EXCEL (починаючи з версії 2010 року) використовуються функції КОВАРІАЦІЯ.Г() та КОВАРІАЦІЯ.В(). У першому випадку формула для обчислення аналогічна вищезазначеній (закінчення .Гпозначає Генеральна сукупність ), у другому – замість множника 1/n використовується 1/(n-1), тобто. закінчення .позначає Вибірка.

Примітка: Функція КОВАР(), яка присутня в MS EXCEL більш ранніх версій, аналогічна функції КОВАРІАЦІЯ.Г().

Примітка: Функції КОРРЕЛ() та КОВАР() в англійській версії представлені як CORREL та COVAR. Функції КОВАРІАЦІЯ.Г() та КОВАРІАЦІЯ.В() як COVARIANCE.P та COVARIANCE.S.

Додаткові формули для розрахунку підступи:

=СУМПРОВИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88-СРЗНАЧ(D28:D88)))/РАХУНОК(D28:D88)

=СУМПРОВИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88))/РАХУНОК(D28:D88)

=СУМПРОВИЗВ(B28:B88;D28:D88)/РАХУНОК(D28:D88)-СРЗНАЧ(B28:B88)*СРЗНАЧ(D28:D88)

Ці формули використовують властивість підступи:

Якщо змінні xі yнезалежні, їх коваріація дорівнює 0. Якщо змінні є незалежними, то дисперсія їх суми дорівнює:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсіяїх різниці дорівнює

VAR(x-y) = VAR(x) + VAR(y)-2COV(x;y)

Оцінка статистичної значущості коефіцієнта кореляції

Щоб перевірити гіпотезу, ми повинні знати розподіл випадкової величини, тобто. коефіцієнта кореляції r. Зазвичай перевірку гіпотези здійснюють не для r, а для випадкової величини t r:

яка має з n-2 ступенями свободи.

Якщо обчислене значення випадкової величини | t r | більше, ніж критичне значення t α,n-2 (α-заданий ), то нульову гіпотезу відхиляють (взаємозв'язок величин є статистично значущою).

Надбудова Пакет аналізу

Для обчислення коваріації та кореляції є однойменні інструменти аналізу.

Після виклику інструмента з'являється діалогове вікно, яке містить такі поля:

• Вхідний інтервал: потрібно ввести посилання на діапазон з вихідними даними для 2-х змінних
• Групування: як правило, вихідні дані вводяться в 2 стовпці
• Мітки у першому рядку: якщо встановлена ​​галочка, то Вхідний інтервалповинен містити заголовки стовпців. Рекомендується встановлювати галочку, щоб результат роботи Надбудови містив інформативні стовпці
• Вихідний інтервал: діапазон осередків, куди будуть розміщені результати обчислень. Достатньо вказати ліву верхню комірку цього діапазону.

Надбудова повертає обчислені значення кореляції та коваріації (для коваріації також обчислюються дисперсії обох випадкових величин).

У наукових дослідженняхчасто виникає необхідність у знаходженні зв'язку між результативними та факторними змінними (урожайністю будь-якої культури та кількістю опадів, зростанням та вагою людини в однорідних групах за статтю та віком, частотою пульсу та температурою тіла тощо).

Другі є ознаки, сприяють зміні таких, що з ними (першими).

Поняття про кореляційний аналіз

Існує безліч Виходячи з вищевикладеного, можна сказати, що кореляційний аналіз - це метод, що застосовується з метою перевірки гіпотези про статистичної значимостідвох і більше змінних, якщо дослідник може вимірювати, але з змінювати.

Є й інші визначення поняття, що розглядається. Кореляційний аналіз - це метод обробки полягає у вивченні коефіцієнтів кореляції між змінними. При цьому порівнюються коефіцієнти кореляції між однією парою або безліччю пар ознак для встановлення між ними статистичних взаємозв'язків. Кореляційний аналіз - це метод вивчення статистичної залежності між випадковими величинами з необов'язковою наявністю суворого функціонального характеру, при якій динаміка однієї випадкової величини призводить до динаміки математичного очікуванняінший.

Поняття про хибність кореляції

При проведенні кореляційного аналізу необхідно враховувати, що його можна провести по відношенню до будь-якої сукупності ознак, часто абсурдних один до одного. Часом вони не мають жодної причинного зв'язкуодин з одним.

У цьому випадку говорять про хибну кореляцію.

Завдання кореляційного аналізу

Виходячи з наведених вище визначень, можна сформулювати такі завдання описуваного методу: отримати інформацію про одну з змінних, що шукаються, за допомогою іншої; визначити тісноту зв'язку між досліджуваними змінними.

Кореляційний аналіз передбачає визначення залежності між ознаками, що досліджуються, у зв'язку з чим завдання кореляційного аналізу можна доповнити такими:

• виявлення факторів, що мають найбільший вплив на результативну ознаку;
• виявлення невивчених раніше причин зв'язків;
• побудова кореляційної моделі з її параметричним аналізом;
• вивчення значимості властивостей зв'язку та його інтервальна оцінка.

Зв'язок кореляційного аналізу з регресійним

Метод кореляційного аналізу часто не обмежується знаходженням тісноти зв'язку між досліджуваними величинами. Іноді він доповнюється складанням рівнянь регресії, які отримують за допомогою однойменного аналізу, і є описом кореляційної залежності між результуючим і факторним (факторними) ознакою (ознаками). Цей метод разом із аналізованим аналізом становить метод

Умови використання методу

Результативні чинники залежить від однієї до кількох чинників. Метод кореляційного аналізу може застосовуватись у тому випадку, якщо є велика кількістьспостережень про величину результативних та факторних показників (факторів), при цьому досліджувані фактори повинні бути кількісними та відображатись у конкретних джерелах. Перше може визначатися нормальним законом - у цьому випадку результатом кореляційного аналізу виступають коефіцієнти кореляції Пірсона, або, якщо ознаки не підкоряються цьому закону, використовується коефіцієнт рангової кореляціїСпірмена.

Правила відбору факторів кореляційного аналізу

При застосуванні даного методунеобхідно визначитися з факторами, які впливають на результативні показники. Їх відбирають з урахуванням того, що між показниками мають бути причинно-наслідкові зв'язки. У разі створення багатофакторної кореляційної моделі відбирають ті з них, які істотно впливають на результуючий показник. чи функціональний характер.

Відображення результатів

Результати кореляційного аналізу можуть бути представлені у текстовому та графічному видах. У першому випадку вони видаються як коефіцієнт кореляції, у другому - у вигляді діаграми розкиду.

За відсутності кореляції між параметрами точки на діаграмі розташовані хаотично, середній ступінь зв'язку характеризується більшим ступенем упорядкованості та характеризується більш-менш рівномірною віддаленістю нанесених позначок медіани. Сильна зв'язок прагне прямий і за r=1 точковий графік є рівною лінію. Зворотна кореляція відрізняється спрямованістю графіка з лівого верхнього в правий нижній, пряма — з нижнього лівого у верхній правий кут.

Тривимірне уявлення діаграми розкиду (розсіювання)

Крім традиційного 2D-подання діаграми розкиду, в даний час використовується 3D-відображення графічного представлення кореляційного аналізу.

Також використовується матриця діаграми розсіювання, яка відображає всі парні графіки на одному малюнку в матричному форматі. Для n змінних матриця містить n рядків та n стовпців. Діаграма, розташована на перетині i-го рядка і j-ого стовпця, є графік змінних Xi в порівнянні з Xj. Таким чином, кожен рядок і стовпець є одним виміром, окремий осередок відображає діаграму розсіювання двох вимірів.

Оцінка тісноти зв'язку

Тіснота кореляційного зв'язку визначається за коефіцієнтом кореляції (r): сильна – r = ±0,7 до ±1, середня – r = ±0,3 до ±0,699, слабка – r = 0 до ±0,299. Ця класифікація перестав бути суворої. На малюнку показано дещо іншу схему.

Приклад застосування методу кореляційного аналізу

У Великій Британії було зроблено цікаве дослідження. Воно присвячене зв'язку куріння з раком легенів, та проводилося шляхом кореляційного аналізу. Це спостереження наведено нижче.

Вихідні дані для кореляційного аналізу

Професійна група		смертність
Фермери, лісники та рибалки
Шахтарі та працівники кар'єрів
Виробники газу, коксу та хімічних речовин
Виробники скла та кераміки
Працівники печей, ковальських, ливарних та прокатних станів
Працівники електротехніки та електроніки
Інженерні та суміжні професії
Деревообробні виробництва
Кожувенники
Текстильні робітники
Виробники робочого одягу
Працівники харчової, питної та тютюнової промисловості
Виробники паперу та друку
Виробники інших продуктів
Будівельники
Художники та декоратори
Водії стаціонарних двигунів, кранів тощо.
Робочі, не включені до інших місць
Працівники транспорту та зв'язку
Складські робітники, комірники, пакувальники та працівники розливальних машин
Канцелярські працівники
Продавці
Працівники служби спорту та відпочинку
Адміністратори та менеджери
Професіонали, технічні працівники та художники

Розпочинаємо кореляційний аналіз. Рішення краще починати для наочності з графічного методу, Навіщо побудуємо діаграму розсіювання (розкиду).

Вона показує прямий зв'язок. Однак на підставі лише графічного методу зробити однозначний висновок складно. Тому продовжимо виконувати кореляційний аналіз. Приклад розрахунку коефіцієнта кореляції наведено нижче.

За допомогою програмних засобів (з прикладу MS Excel буде описано далі) визначаємо коефіцієнт кореляції, який становить 0,716, що означає сильний зв'язок між досліджуваними параметрами. Визначимо статистичну достовірність отриманого значення за відповідною таблицею, для чого нам потрібно відняти з 25 пар значень 2, в результаті чого отримаємо 23 і по цьому рядку в таблиці знайдемо r критичне для p=0,01 (оскільки це медичні дані, тут використовується більш строга залежність, в решті випадків достатньо p=0,05), яке становить 0,51 для цього кореляційного аналізу. Приклад продемонстрував, що розрахункове більше r критичного, значення коефіцієнта кореляції вважається статистично достовірним.

Використання ПЗ під час проведення кореляційного аналізу

Описуваний вид статистичної обробки даних може здійснюватися за допомогою програмного забезпеченнязокрема, MS Excel. Кореляційний передбачає обчислення наступних параметрів з використанням функцій:

1. Коефіцієнт кореляції визначається за допомогою функції КОРРЕЛ (масив1; масив2). Масив1,2 - осередок інтервалу значень результативних і факторних змінних.

Лінійний коефіцієнт кореляції також називається коефіцієнтом кореляції Пірсона, у зв'язку з чим, починаючи з Excel 2007 можна використовувати функцію з тими ж масивами.

Графічне відображення кореляційного аналізу в Excel здійснюється за допомогою панелі "Діаграми" з вибором "Точкова діаграма".

Після вказівки вихідних даних отримуємо графік.

2. Оцінка значимості коефіцієнта парної кореляції з допомогою t-критерію Стьюдента. Розраховане значення t-критерію порівнюється з табличною (критичною) величиною даного показника з відповідної таблиці значень аналізованого параметра з урахуванням заданого рівня значущості та числа ступенів свободи. Ця оцінка здійснюється з використанням функції СТЬЮДРАСПОБР (імовірність; ступеня_свободи).

3. Матриця коефіцієнтів парної кореляції. Аналіз здійснюється за допомогою засобу "Аналіз даних", в якому вибирається "Кореляція". Статистичну оцінку коефіцієнтів парної кореляції здійснюють при порівнянні його абсолютної величиниз табличним (критичним) значенням. При перевищенні розрахункового коефіцієнта парної кореляції над таким критичним можна говорити, з урахуванням заданого ступеня ймовірності, що нульова гіпотеза про значимість лінійного зв'язку не відкидається.

На закінчення

Використання в наукових дослідженнях методу кореляційного аналізу дозволяє визначити зв'язок між різними факторами та результативними показниками. При цьому необхідно враховувати, що високий коефіцієнт кореляції можна отримати і з абсурдної пари чи безлічі даних, у зв'язку з чим даний виданалізу слід здійснювати на досить великому масиві даних.

Після отримання розрахункового значення r бажано порівняти з r критичним для підтвердження статистичної достовірностіпевної величини. Кореляційний аналіз може здійснюватися вручну з використанням формул або за допомогою програмних засобів, зокрема MS Excel. Тут же можна побудувати діаграму розкиду (розсіювання) з метою наочного уявлення про зв'язок між факторами кореляційного аналізу, що вивчаються, і результативною ознакою.