Решение нелинейных уравнений в Excel

Обновлено: 15.05.2024

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

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

В Excel VBA решение уравнений можно найти с помощью функций Worksheetfunction.MInverse и MMult, как показано в приведенном ниже коде:

Определяемая пользователем функция (UDF) MSolve была изменена, как показано выше, и более ранняя версия была переименована в MSolve2. Электронную таблицу, включающую примеры обеих функций и полный открытый исходный код, можно загрузить по адресу:

Eval2.zip

Ввод и вывод обеих функций показаны на снимке экрана ниже:

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

Пример MSolve добавляет еще одно неизвестное (диаметр натянутой арматуры) и еще одну цель — напряжение натянутой арматуры.

Пакет Python Scipy также содержит ряд подпрограмм для решения задач этого типа. Чтобы получить к ним доступ из Excel, нам нужно:

Подпрограмма Python для решаемой функции.
Интерфейс, позволяющий получить доступ к этой процедуре и функциям Scipy из Excel.

Я обновил электронную таблицу Eval-PyIntegration для выполнения этой задачи, и ее можно скачать здесь:

Eval-PyInt.zip

В электронной таблице для связи с Python используется ExcelPython, а загружаемый файл включает все необходимые файлы (кроме Excel, Python и Scipy).

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

Загружаемый файл также включает версии функций CurveatMA и CurveatMAS для Python с входными данными, как показано ниже. Функции используют функцию SciPy optimise.root, которая имеет множество необязательных аргументов, подробно описанных в руководстве по SciPy. В электронной таблице включены только параметры для типа решателя и допустимого отклонения.

На снимке экрана ниже показаны результаты CurveatMA для нулевой осевой нагрузки и нулевого изгибающего момента. Возвращаемые значения глубины нейтральной оси (DNA) и деформации поверхности сжатия (Epsc) были проверены с помощью функции SectForceMV, которая обнаружила, что осевая сила и изгибающий момент соответствуют ожидаемым.

Функция xl_SolveF также может быть вызвана напрямую с помощью функции, которая возвращает разницу между значениями функции и целевыми значениями, в данном случае SectForceMDiff.

Наконец, CurveatMAS регулирует глубину нейтральной оси, деформацию поверхности сжатия и площадь растянутой стали таким образом, чтобы осевая сила, изгибающий момент и кривизна сечения соответствовали целевым значениям. И снова xl_SolveF можно использовать с SectForceMdiff для возврата тех же трех значений.

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

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

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

Этот канал имеет известные скорость потока (Q), ширину (B) и уклон (S). Площадь потока зависит от глубины y, для которой и будет решаться. Вся система управляется этими тремя уравнениями для расхода (Q), площади (A) и гидравлического радиуса (R):

И площадь, и гидравлический радиус зависят от y, и оба эти условия входят в уравнение скорости потока.

Для начала введите примерное значение y, равное 2 метрам. Назовите эту ячейку y, используя поле имени. Другим входным ячейкам были присвоены имена.

Далее мы введем три уравнения в Excel. Для области:

Назовите эту ячейку A. Для гидравлического радиуса введите формулу:

Excel не принимает R в качестве имени, поэтому назовите эту ячейку Rad как сокращение от радиуса.

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

=1/n*SQRT(S)*A*Rad^(2/3)

Назовите эту ячейку Qcalc. Очевидно, что расчетный расход не совпадает с фактическим расходом 110 м 3 /с, поэтому вам потребуется более точное предположение о глубине y. Чтобы использовать поиск цели, настройте ячейку для расчета разницы между фактическим и расчетным расходом в ячейке C14:

=Q-Qрасч

Эта ячейка вычисляет ошибку вашего предположения. Вы можете использовать Goal Seek, чтобы свести ошибку к нулю. Выберите ячейку, содержащую ошибку, затем перейдите в «Данные» > «Анализ «что, если»» > «Поиск цели». Он автоматически использует выбранную ячейку в качестве ячейки Set. Введите ноль в качестве значения Кому. Переменная, которая будет изменена, чтобы обнулить ошибку, — это y, поэтому щелкните эту ячейку.

Нажмите «ОК», и поиск цели отрегулирует значение y таким образом, чтобы разница между фактическим расходом и рассчитанным расходом равнялась нулю. Окончательное значение y равно 1,499 м.

Вы хотите решить нелинейное уравнение, требующее итеративного решения, и не знаете, как это сделать в Excel.

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

В этом рецепте я хочу показать вам на примере, как использовать встроенные в Excel инструменты Solver и Goal Seek для решения нелинейного уравнения. Уравнение, которое мы рассмотрим:

Это уравнение используется для оценки коэффициента сопротивления трения Cf в зависимости от числа Рейнольдса RN для некоторых расчетов сопротивления судна. Для этой же цели существуют и другие уравнения, но я выбрал это классическое, потому что его нельзя записать в явном виде y = g(x). Вместо этого вам придется прибегнуть к некоторому итеративному методу, чтобы найти значение Cf, соответствующее некоторому заданному значению RN.

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

Теперь мы можем использовать Solver или Goal Seek, чтобы итеративно найти значение Cf при заданном значении RN, которое делает правую часть этого уравнения равной 0. Но сначала вам нужно ввести правую часть этого уравнения как формула электронной таблицы, как показано на рис. 9-7.

Рисунок 9-7. Пример нелинейного уравнения

Ячейка C5 содержит формулу рабочего листа, представляющую правую часть нашего уравнения. Формула =0,242/SQRT(C4)-LOG(C3*C4,10), как показано в строке формул на рис. 9-7. Ячейка C3 содержит заданное число Рейнольдса, а ячейка C4 содержит соответствующий коэффициент трения, найденный с помощью Solver или Goal Seek. Прежде чем использовать Solver или Goal Seek, вы должны ввести исходное предположение для коэффициента трения в ячейку C4.Как упоминалось во введении к этой главе, вы должны выбрать начальное предположение, разумное для рассматриваемой проблемы.

Поиск Cf с поиском цели

Чтобы использовать поиск цели для поиска решения для этого примера, выберите Инструменты images/U2192.jpg border=0> Поиск цели. из строки главного меню, чтобы открыть окно поиска цели, показанное на рис. 9-8.

Рисунок 9-8. Окно поиска цели для примера нелинейного уравнения

В поле "Установить ячейку" введите C5 или нажмите маленький значок справа от поля редактирования, чтобы временно вернуться к электронной таблице, и щелкните ячейку C5, чтобы выбрать ее (нажмите Введите, когда вы выбрали его, чтобы вернуться в окно поиска цели). Помните, ячейка C5 содержит формулу, представляющую правую часть нашего уравнения, и мы хотим, чтобы результат этой формулы был равен нулю. Поэтому в поле «В значение» введите 0. Это наше целевое значение. Теперь установите в поле «Изменяя ячейку» значение C4, которое содержит значение коэффициента трения. Когда вы нажмете кнопку OK, поиск цели будет изменять значение в ячейке C4 до тех пор, пока формула в ячейке C5 не станет приблизительно равной 0. Результат для этого примера показан на рис. 9-7, где остаток, показанный в ячейке C5, равен -2,23 x 10-. 7.

Должен упомянуть, что мое первоначальное предположение (то есть значение, с которого я начал в ячейке C4) было 1 x 10-7. Я бы выбрал 0, так как знаю, что коэффициент трения должен быть довольно маленьким числом, но я этого не сделал, потому что это привело бы к ошибке деления на ноль для этой задачи. Запуск поиска цели с ошибкой деления на ноль приведет к сбою поиска цели с ошибкой "Формула в ячейке должна привести к числу".

Остаток -2,23 x 10-7 не так уж и плох, но мы можем добиться большего. Как обсуждалось во введении, вы можете изменить настройки конвергенции для поиска цели, перейдя в окно «Параметры». Выберите Параметры инструментов. из строки главного меню, чтобы открыть окно параметров (показанное ранее на рис. 9-2). На панели «Расчет» я проверил «Итерация», установил «Максимальное количество итераций» на 5000 и установил «Максимальное изменение» на 1 x 10-8. С этими настройками поиск цели находит решение с гораздо меньшим остатком, 1,2 x 10-13. Соответствующий коэффициент трения для заданного числа Рейнольдса равен 0,0124.

Поиск Cf с помощью решателя

Поиск цели хорошо подходит для этого простого примера, и на практике вам не придется полагаться на Solver для решения этой задачи. Однако в целях иллюстрации я хочу показать вам, как применить Solver к той же задаче. Электронная таблица, настроенная в этом случае, такая же, как и в случае использования Goal Seek. Все, что вы делаете по-другому, это выберите Tools images/U2192.jpg border=0> Solver. из строки главного меню, чтобы открыть Solver вместо Goal Seek. Окно параметров решателя для этого примера показано на рис. 9-9.

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

Здесь я установил в поле "Установить целевую ячейку" значение C5, а для параметра "Равно" задал значение 0. Опять же, C5 содержит правую часть нашего уравнения, которая должна быть равна 0. Затем я устанавливаю значение "По". Изменение поля Cells на ячейку C4, содержащую начальное значение коэффициента трения. Нажатие кнопки "Решить" запускает процесс решения.

Решатель действительно находит решение, давая коэффициент трения 0,0124 с невязкой -4,87 x 10-12. Эти значения сопоставимы с результатами, полученными с помощью Goal Seek.

Просто ради интереса попробуйте этот пример еще раз, но на этот раз используйте начальное предположение для коэффициента трения, равное 1, 5 или некоторому большому числу. В этом случае Solver фактически не может найти решение! Что происходит, так это то, что Solver выходит за рамки фактического решения и в конечном итоге пытается использовать значение коэффициента трения, равное 0, для итерации. Это, конечно, приводит к ошибке деления на ноль, что приводит к преждевременному сбою Солвера. Это небольшое упражнение подчеркивает важность выбора разумного начального предположения. Это также показывает, что если ваша первая попытка не удалась, вам следует попробовать другое исходное предположение, поскольку новое может сработать.

Tay (2006) предложил решать численные методы с помощью калькулятора Casio fx-570MS, чтобы преодолеть утомительные рекурсивные вычисления. Здесь мы представляем другую альтернативу, то есть решение нелинейной системы с использованием метода Ньютона в Microsoft Office Excel. Для этого мы просто используем функцию MULT для умножения матриц и.

Цитаты

<р>.Дополнительные сведения о Microsoft Excel Solver можно найти в такой литературе, как Briti et al. (2013); Барати (2013 г.); Тай и др. (2014) ; Оке и др. (2016Oke et al. ( , 2017Oke et al. ( , 2018). Процедуры, используемые при расчете параметров модели с помощью Microsoft Excel Solver, следующие (Oke et al., 2017): a) Microsoft Excel Solver был добавлен на панель инструментов Microsoft Excel; б) целевое (предельное) значение итерации было установлено для программного обеспечения на основе квадрата разности как .

В этой статье автоматизированная система дезинфекции была построена из пластиковых цилиндрических резервуаров и системы автоматического управления. Оценка производительности системы дезинфекции на основе способности выпускать раствор хлора в требуемое время была проведена на очистке воды Университета Элизаде, Илара-Мокин, штат Ондо, Нигерия. Влияние времени и концентрации хлора на производительность системы оценивали с помощью дисперсионного анализа. Применение системы в течение определенного периода времени тестировалось и контролировалось на месте на установке очистки воды учреждения на основе остаточного хлора в пробах очищенной воды. Модель, которая связывает добавленную концентрацию хлора и время обработки с концентрацией остаточного хлора, была предложена и разработана с использованием статистических методов и методов решения Microsoft Excel (MES). Оценки производительности и модели были установлены с использованием статистических методов (информационный критерий Акаике (AIC), критерий Шварца (SC), коэффициент детерминации (CD) и модельный критерий выбора (MSC)) с использованием ожидаемого значения в качестве эталонных данных. Был проведен краткий анализ затрат. Исследование показало, что не было существенной разницы между наблюдаемыми автоматическими и ожидаемыми автоматическими таймерами при доверительном уровне 95 %. Не было существенной разницы между наблюдаемой концентрацией хлора и ожидаемой концентрацией хлора при доверительном уровне 99 %. MSC, AIC, SC и CD составили 44,0, 10,0, 33,0 и 0,9679 и 43, 12, 35 и 0,9523 для MES и статистических методов соответственно. Соотношение между фактической и расчетной концентрацией хлора имеет КД 0,7078 и 0,7076 для МЭС и статистических методов соответственно. Сделан вывод о том, что разработанный автоматизированный хлоратор является перспективным устройством на обычных очистных сооружениях поверхностных вод.

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