Среднее линейное отклонение в Excel

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции ЛИНЕЙН в Microsoft Excel. Ссылки на дополнительную информацию о построении диаграмм и проведении регрессионного анализа можно найти в разделе «См. также».

Описание

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

Уравнение для линии:

y = m1x1 + m2x2 + . + б

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

Синтаксис

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

Синтаксис функции ЛИНЕЙН имеет следующие аргументы:

Синтаксис

known_y's Требуется. Набор уже известных вам значений y в отношении y = mx + b.

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

Если диапазон known_y содержится в одной строке, каждая строка known_x интерпретируется как отдельная переменная.

Необязательный параметр known_x. Набор значений x, которые вы, возможно, уже знаете в отношении y = mx + b.

Диапазон known_x может включать один или несколько наборов переменных. Если используется только одна переменная, known_y и known_x могут быть диапазонами любой формы, если они имеют одинаковые размеры. Если используется более одной переменной, known_y's должен быть вектором (то есть диапазоном с высотой в одну строку или шириной в один столбец).

Если known_x не указан, предполагается, что это массив того же размера, что и known_y.

константа Необязательный. Логическое значение, указывающее, следует ли принудительно приравнять константу b к 0.

Если const имеет значение TRUE или опущено, b вычисляется обычным образом.

Если const имеет значение FALSE, b устанавливается равным 0, а значения m корректируются так, чтобы соответствовать y = mx.

статистика Необязательно. Логическое значение, указывающее, следует ли возвращать дополнительную статистику регрессии.

Если параметр stats имеет значение TRUE, функция ЛИНЕЙН возвращает дополнительную статистику регрессии; в результате возвращается массив .

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

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

Описание

Значения стандартной ошибки для коэффициентов m1,m2. мин.

Коэффициент детерминации. Сравнивает оценочные и фактические значения y, значения варьируются от 0 до 1. Если это 1, то в выборке существует идеальная корреляция — нет никакой разницы между оценочным значением y и фактическим значением y. С другой стороны, если коэффициент детерминации равен 0, уравнение регрессии бесполезно для прогнозирования значения y. Сведения о вычислении r 2 см. в разделе "Примечания" далее в этом разделе.

Стандартная ошибка для оценки y.

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

Степени свободы. Используйте степени свободы, чтобы найти F-критические значения в статистической таблице. Сравните значения, которые вы найдете в таблице, со статистикой F, возвращенной функцией ЛИНЕЙН, чтобы определить уровень достоверности для модели. Сведения о том, как вычисляется df, см. в разделе «Примечания» далее в этом разделе. В примере 4 показано использование F и df.

Сумма квадратов регрессии.

Остаточная сумма квадратов. Сведения о том, как рассчитываются ssreg и ssresid, см. в разделе "Примечания" далее в этом разделе.

На следующем рисунке показан порядок, в котором возвращается дополнительная статистика регрессии.

Примечания

Вы можете описать любую прямую линию с наклоном и точкой пересечения по оси Y:

Наклон (m):
Чтобы найти наклон линии, часто обозначаемой как m, возьмите две точки на линии (x1,y1) и (x2,y2); наклон равен (y2 - y1)/(x2 - x1).

Отрезок по оси Y (b):
Отрезок по оси Y линии, часто обозначаемый буквой b, представляет собой значение y в точке, где линия пересекает ось y.

Уравнение прямой линии: y = mx + b. Как только вы узнаете значения m и b, вы можете вычислить любую точку на линии, подставив значение y или x в это уравнение. Вы также можете использовать функцию TREND.

Если у вас есть только одна независимая переменная x, вы можете получить значения наклона и точки пересечения y напрямую, используя следующие формулы:

Точность линии, рассчитанной функцией ЛИНЕЙН, зависит от степени разброса ваших данных. Чем линейнее данные, тем точнее модель ЛИНЕЙН. ЛИНЕЙН использует метод наименьших квадратов для определения наилучшего соответствия данных. Если у вас есть только одна независимая переменная x, расчеты m и b основаны на следующих формулах:

где x и y — выборочные средние значения; то есть x = СРЗНАЧ (известные x) и y = СРЗНАЧ (известные_y).

Функции подбора линий и кривых ЛИНЕЙН и ЛИНЕЙН позволяют рассчитать наилучшую прямую линию или экспоненциальную кривую, соответствующую вашим данным. Однако вы должны решить, какой из двух результатов лучше всего соответствует вашим данным. Вы можете рассчитать TREND(известные_y, известные_x) для прямой линии или РОСТ(известные_y, известные_x) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, предсказанных вдоль этой линии или кривой в ваших фактических точках данных. Затем вы можете сравнить прогнозируемые значения с фактическими значениями. Вы можете нанести их на карту для визуального сравнения.

В регрессионном анализе Excel вычисляет для каждой точки квадрат разницы между расчетным значением y для этой точки и ее фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов, ssresid. Затем Excel вычисляет общую сумму квадратов, sstotal. Когда аргумент const = TRUE или опущен, общая сумма квадратов представляет собой сумму квадратов разностей между фактическими значениями y и средним значением значений y. Когда аргумент const = FALSE, общая сумма квадратов представляет собой сумму квадратов фактических значений y (без вычитания среднего значения y из каждого отдельного значения y). Тогда сумма квадратов регрессии, ssreg, может быть найдена из: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминации r 2 , который является показателем того, насколько хорошо уравнение, полученное в результате регрессионного анализа, объясняет взаимосвязь между переменными. . Значение r 2 равно ssreg/sstotal.

В некоторых случаях один или несколько столбцов X (предположим, что Y и X находятся в столбцах) могут не иметь дополнительной прогностической ценности при наличии других столбцов X. Другими словами, удаление одного или нескольких столбцов X может привести к тому, что предсказанные значения Y будут одинаково точными. В этом случае эти избыточные столбцы X следует исключить из регрессионной модели. Это явление называется «коллинеарностью», потому что любой избыточный столбец X может быть выражен как сумма кратных неизбыточных столбцов X. Функция ЛИНЕЙН проверяет коллинеарность и удаляет любые избыточные столбцы X из регрессионной модели, когда она их идентифицирует. Удаленные столбцы X могут быть распознаны в выводе ЛИНЕЙН как имеющие 0 коэффициентов в дополнение к 0 значениям se. Если один или несколько столбцов удаляются как избыточные, это влияет на df, поскольку df зависит от количества X столбцов, фактически используемых для целей прогнозирования. Подробнее о вычислении df см. в примере 4. Если df изменяется из-за удаления избыточных столбцов X, это также влияет на значения sey и F. Коллинеарность должна быть относительно редкой на практике. Однако один случай, когда это более вероятно, - это когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли субъект в эксперименте членом определенной группы. Если const = TRUE или опущено, функция ЛИНЕЙН эффективно вставляет дополнительный столбец X со всеми 1 значениями для моделирования пересечения. Если у вас есть столбец с 1 для каждого субъекта, если он мужской, или 0, если нет, и у вас также есть столбец с 1 для каждого субъекта, если женский, или 0, если нет, этот последний столбец является избыточным, поскольку записи в нем могут быть получается путем вычитания записи в столбце «мужской показатель» из записи в дополнительном столбце всех 1 значений, добавленных функцией ЛИНЕЙН.

Значение df вычисляется следующим образом, когда из модели не удаляются столбцы X из-за коллинеарности: если есть k столбцов known_x и const = TRUE или опущен, df = n – k – 1. Если const = FALSE, df = n – k. В обоих случаях каждый столбец X, удаленный из-за коллинеарности, увеличивает значение df на 1.

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

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

Алгоритм, используемый в функции ЛИНЕЙН, отличается от алгоритма, используемого в функциях НАКЛОН и ОТРЕЗОК. Разница между этими алгоритмами может привести к разным результатам, когда данные не определены и коллинеарны. Например, если точки данных аргумента known_y's равны 0, а точки данных аргумента known_x равны 1:

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

Помимо использования ЛИНЕЙН для расчета статистики для других типов регрессии, вы можете использовать ЛИНЕЙН для расчета диапазона других типов регрессии, вводя функции переменных x и y в качестве рядов x и y для ЛИНЕЙН. Например, следующая формула:

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

y = m1*x + m2*x^2 + m3*x^3 + b

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

Значение F-критерия, возвращаемое функцией ЛИНЕЙН, отличается от значения F-критерия, возвращаемого функцией FTEST. ЛИНЕЙН возвращает статистику F, а ФТЕСТ возвращает вероятность.

Примеры

Пример 1. Наклон и точка пересечения по оси Y

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

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

Объяснение выходных данных регрессионного анализа Excel: множественная регрессия

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

РЕГРЕСС-АНАЛИЗ EXCEL. ЧАСТЬ ПЕРВАЯ: СТАТИСТИКА РЕГРЕССИИ

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

РЕГРЕСС-АНАЛИЗ EXCEL ОБЪЯСНЕНИЕ ЧАСТЬ ВТОРАЯ: АНОВЫЙ АНАЛИЗ

  1. СС = сумма квадратов.
  2. Регрессия MS = регрессия SS / степени свободы регрессии.
  3. Остаточная MS = среднеквадратическая ошибка (остаточная SS / остаточная степень свободы).
  4. F: общий F-критерий для нулевой гипотезы.
  5. Значимость F: P-значение, связанное со значимостью.

Вторая часть вывода, которую вы получаете в Excel, используется редко, по сравнению с приведенным выше регрессионным выводом. Он разбивает сумму квадратов на отдельные компоненты (см. Остаточная сумма квадратов), поэтому может быть сложнее использовать статистику каким-либо осмысленным образом. Если вы просто выполняете базовую линейную регрессию (и не хотите углубляться в отдельные компоненты), вы можете пропустить этот раздел вывода.
Например, для расчета R 2 из этой таблицы следует использовать следующую формулу:
R 2 = 1 – остаточная сумма квадратов (SS Residual) / общая сумма квадратов (SS Total).
В приведенной выше таблице остаточная сумма квадратов = 0,0366, а общая сумма квадратов равна 0,75, поэтому:
R 2 = 1 – 0,0366/0,75=0,9817

РЕГРЕСС-АНАЛИЗ EXCEL, ЧАСТЬ ТРЕТЬЯ: ИНТЕРПРЕТАЦИЯ КОЭФФИЦИЕНТОВ РЕГРЕССИИ

В этом разделе таблицы содержится очень конкретная информация о компонентах, которые вы выбрали для анализа данных. Поэтому в первом столбце (в данном случае «Дом/Квадратные футы») будет указано что-то другое, в зависимости от того, какие данные вы ввели в рабочий лист. Например, это может быть "рост", "доход" или любые другие переменные, которые вы выбрали.

  1. Коэффициент: дает оценку методом наименьших квадратов.
  2. Стандартная ошибка: оценка стандартной ошибки методом наименьших квадратов. : T-статистика для нулевой гипотезы по сравнению с альтернативной гипотезой. : дает значение p для проверки гипотезы.
  3. Нижние 95 %: нижняя граница доверительного интервала.
  4. Верхние 95 %: верхняя граница доверительного интервала.

Самая полезная часть этого раздела заключается в том, что он дает вам уравнение линейной регрессии:
y = mx + b.
y = наклон * x + точка пересечения.
Для приведенной выше таблицы уравнение будет примерно таким:
y = 3,14 – 0,65X1 + 0,024X2.

Будьте осторожны при толковании некоторых терминов

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

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

Ссылки

Нужна помощь с домашним заданием или контрольным вопросом? С Chegg Study вы можете получить пошаговые решения ваших вопросов от эксперта в этой области. Ваши первые 30 минут занятий с репетитором Chegg бесплатны!

Комментарии? Нужно опубликовать исправление? Оставьте комментарий на нашей странице Facebook.

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

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

Теперь мы знаем, что показанный выше набор данных имеет наклон 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 выводятся в ячейку. Это означает, что мы можем использовать их динамически в вычислениях где-то еще в электронной таблице.

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

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

Давайте снова начнем с данных 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.

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

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

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

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

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

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

Сначала поместите курсор в поле «Входной диапазон 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. Это похоже на добавление линии тренда на график.

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

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

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