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

Обновлено: 21.11.2024

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

Типы задач CAS с системами уравнений

Экзамен 9. Нахождение факторов в многофакторной модели APT (BKM, глава 10)

Экзамен 8. Нахождение факторов многомерной достоверности (Куре и Вентер)

Ключевые формулы Excel

MINVERSE( Array A ) — возвращает значение, обратное A

МУМНОЖ(Массив A, Массив B) — умножает матрицы A и B

Пример

Решите приведенную ниже систему линейных уравнений для неизвестных переменных a, b и c:

2а + 12б + 4в = 25

8a + 6b + 11c = 32

Решение

A = Матрица коэффициентов

X = неизвестные переменные (вектор решения): [a, b, c]

B = вектор констант: [25, 9, 32]

В линейной алгебре решение для вектора неизвестной переменной выглядит так:

X = A-1 x B
где A-1 — это обратное значение A

Ниже приведены шаги для решения в Excel:

Шаг 1. Вычислите значение, обратное A

Используйте MINVERSE(массив) для вычисления обратного числа A.

Это формула массива, поэтому выберите выходной диапазон (E7:G9), введите формулу и нажмите CTRL + SHIFT + ENTER.

Шаг 2. Вычисление вектора решения X путем умножения A_inverse и B

Используйте МУМУЛЬТ(массив A, массив B) для вычисления вектора решения X как произведения A_inverse и B.

Опять же, это формула массива: выберите выходной диапазон (E11:E13), введите формулу и нажмите CTRL + SHIFT + ENTER. Обратите внимание, что вы не добавляете фигурные скобки в формулу. Это делает Excel.

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

Загрузите приведенную ниже электронную таблицу Excel для быстрого ознакомления. В нем также есть две тренировочные задачи для Экзамена 8 и Экзамена 9:

Экзамен 9 – RF BKM 10-1: Многофакторная модель APT (из пакета задач для экзамена 9)

Экзамен 8. Рецепт многомерной достоверности: Куре и Вентер (из Поваренной книги экзамена 8)

Таблица системы уравнений

Последнее замечание: попробуйте сами решить задачу с электронной таблицей на демонстрационном сайте Pearson Vue, чтобы убедиться, что она вам нравится.

С математической точки зрения система уравнений представляет собой набор из двух или более уравнений с одинаковым набором неизвестных переменных, таких как x, y, z, где нам нужно найти значения неизвестных переменных для решения этих уравнений. Чтобы решить систему уравнений, мы можем использовать функции и инструмент для решения уравнений.

Рисунок 1. Как решить систему уравнений

Использование функций Excel

Мы можем решить систему уравнений, используя математические функции MINVERSE и MMULT. Предположим, у нас есть три уравнения в нашей системе уравнений в нашем примере. Чтобы решить уравнения с помощью функций, нам нужно настроить данные следующим образом:

Рисунок 2. Настройка данных системы уравнений

В матричной записи это можно записать как;

И чтобы найти значение неизвестной переменной X, нам нужно найти обратную матрицу A ( A-1 ) и умножить ее на матрицу B , например;

Х= А-1В

Чтобы найти обратную матрицу A ( A-1 ), нам нужно использовать функцию MINVERSE следующим образом:

Выберите диапазон ячеек в соответствии с макетом матрицы A, например C10: E12.

Рисунок 3. Выбор диапазона ячеек

Перейдите к строке формул и вставьте формулы MINVERSE как =MINVERSE(C6: E8) и нажмите Ctrl + Shift + Enter вместо нажатия клавиши Enter, чтобы сделать формулу массива, чтобы возвращать массив значений как обратную матрице А.

Рисунок 4. Нахождение обратной матрицы A

Теперь нам нужно найти значения неизвестных переменных в матрице X (x, y, z) в столбце H, чтобы решить систему уравнений с помощью функции Excel МУМНОЖ следующим образом;

Выберите диапазон ячеек H6:H8 матрицы X.

Рисунок 5. Выбор диапазона ячеек матрицы X

В строке формул вставьте формулу МУМНОЖ как =МУМНОЖ(C10: E12, K6: K8), чтобы получить произведение двух матриц A-1 (обратная матрица A) и B с использованием ее массивов, и нажмите Ctrl + Shift + Enter, чтобы сделать его формулой массива. Эта формула возвращает значения неизвестных переменных и решает систему уравнений.

Рисунок 6. Решение системы уравнений

Использование надстройки Excel Solver

Еще один подход к решению системы уравнений — надстройка Excel Solver. Нам нужно установить эту надстройку из доступных надстроек из «Параметры»> «Надстройки». Для Excel Solver нам нужно настроить данные системы уравнений следующим образом;

Для матрицы A нам нужно ввести формулу для каждого из уравнений в столбце C, как показано.
Вставьте значения матрицы B в столбец D.
Оставьте ячейки матрицы X пустыми в столбце E, чтобы вернуть значения неизвестных переменных x, y, z .

Рисунок 7. Настройка данных уравнений для решателя

Перейдите на вкладку "Данные", в группе "Анализ" выберите "Решатель" для запуска.

Рис. 8. Окно параметров решателя

В поле «Установить цель» вставьте ссылку на ячейку C7 , первую ячейку матрицы A, где мы установили формулу для первого уравнения системы.
Выберите цель как Макс.
Вставьте диапазон ссылок на ячейки E7: E9 матрицы X в поле "Изменение переменных ячеек".

Рисунок 9. Настройка параметров решателя

В поле Subject to Constraints нам нужно добавить ограничения для каждого из уравнений, нажав кнопку «Добавить».
В окне «Добавить ограничения» нам нужно ввести ограничения для всех уравнений одно за другим, нажав кнопку «Добавить». Когда мы закончим добавлять все ограничения, нажмите кнопку OK.

Рис. 10. Добавление ограничений в решателе

После добавления всех ограничений нам нужно снять флажок "Сделать неограниченные переменные неотрицательными" под полем ограничений.
Нажмите кнопку "Решить".

Рисунок 11. Решение уравнений

В окне "Результаты решателя" выберите "Сохранить решения решателя" и нажмите кнопку "ОК".

Рисунок 12. Результаты решателя

Поиск решения возвращает значения неизвестных переменных для матрицы X в столбце E.

Рисунок 13. Решатель уравнений

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Что на этой странице

Как привести уравнения к стандартной форме?

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

Например, следующее уравнение имеет нестандартную форму: 4 - 1,5z = -0,5 x – 2y + 2

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

4 – 1,5 г = 0,5 x – 2 г + 2

+0,5x + 2y + 4–1,5z = -0,5x – 2y + 0,5x + 2y + 2

0,5x + 2y - 1,5z + 4 = 2

Теперь проделайте то же самое с константами, но переместите их в правую часть уравнения.

0,5x + 2y - 1,5z + 4 = 2

0,5x + 2y - 1,5z + 4 - 4 = 2 - 4

0,5x + 2y - 1,5z = -2

Решение линейных уравнений с помощью Решателя

На изображении выше показаны три уравнения в диапазоне ячеек B3:B5, каждое уравнение содержит три переменные x, y и z.

Эти уравнения можно создать в виде формул, если использовать именованные диапазоны в качестве переменных. Мы создадим три именованных диапазона для ячеек D8, D9 и D10. Здесь подробно описаны шаги.

Повторите вышеуказанные шаги с ячейками D9 и D10, используя имена y и z соответственно. Теперь мы можем использовать эти именованные диапазоны в наших формулах. Нам нужно переписать уравнения, чтобы Excel мог их интерпретировать. Символ звездочки умножает число на именованный диапазон.

Формула в ячейке C3:

Формула в ячейке C4:

Формула в ячейке C5:

На изображении выше показаны формулы в ячейках C3, C4 и C5 соответственно. Они используют числа в ячейках D8, D9 и D10, чтобы вернуть вычисленное число.

Искомые значения в диапазоне ячеек D3:D5 соответствуют числам в уравнениях. Ячейка D3 содержит -2, D4 содержит -4, а ячейка D5 содержит 0 (ноль).

Как установить надстройку Solver

Перейдите на вкладку "Данные" на ленте, кнопка "Решатель" теперь доступна обычно в правой части ленты.

Настройки решателя

Нажмите левой кнопкой мыши на кнопку "Решатель" на вкладке "Данные" на ленте. Появится диалоговое окно с настройками Солвера, которые вы можете настроить.

Нажмите мышью стрелку рядом с "Установить цель", чтобы выбрать ячейку на следующем шаге.
Нажмите мышью на ячейку C3.
Выберите переключатель "Значение:" и введите -2 в поле.
Нажмите мышью на стрелку, соответствующую «путем изменения ячеек переменных:».
Выберите диапазон ячеек D8:D10.
Нажмите мышью на кнопку "Добавить" рядом с "С учетом ограничений:". Появится диалоговое окно.
Выберите ссылку на ячейку C3.
Выберите знак равенства.
Выберите «Ограничение:» D3. Теперь диалоговое окно выглядит так, см. изображение ниже.
Нажмите левой кнопкой мыши на кнопку "ОК".
Повторите шаги с 6 по 10, используя ячейки C4 = D4 и C5 = D5.
Замените "Выберите метод решения:" на "Симплексный LP".

Диалоговое окно "Решатель" теперь выглядит так, см. изображение ниже.

Нажмите левой кнопкой мыши на кнопку "Решить". Если решение найдено, появится следующее диалоговое окно.

Снимите флажок «Вернуться к диалоговому окну параметров решения», если вас устраивает решение. Нажмите левой кнопкой мыши на кнопку "ОК", чтобы закрыть диалоговое окно.

Решение линейных уравнений с помощью формул Excel

На изображении выше показано, как решить три одновременных уравнения с тремя переменными, используя одну формулу Excel. Диапазон ячеек C3:C5 содержит три уравнения с переменными x, y и z.

Сначала извлеките числа из этих трех уравнений. Число перед переменной x в первом уравнении помещается в ячейку C8, в этом примере в ячейку C8 вводится 0,5.

Продолжайте со вторым уравнением, которое равно 4, введите 4 в ячейку C9. Продолжайте с оставшимися переменными и уравнениями, пока не будут извлечены все числа.

Константы вводятся в диапазоне ячеек F8:F10. См. изображение ниже.

Теперь вам нужно ввести следующую формулу массива в ячейку D13.

Формула массива в ячейке D13:D15:

Подписчики Excel 365 вводят формулу в ячейку D13 и нажимают клавишу ввода. Формула представляет собой формулу динамического массива, а результатом является массив чисел, который Excel переносит в ячейки ниже, если они пусты.

Вам нужно ввести формулу как формулу массива в диапазоне ячеек D13:D15, если у вас более ранняя версия Excel, вот шаги.

Выберите диапазон ячеек D13:D15.
Скопируйте приведенную выше формулу и вставьте ее в строку формул, см. изображение ниже.
Одновременно нажмите и удерживайте клавиши CTRL и SHIFT.
Нажмите Enter один раз.
Отпустите клавиши CTRL и SHIFT.

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

На изображении ниже показано, что ячейки D13, D14 и D15 теперь содержат числа, полученные из только что введенной формулы массива.

Объяснение формулы в ячейке D13

Инструмент "Оценить формулу" позволяет более подробно изучить вычисление по формуле. Выберите ячейку, содержащую формулу, которую вы хотите проверить. Перейдите на вкладку "Формулы" на ленте, нажмите левой кнопкой мыши на кнопку "Вычислить формулу".

Появится диалоговое окно с формулой, см. изображение выше. Подчеркнутое выражение — это то, что будет вычислено, когда вы нажмете кнопку «Оценить». Значения, выделенные курсивом, являются результатами самых последних расчетов.

В приведенном ниже видео очень подробно объясняется, как решить матрицу 3x3 с помощью матричного уравнения.

Шаг 1. Вычисление обратной матрицы

Функция MINVERSE вычисляет обратную матрицу для заданного массива. На этой веб-странице «Как найти обратную матрицу 3x3» подробно описаны два метода вычисления обратной матрицы.

или дробями:

Шаг 2. Вычисление матричного произведения двух массивов

Функция МУМНОЖ вычисляет матричное произведение двух массивов.

Получить файл Excel

Решение-одновременных-линейных-уравненийv2.xlsx

Еженедельный блог EMAIL

[newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

Статьи по теме

Вот полезная функция, которую я недавно нашел в поиске примеров решателя Excel.Я суммировал некоторые случайные значения из […]

Как вы, наверное, уже знаете, ранее я показывал вам созданный мной макрос vba, который находит […]

Я нашел этот вопрос Excel: Я контролер в многонациональной компании. У нас много транзакций (продажи, кредиты, дебеты, […]

Это вопрос, который я нашел внизу этой страницы. Использование Solver для планирования вашей рабочей силы Bank 24 […]

Вопрос: мне нужно настроить шаблон, чтобы удалить самые крупные доступные купюры (количество и номинал) и оставить ровно 150 долларов […]

Оставить ответ

Как комментировать

Как добавить формулу в комментарий
Вставьте сюда формулу.

Преобразование знаков меньше и больше
Использование символов HTML вместо знаков меньше и больше.
становится >

Как добавить код VBA в комментарий
[vb 1="vbnet" language=","]
Поместите здесь код VBA.
[/vb]

Одновременные уравнения обычно представляют собой кошмар для среднего школьника: он не может или не хочет их решать. Я не был исключением. Однако я помню, как понял, что они из себя представляли и как они работали спустя годы после окончания школы, а потом подумал, какого черта у кого-то с ними проблемы.

Если вы знаете SE, вы знаете, что существует несколько способов их решения, и я не математик, поэтому я не буду объяснять правило Крамера, матричную алгебру или даже подстановку… просто покажу вам, как Excel может помочь.

Что такое одновременные уравнения?

Одновременные уравнения получили свое название из-за того, что в двух, трех или более уравнениях X имеет одинаковое значение для каждого, Y имеет одинаковое значение для каждого и так далее. Например, вот два одновременных уравнения:

В этом случае я знаю, что x = 5 и y = 1, и мы докажем это, поставив 5, где x, и 1, где 1, в любом из двух уравнений, например, в уравнении (1):

5 + 3*1 = 8 … 5 + 3 = 8 … 8 = 8 … КЭД!

Попробуйте сами решить уравнение (2), если хотите.

Методы

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

Косвенный метод: использование двух функций массива MINVERSE() и затем MMULT() по отдельности: это, по сути, целая задача или матричное решение, которое дает нам значения каждой переменной

Прямой метод Использование двух функций массива MINVERSE(), а затем MMULT() вместе в форме =MMULT(MINVERSE()) .. это, по сути, решение с одной ячейкой для каждой переменной.

Имена косвенных и прямых являются моими собственными, и я классифицирую их как разные методы, хотя на самом деле это не так!

Решение SOLVER: использование надстройки SOLVER для предоставления значений переменных.

Метод 1: Косвенный метод

Настройте рабочий лист следующим образом:

Это набор одновременных уравнений с двумя переменными, и я оставил столбец C пустым, чтобы мы могли разработать примеры с тремя переменными. Все, что вам нужно сделать, это ввести жесткий код, записи в диапазоне A3:D5 в этом примере.

Диапазон A8:E9 включает решение, использующее сначала функцию массива MINVERSE(), а затем функцию массива MMULT().

Диапазон A12:D13 содержит область Proof, в которой мы проверяем решение, что x = 5 и y = 1.

На следующем снимке экрана показаны мои формулы… опять же, просто скопируйте их так, как вы видите здесь, но помните, что функции массива — это не обычные функции. Итак, для записи функции MIVERSE() вы должны сделать следующее:

Выделите диапазон A8:B9 и оставьте его выделенным, затем введите =MINVERSE(A4:B5), затем нажмите Control и удерживайте его нажатым, нажмите Shift и также удерживайте его, затем нажмите Enter, теперь отпустите все три клавиши I теперь будет называть это СПП!). И у вас должна получиться обратная матрица, которую вы видите в диапазоне A8:B9… если она не работает, сделайте это еще раз внимательно, и если она все еще не работает, проверьте ваш набор текста. Если это все еще не работает, загрузите мой файл примера!

Для функции массива МУМНОЖ() выполните следующие действия:

Выберите диапазон D8:D9 и оставьте его выбранным, затем введите =MMULT(A8:B9,D4:D5) … CSE … если это не работает … проверьте ввод …

Функция массива MMULT() дает вам ответ, так как я обозначил здесь первое число, 5, является значением x, а второе число, 1, является значением y.

Попробуйте это

Повторяйте вышеприведенное, пытаясь решить эти уравнения для x и y:

Не беспокойтесь обо всех десятичных знаках, которые вы можете увидеть, потому что Excel сделает всю работу за вас; и ваши ответы должны быть x = -1,176470588 и y = 3,470588235

Проведите проверку, чтобы убедиться, что ваши ответы верны… не смотрите на мое решение в моей рабочей тетради Excel, пока не будете довольны своим собственным!

Метод 2: прямой метод

Хотя это действительно то же самое, что и непрямой метод, я помечаю его здесь, чтобы те из вас, кто уверен в своих силах и/или кому не нужно видеть обратную матрицу, могли сразу перейти к решение:

Выберите диапазон G8:G9 и оставьте его выбранным, затем введите следующее =MMULT(MINVERSE(A4:B5),D4:D5) … CSE …

Вот и вы, прямо к ответам, x = 5 и y = 1.

Повторите это для уравнений

И докажи свой метод и ответы!

Теперь у вас есть шаблон для решения любого набора одновременных уравнений с двумя переменными, для которого есть решение.

Уравнения с тремя переменными

Многие учащиеся могут решать одновременные уравнения с двумя переменными, но затем запутываются с тремя и более переменными. Если бы я был на вашем месте, и вам нужно изучить и применить правила, такие как правило Крамера, в полном объеме, найдите решения здесь, а затем двигайтесь в обратном направлении, чтобы использовать более формальные методы… это главный совет для вас!

На снимке экрана показан набор одновременных уравнений с тремя переменными:

Вы можете видеть, что я полностью настроил его для вас и пометил решения цветом, так как эта часть рабочего листа сейчас немного переполнена.

ПРЕЖДЕ ЧЕМ вы посмотрите на мои решения в моей рабочей тетради, попробуйте самостоятельно решить систему уравнений с тремя переменными одновременно.

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

Способ 3: ПОИСК

SOLVER – это мощная надстройка, которую вам, возможно, потребуется установить, прежде чем вы начнете читать дальше

Менеджер… Надстройки Excel… Перейти

Выберите надстройку SOLVER

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

Вернуться к вопросу 1

Настройте область SOLVER, как показано здесь, в диапазоне H3:I4 и J5:j6:

Записи в H3:I4 просто набраны, и вам НЕ нужно ничего вводить в ячейки H4:I4, так как SOLVER запишет что-либо там с решениями проблемы. В качестве альтернативы введите любые цифры, чтобы проверить, работают ли ваши записи в J5:J6:

Математика: переставьте оба уравнения так, чтобы они = 0, вот так:

Х – х + 3у = 8 – х … … … (1)

Х – х – 2у = 3 – х … … … (2)

0 = 8 – x – 3y … … … (1)

0 = 3 – x + 2y … … … (2)

Вы должны понять, почему я сделал то, что сделал там… вычитая X из левой и правой сторон, чтобы оба уравнения были = 0

Затем в J5:J6 вы можете увидеть, что я сделал на скриншоте выше. Например, в J5:

SOLVER найдет ответ на этот вопрос, когда поместит правильные значения в ячейки H4 и I4 в этом примере, а J5 = 0 и J6 = 0… точно в соответствии с работой, которую мы только что проделали.

Настройте SOLVER следующим образом:

Перейдите на вкладку "Данные"... ПОИСК

Вы увидите это:

Введите то, что вы видите, в различные поля на случай, если я очистил их перед загрузкой файла! Убедитесь, что вы понимаете, что я сделал, потому что через минуту вы сделаете это самостоятельно для других и более крупных вопросов: трех и четырех переменных.

Установите для ячейки Цели значение J5… SOLVER заставит ее читать $J$5

Кому: значение 0… это жизненно важно

Изменяя ячейки переменных, H4:I4… снова SOLVER добавляет $$, и это ячейки, которые дадут вам ответ

С учетом ограничений:

В этом случае есть только один, и это J6 … нажмите «Добавить» … и сделайте так, чтобы он выглядел так:

Теперь нажмите «Решить», и ПОИСК должен сообщить вам, что он нашел решение, поместить решение в H4: I4 И показать, что J5 и J6 оба = 0. ЭТО ваш ответ. КЭД x = 5 и y = 1.

Попробуйте это

Повторяйте вышеприведенное, пытаясь решить эти уравнения для x и y:

Вы уже решили эту задачу, но для практики также используйте SOLVER.

Решение одновременных уравнений с тремя переменными с помощью SOLVER

Используйте SOLVER для решения задачи с тремя переменными, которую мы рассмотрели выше, и вот скриншот этого набора одновременных уравнений с тремя переменными:

ПРЕЖДЕ ЧЕМ вы посмотрите на мои решения в моей рабочей тетради, попробуйте сами решить, как решить эту проблему с помощью SOLVER.

Вот оно! Заставьте это работать, и это реальная экономия времени. Если вы можете заставить уравнения работать, но не SOLVER, не беспокойтесь, по крайней мере, вы нашли один работающий способ. И наоборот: если вы можете использовать SOLVER, но не уравнения…

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

Я также включил в этот файл формулу, которую вам может быть интересно изучить:

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

Читайте также: