Множественная регрессия в примере Excel с решением

Обновлено: 05.07.2024

Представьте себе: вам предоставляется множество различных данных, и вас просят спрогнозировать объем продаж вашей компании в следующем году. Вы обнаружили десятки, а то и сотни факторов, которые могут повлиять на цифры. Но как узнать, какие из них действительно важны? Запустите регрессионный анализ в 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".

В реальном мире вы, вероятно, никогда не будете проводить множественный регрессионный анализ вручную. Скорее всего, вы будете использовать компьютерное программное обеспечение (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.

Проблема

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

Самый высокий год окончания школы	Мотивация, измеренная Хиггинсом Шкала мотивации	Годовые продажи в долларах
12	32	350 000 долларов США
14	35	399 765 долларов США
15	45	429 000 долл. США
16	50	435 000 долларов США
18	65	433 000 долларов США

Уравнение

Как правило, множественный регрессионный анализ предполагает наличие линейной зависимости между зависимой переменной (y) и независимыми переменными (x1, x2, x3… xn). И такого рода линейную зависимость можно описать с помощью следующей формулы:

Y = константа + β1*x1 + β2*x2+…+ βn*xn

Вот объяснение констант и коэффициентов:

< td width="97">Константа< tr>

Y	Прогнозируемое значение Y
Пересечение по оси Y
β1	Изменение Y при каждом шаге изменения x1
β2	Изменение в Y каждое 1 приращение изменяется в x2
…	…
βn	Изменение Y при каждом изменении приращения xn

< /p>

Константа и β1, β2… βn могут быть рассчитаны на основе доступных выборочных данных. После того, как вы получите значения констант β1, β2… βn, вы можете использовать их для прогнозирования.

Что касается нашей проблемы, нас интересуют только два фактора. Следовательно, уравнение будет таким:

Годовой объем продаж = константа + β1*(самый высокий год окончания школы) + β2*(мотивация, измеренная по шкале мотивации Хиггинса)

Настроить модель

Годовые продажи, самый высокий год окончания школы и мотивация были введены в столбцы A, B и C, как показано на рисунке 1. Лучше всегда помещать зависимую переменную (Годовые продажи здесь) перед независимыми переменными.

Загрузить пакет инструментов для анализа

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

Перейдите на вкладку «Файл» -> «Параметры», а затем нажмите «Надстройки» в диалоговом окне «Параметры Excel». Нажмите кнопку «Перейти» в нижней части диалогового окна «Параметры Excel», чтобы открыть диалоговое окно «Надстройки». В диалоговом окне «Надстройки» установите флажок «Analysis TakePak» и нажмите «ОК».

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

Рисунок 2 [щелкните изображение, чтобы открыть его полностью]

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

Нажмите «Анализ данных» в группе «Анализ» на вкладке «Данные». Выберите «Регрессия» в диалоговом окне «Анализ данных». Вы также можете выполнять другой статистический анализ, например t-критерий, ANOVA и т. д.

После выбора параметра "Регрессия" появится диалоговое окно "Регрессия". Заполните диалоговое окно, как показано на рисунке 3.2.

Входной диапазон Y содержит зависимую переменную и данные, а входной диапазон X содержит независимые переменные и данные. Здесь я должен напомнить вам, что независимые переменные должны быть в соседних столбцах. А максимальное количество независимых переменных равно 15.

Поскольку диапазон A1: C1 включает переменные метки, необходимо установить флажок «Ярлыки». На самом деле, я рекомендую вам включать метки каждый раз, когда вы заполняете Input Y Range и Input X Range. Эти метки полезны при просмотре сводных отчетов, возвращаемых Excel.

Установив флажок «Остатки», вы можете разрешить Excel отображать остаточные значения для каждого наблюдения. Посмотрите на рисунок 1, всего 5 наблюдений, и вы получите 5 невязок. Остаток — это то, что остается, когда вы вычитаете прогнозируемое значение из наблюдаемого значения. Стандартизированный остаток – это остаток, деленный на его стандартное отклонение.

Вы также можете установить флажок График остатка, который позволит Excel возвращать графики остатка. Количество остаточных графиков равно количеству независимых переменных. График остатков — это график, который показывает остатки по оси Y и независимые переменные по оси X. Случайно разбросанные точки по оси x на графике остатков означают, что модель линейной регрессии подходит. Например, на рис. 3.3 показаны три типичных паттерна остаточных графиков. Только тот, что на левой панели, указывает на то, что он хорошо подходит для линейной модели. Два других шаблона лучше подходят для нелинейной модели.

Excel вернет построенный линейный график, если установить флажок Line Fit Plots. Подогнанный линейный график может отображать взаимосвязь между одной зависимой переменной и одной независимой переменной. Другими словами, Excel вернет вам то же количество подогнанных линейных графиков, что и независимой переменной. Например, вы получите 2 графика для нашей задачи.

Результаты

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

Чем выше R-квадрат (ячейка F5), тем теснее взаимосвязь между зависимыми и независимыми переменными. А коэффициенты (диапазон F17:F19) в третьей таблице вернули вам значения констант и коэффициентов. Уравнение должно быть таким: годовой объем продаж = 1589,2 + 1 9928,3 * (самый высокий год окончания школы) + 11,9 * (мотивация, измеренная по шкале мотивации Хиггинса).

Однако, чтобы убедиться, что результаты надежны, вам также необходимо проверить p-значения, выделенные желтым цветом. Только если значение p в ячейке J12 меньше 0,05, все уравнение регрессии надежно. Но вам также необходимо проверить p-значения в диапазоне I17: I19, чтобы увидеть, полезны ли постоянные и независимые переменные для предсказания зависимой переменной. Для нашей проблемы нам лучше отказаться от мотивации при рассмотрении независимых переменных.

Удалить мотивацию из независимых переменных

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

Годовой объем продаж = 1167,8 + 1 9993,3 * (самый высокий год окончания школы)

Рисунок 3.5 [щелкните изображение, чтобы открыть его полностью]

Помимо инструмента надстроек вы также можете использовать функцию ЛИНЕЙН для проведения множественного регрессионного анализа. Функция ЛИНЕЙН — это функция массива, которая может возвращать результат либо в одной ячейке, либо в диапазоне ячеек. Прежде всего, выберите диапазон A8:B12, а затем введите формулу «=ЛИНЕЙН (A2:A6, B2:B6, ИСТИНА, ИСТИНА)» в первую ячейку этого диапазона (A8). После того, как вы нажмете CTRL + SHIFT + ENTER, Excel вернет результаты, как показано ниже. Сравнивая с рисунком 3.4, вы можете видеть, что 19993,3 — это коэффициент наивысшего года окончания школы, а 1167,8 — постоянное значение. В любом случае, я рекомендую вам использовать инструмент надстроек. Это намного проще.

Определение 1. Мы используем ту же терминологию, что и в определении 3 регрессионного анализа, за исключением того, что степени свободы df_Res и df_Reg изменяются с учетом количества k независимых переменных.

Свойство 1:

Доказательство. Доказательство такое же, как и для свойства 1 регрессионного анализа.

Свойство 2: где R – коэффициент множественной корреляции (определенный в определении 1 множественной корреляции)

Доказательство. Эти свойства являются множественными регрессионными аналогами свойств 2, 3 и 5f регрессионного анализа соответственно, и их доказательства аналогичны.

Наблюдение: из свойства 2 и второго утверждения свойства 3

который представляет собой многомерную версию свойства 1 основных понятий корреляции.

Свойство 3:

Свойство 4: MS_Res — это несмещенная оценка того, где находится дисперсия условий ошибки

Наблюдение: на основании свойства 4 и свойства 4 множественной регрессии с использованием матриц ковариационная матрица B может быть оценена с помощью

В частности, диагональ C = [c_ij] содержит дисперсию b_{j< /sub>}, поэтому стандартная ошибка b_j может быть выражена как

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

Рисунок 1. Создание линии регрессии с использованием матричных методов

Результат показан на рисунке 1. Диапазон E4:G14 содержит матрицу дизайна X, а диапазон I4:I14 содержит Y. Матрица (X T X) -1 в диапазоне E17:G19 может быть рассчитана по формуле массива

В соответствии со свойством 1 множественной регрессии с использованием матриц вектор коэффициентов B (в диапазоне K4:K6) можно рассчитать с помощью формулы массива:

Прогнозируемые значения Y, т. е. Y-hat, затем можно рассчитать с помощью формулы массива

Стандартная ошибка каждого из коэффициентов в B может быть рассчитана следующим образом. Сначала вычислите массив членов ошибки E (диапазон O4:O14), используя формулу массива I4:I14 – M4:M14. Тогда, как и в случае простой регрессии, SS_Res = DEVSQ(O4:O14) = 277,36, df_Res = n – k – 1 = 11 – 2 – 1 = 8 и MS_Res = SS _Res/df_Res = 34,67 (дополнительные сведения см. в разделе Анализ множественной регрессии).

Из наблюдения, следующего за свойством 4, следует, что MS_Res (X T X) -1 равно ковариационная матрица для коэффициентов, поэтому квадратный корень из диагональных членов представляет собой стандартную ошибку коэффициентов. В частности, стандартная ошибка отрезка b₀ (в ячейке K9) выражается формулой =КОРЕНЬ(I17), стандартная ошибка цветового коэффициента b₁ (в ячейке K10) выражается формулой =КОРЕНЬ(J18), а стандартная ошибка коэффициента качества b₂ (в ячейке K11) выражается формулой =КОРЕНЬ(K19).

Функции Excel. Функции НАКЛОН, ОТРЕЗОК, КРОНСИМВ и ПРОГНОЗ не работают для множественной регрессии, но функции ТРЕНД и ЛИНЕЙН поддерживают множественную регрессию, как и инструмент анализа данных регрессии.

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

ЛИНЕЙН работает так же, как и в случае простой линейной регрессии, за исключением того, что вместо использования области 5 × 2 для вывода требуется область 5 × k, где k = количество независимых переменных + 1. Таким образом, для модели с 3 независимыми переменными вам нужно выделить пустую область 5 × 4. Как и раньше, вам нужно вручную добавить соответствующие ярлыки для ясности.

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

Пример 2. Мы вернемся к примеру 1 множественной корреляции, анализируя модель, в которой уровень бедности можно оценить как линейную комбинацию уровня младенческой смертности, доли белого населения и уровня насильственных преступлений (на 100 000 человек).

Нам нужно найти параметры b₀, b₁ и такие, что

На рисунке 2 показано, как использовать TREND и LINEST.

Рисунок 2. ТРЕНД и ЛИНЕЙН для данных в примере 1

Здесь показаны данные для первых 15 из 50 штатов (столбцы от A до E) и прогнозируемый процент бедности, когда младенческая смертность, процент белых в населении и уровень преступности соответствуют указанным (диапазон G6:J8). Выделив диапазон J6:J8, вводим формулу массива =TREND(B4:B53,C4:E53,G6:I8). Как видно из рис. 2, модель предсказывает уровень бедности в 12,87 %, когда младенческая смертность составляет 7,0, белые составляют 80 % населения, а насильственные преступления составляют 400 на 100 000 человек.

На рис. 2 также показаны выходные данные функции ЛИНЕЙН после того, как мы выделим заштрихованный диапазон H13:K17 и введем =ЛИНЕЙН(B4:B53,C4:E53,ИСТИНА,ИСТИНА). Заголовки столбцов b₁, b₂, b_{3< /sub> и intercept относятся только к первым двум строкам (обратите внимание на порядок коэффициентов). Остальные три строки содержат по два значения, помеченных слева и справа.}

Итак, мы видим, что линия регрессии

Бедность = 0,437 + 1,279 ∙ Детская смертность + 0,0363 ∙ Белые + 0,00142 ∙ Преступность

Здесь бедность представляет прогнозируемое значение. Мы также видим, что R Square равен 0,337 (т.е. 33,7% дисперсии уровня бедности объясняется моделью), стандартная ошибка оценки составляет 2,47 и т. д.

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

Рисунок 3. Выходные данные инструмента регрессионного анализа данных

Поскольку значение p = 0,00026

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

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

Рисунок 4. Упрощенная регрессионная модель для примера 1

И снова мы видим, что модель Бедность = 4,27 + 1,23 ∙ Младенческая смертность хорошо подходит для данных (значение p = 1,96E-05

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

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

Пример 3. Определите, подходит ли регрессионная модель для данных из примера 1 метода наименьших квадратов для множественной регрессии с помощью инструмента регрессионного анализа данных.

Результаты анализа показаны на рис. 5.

Рисунок 5. Выходные данные инструмента регрессионного анализа данных

Поскольку значение p = 0,00497

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

Статистика регрессии

Множественное значение R – SQRT(F7) или расчет по определению 1 множественной корреляции
R Square = G14/G16
Скорректированный R-квадрат — расчет на основе R-квадрата с использованием определения 2 множественной корреляции.
Стандартная ошибка = SQRT(H15)
Наблюдения = COUNT(A4:A14)

Дисперсный анализ

SS_T = DEVSQ(C4:C14)
SS_Reg = DEVSQ(M4:M14) из рисунка 3 метода наименьших квадратов для множественной регрессии
SS_{Разрешение} = G16-G14
Все остальные записи можно рассчитать аналогично тому, как мы рассчитали значения ANOVA для примера 1 проверки соответствия линии регрессии (см. рис. 1 на этой веб-странице).

Коэффициенты (в третьей таблице) — показываем, как рассчитать поля перехвата; поля цвета и качества аналогичны

Коэффициент и стандартную ошибку можно рассчитать, как показано на рис. 3 метода наименьших квадратов для множественной регрессии.
Стат. = F19/G19
P-значение = T.DIST.2T(ABS(H19),F15)
Нижние 95 % = F19-T.INV.2T(0,05;F15)*G19
Верхние 95 % = F19+T.INV.2T(0,05;F15)*G19

Остальные результаты регрессионного анализа данных показаны на рис. 6.

Рисунок 6. Остатки/процентили на выходе регрессии

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

Наблюдения с 1 по 11 соответствуют необработанным данным в A4:C14 (из рисунка 5). В частности, записи для наблюдения 1 можно рассчитать следующим образом:

Прогнозируемая цена = F19+A4*F20+B4*F21 (из рис. 5)
Остатки = C4–F26
Стандартные остатки =G26/СТАНДОТКЛОН.S(G26:G36)

Вероятностный вывод

Процентиль: ячейка J26 содержит формулу =100/(2*E36), ячейка J27 содержит формулу =J26+100/E36 (аналогично для ячеек с J28 по J36).
Цена: это просто значения цен в диапазоне C4:C14 (на рис. 5) в отсортированном порядке. Например. формула дополнительного массива =QSORT(C4:C14) может быть помещена в диапазон K26:K36.

Наконец, инструмент анализа данных создает следующие диаграммы рассеяния.

График нормальной вероятности

Здесь показана зависимость процентиля от цены из выходных данных таблицы на рис. 6. Этот график используется для определения того, соответствуют ли данные нормальному распределению. Может быть полезно добавить линию тренда, чтобы увидеть, соответствуют ли данные прямой линии. Это можно сделать, щелкнув график и выбрав «Макет» > «Анализ|Линия тренда» и выбрав «Линейная линия тренда».
Он играет ту же роль, что и график QQ. Фактически, за исключением масштаба, он генерирует тот же график, что и график QQ, созданный дополнительным инструментом анализа данных (переключение осей).

Рисунок 7. График нормальной вероятности

График на рис. 7 показывает, что данные в разумных пределах соответствуют нормальному предположению.

Остаточные графики

Для каждой независимой переменной создается один график. Для примера 2 генерируются два графика: "Цвет по сравнению с остатками" и "Качество по сравнению с остатками".
Эти графики используются для определения того, соответствуют ли данные предположениям о линейности и однородности дисперсии. Для выполнения предположения об однородности дисперсии каждый график должен отображать случайный набор точек. Если появляется определенная форма точек или если вертикальный разброс точек не является постоянным на горизонтальных интервалах одинаковой длины, то это указывает на нарушение предположения об однородности дисперсий.
Для выполнения допущения о линейности остатки должны иметь среднее значение 0, на что указывает приблизительно равный разброс точек выше и ниже оси X.

Рисунок 8. Остаточные графики

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

Два графика на рис. 9 показывают явные проблемы. К счастью, они не основаны на данных примера 3.

Рисунок 9. Остаточные графики, показывающие нарушение предположений

Для диаграммы слева на рисунке 9 вертикальный разброс точек в правой части диаграммы больше, чем в левой. Это явный признак того, что дисперсии не являются однородными.Для диаграммы справа точки не кажутся случайными, а также несколько точек находятся ниже оси x (что указывает на нарушение линейности). Диаграмма на рис. 10 идеально соответствует тому, что нам нужно: случайное распределение точек с одинаковым числом точек выше и ниже оси x.

Рисунок 10. Остатки, предположения о линейности и дисперсии

Графики выравнивания по линии

Для каждой независимой переменной создается один график. Для примера 3 генерируются два графика: один для цвета и один для качества. Для каждой диаграммы наблюдаемые значения y (цена) и прогнозируемые значения y наносятся на график относительно наблюдаемых значений независимой переменной.

Рисунок 11. Графики аппроксимации линий для примера 3

Наблюдение: результаты примера 3 можно представить следующим образом:

Множественный регрессионный анализ использовался для проверки того, влияют ли определенные характеристики на цену бриллиантов. Результаты регрессии показали, что два предиктора объяснили 81,3% дисперсии (R 2 = 0,85, F(2,8) = 22,79, p

Вы можете выразить p-значения другими способами, а также добавить уравнение регрессии: цена = 1,75 + 4,90*цвет + 3,76*качество

Читайте также: