Excel - це потужний інструмент, який має деякі дивовижні функції та функціональні можливості при роботі зі статистикою.
Знаходження кореляції між двома рядами даних є одним із найпоширеніших статистичних обчислень при роботі з великими наборами даних,
Я працював фінансовим аналітиком кілька років тому, і хоча ми не були залучені до статистичних даних, виявлення кореляції - це те, що нам досі доводилося робити досить часто.
У цьому уроці я покажу вам два дійсно простих способи розрахувати коефіцієнт кореляції в Excel. Для цього вже є вбудована функція, і ви також можете скористатися пакетом інструментів аналізу даних.
Тож почнемо!
Що таке коефіцієнт кореляції?
Оскільки це не клас статистики, дозвольте мені коротко пояснити, що таке коефіцієнт кореляції, а потім ми перейдемо до розділу, де розраховуємо коефіцієнт кореляції в Excel.
Коефіцієнт кореляції - це значення, яке вказує, наскільки тісно пов'язані два ряди даних.
Поширений приклад - це вага і зріст 10 осіб у групі. Якщо ми обчислимо коефіцієнт кореляції для даних про зріст і вагу цих людей, ми отримаємо значення від -1 до 1.
Значення менше нуля вказує на негативну кореляцію, що означає, що якщо висота зростає, то вага зменшується, або якщо вага зростає при цьому, то висота зменшується.
І значення більше нуля вказує на позитивну кореляцію, а це означає, що якщо висота зростає, то вага зростає, а якщо висота зменшується, то вага зменшується.
Чим ближче значення до 1, тим сильніша позитивна кореляція. Отже, значення 0,8 свідчить про те, що дані про зріст та вагу сильно корелюють.
Примітка: Існують різні типи коефіцієнтів кореляції та статистика, але в цьому підручнику ми розглянемо найпоширеніший - коефіцієнт кореляції ПірсонаТепер давайте подивимося, як розрахувати цей коефіцієнт кореляції в Excel.
Розрахунок коефіцієнта кореляції в Excel
Як я вже згадував, є кілька способів обчислення коефіцієнта кореляції в Excel.
Використовуючи формулу CORREL
CORREL - це функція статистики, яка була представлена в Excel 2007.
Припустимо, у вас є набір даних, як показано нижче, де потрібно обчислити коефіцієнт кореляції між зростом і вагою 10 осіб.
Нижче наведена формула, яка зробить це:
= CORREL (B2: B12, C2: C12)
Наведена вище функція CORREL приймає два аргументи - ряд з точками даних висоти та ряд з точками даних ваги.
І це все!
Як тільки ви натискаєте Enter, Excel виконує всі обчислення в інтерфейсі, він дає вам єдине число коефіцієнта кореляції Пірсона.
У нашому прикладі це значення трохи більше 0,5, що вказує на наявність досить сильної позитивної кореляції.
Цей метод найкраще використовувати, якщо у вас є дві серії, і вам потрібен лише коефіцієнт кореляції.
Але якщо у вас є кілька серій, і ви хочете дізнатися коефіцієнт кореляції всіх цих рядів, то ви також можете розглянути можливість використання пакета інструментів аналізу даних у Excel (розглянуто далі)
Використання пакета інструментів аналізу даних
Excel має пакет інструментів аналізу даних, який можна використовувати для швидкого обчислення різних статистичних значень (включаючи отримання коефіцієнта кореляції).
Але пакет інструментів аналізу даних вимкнено за замовчуванням у Excel. Отже, першим кроком було б увімкнути інструмент аналізу даних, а потім використати його для розрахунку коефіцієнта кореляції Пірсона в Excel.
Увімкнення пакета інструментів аналізу даних
Нижче наведено кроки, щоб увімкнути Пакет інструментів аналізу даних у Excel:
- Перейдіть на вкладку Файл
- Натисніть Параметри
- У діалоговому вікні Параметри Excel, що відкриється, натисніть опцію Надбудови на панелі бічної панелі
- У спадному меню Керування виберіть надбудови Excel
- Натисніть Перейти. Відкриється діалогове вікно надбудов
- Перевірте параметр Пакет інструментів аналізу
- Натисніть кнопку ОК
Наведені вище кроки додадуть нову групу на вкладці Дані на стрічці Excel під назвою Аналіз. У цій групі ви матимете можливість аналізу даних
Розрахунок коефіцієнта кореляції за допомогою пакета інструментів аналізу даних
Тепер, коли у вас знову є інструмент аналізу на стрічці, давайте подивимося, як обчислити коефіцієнт кореляції за його допомогою.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете з'ясувати кореляцію між трьома рядами (зріст і вага, зріст і дохід, а також вага і дохід)
Нижче наведено кроки для цього.
- Перейдіть на вкладку Дані
- У групі «Аналіз» натисніть на опцію «Аналіз даних»
- У діалоговому вікні "Аналіз даних", що відкриється, натисніть "Співвідношення"
- Натисніть OK. Відкриється діалогове вікно Співвідношення
- Для діапазону введення виберіть три серії, включаючи заголовки
- Для "Згруповано за" переконайтеся, що вибрано "Стовпці"
- Виберіть варіант - "Мітка в першому рядку". Це переконається, що отримані дані матимуть однакові заголовки, і зрозуміти результати буде набагато простіше
- У параметрах виводу виберіть, де ви хочете отримати отриману таблицю. Я збираюся перейти до комірки G1 на тому ж робочому аркуші. Ви також можете вибрати, щоб результати відображалися на новому робочому аркуші або в новій книзі
- Натисніть OK
Як тільки ви це зробите, Excel обчислить коефіцієнт кореляції для всіх серій і надасть вам таблицю, як показано нижче:
Зверніть увагу, що отримана таблиця є статичною і не оновлюється у разі зміни будь -якої точки даних у вашій таблиці. У разі будь -яких змін вам доведеться повторити вищевказані кроки ще раз, щоб створити нову таблицю коефіцієнтів кореляції.
Отже, це два швидких і простих методу для розрахунку коефіцієнта кореляції в Excel.
Сподіваюся, вам цей підручник був корисним!
