Как построить уравнение регрессии в Excel

Обновлено: 23.11.2024

В реальном мире вы, вероятно, никогда не будете проводить множественный регрессионный анализ вручную. Скорее всего, вы будете использовать компьютерное программное обеспечение (SAS, SPSS, Minitab, Excel и т. д.).

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

Пример проблемы с Excel

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

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

Как включить Excel

Откройте Excel.
Перейдите на вкладку "Данные".
Если вы видите кнопку "Анализ данных" в правом верхнем углу, пакет инструментов для анализа включен, и вы готовы к работе.

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

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

Ввод данных в Excel

Введите данные в электронную таблицу.
Определите независимые и зависимые переменные.
Укажите нужные анализы.

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

Далее мы хотим определить независимые и зависимые переменные. Начните с нажатия на вкладку "Данные" и кнопку "Анализ данных".

Откроется диалоговое окно Анализ данных. В раскрывающемся списке выберите "Регрессия" и нажмите "ОК".

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

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

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

Анализ данных с помощью Excel

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

Давайте рассмотрим выходные данные Excel и посмотрим, как он решает каждую задачу.

Уравнение регрессии

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

В этом уравнении ŷ — это прогнозируемый результат теста. Независимыми переменными являются IQ и количество учебных часов, которые обозначаются x₁ и x₂ соответственно. Коэффициенты регрессии: b₀, b₁ и b₂. В правой части уравнения неизвестными являются только коэффициенты регрессии; поэтому, чтобы указать уравнение, нам нужно присвоить значения коэффициентам.

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

Здесь мы видим, что точка пересечения регрессии (b₀) равна 23,156, коэффициент регрессии для IQ (b₁) равен 0.509, а коэффициент регрессии для учебных часов (b₂) равен 0,467. Таким образом, уравнение регрессии методом наименьших квадратов можно переписать так:

ŷ = 23,156 + 0,505 * IQ + 0,467 * Часы

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

Коэффициент множественной детерминации

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

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

Коэффициент множественной детерминации может быть определен как сумма квадратов:

SSR = Σ ( ŷ - y ) 2

SSTO = Σ (y - y) 2

где SSR — это сумма квадратов из-за регрессии, SSTO — это общая сумма квадратов, ŷ — прогнозируемое значение зависимой переменной, y — среднее значение зависимой переменной, а y — необработанная оценка зависимой переменной.

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

Беглый взгляд на результат показывает, что уравнение регрессии довольно хорошо соответствует данным. Коэффициент множественной детерминации равен 0,905. Для нашего примера задачи это означает, что 90,5 % различий в результатах тестов можно объяснить коэффициентом интеллекта и количеством часов, потраченных на учебу.

Альтернативный взгляд на R 2

Коэффициент множественной корреляции (R 2 ) представляет собой квадрат корреляции между фактическими и прогнозируемыми значениями зависимой переменной. Таким образом,

где y – исходная оценка зависимой переменной, ŷ – прогнозируемое значение зависимой переменной, а r_{y, ŷ} – корреляция между y и ŷ.

Таблица дисперсионного анализа

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

В этой таблице проверяется статистическая значимость независимых переменных как предикторов зависимой переменной. В последнем столбце таблицы показаны результаты общего F-теста. Статистика F (33,4) большая, а значение p (0,00026) маленькое. Это указывает на то, что одна или обе независимые переменные обладают объяснительной силой, превышающей ожидаемую случайно.

Как и коэффициент множественной корреляции, общий F-критерий, найденный в таблице ANOVA, предполагает, что уравнение регрессии хорошо соответствует данным.

Значение коэффициентов регрессии

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

В таблице коэффициентов регрессии представлена следующая информация для каждого коэффициента: его значение, его стандартная ошибка, t-статистика и значимость t-статистики. В этом примере t-статистика IQ и часов обучения статистически значимы на уровне 0,05. Это означает, что IQ вносит значительный вклад в регрессию после того, как будет принято во внимание влияние количества учебных часов. И количество учебных часов значительно влияет на регрессию после учета влияния коэффициента интеллекта.

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

Заключительные мысли

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

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

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

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

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

Зависимая переменная (также известная как переменная критерий) – это основной фактор, который вы пытаетесь понять и предсказать.

Независимые переменные (они же независимые переменные или предикторы) — это факторы, которые могут влиять на зависимую переменную.

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

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

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

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

Уравнение линейной регрессии

Математически линейная регрессия определяется следующим уравнением:

x — независимая переменная.
y – это зависимая переменная.
a – это отрезок Y, представляющий собой ожидаемое среднее значение y, когда все переменные x равны 0. На регрессионном графике это точка пересечения прямой с осью Y.
b — это наклон линии регрессии, то есть скорость изменения y при изменении x.
ε – случайная погрешность, представляющая собой разницу между фактическим значением зависимой переменной и ее прогнозируемым значением.

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

Для нашего примера уравнение линейной регрессии принимает следующий вид:

Продано зонтиков = b * количество осадков + a

Инструмент регрессии, включенный в пакет инструментов анализа
Точечная диаграмма с линией тренда
Формула линейной регрессии

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

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

В этом примере показано, как запустить регрессию в Excel с помощью специального инструмента, включенного в надстройку Analysis ToolPak.

Включить надстройку Analysis ToolPak

Пакет инструментов анализа доступен во всех версиях Excel с 2019 по 2003, но не включен по умолчанию. Итак, вам нужно включить его вручную. Вот как:

Это добавит инструменты анализа данных на вкладку Данные ленты Excel.

Выполнить регрессионный анализ

В этом примере мы собираемся выполнить простую линейную регрессию в Excel. У нас есть список среднемесячных осадков за последние 24 месяца в столбце B, который является нашей независимой переменной (предиктор), и количество проданных зонтов в столбце C, который является зависимой переменной. Конечно, есть много других факторов, которые могут повлиять на продажи, но пока мы сосредоточимся только на этих двух переменных:

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

Если вы строите модель множественной регрессии, выберите два или более смежных столбца с разными независимыми переменными.

Установите флажок "Ярлыки", если над диапазонами X и Y есть заголовки.
Выберите предпочтительный вариант вывода, в нашем случае новый лист.
При необходимости установите флажок "Остатки", чтобы получить разницу между прогнозируемыми и фактическими значениями.

Интерпретация результатов регрессионного анализа

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

Результаты регрессионного анализа: итоговые результаты

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

Вот что означает каждая часть информации:

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

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

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

В нашем примере R 2 – 0,91 (округлено до двух цифр), что очень хорошо. Это означает, что 91% наших значений соответствуют модели регрессионного анализа. Другими словами, 91% зависимых переменных (значений y) объясняется независимыми переменными (значениями x). Как правило, хорошим соответствием считается значение R в квадрате, равное 95 % или более.

Скорректированная площадь R. Это R-квадрат с поправкой на количество независимых переменных в модели. Вы захотите использовать это значение вместо R Square для множественного регрессионного анализа.

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

Наблюдения. Это просто количество наблюдений в вашей модели.

Результаты регрессионного анализа: ANOVA

Вторая часть выходных данных — это дисперсионный анализ (ANOVA):

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

df – количество степеней свободы, связанных с источниками дисперсии.
SS — это сумма квадратов. Чем меньше Residual SS по сравнению с Total SS, тем лучше ваша модель соответствует данным.
MS — это среднеквадратичное значение.
F – F-статистика или F-критерий для нулевой гипотезы. Он используется для проверки общей значимости модели.
Значимость F – это P-значение F.

Часть ANOVA редко используется для простого линейного регрессионного анализа в Excel, но вам определенно следует внимательно изучить последний компонент. Значение Significance F дает представление о том, насколько надежны (статистически значимы) ваши результаты. Если значимость F меньше 0,05 (5%), ваша модель в порядке. Если оно больше 0,05, возможно, вам лучше выбрать другую независимую переменную.

Выходные данные регрессионного анализа: коэффициенты

В этом разделе содержится конкретная информация о компонентах вашего анализа:

Самый полезный компонент в этом разделе — Коэффициенты. Это позволяет вам построить уравнение линейной регрессии в Excel:

Для нашего набора данных, где y — количество проданных зонтов, а x — среднемесячное количество осадков, наша формула линейной регрессии выглядит следующим образом:

Y = коэффициент осадков * x + точка пересечения

Оборудованный значениями a и b, округленными до трех знаков после запятой, он превращается в:

Например, при среднемесячном количестве осадков, равном 82 мм, продажи зонтов составят примерно 17,8:

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

Выходные данные регрессионного анализа: остатки

Если вы сравните расчетное и фактическое количество проданных зонтов, соответствующих месячному количеству осадков 82 мм, вы увидите, что эти цифры немного отличаются:

Приблизительно: 17,8 (вычислено выше)
Фактически: 15 (строка 2 исходных данных)

В чем разница? Потому что независимые переменные никогда не являются идеальными предикторами зависимых переменных. А остатки могут помочь вам понять, насколько далеки фактические значения от прогнозируемых значений:

Для первой точки данных (осадки 82 мм) невязка составляет приблизительно -2,8. Итак, мы прибавляем это число к прогнозируемому значению и получаем фактическое значение: 17,8 - 2,8 = 15.

Как построить график линейной регрессии в Excel

Если вам нужно быстро визуализировать взаимосвязь между двумя переменными, нарисуйте диаграмму линейной регрессии. Это очень легко! Вот как:

К этому моменту ваша диаграмма уже выглядит как приличный график регрессии:

Тем не менее, вы можете внести еще несколько улучшений:

Перетащите уравнение туда, куда считаете нужным.
Добавить заголовки осей (кнопка Элементы диаграммы >Заголовки осей).
Если ваши точки данных начинаются в середине горизонтальной и/или вертикальной оси, как в этом примере, вы можете избавиться от лишнего пробела. Следующий совет объясняет, как это сделать: масштабируйте оси диаграммы, чтобы уменьшить пустое пространство.

А вот как выглядит наш улучшенный график регрессии:

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

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

В Microsoft Excel есть несколько статистических функций, которые могут помочь вам в проведении линейного регрессионного анализа, таких как ЛИНЕЙН, НАКЛОН, ОТРЕЗОК и КОРРЕЛ.

Функция ЛИНЕЙН использует метод регрессии наименьших квадратов для вычисления прямой линии, которая лучше всего объясняет взаимосвязь между вашими переменными, и возвращает массив, описывающий эту линию. Вы можете найти подробное объяснение синтаксиса функции в этом руководстве. А пока давайте просто создадим формулу для нашего примера набора данных:

Поскольку функция ЛИНЕЙН возвращает массив значений, вы должны ввести ее как формулу массива. Выберите две соседние ячейки в одной строке, в нашем случае E2:F2, введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.

Формула возвращает коэффициент b (E1) и константу a (F1) для уже известного уравнения линейной регрессии:

Если вы избегаете использования формул массива в своих рабочих листах, вы можете вычислять a и b по отдельности с помощью обычных формул:

Получить точку пересечения Y (a):

Получить наклон (b):

Кроме того, вы можете найти коэффициент корреляции (Multiple R в итоговых результатах регрессионного анализа), который показывает, насколько сильно две переменные связаны друг с другом:

На следующем снимке экрана показаны все эти формулы регрессии Excel в действии:

Совет. Если вы хотите получить дополнительную статистику для регрессионного анализа, используйте функцию ЛИНЕЙН с параметром stats, установленным в значение ИСТИНА, как показано в этом примере.

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

Доступные загрузки:

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

Каждому из этих методов линейной регрессии соответствует свое время и место. Давайте рассмотрим каждый из них в отдельности.

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

Когда вам нужно получить быстрое и грубое линейное уравнение, соответствующее набору данных, лучше всего просто создать диаграмму XY (или «точечную диаграмму») и добавить быструю линию тренда. Добавьте уравнение к линии тренда, и у вас есть все, что вам нужно. Вы можете перейти от необработанных данных к наклону и пересечению линии наилучшего соответствия за 6 кликов (в Excel 2016).

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

Выберите данные x и y.
Открыть вкладку "Вставка"
Выбрать точечную диаграмму
Нажмите правой кнопкой мыши ряд данных
Выберите "Добавить линию тренда".
Проверить отображение уравнения на диаграмме

Теперь мы знаем, что показанный выше набор данных имеет наклон 165,4 и точку пересечения по оси Y -79,85.

Линейная регрессия с функцией ЛИНЕЙН

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

Во-первых, уравнение, отображаемое на диаграмме, нельзя использовать где-либо еще. По сути, это «тупой» текст.

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

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

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

Функция ЛИНЕЙН делает это идеально. Учитывая два набора данных, x и y, он вернет значения наклона (m) и точки пересечения (b), которые завершают уравнение

Синтаксис функции следующий:

ЛИНЕЙН(известные_y, [известные_x], [константа], [статистика])

Known_y – это y-данные, которые вы пытаетесь подобрать

Known_x – это x-данные, которые вы пытаетесь сопоставить

Const – это логическое значение, указывающее, обнуляется ли точка пересечения (FALSE) или нет (TRUE)

Stats – это логическое значение, указывающее, возвращается ли статистика регрессии

ЛИНЕЙН — это функция массива, поэтому нам нужно ввести ее как формулу массива, указав две ячейки, в которые она может возвращать значения m и b.

Давайте посмотрим, как можно использовать ЛИНЕЙН для определения уравнения линии наилучшего соответствия для приведенных выше данных.

Поскольку ЛИНЕЙН возвращает два значения, я начинаю с выбора двух соседних ячеек на листе.

Затем я ввожу формулу в строку формул, а не в ячейку.

Наконец, поскольку это формула массива, я нажимаю CTRL+SHIFT+ENTER, чтобы вычислить ячейки.

Результаты…

…точно такие же, как и при использовании метода линий тренда.

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

Линейная регрессия с использованием решателя

Введите «предположительные значения» для наклона и точки пересечения уравнения.
Рассчитать новые значения Y на основе этих значений.
Рассчитать ошибку между рассчитанными значениями y и данными y
Используйте решатель, чтобы найти значения наклона и точки пересечения, которые минимизируют общую ошибку.

Начнем снова с данных x и y, которые у нас были раньше.

Затем введите приблизительные значения для m и b в некоторые ячейки на листе.

Теперь создайте новый столбец вычисленных значений y на основе предполагаемых значений m и b и известных данных x.

Затем создайте столбец ошибок, вычислив разницу между y-данными и вычисленными значениями y.

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

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

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

После загрузки надстройки вы можете открыть Солвер на вкладке Данные. Вы найдете его справа на ленте:

Мы хотим минимизировать цель, ячейку H3 или сумму квадратов ошибок.
Для этого мы изменим переменные ячейки E3 и F3, наклон и точку пересечения по оси Y нашего линейного уравнения.
В качестве последнего шага снимите флажок "Сделать неограниченные переменные неотрицательными".

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

Когда мы нажимаем «Решить», Решатель делает свое дело и обнаруживает, что значения m = 165,36 и b = -79,85 определяют наиболее подходящую линию, проходящую через данные. Именно то, что было предсказано линией тренда диаграммы и ЛИНЕЙН.

Конечно, это ожидаемо. В конце концов, мы только что сделали «вручную» то, что инструмент «Линия тренда» и ЛИНЕЙН делают автоматически.

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

Регрессионный анализ в Excel с надстройкой Analysis Toolpak

Последний метод выполнения линейной регрессии в Excel — использование надстройки Analysis Toolpak. Эта надстройка позволяет Excel выполнять сложный статистический анализ, но она не включена по умолчанию в установках Excel.

Установите надстройку пакета инструментов анализа

Откройте вкладку "Файл", затем выберите "Параметры" в левом нижнем углу.
Нажмите "Надстройки" в левом нижнем углу окна "Параметры Excel".
В раскрывающемся списке "Управление" выберите "Надстройки Excel".
Нажмите "Перейти".
Выберите пакет инструментов анализа
Нажмите "ОК".

Пакет анализа будет доступен на вкладке «Данные» в группе «Анализ» (в крайнем правом углу ленты рядом с «Решателем»). Он помечен как «Анализ данных».

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

Откройте надстройку "Пакет инструментов анализа" на ленте и прокрутите вниз, пока не увидите "Регрессия". Выберите его и нажмите ОК.

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

Сначала поместите курсор в поле «Входной диапазон Y» и выберите значения y или зависимые переменные.

Повторите это для «Входного диапазона X».

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

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

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

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

Наконец, когда все настроено, остается только нажать кнопку OK, чтобы создать отчет.

Вот как это должно выглядеть:

Столбец этой таблицы с пометкой «Коэффициенты» содержит значения точки пересечения и наклона (X-переменная 1). Вы можете видеть, что они совпадают со значениями, полученными нами другими методами. (Что всегда приятно видеть!)

График остатков является случайным, и в остатках нет трендов:

Инструмент регрессии также генерирует много других данных, поэтому давайте рассмотрим некоторые из наиболее важных деталей:

Статистика линейной регрессии

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

Множественный R: это коэффициент корреляции Пирсона, который описывает корреляцию между предсказанными значениями Y и наблюдаемыми значениями Y. Значение 1 означает, что между ними существует идеальная корреляция, а значение 0 означает что нет никакой корреляции. В этом анализе значение равно 0,96, поэтому существует очень сильная корреляция между прогнозируемыми и наблюдаемыми значениями y.

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

Наблюдения: это просто количество наблюдаемых точек данных.

Коэффициенты регрессии

Коэффициенты
Стандартная ошибка
Стат.
P-значение
Ниже 95%
Верхние 95%

Коэффициенты. Это коэффициенты переменных, которые описывают линию наилучшего соответствия. В этом примере мы собираем коэффициенты в уравнение:

Стандартная ошибка: это значение говорит нам, насколько наблюдаемые значения отклоняются от линии наилучшего соответствия.

t Stat: это значение, которое вы использовали бы в t-тесте.

P-значение: это P-значение, используемое для проверки гипотезы. Если P-значение низкое, мы отклоняем нулевую гипотезу.

Нижний 95 %: это нижняя граница 95 % доверительного интервала.

Верхние 95 %: это верхняя граница 95 % доверительного интервала.

Остаточный результат

В итоговой таблице отчета указано прогнозируемое значение y и невязка или ошибка между прогнозируемым и наблюдаемым значением для каждого значения x.

Параметры регрессионного анализа

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

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

Уровень достоверности: в этом поле можно установить другой уровень достоверности. По умолчанию 95%.

Остатки: при выборе этого параметра остатки будут добавлены в выходную таблицу.

Стандартизированные остатки: если выбран этот параметр, на лист будут записаны стандартизированные остатки.

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

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

сообщить об этом объявлении

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

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

X — независимая переменная,
Y — зависимая переменная,
b — наклон линии,
a – точка пересечения с осью y (т. е. значение y, когда x=0).

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

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

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

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

Сумма медицинских расходов = b*age + a

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

Нажмите «Вставить», разверните раскрывающийся список «Точечная диаграмма» и выберите миниатюру «Точечная диаграмма» (первая).

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

Теперь на панели «Формат линии тренда» справа выберите «Линейная линия тренда» и «Отображать уравнение на диаграмме».

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

Обучение Excel (21 курс, более 9 проектов) 21 онлайн-курс | 9 практических проектов | 110+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,9 (10 098 оценок)

После импровизации диаграммы мы получили вот такой результат.

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

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

После этого нажмите «Параметры».

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

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

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

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

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

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

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

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

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

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

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

Так что y= 16,891 * 72 -355,32 = 860,832

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

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

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

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

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