Регрессия в Excel: уравнение, примеры. Линейная регрессия

Значение методик корреляционного и регрессионного анализа для статистики. Как найти нужные коэффициенты с помощью средств Excel в два клика. Построение поля корреляции.

Регрессионный анализ в Excel

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

Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

Регрессия бывает:

  • линейной (у = а + bx);
  • параболической (y = a + bx + cx2);
  • экспоненциальной (y = a * exp(bx));
  • степенной (y = a*x^b);
  • гиперболической (y = b/x + a);
  • логарифмической (y = b * 1n(x) + a);
  • показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

Зарплата сотрудников.

Модель линейной регрессии имеет следующий вид:

У = а0 + а1х1 +…+акхк.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

  1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».Надстройки.
  2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.Управление.
  3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

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

После активации надстройка будет доступна на вкладке «Данные».

Анализ данных.

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

  1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».Регрессия.
  2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.Параметры регрессии.
  3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

Результат анализа регрессии.

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.



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

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

  1. Перемещаемся во вкладку «Файл».

Переход во вкладку Файл в Microsoft Excel

Переходим в раздел «Параметры».

Переход в параметры в программе Microsoft Excel

Открывается окно параметров Excel. Переходим в подраздел «Надстройки».

Переход в надстройки в программе Microsoft Excel

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

Перемещение в надстройки в программе Microsoft Excel

Открывается окно доступных надстроек Эксель. Ставим галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

Активация пакета анализа в программе Microsoft Excel

Теперь, когда мы перейдем во вкладку «Данные», на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных».

Блок настроек Анализ в программе Microsoft Excel

Введение в линейную регрессию в Excel

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

Линия линейной регрессии имеет уравнение вида: Y = a + bX;

Где:

  • X – объясняющая переменная,
  • Y является зависимой переменной,
  • б – наклон линии,
  • a является y-перехватом (то есть значением y, когда x = 0).

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

Методы использования линейной регрессии в Excel

В этом примере показано, как выполнить анализ линейной регрессии в Excel. Давайте посмотрим на несколько методов.

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

Метод № 1 – Точечная диаграмма с линией тренда

Допустим, у нас есть набор данных о некоторых людях с их возрастом, индексом биомассы (ИМТ) и суммой, потраченной ими на медицинские расходы за месяц. Теперь, имея представление о характеристиках людей, таких как возраст и ИМТ, мы хотим выяснить, как эти переменные влияют на медицинские расходы, и, следовательно, использовать их для проведения регрессии и оценки / прогнозирования средних медицинских расходов для некоторых конкретных людей. Давайте сначала посмотрим, как только возраст влияет на медицинские расходы. Давайте посмотрим на набор данных:

linear-regression-excel-2.png.webp

Сумма на медицинские расходы = б * возраст + а

  • Выберите два столбца набора данных (x и y), включая заголовки.

linear-regression-excel-3.png.webp

  • Нажмите «Вставить» и разверните раскрывающийся список «Диаграмма разброса» и выберите эскиз «Разброс» (первый)

linear-regression-excel-4.png.webp

  • Теперь появится график рассеяния, и мы нарисуем на этом линию регрессии. Для этого щелкните правой кнопкой мыши любую точку данных и выберите «Добавить линию тренда».

linear-regression-excel-5.png.webp

  • Теперь на панели «Format Trendline» справа выберите «Linear Trendline» и «Показать уравнение на графике».

linear-regression-excel-6.png.webp

  • Выберите «Показать уравнение на графике».

linear-regression-excel-7.png.webp

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

linear-regression-excel-8.png.webp

После Импровизации диаграммы мы получаем вывод.

linear-regression-excel-9.png.webp

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

Метод № 2 – Анализ надстройки ToolPak Метод

Пакет инструментов анализа иногда не включен по умолчанию, и нам нужно сделать это вручную. Для этого:

linear-regression-excel-10.png.webp

  • Нажмите на меню «Файл».

linear-regression-excel-11.png.webp

После этого нажмите «Опции».

linear-regression-excel-12.png.webp

  • Выберите «Надстройки Excel» в поле «Управление» и нажмите «Перейти»

linear-regression-excel-13.png.webp

  • Выберите «Пакет инструментов анализа» -> «ОК»

linear-regression-excel-14.png.webp

Это добавит инструменты «Анализ данных» на вкладку «Данные». Теперь запустим регрессионный анализ:

  • Нажмите «Анализ данных» на вкладке «Данные»

linear-regression-excel-15.png.webp

  • Выберите «Регрессия» -> «ОК».

linear-regression-excel-16.png.webp

  • Откроется диалоговое окно регрессии. Выберите диапазон ввода Y и диапазон ввода X (медицинские расходы и возраст соответственно). В случае множественной линейной регрессии мы можем выбрать больше столбцов независимых переменных (например, если мы хотим увидеть влияние ИМТ также на медицинские расходы).
  • Установите флажок «Метки», чтобы включить заголовки.
  • Выберите желаемый вариант вывода.
  • Установите флажок «Остатки» и нажмите «ОК».

linear-regression-excel-17.png.webp

Теперь результаты нашего регрессионного анализа будут созданы в новом рабочем листе с указанием статистики регрессии, ANOVA, остатков и коэффициентов.

Выходная интерпретация:

  • Статистика регрессии показывает, насколько хорошо уравнение регрессии соответствует данным:

linear-regression-excel-18.png.webp

  • Множество R – это коэффициент корреляции, который измеряет силу линейных отношений между двумя переменными. Он лежит в диапазоне от -1 до 1, и его абсолютное значение показывает силу отношения с большим значением, указывающим на более сильное отношение, низким значением, указывающим на отрицательное значение, и нулевым значением, указывающим на отсутствие отношения.
  • Квадрат R – это коэффициент определения, используемый в качестве показателя качества соответствия. Он находится в диапазоне от 0 до 1, а значение, близкое к 1, указывает на то, что модель хорошо подходит. В этом случае 0, 57 = 57% значений y объясняются значениями x.
  • Скорректированный квадрат R – это квадрат R, скорректированный на количество предикторов в случае множественной линейной регрессии.
  • Стандартная ошибка отображает точность регрессионного анализа.
  • Наблюдения отображают количество модельных наблюдений.
  • Anova рассказывает об уровне изменчивости в рамках регрессионной модели.

linear-regression-excel-19.png.webp

Обычно это не используется для простой линейной регрессии. Однако «Значения F значимости» указывают на то, насколько надежны наши результаты, при этом значение больше 0, 05 предлагает выбрать другого предиктора.

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

linear-regression-excel-20.png.webp

Итак, наше уравнение регрессии будет: у = 16, 891 х – 355, 32. Это то же самое, что сделано методом 1 (точечная диаграмма с линией тренда).

Теперь, если мы хотим предсказать средние медицинские расходы в возрасте 72 лет:

Итак, у = 16, 891 * 72 -355, 32 = 860, 832

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

  • Остатки указывают на разницу между фактическими и прогнозируемыми значениями.

linear-regression-excel-21.png.webp

Последний метод регрессии используется не так часто и требует статистических функций, таких как slope (), intercept (), correl () и т. Д. Для проведения регрессионного анализа.

Что нужно помнить о линейной регрессии в Excel

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

Рекомендуемые статьи

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

  1. Как подготовить платежную ведомость в Excel?
  2. Использование формулы MAX в Excel
  3. Учебники по ссылкам на ячейки в Excel
  4. Создание регрессионного анализа в Excel
  5. Линейное программирование в Excel

Регрессионный анализ в Microsoft Excel

Regressivnyiy-analiz-v-Microsoft-Excel.png

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

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

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

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

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

Корреляционный анализ в Excel

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

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

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.

Время и стоимость.

Ставим курсор в любую ячейку и нажимаем кнопку fx.

  1. В категории «Статистические» выбираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» – первый диапазон значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» – второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

Функция КОРРЕЛ.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:

Корреляционная матрица.

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

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

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

  1. Кликаем по кнопке «Анализ данных». Она размещена во вкладке «Главная» в блоке инструментов «Анализ».

Переход в анализ данных в программе Microsoft Excel

Открывается небольшое окошко. В нём выбираем пункт «Регрессия». Жмем на кнопку «OK».

Запуск регрессии в программе Microsoft Excel

Открывается окно настроек регрессии. В нём обязательными для заполнения полями являются «Входной интервал Y» и «Входной интервал X». Все остальные настройки можно оставить по умолчанию.

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

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

Ввод интервала в настройках регрессии в программе Microsoft Excel

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

Параметры вывода в настройках регрессии в программе Microsoft Excel

После того, как все настройки установлены, жмем на кнопку «OK».

Запуск регрессивного анализа в программе Microsoft Excel

Использование возможностей табличного процессора «Эксель»

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

  • с вкладки «Файл» перейти в раздел «Параметры»;
  • в открывшемся окне выбрать строку «Надстройки»;
  • щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
  • поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

  • кредиторская задолженность (VK);
  • объем годового оборота (VO);
  • дебиторская задолженность (VD);
  • стоимость основных фондов (СОФ).

Кроме того, используется параметр задолженность предприятия по зарплате (V3 П) в тысячах американских долларов.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...