Лабораторная работа № 2.

Задано изменение признака в зависимости от даты.

Таблица 1.

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

Задание:

1. Выявите трендовый характер изменения показателя, статистические данные которого представлены в таблице (значение показателя увеличивается в среднем; уменьшается в среднем; показатель, в среднем не изменяется).

2. Используя функцию ТЕНДЕНЦИЯ получить прогнозные значения рассматриваемого показателя на первое число каждого месяца в период 2010 – 2016 годов.

3. Спрогнозировать значение показателя на 22 ноября 2018 года при условии сохранения общего линейного характера его изменения.

4. Изобразить график данного показателя и показателя, полученного в пункте 2 задания.

Ход решения:

Используя пакет Excel, введем данные в электронную таблицу. Следующим шагом будет выявление трендового характера изменения показателя. Для этого найдем параметры тренда и собственно его уравнение. Для этого воспользуемся Мастером функции. Для этого переходим на вкладку «Формулы » «Вставить функцию ».

Рисунок 1. Использование формул для расчета параметров.

В появившемся окне Мастера Функции выбираем функцию «ЛИНЕЙН » или же «НАКЛОН ». Нажимаем на название функции и в появившемся окне заполняем строки Известные значения Y (где указываем диапазон значений Данных) и Известные значения X (где указываем диапазон значений Дат). Данные действия аналогичны, как для функции «ЛИНЕЙН » так и для функции «НАКЛОН ».

Рисунок 2. Мастер функций и его возможности.

В результате получаем следующие результаты расчетов:

Таблица 2.

Параметры уравнения тренда.

Следовательно, уравнение линии тренда:

Попробуем объяснить полученную модель. С каждым днем значение признака Y уменьшается в среднем на .

Построим изображение линии тренда и исходные данные на точечном графике статистических данных. Для этого в пакете программе Excel переходим в меню «Вставка» «Диаграммы» «Точечная с маркерами».

Рисунок 3. Выбор вида диаграммы.

Нажимаем правой кнопкой мыши на появившейся области и нажимаем на «Выбрать данные». В итоге появится новое окно:

Рисунок 4. Выбор данных для постройки поля корреляции.

В появившимся окне нажимаем на кнопку «Добавить». Появится новое окно, в котором есть два ряда: «Имя ряда », «Значения ». В строке «Имя ряда » прописываем «Данные ». В строке «Значения » выбираем диапазон значений по Y «Данные ». Кроме того, в области Подписи горизонтальной оси выбираем наш массив Дат.

Рисунок 5. Заполнение данных.

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

Рисунок 5. Точечный график статистических данных.

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

Рисунок 6. Нанесение линии тренда.

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

Рисунок 7. Параметры линии тренда.

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

Рисунок 8. Линия регрессии.

Используя функцию ТЕНДЕНЦИЯ получим прогнозные значения рассматриваемого показателя на первое число каждого месяца в период 2010 – 2016 годов. Для этого создаем массив Дат начиная с 01.01.2010 и по 01.12.2016. Далее воспользуемся тем самым Мастером функции.

В появившемся окне Мастера Функции выбираем функцию «Тенденция ». Нажимаем на название функции и в появившемся окне заполняем строки Известные значения Y (где указываем диапазон значений Данных) и Известные значения X (где указываем диапазон значений Дат). Кроме того, очень важно в данном случае, чтоб наши значения не менялись в ходе подсчетов, поэтому ставим дополнительное ограничение в виде $B$2:$B$51 для значений Y и $A$2:$A$51 для значений X. Далее в строке Новые значения Х выбираем клетку в которой находится массив Дат начиная с 01.01.2010 и по 01.12.2016.

Рисунок 9. Мастер функций и его возможности.

В результате получаем следующие результаты расчетов.

Таблица 3.

Тенденция.

В итоге мы, получили массив данных, который явно демонстрирует, что с каждым месяцем значения признака Y падали. Наглядно результаты расчетов представлены на рисунке 10. Который построен аналогично с графиком Исходных данных.

Рисунок 10. Тенденция.

Используя функцию ТЕНДЕНЦИЯ получим прогнозное значение на 22 ноября 2018 года при условии сохранения общего линейного характера его изменения. Для этого воспользуемся тем самым Мастером функции.

В результате получаем следующие результаты расчетов:

Таблица 4.

Параметры уравнения тренда.

Следовательно, уравнение линии тренда:

А прогноз на 22 ноября 2018 года при условии сохранения общего линейного характера его изменения показал значение Y на уровне 0,212367432.