Як розрахувати кореляцію в microsoft excel

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

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

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

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

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

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

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

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

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

Аналіз даних і графіків кореляції дозволяє поліпшити її достовірність дуже істотно. Грунтуючись на отриманих результатах можна сказати, що курси корелюють в періоди нормальної економіки (нема на піку і дні) на 81% за формулою EUR / RUR = 23.279EUR / USD + 7.2776.

Розрахунки ви можете подивитися, скачавши файл внизу.

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