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

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

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

Ця стаття розрахована на сучасний рівень володіння Microsoft Excel. Якщо у вас немає часу, щоб читати всю статтю, ви можете завантажити файл і розібратися з ним самостійно.

Якщо ви часто стикаєтеся з необхідністю зробити щось подібне, настійно рекомендую подумати про покупку книги Статистичні обчислення в середовищі Excel.

Що важливо знати про кореляції

Щоб розрахувати достовірну кореляцію, необхідно мати достовірну вибірку, що більше вона буде, то достовірнішим буде результат. Для цілей цього прикладу я взяв щоденну вибірку курсів валют за 10 років. Дані є у вільному доступі, я їх брав із сайту http://oanda.com.

Що я, власне, зробив

(1) Коли я мав вихідні дані, я почав з того, що перевірив ступінь кореляції цих двох наборів даних. Для цього я скористався функцією CORREL (КОРРЕЛ) - про неї є небагато інформації. Вона повертає рівень кореляції двох діапазонів даних. Результат, прямо скажемо, вийшов не дуже вражаючим (всього близько 70%). А взагалі, ступінь співвідношення двох величин прийнято вважати, як квадрат цієї величини, тобто кореляція вийшла достовірною приблизно на 49%. Це дуже мало!

(2) Мені це здалося дуже дивним. Які помилки могли закрастись у мої розрахунки? Тому я вирішив побудувати графік та подивитися, що могло статися. Графік був простоєний спеціально з розбивкою за роками, щоб можна було візуально побачити, де рве кореляцію. Графік вийшов ось таким

(3) З графіка очевидно, що у діапазоні близько 35 рублів за євро кореляцію починає рвати дві частини. Через це вона і вийшла недостовірною. Необхідно було визначити у зв'язку із чим це відбувається.

(4) За кольором видно, що ці дані відносяться до 2007, 2008, 2009 років. Звичайно! p align="justify"> Періоди економічних піків і спадів зазвичай недостовірні статистично, що і сталося в даному випадку. Тому я спробував виключити з даних ці періоди (і для перевірки, я перевірив ступінь кореляції даних у цьому періоді). Ступінь кореляції цих даних становить 0.01%, тобто вона відсутня в принципі. Проте без них дані корелюють приблизно на 81%. Це вже достовірна кореляція. Ось графік із функцією.

Подальші кроки

Теоретично, функцію кореляції можна уточнити, якщо перевести її з лінійної до експоненційної або логарифмічної. У цьому статистична достовірність кореляції зростає приблизно один відсоток, але складність застосування формули підвищується неймовірно. Тому для себе я ставлю питання: а чи це потрібно насправді? Вирішувати вам – для кожного конкретного випадку.

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

Використання кореляції

Обчислення кореляції особливо широко використовується в економіці, соціологічних дослідженнях, медицині та біометрії — скрізь, де можна отримати два масиви даних, між якими може виявитися зв'язок.

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

Як виконати кореляцію в Excel?

Найбільш трудомістким етапом визначення кореляції є набір масиву даних. Порівнювані дані розташовуються зазвичай у двох колонках чи рядках. Таблицю слід робити без перепусток у осередках. Сучасні версії Excel (з 2007 і молодше) не вимагають встановлення додаткових налаштувань для статистичних розрахунків; необхідні маніпуляції можна зробити:

1. Вибрати порожній осередок, до якого буде виведено результат розрахунків.
2. Натисніть у головному меню Excel пункт Формули.
3. Серед кнопок, згрупованих у «Бібліотеку функцій», виберіть «Інші функції».
4. У списках, що випадають, вибрати функцію розрахунку кореляції (Статистичні — КОРРЕЛ).
5. В Excel відкриється панель "Аргументи функції". Масив 1 і Масив 2 - це діапазони порівнюваних даних. Для автоматичного заповнення цих полів можна виділити потрібні осередки таблиці.
6. Натиснути "ОК", закривши вікно аргументів функції. У комірці з'явиться підрахований коефіцієнт кореляції.

Кореляція може бути пряма (якщо коефіцієнт більший за нуль) і зворотна (від -1 до 0).

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

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

Якщо коефіцієнт показує середній або сильний взаємозв'язок (від ±0,5 до ±0,99), слід пам'ятати, що це лише статистичний взаємозв'язок, який зовсім не гарантує впливу одного параметра на інший. Також не можна виключати ситуації, що обидва параметри незалежні один від одного, але на них впливає якийсь третій неврахований фактор. Excel допомагає моментально обчислити коефіцієнт кореляції, але зазвичай лише кількісних методівнедостатньо для встановлення причинно-наслідкових зв'язків у співвідносних вибірках.

Критерій кореляції Пірсона – це метод параметричної статистики, що дозволяє визначити наявність або відсутність лінійного зв'язку між двома кількісними показниками, а також оцінити її тісноту та статистичну значимість. Іншими словами, критерій кореляції Пірсона дозволяє визначити, чи є лінійний зв'язок між змінами значень двох змінних. У статистичних розрахунках та висновках коефіцієнт кореляції зазвичай позначається як r xyабо R xy.

1. Історія розробки критерію кореляції

Критерій кореляції Пірсона був розроблений командою британських учених на чолі з Карлом Пірсоном(1857-1936) у 90-х роках 19-го століття, для спрощення аналізу коваріації двох випадкових величин. Крім Карла Пірсона, над критерієм кореляції Пірсона працювали також Френсіс Еджуорті Рафаель Велдон.

2. Навіщо використовується критерій кореляції Пірсона?

Критерій кореляції Пірсона дозволяє визначити, яка тіснота (або сила) кореляційного зв'язку між двома показниками, виміряними у кількісній шкалі. За допомогою додаткових розрахунків можна також визначити, наскільки статистично значущий виявлений зв'язок.

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

3. Умови та обмеження застосування критерію хі-квадрат Пірсона

1. Порівняні показники повинні бути виміряні в кількісній шкалі(Наприклад, частота серцевих скорочень, температура тіла, вміст лейкоцитів в 1 мл крові, систолічний артеріальний тиск).
2. За допомогою критерію кореляції Пірсона можна визначити лише наявність та силу лінійного взаємозв'язкуміж величинами. Інші характеристики зв'язку, у тому числі напрямок (прямий або зворотний), характер змін (прямолінійний або криволінійний), а також наявність залежності однієї змінної від іншої - визначаються за допомогою регресійного аналізу.
3. Кількість зіставлюваних величин має дорівнювати двом. У разі аналізу взаємозв'язку трьох і більше параметрів слід скористатися методом факторного аналізу.
4. Критерій кореляції Пірсона є параметричним, у зв'язку з чим умовою його застосування служить нормальний розподілзіставлюваних змінних. У разі потреби кореляційного аналізу показників, розподіл яких відрізняється від нормального, у тому числі виміряних у порядковій шкалі, слід використовувати коефіцієнт рангової кореляції Спірмена.
5. Слід чітко розрізняти поняття залежності та кореляції. Залежність величин обумовлює наявність кореляційного зв'язку з-поміж них, але з навпаки.

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

В іншій ситуації розглянемо зв'язок зростання дитини та частоти серцевих скорочень (ЧСС). Як відомо, обидві ці величини безпосередньо залежать від віку, тому в більшості випадків діти більшого зростання (а значить і старшого віку) матимуть менші значення ЧСС. Тобто, кореляційний зв'язокспостерігатиметься і може мати досить високу тісноту. Однак якщо ми візьмемо дітей одного віку, але різного зростання, то, швидше за все, ЧСС у них буде відрізнятися несуттєво, у зв'язку з чим можна зробити висновок незалежностіЧСС від зростання.

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

4. Як розрахувати коефіцієнт кореляції Пірсона?

Розрахунок коефіцієнта кореляції Пірсона здійснюється за такою формулою:

5. Як інтерпретувати значення коефіцієнта кореляції Пірсона?

Значення коефіцієнта кореляції Пірсона інтерпретуються з його абсолютних значень. Можливі значення коефіцієнта кореляції варіюють від 0 до ±1. Чим більше абсолютне значення r xy – тим вища тіснота зв'язку між двома величинами. r xy = 0 говорить про повну відсутність зв'язку. r xy = 1 – свідчить про наявність абсолютного (функціонального) зв'язку. Якщо значення критерію кореляції Пірсона виявилося більшим за 1 або менше -1 – у розрахунках допущена помилка.

Для оцінки тісноти або сили кореляційного зв'язку зазвичай використовують загальноприйняті критерії, згідно з якими абсолютні значення r xy< 0.3 свидетельствуют о слабкоюзв'язку, значення r xy від 0.3 до 0.7 - про зв'язок середньоїтісноти, значення r xy > 0.7 - о сильноюзв'язку.

Точнішу оцінку сили кореляційного зв'язку можна отримати, якщо скористатися таблицею Чеддока:

Оцінка статистичної значимості коефіцієнта кореляції r xy здійснюється за допомогою t-критерію, що розраховується за такою формулою:

Отримане значення t r порівнюється з критичним значенням за певного рівня значущості та числа ступенів свободи n-2. Якщо t r перевищує t критий, то робиться висновок про статистичну значущість виявленого кореляційного зв'язку.

6. Приклад розрахунку коефіцієнта кореляції Пірсона

Метою дослідження стало виявлення, визначення тісноти та статистичної значущості кореляційного зв'язку між двома кількісними показниками: рівнем тестостерону в крові (X) та відсотком м'язової масиу тілі (Y). Вихідні дані для вибірки, що складається з 5 досліджуваних (n = 5), зведені у таблиці.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обчислимо коефіцієнт кореляції та коваріацію для різних типіввзаємозв'язків випадкових величин

Коефіцієнт кореляції(критерій кореляції Пірсона, анг. Pearson Product Moment correlation coefficient)визначає ступінь лінійноївзаємозв'язку між випадковими величинами

Як випливає з визначення, для обчислення коефіцієнта кореляціїпотрібно знати розподіл випадкових величин Х та Y. Якщо розподіли невідомі, то для оцінки коефіцієнта кореляціївикористовується вибірковий коефіцієнт кореляціїr (ще він позначається як R xy або r xy) :

де S x - стандартне відхиленнявибірки випадкової величиних, що обчислюється за формулою:

Як видно з формули для розрахунку кореляції, знаменник (твір стандартних відхилень) просто нормує чисельник таким чином, що кореляціявиявляється безрозмірним числом від –1 до 1. Кореляціяі коваріаціянадають одну і ту ж інформацію (якщо відомі стандартні відхилення ), але кореляцієюзручніше скористатися, т.к. вона є безрозмірною величиною.

Розрахувати коефіцієнт кореляціїі підступність вибіркив MS EXCEL не становить труднощів, тому що для цього є спеціальні функції КОРРЕЛ() і КОВАР(). Набагато складніше розібратися, як інтерпретувати набуті значення, більшість статті присвячена саме цьому.

Теоретичний відступ

Нагадаємо, що кореляційним зв'язкомназивають статистичний зв'язок, який полягає в тому, що різним значеннямоднієї змінної відповідають різні середнізначення інший (зі зміною значення Х середнє значення Y змінюється закономірним чином. Передбачається, що обидвізмінні Х і Y є випадковимивеличинами і мають якийсь випадковий розкид щодо них середнього значення.

Примітка. Якщо випадкову природу має лише одна змінна, наприклад, Y, а значення інший є детермінованими (задаються дослідником), можна говорити лише регресії.

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

Кореляційний зв'язокміж змінними може виникнути кількома шляхами:

1. Наявність причинної залежності між змінними. Наприклад, кількість інвестицій у наукові дослідження(змінна Х) та кількість отриманих патентів (Y). Перша змінна виступає як незалежна змінна (фактор), друга - залежна змінна (результат). Необхідно пам'ятати, що залежність величин обумовлює наявність кореляційного зв'язку між ними, але не навпаки.
2. Наявність сполученості (загальної причини). Наприклад, зі зростанням організації зростає фонд оплати праці (ФОП) та витрати на оренду приміщень. Очевидно, що неправильно припускати, що оренда приміщень залежить від ФОП. Обидві цих змінних у часто лінійно залежать від кількості персоналу.
3. Взаємовплив змінних (при зміні однієї, друга змінна змінюється, і навпаки). За такого підходу допустимі дві постановки завдання; Будь-яка змінна може бути як у ролі незалежної змінної й у ролі залежної.

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

Кореляція, як і будь-який інший статистичний показник, при правильному застосуванні може бути корисною, але вона також має обмеження щодо використання. Якщо показує чітко виражену лінійну залежність чи повну відсутність взаємозв'язку, то кореляціячудово це відобразить. Але якщо дані показують нелінійний взаємозв'язок (наприклад, квадратичний), наявність окремих груп значень або викидів, то обчислене значення коефіцієнта кореляціїможе ввести в оману (див. файл прикладу).

Кореляціяблизька до 1 або -1 (тобто близька за модулем до 1) показує сильний лінійний взаємозв'язок змінних, значення близьке до 0 показує відсутність взаємозв'язку. Позитивна кореляціяозначає, що зі зростанням одного показника інший у середньому збільшується, а при негативному – зменшується.

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

• кількість змінних має дорівнювати двом;
• змінні мають бути кількісними (наприклад, частота, вага, ціна). Обчислене середнє значення цих змінних має зрозуміле значення: Середня цінаабо середня вага пацієнта. На відміну від кількісних, якісні (номінальні) змінні набувають значення лише з кінцевого набору категорій (наприклад, стать або група крові). Цим значенням умовно зіставлені числові значення (наприклад, жіноча стать – 1, а чоловіча – 2). Зрозуміло, що в цьому випадку обчислення середнього значення, яка потрібна для знаходження кореляції, некоректно, а значить некоректно і обчислення самої кореляції;
• змінні повинні бути випадковими величинами та мати .

Двовимірні дані можуть мати різну структуру. Для роботи з деякими з них потрібні певні підходи:

• Для даних з нелінійним зв'язком кореляціюпотрібно використовувати з обережністю. Для деяких завдань буває корисно перетворити одну або обидві змінні так, щоб отримати лінійний взаємозв'язок (для цього потрібно зробити припущення про вид нелінійного зв'язку, щоб запропонувати потрібний тип перетворення).
• За допомогою діаграми розсіюванняу деяких даних можна спостерігати нерівну варіацію (розкид). Проблема неоднакової варіації у тому, що з високої варіацією як надають найменш точну інформацію, а й мають найбільший вплив під час розрахунку статистичних показників. Цю проблему часто вирішують за допомогою перетворення даних, наприклад, за допомогою логарифмування.
• У деяких даних можна спостерігати поділ на групи (clustering), що може свідчити необхідність поділу сукупності на частини.
• Викид (різно відхиляється) може спотворити обчислене значення коефіцієнта кореляції. Викид може бути причиною випадковості, помилки при зборі даних або можуть справді відображати певну особливість взаємозв'язку. Так як викид сильно відхиляється від середнього значення, то він робить великий внесок при розрахунку показника. Часто розрахунок статистичних показників виробляють з і без урахування викидів.

Використання MS EXCEL для розрахунку кореляції

Як приклад візьмемо 2 змінні Хі Yі відповідно, вибіркущо складається з кількох пар значень (Х i ; Y i). Для наочності побудуємо.

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

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

Примітка: У файлі прикладу можна задати параметри лінійного тренду (нахил, перетин з віссю Y) і ступінь розкиду щодо цієї лінії тренду. Також можна налаштувати параметри квадратичної залежності.

У файлі прикладу для побудови діаграми розсіюванняу разі відсутності залежності змінних використано діаграму типу Точкова. І тут точки на діаграмі розташовуються як хмари.

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

Як було сказано вище, для розрахунку коефіцієнта кореляціїв MS EXCEL існує функцій Корел (). Також можна скористатися аналогічною функцією PEARSON(), яка повертає той самий результат.

Для того, щоб переконатися, що обчислення кореляціївиробляються функцією КОРРЕЛ() за вищезгаданими формулами, у файлі прикладу наведено обчислення кореляціїза допомогою більш докладних формул:

=КОВАРІАЦІЯ.Г(B28:B88;D28:D88)/СТАНДОТКЛОН.Г(B28:B88)/СТАНДОТКЛОН.Г(D28:D88)

=КОВАРІАЦІЯ.В(B28:B88;D28:D88)/СТАНДОТКЛОН.В(B28:B88)/СТАНДОТКЛОН.В(D28:D88)

Примітка: Квадрат. коефіцієнта кореляції r дорівнює коефіцієнт детермінації R2, який обчислюється при побудові лінії регресії за допомогою функції КВПІРСОН(). Значення R2 також можна вивести на діаграмі розсіювання, побудувавши лінійний тренд за допомогою стандартного функціоналу MS EXCEL (виділіть діаграму, виберіть вкладку Макет, потім у групі Аналізнатисніть кнопку Лінія трендута виберіть Лінійне наближення). Докладніше про побудову лінії тренду див., наприклад, в .

Використання MS EXCEL для розрахунку коваріації

Коваріаціяблизька за змістом з (також є мірою розкиду) з тією відмінністю, що вона визначена для 2-х змінних, а дисперсія- Для однієї. Тому cov(x;x)=VAR(x).

Для обчислення коваріації в MS EXCEL (починаючи з версії 2010 року) використовуються функції КОВАРІАЦІЯ.Г() та КОВАРІАЦІЯ.В(). У першому випадку формула для обчислення аналогічна вищезазначеній (закінчення .Гпозначає Генеральна сукупність ), у другому – замість множника 1/n використовується 1/(n-1), тобто. закінчення .позначає Вибірка.

Примітка: Функція КОВАР(), яка присутня в MS EXCEL більш ранніх версій, аналогічна функції КОВАРІАЦІЯ.Г().

Примітка: Функції КОРРЕЛ() та КОВАР() в англійській версії представлені як CORREL та COVAR. Функції КОВАРІАЦІЯ.Г() та КОВАРІАЦІЯ.В() як COVARIANCE.P та COVARIANCE.S.

Додаткові формули для розрахунку підступи:

=СУМПРОВИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88-СРЗНАЧ(D28:D88)))/РАХУНОК(D28:D88)

=СУМПРОВИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88))/РАХУНОК(D28:D88)

=СУМПРОВИЗВ(B28:B88;D28:D88)/РАХУНОК(D28:D88)-СРЗНАЧ(B28:B88)*СРЗНАЧ(D28:D88)

Ці формули використовують властивість підступи:

Якщо змінні xі yнезалежні, їх коваріація дорівнює 0. Якщо змінні є незалежними, то дисперсія їх суми дорівнює:

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсіяїх різниці дорівнює

VAR(x-y) = VAR(x) + VAR(y)-2COV(x;y)

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

Щоб перевірити гіпотезу, ми повинні знати розподіл випадкової величини, тобто. коефіцієнта кореляції r. Зазвичай перевірку гіпотези здійснюють не для r, а для випадкової величини t r:

яка має з n-2 ступенями свободи.

Якщо обчислене значення випадкової величини | t r | більше, ніж критичне значення t α,n-2 (α-заданий ), то нульову гіпотезу відхиляють (взаємозв'язок величин є статистично значущою).

Надбудова Пакет аналізу

Для обчислення коваріації та кореляції є однойменні інструменти аналізу.

Після виклику інструмента з'являється діалогове вікно, яке містить такі поля:

• Вхідний інтервал: потрібно ввести посилання на діапазон з вихідними даними для 2-х змінних
• Групування: як правило, вихідні дані вводяться в 2 стовпці
• Мітки у першому рядку: якщо встановлена ​​галочка, то Вхідний інтервалповинен містити заголовки стовпців. Рекомендується встановлювати галочку, щоб результат роботи Надбудови містив інформативні стовпці
• Вихідний інтервал: діапазон осередків, куди будуть розміщені результати обчислень. Достатньо вказати ліву верхню комірку цього діапазону.

Надбудова повертає обчислені значення кореляції та коваріації (для коваріації також обчислюються дисперсії обох випадкових величин).