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

Обновлено: 21.11.2024

РЕГРЕССИЯ С ИСПОЛЬЗОВАНИЕМ ФУНКЦИЙ EXCEL INTERCEPT, SLOPE, RSQ, STEYX и ПРОГНОЗ

Используемые данные находятся в файле carsdata.xls

Модель регрессии населения: y = β1 + β2 x + u

Мы хотим оценить линию регрессии: y = b1 + b2 x

Отдельные функции INTERCEPT, SLOPE, RSQ, STEYX и FORECAST можно использовать для получения ключевых результатов регрессии с двумя переменными

  • INTERCEPT(A1:A6,B1:B6) дает оценку пересечения OLS, равную 0,8
  • SLOPE(A1:A6,B1:B6) дает оценку наклона МНК, равную 0,4
  • RSQ(A1:A6,B1:B6) дает R-квадрат 0,8
  • STEYX(A1:A6,B1:B6) дает стандартную ошибку регрессии 0,36515 0,8
  • ПРОГНОЗ(6,A1:A6,B1:B6) дает прогнозное значение МНК Yhat=3,2 для X=6 (прогноз 3,2 автомобиля для домохозяйства размером 6).

Таким образом, оценочная модель
y = 0,8 + 0,4*x,
с R-квадратом 0,8 и расчетным стандартным отклонением u, равным 0,36515,
и мы прогнозируем, что для x = 6 мы у = 0,8 + 0,4 * 6 = 3,2.

РЕГРЕССИЯ С ИСПОЛЬЗОВАНИЕМ EXCEL FUNCTION LINEST

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

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

Мы рассмотрим пример, в котором выходные данные помещаются в массив D2:E6.

Сначала в ячейке D2 введите функцию ЛИНЕЙН(A2:A6,B2:B6,1,1).

  • Выделите нужный массив D2:E6
  • Нажмите клавишу F2 (в левом нижнем углу электронной таблицы появится поле редактирования).

Наконец нажмите CTRL-SHIFT-ENTER.
Это дает

где результаты в A2:E6 представляют собой
Коэфф наклона Коэфф пересечения
Ст.ошибка наклона St.ошибка пересечения
R-квадрат St.ошибка регрессии
Общие степени свободы F-критерия (nk)
Регрессия SS Остаточная SS

В частности, подобранная регрессия
CARS = 0,4 + 0,8 HH SIZE с R 2 = 0,8
Оцененные коэффициенты имеют стандартные ошибки, соответственно, 0,11547 и 0,382971.

Чтобы получить только коэффициенты, введите команду ЛИНЕЙН с последней записью 0, а не 1, т.е. ЛИНЕЙН(A2:A6,B2:B6,1,0),
, а затем выделите ячейки A8:B8, скажем, нажмите клавишу F2 и нажмите CTRL-SHIFT-ENTER.

ЛИНЕЙН можно расширить до множественной регрессии (больше, чем перехват и один регрессор).
Затем первые две строки вывода расширяются до количества столбцов, равного количеству регрессоров (включая константы)
а последние три строки такие же, как и раньше.

Функция ЛИНЕЙН аналогична функции ЛИНЕЙН, за исключением того, что оценивается экспоненциальная зависимость, а не линейная.


ПРОГНОЗ С ИСПОЛЬЗОВАНИЕМ EXCEL FUNCTION TREND

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

  • Установите значения X для прогноза, скажем, 6 в ячейке C2 и 7 в ячейке C3.
  • В ячейке A8 задайте функцию ТЕНДЕНЦИЯ(A2:A6,B2:B6,C2:C3,1).
    Обратите внимание, что метки не включаются при использовании функции ТЕНДЕНЦИЯ.
    Это дает только одно значение 3,2 в ячейке B21. Нам нужен и другой прогноз.
  • Выделите ячейки A8:A9 и нажмите клавишу F2 (после этого в нижней части экрана появится пункт "Правка").
    Нажмите CTRL-SHIFT-ENTER.

Таким образом, для X=6 мы прогнозируем Y=3,2, а для X=7 мы прогнозируем Y=3,6, как и ожидалось, учитывая Y = 0,8 + 0,4*X.

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

Всякий раз, когда мы подгоняем модель линейной регрессии, модель принимает следующую форму:

где ϵ – ошибка, не зависящая от X.

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

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

В этом руководстве представлен пошаговый пример расчета стандартной ошибки регрессионной модели в Excel.

Шаг 1. Создайте данные

Для этого примера мы создадим набор данных, содержащий следующие переменные для 12 разных учащихся:

  • Оценка за экзамен
  • Часы, потраченные на учебу
  • Текущая оценка

Шаг 2. Подгонка регрессионной модели

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

Для этого нажмите вкладку "Данные" на верхней ленте, а затем нажмите "Анализ данных":

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

В появившемся окне выберите Регрессия. В появившемся новом окне введите следующую информацию:

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

Шаг 3. Интерпретация стандартной ошибки регрессии

Стандартная ошибка модели регрессии — это число рядом со стандартной ошибкой:

Стандартная ошибка этой конкретной регрессионной модели составляет 2,790029.

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

Обратите внимание, что некоторые экзаменационные баллы будут отличаться от прогнозируемого более чем на 2,79 единицы, а некоторые будут ближе. Но в среднем разница между реальными экзаменационными баллами и прогнозируемыми составляет 2,790029.

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

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

Дополнительные ресурсы

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

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

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

В этой статье я покажу вам два способа расчета стандартной ошибки в Excel.

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

Итак, приступим!

Оглавление

Что такое стандартная ошибка?

При работе с реальными данными часто невозможно работать с данными всего населения. Поэтому мы обычно берем случайные выборки из населения и работаем с ними.

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

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

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

Как рассчитывается стандартная ошибка?

Стандартная ошибка для выборки обычно рассчитывается по формуле:

В приведенной выше формуле:

  • SE — это стандартная ошибка
  • σ представляет собой стандартное отклонение выборки
  • n представляет размер выборки.

Как найти стандартную ошибку в Excel с помощью формулы

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

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

  1. Нажмите на ячейку, в которой должно появиться стандартное сообщение об ошибке, и нажмите на строку формул рядом с символом fx под панелью инструментов.
  2. Введите символ «=» в строке формул. И введите: =СТАНДОТКЛОН(
  3. Перетащите и выберите диапазон ячеек, которые являются частью вашего примера данных. Это добавит местоположение диапазона в вашу формулу.Таким образом, если ваш образец данных находится в ячейках с B2 по B14, вы увидите: =СТАНДОТКЛОН(B2:B14 в строке формул.
  4. Закройте скобки для формулы СТАНДОТКЛОН. До сих пор вы использовали функцию СТАНДОТКЛОН, чтобы найти стандартное отклонение ваших выборочных данных.
  5. Далее мы хотим разделить это стандартное отклонение на квадратный корень из размера выборки. Итак, продолжим нашу формулу. Нажмите на строку формул после закрывающих скобок формулы СТАНДОТКЛОН и добавьте символ ‘/’, чтобы указать, что вы хотите разделить результат функции СТАНДОТКЛОН. Итак, ваша формула пока такова: =СТАНДОТКЛОН(B2:B14)/
  6. Чтобы найти квадратный корень числа, мы используем формулу КОРЕНЬ. Затем введите КОРЕНЬ(). Теперь в строке формул будет следующая формула: =СТАНДОТКЛОН(B2:B14)/КОРЕНЬ(
  7. Наконец, вам нужен размер выборки. Для этого вам нужно использовать функцию COUNT. Итак, введите COUNT( после того, что у вас уже есть в строке формул. Снова перетащите и выберите диапазон ячеек, которые являются частью ваших данных образца, и закройте квадратную скобку для COUNT формула. Это даст вам количество ячеек в выбранном диапазоне.
  8. Закройте квадратную скобку и для функции SQRT. Итоговая формула должна выглядеть так: =СТАНДОТКЛОН(B2:B14)/КОРЕНЬ(СЧЁТ(B2:B14)) Обратите внимание, что в конце две закрывающие скобки. Один для функции COUNT, другой для функции SQRT.
  9. Вот оно! Нажмите клавишу возврата на клавиатуре, и вы получите стандартную ошибку вашего образца!

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

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

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

Как найти стандартную ошибку в Excel с помощью пакета инструментов анализа данных

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

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

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

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

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

  1. Перейдите на вкладку Файл и нажмите Параметры. Откроется окно Параметры.
  2. Здесь нажмите «Надстройки» на левой боковой панели.
  3. В списке надстроек выберите Пакет инструментов анализа.
  4. Внизу окна нажмите кнопку Перейти рядом с пунктом Управление: надстройки.
  5. Установите флажок рядом с пунктом Пакет инструментов анализа и нажмите кнопку "ОК".

При этом ваш Пакет инструментов анализа данных будет добавлен на панель инструментов Excel. Когда вы щелкаете вкладку Excel «Данные», вы должны найти инструмент под названием «Анализ данных» в крайнем правом углу панели инструментов Данные ( в группе "Анализ").

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

  1. Нажмите на инструмент Анализ данных на вкладке Данные. Откроется диалоговое окно Инструменты анализа.
  2. Выберите «Описательная статистика» в списке слева от диалогового окна и нажмите «ОК».
  3. Введите расположение диапазона ячеек, содержащих ваши образцы данных, в поле «Входной диапазон». Вы также можете перетащить и выбрать нужный диапазон ячеек. Если у вас есть данные для нескольких проб, расположенных в соседних столбцах, вы можете выбрать данные во всех столбцах. Вы получите результаты отдельно для каждого столбца.
  4. Если ваши данные содержат заголовки столбцов, установите флажок "Ярлыки в первой строке".
  5. Выберите, где должны отображаться результаты. Безопаснее выбрать «Новый рабочий лист». Это обеспечит отображение сведений на вновь созданном листе и не повлияет на данные на текущем листе.
  6. Установите флажок рядом с «Сводная статистика» и нажмите ОК.

Это отобразит все ваши аналитические показатели на новом листе.

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

Заключение

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

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

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

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

В подходе робастных стандартных ошибок Хьюбера-Уайта метод МНК используется для расчета коэффициентов регрессии, но ковариационная матрица матрицы коэффициентов рассчитывается с помощью

где S — ковариационная матрица остатков, которые в предположении, что остатки имеют среднее значение 0 и не являются автокоррелированными, т. е. E[e< /em>] = 0 и E[ee T ] = 0 означает, что S — диагональная матрица, диагональные элементы которой равны . Мы должны умножить S на n/(n−k−1), но для больших n разница несущественна .

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

в то время как, если предположение об однородности отклонений не выполняется, то

Надежные стандартные ошибки Хубера-Уайта равны квадратному корню из элементов диагонали ковариационной матрицы.

где элементы S – это квадраты невязок по методу МНК. Мы называем эти стандартные ошибки совместимыми с гетероскедастичностью (HC) стандартными ошибками.

Гетероскедастичность означает непостоянную дисперсию. Эти оценки СИНИЕ (наилучшая линейная несмещенная оценка), но только для больших выборок. Далее мы определяем четыре другие меры, которые эквивалентны для больших выборок, но могут быть менее смещенными для выборок меньшего размера. Каждая оценка снова является квадратным корнем элементов диагонали ковариационной матрицы, как описано выше, за исключением того, что мы используем другую версию S.

Здесь hi — это значения кредитного плеча (т. е. диагональные элементы матрицы шляпы МНК, как описано в разделах Множественная регрессия с использованием матриц и Множественная регрессия Выбросы и факторы влияния ), n = размер выборки и k = количество независимых переменных.

HC1 настраивается по степеням свободы. HC2 уменьшает смещение из-за точек высокого рычага. HC3 имеет тенденцию давать лучшие результаты, чем HC2. HC4 — это более поздний подход, который может превосходить HC3.

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

Пример 1. Повторите пример 2 множественного регрессионного анализа в Excel, используя версию HC3 устойчивых стандартных ошибок Хубера-Уайта. Первые 17 из 50 строк входных данных показаны в A3:E20 на рисунке 2.

Нажмите Ctrl-m и дважды щелкните параметр Регрессия в появившемся диалоговом окне. Затем выберите «Множественная линейная регрессия» из списка параметров и нажмите кнопку «ОК». Заполните появившееся диалоговое окно, как показано на рисунке 1.

Рисунок 1. Диалоговое окно "Линейная регрессия"

После нажатия кнопки «ОК» выходные данные инструмента анализа данных отображаются в правой части рис. 2. Стандартные стандартные ошибки с использованием МНК (без надежных стандартных ошибок) вместе с соответствующими p-значениями также были определены вручную. добавлено к рисунку в диапазоне P16:Q20, чтобы вы могли сравнить выходные данные, используя надежные стандартные ошибки, со стандартными ошибками OLS.

Рисунок 2. Линейная регрессия с надежными стандартными ошибками

Как видно из рисунка 2, единственным коэффициентом, значительно отличающимся от нуля, является коэффициент младенческой смертности. Стандартная ошибка коэффициента младенческой смертности составляет 0,42943 (ячейка I18) при использовании надежных стандартных ошибок (версия HC3) по сравнению с 0,300673 (ячейка P18) при использовании OLS.

Обратите внимание, что некоторые надежные стандартные ошибки ниже, чем соответствующие стандартные ошибки МНК, а некоторые выше.

Внимание! При использовании устойчивых стандартных ошибок F-статистика (ячейка K12 на рис. 2) неточна, поэтому на нее и соответствующее значение p полагаться не следует.

Функция реальной статистики. Следующая функция массива вычисляет коэффициенты и их стандартные ошибки для взвешенной линейной регрессии. Здесь R1 — это массив n × k, содержащий выборочные данные X, а R2 — это массив n × 1, содержащий образец данных Y.

Диапазон

RRegCoeff(R1, R2, hc, con) = kk × 2, состоящий из вектора коэффициентов регрессии, за которым следует вектор стандартных ошибок этих коэффициентов, где < em>kk = k+1, если con = TRUE (по умолчанию) и kk = k, если con = FALSE (регрессия без перехват) и hc = значение от 0 до 4, представляющее надежные стандартные ошибки от HC0 до HC4 (по умолчанию = 3).

Например, диапазон H17:I20 содержит формулу массива рабочего листа =RRegCoeff(C4:E53,B4:B53.

Ссылка:

Хейс, А. и Кай, (2007) Использование согласованных с гетероскедастичностью оценок стандартных ошибок в регрессии МНК: введение и программная реализация. Behavior Research Methods 2007, 39 (4), 709–722.

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