Электронная таблица Excel (стр. 5 )

Листинг 6.10. Решение систем линейных алгебраических уравнений методом Крамера
	А	B	C	D	E	F	G
1		Решение систем линейных уравнений методом Крамера
2		Главный определитель
3		2	-5	6	-9
4		1	-4	7
5		3	-6	8
6		Первый дополнительный		Результат
7		8	-5	6	144	X=	-16,00
8		5	-4	7
9		-4	-6	8
10		Второй дополнительный
11		2	8	6	126	Y=	-14,00
12		1	5	7
13		3	-4	8
14		Третий дополнительный
15		2	-5	8	45	Z=	-5,00
16		1	-4	5
17		3	-6	-4

5.  Записать напротив первой строки матрицы коэффициентов главного определителя в ячейку F3 расчетную формулу для его вычисления: МОПРЕД(A3:C5) ;

6.  Скопировать расчетную формулу из ячейки F3 в ячейки F8, скорректировать ее, а затем скопировать в ячейки F12 и F16;

7.  Записать формулы для вычисления неизвестных как отношение соответствующих дополнительных определителей к главному определителю в ячейки I3, I4 и I5.

Контрольные вопросы

1.  Что такое расширенная матрица коэффициентов системы линейных уравнений?

2.  Что является решением системы линейных алгебраических уравнений?

3.  Какие методы применяются для решения СЛАУ в электронной таблице?

4.  Напишите алгоритм решения САЛУ методом Крамера.

5.  Напишите алгоритм решения САЛУ матричным методом с использованием обратной матрицы.

6.  Назовите функции электронной таблицы для работы с матрицами.

7.  Какие методы используются для решения нелинейных систем уравнений?

6.5. Элементы математического анализа

6.5.1. Вычисление производных численными методами

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

O(h) (6.6)

O(h2) (6.7)

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

Ошибка усечения пропорциональна величине шага для первой производной и квадрату величины шага для второй производной. С уменьшением шага ошибка усечения уменьшается, но одновременно возрастает ошибка округления. Чтобы избежать получения ошибочных результатов необходимо контролировать, чтобы разность двух смежных значений функции не была меньше точности вычислений. Например, на компьютере вычисляющем с точностью до 14 знаков, разность не должна быть меньше 10-14 (см. дополнительно раздел 3).

6.5.2. Вычисление определенного интеграла численными методами

Известно, что определенный интеграл равен площади, ограниченной подынтегральной функцией, осью х-ов и вертикальными прямыми y=a и y=b, где a и b – границы интервала интегрирования функции.

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

, (6.8)

Здесь h - шаг табулирования функции.

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

6.5.3. Определение коэффициентов

эмпирических формул методом

наименьших квадратов

Нередко при обработке результатов наблюдений встречаются со следующей задачей: в итоге опыта получен ряд значений переменных x и y, однако характер функциональной зависимости между ними остается неизвестным. Требуется по полученным данным найти аналитическое выражение зависимости между ними. Формулы, полученные в результате решения задач подобного рода, называются эмпирическими.

Задача о построении эмпирической формулы состоит в следующем.

Пусть в результате экспериментов получены данные, представленные таблицей:

x1	x2	...	xk	...	xn
y1	y2	...	yk	...	yn

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

Требуется найти эмпирическую формулу у=f(xi, a1,a2,…am), где функция f зависит не только от значения аргумента хi, но и от некоторых параметров aj. Разности

f(xi, a1,a2,…am)-yi=ei, (i=1,2,3,..m) (6.9)

называются отклонениями или погрешностями, где

xi - числа из первой строки таблицы,

yi - числа из второй строки данной таблицы,

f(xi, a1,a2,…am) - значения функции при соответствующих значениях аргумента xi .

Параметры aj эмпирической формулы y=f(x, a1,a2,…am) необходимо подобрать таким образом, чтобы отклонения ei оказались наименьшими. Наиболее распространенным критерием является критерий, лежащий в основе метода наименьших квадратов: параметры функции выбирают так, чтобы сумма квадратов отклонений оказалась минимальной:

(6.10)

Минимум функции находят приравнивая нулю частные производные по переменным параметрам аj

, , … (6.11)

Полученные соотношения образуют систему уравнений, для определения коэффициентов ai, для i=1,2,..,m.

Пусть функция f(x) является многочленом степени m, т. е.

f(x)=a0xm + a1xm-1 + ...+aixm-i +...+am-2x2 am-1x + am, (a0 не равно 0).

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

В случае m=1 имеем линейное приближение функции по методу наименьших квадратов, в случае m=2 - квадратичное приближение.

В случае линейной зависимости предполагается, что все точки лежат на некоторой прямой

y=a*x+b, (6.12)

где a и b - некоторые постоянные параметры, подлежащие определению. Для их нахождения требуется решить систему:

a*Sxi2 + b*Sxi = Sxi*yi (6.13)

a*Sxi + b*n =Syi

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

y=a*x2+b*x+c, (6.14)

где a, b, c - постоянные параметры, подлежащие определению. Они находятся из системы:

a*Sxi 4+ b*Sxi3 + c*Sxi2 = Syi*xi2

a*Sxi3 + b*Sxi2 + c*Sxi =Sxi*yi (6.15)

a*Sxi2 + b*Sxi + c*n =Syi

Решение системы осуществляется любым известным методом.

После того как найдены значения коэффициентов систем уравнений (6.13) и (6.15), вычисляют значения выражений (6.12) и (6.14) при заданных значениях аргументов. Каждое из полученных уравнений удовлетворяет условию (6.11).

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

Порядок определения коэффициентов

эмпирических формул

1.  Загрузите программу Excel.

2.  Составьте таблицу:

Xi	Yi	Xi2	Xi3	Xi 4	XiYi	XiYi2
...
...
S Xi	SYi	S Xi2	S Xi3	S Xi 4	S XiYi	S XiYi2

3.  Занесите в два первых столбца экспериментальные значения X и Y.