Как сделать систему в экселе
Добавил пользователь Алексей Ф. Обновлено: 04.10.2024
Приближенно найти корни некоторых систем из двух нелинейных уравнений можно графическим способом.
Для того, чтобы графически решить систему из двух уравнений c двумя неизвестными, представленными в виде
нужно выполнить следующие действия:
1. Привести уравнения системы к виду y=φ(x):
2. Создать последовательность значений аргумента x в заданном диапазоне.
3. Рассчитать значения функций для каждого значения аргумента (табулировать функции).
4. По полученным табличным значениям построить графики функций f1(x) и f2(x) .
5. Найти точку пересечения построенных графиков, навести указатель мыши на точку пересечения - появится всплывающая надпись с указанием искомых (приближенных) значений координат точки пересечения.
Заметим, что точность вычисления корней при графическом методе решения определяется величиной шага последовательности х . Если, например, шаг равен 0,2, то абсолютная погрешность вычисления равна +/- 0,2.
Технологию графического решения систем уравнений рассмотрим на примере.
Пример . Найти графически приближенное решение системы :
в диапазоне [0,2 - 3] с шагом 0,2.
- представим приведенные уравнения в виде:
- табулируем функции f1(x) и f2(x) в заданном диапазоне х 1 и с указанным шагом (рис. 1);
- по данным полученной таблицы построим график (рис. 1);
- подведем указатель мыши к узловой точке, которая расположена ближе других к точке пересечения графиков функций – отобразятся координаты точки пересечения с точностью, определяемой шагом табуляции (0,8; -0,22).
На графике видно, что система имеет только одно решение.
Таким образом, приближенное решение системы получено. Если подставить полученное решения в уравнения, то их правые части не обращаются в ноль. Это говорит о том, что решения только очень приблизительные.
Рассмотренный метод имеет, по крайней мере два недостатка:
- можно решать только системы из двух уравнений;
- решение получается неточным с абсолютной погрешностью (+/-) шаг табуляции.
Решение систем нелинейных уравнений с заданной относительной погрешностью
Рассмотрим технологию решения систем нелинейных уравнений на примере.
Пример 1. Требуется найти решение выше приведенной системы с относительной погрешностью 0,000001.
Решение.
Для решения системы применим надстройку Excel Поиск решения. Для этого:
1. На рабочем листе создадим модель для вычисления как на рис. 2.
В качестве целевой функции будем использовать первое уравнение системы, а в качестве функции ограничения - второе уравнение.
Понятно, что целевая функция в результате решения должна принять значение ноль, а второе уравнение также должно принять значение ноль.
2. Включим инструмент Поиск решения ( Данные> Анализ > Поиск решения ).
3. В диалоговом окне Параметры поиска решения установим опции:
- Оптимизировать целевую функцию - ссылка на ячейку, где записано первое уравнение;
- До Значени е=0;
- изменяя значения ячеек - ссылка на диапазон, где будет сохраняться решение ;
- Ограничения - ссылка на ячейку, содержащую второе уравнение, она должна принимать значение ноль (рис. 3).
4. В диалоговом окне Параметры поиска решения кликнем на кнопке Параметры и в открывшемся окне Параметры укажем погрешность вычисления (рис. 4), кликнем на кнопке ОК..
6. В диалоговом окне Параметры поиска решения кликнем на кнопке Найти решение. На рабочем листе отобразится результат (рис. 5)
Обратим внимание, что значения, принятые первым и вторым уравнением системы близки к нулевым значениям с заданной погрешностью вычислений. Значит решение верное и оно единственное.
Пример 2 . Требуется найти хотя бы одно решение системы двух нелинейных уравнений с относительной погрешностью |ε |
Excel — это программный продукт корпорации Microsoft, предназначенный для работы с электронными таблицами.
Введение
Программный продукт MS Excel считается отличным инструментальным набором, позволяющим выполнять обработку и анализ больших информационным объёмов, а, помимо этого, осуществлять несложные вычислительные операции и даже решать системы уравнений. Основой великолепных достоинств приложения Excel может считаться использование сеточной совокупности ячеек, в которые могут быть записаны информационные данные в виде чисел, текста, и даже формул. Для начала работы следует выполнить запись информации в ячейки и её группировку в виде столбцов и строк. Далее имеется возможность исполнения различных вычислительных операций с данными, их сортировки и фильтрации, а также использования табличного формата данных и формирования профессиональных диаграмм.
Решение системы уравнений в Microsoft Excel
Способность выполнять решение системы уравнений может оказаться полезной не только пользователям, проходящим обучение в разных учебных заведениях, но и при решении многих практических задач. При этом, ещё далеко не всем пользователям персональных компьютеров известно, что в программе MS Excel имеется собственный набор методов для решения линейных уравнений. Каждое уравнение считается решённым лишь в том случае, когда станут известны его корни. Программа MS Excel обладает целым набором способов отыскания корней.
Наиболее известным способом решения системы линейных уравнений при помощи инструментов программы Excel является использование матричной методики. Она состоит в формировании матрицы, состоящей из коэффициентов выражений, а далее в переходе к обратной матрице. Покажем использование данного метода на примере решения приведённой ниже системы уравнений:
Самый распространенный способ решения системы линейных уравнений инструментами Excel – это применение матричного метода. Он заключается в построении матрицы из коэффициентов выражений, а затем в создании обратной матрицы. Попробуем использовать данный метод для решения следующей системы уравнений:
Готовые работы на аналогичную тему
$14X_1 + 2X_2 + 8X_4 = 218$
$7X_1 - 3X_2 + 5X_3 +12X_4 = 213$
$5X_1 + X2 - 2X_3 + 4X_4 = 83$
$6X_1 + 2X_2 + X_3 - 3X_4 = 21$
Сначала надо заполнить матрицу числами, являющимися коэффициентами уравнения. Эти числа следует расположить в последовательном порядке, учитывая расположение всех корней, которым они соответствуют. Когда в каком-либо выражении одного из корней нет, тогда коэффициент приравнивается к нулю. Когда коэффициент не имеет обозначения в уравнении, а корень, соответствующий ему, есть, то тогда коэффициент принимается равным единице. Введём обозначение сформированной таблицы, как вектор А.
Рисунок 1. Окно программы. Автор24 — интернет-биржа студенческих работ
Рисунок 2. Окно программы. Автор24 — интернет-биржа студенческих работ
Затем, чтобы найти корни уравнения, следует определить матрицу, которая является обратной существующей. Для выполнения такой задачи в программе Excel предусмотрен специальный оператор, который именуется как МОБР. Его синтаксис достаточно прост:
Рисунок 3. Окно программы. Автор24 — интернет-биржа студенческих работ
Итогом этой операции станет отображение в заранее назначенной ячейке корней уравнения, то есть, это $Х_1, Х_2, Х_3 и Х_4$. Они будут располагаться в последовательном порядке, то есть, поставленная задача полностью решена:
Рисунок 4. Окно программы. Автор24 — интернет-биржа студенческих работ
Для проверки правильности решения можно выполнить подстановку в исходную систему уравнений полученные ответы вместо переменных Х. В случае, когда равенство выполняется, можно уверено сказать, что заданная система уравнений решена правильно.
Известен и ещё один метод решения системы уравнений в программе Excel. Суть его заключается в использовании способа, основанного на подборе параметров, то есть, в выполнении поиска от обратного.
Microsoft Office Excel может здорово помогать студентам и магистрантам в решении различных задач из высшей математики. Не многие пользователи знают, что базовые математические методы поиска неизвестных значений в системе уравнений реализованы в редакторе. Сегодня рассмотрим, как происходит решение уравнений в excel.
Первый метод
1. Зададимся простым квадратичным уравнением и найдем решение при х=0.
2. Переходите к инструменту и заполняете все необходимые поля
3. После проведения вычислений программа выдаст результат в ячейке с иксом.
4. Подставив полученное значение в исходное уравнение можно проверить правильность решения.
Второй метод
Используем графическое решение этого же уравнения. Суть заключается в том, что создается массив переменных и массив значений, полученных при решении выражения. Основываясь на этих данных, строится график. Место пересечения кривой с горизонтальной осью и будет неизвестной переменной.
1. Создаете два диапазона.
На заметку! Смена знака результата говорит о том, что решение находится в промежутке между этими двумя переменными.
2. Переходите во вкладку Вставка и выбираете обычный график.
3. Выбираете данные из столбца f (x), а в качестве подписи горизонтальной оси – значения иксов.
Важно! В настройках оси поставьте положение по делениям.
4. Теперь на графике четко видно, что решение находится между семеркой и восьмеркой ближе к семи. Чтобы узнать более точное значение, необходимо изменять масштаб оси и уточнять цифры в исходных массивах.
Такая исследовательская методика в первом приближении является достаточно грубой, однако позволяет увидеть поведение кривой при изменении неизвестных.
Третий метод
Решение систем уравнений можно проводить матричным методом. Для этого в редакторе есть отдельная функция МОБР. Суть заключается в том, что создаются два диапазона: в один выписываются аргументы при неизвестных, а во второй – значения в правой стороне выражения. Массив аргументов трансформируется в обратную матрицу, которая потом умножается на цифры после знака равно. Рассмотрим подробнее.
1. Записываете произвольную систему уравнений.
2. Отдельно выписываете аргументы при неизвестных в каждую ячейку. Если нет какого-то из иксов – ставите ноль. Аналогично поступаете с цифрами после знака равно.
3. Выделяете в свободной зоне диапазон ячеек равный размеру матрицы. В строке формул пишете МОБР и выбираете массив аргументов. Чтобы функция сработала корректно нажимаете одновременно Ctrl+Shift+Enter.
4. Теперь находите решение при помощи функции МУМНОЖ. Также предварительно выделяете диапазон размером с матрицу результатов и нажимаете уже известное сочетание клавиш.
Четвертый метод
Методом Гаусса можно решить практически любую систему уравнений. Суть в том, чтобы пошагово отнять одно уравнение из другого умножив их на отношение первых коэффициентов. Это прямая последовательность. Для полного решения необходимо еще провести обратное вычисление до тех пор, пока диагональ матрицы не станет единичной, а остальные элементы – нулевыми. Полученные значения в последнем столбце и являются искомыми неизвестными. Рассмотрим на примере.
Важно! Если первый аргумент является нулевым, то необходимо поменять строки местами.
1. Зададимся произвольной системой уравнений и выпишем все коэффициенты в отдельный массив.
2. Копируете первую строку в другое место, а ниже записываете формулу следующего вида: =C67:F67-$C$66:$F$66*(C67/$C$66).
Поскольку работа идет с массивами, нажимайте Ctrl+Shift+Enter, вместо Enter.
3. Маркером автозаполнения копируете формулу в нижнюю строку.
4. Выделяете две первые строчки нового массива и копируете их в другое место, вставив только значения.
5. Повторяете операцию для третьей строки, используя формулу
=C73:F73-$C$72:$F$72*(D73/$D$72). На этом прямая последовательность решения закончена.
6. Теперь необходимо пройти систему в обратном порядке. Используйте формулу для третьей строчки следующего вида =(C78:F78)/E78
7. Для следующей строки используйте формулу =(C77:F77-C84:F84*E77)/D77
8. В конце записываете вот такое выражение =(C76:F76-C83:F83*D76-C84:F84*E76)/C76
9. При получении матрицы с единичной диагональю, правая часть дает искомые неизвестные. После подстановки полученных цифр в любое из уравнений значения по обе стороны от знака равно являются идентичными, что говорит о правильном решении.
Метод Гаусса является одним из самых трудоемких среди прочих вариантов, однако позволяет пошагово просмотреть процесс поиска неизвестных.
Как видите, существует несколько методов решения уравнений в редакторе. Однако каждый из них требует определенных знаний в математике и четкого понимания последовательности действий. Однако для упрощения можно воспользоваться онлайн калькулятором, в который заложен определенный метод решения системы уравнений. Более продвинутые сайты предоставляют несколько способов поиска неизвестных.
Если дано уравнение:
A*X = B, где A - квадратная матрица, X,B - вектора;
причем B - известный вектор (т е столбец чисел), X - неизвестный вектор,
то решение X можно записать в виде:
X = A -1 *B, где A -1 - обратная от А матрица.
В MS Excel обратная матрица вычисляется функцией МОБР(), а перемножаются матрицы (или матрица на вектор) - функцией МУМНОЖ().
Имеются "тонкости" использования этих матричных действий в Excel. Так, чтобы вычислить обратную матрицу от матрицы А, нужно: Чтобы умножить матрицу на вектор: Есть и другой спососб, при котором используется кнопка построителя функции Excel.
Пример СЛАУ 4-го порядка
2. Метод Гаусса
Метод Гаусса подробно (по шагам) выполняется только в учебных целях, когда нужно показать, что Вы это умеете. А чтобы решить реальную СЛАУ, лучше применить в Excel метод обратной матрицы или воспользоваться специальными программами, например, этой
Краткое описание.
- Решаю систему уравнений: A*X=B, где A - квадратная матрица n-го порядка, X,B - вектора
- К матрице A справа приписываю вектор B. Получаю расширенную матрицу A
- В дальнейшем A обозначает расширенную матрицу (n строк, n+1 столбец)
- Aij - обозначает элемент матрицы, находящийся на i-й строке и j-м столбце
- Делю 1-ю строку на A11, т е A'1j = A1j/A11 (j = 1..n+1). В результате A'11 = 1. A' обозначает преобразованную строку
- Преобразую остальные строки по формуле: A'ij = Aij - A'1j*Ai1 (i = 2..n; j = 1..n+1)
- В результате 1-й столбец в строках 2..n заполнится нулями
- Отметим, что все эти преобразования не нарушают правильность уравнений
- Аналогичные действия проводим для обнуления 2-го столбца в строках 3..n, то есть:
- Делю 2-ю строку на A'22, т е A''2j = A'2j/A'22 (j = 2..n+1). В результате A''22 = 1. A'' обозначает резельтат 2-го преобразования строки
- Преобразую остальные строки по формуле: A''ij = A'ij - A''2j*A'i2 (i = 3..n; j = 2..n+1)
- В результате 2-й столбец в строках 3..n заполнится нулями
- Аналогичные действия проводим далее
- В результате левые n столбцов матрицы A превращаютс в верхнюю треугольную матрицу, т е ниже главной диагонали находятся только нули (а на главной диагонали - единицы) - см Рис 1. На этом рисунке вектор B - слева, S - номер шага
- Затем выполняется "обратный ход", начиная с нижней строки, из которой можно вычислить Xn = Bn/Ann, например: Х4 = 9,55741/68,6388 = 0,13924 (рис. 1)
- Затем можно вычислить X3 = (0,9065 - 2,40919*0,13924) = 0,57059
- Затем из второй строки: X2 + 2,83562*X3 + 8,17808*X4 = 2,47945 вычисляю X2, и т д
3. Метод Якоби (метод простых итераций)
Для применения метода Якоби (и метода Зейделя) необходимо, чтобы диагональные компоненты матрицы А были больше суммы остальных компонент той же строки. Заданная система не обладает таким свойством, поэтому выполняю предварительные преобразования.
Далее номер в скобках означает номер строки. Новую первую строку получаю сложением старой первой строки с другими строками, умноженными на специально подобранные коэффициенты. Записываю это в виде формулы:
Для применения метода Якоби систему уравнений нужно преобразовать к виду:
X = B2 + A2*X Преобразую:
Далее делю каждую строку на множитель левого столбца, то есть на 16, 7, 3, 70 соответственно. Тогда матрица А2 имеет вид :
Читайте также: