1. АНАЛИЗ И ПРОГНОЗ РЯДОВ НАБЛЮДЕНИЙ
Трудно найти область знаний или хозяйственной деятельности, г де не приходилось бы принимать решения, основанные на знании поведения объекта в пространстве признаков или времени. В большинстве случаев это решение принимается на основании модели [1], базирующейся на знании предыдущих состояний объекта, на умении прогнозировать его поведение в будущем (или прошлом).
Excel предоставляет пользователю широкие возможности построения таких моделей и прогнозирования поведения объекта. Это прежде всего методы построения линий трендов для известных значений временных рядов [2], методы статистического анализа данных наблюдений, методы линейного и динамического программирования («Поиск решения») и др.
Рассмотрим применение этих методов на нескольких примерах, имеющих отношение к деятельности торговых предприятий.
остроение линии тренда расходов на продукты питания в зависимости от уровня дохода семьи
В таблице приведены статистические данные опроса (январь 2001 года) населения по их затратам на приобретение продуктов питания,
Для анализа приведенных данных построим диаграмму рассеяния в корреляционном поле - Затраты семьи на приобретение продуктов - доходы семьи.
· Выделим таблицу данных;
|
· выполним команду ВСТАВКАЮДИАГРАММА
· 
в открывшемся диалоговом окне выберем пиктограмму «точечная».
· Выполним указания «мастера диаграмм» и построим диаграмму, иллюстрирующую затраты семьи на продукты питания в зависимости от дохода.
· Для построения линии тренда:
выделим кривую, щелкнув правой кнопкой мыши по любой из точек данных на диаграмме;
в появившемся меню щелкнем на строке ДОБАВИТЬ ЛИНИЮ ТРЕНДА
·
При построении линии тренда на вкладке «линия тренда» выполним следующие действия:
Рис. 1 Диалоговое окно «Линия тренда
· выберем наиболее подходящий тип кривой аппроксимации (в нашем примере -это аппроксимация –«логарифмическая»)
· «Установим флажки «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации R2»
· При необходимости в группе списков «Прогноз» установим необходимую величину шага прогноза (необходимая величина шага прогноза вводится либо с клавиатуры, либо использованием стрелок).
Рис.2 Диаграмма расходов семьи на продукты питания со вставленными линиями и уравнениями тренда (логарифмической и линейной)
После того как построены линии тренда и соответствующие им уравнения, не составляет труда вычислить новое значение Y (зависимой переменной) для нового значения Х (независимой переменной)
Примечание: Следует очень внимательно относиться к выбору типа линии тренда для аппроксимации данных наблюдений и учитывать то, что прогноз делается в предположении, что в дальнейшем события будут развиваться также как и в период наблюдения.
Показателем точности аппроксимации данных наблюдений является величина R2 ( чем ближе эта величина к единице, тем точнее выбранная линия тренда приближена к данным наблюдений – выше точность аппроксимации).
Ø Однако, прежде всего Вы должны понимать (и уметь объяснить) смысл переменных входящих в уравнение аппроксимации (линии тренда) и характер исследуемого вами процесса.
Так, например, при аппроксимации ряда наблюдений за динамикой продаж уравнением типа Y= a+b*X и прогнозируя их развитие на достаточно продолжительный период, трудно ожидать, что за это время рынок не окажется насыщенным Вашими товарами или упадет покупательная способность населения.
Ø Наиболее часто ошибки прогноза возникают при использовании в качестве уравнения аппроксимации степных полиномов типа Y= a+ b*X + c*X2+ d*X3 +…
несмотря на то, что величина R2 может иметь достаточно высокое значение.
Ø Использование в качестве линии тренда линейной фильтрации (скользящей средней) позволяет получить достаточно хорошее приближение линии тренда к данным наблюдений, но не позволяет прогнозировать значения ряда и, соответственно, не дает уравнение прогноза.
1.2. Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.
Для преодоления вышеописанных «сложностей» может быть использована процедура «Поиск решения»
Рассмотрим возможность ее использования для аппроксимации ряда наблюдений, содержащей периодические (сезонные) колебания.
Предсдставьте себе, что мы имеем многолетний ряд наблюдений (рис.3), характеризующих товарооборот некоторого торгового предприятия в течении нескольких лет[3].
|
| Рис. 3. Фрагмент рабочего листа Excel с таблицей и графиком товарооборота предприятия «Альфа» в 2001, 2002 и 2003г.г. (в сопоставимых ценах) |
Первое, что приходит в голову при анализе приведенных на рисунке таблицы и графика, это то, что в течение каждого года наблюдался рост товарооборота. При этом, наблюдалось и увеличение объемов от года к году. Можно для каждой кривой графика построить линию тренда (команда ВСТАВКА Ю ЛИНИЯ ТРЕНДА для выделенной кривой) и попытаться дать оценку скорости прироста товарооборота в течение года, а из сравнения их сделать вывод о динамике товарооборота в течении всего периода наблюдений (2001-2003гг.). Однако, и в этом случае мы мало что сможем сказать о наблюдаемых отклонениях точек наблюдений от линии тренда и тем более об их закономерностях в течение всего периода. Мы абсолютно ничего не можем сказать о периодических составляющих и их влиянии на величину товарооборота.
Предлагаемый Excel набор функций аппроксимации (линий тренда) рядов наблюдений весьма ограничен и позволяет проследить (оценить) только генеральную тенденцию их поведения.
1. Прежде всего представим имеющиеся данные в виде непрерывного ряда наблюдений и по этим данным построим график и линию тренда изменения товарооборота за период 2001-2003гг.
Примечание: При преобразовании исходных данных в непрерывный ряд наблюдений удобно воспользоваться операцией транспонирования матрицы наблюдений
Для транспонирования матрицы:
· Выделите исходный ряд наблюдений (ячейки B3:M6).
· Выполните команду КОПИРОВАТЬ.
· Откройте новый лист Excel.
· Установите курсор мыши в любую ячейку, например, В3.
· Выполните команду ПРАВКА Ю СПЕЦИАЛЬНАЯ ВСТАВКА.
· В открывшемся диалоговом окне (рис. 4) установите флажок "Транспонировать "
· Щелкните на кнопке «ОК».
В результате выполненных действий на листе появиться транспонированная таблица наблюдений (рис.4).
| 
|
| Рис. 4. Диалоговое окно Специальная вставка и результат выполнения операции транспонирования |
FДля преобразования транспонированной таблицы в непрерывный ряд наблюдений необходимо:
· В столбце "В" продлите ряд дат наблюдений. Для этого выделите ячейки В13:В14 и выполните операцию автозаполнения с помощью мыши до ячейки В38.
· Вырежьте ячейки D3:D14 и вставьте их в интервал C15:C26;
· В ячейках столбца А столбцах разместите порядковый номер наблюдения
После того как непрерывный ряд наблюдений сформирован, оформите таблицу подобно тому, как показано на рис. 5.
2. После того, как данные наблюдений представлены в виде непрерывного ряда, постройте диаграмму и найдите уравнение линии тренда (как было описано выше).
Рис. 5. Фрагмент Рабочего листа Excel с данными непрерывного ряда наблюдений и результатами аппроксимации ряда наблюдений
В качестве примера, используем линию линейного тренда[4]
y = 1.6757*Х + 37.374
Полученное уравнение линии линейного тренда позволяет предсказать величину товарооборота[5], но не позволяет сделать какие-либо выводы о закономерностях отклонений точек наблюдения относительно линии тренда, о возможных сезонных (периодических) изменениях объемов продаж.
3. Для исследования временных изменений ряда наблюдений, например, сезонных, выполните следующие действия
o несколько изменим полученное уравнение линии тренда, заменив коэффициенты уравнения на неизвестные величины «а» и «b» и добавив член с периодической компонентой, например, - c *Sin(X)
Y =a + b*X+ c*Sin(X)
где Х номер наблюдения (или дата, размещенная в столбце В)
o В ячейках F2:H2 разместим названия коэффициентов уравнения a, b и c, а в ячейках F3:H3 их значения (которые позднее будут найдены с помощью процедуры «Поиск решения»
o В ячейку D3 запишите уравнение, которым Вы намерены аппроксимировать данные наблюдений:
Например, =$F$3+$G$3*A3+$H$3*SIN(A3)
где $F$3, $G$3 и $H$3 - коэффициенты уравнения.
А3 - номер наблюдения (независимая переменная).
Ø Скопируйте формулу в ячейки D4:D38
Ø В ячейку E3 запишите формулу квадрата разности наблюденного и предсказанного с помощью формулы значения: (C3-D3)^2 [6] и скопируйте ее в ячейки интервала E4:E38.
Ø В ячейке Е39 вычислите сумму квадратов отклонений =СУММ(Е3:Е38) [7]
Примечание. Величина суммы квадратов отклонений значений ряда наблюдений и ряда прогноза может быть также определена с помощью функции Excel ( категория «Математические»)
=СУММКВРАЗН(C3:C38;D3:D38).
.
Таким образом, вы подготовили наш рабочий лист к использованию программы " Поиск решения ".
Для того чтобы получить оптимальное (наилучшее) приближение к нашей функции необходимо найти минимальное значение целевой функции {=СУММ(Е3:E38)} Ю min, изменяя значения коэффициентов - ячейки a, b, c (F3:H3).
Примечание:
Обратите внимание, что записанная нами функция аппроксимации содержит нелинейный член (Sin(X)). Функции такого типа могут иметь не одно, а несколько решений, различающихся своими значениями[8]. Для решения подобных задач, необходимо указать область решения.
Это может быть сделано, задавая различные ограничения в диалоговом окне «Поиск решения». Однако, это достаточно сложная и трудоемкая задача – задача определения граничных условий решения задачи.
Найти необходимую область решения задачи можно, следуя следующему алгоритму:
Ø Введите в построенную диаграмму данные уравнения прогноза (в нашем случае, ячейки D3:D38).
Для этого:
§ Выделите диаграмму;
§ Выполните команду ДИАГРАММАÞДОБАВИТЬ ДАННЫЕ
§ 
В открывшееся диалоговое окно «Новые данные» введите диапазон ячеек $D$2:$D$38.
Так как ячейки коэффициентов уравнения F3:H3 пока не содержат данных, Вы не увидите на диаграмме никаких изменений
§ В ячейки F3 и G3 введите ранее полученные коэффициенты уравнения линии тренда (37,374 и 1,67, соответственно).
В результате выполненных действий на диаграмме появится новая линия уравнения прогноза, полностью совпадающая с ранее построенной линией тренда.
§ В ячейку H3 введите произвольное число (например, 10).В результате к введенным данным будет добавлена периодическая компонента (подобно тому как это показано на рисунке). Внимательно проанализируйте ход этой периодической компоненты и при необходимости откорректируйте уравнение аппроксимации (может потребоваться изменить фазу колебаний или добавить какую-то новую компоненту и сменить тип уравнения).
В том случае, если характер новой линии аппроксимации Вас устраивает, можно перейти к процедуре «Поиск решения»
4. Выполните команду СЕРВИС Ю ПОИСК РЕШЕНИЯ.
· В открывшемся диалоговом окне выполните необходимые установки:
| · В окне "Установить целевую ячейку:"(рис. 6.)введите адрес целевой функции - $E$39 · Переключатель Равной установите минимальномузначению. | 
|
| Рис. 6. Диалоговое окно процедуры Поиск решения |
· В окне " Изменяя ячейки " укажите адреса изменяемых ячеек (коэффициентов уравнения) - $F$3:$H$3.
| · В окне "Ограничения" (рис. 7) укажите, что значение ячейки $Е$39 должно быть больше нуля. | 
| |
| Рис. 7. Диалоговое окно Изменение ограничений процедуры Поиск решения | ||
| · Сохраните найденное решение, щелкнув по кнопке "ОК" (рис. 8). | 
| |
| Рис. 8. Диалоговое окно Результаты поиска решений процедуры Поиск решения | ||
В результате выполненных действий процедура «Поиск решения» определила значения коэффициентов уравнения, которое наилучшим образом аппроксимирует данные наблюдений с учетом их периодической составляющей:
Y = 36.82+1.7*X+4.21*Sin(X)
Полученное уравнение имеет не только более высокую степень аппроксимации (см рис. 8), но и позволяет проанализировать влияние периодической составляющей. (В нашем примере, частичное увеличение спроса ожидается в осенние месяцы).
Дальнейший анализ влияния периодической составляющей на величину объема продаж может быть направлен на оценку величины увеличения (уменьшения) объемов продаж, вызванной этой компонентой. Проведение такого анализа не вызывает особых затруднений и может быть легко выполнен Вами самостоятельно.
