Валентин Арьков - Анализ рядов динамики в электронных таблицах

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

Задание. Проведите экспоненциальное сглаживание и нанесите EMA на график T+S+E. Подберите такой период сглаживания, чтобы сглаженная линия стала линией поддержки для графика.

Мы рассмотрели основные методы сглаживания на примере аддитивной модели. Теперь пришло время для мультипликативной модели.

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

Задание. Скопируйте данные мультипликативной модели на новый лист. Проведите сглаживание этого ряда динамики всеми методами, описанными выше в разделе 8.

Мы рассмотрели в разделе 8 различные методы МЕХАНИЧЕСКОГО ВЫРАВНИВАНИЯ рядов динамики, или механического сглаживания. Это различные вариации на тему скользящей средней. Слово «механическое» намекает, что с нашими данными производят механические действия, похожие на механическое движение из курса физики.

Задание. Дайте определение выражению МЕХАНИЧЕСКОЕ ВЫРАВНИВАНИЕ.

В данном разделе мы переходим к так называемому АНАЛИТИЧЕСКОМУ ВЫРАВНИВАНИЮ. Это получение уравнения тренда и соответствующей линии тренда на графике. Слово «аналитическое» намекает на методы математического анализа функций. Студенты для краткости называют этот предмет «матан». Когда мы используем уравнения, обычно говорят про аналитические методы. Так что здесь должно появиться уравнение. Уравнение тренда.

Задание. Дайте определение выражению АНАЛИТИЧЕСКОЕ ВЫРАВНИВАНИЕ.

На самом деле, мы уже рассмотрели все возможные способы построения уравнения тренда. Это произошло в предыдущей работе, где мы изучали взаимосвязь явлений [2]. И называлось это РЕГРЕССИОННЫЙ АНАЛИЗ. Напомним, что это такое. Регрессия (в статистике) — это построение уравнения связи В СРЕДНЕМ. Исходные данные — это точки, разбросанные вокруг линии (или многомерной поверхности). Линия может быть прямой или кривой. Коэффициенты уравнения подбирают так, чтобы линия проходила ближе к исходным точкам. То есть чтобы разброс точек вокруг этой линии был меньше, чем для любой другой линии.

Задание. Дайте определение выражению РЕГРЕССИОННЫЙ АНАЛИЗ.

В качестве инструмента для регрессионного анализа часто используют МЕТОД НАИМЕНЬШИХ КВАДРАТОВ (рис. 9.1). В этом случае находят такие значения коэффициентов в уравнении связи, чтобы получить наименьшую сумму квадратов расстояний между будущей линией и исходными данными (точками). Причём расстояние измеряется по вертикали. То есть берётся разность соответствующих «игреков». И никаких перпендикуляров — это слишком сложно для вычислений.

Рис. 9.1. Регрессионный анализ

Задание. Объясните, о каких КВАДРАТАХ идёт речь в методе наименьших квадратов?

При построении уравнения тренда тоже часто используется метод наименьших квадратов — сокращённо МНК. Единственное отличие: вместо «иксов» здесь участвуют моменты времени, причём расположенные по возрастанию (рис. 9.2). В остальном всё то же самое. Подойдут любые средства регрессионного анализа Excel:

— встроенные инструменты анализа графика

— надстройка «Анализ данных»

— функции

— формулы.

Мы рассмотрим их все, но уже не так подробно, как предыдущей работе. Если будет трудно, вернитесь к пособию [2] и перечитайте его.

Вначале мы пройдёмся по аддитивной модели. Потом повторим всю работу для мультипликативной модели.

Рис. 9.2. Уравнение тренда

Задание. Объясните, как применяется МНК при построении уравнения тренда.

Первый и самый простой инструмент построения тренда встроен в графики.

Строим график исходного ряда динамики (аддитивная модель):

Y = T + S + E

Нажимаем кнопку [+] Chart Elementsи выбираем Trendline(рис. 9.3). Это означает «линия тренда». Настраиваем цвет и тип линий. Включаем легенду под графиком: Legend — Bottom.

Рис. 9.3. «Встроенный» тренд

Задание. На новом листе постройте график исходного ряда динамики и включите линию тренда. Настройте оформление графика.

Мы выбираем масштаб по осям так, чтобы всё поле графика было занято нашими данными и линиями. Поэтому не обязательно всё начинается от нуля. Мы настроили масштаб, но теперь на оси «игреков» появились какие-то «некрасивые» числа.

Дважды щёлкнем по оси «игрек». В боковом меню FormatAxis выбираем

Axis Options — Text Options — Units — Major — 5.0

Теперь наши метки идут с шагом 5 единиц. «Красивые» круглые числа (рис. 9.4).

Рис. 9.4. Настройка масштабных меток

Задание. Настройте шаг для масштабных меток — при необходимости.

У нас есть линия тренда. Теперь получим уравнение тренда. Щёлкаем по кнопке [+] Chart Elementsи выбираем Trendline — More Options. Ставим галочку в пункте Display Equation on chart. На графике появилось уравнение (рис. 9.5).

Рис. 9.5. Включение уравнения тренда

Перетаскиваем уравнение мышкой на свободное место, и всё готово (рис. 9.6). Сравниваем с уравнением тренда по заданию:

T = 7 +0,3 t

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

Рис. 9.6. Уравнение тренда

Задание. Включите отображение уравнения тренда на графике. Расположите уравнение на свободном месте. Сравните полученное уравнение с параметрами задания.

Следующий способ получить уравнение тренда — это надстройка. Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Regression

Настраиваем параметры регрессионного анализа (рис. 9.7):

Исходные данные (вначале указываем «игреки»):

Input — Input Y Range

Input — Input X Range

Адрес для вывода результатов анализа — первая ячейка диапазона

Output options — Output Range

Это адрес первой ячейки в левом верхнем углу будущей таблички. Табличка будет довольно большой, так что лучше расположить её на свободном месте.

Рис. 9.7. Параметры регрессионного анализа

Задание. Вызовите надстройку и получите таблицу с результатами регрессионного анализа.

В полученной таблице нас будут интересовать только значения коэффициентов (рис. 9.8). По ним мы запишем уравнение тренда — средствами Excel.

Свободный член уравнения назван Intercept. То есть пересечение с осью «игреков».

Коэффициент регрессии (коэффициент при переменной t) — X Variable.

Цифры получились похожи на предыдущие. При желании можно рассмотреть гораздо больше разрядов в каждой ячейке — в отличие от графика, где коэффициенты уравнения нельзя уточнить.