для_Поиска решения (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003)

x -2 -0.5 0.5 Задание: Задание: 1) С помощью команды Под 1) С помощью команды По ячейках D3, D4 и D5 послед ячейках D3, D4 и D5 после три корня уравнения на отре три корня уравнения на отр используя заданные прибли используя заданные прибл y = 3 x3 + 5 x2 - 1 Задание: Задание: 1) С помощью команды Подбор параметра… в 1) С помощью команды Подбор параметра… в ячейках D3, D4 и D5 последовательно найдите ячейках D3, D4 и D5 последовательно найдите три корня уравнения на отрезке [-2; 1], три корня уравнения на отрезке [-2; 1], используя заданные приближения.

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

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

На листе Расчет для этих параметров отведена таблица в верхней части листа. Задание 1) На листе Расчет в столбце Yвыч ввести формулу, вычисляющую значение модельной функции. X следует брать из левого столбца, а параметры модели - из второй строки. 2) В ячейке D4 вычислить сумму квадратов разностей экспериментальных и вычисленных значений Y. Вспомните о функции СУММКВРАЗН. 3) При помощи надстройки Поиск решения найти значения шести параметров модели, соответствующих минимальному значению суммы квадратов разностей. Проверить, что вычисленные значения модельной функции действительно соответствуютэкспериментальным данным (в пределах ошибки эксперимента), как на рисунке.

a1 x1 1 s1 2 a2 4 x2 1 4 Сумма квадратов разностей = X Y 1 1.2 1.4 1.6 1.8 2 2.2 2.4 2.6 2.8 3 3.2 3.4 3.6 3.8 4 4.2 4.4 4.6 4.8 5 0.114 0.133 0.303 0.602 1.14 1.556 1.857 1.7 1.394 0.96 0.72 0.69 0.811 0.833 0.678 0.607 0.382 0.28 0.164 0.106 0.127 Yвыч s2 4 АППРОКСИМАЦИЯ АППРОКСИМАЦИЯЭКСПЕРИМЕНТА ЭКСПЕРИМЕНТАМОДЕЛЬНОЙ МОДЕЛЬНОЙФ 2 1.8 1.6 1.4 1.2 1 0.8 0.6 0.4 0.2 0 0.5 1 1.5 2 2.5 3 3.5 4 4 РИМЕНТА РИМЕНТАМОДЕЛЬНОЙ МОДЕЛЬНОЙФУНКЦИЕЙ ФУНКЦИЕЙ 3 3.5 4 4.5 5 5.5 .