Если вы когда-нибудь хотели найти взаимосвязь между двумя вещами, использование регрессионного анализа в Excel является одним из лучших способов сделать это.
Регрессия в Excel — это способ автоматизировать статистический процесс сравнения нескольких наборов информации, чтобы увидеть, как изменения в независимых переменных влияют на изменения в зависимых переменных.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365 и Excel для Mac.
В чем смысл регрессии?
Регрессия — это подход статистического моделирования, который аналитики используют для определения отношений между несколькими переменными.
Регрессионный анализ начинается с одной переменной, которую вы пытаетесь проанализировать, и независимых переменных, которые вы тестируете, чтобы увидеть, влияют ли они на эту единственную переменную. Анализ рассматривает изменения в независимых переменных и пытается соотнести эти изменения с результирующими изменениями в одной (зависимой) переменной.
Это может звучать как расширенная статистика, но Excel делает этот сложный анализ доступным для всех.
Выполнение линейной регрессии в Excel
Простейшей формой регрессионного анализа является линейная регрессия. Простая линейная регрессия смотрит на отношения только между двумя переменными.
Например, в следующей таблице приведены данные, содержащие количество калорий, которые человек съел каждый день, и их вес в этот день.
Поскольку эта электронная таблица содержит два столбца данных, и одна переменная может оказать влияние на другую, вы можете выполнить регрессионный анализ этих данных с помощью Excel.
Прежде чем вы сможете использовать функцию регрессионного анализа в Excel, вам необходимо включить дополнение Analysis ToolPak.
-
Выберите меню « Файл» и выберите « Параметры» .
-
Выберите Надстройки в левом меню навигации. Затем убедитесь, что надстройки Excel выбраны в поле « Управление» . Наконец, нажмите кнопку « Перейти» .
-
Во всплывающем окне Надстройки. Включите пакет инструментов анализа и выберите ОК .
-
Теперь, когда Analysis ToolPak включен, вы готовы начать выполнять регрессионный анализ в Excel.
Как выполнить простую линейную регрессию в Excel
Используя электронную таблицу веса и калорий в качестве примера, вы можете выполнить линейный регрессионный анализ в Excel следующим образом.
-
Выберите меню « Данные» . Затем в группе Анализ выберите Анализ данных .
-
В окне « Анализ данных» выберите « Регрессия» из списка и нажмите « ОК» .
-
Диапазон ввода Y — это диапазон ячеек, который содержит зависимую переменную. В этом случае это вес. Диапазон ввода X — это диапазон ячеек, который содержит независимую переменную. В данном случае это столбец калорий. Выберите « Метки» для ячеек заголовка, а затем выберите « Новый рабочий лист», чтобы отправить результаты на новый рабочий лист.
-
Выберите OK, чтобы Excel запустил анализ и отправил результаты на новый лист. Результаты анализа имеют ряд значений, которые вам необходимо понять, чтобы интерпретировать результаты.
Каждое из этих чисел имеет следующие значения:
- Несколько R : Коэффициент корреляции. 1 означает, что существует сильная корреляция между двумя переменными. -1 означает, что есть сильные негативные отношения. 0 означает, что корреляции нет.
- Квадрат R : Коэффициент определения, который показывает, сколько точек между двумя переменными попадают на линию регрессии. По статистике, это сумма квадратов отклонений от среднего.
- Скорректированный квадрат R : статистическое значение, называемое квадратом R, которое корректируется на количество выбранных вами независимых переменных.
- Стандартная ошибка : насколько точны результаты регрессионного анализа. Если эта ошибка мала, то ваши результаты регрессии являются более точными.
- Наблюдения : количество наблюдений в вашей регрессионной модели.
Остальные значения в выходных данных регрессии дают подробные сведения о более мелких компонентах в регрессионном анализе.
- df : Статистическая ценность, известная как степени свободы, связанные с источниками отклонения .
- С.С . : Сумма квадратов. Отношение остаточной суммы квадратов к общей СС должно быть меньше, если большая часть ваших данных соответствует линии регрессии.
- МС : средний квадрат данных регрессии.
- F : F статистика (F-тест) для нулевой гипотезы. Это обеспечивает значимость регрессионной модели.
- Значение F : Статистическая величина, известная как P-значение F.
Если вы не понимаете статистику и расчет регрессионных моделей, значения в нижней части сводки не будут иметь большого значения. Тем не менее, множественные квадраты R и R являются двумя наиболее важными.
Как вы можете видеть в этом примере, калории имеют очень сильную корреляцию с общим весом.
Анализ множественной линейной регрессии в Excel
Чтобы выполнить ту же линейную регрессию, описанную выше, но с несколькими независимыми переменными, вы можете просто выбрать весь диапазон (несколько столбцов и строк) для Input X Range .
При выборе нескольких независимых переменных менее вероятно, что они найдут сильную корреляцию, потому что существует очень много переменных.
Однако регрессионный анализ в Excel может помочь вам найти корреляции с одной или несколькими переменными, которые, возможно, вам не известны, просто просмотрев данные вручную.