Задание 3. Сглаживание обрабатываемых данных

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

Скользящее среднее - это вычисление для каждого интервала среднего значения величин из нескольких предыдущих интервалов.

Экспоненциальное сглаживание - это вычисление для каждой итерации среднего от значения точки данных на текущем интервале и экспоненциального сглаженного, полученное на предыдущей итерации.

Все предшествующие интервалы автоматически включаются в вычисление на каждой итерации. Можно задать весовой коэффициент для текущего интервала (фактор затухания).

Имеются следующие данные об объеме реализации за месяц. (табл. 1.1)

Таблица 1. 1

Исходные данные для задания 1.3

Для того, чтобы построить скользящее среднее для этих данных:

· Создайте новый лист и назовите его Сглаживание.

· Выделите команду Сервис / Анализ данных / затем выберите Скользящее среднее и нажмите кнопку ОК.

· В появившемся диалоговом окне Скользящее среднее (рис. 2.28) установите необходимые флажки:

Рис. 2. 31. Установка параметров скользящего среднего

· Входной интервал - содержит сглаживаемые данные (В2:В31).

· Выходной интервал - верхняя ячейка диапазона результатов (С2). Он будет иметь ширину в один столбец, и длину, равную исходному диапазону.

· Интервал - число используемых предшествующих периодов. Чем больше это число, тем больше сглаживаемость, но пропадает информативность.

· Стандартные погрешности - добавляется столбец, содержащий статистическую ошибку (не устанавливайте)

· Вывод графика - создает диаграмму.

· Нажмите кнопку ОК.

· Измените размер диаграммы, для большей наглядности. В ячейку С2 введите текст "Скользящее среднее".

Рис. 2. 32. Результаты определения скользящего среднего

Сглаженные данные заполняют диапазон результатов. Две первые ячейки будут содержать значения ошибки #Д/Н, поскольку для них не существует трех предшествующих интервалов.

Чтобы построить экспоненциальное среднее для данных табл. 1.1:

· В ячейку D 1 листа Сглаживание введите текст " Экспоненциальное сглаживание ".

· Выполните команду Сервис / Анализ данных / затем выберите Экспоненциальное сглаживание и нажмите кнопку ОК.

· В поле Входной интервал введите ссылку на диапазон с данными.

· В поле Фактор затухания введите 0,3 - весовой коэффициент для предыдущего интервала.

· В поле Выходной интервал введите адрес ячейки D2.

· Нажмите кнопку ОК.

Результат выполнения экспоненциального сглаживания приведен на рис. 2.30.

Рис. 2. 33. График выполнения экспоненциального сглаживания

· Измените размер диаграммы, для большей наглядности.

· Данные заполняют диапазон результатов. Две первые ячейки будут содержать значения ошибки #Д/Н, поскольку для них не существует трех предшествующих интервалов.

· Постройте третью диаграмму, в которую включите данные трех столбцов Реализация, Скользящее среднее, Экспоненциальное сглаживание.

· На сглаженных данных будут лучше видны тенденции изменений числа заказов.

Для этого:

· Выделите область ячеек B1:D31.

· Нажмите на кнопку Вставить диаграмму панели инструментов Стандартная.

· В первом диалоговом окне Мастера диаграмм выберите Тип диаграммы - График, Вид - левый в первой строке. Нажмите кнопку Далее.

· Во втором окне нажмите кнопку Далее.

· В третьем диалоговом окне Мастера диаграмм в поле Название диаграммы введите текст: «Диаграмма со сглаженными и усредненными данными». В поле Оси Х (категорий) - текст: "Дни", а в поле Оси У – "Реализация.

· Нажмите кнопку Далее.

· Нажмите кнопку ГОТОВО в четвертом диалогом окне.

· Переместите диаграмму и измените ее размеры.

· Щелкните правой кнопкой мыши в Области диаграммы и выберите команду Формат области построения диаграммы.

· Выберите заливку белого цвета. Нажмите кнопку ОК.

Результат приведен на рис. 2.31.

Рис. 2. 34. Итоговая диаграмма по заданию.3

G Внимание. Скользящее среднее и Экспоненциальное сглаживание - это единственные средства в Пакете анализа автоматически пересчитывающиеся при изменении исходных данных.

2.4. ИСПОЛЬЗОВАНИЕ ПРОЦЕДУРЫ «ПОИСК РЕШЕНИЯ» ДЛЯ ПОДБОРА КОЭФФИЦИЕНТОВ ФУНКЦИИ АППРОКСИМАЦИИ ДАННЫХ НАБЛЮДЕНИЙ

Для преодоления вышеописанных сложностей может быть использована процедура Поиск решения.

Рассмотрим возможность ее использования для аппроксимации ряда наблюдений, содержащей периодические (сезонные) колебания.