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

Задача №3. Расчёт параметров регрессии и корреляции с помощью Excel

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

.

3. Оцените тесноту связи с помощью показателей корреляции и детерминации.

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

5. Оцените с помощью средней ошибки аппроксимации качество уравнений.

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

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

8. Оцените полученные результаты, выводы оформите в аналитической записке.

Решение:

Решим данную задачу с помощью Excel.

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

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

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН.

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х – диапазон, содержащий данные факторного признака;

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

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

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

Значение коэффициента b	Значение коэффициента a
Стандартная ошибка b	Стандартная ошибка a
Коэффициент детерминации R 2	Стандартная ошибка y
F-статистика	Число степеней свободы df
Регрессионная сумма квадратов

Рисунок 4 Результат вычисления функции ЛИНЕЙН

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

Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.

3. Коэффициент детерминации означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х – среднедушевого прожиточного минимума, а 48% — действием других факторов, не включённых в модель.

По вычисленному коэффициенту детерминации можно рассчитать коэффициент корреляции: .

Связь оценивается как тесная.

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

Для уравнения прямой средний (общий) коэффициент эластичности определим по формуле:

Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее, и то же самое произведём со значениями у.

Рисунок 5 Расчёт средних значений функции и аргумент

Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.

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

Порядок действий следующий:

1) проверьте доступ к Пакету анализа. В главном меню последовательно выберите: Файл/Параметры/Надстройки.

2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

3) В окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

• Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

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

4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия, а затем нажмите кнопку ОК.

5) Заполните диалоговое окно ввода данных и параметров вывода:

Входной интервал Y – диапазон, содержащий данные результативного признака;

Входной интервал X – диапазон, содержащий данные факторного признака;

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

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

Затем нажмите кнопку ОК.

Рисунок 6 Диалоговое окно ввода параметров инструмента Регрессия

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

Рисунок 7 Результат применения инструмента регрессия

5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.

Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»

Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:

Рисунок 9 Расчёт средней ошибки аппроксимации

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

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

6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера:

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

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

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

.

для числа степеней свободы

На рисунке 7 имеются фактические значения t-статистики:

t-критерий для коэффициента корреляции можно рассчитать двумя способами:

I способ:

где – случайная ошибка коэффициента корреляции.

Данные для расчёта возьмём из таблицы на Рисунке 7.

II способ:

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

Поэтому гипотеза Н₀ отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы.

Доверительный интервал для параметра a определяется как

Для параметра a 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для коэффициента регрессии определяется как

Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:

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

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

Тогда прогнозное значение прожиточного минимума составит:

Ошибку прогноза рассчитаем по формуле:

где

Дисперсию посчитаем также с помощью ППП Excel. Для этого:

1) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.

2) В окне Категория выберете Статистические, в окне функция – ДИСП.Г. Щёлкните по кнопке ОК.

3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК.

Рисунок 10 Расчёт дисперсии

Получили значение дисперсии

Для подсчёта остаточной дисперсии на одну степень свободы воспользуемся результатами дисперсионного анализа как показано на Рисунке 7.

Доверительные интервалы прогноза индивидуальных значений у при с вероятностью 0,95 определяются выражением:

Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.

Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. – М.: Финансы и статистика, 2003. – 192 с.: ил.

Прогнозирование продаж в Excel с учетом сезонности

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

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

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

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

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

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

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

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

1. Аддитивная модель
2. Мультипликативная модель
3. Смешанная модель

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Алгоритм построения модели

1. Выравниваем ряд с помощью скользящей средней, то есть сглаживаем ряд и отфильтровываем высокочастотные колебания.
2. Рассчитываем значение сезонной компоненты St.
3. Рассчитываем значения Tt с использованием полученного уравнения тренда.
4. Используя полученные значения St и Tt, находим прогнозные значения уровней временного ряда.
5. Оцениваем качество модели.

Реализация на практике

Итак, мы имеем на руках данные о продажах за 2016 и 2017 год и хотим спрогнозировать продажи на 2018 год.

Шаг 1

Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Шаг 2

Так как мы рассматриваем аддитивную модель вида:

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

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

Шаг 3

Теперь рассчитываем значения уровня тренда T(t) по тому уравнению, которое мы получили при построении сглаженного тренда на первом шаге.

T(t) = — 23294 + 34114 * t — 1593 *t^2 + 26,3 *t^3

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

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

Модель может считаться адекватной, если:

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении ��

Полезные ссылки:

• Ссылка на пример Google Sheets
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
• Бывшев В.А. Эконометрика

Екатерина Шипова

Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

• Прогнозирование продаж в Excel с учетом сезонности — 27.06.2018
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности — 05.06.2018
• Когортный анализ. Сколько пользователей к вам вернулось? — 24.05.2018

Рубрика: Анализ данных | Тег: MS Excel

15 replies on “ Прогнозирование продаж в Excel с учетом сезонности ”

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

Средняя ошибка аппроксимации.

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

Построенное уравнение регрессии считается удовлетворительным, если значение не превышает 10% – 12%.

Пример.

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

Номер региона	Среднедушевой денежный доход в месяц, тыс. руб.,	Объем розничной продажи телевизоров, тыс. шт.,
2,4	21,3
2,1
2,6	23,3
1,7	15,8
2,5	21,9
2,4
2,6
2,8	23,9
2,6
2,6	24,6
2,5
2,9
2,6
2,2
2,6
3,3	31,9
3,9
35,4
3,7
3,4

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

Рассмотрим вопрос применения модели линейной регрессии в этой задаче.

Построим поле корреляции, т.е. нанесем исходные данные на координатную плоскость. Для этого воспользуемся, например, возможностями MS Excel 2003.

Подготовим таблицу исходных данных.

Нанесем на координатную плоскость исходные данные:

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

По исходным данным рассчитываем необходимые суммы:

Номер региона
2,4	21,3	51,12	5,76	453,69
2,1	44,1	4,41
2,6	23,3	60,58	6,76	542,89
1,7	15,8	26,86	2,89	249,64
2,5	21,9	54,75	6,25	479,61
2,4	5,76
2,6	57,2	6,76
2,8	23,9	66,92	7,84	571,21
2,6	67,6	6,76
2,6	24,6	63,96	6,76	605,16
2,5	52,5	6,25
2,9	78,3	8,41
2,6	54,6	6,76
2,2	52,8	4,84
2,6	62,4	6,76
3,3	31,9	105,27	10,89	1017,61
3,9	128,7	15,21
35,4	141,6	1253,16
3,7	125,8	13,69
3,4	105,4	11,56
Сумма	57,4	530,1	1504,46	164,32	13926,97

Составляем систему уравнений:

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

Тогда, согласно теореме Крамера,

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

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

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

Для оценки тесноты линейной зависимости рассчитаем коэффициент детерминации. Для этого необходимо провести ряд дополнительных вычислений.

Прежде всего, найдем выборочное среднее по формуле:

Для рассматриваемого примера имеем:

Теперь произведем расчет остальных вспомогательных величин:

Номер региона
19,76	8,24	67,89	2,76	7,60
2,4	21,3	22,75	-1,45	2,11	-3,94	15,55
2,1	20,51	0,49	0,24	-4,24	18,00
2,6	23,3	24,25	-0,95	0,90	-1,94	3,77
1,7	15,8	17,52	-1,72	2,95	-9,44	89,17
2,5	21,9	23,50	-1,60	2,56	-3,34	11,17
2,4	22,75	-2,75	7,57	-5,24	27,49
2,6	24,25	-2,25	5,04	-3,24	10,52
2,8	23,9	25,74	-1,84	3,39	-1,34	1,80
2,6	24,25	1,75	3,08	0,76	0,57
2,6	24,6	24,25	0,35	0,13	-0,64	0,41
2,5	23,50	-2,50	6,24	-4,24	18,00
2,9	26,49	0,51	0,26	1,76	3,09
2,6	24,25	-3,25	10,54	-4,24	18,00
2,2	21,26	2,74	7,53	-1,24	1,54
2,6	24,25	-0,25	0,06	-1,24	1,54
3,3	31,9	29,48	2,42	5,86	6,66	44,32
3,9	33,96	-0,96	0,93	7,76	60,17
35,4	34,71	0,69	0,47	10,16	103,17
3,7	32,47	1,53	2,34	8,76	76,69
3,4	30,23	0,77	0,60	5,76	33,14
Сумма	57,4	530,1	130,68	545,73

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

Для вычисления коэффициента детерминации воспользуемся формулой ():

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

Построим линию регрессии на корреляционном поле, для чего добавим на координатной плоскости точки, соответствующие уравнению регрессии ().

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

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

Нажав на ОК, получаем еще одну прямую на диаграмме, которая совпадает с построенными ранее точками линии регрессии:

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

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

Номер региона
19,76	8,24	0,29
2,4	21,3	22,75	-1,45	0,07
2,1	20,51	0,49	0,02
2,6	23,3	24,25	-0,95	0,04
1,7	15,8	17,52	-1,72	0,11
2,5	21,9	23,50	-1,60	0,07
2,4	22,75	-2,75	0,14
2,6	24,25	-2,25	0,10
2,8	23,9	25,74	-1,84	0,08
2,6	24,25	1,75	0,07
2,6	24,6	24,25	0,35	0,01
2,5	23,50	-2,50	0,12
2,9	26,49	0,51	0,02
2,6	24,25	-3,25	0,15
2,2	21,26	2,74	0,11
2,6	24,25	-0,25	0,01
3,3	31,9	29,48	2,42	0,08
3,9	33,96	-0,97	0,03
35,4	34,71	0,69	0,02
3,7	32,47	1,53	0,05
3,4	30,23	0,77	0,02

Здесь столбец «» – это значения , рассчитанные с помощью построенного уравнения регрессии, столбец «» – это столбец так называемых «остатков»: разностей между исходными значениями , и рассчитанными с помощью уравнения регрессии , и, наконец, последний столбец «» – это вспомогательный столбец для вычисления элементов суммы по формуле (). Просуммируем теперь элементы последнего столбца и разделим полученную сумму на 21 – общее количество исходных данных:

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

Итак, средняя ошибка аппроксимации оказалась около 8%, что говорит о небольшой погрешности построенной модели. Данную модель, с учетом неплохих характеристик ее качества, вполне можно использовать для прогноза – одной из основных целей эконометрического анализа. Предположим, что среднедушевой месячный доход в одном из регионов составит 4,1 тыс. руб. Оценим, каков будет уровень продаж телевизоров в этом регионе согласно построенной модели? Для этого необходимо выбранное значение фактора подставить в уравнение регрессии ():

т.е. при таком уровне дохода, розничная продажа телевизоров составит, в среднем, 35 480 телевизоров.

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:

Средняя ошибка аппроксимации

По семи территориям Уральского района за 199Х г. известны значения двух признаков.

Требуется:
1. Для характеристики зависимости у от х рассчитать параметры следующих функций:
а) линейной;
б) степенной;
в) показательной;
г) равносторонней гиперболы (так же нужно придумать как предварительно линеаризовать данную модель).
2. Оценить каждую модель через среднюю ошибку аппроксимации А_ср и F-критерий Фишера.

Решение проводим при помощь онлайн калькулятора Линейное уравнение регрессии.
а) линейное уравнение регрессии;
Использование графического метода.
Этот метод применяют для наглядного изображения формы связи между изучаемыми экономическими показателями. Для этого в прямоугольной системе координат строят график, по оси ординат откладывают индивидуальные значения результативного признака Y, а по оси абсцисс — индивидуальные значения факторного признака X.
Совокупность точек результативного и факторного признаков называется полем корреляции.

На основании поля корреляции можно выдвинуть гипотезу (для генеральной совокупности) о том, что связь между всеми возможными значениями X и Y носит линейный характер.
Линейное уравнение регрессии имеет вид y = bx + a + ε
Здесь ε — случайная ошибка (отклонение, возмущение).
Причины существования случайной ошибки:
1. Невключение в регрессионную модель значимых объясняющих переменных;
2. Агрегирование переменных. Например, функция суммарного потребления – это попытка общего выражения совокупности решений отдельных индивидов о расходах. Это лишь аппроксимация отдельных соотношений, которые имеют разные параметры.
3. Неправильное описание структуры модели;
4. Неправильная функциональная спецификация;
5. Ошибки измерения.
Так как отклонения ε_i для каждого конкретного наблюдения i – случайны и их значения в выборке неизвестны, то:
1) по наблюдениям x_i и y_i можно получить только оценки параметров α и β
2) Оценками параметров α и β регрессионной модели являются соответственно величины а и b, которые носят случайный характер, т.к. соответствуют случайной выборке;
Тогда оценочное уравнение регрессии (построенное по выборочным данным) будет иметь вид y = bx + a + ε, где e_i – наблюдаемые значения (оценки) ошибок ε_i, а и b соответственно оценки параметров α и β регрессионной модели, которые следует найти.
Для оценки параметров α и β — используют МНК (метод наименьших квадратов).
Система нормальных уравнений.

x y x 2 y 2 x • y y(x) (y i -y cp ) 2 (y-y(x)) 2 |y — y x |:y 45,1 68,8 2034,01 4733,44 3102,88 61,28 119,12 56,61 0,1094 59 61,2 3481 3745,44 3610,8 56,47 10,98 22,4 0,0773 57,2 59,9 3271,84 3588,01 3426,28 57,09 4,06 7,9 0,0469 61,8 56,7 3819,24 3214,89 3504,06 55,5 1,41 1,44 0,0212 58,8 55 3457,44 3025 3234 56,54 8,33 2,36 0,0279 47,2 54,3 2227,84 2948,49 2562,96 60,55 12,86 39,05 0,1151 55,2 49,3 3047,04 2430,49 2721,36 57,78 73,71 71,94 0,172 384,3 405,2 21338,41 23685,76 22162,34 405,2 230,47 201,71 0,5699

Примечание: значения y(x) находятся из полученного уравнения регрессии:
y(45.1) = -0.35*45.1 + 76.88 = 61.28
y(59) = -0.35*59 + 76.88 = 56.47
. . .

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

Поскольку ошибка меньше 15%, то данное уравнение можно использовать в качестве регрессии.

F-статистики. Критерий Фишера.
Проверка значимости модели регрессии проводится с использованием F-критерия Фишера, расчетное значение которого находится как отношение дисперсии исходного ряда наблюдений изучаемого показателя и несмещенной оценки дисперсии остаточной последовательности для данной модели.
Если расчетное значение с k1=(m) и k2=(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

где m=1 для парной регрессии.
3. Табличное значение определяется по таблицам распределения Фишера для заданного уровня значимости, принимая во внимание, что число степеней свободы для общей суммы квадратов (большей дисперсии) равно 1 и число степеней свободы остаточной суммы квадратов (меньшей дисперсии) при линейной регрессии равно n-2.
4. Если фактическое значение F-критерия меньше табличного, то говорят, что нет основания отклонять нулевую гипотезу.
В противном случае, нулевая гипотеза отклоняется и с вероятностью (1-α) принимается альтернативная гипотеза о статистической значимости уравнения в целом.
Табличное значение критерия со степенями свободы k1=1 и k2=5, Fkp = 6.61
Поскольку фактическое значение F < Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).

б) степенная регрессия;
Решение проводится с помощью сервиса Нелинейная регрессия. При выборе укажите Степенная y = ax b
в) показательная регрессия;
г) модель равносторонней гиперболы.
Система нормальных уравнений.

Поскольку ошибка меньше 15%, то данное уравнение можно использовать в качестве регрессии.