Выполнение. Цель работы: ознакомиться с основными способами анализа временных рядов в MS Excel: выявление основной тенденции ряда динамики с помощью линий тренда;

Занятие 8

АНАЛИЗ ВРЕМЕННЫХ РЯДОВ

Цель работы: ознакомиться с основными способами анализа временных рядов в MS Excel: выявление основной тенденции ряда динамики с помощью линий тренда; сглаживание временного ряда методом скользящей средней и экспоненциальным сглаживанием; обработка с помощью рядов Фурье.

Основные положения

Задание 1. Построить график, содержащий точки экспериментального временного ряда, добавить линии трендов: линейного, логарифмического, полиномиального 2-ой и 3-ей степени, степенного, экспоненциального с соответствующими уравнениями. Оценить достоверность аппроксимации. Сформировать таблицу с результатами расчётов. Выполнить прогноз по каждому из трендов на два периода.

Таблица 1

Экспериментальные значения временного ряда

Выполнение

1. Ввести данные в диапазон A1: M2 (рис.1).

2. Выделить диапазон В1: M2 и построить график ряда, выполнив следующие действия: на вкладке Вставка в панели Диаграммы выбрать тип создаваемой диаграммы График, вариант графика График с маркерами. Отредактировать полученную диаграмму (рис.1).

Рис 1. График временного ряда с линией линейного тренда

3. Щёлкнуть правой кнопкой мыши по графику (выделить ряд) и выполнить команду Добавить линию тренда. Откроется диалоговое окно Формат линии тренда (рис.2).

4. На вкладке Параметры линии тренда выбрать тип линии Линейная и задать дополнительные параметры линии тренда. В разделе Название аппроксимирующей (сглаженной) кривой выбрать вариант автоматическое, опции показывать уравнение на диаграмме, поместить на диаграмму величину достоверности аппроксимации (R^2). Щёлкнуть по кнопке Закрыть. Результаты показаны на рис. 1.

Уравнение зависимости: , . Логарифмическая: , .

Полиномиальная степени 2: , .

Полиномиальная степени 3: , .

Степенная: , .

Экспоненциальная: , .

Рис. 2. Диалоговое окно Формат линии тренда

5. Щёлкнуть по области диаграммы правой кнопкой мыши, выбрать в контекстном меню команду Копировать, затем щёлкнуть на свободном участке листа Excel правой кнопкой мыши, выбрать команду Вставить, получить вторую диаграмму.

6. Щёлкнуть на второй диаграмме правой кнопкой мыши на линии тренда, выбрать в контекстном меню команду Очистить.

7. Выполнить п. 3-4, выбирая последовательно на вкладке Параметры линии тренда типы линии тренда: Логарифмическая, Полиномиальная Степень: 2, Полиномиальная Степень: 3, Степенная, Экспоненциальная. Записать полученные результаты:

8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости, рассчитанные по уравнению значения временного ряда, величину достоверности аппроксимации R^2 (таблица 2).

9. Для каждого из трендов выполнить прогноза на два периода, установив в разделе Прогноз вариант вперед на: 2 периодов. Результаты показать на графиках и добавить в таблицу 2.

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

Таблица 2

Результаты значений временного ряда, рассчитанные по различным зависимостям

Задание 2. Сгладить ряд динамики, дающий среднюю урожайность зерновых культур в хозяйстве (y) методом трёхлетней средней (исходные данные записаны в таблице 3).

Таблица 3