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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Переходимо до розділу «Файл».

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

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

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

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

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

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

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

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

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

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

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

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

Наприклад, у вас значення величин х і у.

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

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

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

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

4. Обчислюємо суму показників у цьому стовпці. Чисельник – знайдений результат.

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

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

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

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

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

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

Покрокова інструкція:

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

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

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

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

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

Алгоритм розрахунку виглядає так:

Відображений показник близький до 1. Результат:

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

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

Використовуючи наведені нижче формули, розрахуємо коефіцієнт кореляції:

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

Результати:

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

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

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

Тепер необхідно провести визначення наявності зв’язку між 2-ма показниками за наведеною нижче формулою:
0,7;ЯКІ(КОРРЕЛ(A3:A8;B3:B8)>0,7;”Сильна пряма залежність”;”Сильна зворотна залежність”);”Слаба залежність або її відсутність”)’ class=’formula’>
Якщо отриманий коефіцієнт вище 0,7, доцільніше застосовувати функцію лінійної регресії. У аналізованому прикладі робимо:

Тепер робимо побудову графіка:

Застосовуємо це рівняння, щоб визначити кількість переглядів при 200, 500 та 1000 репостів:  =9,2937*D4-206,12. Отримуємо наступні результати:

Функція ПОПЕРЕДЖЕННЯ дозволяє визначити число переглядів у моменті, якщо було проведено, наприклад, двісті п’ятдесят ріпостів. Застосовуємо:  0,7; ПЕРЕДСКАЗ (D7; B3: B8; A3: A8); Отримуємо наступні результати:

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

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

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

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

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

Висновок

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