2.4. Использование процедуры «поиск решения» для подбора коэффициентов функции аппроксимации данных наблюдений
Для преодоления вышеописанных сложностей может быть использована процедура Поиск решения.
Рассмотрим возможность ее использования для аппроксимации ряда наблюдений, содержащей периодические (сезонные) колебания.
Подбор коэффициентов функции аппроксимации
Мы имеем многолетний ряд наблюдений (рис.2.32), характеризующий товарооборот некоторого торгового предприятия в течение нескольких лет3.
Рис. 2. 32. Фрагмент рабочего листа Excel с таблицей и графиком товарооборота предприятия «Альфа» в 2002, 2003 и 2004г.г. (в сопоставимых ценах)
На листе Excel подготовьте таблицу и графики, показанные на рис. 2.32.
Первое, что приходит в голову при анализе приведенных на рисунке таблицы и графика данных, это то, что в течение каждого года наблюдался рост товарооборота. При этом наблюдалось и увеличение объемов от года к году. Можно для каждой кривой графика построить линию тренда (команда Вставка / Линия тренда для выделенной кривой) и попытаться дать оценку скорости прироста товарооборота в течение года, а из сравнения их сделать вывод одинамике товарооборота в течение всего периода наблюдений (2002-2004гг.). Однако и в этом случае мало, что можно сказать о наблюдаемых отклонениях точек наблюдений от линии тренда и тем более об их закономерностях в течение всего периода. Мы абсолютно ничего не можем сказать о периодических составляющих и их влиянии на величину товарооборота.
Предлагаемый Excel набор функций аппроксимации (линий тренда) рядов наблюдений весьма ограничен и позволяет проследить (оценить) только генеральную тенденцию их поведения.
1. Сначала представьте имеющиеся данные в виде непрерывного ряда наблюдений и по этим данным постройте график и линию тренда изменения товарооборота за период 2002-2004гг.
Внимание. При преобразовании исходных данных в непрерывный ряд наблюдений удобно воспользоваться операциейтранспонирования матрицы наблюдений
Для транспонирования матрицы:
Выделите исходный ряд наблюдений (ячейки B3:M6).
Выделите команду Копировать.
Откройте новый лист Excel. Назовите его .
Установите курсор мыши в любую ячейку, например, В3.
Выделите команду Правка / Специальная вставка.
В открывшемся диалоговом окне (рис. 2.33) установите флажок "Транспонировать"
Щелкните на кнопке «ОК».
В результате выполненных действий на листе появиться транспонированная таблица наблюдений (рис.2.33).
Рис. 2. 33. Диалоговое окно Специальная вставка и результат выполнения операции транспонирования
Для преобразования транспонированной таблицы в непрерывный ряд наблюдений необходимо:
В столбце "В" продлите ряд дат наблюдений. Для этого выделите ячейки В13:В14 и выполните операцию автозаполнения с помощью мыши до ячейки В38 (рис. 2.34).
Вырежьте ячейки D3:D14 и вставьте их в интервал C15:C26; С27:С38.
В ячейках столбца "А" с А3 по А38 поместите порядковый номер наблюдения.
После того как непрерывный ряд наблюдений сформирован, оформите таблицу подобно тому, как показано на рис. 2.34.