Кореляційний аналіз у Excel. Приклад виконання кореляційного аналізу

Кореляційний аналіз – це поширений метод дослідження, застосовуваний визначення рівня залежності 1-ї величини від 2-ї. У табличному процесорі є спеціальний інструмент, який дозволяє реалізувати цей тип досліджен

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

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

Призначення кореляційного аналізу

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

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

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

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

Корел – функція, що дозволяє реалізувати кореляційний аналіз. Загальний вигляд – КОРРЕЛ (масив1; масив2). Докладна інструкція:

  1. Необхідно провести виділення осередку, у якій планується виводити результат розрахунку. Натиснути “Вставити функцію”, що знаходиться ліворуч від текстового поля для введення формули.
  2. Відкриється “Майстер функцій”. Тут необхідно знайти Корел , клікнути на неї, потім на «ОК».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Відкрилося віконце аргументів. У рядок «Массив1» необхідно ввести координати інтервали одного зі значень. У прикладі — це стовпець «Величина продажів». Потрібно просто зробити виділення всіх осередків, що знаходяться в цій колонці. У рядок “Масив2” аналогічно необхідно додати координати другої колонки. У прикладі — це стовпець «Витрати на рекламу».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Після введення всіх діапазонів натискаємо кнопку «ОК».

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

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

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

Читайте також  Стандартне відхилення і дисперсія в EXCEL - Як рахувати
  1. Переходимо до розділу «Файл».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Відкрилося нове віконце, в якому потрібно натиснути на розділ «Параметри».
  2. Тиснемо на «Надбудови».
  3. Знаходимо в нижній частині елемент керування. Тут необхідно вибрати з контекстного меню «Надбудови Excel» та натиснути «ОК».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Відкрилося спеціальне вікно надбудов. Ставимо галочку поруч із елементом «Пакет аналізу». Клікаємо «ОК».
  2. Активація відбулася успішно. Тепер переходимо у «Дані». З’явився блок “Аналіз”, в якому необхідно натиснути “Аналіз даних”.
  3. У новому вікні вибираємо елемент «Кореляція» і тиснемо на «ОК».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. На екрані з’явилося віконце налаштувань аналізу. У рядок «Вхідний інтервал» потрібно ввести спектр всіх колонок, що беруть участь в аналізі. У цьому прикладі — це стовпчики «Величина продажів» і «Витрати реклами». У параметрах відображення виводу спочатку виставлено параметр “Новий робочий лист”, що означає показ результатів на іншому листі. За бажанням можна змінити локацію виведення результату. Після всіх налаштувань натискаємо на «ОК».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Вивелися підсумкові показники. Результат такий самий, як і в першому методі – 0,97.

Визначення та обчислення множинного коефіцієнта кореляції в MS Excel

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

  1. У розділі “Дані” знаходимо вже відомий блок “Аналіз” і тиснемо “Аналіз даних”.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. У вікні, що відобразиться, тиснемо на елемент «Кореляція» і клацаємо на «ОК».
  2. У рядок «Вхідний інтервал» вбиваємо інтервал за трьома або більше стовпцями вихідної таблиці. Діапазон можна ввести вручну або просто виділити його ЛКМ, і він автоматично відобразиться в потрібному рядку. У “Групування” вибираємо відповідний спосіб угруповання. У «Параметрі виводу» вказується місце, в яке будуть виведені результати кореляції. Клікаємо «ОК».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Готово! Збудувалась матриця кореляції.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

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

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

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

Наприклад, у вас значення величин х і у.
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Х – це залежна змінна, а у – незалежна. Необхідно знайти напрямок та силу зв’язку між цими показниками. Покрокова інструкція:

  1. Виявимо середні показники величин за допомогою функції СРЗНАЧ.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Зробимо розрахунок кожного х і хсредн , у і усредн за допомогою оператора «-».

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Виробляємо перемноження обчислених різниць.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Обчислюємо суму показників у цьому стовпці. Чисельник – знайдений результат.
Читайте також  Середнє квадратичне відхилення в Excel - і інші статистичні функції

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Порахуємо знаменники різниці х і х-средн, у і у-средн . Для цього зробимо зведення у квадрат.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Використовуючи функцію АВТОСУМА , знайдемо показники в отриманих стовпчиках. Виробляємо перемноження. За допомогою функції КОРІНЬ зводимо результат квадрат.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. Проводимо підрахунок приватного, використовуючи значення знаменника та чисельника.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

  1. КОРРЕЛ – інтегрована функція, що дозволяє запобігти проведенню найскладніших розрахунків. Заходимо в «Майстер функцій», вибираємо Корел і вказуємо масиви показників х і у . Будуємо графік, що відображає отримані значення.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

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

Розберемо, як проводити підрахунок коефіцієнтів парних матриць. Наприклад, є матриця із чотирьох змінних.
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Покрокова інструкція:

  1. Заходимо в “Аналіз даних”, що знаходиться в блоці “Аналіз” вкладки “Дані”. У списку вибираємо «Кореляція».
  2. Виставляємо всі необхідні налаштування. “Вхідний інтервал” – інтервал усіх чотирьох колонок. “Вихідний інтервал” – місце, в якому бажаємо відобразити результати. Клацаємо на кнопку «ОК».
  3. У вибраному місці збудувалась матриця кореляції. Кожне перетинання рядка та стовпця – коефіцієнти кореляції. Цифра 1 відображається при координатах, що збігаються.

корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

Функція КОРРЕЛ для визначення взаємозв’язку та кореляції в Excel

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

Приклади використання функції КОРРЕЛ в Excel

Перший приклад. Є табличка, в якій розписана інформація про усереднені показники заробітної плати працівників компанії протягом одинадцяти років та курсу $. Необхідно виявити зв’язок між цими двома величинами. Табличка виглядає так:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Алгоритм розрахунку виглядає так:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Відображений показник близький до 1. Результат:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

Визначення коефіцієнта кореляції впливу дій на результат

Другий приклад. Два претенденти звернулися за допомогою до двох різних агентств для реалізації рекламного просування тривалістю п’ятнадцять діб. Щодобу проводилося соціальне опитування, що визначає ступінь підтримки кожного претендента. Будь-який опитаний міг обрати одного з двох претендентів або виступити проти всіх. Необхідно визначити, як сильно вплинуло кожне рекламне просування на рівень підтримки претендентів, яка компанія ефективніша.
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Використовуючи наведені нижче формули, розрахуємо коефіцієнт кореляції:

  • КОРРЕЛ (А3: А17; В3: В17).
  • = Коррел (А3: А17; С3: С17).

Результати:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
З отриманих результатів стає зрозуміло, що ступінь підтримки одного претендента підвищувалася з кожною добою проведення рекламного просування, отже, коефіцієнт кореляції наближається до 1. При запуску реклами інший претендент мав велику кількість довіри, і протягом 5 днів була позитивна динаміка. Потім ступінь довіри знизився і до п’ятнадцятої доби опустився нижче за початкові показники. Низькі показники свідчать, що рекламне просування негативно вплинуло підтримку. Не слід забувати, що на показники могли вплинути й інші супутні фактори, які не розглядаються в табличній формі.

Читайте також  Доступ в Інтернет закритий ERR_NETWORK_ACCESS_DENIED Chrome — як виправити?

Аналіз популярності контенту з кореляції переглядів та репостів відео

Третій приклад. Людина на просування своїх роликів на видеохостинге Ютуб використовує соцмережі для рекламування каналу. Він зауважує, що існує певний взаємозв’язок між числом репостів у соцмережах та кількістю переглядів на каналі. Чи можна за допомогою інструментів табличного процесора зробити прогноз майбутніх показників? Необхідно виявити резонність застосування рівняння лінійної регресії для прогнозування кількості переглядів відеозаписів залежно кількості репостів. Табличка зі значеннями:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Тепер необхідно провести визначення наявності зв’язку між 2-ма показниками за наведеною нижче формулою:
0,7;ЯКІ(КОРРЕЛ(A3:A8;B3:B8)>0,7;”Сильна пряма залежність”;”Сильна зворотна залежність”);”Слаба залежність або її відсутність”)’ class=’formula’>
Якщо отриманий коефіцієнт вище 0,7, доцільніше застосовувати функцію лінійної регресії. У аналізованому прикладі робимо:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Тепер робимо побудову графіка:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Застосовуємо це рівняння, щоб визначити кількість переглядів при 200, 500 та 1000 репостів:  =9,2937*D4-206,12. Отримуємо наступні результати:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз
Функція ПОПЕРЕДЖЕННЯ дозволяє визначити число переглядів у моменті, якщо було проведено, наприклад, двісті п’ятдесят ріпостів. Застосовуємо:  0,7; ПЕРЕДСКАЗ (D7; B3: B8; A3: A8); Отримуємо наступні результати:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

Особливості використання функції КОРРЕЛ в Excel

Ця функція має наведені нижче особливості:

  1. Не враховуються осередки порожнього типу.
  2. Не враховуються осередки, у яких міститься інформація типу Boolean і Text.
  3. Подвійне заперечення “-” застосовується для обліку логічних величин у вигляді чисел.
  4. Кількість осередків у досліджуваних масивах повинні збігатися, інакше буде виведено повідомлення #Н/Д.

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

При перевірці значущості кореляційного коефіцієнта нульова гіпотеза у тому, що має значення 0, а альтернативна немає. Для перевірки застосовується наведена нижче формула:
корреляційний-аналіз-в-excel-primer-виполнення-корреляціонального-аналіз

Висновок

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

Степан Лютий

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

You may also like...

Залишити відповідь

Ваша e-mail адреса не оприлюднюватиметься. Обов’язкові поля позначені *