Скользящее среднее (линейная фильтрация)

Использование в качестве приближения скользящего среднего позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (оно задается параметром Шаг). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если Шаг равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка — как среднее следующих двух элементов и так далее. Для скользящего среднего значение R-квадрат не может быть отображено.

Формула:

Примечание. Число точек, образующих линию скользящего среднего, равно числу точек в исходном ряде минус значение периода.

В следующем примере показана зависимость числа продаж на протяжении 26 недель, полученная путем расчета скользящего среднего.

Добавление и редактирование линии тренда

1. Создайте таблицу. Для этой таблицы постройте график. Дважды щелкните по графику для перехода в режим редактирования.

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

3. На вкладке Тип выберите нужный тип регрессионной линии тренда. или линии скользящего среднего (линейная фильтрация).

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

При выборе типа Скользящее среднее (линейная фильтрация) введите в поле Период число периодов (точек), используемых для расчета скользящего среднего.

Примечания

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

· Если вариант «Скользящее среднее (линейная фильтрация)» выбран для точечной диаграммы, результат будет зависеть от порядка расположения значений X во входном диапазоне. Чтобы получить правильный результат, необходимо отсортировать значения X перед построением линии скользящего среднего.

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

4. Для осуществления прогноза выделите линию тренда, щелкнув но ней мышью, затем щелкните правой кнопкой мыши и выберите пункт Форматирование линии тренда, откройте вкладку Параметры и установите необходимые параметры: Прогноз вперед на один или несколько периодов. На диаграмме будет показана линия тренда и прогноз на один или несколько периодов.

5. Для вывода формулы и значения R-квадрат откройте вкладку Параметры и установите необходимые параметры: Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R^2).

6. Линию тренда можно форматировать. Выделите линию тренда, щелкнув но ней мышью, затем щелкните правой кнопкой мыши и выберите пункт Форматирование линии тренда, установите желаемый Вид тренда, например, выберите красный пунктирный вид линии.

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

Практическое задание.

1. Создать свою папку в Моих документах

2. Создать или выбрать Лист 1.

3. Набрать заголовки согласно Рис. примера. Выбрать самостоятельно фирму, товар и т.п.

4. Создать таблицу продаж, спроса и т.п. по месяцам, годам, кварталам и т.п.

5. Ввести данные. Допускается использовать данные из примера, выбирать самостоятельно или использовать данные, предоставленные преподавателем.

6. Создать не менее трёх линий тренда различного типа с прогнозами.

7. Отформатировать диаграмму и линии тренда согласно примера.

Требования к отчёту

Отчёт - отдельный файл формата электронных таблиц с именем Фамилия.xls, содержащий на листе 1 заголовки, график с тремя линиями тренда, формулами и критерием R-квадрат для них. Пример результата:

Ещё один пример данных и построения простой линии тренда (линейная апроксимация):

Затраты на рекламу	Продано продукции