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

Зміст

• Обчислення множинного коефіцієнта кореляції
  • Етап 1: активація пакету аналізу
  • Етап 2: Розрахунок коефіцієнта
  • Етап 3: аналіз отриманого результату
• Питання та відповіді

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

Читайте також: кореляційний аналіз в Екселі

Обчислення множинного коефіцієнта кореляції

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

Якщо кореляційний коефіцієнт негативний, то це означає, що зв'язок параметрів зворотна.

Для того, щоб скласти кореляційну матрицю в Ексель, використовується один інструмент, що входить в пакет " аналіз даних» . Він так і називається – " кореляція» . Давайте дізнаємося, як за допомогою нього можна обчислити показники множинної кореляції.

Етап 1: активація пакету аналізу

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

1. Переходимо у вкладку " Файл» . У лівому вертикальному меню вікна, яке відкриється після цього, клацаємо по пункту »Параметри" .

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

3. Відбувається запуск невеликого віконця «надбудови» . Встановлюємо прапорець близько параметра " Пакет аналізу» . Потім в правій частині вікна натискаємо на кнопку «OK» .

Після зазначеної дії пакет інструментів " аналіз даних» буде активовано.

Етап 2: Розрахунок коефіцієнта

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

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

2. Відкривається віконце, яке носить найменування " аналіз даних» . Виділяємо в списку інструментів, розташованих в ньому, найменування " кореляція» . Після цього клацаємо по кнопці «OK» у правій частині інтерфейсу вікна.

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

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

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

   У блоці налаштувань " Параметр виводу» слід вказати, де саме буде розташовуватися наша кореляційна матриця, в яку виводиться результат розрахунку. Доступні три варіанти:

   • Нова книга (інший файл);
   • Новий лист (при бажанні в спеціальному полі можна дати йому найменування);
   • Діапазон на поточному аркуші.

   Давайте виберемо останній варіант. Переставляємо перемикач в положення " вихідний інтервал» . В цьому випадку у відповідному полі потрібно вказати адресу діапазону матриці або хоча б її верхню ліву клітинку. Встановлюємо курсор в поле і клацаємо по осередку на аркуші, яку плануємо зробити верхнім лівим елементом діапазону виведення даних.

   Після виконання всіх зазначених маніпуляцій залишається тільки клацнути по кнопці «OK» в правій частині віконця " кореляція» .


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

Етап 3: аналіз отриманого результату

Тепер давайте розберемося, як розуміти той результат, який ми отримали в процесі обробки даних інструментом " кореляція» у програмі Excel.

Як бачимо з таблиці, коефіцієнт кореляції фондоозброєності (стовпець 2 ) і енергоозброєності ( стовпець 1 ) становить 0,92, що відповідає дуже сильному взаємозв'язку. Між продуктивністю праці ( стовпець 3 ) і енергоозброєністю ( стовпець 1 ) даний показник дорівнює 0,72, що є високим ступенем залежності. Коефіцієнт кореляції між продуктивністю праці ( стовпець 3 ) і фондоозброєністю ( стовпець 2 ) дорівнює 0,88, що теж відповідає високому ступеню залежності. Таким чином, можна сказати, що залежність між усіма досліджуваними факторами простежується досить сильна.

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