Стандартная ошибка регрессии в excel

Обновлено: 06.01.2025

При использовании функции ЛИНЕЙН на листе в Microsoft Excel результаты статистического вывода могут возвращать неверные значения. Средство регрессия в окне "пакет анализа" может также возвращать неверные значения.

Причина

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

Диапазон значений x перекрывает диапазон значений y.

Количество строк в диапазоне входных данных меньше числа столбцов в общем диапазоне (x-value + y-Value).

Вы задаете нулевую константу (для третьего аргумента функции ЛИНЕЙН установите значение истина).

Обходное решение

Случай 1: диапазоны x-value и y перекрываются

Если диапазоны x-value и y перекрываются, функция ЛИНЕЙН возвращает неверные значения во всех ячейках результата. Нормальная статистическая вероятность запрещает значения в диапазонах x и y для перекрытия (повторяющиеся друг друга). Не перекрывают диапазоны x и y при ссылке на ячейки в формуле.Примечание. Средство регрессия предупреждает об этой проблеме и не продолжает работу. Вы можете использовать средство регрессия вместо функции ЛИНЕЙН. В Microsoft Office Excel 2007 вы можете найти инструмент регрессия, щелкнув анализ данных в группе анализ на вкладке данные . В Microsoft Office Excel 2003 и более ранних версиях Excel можно найти инструмент регрессия, выбрав пункт анализ данных в меню Сервис .

Случай 2: количество строк меньше числа столбцов x-Columns.

Статистические функции не действительны, так как количество строк должно быть меньше числа столбцов x (переменных). Количество строк данных должно быть больше количества столбцов данных (столбцов x и y).

Случай 3: указывается нулевая константа

Не указывайте нулевые константы (b = 0) в функции.

Дополнительная информация

Средство регрессия входит в пакет анализа. Пакет анализа — это программа надстройки Excel. Оно доступно при установке Microsoft Office или Excel. Прежде чем использовать средство регрессия в Excel, вы должны загрузить анализ ToolPak.To в Excel 2007, выполнив указанные ниже действия.

Выберите пункт надстройки, а затем в поле Управление выберите пункт надстройки Excel .

В окне Доступные надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК.Примечание. Если в списке Доступные надстройки не указан Пакет анализа , нажмите кнопку Обзор , чтобы найти его.

Чтобы сделать это в Excel 2003 и более ранних версиях Excel, выполните указанные ниже действия.

В меню Сервисвыберите пунктнадстройки.

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

Ссылки

Статистические вычисления на цифровом компьютере. Уильям J. Hemmerle. Blaisdell компания публикации: 1967. Глава 3, "вычисления с несколькими регрессиями" и раздел 3.2.1, "теория для предварительной регрессии".

Построение линейной регрессии, оценивание ее параметров и их значимости можно выполнить значительнее быстрей при использовании пакета анализа Excel (Регрессия). Рассмотрим интерпретацию полученных результатов в общем случае (k объясняющих переменных) по данным примера 3.5.

Вывод итогов
Регрессионная статистика
Множественный R	0,940
R-квадрат	0,884
Нормированный R – квадрат	0,868
Стандартная ошибка	22,87
Наблюдения

В таблице регрессионной статистики приводятся значения:

Множественный R – коэффициент множественной корреляции ;

R-квадрат – коэффициент детерминации R 2 ;

Нормированный R-квадрат – скорректированный R 2 с поправкой на число степеней свободы;

Стандартная ошибка– стандартная ошибка регрессии S;

Наблюдения –число наблюдений n.

Дисперсионный анализ
df	SS	MS	F	Значимость F
Регрессия	28102,2	28102,2	53,69	0,00016
Остаток	3663,7	523,3
Итого

В таблице Дисперсионный анализприведены:

1. Столбец df - число степеней свободы, равное

для строки Регрессия df = k;

для строкиОстатокdf = n – k – 1;

для строкиИтогоdf = n – 1.

2. Столбец SS –сумма квадратов отклонений, равная

для строки Регрессия ;

для строкиОстаток ;

для строкиИтого .

3. Столбец MSдисперсии, определяемые по формуле MS = SS/df:

для строки Регрессия – факторная дисперсия;

для строкиОстаток– остаточная дисперсия.

4. Столбец F – расчетное значение F-критерия, вычисляемое по формуле

F = MS(регрессия)/MS(остаток).

5. Столбец Значимость F –значение уровня значимости, соответствующее вычисленной F-статистике.

Значимость F = FРАСП(F-статистика, df(регрессия), df(остаток)).

Если значимость F < стандартного уровня значимости, то R 2 статистически значим.

Коэффи-циенты	Стандартная ошибка	t-cта-тистика	P-значение	Нижние 95%	Верхние 95%
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

В этой таблице указаны:

1. Коэффициенты– значения коэффициентов a, b.

2. Стандартная ошибка–стандартные ошибки коэффициентов регрессии S_a, S_b.

3. t-статистика – расчетные значения t-критерия, вычисляемые по формуле:

t-статистика = Коэффициенты / Стандартная ошибка.

4.Р-значение (значимость t)– это значение уровня значимости, соответствующее вычисленной t-статистике.

Р-значение = СТЬЮДРАСП(t-статистика, df(остаток)).

Если Р-значение < стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. Нижние 95% и Верхние 95%– нижние и верхние границы 95 %-ных доверительных интервалов для коэффициентов теоретического уравнения линейной регрессии.

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное y	Остатки e
72,70	-29,70
82,91	-20,91
94,53	-4,53
105,72	5,27
117,56	12,44
129,70	19,29
144,22	20,77
166,49	24,50
268,13	-27,13

В таблице ВЫВОД ОСТАТКАуказаны:

в столбце Наблюдение– номер наблюдения;

в столбце Предсказанное y– расчетные значения зависимой переменной;

в столбце Остатки e– разница между наблюдаемыми и расчетными значениями зависимой переменной.

Пример 3.6.Имеются данные (усл. ед.) о расходах на питание y и душевого дохода x для девяти групп семей:

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

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

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Коэффициенты регрессии а = 65,92 и b = 0,107. Направление связи между y и xопределяет знак коэффициентарегрессии b = 0,107, т.е. связь является прямой и положительной. Коэффициент b = 0,107 показывает, что при увеличении душевого дохода на 1 усл. ед. расходы на питание увеличиваются на 0,107 усл. ед.

Оценим значимость коэффициентов полученной модели. Значимость коэффициентов (a, b) проверяется по t-тесту:

Р-значение (a) = 0,00080 < 0,01 < 0,05

Р-значение (b) = 0,00016 < 0,01 < 0,05,

следовательно, коэффициенты (a, b) значимы при 1 %-ном уровне, а тем более при 5 %-ном уровне значимости. Таким образом, коэффициенты регрессии значимы и модель адекватна исходным данным.

Результаты оценивания регрессии совместимы не только с полученными значениями коэффициентов регрессии, но и с некоторым их множеством (доверительным интервалом). С вероятностью 95 % доверительные интервалы для коэффициентов есть (38,16 – 93,68) для a и (0,0728 – 0,142) для b.

Качество модели оценивается коэффициентом детерминации R 2 .

Величина R 2 = 0,884 означает, что фактором душевого дохода можно объяснить 88,4 % вариации (разброса) расходов на питание.

Значимость R 2 проверяется по F-тесту: значимость F = 0,00016 < 0,01 < 0,05, следовательно, R 2 значим при 1 %-ном уровне, а тем более при 5 %-ном уровне значимости.

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

Полученные теоретические дисперсии D(a), D(b) зависят от дисперсии s 2 случайного члена.

По данным выборки отклонения e_i, а, следовательно, и их дисперсии s 2 неизвестны, поэтому они заменяются наблюдаемыми остатками e_i и их выборочной дисперсией var(e).

Но оценка var(e) является смещенной, т.е.

Несмещенной оценкой дисперсии s 2 является величина (остаточная дисперсия):

которая служит мерой разброса зависимой переменной вокруг линии регрессии.

Отметим, что в знаменателе остаточной дисперсии стоит число степеней свободы (n – 2), а не n, так как две степени свободы теряются при определении двух параметров (a; b).

Величина S называется стандартной ошибкой регрессии.

Заменив в теоретических дисперсиях неизвестную s 2 на оценку S 2 , получим оценки дисперсий:

Величины S_a, S_b называется стандартными ошибками коэффициентов регрессии.

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

Исходные данные: n = 5, var(x) = 32, = 132, var(e) = 1,98.

Остаточная дисперсия S 2 и стандартная ошибка регрессии S есть:

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

S = СТОШYX(массив Y; массив X).

Стандартные ошибки коэффициентов регрессии:

Пример 3.2. Покажем, что в выборочной регрессии без свободного члена стандартная ошибка оценки b есть:

,где .

Подставим в оценку для b выражение , получим:

Оценка b является несмещенной, т.к. .

Дисперсия оценки b есть:

В исходной модели оценивается один параметр, поэтому оценкой является , следовательно, .

Пример 3.3. Покажем, что в выборочной регрессии стандартная ошибка оценки a есть

,где .

Подставим в оценку для a выражение , получим:

Оценка aявляется несмещенной, т.к. .

Дисперсия оценки a есть:

В исходной модели оценивается один параметр, поэтому оценка :

Пример 3.4.По данным примера 2.5. построим зависимость расходов на питание y от личного дохода x для модели регрессии без свободного члена и рассчитаем стандартную ошибку коэффициента регрессии.

Исходные данные и расчетные показатели представим в таблице.

Год	x	y	x 2	Xy
1,28	26,378	0,0806
3,85	6,594	3,429
6,42	5,856
8,99	6,594	4,048
11,56	26,378	0,193
Итого	32,1	65,946	13,608
Среднее	84,8	6,42	21,2	13,189	2,721

Коэффициент b определяется выражением , следовательно, .

Заметим, что в отсутствии свободного члена .

Остаточная дисперсия S 2 и стандартная ошибка регрессии S равны: .

Стандартная ошибка коэффициента регрессии равна:

Статистические свойства МНК-оценок (a; b)

Пусть выполняется условие нормальности распределения случайного члена: e_I

N(0; s 2 ). Тогда МНК-оценки коэффициентов регрессии также имеют нормальное распределение, поскольку являются линейными функциями от e_i, т.е.

Если условие нормальности распределения случайного члена не выполняется, то оценки (a; b) имеют асимптотически нормальное распределение.

3.3. Проверка гипотез, относящихся к коэффициентам регрессии (a; b).
Проверка гипотезы H₀: b = b₀.

Пусть в теоретической зависимости Y = a + b X + e случайный член e распределен нормально с неизвестной дисперсией s 2 .

Величина b хотя и неизвестна, но имеется основание предполагать, что она равна заданной величине b₀.

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

Пусть по выборочным данным получена оценка b.

В качестве критерия проверки нулевой гипотезы принимают случайную величину:

которая имеет распределение Стьюдента с n = n – 2 степенями свободы.

По таблице критических точек распределения Стьюдента по заданному уровню значимости a и числу n степеней свободы находят критическую точкуt_кр.

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

Результаты оценивания регрессии совместимы не только с конкретной гипотезой H₀: b = b₀, но и с некоторым их множеством.

Любое значение b, совместимое с оценкой b, удовлетворяет условию

Разрешив это неравенство относительно b получим:

т.е. доверительный интервал для величины b.

Посредине интервала лежит величина b. Границы интервала одинаково отстоят отb, зависят от выбора уровня значимости и являются случайными числами.

Доверительный интервал покрывает значение параметра b с заданной вероятностью (1 – a), т.е.

P(b – t_кр S_b < b < b + t_крS_b) = 1 – a.

Проверка гипотезы H₀: b = 0

Пусть по выборке получена оценка коэффициента регрессии b.

Для определения статистической значимости коэффициента регрессииb проверяется гипотеза H₀: b = 0 для t-статистики, рассчитываемой по формуле .

Величина t имеетраспределение Стьюдента с n = n – 2 степенями свободы.

Наблюдаемому (расчетному) значению критерия t соответствует определенная значимостьt, которую можно определить в Excel с помощью функции:

Значимость t =CТЬЮДРАСП(t; n; 2).

Из сравнения значимости t с заданным стандартным уровнем значимости,получаем:

- если значимость t > стандартного уровня, то b незначим;

- если значимость t <стандартного уровня, то b значим.

Пример 3.5. Зависимость расходов на питание от личного дохода по данным примера 2.5 имеет вид (в скобках указаны стандартные ошибки):

Оценим значимость коэффициента регрессии b = 0,775 и построим доверительный интервал для b при уровне значимости 5 %.

Наблюдаемое значение критерия .

Значимость t = 0,0124, соответствующая расчетному значению критерия t = 5,4, определяем с помощью функции: значимость t = СТЬЮДРАСП(t; n; 2)[1], где n = 3. Поскольку значимость t = 0,0124 < 0,05, то коэффициент регрессии b=0,775 значим.

При a = 0,05 критическое значение критерия t_кр= 3,18 определяем с помощью функции: t_кр=СТЬЮДРАСПОБР(a; n)[2].

Доверительный интервал для b есть:

0,775 – 3,18×0,143 < b < 0,775 + 3,18×0,143 или 0,32 < b < 1,23.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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