Як обчислити R2 Excel

У фінансах і статистиці, в коефіцієнт детермінації, також іменований як R-квадрат (або Р²) є мірою зв'язку між двома наборами даних, що використовуються в математичній моделі. Він представляє відношення дисперсії залежної змінної, яку можна передбачити з незалежної змінної в моделі. Він часто використовується в регресійному аналізі для оцінки прогнозів майбутніх результатів на основі спостережуваних результатів. Ви можете обчислити R-квадрат в Excel за допомогою функції RSQ.

У Microsoft Excel функція RSQ використовується для визначення значення R-квадрат для двох наборів точок даних. Функція повертає квадрат коефіцієнта кореляції моменту Пірсона, який вимірює лінійну кореляцію між змінними x і y. Коефіцієнт кореляції завжди знаходиться в межах -1 і +1. Значення, яке повертає RSQ в Excel, завжди знаходиться в межах від 0 до 1 (оскільки воно обчислюється як квадрат коефіцієнта кореляції, воно ніколи не може повернути від’ємне значення).

Функція RSQ приймає в якості аргументів два набори даних, які називаються відомим_х і відомим_у. Ці набори даних можуть бути у формі списку чисел або списку чи діапазону посилань на клітинки. Наприклад, скажімо, що ви хотіли зробити регресійний аналіз грошей, витрачених на рекламу, і дохід від продажу, де щомісячні витрати на рекламу вказані в колонці А, а місячний дохід — у колонці Б. Ви можете використовувати функцію RSQ, ввівши RSQ(A1:A10,B1:B10), яка використовує значення в рядках з 1 по 10 зі стовпців A (витрати на рекламу) і B (дохід).

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

Функції CORREL і PEARSON повертають значення від -1 до 1. Це безрозмірна міра позитивної чи негативної кореляції між двома наборами даних, наданими як аргументи. Значення, яке повертає функція RSQ, становить від 0 до 1, іноді виражається у відсотках від 0 до 100. Багато аналітиків вважають, що вищий результат RSQ вказує на більш точну математичну модель, в той час як інші скажімо, що перед розіграшем важливо розглянути всі фактори, які можуть спотворити високий або низький результат висновки.

Експерти також кажуть, що слід уникати порівняння значень R-квадрата для різних моделей і наборів даних. У випадках, коли між типами даних, що порівнюються, є великі відмінності, результати можуть бути оманливими. Існують більш складні заходи для порівняння моделей, ніж значення R-квадрат, такі як F-тести та інформаційні критерії.

Точкова діаграма Excel найчастіше використовується для відображення зв’язків між наборами даних під час регресійного аналізу. Діапазон значень для одного набору даних показано на горизонтальній осі x, а діапазон для іншого набору — на вертикальній осі y. Точки даних відображаються на перетині значень x і y за допомогою пар значень з кожного набору даних.

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