Как решить систему уравнений в excel

Решение уравнений методом подбора параметров Excel

Инструмент «Подбор параметра» применяется в ситуации, когда известен результат, но неизвестны аргументы. Excel подбирает значения до тех пор, пока вычисление не даст нужный итог.

Путь к команде: «Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра».

Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:

Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» — ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое нужно получить. В графе «Изменяя значение ячейки» — В1. Здесь должен отобразиться отобранный параметр.
После нажатия ОК отобразится результат подбора. Если нужно его сохранить, вновь нажимаем ОК. В противном случае – «Отмена».

Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».

Актуальные примеры решения

Единственность арифметических действий с системой при её совместимости обеспечивает условие неравенства нулю основного определителя. Но если сумма точек, которые были возведены в квадрат, строго положительна, то полученный СЛАУ будет несовместим с квадратной матрицей. Такая ситуация может произойти тогда, когда минимум один из присутствующих элементов deti отличён от нуля.

В качестве примера можно рассмотреть задачу, по условиям которой необходимо решить трёхмерную систему ЛАУ, используя для этого формулы Крамера:

x1 + 2 x2 + 4 x3 = 31.
5 x1 + x2 + 2 x3 = 29.
3 x1 — x2 + x3 =10.

Для решения следует выписать матрицу системы построчно. Строку матрицы принято обозначать символом i. После этого можно получить формулу A1=(1 2 4), A2=(5 1 2), A3=(3 -1 1). Существование значения b = (31 29 10) помогает отобразить столбец свободных коэффициентов. Основной определитель Det будет соответствовать следующим данным: a11 a22 a33 + a12 a23 a31 + a31 a21 a32 — a13 a22 a31 — a11 a32 a23 — a33 a21 a12 = 1—20 + 12 — 12 + 2—10 = -27.

В соответствии с формулой Крамера можно найти: x1 = -81/(-27) = 3, x2 = -108/(-27) = 4, x3 = -135/(-27) = 5. Если всё сделать правильно, то можно получить следующий ответ: x° = (3,4,5). Если руководствоваться базовыми понятиями, то многочисленные средства Крамера для решения сложных линейных уравнений можно использовать опосредованно.

Нелишним также будет рассмотреть следующий пример, где ученику нужно определить то, при каких показателях параметра F неравенство формулы | F x — y — 4|+|x + F y + 4|<=0 будет иметь ровно одно логическое решение. В силу определения модуля функции представленное неравенство может быть выполнено только в том случае, если оба выражения равны нулю. Именно поэтому рассматриваемая задача сводится только к нахождению решения линейной системы алгебраических уравнений. Соблюдаемый принцип действий должен соответствовать двум следующим формулам:

F x — y = 4.
x + F y = -4.

Это условие выполняется абсолютно для всех действительных значений параметра F. Стоит отметить, что к математическим задачам этого типа могут быть сведены многочисленные практические примеры из области физики, математики и даже химии.

Использование графических возможностей Excel для решения математических задач методическая разработка по алгебре на тему

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

Предварительный просмотр:

Использование графических возможностей Excel для решения математических задач

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

Построение графиков элементарных функций в Excel

Для построения графика функции в Excel прежде всего надо построить таблицу, в одну колонку которой занести значение аргумента функции, а в другую — значение функции при заданном значении аргумента.

Для этого в рабочем поле Excel в ячейках 1-й строки напечатаем наименование работы, во 2-ой строке – заголовок «Расчетная таблица», в 3-й – наименование колонок (столбцов) расчетной таблицы.

Начиная с ячейки А5 произведем формирование значение таблицы. Для этого необходимо в ячейку А5 ввести первое значение аргумента вычисляемой функции из заданного диапазона значений аргументов. В ячейку А6 введем второе значение аргумента, отличающееся от первого на заданный шаг изменения аргумента. Далее пометим эти ячейки и, ухватив указателем мыши квадратную точку в правом нижнем углу помеченной области ячеек, движением вниз по столбцу с нажатой левой кнопкой мыши рассчитаем значения аргумента с шагом, который вычислил Excel по указанным первым двум ячейкам (рис.1).

Пометив ячейку В5, вычисляем первое значении функции, используя Мастер формул, и если функция проста, то записываем формулу вручную. Запись формулы в ячейку вручную следует начать со знака «=» и закончить нажатием клавиши Enter. Затем, используя квадратную точку помеченной ячейки, копируем формулу в остальные ячейки.

Для построения графика заданной функции по построенной таким образом таблице необходимо воспользоваться Мастером диаграмм. Следуя указаниям Мастера, выбираем форму диаграммы Точечная.

Построение графика функции y=ax 2 +bx+c.

Построим график указанной функции при а-2, b=5, c=-10. Для построения графика функции будем изменять аргумент в диапазоне -5≤x≤2,5 с шагом 0,5.

Выполним последовательно все действия, описанные выше, сравнивая получаемый результат с рис.1.

Решение СЛУ методом Гаусса в Excel:

В тексте будет предлагаться ввести в диапазон ячеек формулу вида: {=A1:B3+$C$2:$C$3} и т.п., это так-называемые «формулы массива». Microsoft Excel автоматически заключает ее в фигурные скобки ( { } ). Для введения такого типа формул необходимо выделить весь диапазон, куда нужно вставить формулу, в первой ячейке ввести формулу без фигурных скобок (для примера выше – =A1:B3+$C$2:$C$3) и нажать Ctrl+Shift+Enter.
Пускай имеем систему линейных уравнений:

1.
Запишем коэффициенты системы уравнений в ячейки A1:D4 а столбец свободных членов в ячейки E1:E4. Если в ячейке A1 находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение. Для большей наглядности можно добавить заливку ячеек, в которых находятся свободные члены.

2. Необходимо коэффициент при x1 во всех уравнениях кроме первого привести к 0. Для начала сделаем это для второго уравнения. Скопируем первую строку в ячейки A6:E6 без изменений, в ячейки A7:E7 необходимо ввести формулу: {=A2:E2-$A$1:$E$1*(A2/$A$1)}. Таким образом мы от второй строки отнимаем первую, умноженную на A2/$A$1, т.е. отношение первых коэффициентов второго и первого уравнения. Для удобства заполнения строк 8 и 9 ссылки на ячейки первой строки необходимо использовать абсолютные (используем символ $).

3. Копируем введенную формулу формулу в строки 8 и 9, таким образом избавляемся от коэффициентов перед x1 во всех уравнениях кроме первого.

4. Теперь приведем коэффициенты перед x2 в третьем и четвертом уравнении к 0. Для этого скопируем полученные 6-ю и 7-ю строки (только значения) в строки 11 и 12, а в ячейки A13:E13 введем формулу {=A8:E8-$A$7:$E$7*(B8/$B$7)}, которую затем скопируем в ячейки A14:E14. Таким образом реализуется разность строк 8 и 7, умноженных на коэффициент B8/$B$7. Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби.

5. Осталось привести коэффициент при x3 в четвертом уравнении к 0, для этого вновь проделаем аналогичные действия: скопируем полученные 11, 12 и 13-ю строки (только значения) в строки 16-18, а в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(C14/$C$13)}. Таким образом реализуется разность строк 14 и 13, умноженных на коэффициент C14/$C$13. Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби.

6. Прямая прогонка методом Гаусса завершена. Обратную прогонку начнем с последней строки полученной матрицы. Необходимо все элементы последней строки разделить на коэффициент при x4. Для этого в строку 24 введем формулу {=A19:E19/D19}.

7. Приведем все строки к подобному виду, для этого заполним строки 23, 22, 21 следующими формулами:

23: {=(A18:E18-A24:E24*D18)/C18} – отнимаем от третьей строки четвертую умноженную на коэффициент при x4 третьей строки.

22: {=(A17:E17-A23:E23*C17-A24:E24*D17)/B17} – от второй строки отнимаем третью и четвертую, умноженные на соответствующие коэффициенты.

21: {=(A16:E16-A22:E22*B16-A23:E23*C16-A24:E24*D16)/A16} – от первой строки отнимаем вторую, третью и четвертую, умноженные на соответствующие коэффициенты.

Результат (корни уравнения) вычислены в ячейках E21:E24.

Составитель: Салий Н.А.

Метод Гаусса

Процесс решения по методу Гаусса состоит из двух этапов.

1. Прямой ход:
система приводится к ступенчатому (в частности, треугольному) виду.

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

2. Обратный ход:
идет последовательное определение неизвестных из этой ступенчатой системы.

Пример.
Установить совместность и решить системуРешение.
Прямой ход:
Выпишем расширенную матрицу системы и поменяем местами первую и вторую строки для того, чтобы элемент равнялся единице (так удобнее производить преобразования матрицы)..

Имеем Ранги матрицы системы и ее расширенной матрицы совпали с числом неизвестных. Согласно теореме Кронекера-Капелли система уравнений совместна и решение ее единственно.Обратный ход:
Выпишем систему уравнений, расширенную матрицу которой мы получили в результате преобразований:
Итак, имеем .
Далее, подставляя в третье уравнение, найдем .
Подставляя и во второе уравнение, получим .
Подставляя в первое уравнение найденные получим .
Таким образом, имеем решение системы .

Метод Гаусса

Процесс решения по методу Гаусса состоит из двух этапов.

1. Прямой ход:
система приводится к ступенчатому (в частности, треугольному) виду.

2. Обратный ход:
идет последовательное определение неизвестных из этой ступенчатой системы.

Метод Гаусса

Процесс решения по методу Гаусса состоит из двух этапов.

1. Прямой ход:
система приводится к ступенчатому (в частности, треугольному) виду.

2. Обратный ход:
идет последовательное определение неизвестных из этой ступенчатой системы.

Решение СЛУ методом Гаусса в Excel:

В тексте будет предлагаться ввести в диапазон ячеек формулу вида: {=A1:B3+$C$2:$C$3} и т.п., это так-называемые «формулы массива». Microsoft Excel автоматически заключает ее в фигурные скобки ({ }). Для введения такого типа формул необходимо выделить весь диапазон, куда нужно вставить формулу, в первой ячейке ввести формулу без фигурных скобок (для примера выше – =A1:B3+$C$2:$C$3) и нажать Ctrl+Shift+Enter.
Пускай имеем систему линейных уравнений:

1.

Запишем коэффициенты системы уравнений в ячейки A1:D4 а столбец свободных членов в ячейки E1:E4. Если в ячейке
A1
находится 0, необходимо поменять строки местами так, чтоб в этой ячейке было отличное от ноля значение
. Для большей наглядности можно добавить заливку ячеек, в которых находятся свободные члены.

2.
Необходимо коэффициент при x1 во всех уравнениях кроме первого привести к 0. Для начала сделаем это для второго уравнения. Скопируем первую строку в ячейки A6:E6 без изменений, в ячейки A7:E7 необходимо ввести формулу: {=A2:E2-$A$1:$E$1*(A2/$A$1)}. Таким образом мы от второй строки отнимаем первую, умноженную на A2/$A$1, т.е. отношение первых коэффициентов второго и первого уравнения. Для удобства заполнения строк 8 и 9 ссылки на ячейки первой строки необходимо использовать абсолютные (используем символ $).

3.
Копируем введенную формулу формулу в строки 8 и 9, таким образом избавляемся от коэффициентов перед x1 во всех уравнениях кроме первого.

4.
Теперь приведем коэффициенты перед x2 в третьем и четвертом уравнении к 0. Для этого скопируем полученные 6-ю и 7-ю строки (только значения) в строки 11 и 12, а в ячейки A13:E13 введем формулу {=A8:E8-$A$7:$E$7*(B8/$B$7)}, которую затем скопируем в ячейки A14:E14. Таким образом реализуется разность строк 8 и 7, умноженных на коэффициент B8/$B$7. .

5.
Осталось привести коэффициент при x3 в четвертом уравнении к 0, для этого вновь проделаем аналогичные действия: скопируем полученные 11, 12 и 13-ю строки (только значения) в строки 16-18, а в ячейки A19:E19 введем формулу {=A14:E14-$A$13:$E$13*(C14/$C$13)}. Таким образом реализуется разность строк 14 и 13, умноженных на коэффициент C14/$C$13. Не забываем проводить перестановку строк, чтоб избавиться от 0 в знаменателе дроби
.

6.
Прямая прогонка методом Гаусса завершена. Обратную прогонку начнем с последней строки полученной матрицы. Необходимо все элементы последней строки разделить на коэффициент при x4. Для этого в строку 24 введем формулу {=A19:E19/D19}.

7.
Приведем все строки к подобному виду, для этого заполним строки 23, 22, 21 следующими формулами:

23: {=(A18:E18-A24:E24*D18)/C18} – отнимаем от третьей строки четвертую умноженную на коэффициент при x4 третьей строки.

Результат (корни уравнения) вычислены в ячейках E21:E24.

Составитель: Салий Н.А.

Метод Крамера основан на использовании определителей в решении систем линейных уравнений. Это значительно ускоряет процесс решения.

Метод Крамера может быть использован в решении системы стольких линейных уравнений,
сколько в каждом уравнении неизвестных. Если определитель системы не равен нулю,
то метод Крамера может быть использован в решении, если же равен нулю, то не может.
Кроме того, метод Крамера может быть использован в решении систем линейных уравнений,
имеющих единственное решение.

Определение
. Определитель, составленный из коэффициентов при неизвестных, называется определителем системы и обозначается (дельта).

Определители

получаются путём замены коэффициентов при соответствующих неизвестных свободными членами:

;

Теорема Крамера
. Если определитель системы отличен от нуля, то система линейных уравнений имеет одно единственное решение, причём неизвестное равно отношению определителей. В знаменателе – определитель системы, а в числителе – определитель, полученный из определителя системы путём замены коэффициентов при этом неизвестном свободными членами. Эта теорема имеет место для системы линейных уравнений любого порядка.

Пример 1.
Решить систему линейных уравнений:

Согласно теореме Крамера
имеем:

Итак, решение системы (2):

онлайн-калькулятором , решающим методом Крамера.

Примеры решений линейных уравнений по методу Крамера

Пример 2

Задание 2

Решить систему уравнений с помощью метода Крамера:

Решение

Находим определитель матрицы

Заменяем первый столбец

свободными членами и находим определитель

Найдем значение

Теперь заменим на свободные члены второй столбец матрицы и найдём определитель

для

Найдем

Ответ

Пример 3

Задание 3

С помощью метода Крамера решить систему уравнений:

Решение

Как и в предыдущих примерах, сначала находим общий определитель матрицы

Заменяем первый столбец свободными членами:

Найдем значение

согласно формуле:

Найдем определитель матрицы для

заменив на свободные члены второй столбец:

Найдем значение

Ответ

Пример 4

Задание 4

Решить систему уравнений методом Крамера:

Решение

Здесь видим матрицу 3х3, следовательно определитель матрицы находим методом треугольников:

Определитель не равен 0, а значит можем продолжать решение.

Замени первый столбец матрицы на свободные члены и найдем её определитель для

Таким образом, определим значение

Таким же способом получим определитель матрицы для

заменив на свободные члены второй столбец:

Найдем

Также заменим на свободные члены значения третьего столбца и получим определитель матрицы для

Найдем

Ответ

Пример 5

Задание 5

Решить методом Крамера систему уравнений:

Решение

Аналогично, как в предыдущем примере, найдём определитель матрицы

методом треугольников:

следовательно, можем продолжать.

Найдем определитель матрицы для

Заменяем коэффициенты первого столбца:

Найдем

Найдем определитель матрицы для

Проделаем то же самое, но заменив коэффициенты второго столбца.

Найдем значение

Найдем определитель матрицы для

заменив на свободные члены третий столбец:

Найдем значение

Ответ

Пример 6

Задание 6

Решить систему уравнений методом Крамера:

Решение

Здесь мы видим, что в строках отсутствуют некоторые перемененные. Преобразим вид системы уравнений в квадратный:

Таким образом, наша матрица будет следующего вида:

Найдем определитель матрицы:

Найдем определитель матрицы для

Найдем значение

Найдем определитель матрицы для

заменив на свободные члены второй столбец:

Найдем значение

Заменим третий столбец и найдем определитель матрицы для

Найдем

Ответ

Пример 7

Задание 7

С помощью метода Крамера решить систему уравнений:

Решение

Найдем определитель матрицы

Определитель

Это значит, что данную систему нельзя решить методом Крамера, и мы не можем продолжать решение согласно нашему алгоритму.

Ответ

Метод Крамера нельзя применить к данной системе линейных уравнений

Пример 8

Задание 8

Решить систему уравнений методом Крамера:

Решение

Здесь a – это некоторое реальное число.

Найдем общий определитель матрицы

Найдем определитель матрицы

Для этого подставим в первый столбец матрицы свободные члены системы уравнений.

Найдем значение

Таким же способом найдем определитель матрицы

Найдем

Ответ

Пример 9

Задание 9

Решить систему уравнений методом Крамера:

Решение

Найдем определитель матрицы:

Найдем определитель матрицы для

заменив на свободные члены первый столбец:

Найдем значение

Найдем определитель матрицы для

:, заменив на свободные члены второй столбец:

Найдем значение

Найдем определитель матрицы для

заменив на свободные члены третий столбец:

Найдем значение

Ответ

Пример 10

Задание 10

Решить систему уравнений методом Крамера:

Решение

Преобразим вид системы уравнений в квадратный. Для этого перенесём одну из переменных в свободные члены. Так как, количество строк в системе уравнений меньше, чем количество переменных, то значение одной из переменных будет с параметром. Следовательно, система может выглядеть так:

Таким образом, наша матрица будет следующего вида:

Найдем определитель матрицы:

Если значение определителя будет равно 0, то можно попробовать перенести в свободные члены другую переменную.

Найдем определитель матрицы для переменной

. Здесь заменяем первый столбец на получившуюся сумму свободных членов:

Найдем значение

Найдем определитель матрицы для переменной

тем же способом:

Найдем

Ответ

Решение системы с помощью обратной матрицы

Метод обратной матрицы – это, по существу, частный случай матричного уравнения (см. Пример №3 указанного урока).

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

Пример 11

Решить систему с матричным методом

Решение: Запишем систему в матричной форме: , где

Пожалуйста, посмотрите на систему уравнений и на матрицы. По какому принципу записываем элементы в матрицы, думаю, всем понятно. Единственный комментарий: если бы в уравнениях отсутствовали некоторые переменные, то на соответствующих местах в матрице нужно было бы поставить нули.

Решение системы найдем по формуле (её подробный вывод можно посмотреть в статье Матричные уравнения).

Согласно формуле нам нужно найти обратную матрицу и выполнить матричное умножение . Алгоритм нахождения обратной матрицы подробно разобран на уроке Как найти обратную матрицу?

Обратную матрицу найдем по формуле:, где – транспонированная матрица алгебраических дополнений соответствующих элементов матрицы .

Сначала разбираемся с определителем:

Здесь определитель раскрыт по первой строке.

Внимание! Если , то обратной матрицы не существует, и решить систему матричным методом невозможно. В этом случае система решается методом исключения неизвестных (методом Гаусса)

Теперь нужно вычислить 9 миноров и записать их в матрицу миноров

Справка: Полезно знать смысл двойных подстрочных индексов в линейной алгебре. Первая цифра – это номер строки, в которой находится данный элемент. Вторая цифра – это номер столбца, в котором находится данный элемент:
То есть, двойной подстрочный индекс указывает, что элемент находится в первой строке, третьем столбце, а, например, элемент находится в 3 строке, 2 столбце

В ходе решения расчет миноров лучше расписать подробно, хотя, при определенном опыте их можно приноровиться считать ~~с ошибками~~ устно.

Порядок расчета миноров совершенно не важен, здесь я их вычислил слева направо по строкам. Можно было рассчитать миноры по столбцам (это даже удобнее).

Таким образом:

– матрица миноров соответствующих элементов матрицы .

– матрица алгебраических дополнений.

– транспонированная матрица алгебраических дополнений.

Повторюсь, выполненные шаги мы подробно разбирали на уроке Как найти обратную матрицу?

Теперь записываем обратную матрицу:

Ни в коем случае не вносим в матрицу, это серьезно затруднит дальнейшие вычисления. Деление нужно было бы выполнить, если бы все числа матрицы делились на 60 без остатка. А вот внести минус в матрицу в данном случае очень даже нужно, это, наоборот – упростит дальнейшие вычисления.

Осталось провести матричное умножение. Умножать матрицы можно научиться на уроке Действия с матрицами. Кстати, там разобран точно такой же пример.

Обратите внимание, что деление на 60 выполняется в последнюю очередь. Иногда может и не разделиться нацело, т.е

могут получиться «плохие» дроби. Что в таких случаях делать, я уже рассказал, когда мы разбирали правило Крамера.

Ответ:

Пример 12

Решить систему с помощью обратной матрицы.

Это пример для самостоятельного решения (образец чистового оформления и ответ в конце урока).

Наиболее универсальным способом решения системы является метод исключения неизвестных (метод Гаусса). Доступно объяснить алгоритм не так-то просто, но я старался!.

Желаю успехов!

Ответы:

Пример 3:

Пример 6:

Пример 8: , . Вы можете посмотреть или скачать образец решения данного примера (ссылка ниже).

Примеры 10, 12:

(Переход на главную страницу)

Краткое описание

Широко востребованный метод Крамера активно используется специалистами для решения распространённых алгебраических уравнений (СЛАУ). Итоговая точность полученного результата обусловлена применением определённой математической матрицы, а также некоторыми вспомогательными ограничениями, которые неизбежно накладываются во время доказательства конкретной теоремы.

Набором выражений вида yr 2 x1+ yr 2 x2 +… yr n xn = b r при r =1, 2,…, m принято называть универсальную систему линейных алгебраических уравнений. В этом случае также присутствуют определённые коэффициенты, которые могут принадлежать множеству W -действительных чисел, от неизвестных x 1… xn.

Чаще всего в роли действенных чисел выступают yr и br. Каждое из представленных значений называется линейным уравнением. Элементарные коэффициенты при неизвестных — это yr, а вот bi — свободные коэффициенты уравнений. Стандартный n -мерный вектор k ° = (k 1°, k 2°,…, k n°) называют решением системы. При правильной подстановке в систему вместо неизвестных элементов каждая из строчек становится верным равенством.

Если у системы присутствует минимум одно решение, то она называется совместной. Речь касается несовместного примера только в том случае, если многочисленные алгоритмы решения совпадают с пустым множеством. Классическая формула Крамера используется в том случае, если необходимо отыскать верное решение для линейных уравнений. Для получения достоверного результата матрицы должны быть исключительно квадратными. А на практике такой подход означает одинаковое количество уравнений и неизвестных в системе.