Решение системы уравнений в Excel
Возможно вы слышали о нобелевском лауреате, психологе и исследователе по имени Дэниель Канеман. Канеман занимался наукой, которую называют термином «поведенческая экономика», т.е. изучал реакции, поведение и суждения людей в типовых жизненных (и экономических) ситуациях и условиях неопределенности. В его книге, которая называется «Думай медленно — решай быстро» (очень рекомендую, кстати) в качестве одного из примеров когнитивных искажений — несознательной автоматической реакции — приводится следующая задача: Бейсбольная бита и мяч стоят вместе 1 доллар 10 центов.
Бита дороже мяча на 1 доллар.
Сколько стоит мяч? Подозреваю, что вашей первой рефлекторной мыслью, скорее всего, будет «10 центов!» 🙂 Но весьма скоро, я уверен, вы сообразите, что на самом деле всё не так примитивно и для получения ответа нужно решить простую систему уравнений (здесь b — это бита, а m — это мяч): 
Конечно можно «тряхнуть стариной» и решить всё вручную на бумажке через подстановку переменных — как-то так: 
Но, во-первых, на практике уравнения могут быть сложнее и переменных может оказаться сильно больше двух и, во-вторых, у нас с вами есть Microsoft Excel — универсальный мега-инструмент, величайшее изобретение человечества. Так что давайте-ка лучше разберём как решить нашу задачу с его помощью.
Способ 1. Матричные функции МУМНОЖ и МОБР
Само собой, изобретать велосипед тут не надо — прогрессивное человечество в лице математиков давным-давно придумало кучу способов для решения подобных задач. В частности, если уравнения в нашей системе линейные (т.е. не используют степени, логарифмы, тригонометрические функции типа sin, cos и т.д.), то можно использовать метод Крамера. Сначала записываем числовые коэффициенты, стоящие перед нашими переменными в виде матрицы (в нашем случае — размером 2х2, в общем случае — может быть и больше). Затем находим для неё так называемую обратную матрицу , т.е. матрицу, при умножении которой на исходную матрицу коэффициентов получается единица. В Excel это легко сделать с помощью стандартной математической функции МОБР (MINVERSE) :
Здесь важно отметить, что если у вас свежая версия Excel 2021 или Excel 365, то достаточно ввести эту функцию обычным образом в первую ячейку (G7) — сразу получится динамический массив с обратной матрицей 2х2. Если же у вас более старая версия Excel, то эту функцию нужно обязательно вводить как формулу массива, а именно:
- Выделить диапазон для результатов — G7:H8
- Ввести функцию =МОБР(B7:C8) в строку формул
- Нажать на клавиатуре сочетание клавиш Ctrl + Shift + Enter
Замечательное свойство обратной матрицы состоит в том, что если умножить её на значения правых частей наших уравнений (свободные члены), то мы получим значения переменных, при которых левые и правые части уравнений будут равны, т.е. решения нашей задачи. Выполнить такое матричное умножение можно с помощью ещё одной стандартной экселевской функции МУМНОЖ (MMULT) :
Если у вас старая версия Excel, то не забудьте также ввести её в режиме формулы массива, т.е. сначала выделить диапазон K7:K8, а после ввода функции нажать сочетание клавиш Ctrl + Shift + Enter .
Само собой, уравнений и переменных может быть больше, да и посчитать всё можно сразу в одной формуле, вложив используемые функции одна в другую:
Не так уж и сложно, правда? Однако надо понимать, что этот метод подходит только для решения систем линейных уравнений. Если у вас в уравнениях используются функции посложнее четырех базовых математических действий, то зачастую проще будет пойти другим путем — через подбор.
Способ 2. Подбор надстройкой «Поиск решения» (Solver)
Принципиально другой способ решения подобных задач — это итерационные методы, т.е. последовательный подбор значений переменных, чтобы после подстановки их в наши уравнения мы получили верные равенства. Само собой, подбор имеется ввиду не тупой и долгий (брутфорс), а умный и быстрый, благо математики, опять же, давным-давно придумали кучу различных методов для решения таких задач буквально за несколько итераций.
В Microsoft Excel некоторые из этих методов реализованы в стандартной надстройке Поиск решения (Solver) . Её можно подключить через Файл — Параметры — Надстройки — Перейти (File — Options — Add-ins — Go to) или на вкладке Разработчик — Надстройки (Developer — Add-ins) .
Давайте рассмотрим её использование на следующей задаче. Предположим, что нам с вами нужно решить вот такую систему из двух нелинейных уравнений:
Подготавливаем основу для оптимизации в Excel:
- В жёлтых ячейках C9:C10 лежат текущие значения наших переменных, которые и будут подбираться в процессе оптимизации. В качестве стартовых можно взять любые значения, например, нули или единицы — роли не играет. Для удобства, кстати, этим ячейкам можно дать имена, назвав их именами переменных x и y, — для этого выделите диапазон C9:C10 и выберите команду Формулы — Создать из выделенного — Слева (Formulas — Create from selection — Left column) .
- В зелёных ячейках E9:E10 введены наши уравнения с использованием либо прямых ссылок на жёлтые ячейки переменных, либо созданных имён (так нагляднее). В результате мы видим, чему равны наши уравнения при текущих значениях переменных.
- В синих ячейках F9:F10 введены значения правых частей наших уравнений, к которым мы должны стремиться.
Теперь запускаем нашу надстройку на вкладке Данные — Поиск решения (Data — Solver) и вводим в появившемся диалоговом окне следующие параметры:
- Оптимизировать целевую функцию (Set target cell) — любая из двух наших зелёных ячеек с уравнениями, например E9.
- Изменяя ячейки переменных (By changing cells) — жёлтые ячейки с текущими значениями переменных, которыми мы «играем».
- Добавляем ограничение с помощью кнопки Добавить (Add) и задаём равенство левой и правой части наших уравнений, т.е. зелёного и голубого диапазонов.
- В качестве метода решения выбираем Поиск решения нелинейных задач методом ОПГ, т.к. уравнения у нас нелинейные. Для линейных можно смело выбирать симплекс-метод.
После нажатия на кнопку Найти решение (Solve) через пару мгновений (или не пару — это зависит от сложности задачи) мы должны увидеть окно с результатами. Если решение найдено, то в жёлтых ячейках отобразятся подобранные значения наших переменных:
Обратите внимание, что поскольку мы здесь используем итерационные, а не аналитические методы, то зеленые ячейки не совсем равны голубым, т.е. найденное решение не абсолютно точно. На практике, конечно же, такой точности вполне достаточно для большинства задач, и если необходимо, её можно настроить, вернувшись в окно Поиск решения и нажав кнопку Параметры (Options) .
Как построить уравнение в Excel
Часто вас может заинтересовать построение уравнения или функции в Excel. К счастью, это легко сделать с помощью встроенных формул Excel.
В этом руководстве представлено несколько примеров того, как строить уравнения/функции в Excel.
Пример 1: построение линейного уравнения
Предположим, вы хотите построить следующее уравнение:
у = 2х + 5
На следующем изображении показано, как создать значения y для этого линейного уравнения в Excel, используя диапазон от 1 до 10 для значений x:
Затем выделите значения в диапазоне A2:B11.Затем нажмите на вкладку « Вставка ». В группе « Диаграммы » щелкните параметр графика под названием « Разброс ».
Автоматически появится следующий график:
Мы видим, что график следует прямой линии, поскольку уравнение, которое мы использовали, было линейным по своей природе.
Пример 2. Построение квадратного уравнения
Предположим, вы хотите построить следующее уравнение:
у = 3x 2
На следующем изображении показано, как создать значения y для этого уравнения в Excel, используя диапазон от 1 до 10 для значений x:
Затем выделите значения в диапазоне A2:B11.Затем нажмите на вкладку « Вставка ». В группе « Диаграммы » щелкните параметр графика под названием « Разброс ».
Автоматически появится следующий график:
Мы видим, что график следует изогнутой линии, поскольку уравнение, которое мы использовали, было квадратным.
Пример 3: построение уравнения обратной связи
Предположим, вы хотите построить следующее уравнение:
у = 1/х
На следующем изображении показано, как создать значения y для этого уравнения в Excel, используя диапазон от 1 до 10 для значений x:
Затем выделите значения в диапазоне A2:B11.Затем нажмите на вкладку « Вставка ». В группе « Диаграммы » щелкните параметр графика под названием « Разброс ».
Автоматически появится следующий график:
Мы видим, что график следует по изогнутой линии вниз, поскольку это представляет уравнение y = 1/x.
Пример 4. Построение уравнения синуса
Предположим, вы хотите построить следующее уравнение:
у = грех (х)
На следующем изображении показано, как создать значения y для этого уравнения в Excel, используя диапазон от 1 до 10 для значений x:
Затем выделите значения в диапазоне A2:B11.Затем нажмите на вкладку « Вставка ». В группе « Диаграммы » щелкните параметр графика « Разброс с плавными линиями и маркерами» .
Автоматически появится следующий график:
Вывод
Вы можете использовать аналогичную технику для построения графика любой функции или уравнения в Excel. Просто выберите диапазон значений x для использования в одном столбце, затем используйте уравнение в отдельном столбце, чтобы определить значения y на основе значений x.
Создание уравнений и формул
Совет. Видео не на вашем языке? Попробуйте выбрать Скрытые субтитры .
Проверьте, как это работает!
Если вам нужна формула, добавьте или напишите ее в Word.
- На вкладке Вставка выберите команду Уравнение. Также можно нажать клавиши Alt+=.
- Чтобы использовать встроенную формулу, на вкладке Конструктор нажмите кнопку Уравнение.
- Чтобы создать собственную формулу, на вкладке Вставка выберите команду Уравнение, а затем нажмите кнопку Рукописное уравнение.
- Написать формулу можно с помощью пальца, пера или мыши.
- Нажмите кнопку Вставка, чтобы перенести формулу в файл.
Создание простой формулы в Excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Вы можете создать простую формулу для с суммы, вычитания, умножения и деления значений на вашем компьютере. Простые формулы всегда начинаются со знака равной(=),за которым следуют константы, которые являются числами и операторами вычислений, такими как «плюс»(+),«минус» (— ),«звездочка»*или «косая черта»(/)в начале.
В качестве примера рассмотрим простую формулу.
- Выделите на листе ячейку, в которую необходимо ввести формулу.
- Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении. В нашем примере введите =1+1.
- Вместо ввода констант в формуле можно выбрать ячейки с нужными значениями и ввести операторы между ними.
- В соответствии со стандартным порядком математических операций, умножение и деление выполняются до сложения и вычитания.
Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.
Использование автосуммирования
Для быстрого суммирования чисел в столбце или строке можно использовать кнопку «Автосумма». Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная, а затем нажмите клавишу ВВОД (Windows) или Return (Mac).
Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).
Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT++= (Mac), и Excel автоматически вставит функцию СУММ.
Приведем пример. Чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку «Автоумма». Формула появится в ячейке B7, а Excel выделит ячейки, которые вы суммируете.
Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.
- Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
- Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
- Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
Скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).