Уравнение парной линейной регрессии имеет вид: y=a+bx. Параметры a и b определяются методом наименьших квадратов.
В статье «Построение линии тренда» мы уже получали уравнение регрессии для данных по численности населения РК.
В данной статье рассмотрим более подробно способы получения этого уравнения.
1. Функция ЛИНЕЙН
- Перенесем для удобства на новый лист в ячейки B2:C24 наши данные.
- В ячейку B26 пишем «Коэффициенты парной линейной регрессии».
- Выделяем ячейки B27:С27.
- Вызываем мастера функций.
- Выбираем функцию ЛИНЕЙН, задаем необходимые диапазоны.
- Нажимаем Ctrl-Shift и мышкой Ok.
В результате ячейка B27 содержит значение коэффициента a, ячейка C27 содержит значение коэффициента b.
2. Инструмент анализа Регрессия
- Необходимо убедиться, что Пакет анализа включен.
- Выбираем Данные – Анализ данных – Регрессия.
- Задаем необходимые диапазоны:
- Входной интервал Y – $C$2:$C$24
- Входной интервал X – $B$2:$B$24
- Галочка на Метки
- Выходной интервал – $F$2
- Обсудим некоторые параметры
- В ячейке G6 указан коэффициент детерминации. Чем он ближе к 1, тем точнее результат. Заметим, что он совпадает с результатом на линии тренда.
- Ячейки G18 и G19 содержат коэффициенты уравнения парной линейной регрессии.
3. Прогнозирование данных
Построим прогноз численности населения в 2017-2021 годах тремя способами и сравним полученные данные. В ячейки L2:L7 введем «Год, 2017, 2018, 2019, 2020, 2021».
1-й способ. Прогноз по формуле регрессии
У нас есть коэффициенты. Используем их для задания формулы.
В ячейку M2 пишем «Прогноз по формуле регрессии».
В M3 пишем
=$C$27+$B$27*L3
И распространяем эту формулу на M4:M7
2-й способ. Функция ПРЕДСКАЗ
В ячейку M2 пишем «Функция ПРЕДСКАЗ».
В N3 пишем
=ПРЕДСКАЗ(L3;$C$3:$C$24;$B$3:$B$24)
И распространяем эту формулу на N4:N7
3-й способ. Функция ТЕНДЕНЦИЯ
В ячейку O2 пишем «Функция ТЕНДЕНЦИЯ».
В O3 пишем
=ТЕНДЕНЦИЯ($C$3:$C$24;$B$3:$B$24;L3)
И распространяем эту формулу на O4:O7
Заметим, что результат, полученный тремя способами, одинаков.
4. Самостоятельная работа
На основе данных «Показатели эффективности…» самостоятельно (любым способом) постройте прогноз значений на 5 лет вперед для следующих показателей:
- Среднемесячная номинальная начисленная заработная плата учителей муниципальныx общеобразовательных учреждений.
- Среднегодовая численность постоянного населения.
- Объем инвестиций в основной капитал (за исключением бюджетных средств) в расчете на 1 жителя.
5. Интервальный прогноз
6. Построение нелинейных прогнозов
Строим линию тренда, выводим на нее формулу и по этой формуле считаем.
Лабораторные работы
- Аналитические показатели динамики
- Средние показатели
- Построение линии тренда
- Парная линейная регрессия
- Множественная регрессия и корреляция
Лабораторные работы по прогнозированию в виде pdf
Ссылка на Курс «Прогнозирование социально-экономического развития…»
Как получить прогноз в диапазоне?
А здесь не наоборот?
«В результате ячейка B27 содержит значение коэффициента a, ячейка C27 содержит значение коэффициента b.»
т.е. а и б надо местами поменять
Комментарии на сайте снова открыты!