Читать «Анализ рядов динамики в электронных таблицах» онлайн - страница 19

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

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

— функции

— формулы.

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

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

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

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

9.1. Встроенный тренд

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

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

Y = T + S + E

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

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

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

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

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

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. Уравнение тренда

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

9.2. Надстройка

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

Data — Analysis — Data Analysis — Regression

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

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

Input — Input Y Range

Input — Input X Range

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

Output options — Output Range

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