Excel

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

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

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

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

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

  • 0 - 0,3 - зв'язок відсутній;
  • 0,3 - 0,5 - зв'язок слабка;
  • 0,5 - 0,7 - середня зв'язок;
  • 0,7 - 0,9 - висока;
  • 0,9 - 1 - дуже сильна.

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

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

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

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

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

    2. Перехід в параметри в Microsoft Excel

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

    3. Перехід в надбудови в параметрах в Microsoft Excel

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

Установка пакета аналізу в Microsoft Excel

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

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

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

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

    2. Запуск пакета аналізу в Microsoft Excel

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

    3. Запуск інструменту Кореляція в вікні Аналіз даних в Microsoft Excel

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

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

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

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

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

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

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

    4. Вікно кореляція в Microsoft Excel

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

Матриця кореляції в Microsoft Excel

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

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

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

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