Определение коэффициентов аппроксимирующего многочлена

с помощью надстройки «Поиск решения»

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

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

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

Чтобы начать работу с надстройкой «Поиск решения», следует выбрать в меню Сервис команду Поиск решения. Откроется окно диалога Поиск решения (рис.3). В этом окне необходимо указать цель, изменяемые ячейки и ограничения (если они есть):

 

Рис.3. Окно диалога Поиск решения

· В поле Установить целевую ячейку задается цель, которую должен достичь поиск решения. В данном примере - минимизировать сумму квадратов отклонений (значение в ячейке ), поэтому в это поле надо ввести и установить переключатель Равной в положение Минимальному значению.

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

·

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

 

Рис.4. Окно диалога Параметры поиска решения

· Щелкнуть на кнопке Параметры в окне диалога Поиск решения. Откроется окно диалога Параметры поиска решения (рис.4), в котором следует:

§ Снять флажки в поле Линейная модель и в поле Неотрицательные значения (если они стоят), щелкнув по ним мышью.

§ Щелкнуть по кнопке . Откроется окно диалога Поиск решения.

§ Щелкнуть по кнопке Выполнить диалогового окна Поиск решения. Откроется окно диалога Результаты поиска решения (рис.5), в котором можно либо оставить эти значения на листе, если установить переключатель Сохранить найденные значения и нажать , либо восстановить значения, которые содержались на листе перед активизацией поиска решения, если нажать кнопку Отмена.

Рис.5. Окно диалога Результаты поиска решения

 

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

. (10)

Среднеквадратическая погрешность =0,07530 значительно меньше значений заданной в таблице 2 функции, следовательно, подобранная функция хорошо аппроксимирует экспериментальные данные (рис.6).