Просмотр содержимого документа
«Алгоритм решения в табличномпроцессоре EXCEL»
Вычисление сложных выражений
Введите в диапазон а2:Д4 компоненты вектора X.
Введите в диапазон в2:сз компоненты матрицы В.
Введите в диапазон d2:e3 компоненты матрицы С.
Введите в ячейку вб следующую формулу:
= (2*сумм(а2:а4)+сумм(в2:c3*d2:ез)^2)/(1+сумм(а2:а4^2))
Завершите ввод нажатием комбинации клавиш Ctrl+Shift+Enter. MS Excel возьмет формулу в строке формул в фигурные скобки и произведет требуемые вычисления
{=(2*сумм(а2:а4)+сумм(в2:c3*d2:ез) ^2) /(1+сумм(а2:а4^2)) }
перечисленные в табл. 3.1.
Таблица 3.1. Функции обработки матриц
Функция (рус.) | Функция (англ.) | Описание |
мобр(массив) | minverse(array) | Возвращает обратную |
| | матрицу |
мопред(массив) | mdeterm(array) | Возвращает |
| | определитель матрицы |
| | |
мумнож(массив1; массив2) | mmult (array 1", array2) | Возвращает матричное |
| | произведение двух матриц |
трансп(массив) | transpose(array) | Возвращает транспони |
| | рованную матрицу |
Решение системы линейных уравнений
Выберите тот диапазон, в который будет введено решение. Например, F2: F3.
Введите в него формулу
=МУМНОЖ(МОБР(А2:ВЗ);D2:D3)
Завершите ввод формулы нажатием комбинации клавиш Ctrl+Shift+ +Enter. MS Excel возьмет формулу в строке формул в фигурные скобки и произведет требуемые вычисления с элементами массива (рис. 3.5).
{=МУМНОЖ(МОБР(А2:ВЗ);D2:D3}}
Для нахождения вектора X:
Введите элементы матрицы А в диапазон ячеек а2:вз.
Введите элементы вектора В в диапазон ячеек d2:D3.
Выберите диапазон f2:f3, куда поместим элементы вектора решения.
Введите в этот диапазон формулу:
=мумнож(мобр(мумнож(а2:вз;а2:вз));d2:d3)
Завершите ввод формулы нажатием комбинации клавиш Ctrl+Shift+ +Enter. MS Excel возьмет формулу в строке формул в фигурные скобки и произведет требуемые вычисления с элементами массива.
Нахождение значения квадратичной формы
Введите элементы матрицы А в диапазон ячеек А2:вз (рис. 3.6).
Введите элементы вектора X в диапазон ячеек d2: d3.
Выберите ячейку f2, куда необходимо поместить значение квадратичной формы.
Введите в эту ячейку формулу
=мумнож(мумнож(трансп(d2:d3);а2:вз);d2:d3)
Завершите ввод формулы нажатием комбинации клавиш Ctrl+Shift+ +Enter. MS Excel возьмет формулу в строке формул в фигурные скобки и произведет требуемые вычисления с элементами массивов (рис. 3.6).
{=мумнож(мумнож(трансп(d2:d3);а2:вз);d2:d3)}
Пошаговое решение
системы линейных уравнений методом Гаусса
Итак, для пошагового решения этой системы уравнений сначала введите на рабочем листе исходные данные. Для этого:
В ячейки диапазона А2:С4 введите коэффициенты системы, стоящие при неизвестных.
В ячейках диапазона D2:D4 задайте свободные члены. Приступим к прямой прогонке метода Гаусса:
Через буфер обмена скопируйте диапазон A2:D2 на A6:D6.
Выберите диапазон A7:D7.
Введите в него следующую формулу и завершите ее ввод нажатием комбинации клавиш Ctrl+Shift+EnteraF
{=аз:d3-$a$2:$d$2*a3/$a$2} \ч
Выберите диапазон a7:d7, расположите указатель мыши на маркере заполнения этого диапазона и пробуксируйч'^ его вниз на одну строку.
Выделите диапазон a6:d7 и скопируйте епо содержимое в буфер обмена.
Выберите ячейку аю. '
Укажите команду Правка | СпециалыЛя вставка. На экране отобразится диалоговое окно Специальная вставка^ (рис. 3.8). Выберите переключатель значения в группе Вставить и нажму/те кнопку ОК. В результате в диапазон aio:dii из диапазона a6:d7 бур(ут скопированы только значения, а не формулы.
Выделите диапазон a12:D12.
Введите в него следующую формулу и завершите ее ввод нажатием комбинации клавиш Ctrl+'\Shift+Enter.
{=а8:d8-a7:d7*b8/b7}
Примечание:
Команда Правка | Специальная вставка удобна при копировании и вставке части атрибутов ячеек, таких как формат или значение. Команда позволяет комбинировать в одной ячейке атрибуты из разных ячеек, а также выполнять над ними арифметические операции. Кроме того, установка флажка транспонировать позволяет вставлять в рабочий лист данные из буфера обмена с одновременным их транспонированием. А установка флажка пропускать пустые ячейки разрешает игнорировать пустые ячейки при вставке в рабочий лист данных из буфера обмена.
Прямая прогонка метода Гаусса закончилась. Переходим к обратной прогонке.
Выберите диапазон f8:18.
Введите в него следующую формулу и завершите ее ввод нажатием комбинации клавиш Ctrl+Sh$+Enter.
{=А12:d12/C12}
Выделите диапазон f7:17.
Введите в него следующую формулу и завершите ее ввод нажатием комбинации клавиш Ctrl+Shift+Enter.
{= (All:D11-F8:18*С11) /ВЦ)
Выберите диапазон F6:16.
Введите в него следующую формулу и завершите ее ввод нажатием комбинации клавиш Ctrl+Shifl+Enter.
{=(А10:D10-F7:I7*B10-F8:18*С10)/А10} Итак, решением системы уравнений является следующий вектор
0.28037 Х= 0.32710 0.87850
Построение поверхности
Для этого:
Введите в ячейку А2 значение -2, а в ячейку аз — значение -1.8. Выберите диапазон ячеек а2:аз. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон а4:а22. Таким образом, значения аргумента х протабулированы от -2 до 2 с шагом jj . 2.'
Введите в ячейку В1 значение -1, а в ячейку ci значение -0.8. Выберите диапазон ячеек в1:С1. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон di:li. Значения аргумента у протабулированы от -1 до i с шагом 0.2.
В ячейку в2 введите формулу:
=$а2л2 - в$1л2
Выберите ячейку В2, расположите указатель мыши на маркере ее заполнения и протяните его вниз на диапазон в2:ъ22.
Нахождение корней уравнения с помощью подбора параметра
Введите в ячейку А2 значение -1, а в ячейку аз — значение .-0.8.
Выберите диапазон а2:аз, расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон а4:А12. Аргумент протабулирован.
В ячейку В2 введите формулу:
=а2лз-0.01*а2л2—0.7044*а2+0.139104
Выберите ячейку В2. Расположите указатель мыши на маркере заполнения этой ячейки и протяните его на диапазон вз:в12. Функция также протабулирована.
Прежде чем приступить к нахождению корней при помощи подбора параметра, необходимо выполнить некоторую подготовительную работу:
□ Установите точность, с которой находится корень. Корень при помощи подбора параметра находится методом последовательных приближений. Для этого выберите команду Сервис | Параметры и на вкладке Вычисления диалогового окна Параметры задайте относительную погрешность и предельное число итераций равными 0,0000 и 1000, соответственно.
□ Отвести ячейку С4 под второй корень, ввести в нее начальное приближение 0.7, а в ячейку d4 ввести следующую формулу
=с4^3—0.01*с4^2—0,7044*с4+0.139104
Теперь можно переходить к нахождению первого корня уравнения:
Выберите команду Сервис | Подбор параметра. На экране отобразится диалоговое окно Подбор параметра.
В поле Установить в ячейке введите ссылку на ячейку d2 (рис. 2.19). В этом поле дается ссылка на ячейку, в которой введена формула, вычисляющая значение левой части уравнения. Для нахождения корня с помощью подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную.
В поле Значение введите 0. Здесь указывается значение из правой части уравнения.
В поле Изменяя значение ячейки введите С2. В данном поле приводится ссылка на ячейку, отведенную под переменную.
Нажмите кнопку ОК.
Ячейка | Формула либо значение |
A3 | 0 |
ВЗ | 2 |
СЗ | =(АЗ+ВЗ)/2 |
D3 | = (АЗл2-2)*(СЗл2-2) |
ЕЗ | =СЗл2-2 |
F3 v | =ЕСЛИ(ВЗ-АЗ |
А4 | =ЕСЛИ(D3A3;СЗ) |
В4 | =ЕСЛИ(D3 |
С4 | =(А4+В4)/2 |
D4 | =(А4л2-2)*(С4л2-2) |
Е4 | =С4А2-2 |
F4 | =ЕСЛИ(В4-А4ТЕКСТ(С4;"0.0000");"") |