Порядок выполнения работы. 1. Ввод исходных данных задачи.Введем в ячейки A3:A10 годы, по которым имеются фактические данные, а в ячейки B3:B10 введем соответствующие значения уровня

1. Ввод исходных данных задачи. Введем в ячейки A3:A10 годы, по которым имеются фактические данные, а в ячейки B3:B10 введем соответствующие значения уровня безработицы (рис. 2.1).

Рис. 2.1. Расчеты методом скользящих средних

2. Расчет скользящего среднего по формуле. Отведем столбцы C и D для расчетов скользящего среднего по формуле (2.1) при интервале сглаживания m = 3 и m = 5 соответственно. В ячейку C5 введем формулу вычисления среднего значения соответствующего и двух предшествующих уровней ряда:

=СРЗНАЧ(B3:B5).

Скопируем эту формулу в ячейки C6:C10 с помощью автозаполнения.

Аналогично в ячейку D7 введем формулу расчета среднего из пяти уровней ряда:

=СРЗНАЧ(B3:B7).

Скопируем эту формулу вниз по столбцу D.

Результаты расчетов по формулам показаны на рис. 2.1.

3. Использование надстройки Пакет анализа для сглаживания методом скользящих средних. В меню Сервис должен быть пункт Анализ данных …. Если этого пункта нет, это означает, что надстройка Пакет анализа не загружена. Для подключения надстройки Пакет анализа выполним команду Сервис / Надстройки …. В открывшемся окне диалога установим флажок в строке Пакет анализа.

Вызов пакета анализа осуществляется командой Сервис / Анализ данных …. В окне Анализ данных следует выбрать инструмент анализа Скользящее среднее. Появляется окно Скользящее среднее, которое заполним, как показано на рис. 2.2.

Рис. 2.2. Окно Скользящее среднее для интервала сглаживания m = 3

· Поле Входной интервал задает ряд наблюдаемых (фактических) значений показателя y_i (диапазон B3:B10).

· Поле Интервал показывает количество данных, включаемых в подсчет средней величины (m = 3).

· Поле Выходной интервал задает адрес ячейки, с которой начнется вывод результата. Поскольку для интервала сглаживания m = 3 мы хотим получить результат в столбце E, начальной ячейкой для вывода результата будет E3.

· Флажок Вывод графика указывает на необходимость графического представления результатов сглаживания.

После заполнения окна Скользящее среднее и нажатия кнопки OK ячейки E3:E10 будут заполнены результатами расчетов среднего с интервалом сглаживания m = 3. Как мы видим на рис. 2.1, эти результаты полностью совпадают с рассчитанными с помощью функции СРЗНАЧ() в столбце С. Более того, если посмотреть содержимое ячеек E5:E10, то видно, что пакет анализа занес в эти ячейки ту же функцию СРЗНАЧ(). Первые два значения сглаженного ряда пакет анализа не рассчитывает и помещает в ячейки E3 и E4 сокращение #Н/Д (нет данных). Кроме этого, пакет анализа строит график фактических и сглаженных значений (рис. 2.3).

Рис. 2.3. График скользящего среднего, построенный пакетом анализа

Этот график не очень удачен в том смысле, что первые значения сглаженного ряда, которые не рассчитываются, показаны как нулевые значения. Поэтому удалим этот график, и в дальнейшем не будем устанавливать флажок Вывод графика в окне Скользящее среднее.

Аналогично выполним сглаживание с интервалом m = 5 и представим результаты в столбце F.

4. Использование команды Добавить линию тренда для построения графика скользящей средней. Построим график фактических значений уровня безработицы в США по данным в ячейках A3:B10 (используйте тип диаграммы Точечная, причем точки соединяются отрезками прямых).

Выполним один щелчок по диаграмме для того, чтобы перейти в режим ее редактирования. Затем подведем курсор к какой-либо точке на графике и снова щелкнем левой кнопкой мыши. Ряд данных на графике выделяется желтым цветом. Затем нужно нажать правую кнопку мыши для вызова контекстного меню (перемещать курсор мыши после выделения ряда нельзя!). В контекстном меню выберем команду Добавить линию тренда (рис. 2.4). На экране появляется окно Линия тренда (рис. 2.5).

Рис. 2.4. Контекстное меню для выделенного ряда данных

Рис. 2.5. Окно Линия тренда для метода скользящих средних

В окне Линия тренда нужно выбрать метод прогнозирования Линейная фильтрация и задать с помощью счетчика Точки интервал сглаживания (m = 3). Аналогично добавляется на график линейный фильтр для интервала m = 5. Следует отметить, что график скользящей средней называется здесь линейным фильтром по той причине, что метод скользящих средних дает лучшие результаты для рядов, имеющих в своей основе линейную тенденцию.

Итоговый вид графика, который нужно получить в этой работе, показан на рис. 2.6. Для наглядности можно изменить цвет каждого линейного фильтра. Для этого следует выделить его щелчком левой кнопки мыши, а затем вызвать контекстное меню и выбрать из него команду Формат линии тренда. Далее на вкладке Вид можно задать цвет линии.

Рис. 2.6. График фактических значений уровня безработицы
и скользящей средней для двух интервалов сглаживания

Указанные графики позволяют сделать вывод, что сглаженный ряд при большем интервале сглаживания (m = 5) находится дальше от фактических значений, чем ряд с интервалом m = 3. Это происходит в результате влияния старых данных об уровне безработицы (за 1992–
1995 гг.). Поскольку, чем больше интервал сглаживания, тем большее влияние имеют старые данные, высокий уровень безработицы в эти годы обуславливает повышение среднего значения за 1996 г. Таким образом, для данного ряда более целесообразно выбрать интервал сглаживания m = 3, при котором результирующая кривая является достаточно гладкой и, в то же время, более близкой к фактическим данным.

Задача 2. В 1997 г. наблюдались следующие значения курса немецкой марки (период наблюдений – 19 недель) (табл. 2.2).

Таблица 2.2. Значения курса немецкой марки, усл. ед.

Окончание табл. 2.2

Используя метод экспоненциального сглаживания (a = 0,2), выполните выравнивание этого ряда двумя способами:

1) самостоятельно задавая формулу сглаживания;

2) используя надстройку Пакет анализа.

Постройте график фактических и прогнозных значений курса немецкой марки. Изменяя параметр сглаживания a, наблюдайте изменение графика прогнозных значений. На основании этих наблюдений сделайте вывод о том, какая величина a является наиболее подходящей в данном случае.