Построение трендовых моделей при помощи диаграмм

Многие экспериментальные данные можно интерпретировать как временные ряды - последовательность измерений, полученных в определенные моменты времени t_i, где i - порядковый номер измерения на оси времени. Такие ряды характеризуются некоторой тенденцией развития процесса во времени и называются трендовыми. Используя трендовые модели, можно выдавать прогнозы на краткосрочный и среднесрочный периоды. Excel имеет средства для создания трендовых моделей встроенные в построитель диаграмм.

Одной из форм трендовых моделей при постоянном шаге по времени является линейная модель:

(67)

В качестве примера используем данные о динамике изменения числа автомобилей в АТП с 2000 по 2010 гг. Знание этой динамики позволяет планировать развитие АТП и его инфраструктуры на перспективу. Исходные данные приведены в таблице 3. Таблица 3.

Порядок расчетов следующий.

1. Выделить диапазон B2:B12 и построить по этим данным диаграмму типа "График", щелкнув по значку "Мастер диаграмм" на панели инструментов.

2. Выделить диаграмму и выполнить Диаграмма/Добавить линию тренда.

3. В окне "Линия тренда" открыть вкладку "Параметры" и установить флажки "Показывать уравнение на диаграмме" и "Поместить на диаграмму величину достоверности аппроксимации".

1. На вкладке "Тип" выбрать тип диаграммы – линейная и нажать Ok. Результаты показаны на рисунке.

1. Вычислить по формуле y = 23,818x + 105,82. Следует учесть, что аргументом трендовой модели является порядковый номер, т.е. в нашем примере x=12. В результате получим прогноз на 2011 год: 391 автомобиль.

Коэффициент достоверности аппроксимации R² показывает степень соответствия трендовой модели исходным данным. Его значение может лежать в диапазоне от 0 до 1. Чем ближе R² к 1, тем точнее модель описывает имеющиеся данные.

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

В процессе изменения угла опережения зажигания у двигателя автомобиля ГАЗ-3307 контролировали часовой расход топлива. Регулировку выполняли на стенде тяговых качеств. Значения угла опережения зажигания Х и значения часового расхода топлива записывали в таблицу 4.

Таблица 4.

После чего результаты измерений были занесены в таблицу 5 среды Microsoft Excel:

Таблица5.

Рис. 33. Внешний вид таблицы 1 в среде Microsoft Excel

Выделим диапазон А2:B8 и построим по этим данным диаграмму типа "Точечная", щелкнув по значку "Мастер диаграмм" на панели инструментов.

Рис. 34 Внешний вид графика функции в средеMicrosoft Excel

Выделим диаграмму и выполним Диаграмма/Добавить линию тренда. (см. рис. 35).

Рис. 35. Опция добавления линии тренда на график функции

В окне "Линия тренда" открыть вкладку "Параметры" и установить флажки "Показывать уравнение на диаграмме" и "Поместить на диаграмму величину достоверности аппроксимации" (см. рис. 34 и 35).

Рис. 36. Опция добавления линии тренда на график функции

Рис. 37 Опция добавления уравнения графика функции

На вкладке "Тип" выбрать тип диаграммы – «Полиномиальная» и нажать Ok. Результаты показаны на рисунке 36.

Рис. 38 График и уравнение функции

Полученное уравнение y = 3,9048x² – 26,952x + 64,143 зависимости часового расхода топлива от величины угла опережения зажигания с достоверностью аппроксимации R² = 0,9952 позволяет определить оптимальный для данного автомобиля угол опережения зажигания (на уровне 3,5^о), обеспечивающий минимум часового расхода топлива.