2 способи кореляційного аналізу в Microsoft Excel

Зміст

• Суть кореляційного аналізу
• Розрахунок коефіцієнта кореляції
  • Спосіб 1: Визначення кореляції через Майстер функцій
  • Спосіб 2: обчислення кореляції за допомогою пакету аналізу
• Питання та відповіді

Кореляційний аналіз – популярний метод статистичного дослідження, який використовується для виявлення ступеня залежності одного показника від іншого. У Microsoft Excel є спеціальний інструмент, призначений для виконання цього типу аналізу. Давайте з'ясуємо, як користуватися цією функцією.

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

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

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

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

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

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

Одним із способів, за допомогою якого можна провести кореляційний аналіз, є використання функції корел. Сама функція має загальний вигляд КОРРЕЛ (масив1; масив2) .

1. Виділяємо осередок, в якій повинен виводитися результат розрахунку. Кількома по кнопці " Вставити функцію» , яка розміщується зліва від рядка формул.

2. У списку, який представлений у вікні Майстра функцій, шукаємо і виділяємо функцію КОРРЕЛ . Тиснемо на кнопку «OK» .

3. Відкривається вікно аргументів функції. У полі " Масив1» вводимо координати діапазону осередків одного зі значень, залежність якого слід визначити. У нашому випадку це будуть значення в колонці «Величина продажів». Для того, щоб внести адресу масиву в поле, просто виділяємо всі осередки з даними в вищевказаному стовпці.

   У полі " Масив2» потрібно внести координати другого стовпця. У нас це витрати на рекламу. Точно так же, як і в попередньому випадку, заносимо дані в поле.

   Тиснемо на кнопку «OK» .

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

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

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

1. Переходимо у вкладку " Файл» .

2. У вікні переміщаємося в розділ «Параметри» .

3. Далі переходимо в пункт «надбудови» .

4. У нижній частині наступного вікна в розділі «Управління» переставляємо перемикач в позицію «Надбудови Excel» , якщо він знаходиться в іншому положенні. Тиснемо на кнопку «OK» .

5. У вікні надбудов встановлюємо галочку біля пункту " Пакет аналізу» . Тиснемо на кнопку «OK» .

6. Після цього Пакет аналізу активовано. Переходимо у вкладку»дані" . Як бачимо, тут на стрічці з'являється новий блок інструментів – " аналіз» . Тиснемо на кнопку " аналіз даних» , яка розташована в ньому.

7. Відкривається список з різними варіантами аналізу даних. Вибираємо пункт " кореляція» . Кількома по кнопці «OK» .

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

   Параметр»групування" залишаємо без змін – " за стовпцями» , так як у нас групи даних розбиті саме на два стовпці. Якби вони були розбиті рядково, то тоді слід було б переставити перемикач в позицію " по рядках» .

   В параметрах виведення за замовчуванням встановлений пункт " новий робочий аркуш» , тобто, дані будуть виводитися на іншому аркуші. Можна змінити місце, переставивши перемикач. Це може бути поточний лист (тоді ви повинні будете вказати координати осередків виведення інформації) або нова робоча книга (файл).

   Коли всі налаштування встановлені, тиснемо на кнопку «OK» .

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

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