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

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

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

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

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

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

Важливо! При 0-му коефіцієнті зв’язок між величинами відсутній.

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

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

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

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

1. Необхідно вибрати комірку, в якій планується вивести результат обчислення. Щоб ввести формулу, натисніть «Вставити функцію» ліворуч від текстового поля.

2. Відкриється Майстер функцій. Тут потрібно знайти КОРЕЛ, натисніть на нього, а потім на «OK».

3. Відкриється вікно аргументів. У рядку «Масив1» необхідно ввести координати інтервалів 1-го із значень. У цьому прикладі це стовпець «Вартість продажу». Вам просто потрібно виділити всі клітинки, які знаходяться в цьому стовпці. Так само в рядок «Масив2» потрібно додати координати другого стовпця. У нашому прикладі це стовпець «Витрати на рекламу».

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

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

Спосіб 2: обчисліть кореляцію за допомогою Analysis ToolPak

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

1. Перейдіть в розділ «Файл».

2. Відкриється нове вікно, в якому потрібно натиснути на розділ «Налаштування».
3. Натисніть «Додатки».
4. Внизу знаходимо елемент «Управління». Тут потрібно вибрати в контекстному меню «Надбудови Excel» і натиснути «ОК».

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

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

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

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

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

Детальний посібник:

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

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

4. Готовий! Була побудована кореляційна матриця.

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

Давайте розберемося, як правильно намалювати парний коефіцієнт кореляції в таблиці Excel.

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

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

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

1. Знайдемо середні значення за допомогою функції СЕРЦЕ.

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

3. Перемножуємо обчислені різниці.

4. Розраховуємо суму показників у цьому стовпчику. Чисельник - знайдений результат.

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

6. Використання функції АВТОСУМА, знайдіть показники в отриманих стовпчиках. Виконуємо множення. Використання функції ROOT зведіть результат у квадрат.

7. Обчислюємо частку, використовуючи значення знаменника та чисельника.

8. CORREL — інтегрована функція, яка дозволяє запобігти складним обчисленням. Заходимо в «Майстер функцій», вибираємо CORREL і вказуємо масиви індикаторів х и у. Будуємо графік, який відображає отримані значення.

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

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

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

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

Функція CORREL для визначення зв’язку та кореляції в Excel

CORREL – функція для обчислення коефіцієнта кореляції між 2 масивами. Давайте розглянемо чотири приклади всіх можливостей цієї функції.

Приклади використання функції CORREL в Excel

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

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

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

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

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

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

• =CORREL(A3:A17; B3:B17).
• =CORREL(A3:A17;C3:C17).

результати:

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

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

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

Тепер необхідно визначити наявність зв'язку між 2 показниками за наступною формулою:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Сильний прямий зв’язок”;”Сильний зворотний зв’язок”);”Слабкий або відсутній зв’язок”)’ class=’formula’>

Якщо отриманий коефіцієнт вище 0,7, то доцільніше використовувати функцію лінійної регресії. У цьому прикладі ми робимо:

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

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

функція ПРОГНОЗ дозволяє визначити кількість переглядів на даний момент, якщо було, наприклад, двісті п'ятдесят репостів. Застосовуємо: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Значення не пов’язані”)’ class=’formula’>. Отримуємо такі результати:

Особливості використання функції CORREL в Excel

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

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

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

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

Висновок

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