Информатика (стр. 2 )

Рисунок 9 - Формулы для вычисления значений определенного интеграла

Таблица 2 - Результаты расчета по формулам

Приближенные значения интеграла:
Метод прямоугольников	0,
Метод трапеций	0,
Метод парабол	0,

Аналогичным образом вычисляются приближенные значения интеграла при разбиении интервала интегрирования на 20 частей.

Зная, что точное значение интеграла равно 0,393 (ячейка В22) можно оценить уровень погрешности вычислений и сделать выводы о влиянии метода вычисления и количества точек разбиения интервала интегрирования.

Рисунок 10 - Сравнительный анализ погрешности вычислений

В строке формул на рисунке отображена формула для расчета относительной погрешности в ячейке D25. В остальные ячейки этого столбца ее можно просто скопировать. Функцию вычисления абсолютной величины числа (ABS) можно найти в библиотеке встроенных функций MS Excel (Меню: ВставкаФункцияМатематические).

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

2.3.  Решение системы линейных уравнений.

Наиболее простым универсальным способом решения системы линейных уравнений средствами MS Excel является метод обратной матрицы. Решение системы линейных уравнений находится как произведение матрицы, обратной матрице коэффициентов системы линейных уравнений на матрицу правых частей. Все необходимые для этого операции выполняются при помощи двух встроенных функций из библиотеки MS Excel.

Описание функций.

Функция МОБР(массив) находится в разделе «математические», возвращает обратную матрицу. Аргумент один – для данной задачи – матрица коэффициентов системы линейных уравнений.. Результат, возвращаемый функцией (обратная матрица) представляет собой массив такой же размерности как исходный.

Функция МУМНОЖ(массив1;массив2) находится в разделе «математические», возвращает произведение матриц, хранящихся в массивах. Аргументами являются два массива, в данном случае это обратная матрица, полученная с использованием функции МОБР (массив1) и матрица правых частей. Результатом умножения матриц будет массив решений системы линейных уравнений.

Массивы (в данном случае матрицы), представляют собой прямоугольные или квадратные области на листе MS Excel. При использовании области в качестве аргумента функции она задается двумя адресами: первым и последним (левым верхним и правым нижним) через двоеточие. Однако для записи этих адресов в формулу достаточно просто выделить нужную область.

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

Рассмотрим пример:

Постановка задачи

Решить систему линейных уравнений:

Порядок решения:

Записать матрицу коэффициентов системы линейных уравнений (размерность 3х3) и матрицу правых частей (размерность 3х1 – столбец). Пример расположения данных на листе MS Excel приведен на рисунке;

При помощи функции МОБР(A2:C4) вычислить обратную матрицу;

При помощи функции МУМНОЖ(A6:C8;E2:E4) вычислить матрицу решений системы линейных уравнений.

Рисунок 11 - Решение системы линейных уравнений

На рисунке в строке формул видна формула для вычисления значений неизвестных. Фигурные скобки – признак того, что формула размещена не в одной ячейке, а в области – в данном случае в ячейках (Е6:Е8). Программа ставит их автоматически, если перед размещением формулы был выделен диапазон, а не одна ячейка (см. выше).

В порядке оформления для большей наглядности текст в ячейках (D6:D8) отформатирован влево, а значения неизвестных – вправо.

Та же область листа в режиме формул выглядит следующим образом:

Рисунок 12 - Решение системы линейных уравнений (режим формул)

2.4.  Аппроксимация методом наименьших квадратов (МНК)

Постановка задачи.

Дана дискретная (табличная) зависимость Yi от Xi. Требуется найти для нее аналитическое выражение (аппроксимирующую функцию) в виде полинома

F(x)=a+ax+…+ax

За меру отклонения полинома

F(x)=a+ax+…+ax (1)

от заданной функции Y=f(x) на множестве точек x, x,…, x принимают величину

, (2)

или

, (2’)

равную сумме квадратов отклонений полинома F(x) от функции y на заданной системе точек.

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

Найдем частные производные от величины S (cм. выражение (2’)), приравняем их нулю, получим для определения неизвестных коэффициентов a, a,…, a систему линейных уравнений:

(3)

___ ___ ___ ___ ___ ___ ___ ___ ___ ___ ___

Рассмотрим пример решения задачи с использованием электронных таблиц MS Excel.

Даны пять точек с координатами (x; y):

Xi	Yi
0,78	2,4
1,56	1,36
2,34	1,12
3,12	2,02
3,81	4,08

Подобрать аппроксимирующий полином второй степени вида F(x)=a+ax+ax. Построить графики функций F(x) и Yi.

При использовании в качестве аппроксимирующей функции полинома второй степени выражение (3) принимает вид системы трех уравнений с тремя неизвестными: a0, a1, a2

(4)

Для нахождения значений a0, a1 и a2 необходимо вычислить значения коэффициентов системы линейных уравнений, а затем решить эту систему.

Вычисление коэффициентов системы линейных уравнений удобно выполнить в таблице.

Таблица 1 - Вычисление коэффициентов системы линейных уравнений (4)

Xi0	Xi	Xi2	Xi3	Xi4	Yi	Xi*Yi	Xi2*Yi
1	0,78	0,6084	0,474552	0,3701506	2,4	1,872	1,46016
1	1,56	2,4336	3,796416	5,922409	1,36	2,1216	3,309696
1	2,34	5,4756	12,8129	29,982195	1,12	2,6208	6,132672
1	3,12	9,7344	30,37133	94,758543	2,02	6,3024	19,66349
1	3,81	14,5161	55,30634	210,71716	4,08	15,5448	59,22569
5	11,61	32,7681	102,7615	341,75046	10,98	28,4616	89,7917

В последней строке таблицы находятся суммы по столбцам, которые и являются коэффициентами системы уравнений.

Фрагмент этой таблицы для вычисления коэффициентов в режиме формул выглядит так:

Рисунок 13- Фрагмент таблицы для расчета коэффициентов в режиме формул

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

Рисунок 14 - Решение системы (4) методом обратной матрицы

Итак, коэффициенты аппроксимирующего многочлена F(x) (с точностью до трех десятичных знаков) имеют значения:

a=4,830, a=-3,759, a=0,93,
а сам многочлен имеет вид :

F(x)=0,93x- 3,759x+4,830 – это и есть решение задачи.

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

Xi	Yi	F(x)	(Yi-F(Xi))^2
0,78	2,4	2,	0,
1,56	1,36	1,	0,
2,34	1,12	1,	3,34227E-05
3,12	2,02	2,	0,
3,81	4,08	4,	0,
		S=	0,

С учетом того, что значения Хi и Yi в нашем решении уже содержатся в столбцах B и F соответственно (Рисунок 13), а найденные значения коэффициентов полинома в ячейках Е14:Е16 (Рисунок 14), расчетные формулы выглядят так:

Рисунок 15 - Формулы для расчета значений полинома и отклонений

При расчете значений F(x) использована абсолютная адресация по номеру строки для возможности копирования формул. Величина меры отклонения полинома (значение S) оказалась равной 0,044.

Весьма наглядным подтверждением достоверности найденного решения является диаграмма значений Yi и F(x), построенные с одной системе координат (см. п.1.8). В данном случае для построения диаграммы следует использовать значения Хi в качестве аргумента и значения Yi и F(x) в качестве рядов данных.

Для построения диаграммы:

ü  Выделить ячейки (А21:С25);

ü  Вызвать мастер построения диаграмм и по шагам мастера построить диаграмму, тип диаграммы – точечная, линия сглаженная, без маркеров или с маркерами. Для отображения на диаграмме значений функции в закладке «подписи данных» выдрать пункт «значение У»;

ü  Изменить график Y. Для этого щелкнем правой клавишей мыши по графику функции Y, вызвав его контекстное меню (меню свойств). В нем выбираем пункт «Тип диаграммы» и изменяем тип этого графика на точечный график с маркерами не соединенными линией. Это изображение более логично, т. к. Y не является непрерывной функцией – это просто набор точек.

ü  Если диаграмма предназначена для печати, рекомендуется убрать заливку области построения. Если Вы не собираетесь снимать значения с графика, можно убрать и линии сетки. Проще всего это сделать, выделив соответствующий элемент диаграммы и нажав клавишу «Delete».

Рисунок 16 – Сравнительная диаграмма значений Y и F(X)

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

Итак, задача решена. Однако, современные версии электронных таблиц имеют специальные средства для решения таких задач.

Рассмотрим пример решения той же задачи при помощи надстройки «Поиск решения».

Поскольку задача аппроксимации является типичной задачей оптимизации, логично воспользоваться предназначенным для решения таких задач средством – «Поиск решения» (см. п. 1.9).

Отведем на листе MS Excel три ячейки (B2:B4) для искомых значений коэффициентов полинома и построим таблицу, содержащую исходные данные, формулы для расчета значений аппроксимирующего полинома и квадратов отклонений.

Рисунок 17 - Подготовка данных для решения задачи аппроксимации

Вызываем программу «Поиск решения» (Меню: СервисПоиск решения) и выставляем в соответствующих полях диалогового окна значения параметров, необходимых для решения задачи.

Рисунок 18 - Настройка окна "Поиск решения" для решения задачи аппроксимации

В данном случае целевая функция – сумма квадратов отклонений, которая должна иметь минимальное значение. Изменяемые ячейки – ячейки, предназначенные для искомых значений коэффициентов. На этом этапе они могут быть пустыми. Ограничения в этой задаче не требуются.

Запустив на выполнение программу (кнопка «Выполнить»), получаем значения коэффициентов a0, a1 и a2.

Полученные в результате решения (Рисунок 19) значения коэффициентов полинома аппроксимации совпадают (по крайней мере до трех знаков после запятой) с первым вариантом решения (Рисунок 14).

Рисунок 19 - Решение задачи аппроксимации при помощи «Поиска решения»

Характерно, что в отличие от первого способа решения вид аппроксимирующей функции существенного значения не имеет.