Лабораторная работа. Парная линейная регрессия

Уравнение парной линейной регрессии имеет вид: 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. Построение нелинейных прогнозов

Строим линию тренда, выводим на нее формулу и по этой формуле считаем.

Лабораторные работы

1. Аналитические показатели динамики
2. Средние показатели
3. Построение линии тренда
4. Парная линейная регрессия
5. Множественная регрессия и корреляция

Лабораторные работы по прогнозированию в виде pdf

Ссылка на Курс «Прогнозирование социально-экономического развития…»