Анализ рядов динамики в электронных таблицах | страница 23

и выбираем 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.

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

Рис. 9.8. Уравнение регрессии

Задание. Найдите значения коэффициентов уравнения в таблице результатов анализа и составьте уравнение тренда. Сравните с предыдущими результатами.

Следующий способ построить уравнение тренда — вызвать готовую функцию оценки линейной модели:

LINEST (Y, X)

ЛИНЕЙН (Y, X)

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

Обратим внимание, что здесь тоже вначале указывают «игреки», а затем «иксы» (в нашем случае столбец моментов времени t) — см. рис. 9.9.