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
Это адрес первой ячейки в левом верхнем углу будущей таблички. Табличка будет довольно большой, так что лучше расположить её на свободном месте.
Рис. 9.7. Параметры регрессионного анализа
Задание. Вызовите надстройку и получите таблицу с результатами регрессионного анализа.
В полученной таблице нас будут интересовать только значения коэффициентов (рис. 9.8). По ним мы запишем уравнение тренда средствами Excel.
Свободный член уравнения назван Intercept. То есть пересечение с осью «игреков».
Коэффициент регрессии (коэффициент при переменной t) X Variable.
Цифры получились похожи на предыдущие. При желании можно рассмотреть гораздо больше разрядов в каждой ячейке в отличие от графика, где коэффициенты уравнения нельзя уточнить.
Рис. 9.8. Уравнение регрессии
Рис. 9.8. Уравнение регрессии
Задание. Найдите значения коэффициентов уравнения в таблице результатов анализа и составьте уравнение тренда. Сравните с предыдущими результатами.
9.3. Функция LINEST
Следующий способ построить уравнение тренда вызвать готовую функцию оценки линейной модели:
LINEST (Y, X)
ЛИНЕЙН (Y, X)
На самом деле у этой функции больше входных параметров, но нам для наших целей будет достаточно этого упрощённого формата вызова.
Обратим внимание, что здесь тоже вначале указывают «игреки», а затем «иксы» (в нашем случае столбец моментов времени t) см. рис. 9.9.
Функция LINEST выдаёт результаты в виде массива ячеек. Так что вызов функции проводится в несколько этапов:
1) вводим вызов функции в одну ячейку таблицы, например
=LINEST (E4:E54,A4:A54)
2) выделяем диапазон, включающий две ячейки начиная с той ячейки, куда мы уже ввели формулу, например I3:J3
3) нажимаем функциональную клавишу F2
4) нажимаем комбинацию клавиш Ctrl + Shift + Enter
Рис. 9.9. Параметры функции LINEST
На рис. 9.10 схематично показаны четыре шага по вызову функции массива. Если все шаги выполнены правильно, в результате мы получим два числа. Это оценки коэффициентов уравнения тренда. Формулы в обеих ячейках будут выглядеть одинаково. Вокруг формул появятся фигурные скобки. Это указывает, что здесь появилась формула массива.
Оценки коэффициентов уравнения выводятся в следующем порядке: вначале коэффициент регрессии, затем свободный член уравнения.
Сравниваем полученные коэффициенты. Значения совпадают.
Рис. 9.10. Функция массива
Задание. Вызовите LINEST как функцию массива.
9.4. Линия тренда
Мы получили уравнение тренда несколькими способами.
Следующий шаг построить график.
В нашем примере мы построили линейное уравнение. Это линейный тренд. Прямая линия.
Чтобы провести прямую линию, достаточно взять всего две точки. Мы выберем два крайних значения времени и найдём соответствующие значения уровней ряда по нашему уравнению (рис. 9.11). Запишем уравнение тренда, в котором округлим коэффициенты до трёх значащих разрядов. Оценим уровни ряда.
Рис. 9.11. Точки для линии тренда
Задание. Оцените «вручную» две точки для построения линии тренда.
Теперь найдём «точные» значения. Для этого проведём расчёты в Excel и используем ссылки на найденные значения коэффициентов (рис. 9.12). Сравниваем полученные значения с результатами ручных расчётов. Числа очень похожи.
Мы взяли слово «точные» в кавычки. На самом деле, достаточно смоделировать другой набор исходных данных, и получится другое уравнение тренда. Немного другой. В регрессионных оценках всегда скрыта случайная ошибка. Поэтому высокая точность расчётов (например, 20 знаков после запятой) не даёт очень точных результатов. Вот такой парадокс.
Рис. 9.12. Точки для линии тренда
Задание. Найдите «точные» значения для построения линии тренда. Сравните с предыдущими оценками.