yandex rtb 1
ГоловнаЗворотній зв'язок
yande share
Главная->Економіка->Содержание->2.    Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.

МетодМГСУ_ИС в экономике_2001_ч2_Прогнозирование данных

2.    Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.

 

Процедура Excel «Поиск решения» может оказаться весьма полезной при анализе данных наблюдений.

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

 

Рис. 2 Фрагмент  рабочего листа Excel с таблицей и графиком товарооборота предприятия «Альфа» в 1999, 2000 и 2001гг.г.                         (сопоставимых ценах)

Первое, что приходит в голову при анализе приведенных таблицы и графика, это то, что в течение каждого года наблюдался рост товарооборота. При этом, наблюдалось и увеличение объемов от года к году. Можно для каждой кривой графика построить линию тренда ( команда ВСТАВКАЮЛИНИЯ ТРЕНДА для выделенной кривой) и попытаться дать оценку скорости прироста товарооборота в течении года, а из сравнения их сделать вывод о динамике товарооборота в течении всего периода наблюдений (1999-2001гг.). Однако, и в этом случае мы мало что сможем сказать о наблюдаемых отклонениях точек наблюдений от линии тренда и тем более о их закономерностях в течении всего периода.

FПопытаемся провести этот анализ, используя программу «Поиск решения»

1.Представим имеющиеся данные в виде непрерывного ряда наблюдений и по эти данным построим график и линию тренда изменения товарооборота за период 1999 - 2001 гг.

 

 

 

 

 

Примечание: При преобразовании исходных данных в непрерывный ряд наблюдений удобно воспользоваться операцией транспонирования матрицы наблюдений

Для транспонирования матрицы:

·       выделите исходный ряд наблюдений (ячейки B3:M6);

·       выполните команду КОПИРОВАТЬ

·       откройте новый лист Excel

·       установите курсор мыши в любую ячейку (например,А3)

·       выполните команду ПРАВКАðСПЕЦИАЛЬНАЯ ВСТАВКА

·       в открывшемся диалоговом окне установите флажок «Транспонировать»

·       щелкните на кнопке «ОК».

В результате выполненных действий на листе появиться транспонированная таблица наблюдений.

FДля преобразования транспонированной таблицы в непрерывный ряд наблюдений:

·       в столбце «А» продлите ряд дат наблюдений (выделите ячейки А13:А14 и выполните операцию автозаполнения с помощью мыши);

·       вырежьте ячейки С3:С14 и вставьте их в интервал В15:В26;

·       вырежьте ячейки D3:D14 и вставьте их в интервал В27:В38;

 

После того как непрерывный ряд наблюдений сформирован, оформите таблицу подобно тому как показано на рис.3

 

Рис. 3 Фрагмент Рабочего листа Excel с данными непрерывного ряда наблюдений и результатами аппроксимаций ряда наблюдений

 

2.  После того, как данные наблюдений представлены в виде непрерывного ряда, построим диаграмму и найдем уравнение линии тренда (как было описано выше).

Y = 0,0562X - 1993,2;  R2 = 0,9409

 

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

 

3, Для исследования временных изменений ряда наблюдений (например сезонных), выполним следующие действия:

FВ ячейки столбца «С» запишем уравнение линии тренда в, добавив к нему функцию описания колебательного процесса

Y=A + B * X + C^2 * Cos(X)

где  A,B,C, - коэффициенты уравнения

        Х          -  номер наблюдения (независимая переменная)   

 

 

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

Y= A+ B*ln(X) + Sin(C*X)

·       В ячейках  E3, F3, G3 запишем величины этих коэффициентов (первоначально они могут быть заданы произвольно)

·       В ячейке D3 запишем формулу квадрата разности наблюденного и предсказанного с помощью формулы значения (C5-D3)^2 и скопируем ее в ячейки интервала D4:D38.

·       В ячейке D39 вычислим сумму квадратов отклонений =СУММ(D3:D38)

 

Примечание

Величина суммы квадратов отклонений значений ряда наблюдений и ряда прогноза могут быть также определены с помощью функции Excel СУММКВРАЗН(B3:B38;C3:C38) (Пример вычисления этой функции приведен в ячейке «J2»).

Для работы с этой функцией:

·       установите курсор мыши в ячейку, куда должно быть возвращено вычисленное значение;

·       вызовите диалоговое окно мастера функций СУММКВРАЗН() (Группа функций – математические)

·       В окна «Массив Х» и «Массив введите данные ряда наблюдений (Y1) и ряда прогноза (Y2), соответственно.

·       щелкните на кнопке «ОК».

 

Таким образом, мы подготовили наш рабочий лист к использованию программы «Поиск решения» ( мы должны найти минимальное значение целевой функции {=СУММ(Е5:У41)}Юmin изменяя значения коэффициентов - ячейки E3:G3).

 

 

­­4. Выполним команду СЕРВИСЮПОИСК РЕШЕНИЯ.

·            в открывшемся диалоговом окне выполним необходимые установки:

·            В окне «Установить целевую ячейку:» введите адрес целевой функции - $E$21;

·            Установите «Равной минимальному значению»

·            В окне «Изменяя ячейки» укажите адреса изменяемых ячеек (коэффициентов уравнения-  $E$3:$G3;

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

 

 

 

 

4