Работа с матрицами

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

В Excel имеется ряд функций для работы с матрицами.

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

В качестве иллюстрации рассмотрим задачу о решении системы линейных уравнений 3x + 2y + z = 10, 2x — y + 2z = 6 и x + 3y — 3z = -2. В матричном виде эта система может быть записана следующим образом: AX = В, где матрица коэф-

обозначен вектор независимых переменных, относительно кото-

рых решается система уравнений. Решение системы уравнений записывается в виде X = A_1B, где через A»1 обозначена матрица, обратная к матрице коэффициентов A.

Для решения задачи средствами Excel создаем документ, представленный на рис. 6.1. ВЗ • fx , (=МУМНО>К(Е7:Є9;В7:В9)> А В С D Е F G Н 1 Решение системы линейных уравнений г 3 1 3 2 1 г -і 3 1

-2 -0.21429 0.571429 0.5 0.642S57 -0.71429 -0.5 0.357143 -0.28571 -0.5 S В = А*(-1) = 9 10 11 10 12 АХ = б 13 -2 14 Рис. 6.1. Решение системы линейных уравнений

В ячейки E3:G5 заносим коэффициенты системы линейных уравнений (матрица A).

В ячейки B7:B9 вводятся значения правых частей уравнений (вектор B). Обратная матрица A»1 вычисляется по формуле =МОБР(Е3^5), причем эта формула вводится как формула массива: выделяются ячейки E7:G9, в строку формул вводится соответствующее выражение, после чего нажимаем комбинацию клавиш Ctrl+Shift+Enter.

В качестве еще одной задачи на использование функций для работы с матрицами рассмотрим процесс вычисления собственных чисел. Напомним, что собственные числа X матрицы A определяются как решения уравнения det( A — XE) = 0, где через E обозначена единичная матрица того же ранга, что и матрица A, а det(A) обозначает детерминант (определитель) матрицы A.

венными числами этой матрицы являются значения X = 5, X = л/2 и X = -V2. Документ для решения задачи средствами Excel, представлен на рис. 6.2.

Рис. 6.2. Документ с данными для вычисления собственных чисел матрицы

В ячейки B3:D5 вводятся элементы матрицы A. Единичную матрицу E задаем в ячейках B7:D9. Начальное приближение для собственного числа X вводим в ячейку B11. Значения в ячейках B13:D15 вычисляются по формуле массива =B3:D5- B11*B7:D9. Это соответствует вычислению матрицы A — XE. Собственное число X выбираем из условия обращения в нуль детерминанта этой матицы.

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

С» минимальному значению гИзменяя ячейки: |$В$11 Предположить -Ограничения: А Добавить Изменить 1 Удалить Параметры |

Рис. 6.3. Настройки окна Поиск решения для вычисления собственного

Рис. 6.4. Результат вычисления собственного числа матрицы

Найдено решение 1.41421350658554, что, очевидно, соответствует собственному числу X = V2. Аналогично находятся два других решения: собственное число получаем при использовании в качестве начального приближения, на-пример, значения -1, а собственное число X = 5 при начальном приближении, по крайней мере, большем 4.

Рис. 6.5. Вычисление собственных чисел аналитически

Если все же принципиальным является вычисление всех собственных чисел, разумно прибегнуть к помощи надстройки Maple Excel Add-in (загрузка этой надстройки описывалась в гл. 5). Документ, в котором с помощью этой надстройки аналитически вычисляются собственные числа рассмотренной выше матрицы, показан на рис. 6.5.

Что касается результата, то он представлен в виде списка из трех элементов, каждый из которых является символьным представлением значения собственного числа. Формат вывода определяется правилами командного языка Maple, в котором, в частности, оператор Л означает возведение в степень (см. рис. 6.5).

В результате откроется диалоговое окно настройки параметров Maple Excel Options, представленное на рис. 6.7.

Рис. 6.7. Выбор на вкладке Packages в окне настройки параметров Maple Excel Options подключаемого пакета

Окно содержит три вкладки. Следует щелкнуть на вкладке Packages. На этой вкладке имеется список с доступными для подключения пакетами. При выборе пакета внизу под списком отображаются функции этого пакета. Необходимо найти и выбрать пакет с названием Linear Algebra (рис. 6.8).

Рис. 6.9. Подключение пакета подтверждается с помощью кнопки Accept

Рис. 6.10. Собственные числа найдены в числовом виде

Кроме того, окно аргументов можно вызвать, перейдя во вкладку «Формулы». На ленте следует нажать кнопку «Математические», расположенную в блоке инструментов «Библиотека функций». В открывшемся списке доступных элементов нужно выбрать «СТЕПЕНЬ». После этого запустится окно аргументов этой функции.

Как возвести матрицу в степень в excel

Для каждого числа а≠0 существует обратное число а -1 , и для квадратных матриц вводится аналогичное понятие. Обратные матрицы обычно используются для решения систем уравнений с несколькими неизвестными.