Как рассчитать среднюю ошибку аппроксимации в excel

Оценка средней ошибки аппроксимации

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

Для сравнения используются величины отклонений, выраженные в процентах к фактическим значениям. Например, если для первого наблюдения у —у = 5 и у = 20, а для второго у — у = 10 и у = 50, ошибка аппроксимации составит 25 % для первого наблюдения и 20 % — для второго.

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

Отклонения (у —у) можно рассматривать как абсолютную ошибку

аппроксимации, а —— 100% — как относительную ошибку аппрок-

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

Ошибка аппроксимации в пределах 5—7 % свидетельствует о хорошем подборе модели к исходным данным.

Для примера на рисункеЗ.З имеем А = 21%.

Построение эконометрических моделей с помощью MS Excel

При расчете коэффициента корреляции г применяется встроенная статистическая функция MS Excel КОРРЕЛ, либо функция PEARSON. Применение обеих этих функций дает один результат.

Для определения коэффициента детерминации г 2 используется встроенная статистическая функция КВПИРСОН.

Расчет параметров уравнения линейной парной или множественной регрессии можно использовать статистическую функцию Excel ЛИ-НЕЙН.

При вычислении среднего значения х или у , дисперсии Щ или о 2 , среднего квадратического отклонения о, или , ковариации двух признаков cov(x,y) можно использовать следующие статистические функции MS Excel соответственно СРЗНАЧ, ДИСПР, СТАНДОТ-КЛОНП, КОВАР.

При оценивании статистической значимости коэффициента корреляции г, а также параметров а и b уравнения регрессии у = а + Ьх необходимо определить вначале табличное значение /-статистики Стью-дента /_табл, задаваясь уровня значимости ос и числом степеней свободы п — к — 1, где п и к соответственно число точек поля корреляции (измерений) и число факторных признаков. /_та6л можно определить либо по таблицам, либо используя статистическую функцию Excel СТЬЮДРАСПОБР.

При оценивании статистической значимости модели регрессии в целом используется / -критерий Фишера. При этом либо по таблицам, либо с помощью статистической функции Excel ЕРАСПОБР рассчитывается /-статистика Фишера.

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

Кроме того, поставив галочки в соответствующих окнах Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R A 2), можно получить уравнение линии регрессии и значение коэффициента детерминации г 2 , характеризующего качество построенной модели регрессии.

Пример. Исследованием, проведенным в 20 случайно выбранных магазинах, получены следующие данные о числе посетителей магазинов и выручке в течение дня (табл. 3.3).

Исходные данные

Номер магазина

Число посетителей (х)

Выручка (у), тыс. руб.

Номер магазина

Число посетителей (х)

Выручка (у), тыс. руб.

• 1. Построить точечную диаграмму зависимости выручки от числа посетителей магазина.
• 2. Добавить на график поля корреляции линию тренда (линию регрессии) в виде прямой линии с ее уравнением, а также коэффициент детерминации.
• 3. Найти коэффициент корреляции г и коэффициент детерминации г 2 и охарактеризовать степень влияния числа посетителей на выручку магазина.
• 4. Рассчитать коэффициент а и коэффициент регрессии b при помощи встроенных статистических функций.
• 5. Получить уравнение парной линейной регрессии.
• 6. Оценить качество построенного уравнения.
• 7. Дать прогноз ежедневной выручки магазина, который посетят 600 покупателей.

Решение. Данные, приведенные в таблице 3.3, представим в виде точечной диаграммы — поля корреляции, которое наглядно показывает наличие линейной зависимости выручки от продажи (результативный признак у) от числа посетителей магазина (факторный признака) (рис. 3.5).

Рис. 3.5. Поле корреляции и линия регрессии

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

Используя контекстное меню для точек графика и опцию Добавить линию тренда, изобразим на графике уравнение линии тренда и коэффициент детерминации (рис. 3.6).

Для определения коэффициента корреляции используем встроенную статистическую функцию КОРРЕЛ. В результате имеем коэффициент корреляции г = 0,945.

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

Для определения коэффициента детерминации г 2 используем встроенную статистическую функцию КВПИРСОН. В результате имеем г 2 = 0,893.

Рис. 3.6. Графическое окно Добавить линию тренда

Вывод: коэффициент детерминации показывает, какая доля вариации (изменения) результативного признака у объясняется влиянием факторного признака х в регрессионной модели. В данном случае 89,3 % вариации ежедневной выручки магазинов может быть объяснено влиянием числа покупателей. И только 10,7 % вариации можно объяснить иными факторами, не включенными в уравнение регрессии.

Для расчета коэффициента а и коэффициента регрессии b можно использовать встроенную статистическую функцию НАКЛОН (определяет Ь) и ОТРЕЗОК (определяет а). Названия этих функций отвечают геометрическому смыслу коэффициентов регрессии: b — это тангенс угла наклона прямой регрессии, а а — отрезок, отсекаемый этой прямой на оси ординат.

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

Рис. 3.7. Графическое окно функции ЛИНЕЙН

При использовании функции ЛИНЕЙН надо предварительно выделить блок из двух ячеек по горизонтали, например, E18:F18. После этого вызвать функцию ЛИНЕЙН, ввести адреса блока ячеек х и у, затем одновременно нажать три кнопки Ctri + Shift + Enter, завершив матричную операцию.

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

В результате имеем b = 0,091, а = 16,97 .

Вывод: поскольку коэффициент регрессии Ь = 0,091, то это означает, что при увеличении факторного признака х на единицу ожидаемое значение результативного признака у возрастет на 0,091 (или можно сказать, что ожидаемый прирост ежедневной выручки составит 91 тыс. руб. при привлечении в магазин 1 000 дополнительных посетителей).

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

Запишем уравнение линии регрессии у = а + Ьх , подставив найденные коэффициенты b и а. Имеем у = 16,97 + 0,091х. Результат совпал с рассчитанным автоматически при построении поля корреляции (см. рис. 3.5).

Оценим качество полученного уравнения линии регрессии. Для этого в одной из ячеек сначала рассчитаем

где п — число реализовавшихся значений признаков (число измерений).

В результате расчета получено Е_расч = 151,6 .

Затем определим ^_а6л для уровня значимости ос = 0,05, ?,=1 — число факторных переменных, к₂ = п— к_х — 1 = и — 2 — число степеней свободы. Можно пользоваться не таблицами, а встроенной статистической функцией РРАСПОБР, задав Вероятность сх = 0,05. Степень свободы! к = 1 и Степень свободы! к₂ = п — k_t -1 = п -2 = 18 . Панель инструментов функции РРАСПОБР представлена на рисунке 3.8. В результате использования этой функции получено табличное значение статистики Фишера /у_|бл = 4,41.

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

Рис. 3.8. Графическое окно функции РРАСПОБР

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

Отсюда следует, что прогнозируемая дневная выручка для магазина с 600 посетителями в день равна в среднем 71,57 тыс. руб. Для прогноза можно применить также встроенную функцию Excel ПРЕДСКАЗ.

В окна вызванной функции ввести значение х_{прогноз}, который равен 600, и диапазоны факторного и результативного признаков (рис. 3.9).

Рис. 3.9. Графическое окно функции ПРЕДСКАЗ

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

Можно применить MS Excel комплексно и автоматизировать все расчеты, если использовать Надстройку Пакет анализа.

Порядок регрессионного анализа в MS Excel

После подключения Пакета анализа на вкладке Данные на ленте выбираем кнопку Диализ данных. Открывается диалоговое окно, в котором выбираем строку Регрессия (рис. 3.10).

Рис. 3.10. Выбор инструмента анализа в Анализе данных

Откроется новое окно вида, представленного ниже на рисунке 3.11.

Рис. 3.11. Графическое окно функции Регрессия

Пример. Используя данные в таблице 3.3, примените Данные — Анализ данных — Регрессия и сравните результаты с уже полученными ранее.

• 1. Входим в пакет Анализ данных и далее выбираем Регрессия (см. рис. 3.9).
• 2. В окне «Входной интервал Y» открывшейся панели инструментов определяем границы столбца «Выручка, тыс. руб.» (вместе с заголовком) (рис. 3.12).
• 3. В окне «Входной интервал X» определяем границы столбца «Число посетителей» (вместе с заголовком) (см. рис. 3.12).
• 4. Поставим галочки в окне Метки (это означает, что надо вместе с данными выделять заголовки, что мы и сделали).
• 5. Уровень надежности задать 95 %. Это означает, что уровень значимости равен а = 0,05, а доверительная вероятность, с которой можно будет доверять полученным регрессионным оценкам, равна Р = 1 — а = 0,95.
• 6. Выходной интервал, куда будут помещены результаты, зададим, начиная с ячейки А23.

Результаты регрессии будут выданы под заголовком «Вывод итогов» (рис. 3.13):

• ? сведения о значениях множественного коэффициента корреляции (ячейка В26),
• ? критерия детерминации (R-квадрат) (ячейка В27).

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

? F-статистики (ячейка Е34). Это расчетное значение F_pac4. Сравните с найденным выше значением.

Рис. 3.12. Графическое окно функции Регрессия после заполнения

Рис. 3.13. Результаты применения инструмента анализа данных Регрессия

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

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

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

Таблица 1 – Исходные данные

Для решения поставленной задачи и упрощения расчетов воспользуемся средствами табличного процессора MS Excel .

Первым этапом будет ввод исходных данных и построение линейной модели регрессии.

Рисунок 1 – Получение параметров линейной модели регрессии.

Таким образом, получили следующее линейное уравнение регрессии:

На рисунке 1 показано значение коэффициента детерминации R 2 = 0,94. То есть 94% значений переменной Y объясняется значениями переменной X . Таким образом, можно говорить о высоком качестве уравнения регрессии.

Следующим этапом будет построение квадратического уравнения регрессии.

Рисунок 2 – Квадратическое уравнение регрессии и коэффициент детерминации.

Как видим из рисунка 2, коэффициент детерминации составляет R 2 = 0,9654, то есть качество уравнения несколько выше линейного уравнения.

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

Рисунок 3 – Показательная регрессия и коэффициент детерминации.

Уравнение показательной регрессии объясняет 94,06% значений зависимой переменной Y от факторной переменной X .

Рисунок 4 – Степенная регрессия и коэффициент детерминации.

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

Рисунок 5 – Логарифмическая регрессия и коэффициент детерминации

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

В заключении строим график гиперболической регрессии.

Рисунок 6 – Гиперболическая регрессия и коэффициент детерминации

Как видим данное уравнение регрессии является наихудшим по качеству, поскольку объясняет только 56% значений зависимой переменной Y .

Наилучшим по качеству уравнением регрессии в данной задаче является уравнение квадратической регрессии. Данное уравнение объясняет 96,54% значений зависимой переменной Y .

Задача 2. По данным приведенным в таблице 2 требуется:

1. Построить линейное уравнение регрессии Y по X .

2. Рассчитать линейный коэффициент корреляции, коэффициент детерминации и среднюю ошибку аппроксимации.

3. Рассчитать коэффициент эластичности.

Таблица 2 – Исходные данные

Для решения поставленной задачи воспользуемся средствами табличного процессора MS Excel .

Для этого создаем новый лист и вводим исходные данные

Рисунок 7 – Исходные данные.

Уравнение парной регрессии имеет вид:

— x , y – факторная и зависимые переменные;

— a , b – коэффициенты уравнения.

Коэффициенты уравнения парной линейной регрессии будем искать с помощью метода наименьших квадратов и табличного процессора MS Excel . Согласно МНК коэффициенты уравнения находятся по следующим формулам:

Составим дополнительную таблицу и произведем промежуточные расчеты в табличном процессоре:

Рисунок 8 – Промежуточные расчеты и расчет коэффициентов уравнения.

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

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

Поскольку в уравнении стоит знак «+», то можно предположить наличие прямой связи между X и Y , т.е. значения Y напрямую зависят от значений X .

С помощью средств табличного процессора оценим тесноту этой связи:

Рисунок 9 – Оценка тесноты связи с помощью коэффициента корреляции.

Коэффициент корреляции r_yx = 0,47. Отсюда можно сделать вывод, что между переменными X и Y существует умеренная связь. Положительное значение коэффициента корреляции подтверждает наше предположение о направлении связи – Y зависит от X .

Между коэффициентом корреляции и коэффициентом детерминации существует взаимосвязь:

Отсюда получаем значение коэффициента детерминации: R 2 = 0,22. То есть уравнение регрессии объясняет 22% значений зависимой переменной. Можно говорить о невысоком качестве уравнения регрессии.

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

Проведем дополнительные расчеты:

Рисунок 10 – Промежуточные расчеты и расчет средней ошибки аппроксимации.

Получаем, что средняя ошибка аппроксимации не попадает в предел до 5 – 8% (А = 15%), что подтверждает наш вывод о невысоком качестве уравнения регрессии.

Коэффициент эластичности определим по следующей формуле:

Рисунок 11 – Расчет коэффициента эластичности.

Таким образом, при изменении значения Х на 1% значение Y изменится на 0,48%.

Задача 3. По данным приведенным в таблице 3 требуется:

1. Построить линейную модель множественной регрессии.

2. Записать стандартизированное уравнение множественной регрессии.

3. Рассчитать коэффициенты парной, частной и множественной корреляции. Проанализировать их.

Как рассчитать стандартную ошибку регрессии в Excel

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

Y = β 0 + β 1 X + … + β i X +ϵ

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

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

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

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

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

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

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

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

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

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

Если вы не видите эту опцию доступной, вам нужно сначала загрузить Data Analysis ToolPak .

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

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

Шаг 3: Интерпретируйте стандартную ошибку регрессии

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

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

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

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

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

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

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

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

Лабораторная работа №5, Парная регрессия и корреляция, MS Excel

Для расчета параметров уравнения линейной регрессии строим расчетную таблицу 2.

По формулам находим параметры регрессии

Получено уравнение регрессии:

.

Параметр регрессии позволяет сделать вывод, что с увеличением среднедушевого прожиточного минимума на 1 ден.ед. среднедневная заработная плата возрастает в среднем на 0,89 ден.ед.

После нахождения уравнения регрессии заполняем столбцы 7 – 10 таблицы 2.

2. Рассчитать линейный коэффициент парной корреляции, коэффициент детерминации и среднюю ошибку аппроксимации.

Тесноту линейной связи оценит коэффициент корреляции:

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

Это означает, что 67,6% вариации заработной платы (y) объясняется вариацией фактора x – среднедушевого прожиточного минимума.

Качество модели определяет средняя ошибка аппроксимации:

Качество построенной модели оценивается как хорошее, так как не превышает 10%.

3. Оценить статистическую значимость уравнения регрессии в целом и отдельных параметров регрессии и корреляции с помощью -критерия Фишера и t-критерия Стьюдента.

Оценку статистической значимости уравнения регрессии в целом проведем с помощью F-критерия Фишера. Фактическое значение F-критерия по формуле составит

Табличное значение критерия при пятипроцентном уровне значимости и степенях свободы k₁=1 и k₂=12-2=10 составляет F_табл=4,96. Так как , то уравнение регрессии признается статистически значимым.

Оценку статистической значимости параметров регрессии и корреляции проведем с помощью t-статистики Стьюдента и путем расчета доверительного интервала каждого из параметров.

Табличное значение t-критерия для числа степеней свободы df=n-2=12-2=10 и уровня значимости составит t_табл=2,23.

Определим стандартные ошибки (остаточная дисперсия на одну степень свободы

Фактические значения t-статистики превосходят табличное значение:

Поэтому параметры a, b и r_xy не случайно отличаются от нуля, а статистически значимы.

Рассчитаем доверительные интервалы для параметров регрессии a и b. Для этого определим предельную ошибку для каждого показателя:

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

4. Выполнить прогноз заработной платы y при прогнозном значении среднедушевого прожиточного минимума x, составляющем 107% от среднего уровня.

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

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

Ошибка прогноза составит:

Предельная ошибка прогноза, которая в 95% случаев не будет превышена, составит:

Доверительный интервал прогноза:

Выполненный прогноз среднемесячной заработной платы является надежным () и находится в пределах от 140,16 ден.ед. до 185,0 ден.ед.

6. На одном графике отложить исходные данные и теоретическую прямую.

7. Проверить вычисления в MS Excel.

Выбираем Сервис®Анализ данных®Регрессия. Заполняем диалоговое окно ввода данных и параметров вывода. Получаем следующие результаты:

Фактическое значение F-критерия Фишера:

Остаточная дисперсия на одну степень свободы:

Корень квадратный из остаточной дисперсии (стандартная ошибка):

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

Фактические значения t-критерия Стьюдента:

Как видим, найдены все рассмотренные выше параметры и характеристики уравнения регрессии, за исключением средней ошибки аппроксимации (значение t-критерия Стьюдента для коэффициента корреляции совпадает с t_b). Результаты «ручного счета» от машинного отличаются незначительно (отличия связаны с ошибками округления).