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

Добавил пользователь Skiper
Обновлено: 04.10.2024

Зависимая переменная — это фактор, который мы пытаемся оценить.
Независимая переменная — это то, что влияет на зависимую переменную.

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

Как добавить инструмент анализа данных линейной регрессии в Excel?

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

Вы можете скачать этот шаблон Excel для линейной регрессии здесь — Шаблон для Excel для линейной регрессии

Шаг 1: Перейдите в ФАЙЛ >> Параметры.

Примеры

У нас есть данные о средней температуре и проданных куртках за каждый месяц. Здесь нам нужно знать, какие переменные являются независимыми, а какие — зависимыми.

«Куртки проданы” является зависимой переменной, потому что в зависимости от повышения и понижения температуры продажи куртки варьируются.

Теперь мы проведем анализ линейной регрессии в Excel для этих данных.

Шаг 1: Щелкните вкладку Данные и Анализ данных.

Шаг 6: Выберите выходной диапазон как одну из ячеек.

Шаг 8: Нажмите ОК; у нас будет анализ ниже.

Несколько R: Этот расчет относится к коэффициенту корреляции, который измеряет силу линейной связи между двумя переменными. Коэффициент корреляции — это значение от -1 до 1.

1 Указывает на сильные позитивные отношения.
-1 указывает на сильные отрицательные отношения.
0 означает отсутствие связи.

R квадрат: Это коэффициент детерминации, который используется для определения степени соответствия.

Скорректированный квадрат R: Это скорректированное значение R Square на основе количества независимых переменных в наборе данных.

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

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

Ключевые выводы

Линейная регрессия моделирует отношения между зависимой и независимой переменной (ами).
Регрессионный анализ может быть проведен, если переменные независимы, нет гетероскедастичности и члены ошибок переменных не коррелированы.
Моделирование линейной регрессии в Excel стало проще с помощью пакета Data Analysis ToolPak.

Важные соображения

Есть несколько важных предположений о вашем наборе данных, которые должны быть верными, чтобы продолжить регрессионный анализ :

Переменные должны быть действительно независимыми (с использованием критерия хи-квадрат ).
Данные не должны иметь различную дисперсию ошибок (это называется гетероскедастичностью (также обозначается как гетероскедастичность)).
Члены ошибки каждой переменной не должны коррелировать. Если нет, это означает, что переменные последовательно коррелированы.

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

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

Используя Data Analysis ToolPak, создать результат регрессии можно всего за несколько щелчков мышью.

Краткий обзор

Независимая переменная находится в диапазоне X.

Учитывая доходность S&P 500 , скажем, мы хотим знать, можем ли мы оценить силу и взаимосвязь доходностей акций Visa (

[Примечание: если таблица кажется маленькой, щелкните изображение правой кнопкой мыши и откройте новую вкладку для более высокого разрешения.]

Интерпретируйте результаты

Используя эти данные (те же, что и в нашей статье о R-квадрате ), мы получаем следующую таблицу:

Значение R 2, также известное как коэффициент детерминации, измеряет долю вариации в зависимой переменной, объясняемую независимой переменной, или насколько хорошо регрессионная модель соответствует данным. Значение R 2 находится в диапазоне от 0 до 1, и более высокое значение указывает на лучшее соответствие. Значение p или значение вероятности также находится в диапазоне от 0 до 1 и указывает, является ли тест значимым. В отличие от значения R 2, меньшее значение p является благоприятным, поскольку оно указывает на корреляцию между зависимыми и независимыми переменными.

Построение графика регрессии в Excel

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

Подключение пакета анализа

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

Виды регрессионного анализа

Существует несколько видов регрессий:

параболическая;
степенная;
логарифмическая;
экспоненциальная;
показательная;
гиперболическая;
линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе Excel

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк . В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

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

Разбор результатов анализа

Результаты регрессионного анализа выводятся в виде таблицы в том месте, которое указано в настройках.

Одним из основных показателей является R-квадрат. В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Многофакторный регрессионный анализ в excel пример. Регрессионный анализ в excel

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

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

Общий вид модели линейной регрессии:

Y=a 0 +a 1 x 1 +. +a k x k

где a — параметры (коэффициенты) регрессии, x — влияющие факторы, k — количество факторов модели.

Исходные данные

Среди исходных данных нам необходим некий набор данных, который бы представлял из себя несколько последовательных или связанных между собой величин итогового параметра Y (например, ВВП) и такое же количество величин показателей, влияние которых мы изучаем (например, иностранные инвестиции).

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

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

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

Пакет анализа

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

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

Активируем Пакет анализа

По умолчанию эта надстройка отключена и в меню вкладок вы ее не найдете, поэтому пошагово рассмотрим как ее активировать.

В эксель, слева вверху, активируем вкладку Файл , в открывшемся меню ищем пункт Параметры и кликаем на него.

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

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

Инструкция по поиску параметров линейной регрессии с помощью Пакета анализа

После активации надстройки Пакета анализа она будет всегда доступна во вкладке главного меню Данные под ссылкой Анализ данных

В активном окошке инструмента Анализа данных из списка возможностей ищем и выбираем Регрессия

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

После того как выбрали исходные данные и нажали кнопочку ОК, Excel выдает расчеты на новом листе активной книги (если в настройках не было выставлено иначе), эти расчеты имеют следующий вид:

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

Итак, 0,865 — это R 2 — коэффициент детерминации, показывающий что на 86,5% расчетные параметры модели, то есть сама модель, объясняют зависимость и изменения изучаемого параметра — Y от исследуемых факторов — иксов . Если утрировано, то это показатель качества модели и чем он выше тем лучше. Понятное дело, что он не может быть больше 1 и считается неплохо, когда R 2 выше 0,8, а если меньше 0,5, то резонность такой модели можно смело ставить под большой вопрос.

Теперь перейдем к коэффициентам модели :
2079,85 — это a 0 — коэффициент который показывает какой будет Y в случае, если все используемые в модели факторы будут равны 0, подразумевается что это зависимость от других неописанных в модели факторов;
-0,0056 — a 1 — коэффициент, который показывает весомость влияния фактора x 1 на Y, то есть количество предприятий в пределах данной модели влияет на показатель экономически активного населения с весом всего -0,0056 (довольно маленькая степень влияния). Знак минус показывает что это влияние отрицательно, то есть чем больше предприятий, тем меньше экономически активного населения, как бы это ни было парадоксальным по смыслу;
-0,0026 — a 2 — коэффициент влияния объема инвестиций в капитал на величину экономически активного населения, согласно модели, это влияние также отрицательно;
0,0028 — a 3 — коэффициент влияния доходов населения на величину экономически активного населения, здесь влияние позитивное, то есть согласно модели увеличение доходов будет способствовать увеличению величины экономически активного населения.

Соберем рассчитанные коэффициенты в модель:

Y = 2079,85 — 0,0056x 1 — 0,0026x 2 + 0,0028x 3

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

Расчетные значения модели и прогноз

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

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

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

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

Пакет MS Excel позволяет при построении уравнения линейной регрессии большую часть работы сделать очень быстро. Важно понять, как интерпретировать полученные результаты. Для построения модели регрессии необходимо выбрать пункт СервисАнализ данныхРегрессия (в Excel 2007 этот режим находится в блоке Данные/Анализ данных/Регрессия). Затем полученные результаты скопировать в блок для анализа.

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

Основными типами нелинейных регрессий являются:

полиномиальные (квадратичная, кубическая);
гиперболическая;
степенная;
показательная;
логарифмическая.

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

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

MS Office и VBA Рубрика содержит интересные решения, малоизвестные функции и возможности, надстройки и макросы, в общем, все то, что может сделать вашу работу в пакете программ MS Office (в первую очередь - Excel, Word, Access) более эффективной.

Линейная регрессия в Excel через Анализ данных

4.9 (49) | 120149 | 2

Научимся строить линейную регрессионную модель с несколькими влияющими факторами в Эксель всего в несколько кликов с помощью встроенного Пакета анализа.