Порядок выполнения работы. 1. Ввод исходных данных задачи.Введем в ячейки A3:A21 номера недель, а в ячейки B3:B21 – соответствующие значения курса немецкой марки (рис

1. Ввод исходных данных задачи. Введем в ячейки A3:A21 номера недель, а в ячейки B3:B21 – соответствующие значения курса немецкой марки (рис. 2.7).

Рис. 2.7. Расчеты методом экспоненциального сглаживания

2. Расчет методом экспоненциального сглаживания по формуле. В ячейку С2 введем значение заданного параметра сглаживания a, равное 0,2. На эту ячейку мы будем делать ссылки в формулах, затем будем изменять ее, исследуя влияние параметра сглаживания.

В ячейку С3 введем следующую формулу: =B3 (первый элемент сглаженного ряда принимается равным фактическому значению показателя). В ячейку C4 введем формулу расчета экспоненциального среднего, аналогичную формуле (2.2): =$C$2*B4+(1–$C$2)*C3.

Адрес ячейки C2 является абсолютным для того, чтобы эту формулу можно было потом правильно скопировать. Ссылка на ячейку B4 – это ссылка на соответствующее фактическое значение уровня ряда, а ссылка на ячейку C3 – на предыдущее экспоненциальное среднее.

Скопируем эту формулу вниз по столбцу С, используя автозаполнение. Результат расчетов см. на рис. 2.7.

3. Расчет экспоненциальной средней с помощью Пакета анализа. Вызовем надстройку Пакет анализа командой Сервис / Анализ данных … и выберем инструмент анализа Экспоненциальное сглаживание. Открывается окно Экспоненциальное сглаживание, которое нужно заполнить, как показано на рис. 2.8.

Рис. 2.8. Окно Экспоненциальное сглаживание

Поля в этом окне Экспоненциальное сглаживание имеют следующее значение:

· поле Входной интервал задает ряд фактических значений исследуемого показателя (B3:B21);

· поле Фактор затухания определяется параметром сглаживания и равно (1 – a);

· поле Выходной интервал содержит адрес ячейки, с которой начинается вывод результата (D3);

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

Результаты расчетов, которые дает Пакет анализа, представлены на рис. 2.7. в столбце D. Пакет анализа дает сглаженное значение со сдвигом на одну ячейку. Если изучить формулы, которые записывает Пакет анализа в ячейки столбца D, то становится очевидным, что расчет выполняется по несколько другой формуле, чем (2.2):

Q_t = a × y_{t –1} + (1 – a) × Q_{t –1}.

4. Анализ влияния параметра сглаживания a. Построим графики фактического и сглаженного ряда по данным в столбцах B и C, как показано на рис. 2.9 (используйте тип диаграммы точечная, на которой значения соединены отрезками).

Рис. 2.9. Графики фактического и сглаженного ряда для a = 0,2

Затем будем изменять значение параметра a в ячейке C2 и наблюдать изменение графика сглаженного ряда (задавайте a = 0,3; a = 0,4 и т. д.). Исходя из этих наблюдений, можно сделать вывод, что с увеличением параметра a сглаженный ряд приближается к фактическим данным, но его график при этом становится менее гладким. Это объясняется тем, что a – это вес последнего (самого нового) наблюдения в формуле сглаживания.