Функции для работы с массивами. Решение систем линейных уравнений

ЛЕКЦИЯ №7 Функции для работы с массивами. Решение систем линейных уравнений Функции работы с массивами Массив это диапазон ячеек, рассматриваемый как единое целое или диапазон ячеек, над которыми надо произвести однотипную операцию. Например, если стоит задача умножения числа на матрицу, то диапазон ячеек, в которых находятся элементы матрицы будет являться массивом. В мастере функций в категориях «Математические» и «Ссылки и массивы» находятся специальные функции для работы с массивами. Как правило, завершение ввода для формул, работающих с массивами, осуществляется нажатием сочетания клавиш Shift+Ctrl+Enter. Последовательность действий при работе с массивами: • выделяется диапазон ячеек, в которых будет содержаться результат; • делается щелчок в строке формул и набирается формула; • завершается ввод формулы нажатием сочетания клавиш Shift+Ctrl+Enter. Для вычисления определителя существует функция МОПРЕД() у которой в качестве аргумента должен быть указан диапазон ячеек, содержащий элементы определителя. Эта функция возвращает только одно значение, поэтому завершение ввода для этой функции можно осуществить обычным образом. Для работы с матрицами служат следующие функции: МОБР(диапазон) – вычисление обратной матрицы; МУМНОЖ(диап1;диап2) – вычисление произведения двух матриц; МОПРЕД(диапазон) – вычисление определителя квадратной матрицы ТРАНСП(диапазон) – транспонирование матрицы. Эти функции возвращают диапазон значений и для завершения ввода надо обязательно использовать сочетание клавиш Shift+Ctrl+Enter. Кроме этого, перед вводом функции надо выделить диапазон ячеек, в которых будет помещён результат. Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!. МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов. МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к небольшим численным ошибкам округления. Квадратные матрицы, у которых определитель равен 0, не могут быть обращены, в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. При работе с формулами, в которые входят массивы, надо помнить, что если диапазон ячеек под результат будет выделен неправильно, то данными заполнятся только выделенные ячейки, и никакого сообщения об ошибке не будет! Формулы, которые возвращают массивы, в строке формул выглядят заключенными в фигурные скобки. В массиве результата нельзя редактировать, очищать, вставлять, удалять или перемещать отдельные ячейки. Диапазон результатов необходимо рассматривать как целое и редактировать все ячейки сразу. Чтобы удалить такую формулу надо 1 сначала выделить все ячейки, в которых она содержится, а затем нажать клавишу Delete. Для редактирования формулы массива также можно использовать два способа: выделить блок с формулой; нажать клавишу F2; ввести изменения в формулу и нажать комбинацию клавиш Ctrl+Shift+Enter или выделить ячейку с формулой массива; перейти в строку формул; ввести изменения в формулу и нажать комбинацию клавиш Ctrl+Shift+Enter. Для перемещения (копирования) содержимого массива с формулой массива, необходимо: выделить массив; выполнить команду Правка/ Вырезать (Копировать); выделить новый диапазон, чтобы в него поместился перемещаемый массив; выполнить команду Правка/.Вставить. Можно использовать мышь для выполнения тех же операций. Отметим, что использование имен массивов аргументов в формулах массивов позволяет избежать появления неприятных эффектов, связанных с относительной адресацией. При использовании массивов в качестве аргументов в формуле все массивы должны иметь одинаковые размерности. Если размерности массивов аргументов не совпадают, Excel в некоторых случаях может расширить аргументы. Решение системы линейных уравнений Системы уравнений в Excel с использованием встроенных функций для работы с матрицами решаются довольно просто. Как известно, система линейных уравнений может быть представлена в виде N a j =1 ij x j = b, где коэффициенты аij образуют матрицу А, коэффициенты в правой части уравнений bt формируют вектор В, и индекс i (как и индекс j) пробегает значения от 1 до N (т.е. матрица А является квадратной — будем рассматривать именно такие системы), a N, в свою очередь, определяется числом неизвестных переменных х j, относительно, которых решается система. Эта же система может быть записана в матричном виде как АХ = В, где через X обозначен вектор неизвестных переменных. До решения системы уравнений полезно разобраться в ее свойствах. Системы уравнений могут быть совместными и несовместными. Совместной называется система, если она имеет ровно одно решение. Условием совместности квадратной системы, т.е. когда число уравнений равно числу переменных, является неравенство нулю определителя системы. Система называется несовместной, когда она имеет бесчисленное множество решений; в этом случае определитель системы равен нулю. Рассмотрим пример. Решить систему уравнений: 4 x1 − 12 x2 + 3x3 = 12,   24 x2 + 4 x3 = 8,  x1 − 5 x2 = 0.  Для решения этой системы в Excel в рабочую таблицу следует ввести все данные, относящиеся к этой задаче: матрицу коэффициентов системы (т.е. матрицу А), а также вектор со значениями правых частей уравнений (вектор В). 2 1. Решение с.л.у. методом Крамера. Если система уравнений имеет вид АХ = В, то значение переменной х. может быть найдено согласно соотношению xi = i / , где  есть определитель матрицы А, а i тоже определитель, но только матрицы Аi, которая получается из матрицы А, если в ней заменить i-й столбец вектором коэффициентов В правых частей системы уравнений. 2. Решение с.л.у. методом обратной матрицы Решение такой системы, опять же в матричном виде, может быть представлено как X = А-1 В, и здесь через А-1 обозначена матрица, обратная к А. Другими словами, по определению матрица А-1 такая, что А-1 А = Е, а Е− единичная матрица (все диагональные элементы равны единицы, все прочие элементы — нулю). Поэтому формально процедура поиска решения системы линейных уравнений сводится к отысканию обратной матрицы и перемножению матрицы и вектора. 3. Решение с.л.у. методом Поиск решения Для того, чтобы использовать метод Поиск решения, надо подготовить целевую, изменяемые ячейки и ограничения. Целевым может быть любое уравнение системы, тогда остальные уравнения станут ограничениями. Проверка в правильности нахождения корней уравнения заключается в том, что должны выполняться равенства: А * А-1 = Е (единичная матрица) А * Х = В. 3 Решение систем нелинейных уравнений Применяя надстройку Excel Поиск решения можно решать системы нелинейных уравнений. Предварительно система уравнений должна быть приведена к одному уравнению. Рассмотрим последовательность решения на примере. Пример Дана система двух уравнений: x 2 + y 2 = 3   2x + 3 y = 1 Требуется найти все корни приведенных уравнений, если такие существуют. 1)Для определения существования корней построим графики уравнений, представив их в виде y = f(x). Первое уравнение: y1, 2 =  3 − x 2 . Второе уравнение: y3 = 1 − 2x . 3 2) Построим графики уравнений Y1, Y2 и Y3 в границах изменения аргумента [-1,7; 1,7] (решаем неравенство 3-х2≥0). В этих границах находится решение первого уравнения. Строим график. Как видно из рисунка решением системы уравнений являются точки пересечения окружности и прямой. Строим график. Как видно из рисунка решением системы уравнений являются 2 точки пересечения окружности и прямой 4 Для дальнейшего поиска корней необходимо привести систему к одному равносильному уравнению. Пара (х, у) является решением системы тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными: (х2 + у2 − 3)2 + (2х + 3у − 1)2 = 0 (1) 3) По графику определим отрезки локализации для каждой пары корней. Это[ (-2;-1);(1;1,5)] и [(1;2;(-1;-0,5)] 4) Введем в качестве целевой функции функцию (1), изменяемые ячейки – те, где будет находиться пара корней 1.Запускаем Сервис/Поиск решения. Приравниваем целевую ячейку к 0, добавляем 4 ограничения (по 2 на каждое неизвестное), нажимаем Выполнить. Аналогично подбираем вторую пару корней. 5