Як знайти коефіцієнт кореляції з Excel

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

Відкрийте БЕЗКОШТОВНУ шпаргалку «Essential Excel Oneulas» прямо зараз!

Це підпише вас на нашу розсилку

Введіть адресу електронної пошти

[] [] [] [] розблокування

Прочитайте нашу політику конфіденційності

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

Що таке кореляція?

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

Ці дві змінні (одна на осі X, інша на Y) абсолютно випадкові і не мають тісного зв'язку.

Тим не менш, дві змінні нижче взаємопов'язані:

У загальному випадку, коли росте одна змінна, росте й інша. Це кореляція. (Зверніть увагу, що це може бути і навпаки; якщо один підвищується, а інший знижується, це негативна кореляція.)

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

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

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

Будь-яке число між ними представляє шкалу. Наприклад, кореляція 0,5 є помірною позитивною кореляцією.

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

Як знайти коефіцієнт кореляції в Excel, використовуючи CORREL

У Excel є вбудована функція для кореляції. Функція CORREL має дуже простий синтаксис:

=CORREL(array1, array2)

array1 - ваша перша група чисел, а array2 - друга група. Excel викладе число, і це ваш коефіцієнт кореляції. Давайте подивимося на приклад.

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

Там дуже слабка позитивна кореляція; так само, як рік зростає, так і вартість автомобіля. Але не дуже.

Графічні кореляції

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

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

Іншим корисним елементом в діаграмі розсіювання є лінія тренда, яка виглядає наступним чином:

Лінія тренда може бути корисна, коли ви хочете чітко визначити кореляцію на графіку розсіювання. У Windows натисніть «Інструменти діаграми» > «Дизайн» > «Додати елемент діаграми» і виберіть лінію тренда. На Mac вам потрібно перейти до Chart Layout або Chart Design, залежно від випуску Excel.

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

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

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

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

Щоб запустити Toolpak, виберіть «Дані» > «Аналіз даних». Ви побачите список варіантів:

Виберіть Кореляція і натисніть кнопку Гаразд.

У вікні, що з'явиться, виберіть всі свої набори даних у полі «Діапазон вводу» і скажіть Excel, де ви хочете розмістити результати:

Ось що ви отримаєте, коли натиснете OK:

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

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

Кореляція проти лінійної регресії в Excel

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

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

Відкрийте набір інструментів аналізу даних, виберіть «Регресія» та натисніть «OK».

Заповніть діапазони X і Y (значення X - це пояснювальна змінна, а значення Y - це значення, яке ви намагаєтеся передбачити). Потім виберіть, куди ви хочете надіслати вивід, і знову натисніть кнопку Гаразд.

Число, на якому ви хочете зосередитися, є p-значенням для вашої пояснюючої змінної:

Якщо воно менше 0,05, у вас є вагомий аргумент, що зміни у вашій змінній X викликають зміни у вашій змінній Y. На зображенні вище ми показали, що рік є значущим предиктором світового населення.

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

Обидва значення p менше 0,05, тому ми можемо зробити висновок, що як рік, так і населення світу є значущими предикторами ціни сирої нафти. (Хоча сильні кореляції між змінними X можуть викликати їх власні проблеми.)

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

Але тепер у вас є уявлення про те, як вийти за рамки простої кореляції, якщо ви шукаєте більше статистичної інформації!

Отримайте найкращі дані з Excel

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

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

Чи ви регулярно використовуєте функції кореляції Excel? Які ще статистичні функції ви хотіли б дізнатися?