Как выполнить регрессионный анализ в Excel за 9 шагов (плюс советы)

8 апреля 2022 г.

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

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

Что такое регрессионный анализ?

Регрессионный анализ относится к математическим методам, которые позволяют исследователям выявлять тенденции в наборах данных. Вы можете использовать регрессионный анализ, чтобы определить взаимосвязь между различными переменными. Регрессионный анализ может облегчить предсказание будущих трендов переменных путем анализа траектории линии регрессии. Например, если вы проводили регрессионный анализ, чтобы понять взаимосвязь между переменной x и переменной y, направление линии регрессии может дать информацию о характере этой взаимосвязи. Вот три наиболее распространенных типа моделей регрессионного анализа:

Простая линейная регрессия

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

Y = а + bX + ϵ

Где:

• Y = зависимая (отклик) переменная

• X = независимая (объясняющая) переменная

• b = наклон (крутизна линии регрессии)

• a = точка пересечения (где линия пересекает ось)

• ϵ = остаток регрессии (ошибка)

Множественная линейная регрессия

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

Y = b0 + b1X1 + b1 + b2X2 +…+ bpXp

Где:

• Y = зависимая (отклик) переменная

• X1, X2, Xp… = независимые (объясняющие) переменные

• b0 = Y, когда все независимые переменные равны 0

• b1, b2, bp… = коэффициенты регрессии

Нелинейная регрессия

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

Когда делать регрессионный анализ

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

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

Как сделать регрессионный анализ в Excel

Если вы хотите провести регрессионный анализ, один из самых доступных способов сделать это — использовать приложение Microsoft Excel на вашем компьютере. Вот несколько шагов, которые вы можете выполнить при выполнении собственного регрессионного анализа с помощью Excel:

1. Введите свои данные в Excel

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

2. Установите плагин Data Analysis ToolPak

Следующий шаг — убедиться, что на вашем компьютере установлено бесплатное программное обеспечение Data Analysis ToolPak. Эта программа предоставляет необходимые инструменты для расчета широкого спектра статистических запросов, включая регрессионный анализ. Чтобы проверить, есть ли у вас пакет инструментов анализа данных, откройте приложение Excel и перейдите на верхнюю панель на вкладку «Данные» и посмотрите, заполняется ли вкладка «Анализ данных». Если это не так, нажмите «Инструменты анализа», а затем «Пакет инструментов анализа данных», чтобы установить.

3. Откройте «Анализ данных», чтобы открыть диалоговое окно.

После того, как вы успешно загрузили подключаемый модуль Data Analysis ToolPak, вы можете начать свои расчеты. Для этого откройте приложение Excel и щелкните вкладку «Данные» на верхней панели и перейдите к кнопке «Анализ данных», чтобы открыть диалоговое окно. Когда поле заполнится, выберите «Регрессия» и нажмите «ОК», чтобы начать ввод переменных данных.

4. Введите переменные данные

Чтобы начать регрессионный анализ, щелкните поле «Входной диапазон Y» и введите диапазон, в котором вы хотите произвести расчет. Например, если вы пытаетесь вычислить зависимую переменную, и она находится в столбце C с числами, начинающимися после третьей строки и заканчивающимися 20-й строкой, вы должны ввести $C$3:$C$20. Выполните те же шаги при вводе данных независимой переменной, настраивая различные метки столбцов.

5. Выберите параметры вывода

Следующим шагом является выбор параметров вывода, чтобы определить, как вы хотите, чтобы Excel отображал результаты после их расчета. После ввода входных данных установите флажок «Ярлыки» и перейдите вниз к разделу «Параметры вывода». Выберите всплывающее окно «Диапазон вывода» и введите столбец и строку, в которые вы хотите заполнить сводку. Затем установите флажок «Остатки» и нажмите «ОК», чтобы запустить расчет.

6. Проанализируйте свои результаты

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

7. Создайте точечный график

Чтобы превратить ваши значения регрессии в наглядную диаграмму, начните с выделения столбцов данных, включая их заголовки, и нажмите «Вставить» на верхней панели. Перейдите к «Рекомендуемым диаграммам» и нажмите на точечную диаграмму. После того, как вы выберете параметр рассеяния, Excel создаст точечную диаграмму ваших данных на вашем листе.

8. Добавьте линию тренда регрессии

Чтобы добавить линию тренда, щелкните правой кнопкой мыши любую точку на диаграмме рассеяния, чтобы открыть меню, в котором вы можете перейти к кнопке «Добавить линию тренда». Поле «Формат линии тренда» появится на правой панели экрана, где вы можете выбрать параметр «Линейный» в раскрывающемся списке «Параметры линии тренда». Затем прокрутите вниз и установите флажок рядом с параметром «Отображать уравнение на диаграмме», чтобы включить формулу регрессии в диаграмму.

9. Добавьте последние эстетические штрихи

Последним шагом при выполнении регрессионного анализа в Excel является настройка диаграммы в соответствии с вашими личными предпочтениями. Для этого вы можете выбрать вкладку «Заливка и линия», представленную ведром с краской, на панели «Формат линии тренда». Здесь вы можете настроить размер, цвет, прозрачность и ширину линии тренда. Другие эстетические настройки, которые вы можете внести, например, пометив свои оси, щелкнув «Элементы диаграммы» и «Названия осей» и перетащив уравнение туда, где вы предпочитаете, чтобы оно было на диаграмме.

Советы по проведению регрессионного анализа в Excel

Вот несколько советов, которым вы можете следовать при попытке запустить собственный регрессионный анализ с помощью Microsoft Excel:

• Дважды проверьте свои данные. Чтобы убедиться, что ваш анализ данных максимально точен, попробуйте перепроверить свои данные до и после ввода их в Excel. Это может помочь вам определить потенциальные расхождения в ваших данных, которые вы можете заблаговременно исправить и использовать для получения полезного и информативного регрессионного анализа.

• Отобразите значение R2 на диаграмме. Еще один совет при проведении регрессионного анализа — включить значение R2 в диаграмму, чтобы предоставить дополнительную информацию о силе взаимосвязи между переменными. Это полезно для тех, кто использует вашу диаграмму, чтобы сделать выводы относительно корреляции между зависимой и независимой переменной, не обращаясь к сводной выходной таблице.

• Сохраните свои данные. Попробуйте сохранить все ваши входные данные, чтобы использовать их для будущего анализа, диаграмм или любых других расчетов, которые могут вам понадобиться при использовании Excel. Это может упростить поиск важных данных и обращение к ним без необходимости вводить их каждый раз, когда вы выполняете расчет или анализ.

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.