Решение системы линейных уравнений тремя способами в Excel
Задание № 6 (Excel)
Решение системы линейных уравнений тремя способами.
|
Решить систему линейных уравнений:
На рис. 1 приведены все три способа решения этой системы уравнений. Аналогичным образом
следует решить эту систему во время выполнения этого задания, повторив оформление решения.
Способ 1. Решение по формулам Крамера.
Ввод текстовой информации и его оформление не должно вызвать затруднений. Технология получения решения и запись уравнений следующие:
1. Для каждого
коэффициента уравнения, записанного в ячейки А3:С5 задайте пользовательский
формат числа.
2. В ячейке F3 по формуле =МОПРЕД(A3:C5) рассчитайте определитель D и такое же имя присвойте этой ячейке.
3. В ячейке F7 по формуле =МОПРЕД(A7:C9) рассчитайте определитель DX и такое же имя присвойте этой ячейке. DX получается заменой первого столбца в матрице D на столбец свободных членов.
4. Аналогично п. 3 рассчитайте определители DY
и DZ и результирующим ячейкам присвойте имена определителей.
5. Искомые величины X, Y, Z в столбце H определите по формулам, приведенным слева от них.
6. В ячейках G18, G19, G20 делаем проверку, умножая найденные величины X, Y, Z на их коэффициенты в каждом уравнении.
Способ 2. Решение системы уравнений с помощью команды Сервис ] Поиск решения.
1. Ввести в массив J4:L6 исходные данные – коэффициенты при неизвестных.
2. В массиве М4:М6 вычисляются левые части уравнений с приближенными значениями корней уравнений, равными 0, которые задать в ячейки J8, K8, L8. Достаточно вычислить левую часть первого уравнения, и заполнить введенную формулу в остальные две ячейки. (Не забыть ячейки корней в формулах сделать абсолютными!).
3. В массив N3:N6 ввести свободные члены уравнений.
4. Выполнить команду Сервис ] Поиск решения и в диалоговом окне Поиск решения выполнить следующие
настройки.
5. В текстовом поле Изменяя ячейки ввести ячейки $J$8:$L$8, в которых заданы нулевые значения корней.
6. Нажав кнопку Добавить, введите в поле Ссылка на ячейку выражение: $M$4:$M$6, в следующее поле – знак равняется “=”, в текстовое поле Ограничения – выражение $N$4:$N$6 и нажмите кнопку Выполнить (см. рис. 1).
8. Решение систем линейных уравнений в excel
Сначала рассмотрим решение системы линейных уравнений методом Крамера. Для этого используем уже решенный пример 9.
В EXCEL реализована функция вычисления определителей (см. п.7). Запишем матрицу коэффициентов и матрицы, полученные из нее заменой по очереди всех столбцов на столбец свободных членов. Листинг вычислений представлен на рис. 8:
Рис.
8
Матрицы записаны в диапазонах
, а значения определителей – в ячейках . Столбец свободных членов – в G2:G6. Решение системы – в I2:I6.
Тот же пример решим с помощью обратной матрицы. В EXCEL реализованы функции для нахождения обратных матриц и перемножения матриц (см. п.7). Листинг решения представлен на рис. 9. В диапазоне записана матрица коэффициентов, в ячейках – вектор свободных членов, в диапазоне обратная матрица, в ячейках – решение системы, полеченное как результат умножения матрицы на матрицу .
Рис. 9
Предложим
еще один способ решения линейных систем
в EXCELL.
Возможно, для систем он не покажется
эффективным, однако знакомство с ним
полезно для решения задач оптимизации,
в частности задач линейного программирования.
Инструментом для этого метода служит
процедура Поиск
решения, которая
находится в Надстройках.
После вызова процедуры появляется окно,
представленное на рис. 11.
Покажем решение системы на примере.
►Пример 16. Решить систему
Рис. 10
В
ячейки введена матрица коэффициентов
уравнений системы, в – коэффициенты последнего уравнения,
в ячейки G3:G6
— столбец свободных членов. Ячейки B1:E1
отведем для значений неизвестных. В
ячейках F3:F6
сосчитаем сумму произведений коэффициентов
каждого уравнения на неизвестные (для
этого воспользуемся встроенной функцией
СУММПРОИЗВ). Выберем ячейку F6
в качестве целевой и вызовем процедуру Поиск
решения.
В окошке установим, что целевая ячейка
должна быть равной свободному члену последнего уравнения,
и заполним поля. В поле «изменяя
ячейки» введем B1:E1.
В поле «ограничения» будем вводить первые
уравнения. А именно, значение в ячейке
F3
должно равняться заданному значению в
ячейке G3
(1-е уравнение).
Аналогично добавляем
два других уравнения. После заполнения
всех полей нажимаем .
Рис. 11
1. Курош А.Г. Курс высшей алгебры. – М.: Наука, 1977.
2. Фаддеев Д.К., Соминский И.С. Задачи по высшей алгебре. – СПб.: Издательство «Лань», 1998.
3. Краснов М.Л., Киселев А.И., Макаренко Г.И., Шикин Е.В., Заляпин В.И., Соболев С.К. Вся высшая математика: Учебник. Т. 1. – М.: Эдиториал УРСС, 2000.
4. Данко П.Е, Попов А.Г., Кожевникова Т.Я. Высшая математика в упражнениях и задачах. — М.: Высшая школа, 1999. Ч.1.- 304 с. — Ч.2. — 416 с.
5. Фридман Г. Н., Леора С.Н. Математика & Mathematica. Избранные задачи для избранных студентов. – Невский Диалект, БХВ-Петербург , 2010, 299 с.
6. Пащенко И.Г. Excel 2007. -М.: Эксмо, 2009. -496 с.
Введение 3
1. Матрицы и действия с матрицами 4
1.1. Основные понятия. 4
1.
2.
Действия с матрицами. 5
2. Определители и их свойства 9
3. Обратная матрица. Решение матричных уравнений 14
4. Ранг матрицы 17
5. Системы линейных уравнений 18
5.1 Основные понятия. 18
5.2. Решение систем по формулам Крамера. 20
5.3. Решение системы с помощью обратной матрицы. 22
5.4. Исследование систем линейных уравнений. Метод Гаусса 23
5.5. Однородные системы. 28
6. Собственные значения и собственные векторы матрицы 30
7. Действия с матрицами на компьютере в EXCEL 33
Литература 40
Решение систем уравнений в Excel — от разработчиков Microsoft Excel Solver
Если вы хотите найти численных решения для отдельных уравнений или систем уравнений — или неравенств — и Microsoft Excel является знакомым или продуктивный инструмент для вас, вы пришли в нужное место! Вы можете использовать Solver , встроенный компонент Excel, который Frontline Systems изначально разработал для Microsoft .
Что делать, если вам нужно решить системы из более чем 200 уравнений или неравенств — предел встроенного Решателя? Вы можете легко перейти на Premium Solver — это 100% , совместимый с и выше из Excel Solver. Ваши модели Солвера и даже код VBA, управляющий Солвером, будут работать как есть . Premium Solver обрабатывает до 400 нелинейных уравнений и до 1000 линейных уравнений.
С Premium Solver Platform — дальнейшим обновлением Premium Solver — вы можете обрабатывать до 500 нелинейных уравнений и до 8000 линейных уравнений! Платформа Premium Solver включает в себя наши Interval Global Solver , который может сделать больше с нелинейными системами: При наличии достаточного времени он может изолировать все действительные решения системы из гладких нелинейных уравнений — это выходит за рамки возможностей большинства других программ!
Продукты Premium Solver не только решают уравнения — они также находят оптимальных решения задач с переменными решения , ограничениями , которые могут быть уравнениями или неравенствами, и цель максимизировать или минимизировать.
И они решают весь спектр задач оптимизации, от линейного программирования и гладкой нелинейной оптимизации до негладких моделей с использованием произвольных функций Excel, решаемых с помощью методов генетических алгоритмов.
Если вы зарегистрируетесь на Solver.com (это бесплатно), вы сможете:
- Загрузить бесплатную 15-дневную пробную версию любого из наших продуктов Excel Solver
- Загрузить наши руководства пользователя Solver — узнайте больше о наших продуктах Excel и о том, как создавать лучшие модели оптимизации
- Доступ к «защищенным» страницам технической поддержки и загружаемым примерам моделей
P. S. Вам не нужно быть экспертом в уравнениях -решение или другие математические методы. Уже более десяти лет тысячи пользователей в компаниях из списка Global 2000 пользуются нашим передовым программным обеспечением и компетентной технической поддержкой для создания и решения более масштабных и более качественных задач.
модели 0064.
Premium Solver Platform
Нашим самым продаваемым продуктом для решения традиционных уравнений и оптимизации является Premium Solver Platform , отчасти потому, что он может обрабатывать все типы задач до определенных пределов размера:
- Линейные и квадратичные задачи до 8000 переменных
- конические и смешанно-целочисленные задачи до 2000 переменных
- гладкие нелинейные, глобальные и негладкие задачи до 500 переменных
И вам понадобится Premium Solver Platform, даже если ваша задача на больше , чем эти ограничения, потому что это база или «платформа», к которой «подключаются» все наши крупномасштабных Solver Engine . Если ваша проблема увеличивается в размерах, вы можете легко добавить один или несколько Solver Engine на платформу Premium Solver в любое время. Думаете, ваша модель слишком большая? Не беспокойтесь — у некоторых наших пользователей есть модели линейного программирования с миллионами переменных решения!
Более того, вы можете модернизировать Premium Solver Platform до нашего «суперпродукта» Risk Solver Platform, в любое время, за разницу в цене — получая мощные возможности моделирования методом Монте-Карло и оптимизации моделирования, а также новое стохастическое программирование и возможности надежной оптимизации, недоступные больше нигде, чтобы помочь вам найти надежные оптимальные решения для проблем с неопределенностью.
Самый быстрый способ узнать больше — бесплатно зарегистрироваться , загрузите и опробуйте платформу Premium Solver! Или вы можете просмотреть более подробную информацию о продукте Premium Solver Platform. Чтобы получить краткий обзор, щелкните здесь, чтобы просмотреть сравнение продуктов Excel Solver. Чтобы получить информацию о ценах, нажмите «Цены на программное обеспечение и поддержку Excel».
Premium Solver
Если платформа Premium Solver слишком велика для вашего бюджета, и если вам просто нужно решить небольшую линейную, смешанно-целочисленную или гладкую нелинейную модель , вы можете использовать нашу Premium Solver дешевле.
Premium Solver нельзя «расширить» для решения крупномасштабных задач оптимизации. Для всех наших «подключаемых» крупномасштабных решателей требуется Premium Solver Platform или Risk Solver Platform, а интерпретатор полиморфных электронных таблиц, доступный только в продуктах этих платформ, играет решающую роль в эффективном решении крупномасштабных задач.
Но вы можете обновить Premium Solver до Risk Solver Premium, чтобы получить возможности моделирования, до Premium Solver Platform для более мощных возможностей оптимизации или полностью до нашей «суперпродуктовой» Risk Solver Platform — в любое время, для разница в цене. Это делает Premium Solver отлично с чего начать!
Щелкните здесь для получения более подробной информации о продукте Premium Solver. Чтобы получить краткую сводку с ограничениями на размер задачи, нажмите здесь, чтобы просмотреть сравнение продуктов Excel Solver. Чтобы попробовать Premium Solver, просто зарегистрируйте и перейдите по ссылке, чтобы загрузить продукты Excel.
Зарегистрируйтесь для получения преимуществ загрузки и поддержки
Зарегистрируйтесь прямо сейчас! — это просто и бесплатно — так что вы можете:
- Загрузите бесплатную 15-дневную пробную версию любого из наших продуктов Excel Solver или SDK Solver
- Загрузите наши руководства пользователя Solver — узнайте больше о наших продуктах Excel и о том, как создавать лучшие модели оптимизации
- Доступ к «защищенным» страницам технической поддержки и загружаемый пример модели
Просто введите свое имя и адрес электронной почты, а также, при желании, название компании и телефон, выберите ближайший «Тип пользователя» и нажмите кнопку «Зарегистрироваться для загрузки».
Вы можете загрузить сразу после регистрации или вернуться позже в любое время — просто войдите в систему со своим адресом электронной почты — когда вы будете готовы опробовать наши обновления Excel Solver.
Решение систем уравнений в MS Excel
Перейти к основному содержанию
Гболагаде Оладжиде Гболагаде Оладжиде
Выпускник первого класса в области химического машиностроения | Бывший президент AICHE & NSCHE | Заинтересованы в изучении катализа
Опубликовано 7 февраля 2022 г.
+ Подписаться
Давайте сразу к делу: скажем, вам нужно решить следующий набор уравнений:
Это 7 одновременных уравнений; слишком много, чтобы решить вручную.
И вы знаете о хваленом MATLAB едва ли больше, чем его имя, так что это вам не по силам.
Теперь те, кто использует Excel достаточно долго, все согласны в одном: Excel может делать практически все на свете — решать уравнения, анализировать данные, моделировать процессы, программировать с помощью VBA , воскрешение мертвых, – ладно, последнее преувеличение, но суть вы поняли, верно?
Теперь приступим к задаче. Во-первых, составьте таблицу ваших переменных и их значений, которые скоро будут найдены, например:
Я ввел ноль в качестве каждого значения, хотя это и не нужно; вы можете просто оставить ячейки пустыми.
Затем перепишите каждое уравнение так, чтобы его правая часть была равна нулю.
(Здесь и далее упоминаются уравнения, преобразованные выше.)
Теперь создайте таблицу с каждым уравнением, напечатанным вниз, не в коде, а просто обычным образом:
Обратите внимание, что для ввода уравнений 4, 5 и 7 вы должны включить » =» » ( равно , затем двойные кавычки ) перед знаком минус и двойные кавычки после текста; это потому, что Excel принимает это как « = «, « + » или « — » отмечает начало уравнения, и поскольку вы вводите не само уравнение (то есть его код), а просто текст уравнения, начинающийся с «-», вы получите сообщение об ошибке.
Теперь введите код для уравнений:
Теперь, несмотря на то, что каждое уравнение должно иметь нулевую правую часть для решения, на рисунке выше правая часть не равна нулю: это связано с тем, что в настоящий момент переменные имеют нулевое значение, которое, по-видимому, не является тем значением, которое им необходимо принять для решения уравнений. Наша цель состоит в том, чтобы сделать все семь правых частей равными нулю, изменяя значения переменных.
Щелкните Решатель , под Данные :
Видели? Если вы этого не сделали, это означает, что ваш Excel не имеет надстройки Solver ; это означает, что вам придется добавить его вручную (нажмите File , затем Options , затем Add-ins. Там вы увидите Надстройка Solver . Щелкните ее и выберите Go , то добавляем)
Открыв Solver , вспомним что нам нужно сделать, чтобы решить уравнения? Мы должны гарантировать, что правая сторона каждого уравнения станет равной нулю, путем изменения значений переменных.
Установите Цель в любую из ячеек RHS.
До значения : ноль
Путем изменения ячеек переменных : все ячейки, имеющие значения переменных. Выберите их по одному или все сразу.
С учетом ограничений : Нажмите Добавить , затем выберите каждую ячейку с переменным значением и приравняйте к нулю. Можно сделать это по одной или просто выделив сразу все ячейки и приравняв к нулю.
Сделать переменные без ограничений неотрицательными ? Мы не знаем, может ли какая-либо из переменных иметь отрицательное значение, поэтому снимите флажок.
Какой метод решения следует использовать? Метод GRG Nonlinear предназначен для решения нелинейных задач; с другой стороны, метод Simplex LP будет работать для линейных задач, подобных этой (поскольку все 7 уравнений являются линейными).
О Эволюционный метод Чем меньше слов, тем лучше.