Данеев Деменченок

Технология интервального прогнозирования

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

При анализе экспериментальных и статистических данных часто используются трендовые модели (тренды).

Трендовая модель (тренд) – математическая функция вида y = f(x), график которой отражает общую тенденцию ряда данных.

Математическая модель – приближенное описание какого-либо класса явлений внешнего мира, выраженное с помощью математической символики; система математических уравнений, описывающих наиболее важные свойства объекта исследования.

В Excel тренд может быть создан на рабочем листе (используется средство Автозаполнение или одна из статистический функций, например РОСТ или ТЕНДЕНЦИЯ), а также на диаграмме. Создание тренда на диаграмме яснее демонстрирует модель и прослеживает тенденцию изменения данных. Excel способен автоматизировать создание тренда и построение его графика (линии тренда) в гистограммах, линейчатых диаграммах, диаграммах с областями, графиках и точечных диаграммах.

Рассмотрим последовательность создания тренда по данным таблицы, представленной на рис. 41.

Предположим, требуется дать прогноз на конец года. Предварительно Вам необходимо построить график по данным А2:J2 (рекомендуется тип точечная диаграмма, вид со сглаживающими линиями без маркеров).

Рис. 41. Интервальное прогнозирование

Порядок добавления линии тренда к рядам данных:

выделить ряд данных, к которому нужно добавить линию (щелкнуть правой кнопкой мыши на любой точке графика);
выбрать команду контекстного меню Добавить линию тренда;
в появившемся окне выбрать нужный тип регрессионной линии тренда (желательно использовать монотонные функции, в данном случае – тип Линейная). Также можно указать название тренда, задать при необходимости интервал прогнозирования (продолжить линию тренда вперед или назад за пределы, в которых данные известны), задать точку пересечения тренда с осью Y, а также поместить на диаграмму уравнение тренда и величину достоверности аппроксимации. В нашем случае зададим прогноз вперед на 3 периода (интервал прогнозирования не должен превышать 1/3 интервала наблюдения) и отметим пункты Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации.

В результате на диаграмме появится линия тренда, его уравнение y = 13,867х + 287,78 и квадрат коэффициента корреляции R²= 0,881.

Квадрат коэффициента корреляции оценивает степень близости исходных данных и соответствующих точек тренда, его максимальное значение равно 1 (полное совпадение графика и линии тренда). Достоверность аппроксимации признается хорошей при величине R² 0,81. В рассматриваемом примере достигнуто хорошее качество трендовой модели.

Для расчета числовых данных в ячейку В3 нужно записать уравнение тренда =13,867*B1 + 287,78 (т. к. х – номер месяца) и затем скопировать эту формулу в ячейки С3…М3. Получим прогнозные значения: на октябрь – 426, ноябрь – 440, на декабрь – 454.

Рис. 42. Параметры тренда

Насколько обоснованны эти цифры?

Прогнозирование на основе тренда (как и любой другой статистический метод прогнозирования) применимо лишь при выполнении условий:

• сохранения законов, лежащих в основе развития прогнозируемых процессов (любые социальные процессы обладают некоторой инерционностью, поэтому можно полагать, что на небольшом интервале времени общая тенденция развития процесса существенно не изменится);

• данные о прогнозируемом объекте представительны и достоверны.

В противном случае приемлемая точность прогнозов не может быть обеспечена никакими статистическими методами, сколь совершенными они бы ни были.

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

где  – среднее квадратическое отклонение ошибок аппроксимации:

n – количество интервалов наблюдения (9 месяцев);

р – число параметров (коэффициентов) тренда (для линейного р = 2);

y_i – данные ряда (количество преступлений за i-й месяц);

y_Pi – расчетные данные;

t_i – номер интервала, считая от середины интервала наблюдения (середина интервала наблюдения соответствует 5-му месяцу, т.е. t₁ = –4, t₂ = –3 … t₅ = 0, t₆ = 1 … t₉ = 4);

t_k – номер интервала прогноза, считая от середины интервала наблюдения (т. е. t₁₀ = 5, t₁₁ = 6, t₁₂ = 7).

Для расчета среднего квадратического отклонения ошибок аппроксимации  в ячейку В16 запишем формулу

=КОРЕНЬ(СУММКВРАЗН(B2:J2;B3:J3)/7).

Средняя ошибка прогноза линейного тренда зависит от номера интервала прогнозирования, поэтому для 10-го месяца в ячейку В17 запишем формулу =$B$16*КОРЕНЬ(1/9+12*5^2/(9^3-9)), затем скопируем ее в С17:D17 и заменим в полученных формулах номер интервала прогнозирования 5 соответственно на 6 и 7.

Для получения достаточно надежных границ прогноза, скажем, с вероятностью 0,9 (или 90 %) того, что ошибка не будет больше указанной, следует среднюю ошибку умножить на величину t-критерия Стьюдента при указанной вероятности (1–0,9 = 0,1) и при числе степеней свободы, равном

n – p= 9 – 2 = 7.

Кроме того, ошибка прогноза  y должна учитывать и ошибку аппроксимации:

Чтобы определить ошибку прогноза для 10-го месяца, в ячейку В18 запишем формулу =КОРЕНЬ($B$16^2+(СТЬЮДРАСПОБР(0,1;7)*B17)^2), затем скопируем ее в С18:D18.

Получим значения ошибки прогноза  y для октября – 25,3; ноября – 28 и декабря – 31.

Интервал прогноза определяется по формуле y_pi   y (расчетное значение  ошибка прогноза). Для определения интервала прогноза на октябрь в ячейку I16 запишем формулу =К3-В18, в ячейку К16 запишем сумму расчетного значения и ошибки прогноза =К3+В18. Аналогичные формулы для ноября и декабря можно записать в ячейки I17:К18:

ноябрь =L3-C18 =L3+C18

декабрь =M3-D18 =M3+D18. Окончательный вид таблицы представлен на рис. 41.

Получается, что при сохранении существующей тенденции с вероятностью 90 % число наблюдаемых в декабре явлений окажется в пределах 454  31 или в интервале от 423 до 485.

Таким образом, можно считать Microsoft Excel (и другие табличные процессоры) мощным средством автоматизации расчетов различной степени сложности, эффективным инструментом анализа данных и математического моделирования; программным средством, которое удачно сочетает широкие функциональные возможности с простотой освоения пользователем работы с программой.

Содержание