"Алгоритм решения в табличном редакторе"

Вычисление сложных выражений

1.    Введите в диапазон а2:Д4 компоненты вектора X.

2.     Введите в диапазон в2:сз компоненты матрицы В.

3.     Введите в диапазон d2:e3 компоненты матрицы С.

4.     Введите в ячейку вб следующую формулу:

= (2*сумм(а2:а4)+сумм(в2:c3*d2:ез)^2)/(1+сумм(а2:а4^2))

5.     Завершите ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>. MS Excel возьмет формулу в строке формул в фигурные скобки и произ­ведет требуемые вычисления

6.     {=(2*сумм(а2:а4)+сумм(в2:c3*d2:ез) ^2) /(1+сумм(а2:а4^2)) }

7.                пе­речисленные в табл. 3.1.

Таблица 3.1. Функции обработки матриц Функция (рус.) Функция (англ.) Описание мобр(массив) minverse(array) Возвращает обратную матрицу мопред(массив) mdeterm(array) Возвращает определитель матрицы

мумнож(массив1; массив2)	mmult (array 1", array2)	Возвращает матричное
		произведение двух матриц
трансп(массив)	transpose(array)	Возвращает транспони­
		рованную матрицу

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

1.    Выберите тот диапазон, в который будет введено решение. Например, F2: F3.

2.    Введите в него формулу

=МУМНОЖ(МОБР(А2:ВЗ);D2:D3)

3.    Завершите ввод формулы нажатием комбинации клавиш <Ctrl>+<Shift>+ +<Enter>. MS Excel возьмет формулу в строке формул в фигурные скоб­ки и произведет требуемые вычисления с элементами массива (рис. 3.5).

{=МУМНОЖ(МОБР(А2:ВЗ);D2:D3}}

Для нахождения вектора X:

1.    Введите элементы матрицы А в диапазон ячеек а2:вз.

2.     Введите элементы вектора В в диапазон ячеек d2:D3.

3.     Выберите диапазон f2:f3, куда поместим элементы вектора решения.

4.     Введите в этот диапазон формулу:

=мумнож(мобр(мумнож(а2:вз;а2:вз));d2:d3)

5.    Завершите ввод формулы нажатием комбинации клавиш <Ctrl>+<Shift>+ +<Enter>. MS Excel возьмет формулу в строке формул в фигурные скоб­ки и произведет требуемые вычисления с элементами массива.

Нахождение значения квадратичной формы

1.    Введите элементы матрицы А в диапазон ячеек А2:вз (рис. 3.6).

2.     Введите элементы вектора X в диапазон ячеек d2: d3.

3.     Выберите ячейку f2, куда необходимо поместить значение квадратичной формы.

4.     Введите в эту ячейку формулу

=мумнож(мумнож(трансп(d2:d3);а2:вз);d2:d3)

5.     Завершите ввод формулы нажатием комбинации клавиш <Ctrl>+<Shift>+ +<Enter>. MS Excel возьмет формулу в строке формул в фигурные скоб­ки и произведет требуемые вычисления с элементами массивов (рис. 3.6).

{=мумнож(мумнож(трансп(d2:d3);а2:вз);d2:d3)}

Пошаговое решение

системы линейных уравнений методом Гаусса

Итак, для пошагового решения этой системы уравнений сначала введите на рабочем листе исходные данные. Для этого:

1.    В ячейки диапазона А2:С4 введите коэффициенты системы, стоящие при неизвестных.

2.     В ячейках диапазона D2:D4 задайте свободные члены. Приступим к прямой прогонке метода Гаусса:

1.    Через буфер обмена скопируйте диапазон A2:D2 на A6:D6.

2.     Выберите диапазон A7:D7.

3.  Введите в него следующую формулу и завершите ее ввод нажатием ком­бинации клавиш <Ctrl>+<Shift>+<EnteraF

{=аз:d3-$a$2:$d$2*a3/$a$2}         \_ч

4.  Выберите диапазон a7:d7, расположите указатель мыши на маркере за­полнения этого диапазона и пробуксируйч'^ его вниз на одну строку.

5.  Выделите диапазон a6:d7 и скопируйте епо содержимое в буфер обмена.

6.  Выберите ячейку аю.            '

7.  Укажите команду Правка | СпециалыЛя вставка. На экране отобразится диалоговое окно Специальная вставка^ (рис. 3.8). Выберите переключатель значения в группе Вставить и нажму/те кнопку ОК. В результате в диапа­зон aio:dii из диапазона a6:d7 бур(ут скопированы только значения, а не формулы.

8.  Выделите диапазон a12:D12.

9.  Введите в него следующую формулу и завершите ее ввод нажатием ком­бинации клавиш <Ctrl>+'\Shift>+<Enter>.

{=а8:d8-a7:d7*b8/b7}

Примечание:

Команда Правка | Специальная вставка удобна при копировании и вставке части атрибутов ячеек, таких как формат или значение. Команда позволяет комбинировать в одной ячейке атрибуты из разных ячеек, а также выполнять над ними арифметические операции. Кроме того, установка флажка транспо­нировать позволяет вставлять в рабочий лист данные из буфера обмена с од­новременным их транспонированием. А установка флажка пропускать пустые ячейки разрешает игнорировать пустые ячейки при вставке в рабочий лист данных из буфера обмена.

Прямая прогонка метода Гаусса закончилась. Переходим к обратной про­гонке.

1.    Выберите диапазон f8:18.

2.    Введите в него следующую формулу и завершите ее ввод нажатием ком­бинации клавиш <Ctrl>+< Sh$>+< Enter>.

{=А12:d12/C12}

3.    Выделите диапазон f7:17.

4.     Введите в него следующую формулу и завершите ее ввод нажатием ком­бинации клавиш <Ctrl>+<Shift>+<Enter>.

{= (All:D11-F8:18*С11) /ВЦ)

5.    Выберите диапазон F6:16.

6.     Введите в него следующую формулу и завершите ее ввод нажатием ком­бинации клавиш <Ctrl>+<Shifl>+<Enter>.

{=(А10:D10-F7:I7*B10-F8:18*С10)/А10} Итак, решением системы уравнений является следующий вектор

0.28037 Х= 0.32710 0.87850

Построение поверхности

Для этого:

1.    Введите в ячейку А2 значение -2, а в ячейку аз — значение -1.8. Выбери­те диапазон ячеек а2:аз. Расположите указатель мыши на маркере запол­нения этого диапазона и протяните его на диапазон а4:а22. Таким обра­зом, значения аргумента х протабулированы от -2 до 2 с шагом jj . 2.'

2.    Введите в ячейку В1 значение -1, а в ячейку ci значение -0.8. Выберите диапазон ячеек в1:С1. Расположите указатель мыши на маркере заполне­ния этого диапазона и протяните его на диапазон di:li. Значения аргу­мента у протабулированы от -1 до i с шагом 0.2.

3.     В ячейку в2 введите формулу:

=$а2^л2 - в$1^л2

4.    Выберите ячейку В2, расположите указатель мыши на маркере ее запол­нения и протяните его вниз на диапазон в2:ъ22.

Нахождение корней уравнения с помощью подбора параметра

1.    Введите в ячейку А2 значение -1, а в ячейку аз — значение .-0.8.

2.    Выберите диапазон а2:аз, расположите указатель мыши на маркере за­полнения этого диапазона и протяните его на диапазон а4:А12. Аргумент протабулирован.

3.    В ячейку В2 введите формулу:

=а2^лз-0.01*а2^л2—0.7044*а2+0.139104

4.    Выберите ячейку В2. Расположите указатель мыши на маркере заполне­ния этой ячейки и протяните его на диапазон вз:в12. Функция также протабулирована.

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

□ Установите точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого выберите команду Сервис | Параметры и на вкладке Вычис­ления диалогового окна Параметры задайте относительную погрешность и предельное число итераций равными 0,0000 и 1000, соответственно.

□ Отвести ячейку С4 под второй корень, ввести в нее начальное приближе­ние 0.7, а в ячейку d4 ввести следующую формулу

=с4^{^}3—0.01*с4^{^}2—0,7044*с4+0.139104

Теперь можно переходить к нахождению первого корня уравнения:

1.    Выберите команду Сервис | Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.

2.     В поле Установить в ячейке введите ссылку на ячейку d2 (рис. 2.19). В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью под­бора параметра уравнение надо представить в таком виде, чтобы его пра­вая часть не содержала переменную.

3.     В поле Значение введите 0. Здесь указывается значение из правой части уравнения.

4.     В поле Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.

Нажмите кнопку ОК.

Ячейка	Формула либо значение
A3	0
ВЗ	2
СЗ	=(АЗ+ВЗ)/2
D3	= (АЗл2-2)*(СЗл2-2)
ЕЗ	=СЗл2-2
F3 v	=ЕСЛИ(ВЗ-АЗ<$В$1;"Корень найден и равен " & ТЕКСТ (СЗ; "0.0000") ;"")
А4	=ЕСЛИ(D3<=0;A3;СЗ)
В4	=ЕСЛИ(D3 <=0;СЗ;ВЗ)
С4	=(А4+В4)/2
D4	=(А4л2-2)*(С4л2-2)
Е4	=С4А2-2
F4	=ЕСЛИ(В4-А4<$В$1;"Корень найден и равен " & Ч>ТЕКСТ(С4;"0.0000");"")