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

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

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

stat2016_2

4. Самостоятельная работа

На основе данных «Показатели эффективности…» самостоятельно (любым способом) постройте прогноз значений на 5 лет вперед для следующих показателей:

  • Среднемесячная номинальная начисленная заработная плата учителей муниципальныx общеобразовательных учреждений.
  • Среднегодовая численность постоянного населения.
  • Объем инвестиций в основной капитал (за исключением бюджетных средств) в расчете на 1 жителя.

5. Интервальный прогноз

6. Построение нелинейных прогнозов

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

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

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

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

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

Поделиться:
  • Добавить ВКонтакте заметку об этой странице
  • Мой Мир
  • Facebook
  • Twitter
  • LiveJournal
  • FriendFeed
  • В закладки Google
  • Google Buzz
  • Яндекс.Закладки
  • StumbleUpon
  • Technorati
  • БобрДобр
  • Memori.ru
  • МоёМесто.ru

2 комментария к “Лабораторная работа. Парная линейная регрессия”

  1. А здесь не наоборот?
    «В результате ячейка B27 содержит значение коэффициента a, ячейка C27 содержит значение коэффициента b.»
    т.е. а и б надо местами поменять

Комментарии закрыты.