Кореляційний аналіз Пірсон Excel. Кореляційний аналіз як зробити в excel

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

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

Ця стаття розрахована на сучасний рівень володіння Microsoft Excel. Якщо у вас немає часу, щоб читати всю статтю, ви можете завантажити файл і розібратися з ним самостійно.

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

Що важливо знати про кореляції

Щоб розрахувати достовірну кореляцію, необхідно мати достовірну вибірку, що більше вона буде, то достовірнішим буде результат. Для цілей цього прикладу я взяв щоденну вибірку курсів валют за 10 років. Дані є у вільному доступі, я їх брав із сайту http://oanda.com.

Що я, власне, зробив

(1) Коли я мав вихідні дані, я почав з того, що перевірив ступінь кореляції цих двох наборів даних. Для цього я скористався функцією CORREL (КОРРЕЛ) - про неї є небагато інформації. Вона повертає рівень кореляції двох діапазонів даних. Результат, прямо скажемо, вийшов не дуже вражаючим (всього близько 70%). А взагалі, ступінь співвідношення двох величин прийнято вважати, як квадрат цієї величини, тобто кореляція вийшла достовірною приблизно на 49%. Це дуже мало!

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

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

(4) За кольором видно, що ці дані відносяться до 2007, 2008, 2009 років. Звісно! p align="justify"> Періоди економічних піків і спадів зазвичай недостовірні статистично, що і сталося в даному випадку. Тому я спробував виключити з даних ці періоди (і для перевірки, я перевірив ступінь кореляції даних у цьому періоді). Ступінь кореляції цих даних становить 0.01%, тобто вона відсутня в принципі. Проте без них дані корелюють приблизно на 81%. Це вже достовірна кореляція. Ось графік із функцією.

Подальші кроки

Теоретично, функцію кореляції можна уточнити, якщо перевести її з лінійної до експоненційної або логарифмічної. У цьому статистична достовірність кореляції зростає приблизно один відсоток, але складність застосування формули підвищується неймовірно. Тому для себе я ставлю питання: а чи це потрібно насправді? Вирішувати вам – для кожного конкретного випадку.

Кореляційний аналіз– популярний метод статистичного дослідженнящо використовується для виявлення ступеня залежності одного показника від іншого. 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, не надає такої можливості. Нічого страшного, є таблиці критичних значень кореляцій (А.Д. Спадкоємців).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

За відсутності кореляції між параметрами точки на діаграмі розташовані хаотично, середній ступінь зв'язку характеризується більшим ступенем упорядкованості та характеризується більш-менш рівномірною віддаленістю нанесених позначок медіани. Сильна зв'язок прагне прямий і за 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. Тут же можна побудувати діаграму розкиду (розсіювання) з метою наочного уявлення про зв'язок між факторами кореляційного аналізу, що вивчаються, і результативною ознакою.

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

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

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

Розглянемо з прикладу способи розрахунку коефіцієнта кореляції, особливості прямої та зворотної взаємозв'язку між змінними.

Значення показників x та y:

Y – незалежна змінна, x – залежна. Необхідно знайти силу (сильна/слабка) та напрямок (прямий/зворотний) зв'язок між ними. Формула коефіцієнта кореляції виглядає так:

Щоб спростити її розуміння, розіб'ємо кілька нескладних елементів.

Між змінними визначається сильний прямий зв'язок.

Вбудована функція Корел дозволяє уникнути складних розрахунків. Розрахуємо коефіцієнт парної кореляції Excel з її допомогою. Викликаємо майстер функцій. Знаходимо необхідну. Аргументи функції – масив значень y та масив значень х:

Покажемо значення змінних на графіку:

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

Матриця парних коефіцієнтів кореляції в Excel

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

Матриця коефіцієнтів кореляції в Excel будується за допомогою інструмента Кореляція з пакета Аналіз даних.

Між значеннями y та х1 виявлено сильний прямий взаємозв'язок. Між х1 і х2 є сильний зворотний зв'язок. Зв'язок із значеннями в стовпці х3 практично відсутній.

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

Коефіцієнт кореляції(критерій кореляції Пірсона, анг. 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 стовпці
• Мітки у першому рядку: якщо встановлена ​​галочка, то Вхідний інтервалповинен містити заголовки стовпців. Рекомендується встановлювати галочку, щоб результат роботи Надбудови містив інформативні стовпці
• Вихідний інтервал: діапазон осередків, куди будуть розміщені результати обчислень. Достатньо вказати ліву верхню комірку цього діапазону.

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