Умение решать системы уравнений часто может принести пользу не только в учебе, но и на практике. В то же время, далеко не каждый пользователь ПК знает, что в Экселе существует собственные варианты решений линейных уравнений. Давайте узнаем, как с применением инструментария этого табличного процессора выполнить данную задачу различными способами.
Варианты решений
Любое уравнение может считаться решенным только тогда, когда будут отысканы его корни. В программе Excel существует несколько вариантов поиска корней. Давайте рассмотрим каждый из них.
Способ 1: матричный метод
Самый распространенный способ решения системы линейных уравнений инструментами Excel – это применение матричного метода. Он заключается в построении матрицы из коэффициентов выражений, а затем в создании обратной матрицы. Попробуем использовать данный метод для решения следующей системы уравнений:
14+2+8=218
7-3+5+12=213
5+-2+4=83
6+2+-3=21
- Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен . Обозначаем полученную таблицу, как вектор .
- Отдельно записываем значения после знака «равно». Обозначаем их общим наименованием, как вектор .
- Теперь для нахождения корней уравнения, прежде всего, нам нужно отыскать матрицу, обратную существующей. К счастью, в Эксель имеется специальный оператор, который предназначен для решения данной задачи. Называется он . Он имеет довольно простой синтаксис:
=МОБР(массив)
Аргумент — это, собственно, адрес исходной таблицы.
Итак, выделяем на листе область пустых ячеек, которая по размеру равна диапазону исходной матрицы. Щелкаем по кнопке , расположенную около строки формул.
- Выполняется запуск . Переходим в категорию . В представившемся списке ищем наименование . После того, как оно отыскано, выделяем его и жмем на кнопку .
- Запускается окно аргументов функции . Оно по числу аргументов имеет всего одно поле – . Тут нужно указать адрес нашей таблицы. Для этих целей устанавливаем курсор в это поле. Затем зажимаем левую кнопку мыши и выделяем область на листе, в которой находится матрица. Как видим, данные о координатах размещения автоматически заносятся в поле окна. После того, как эта задача выполнена, наиболее очевидным было бы нажать на кнопку , но не стоит торопиться. Дело в том, что нажатие на эту кнопку является равнозначным применению команды . Но при работе с массивами после завершения ввода формулы следует не кликать по кнопке , а произвести набор сочетания клавиш . Выполняем эту операцию.
- Итак, после этого программа производит вычисления и на выходе в предварительно выделенной области мы имеем матрицу, обратную данной.
- Теперь нам нужно будет умножить обратную матрицу на матрицу , которая состоит из одного столбца значений, расположенных после знака в выражениях. Для умножения таблиц в Экселе также имеется отдельная функция, которая называется . Данный оператор имеет следующий синтаксис:
=МУМНОЖ(Массив1;Массив2)
Выделяем диапазон, в нашем случае состоящий из четырех ячеек. Далее опять запускаем , нажав значок .
- В категории , запустившегося , выделяем наименование и жмем на кнопку .
- Активируется окно аргументов функции . В поле заносим координаты нашей обратной матрицы. Для этого, как и в прошлый раз, устанавливаем курсор в поле и с зажатой левой кнопкой мыши выделяем курсором соответствующую таблицу. Аналогичное действие проводим для внесения координат в поле , только на этот раз выделяем значения колонки . После того, как вышеуказанные действия проведены, опять не спешим жать на кнопку или клавишу , а набираем комбинацию клавиш .
- После данного действия в предварительно выделенной ячейке отобразятся корни уравнения: , , и . Они будут расположены последовательно. Таким образом, можно сказать, что мы решили данную систему. Для того, чтобы проверить правильность решения достаточно подставить в исходную систему выражений данные ответы вместо соответствующих корней. Если равенство будет соблюдено, то это означает, что представленная система уравнений решена верно.
Способ 2: подбор параметров
Второй известный способ решения системы уравнений в Экселе – это применение метода подбора параметров. Суть данного метода заключается в поиске от обратного. То есть, основываясь на известном результате, мы производим поиск неизвестного аргумента. Давайте для примера используем квадратное уравнение
3x^2+4x-132=0
- Принимаем значение за равное . Высчитываем соответствующее для него значение , применив следующую формулу:
=3*x^2+4*x-132
Вместо значения подставляем адрес той ячейки, где расположено число , принятое нами за .
- Переходим во вкладку . Жмем на кнопку . Эта кнопка размещена на ленте в блоке инструментов . Открывается выпадающий список. Выбираем в нем позицию .
- Запускается окно подбора параметров. Как видим, оно состоит из трех полей. В поле указываем адрес ячейки, в которой находится формула , рассчитанная нами чуть ранее. В поле вводим число . В поле указываем адрес ячейки, в которой расположено значение , ранее принятое нами за. После выполнения данных действий жмем на кнопку .
- После этого Эксель произведет вычисление с помощью подбора параметра. Об этом сообщит появившееся информационное окно. В нем следует нажать на кнопку .
- Результат вычисления корня уравнения будет находиться в той ячейке, которую мы назначили в поле . В нашем случае, как видим, будет равен.
Этот результат также можно проверить, подставив данное значение в решаемое выражение вместо значения .
Способ 3: метод Крамера
Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в :
14+2+8=218
7-3+5+12=213
5+-2+4=83
6+2+-3=21
- Как и в первом способе, составляем матрицу из коэффициентов уравнений и таблицу из значений, которые стоят после знака .
- Далее делаем ещё четыре таблицы. Каждая из них является копией матрицы , только у этих копий поочередно один столбец заменен на таблицу . У первой таблицы – это первый столбец, у второй таблицы – второй и т.д.
- Теперь нам нужно высчитать определители для всех этих таблиц. Система уравнений будет иметь решения только в том случае, если все определители будут иметь значение, отличное от нуля. Для расчета этого значения в Экселе опять имеется отдельная функция – . Синтаксис данного оператора следующий:
=МОПРЕД(массив)
Таким образом, как и у функции , единственным аргументом выступает ссылка на обрабатываемую таблицу.
Итак, выделяем ячейку, в которой будет выводиться определитель первой матрицы. Затем жмем на знакомую по предыдущим способам кнопку .
- Активируется окно . Переходим в категорию и среди списка операторов выделяем там наименование . После этого жмем на кнопку .
- Запускается окно аргументов функции . Как видим, оно имеет только одно поле – . В это поле вписываем адрес первой преобразованной матрицы. Для этого устанавливаем курсор в поле, а затем выделяем матричный диапазон. После этого жмем на кнопку . Данная функция выводит результат в одну ячейку, а не массивом, поэтому для получения расчета не нужно прибегать к нажатию комбинации клавиш .
- Функция производит подсчет результата и выводит его в заранее выделенную ячейку. Как видим, в нашем случае определитель равен , то есть, не является равным нулю, что нам подходит.
- Аналогичным образом производим подсчет определителей для остальных трех таблиц.
- На завершающем этапе производим подсчет определителя первичной матрицы. Процедура происходит все по тому же алгоритму. Как видим, определитель первичной таблицы тоже отличный от нуля, а значит, матрица считается невырожденной, то есть, система уравнений имеет решения.
- Теперь пора найти корни уравнения. Корень уравнения будет равен отношению определителя соответствующей преобразованной матрицы на определитель первичной таблицы. Таким образом, разделив поочередно все четыре определителя преобразованных матриц на число , которое является определителем первоначальной таблицы, мы получим четыре корня. Как видим, они равны значениям , , и . Таким образом, они в точности совпадают с корнями, которые мы нашли, используя обратную матрицу в , что подтверждает правильность решения системы уравнений.
Способ 4: метод Гаусса
Решить систему уравнений можно также, применив метод Гаусса. Для примера возьмем более простую систему уравнений из трех неизвестных:
14+2+8=110
7-3+5=32
5+-2=17
- Опять последовательно записываем коэффициенты в таблицу , а свободные члены, расположенные после знака — в таблицу . Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы значение было отличным от нуля. В обратном случае следует переставить строки местами.
- Копируем первую строку двух соединенных матриц в строчку ниже (для наглядности можно пропустить одну строку). В первую ячейку, которая расположена в строке ещё ниже предыдущей, вводим следующую формулу:
=B8:E8-$B$7:$E$7*(B8/$B$7)
Если вы расположили матрицы по-другому, то и адреса ячеек формулы у вас будут иметь другое значение, но вы сможете высчитать их, сопоставив с теми формулами и изображениями, которые приводятся здесь.
После того, как формула введена, выделите весь ряд ячеек и нажмите комбинацию клавиш . К ряду будет применена формула массива и он будет заполнен значениями. Таким образом мы произвели вычитание из второй строки первой, умноженной на отношение первых коэффициентов двух первых выражений системы.
- После этого копируем полученную строку и вставляем её в строчку ниже.
- Выделяем две первые строки после пропущенной строчки. Жмем на кнопку , которая расположена на ленте во вкладке .
- Пропускаем строку после последней записи на листе. Выделяем первую ячейку в следующей строке. Кликаем правой кнопкой мыши. В открывшемся контекстном меню наводим курсор на пункт . В запустившемся дополнительном списке выбираем позицию .
- В следующую строку вводим формулу массива. В ней производится вычитание из третьей строки предыдущей группы данных второй строки, умноженной на отношение второго коэффициента третьей и второй строки. В нашем случае формула будет иметь следующий вид:
=B13:E13-$B$12:$E$12*(C13/$C$12)
После ввода формулы выделяем весь ряд и применяем сочетание клавиш .
- Теперь следует выполнить обратную прогонку по методу Гаусса. Пропускаем три строки от последней записи. В четвертой строке вводим формулу массива:
=B17:E17/D17
Таким образом, мы делим последнюю рассчитанную нами строку на её же третий коэффициент. После того, как набрали формулу, выделяем всю строчку и жмем сочетание клавиш .
- Поднимаемся на строку вверх и вводим в неё следующую формулу массива:
=(B16:E16-B21:E21*D16)/C16
Жмем привычное уже нам сочетание клавиш для применения формулы массива.
- Поднимаемся ещё на одну строку выше. В неё вводим формулу массива следующего вида:
=(B15:E15-B20:E20*C15-B21:E21*D15)/B15
Опять выделяем всю строку и применяем сочетание клавиш .
- Теперь смотрим на числа, которые получились в последнем столбце последнего блока строк, рассчитанного нами ранее. Именно эти числа (, и ) будут являться корнями данной системы уравнений. Проверить это можно, подставив их вместо значений , и в выражения.
Как видим, в Экселе систему уравнений можно решить целым рядом способов, каждый из которых имеет собственные преимущества и недостатки. Но все эти методы можно условно разделить на две большие группы: матричные и с применением инструмента подбора параметров. В некоторых случаях не всегда матричные методы подходят для решения задачи. В частности тогда, когда определитель матрицы равен нулю. В остальных же случаях пользователь сам волен решать, какой вариант он считает более удобным для себя.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.