Как использовать множественную регрессию в Excel

Excel — мощный инструмент для анализа данных, независимо от того, работаете ли вы с простой взаимосвязью между одной независимой переменной и зависимой переменной или с несколькими независимыми переменными, которые необходимо учитывать. Изучение того, как выполнять многомерный анализ в Excel — в форме множественной регрессии — и интерпретировать результаты, важно, если вам нужно работать со сложными данными. Хорошей новостью является то, что Excel хорошо настроен для выполнения этих задач, и вам нужно всего лишь узнать, как работает одна функция, чтобы начать понимать свои данные.

Что такое множественная регрессия?

Множественная регрессия — это способ связывания нескольких независимых переменных с одной зависимой переменной путем нахождения уравнения, описывающего, как рассматриваемая переменная изменяется с каждой из них. Более простым, но похожим инструментом является линейная регрессия, целью которой является исследование связи между одной независимой переменной, такой как ожирение, и зависимой переменной, такой как риск развития рака, но все редко бывает так просто. Продолжая пример, количество выкуриваемых сигарет в день также связано с риском развития рака, как и количество выпитого алкоголя. Чтобы сделать надежный прогноз риска развития рака у отдельного человека, необходимо принять во внимание все эти (и многие другие) факторы.

Общая форма уравнения, используемого для множественной регрессии:

Да ^ =а + b х + b х + b х …

Таким образом, Y ^ — ожидаемое значение для наблюдения, b и т. д. представляют наклон прямой зависимости между x и Д ^ и x и так далее — переменные, включенные в анализ. а говорит вам о точке y -перехват. Множественная регрессия включает выбор значений коэффициентов (b и т. д.), которые минимизируют разницу между ожидаемым значением Y ^ и наблюдаемое значение Y , что обеспечивает наилучшее соответствие между моделью и данными.

Что вам говорит множественная регрессия?

Множественные регрессии присваивают числовые значения связи между множеством переменных и результатом, поэтому вы можете использовать их для прогнозов, для оценки относительного вклада различных переменных в результат или для некоторых других целей, таких как выбор наиболее релевантных переменных. для использования в математической модели.

Например, предположим, что у вас есть данные о ценах на дома в определенном городе (ваша зависимая переменная), а также информация о том, есть ли в нем бассейн, сколько квадратных футов он занимает, сколько в нем спален, сколько в нем ванных комнат. , и сколько у него гаражей. Множественная регрессия позволит вам посмотреть, как каждый из этих факторов связан с ценой дома, поэтому — после того, как вы изучите, как они связаны с ценой — вы можете использовать свое уравнение для прогнозирования цены дома на основе этих факторов. только очки.

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

Как выполнить множественную регрессию в Excel

Вы можете выполнить многомерную регрессию в Excel, используя встроенную функцию, доступную через Анализ данных. инструмент в разделе Данные и вкладку Анализ. группа. Нажмите Анализ данных. и найдите параметр регрессия в появившемся окне выделите его и нажмите ОК . Нажмите выбрать ячейки. значок рядом с Введите диапазон Y поле, а затем выберите столбец, содержащий результаты для вашей зависимой переменной. Затем сделайте то же самое для Input X Range. поле, но выберите несколько столбцов для ваших независимых переменных. Эти столбцы должны быть рядом друг с другом, поэтому, если это не так, вы должны переместить их перед созданием регрессии.

Окно регрессии имеет ряд дополнительных опций, которые вы можете выбрать, чтобы настроить процесс в соответствии с вашими потребностями. Например, вы можете установить уровень достоверности, отличный от 95 процентов, если хотите, выбрать отображение остатков и указать, где выходные данные помещаются в вашу книгу. Для последнего параметра автоматически устанавливается значение Новый слой рабочего листа. поэтому результаты отображаются на новом листе, но вы можете изменить этот или любой другой параметр в соответствии со своими потребностями. Кроме того, проверьте Ярлыки поле, если столбцы для ваших независимых переменных имеют метки вверху, чтобы они отображались в выходных данных.

Нажмите ОК. чтобы сгенерировать регрессионный анализ в Excel и перейти на новый лист.

Вывод регрессии из Excel

После выполнения множественной регрессии в Excel есть три основных раздела выходных данных:статистика регрессии, дисперсионный анализ и детали расчетной линии регрессии. Статистика регрессии включает коэффициент множественной корреляции («Multiple R»), который показывает направление и силу корреляции от -1 до +1. Коэффициент детерминации, «Квадрат R», говорит вам, какой процент (в десятичном выражении) вариации зависимой переменной объясняется независимыми переменными. «Скорректированный R-квадрат» дает вам представление об объяснительной способности, но его не так просто интерпретировать, а «Стандартная ошибка» дает вам меру отклонения между наблюдаемыми результатами и вашей линией регрессии.

Раздел ANOVA содержит статистическую информацию о величине вариации, объясненной линией регрессии, где «Регрессия SS» показывает величину, объясненную линией, а «Остаток SS» представляет необъяснимую величину. Разделы «MS» означают «среднеквадратичное значение», а «F-статистика» — это тестовая статистика, используемая для проверки значимого результата, а раздел «Значимость F» дает вам P-значение.

Наконец, в последнем разделе рассказывается о характеристиках оценочной линии регрессии, в частности, о значениях коэффициентов, о том, существенно ли они связаны с зависимой переменной, и о возможных вариациях в них. Положительные коэффициенты показывают положительную связь между рассматриваемой переменной и зависимой переменной, поэтому, когда увеличивается одна, увеличивается и другая. Отрицательные значения означают, что зависимая переменная уменьшается по мере увеличения независимой переменной. Таким образом, если коэффициент «квадратный метр» при множественной регрессии цен на жилье равен 300, это означает, что дополнительный квадратный метр площади увеличивает стоимость дома в среднем на 300 долларов США.

Допущения и ограничения множественной регрессии

Важно помнить, что множественная регрессия — это всего лишь инструмент, и, как и большинство инструментов, вы можете использовать его только в определенных обстоятельствах, и есть вещи, которые он просто не может сделать.

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

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