Средства MS Excel для построения трендовой модели

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

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

В качестве примера используем данные об авиаперевозках в США с 1949 по 1960 годы. Пусть требуется предсказать объем авиаперевозок на 1961 год. Знание этого объема позволяет планировать развитие авиационной промышленности и инфраструктуры, связанной с авиаперевозками. Исходные данные приведены в таблице.

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

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

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

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

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

5. Вычислить по формуле y = 383,09x + 873,52. Следует учесть, что аргументом трендовой модели является порядковый номер, т.е. в нашем примере x=13. В результате получим прогноз на 1961 год: 5853,69 тысяч пассажиров.

Следует заметить, что мы, скорее всего, получили заниженный прогноз. Это видно из диаграммы и обусловлено выбором линейной модели прогноза. Возможно, что более точный прогноз был бы получен с помощью степенной или экспоненциальной линий тренда. Оценить качество прогноза можно только в конце 1961 года. В целом прогноз следует делать весьма осторожно – возможны большие ошибки. Именно поэтому чаще всего используются краткосрочные и среднесрочные прогнозы.

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