Прогнозирование с помощью функций регрессии Excel

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

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

Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

Использование функции рабочего листа ТЕНДЕНЦИЯ — это самый простой способ вы­числения регрессионного анализа. Предположим, результаты ваших наблюдений внесены в ячейки А1:А10, а дни месяца расположены в ячейках В1:В10. Выделите ячейки С1:С10 и введите следующую формулу, используя формулу массива:

ТЕНДЕНЦИЯ (А1:А10;В1:В10)

Замечание

Для ввода формулы массива нажмите комбинацию клавиш <Ctrl+Shift+Enter>.

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

=ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11)

Первый аргумент — А1:А10— определяет данные наблюдений базовой линии (известные-значения-у); второй аргумент — В1:В10 — определяет временные моменты, в которые эти данные были получены (известные-значения-х). Значение 11 в ячейке В11 является новым значением- х и определяет время, которое связывается с перспективной оценкой.

Введя в ячейку В11 большее значение, вы сможете прогнозировать данные более позднего временного момента, чем непосредственно следующего за текущим. Число 24, введенное в ячейку В11, будет определять двадцать четвертый месяц, т.е. декабрь 2009 года. Выполняя вычисления с помощью функции тенденция, получаем результат 23,8, который и будет отражать прогнозируемый объем продаж в декабре 2009 года, полученный на основе фактических результатов наблюдений за период с января по октябрь 2008 года.

Кроме того, существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Например, введите числа 11-24 в ячейки В11-В24 а затем выделите ячейки С11:С24 и введите с помощью формулы массива следующее: {=ТЕНДЕНЦИЯ(А1:А10;В1:В10;В11:В24)}.

Рис. 3 Вид базовой линии и прогнозных значений при использовании функции ТЕНДЕНЦИЯ

Excel вернет в ячейки С11:С24 прогноз на временные моменты с 11 по 24. Данный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А1:А10 и временными моментами базовой линии с 1 по 10, указанными в ячейках В1:В10.