Аналитический прогноз объема пассажирских перевозок Российской Федерации средствами Microsoft Excel

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

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

Встает вопрос - насколько точным может быть такое предсказание? Линейная зависимость, продолжающая имеющиеся данные, является разумным
предположением в том случае, если факторы, влияющие на изменение данных за прошедший период, остаются неизменными. Разумеется, чем дальше вы будете пытаться продолжить прямую линейного тренда, тем более вероятно, что какие-либо из имеющихся факторов, влияющих на изменение данных, в свою очередь изменятся или же возникнут новые факторы, также влияющие на изменение наблюдаемой величины. Таким образом, подобные методы годятся лишь для краткосрочных прогнозов при условии неизменности влияющих факторов. Однако давайте вернемся к Ехсеl и посмотрим, каким же образом можно осуществить подобное линейное прогнозирование.

Пусть у нас имеются данные о пассажирообороте авиапредприятий Российской Федерации за шесть последних лет (с 2006 года по 2011 год), и мы хотели бы спрогнозировать динамику роста объема перевозок на 2012 год. Допустим, что наши данные представляют собой следующую таблицу:

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

- Вз <Сtгl>+<Shift>+<↓>

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

Того же результата можно добиться и с помощью выполнения следующих действий:

- ВЗ: В9

- Правка - Заполнить - Прогрессия...

Тип - автозаполнение
- ОК

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

- Экспоненциальное приближение

- СЗ:С9

- Правка - Заполнить - Прогрессия...

- Автоматическое определение шага

- OК

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

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

Сначала построим диаграмму-график:

- Вставка - Диаграмма...

- Стандартные

Тип: ~ График –Далее
- Диапазон данных

Диапазон: ВЗ: В9 -Далее
-Заголовки

Название диаграммы: = Линейный тренд
-Легенда

- (снять флажок) Добавить легенду
- Готово

Хотя кнопка Готово уже нажата, работа над диаграммой еще не закончена. Прежде всего, отформатированный стандартным образом график не слишком нагляден - все значения попали в верхнюю часть диаграммы, а нижняя часть не используется. Это можно поправить с помощью форматирования оси значений. Щелкните по этой оси, а затем выполните следующие действия:

- Формат -Выделенная ось...
- Шкала

минимальное значение: = 50

- ОК

Теперь займемся горизонтальной осью. Выделите ее, а затем отформатируйте следующим образом:

- Формат - Выделенная ось...
- Шкала

- Число категорий между делениями: = 1

- Вид

Основные деления - пересекают ось
Промежуточные деления - наружу
Метки делений - рядом с осью

- ОК

А теперь, наконец, добавим на диаграмму линию тренда. Для этого щелкните по графику, а затем выполните следующие действия:

- Диаграмма -Добавить линию тренда...
- Тип - Линейная

- Параметры - показывать уравнение на диаграмме