Как рассчитать R2 в Excel

В финансах и статистике коэффициент детерминации, также называемый R-квадрат (или р²) - это мера взаимосвязи между двумя наборами данных, используемыми в математической модели. Он представляет собой коэффициент дисперсии зависимой переменной, который можно предсказать на основе независимой переменной в модели. Он часто используется в регрессионном анализе для оценки прогнозов будущих результатов на основе наблюдаемых результатов. Вы можете рассчитать R-квадрат в Excel с помощью функции RSQ.

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

Функция RSQ принимает в качестве аргументов два набора данных, называемых известными_x и известными_y. Эти наборы данных могут быть в форме списка чисел, списка или диапазона ссылок на ячейки. Например, предположим, что вы хотите провести регрессионный анализ денег, потраченных на рекламу, по сравнению с доход от продаж, где ежемесячные расходы на рекламу указаны в столбце A, а ежемесячный доход - в столбце B. Вы можете использовать функцию RSQ, введя RSQ (A1: A10, B1: B10), который использует значения в строках с 1 по 10 из столбцов A (расходы на рекламу) и B (доход).

Excel также позволяет рассчитать коэффициент корреляции для двух наборов данных с помощью функций КОРРЕЛЬ и ПИРСОН. Как и функция RSQ, CORREL и PEARSON принимают в качестве аргументов два диапазона значений ячеек. Взять результат КОРРЕЛ или ПИРСОН для нахождения коэффициента корреляции и возвести результат в квадрат - это то же самое, что использовать функцию RSQ для определения коэффициента детерминации.

Функции КОРРЕЛ и ПИРСОН возвращают значения от -1 до 1. Это безразмерная мера положительной или отрицательной корреляции между двумя наборами данных, предоставленных в качестве аргументов. Возвращаемое значение функции RSQ находится в диапазоне от 0 до 1, иногда выражается в процентах от 0 до 100. Многие аналитики считают, что более высокий результат RSQ указывает на более точную математическую модель, в то время как другие говорят, что перед рисованием важно посмотреть на все факторы, которые могут исказить высокий или низкий результат выводы.

Эксперты также говорят, что вам следует избегать сравнения значений R-квадрата для разных моделей и наборов данных. В случае значительных различий между типами сравниваемых данных результаты могут вводить в заблуждение. Существуют более сложные меры для сравнения моделей, чем значения R-квадрата, такие как F-тесты и информационные критерии.

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

Используя пример рекламы и продаж, где расходы на рекламу указаны в столбце A, а ежемесячный доход - в столбце B, горизонтальная ось покажет диапазон ежемесячного дохода, а вертикальная - диапазон рекламы. расходы. Точки данных на диаграмме будут построены по соседним ячейкам в столбце A и столбце B. Полученный паттерн точек можно использовать для визуализации степени корреляции между переменными.