Как решать уравнения в экселе

Вычисление вложенной формулы по шагам

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) проще понять, если вы увидите следующие промежуточные результаты:

Шаги, показанные в диалоговом окне

Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

=ЕСЛИ(40>50;СУММ(G2:G5);0)

Диапазон ячеек F2:F5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(F2:F5) возвращает результат 40.

=ЕСЛИ(Ложь;СУММ(G2:G5);0)

Значение 40 не больше 50, поэтому выражение в первом аргументе функции IF (аргумент logical_test) имеет значение false.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, так как она является вторым аргументом функции IF (value_if_true аргумент) и возвращается только в том случае, если выражение имеет значение true.

1. Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Вычислить формулу.

Примечание: Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.

• Некоторые части формул, использующие функции IF и CHOOSE, не вычисляются, а #N/A отображается в поле Оценка .
• Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
• Формулы с циклическими ссылками могут не оцениваться должным образом. Если нужны циклические ссылки, можно включить итеративное вычисление.
• Следующие функции пересчитываются при каждом изменении листа и могут привести к тому, что средство «Оценка формулы » дает результаты, отличные от результатов, отображаемых в ячейке: RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO и SUMIF (в некоторых сценариях).

учимся
программировать

Программированию нельзя научить, можно только научится

Главная » Уроки по Численным методам » Урок 10. Матричные уравнения и их решение c помощью MS Excel

Урок №10. Матричные уравнения и их решение с помощью MS Excel

Матричные уравнения вида
Матричным уравнением называется выражение вида
=
или в краткой записи: =,
где: = = =
, заданные матрицы, det, неизвестная матрица, которую надо найти.

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

Решение матричного уравнения: Искать решение матричного уравнения будем с помощью обратной матрицы = . Т.е. для того, чтобы найти решение матричного уравнения, необходимо найти обратную матрицу А и умножить ее на матрицу В.

Задание №1. Решить матричное уравнение
=

Ход решения:
Уравнение вида =. Искать решение будем по формуле: =
где: = = =

1. Введите исходные матрицы А и В на лист Excel (рис.1).

Рисунок 1. Исходные матрицы

2. Теперь необходимо найти обратную матрицу для матрицы . Найти ее самостоятельно, с помощью функции МОБР. Результат показан на рисунке 2.

Рисунок 2. Вычисленная с помощью функции МОБР обратная матрица

3. Далее необходимо найти произведение обратной матрицы и матрицы . Самостоятельно выполните произведение матриц с помощью функции МУМНОЖ. Результат показан на рисунке 3.

Рисунок 3. Умножение матриц и

4. Для ячеек H5 и H6 необходимо изменить формат. Для этого выделите эти две ячейки, щелкните правую кнопку мыши и из контекстного меню выберите пункт «Формат ячеек». В открывшемся окне на закладке «Число» выберите числовой формат «Числовой» и установите число десятичных знаков равным нулю (рис.4).

Рисунок 4. Формат ячеек

5. В итоге результат вычисления матричного уравнения есть матрица:

=

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

Решение системы уравнений в Excel

Возможно вы слышали о нобелевском лауреате, психологе и исследователе по имени Дэниель Канеман. Канеман занимался наукой, которую называют термином «поведенческая экономика», т.е. изучал реакции, поведение и суждения людей в типовых жизненных (и экономических) ситуациях и условиях неопределенности. В его книге, которая называется «Думай медленно — решай быстро» (очень рекомендую, кстати) в качестве одного из примеров когнитивных искажений — несознательной автоматической реакции — приводится следующая задача: Бейсбольная бита и мяч стоят вместе 1 доллар 10 центов.
Бита дороже мяча на 1 доллар.
Сколько стоит мяч? Подозреваю, что вашей первой рефлекторной мыслью, скорее всего, будет «10 центов!» 🙂 Но весьма скоро, я уверен, вы сообразите, что на самом деле всё не так примитивно и для получения ответа нужно решить простую систему уравнений (здесь b — это бита, а m — это мяч):
Конечно можно «тряхнуть стариной» и решить всё вручную на бумажке через подстановку переменных — как-то так:
Но, во-первых, на практике уравнения могут быть сложнее и переменных может оказаться сильно больше двух и, во-вторых, у нас с вами есть Microsoft Excel — универсальный мега-инструмент, величайшее изобретение человечества. Так что давайте-ка лучше разберём как решить нашу задачу с его помощью.

Способ 1. Матричные функции МУМНОЖ и МОБР

Само собой, изобретать велосипед тут не надо — прогрессивное человечество в лице математиков давным-давно придумало кучу способов для решения подобных задач. В частности, если уравнения в нашей системе линейные (т.е. не используют степени, логарифмы, тригонометрические функции типа sin, cos и т.д.), то можно использовать метод Крамера. Сначала записываем числовые коэффициенты, стоящие перед нашими переменными в виде матрицы (в нашем случае — размером 2х2, в общем случае — может быть и больше). Затем находим для неё так называемую обратную матрицу , т.е. матрицу, при умножении которой на исходную матрицу коэффициентов получается единица. В Excel это легко сделать с помощью стандартной математической функции МОБР (MINVERSE) :
Здесь важно отметить, что если у вас свежая версия Excel 2021 или Excel 365, то достаточно ввести эту функцию обычным образом в первую ячейку (G7) — сразу получится динамический массив с обратной матрицей 2х2. Если же у вас более старая версия Excel, то эту функцию нужно обязательно вводить как формулу массива, а именно:

1. Выделить диапазон для результатов — G7:H8
2. Ввести функцию =МОБР(B7:C8) в строку формул
3. Нажать на клавиатуре сочетание клавиш 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

Данный инструмент следует применять для анализа данных с одним неизвестным (или изменяемым) условием. Например:

• y =7 является функцией x ;
• нам известно значение y , следует узнать при каком значении x мы получим y вычисляемый формулой.

Решим данную задачу встроенными вычислительными инструментами Excel для анализа данных:

1. Заполните ячейки листа, так как показано на рисунке:
2. Перейдите в ячейку B2 и выберите инструмент, где находится подбор параметра в Excel: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра».
3. В появившемся окне заполните поля значениями как показано на рисунке, и нажмите ОК:

В результате мы получили правильное значение 3.

Получили максимально точный результат: 2*3+1=7

Второй пример использования подбора параметра для уравнений

Немного усложним задачу. На этот раз формула выглядит следующим образом:

1. Заполните ячейку B2 формулой как показано на рисунке:
2. Выберите встроенный инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра» и снова заполните его параметрами как на рисунке (в этот раз значение 4):
3. Сравните 2 результата вычисления:

Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.

Это простые примеры быстрого поиска решений формул с помощью Excel. Сегодня каждый школьник знает, как найти значение x. Например:

Excel в своих алгоритмах инструментов анализа данных использует более простой метод – подстановки. Он подставляет вместо x разные значения и анализирует, насколько результат вычислений отклоняется от условий указанных в параметрах инструмента. Как только будет, достигнут результат вычисления с максимальной точностью, процесс подстановки прекращается.

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

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

1. Увеличить в настройках параметр предельного числа итераций.
2. Изменить относительную погрешность.
3. В ячейке переменной (как во втором примере, A3) ввести приблизительное значение для быстрого поиска решения. Если же ячейка будет пуста, то Excel начнет с любого числа (рандомно).

Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.

О подборе нескольких параметров в Excel узнаем из примеров следующего урока.