Численные методы решения нелинейных уравнений в Excel
Обновлено: 05.07.2024
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Solver — это надстройка Microsoft Excel, которую можно использовать для анализа возможных вариантов. Используйте «Поиск решения», чтобы найти оптимальное (максимальное или минимальное) значение формулы в одной ячейке, называемой целевой ячейкой, с учетом ограничений или ограничений на значения других ячеек формулы на листе. Решатель работает с группой ячеек, называемых переменными решения или просто ячейками переменных, которые используются при вычислении формул в ячейках целей и ограничений. Решатель корректирует значения в ячейках переменных решения, чтобы удовлетворить ограничения на ячейки ограничений и получить результат, который вы хотите получить для целевой ячейки.
Проще говоря, вы можете использовать Solver для определения максимального или минимального значения одной ячейки путем изменения других ячеек. Например, вы можете изменить сумму прогнозируемого рекламного бюджета и увидеть, как это повлияет на прогнозируемую сумму прибыли.
Примечание. В версиях Solver до Excel 2007 целевая ячейка называлась «целевой ячейкой», а ячейки переменных решения — «изменяющимися ячейками» или «настраиваемыми ячейками». В надстройку «Поиск решения» для Excel 2010 было внесено множество улучшений, поэтому, если вы используете Excel 2007, работа будет немного отличаться.
В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму дохода от продаж, сопутствующие расходы и прибыль. Решатель может изменять ежеквартальные бюджеты на рекламу (ячейки переменных решения B5:C5) вплоть до ограничения общего бюджета в размере 20 000 долларов США (ячейка F5), пока общая прибыль (целевая ячейка F7) не достигнет максимально возможной суммы. Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы F7, =СУММ (Прибыль Q1:Прибыль Q2).
<р>1. Ячейки переменных <р>2. Ограниченная ячейка <р>3. Целевая ячейкаПосле запуска Solver новые значения выглядят следующим образом.
На вкладке "Данные" в группе "Анализ" нажмите "Решатель".
Примечание. Если команда «Поиск решения» или группа «Анализ» недоступны, необходимо активировать надстройку «Поиск решения». См. статью Как активировать надстройку Solver.
В поле "Установить цель" введите ссылку на ячейку или имя целевой ячейки. Целевая ячейка должна содержать формулу.
Выполните одно из следующих действий:
Если вы хотите, чтобы значение целевой ячейки было как можно больше, нажмите Макс.
Если вы хотите, чтобы значение целевой ячейки было как можно меньше, нажмите Мин.
Если вы хотите, чтобы целевой ячейкой было определенное значение, нажмите «Значение» и введите значение в поле.
В поле Путем изменения ячеек переменной введите имя или ссылку для каждого диапазона ячеек переменной решения. Разделяйте несмежные ссылки запятыми. Переменные ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Вы можете указать до 200 переменных ячеек.
В поле "С учетом ограничений" введите любые ограничения, которые вы хотите применить, выполнив следующие действия:
В диалоговом окне "Параметры решателя" нажмите "Добавить".
В поле "Ссылка на ячейку" введите ссылку на ячейку или имя диапазона ячеек, для которого вы хотите ограничить значение.
Нажмите на отношение ( =, int, bin или dif ), которое вы хотите между ячейкой, на которую указывает ссылка, и ограничением. Если щелкнуть int, в поле Constraint появится целое число. Если щелкнуть bin, в поле Constraint появится двоичный файл. Если вы нажмете "dif", в поле "Ограничение" появится all different.
Если вы выберете = для отношения в поле Ограничение, введите число, ссылку или имя ячейки или формулу.
Выполните одно из следующих действий:
Чтобы принять ограничение и добавить другое, нажмите "Добавить".
Чтобы принять ограничение и вернуться в диалоговое окно "Параметры решателя", нажмите кнопку "ОК".
Примечание. Отношения int, bin и dif можно применять только в ограничениях для ячеек переменных решения.
Вы можете изменить или удалить существующее ограничение, выполнив следующие действия:
В диалоговом окне "Параметры решателя" щелкните ограничение, которое вы хотите изменить или удалить.
Нажмите «Изменить», а затем внесите изменения или нажмите «Удалить».
Нажмите "Решить" и выполните одно из следующих действий:
Чтобы сохранить значения решения на листе, в диалоговом окне "Результаты решателя" нажмите "Сохранить решение решателя".
Чтобы восстановить исходные значения до того, как вы нажали "Решить", нажмите "Восстановить исходные значения".
Вы можете прервать процесс решения, нажав клавишу Esc.Excel пересчитывает рабочий лист с последними значениями, найденными для ячеек переменной решения.
Чтобы создать отчет на основе вашего решения после того, как Solver найдет решение, вы можете выбрать тип отчета в поле «Отчеты», а затем нажать «ОК». Отчет создается на новом листе в вашей книге. Если Solver не находит решения, доступны только определенные отчеты или отчеты отсутствуют.
Чтобы сохранить значения ячеек переменной решения в качестве сценария, который можно отобразить позже, нажмите «Сохранить сценарий» в диалоговом окне «Результаты решения», а затем введите имя сценария в поле «Имя сценария».
После того, как вы определите проблему, нажмите «Параметры» в диалоговом окне «Параметры решателя».
В диалоговом окне "Параметры" установите флажок "Показать результаты итерации", чтобы просмотреть значения каждого пробного решения, а затем нажмите "ОК".
В диалоговом окне "Параметры решения" нажмите "Решить".
В диалоговом окне "Показать пробное решение" выполните одно из следующих действий:
Чтобы остановить процесс решения и отобразить диалоговое окно "Результаты решения", нажмите "Стоп".
Чтобы продолжить процесс решения и отобразить следующее пробное решение, нажмите "Продолжить".
В диалоговом окне "Параметры решателя" нажмите "Параметры".
Выберите или введите значения для любых параметров на вкладках All Methods, GRG Nonlinear и Evolutionary в диалоговом окне.
В диалоговом окне "Параметры решателя" нажмите "Загрузить/Сохранить".
Введите диапазон ячеек для области модели и нажмите "Сохранить" или "Загрузить".
При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в которую вы хотите поместить проблемную модель. При загрузке модели введите ссылку для всего диапазона ячеек, содержащих модель проблемы.
Совет. Вы можете сохранить последние выборки в диалоговом окне "Параметры решателя" с рабочим листом, сохранив рабочую книгу. Каждый рабочий лист в рабочей книге может иметь собственный выбор Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для рабочего листа, нажав Загрузить/Сохранить, чтобы сохранить проблемы по отдельности.
Вы можете выбрать любой из следующих трех алгоритмов или методов решения в диалоговом окне "Параметры решателя":
Обобщенный редуцированный градиент (GRG) Нелинейный Используйте для задач, которые являются гладкими нелинейными.
LP Simplex Используйте для линейных задач.
Эволюционное использование для негладких задач.
Важно! Сначала необходимо включить надстройку Solver. Дополнительные сведения см. в разделе Загрузка надстройки «Поиск решения».
В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму дохода от продаж, сопутствующие расходы и прибыль. Решатель может изменять ежеквартальные бюджеты на рекламу (ячейки переменной решения B5:C5) вплоть до ограничения общего бюджета в 20 000 долларов США (ячейка D5), пока общая прибыль (целевая ячейка D7) не достигнет максимально возможной суммы. Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы D7, =СУММ(Прибыль Q1:Прибыль Q2).
После запуска Solver новые значения выглядят следующим образом.
В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».
В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".
В поле "Установить цель" введите ссылку на ячейку или имя целевой ячейки.
Примечание. Целевая ячейка должна содержать формулу.
Выполните одно из следующих действий:
Сделайте значение целевой ячейки как можно большим
Сделайте значение целевой ячейки как можно меньше
Установите в целевой ячейке определенное значение
Нажмите "Значение" и введите значение в поле.
В поле Путем изменения ячеек переменной введите имя или ссылку для каждого диапазона ячеек переменной решения. Разделяйте несмежные ссылки запятыми.
Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Вы можете указать до 200 переменных ячеек.
В поле Тема ограничений добавьте любые ограничения, которые вы хотите применить.
Чтобы добавить ограничение, выполните следующие действия:
В диалоговом окне "Параметры решателя" нажмите "Добавить".
В поле "Ссылка на ячейку" введите ссылку на ячейку или имя диапазона ячеек, для которого вы хотите ограничить значение.
На знаке = в поле Ограничение введите число, ссылку или имя ячейки или формулу.
Примечание. Вы можете применять отношения int, bin и dif только в ограничениях для ячеек переменных решения.
Выполните одно из следующих действий:
Принять ограничение и добавить другое
Примите ограничение и вернитесь в диалоговое окно "Параметры решателя"
Нажмите "Решить" и выполните одно из следующих действий:
Сохранить значения решения на листе
Нажмите «Сохранить решение решателя» в диалоговом окне «Результаты решателя».
Восстановить исходные данные
Нажмите "Восстановить исходные значения".
Чтобы прервать процесс решения, нажмите ESC . Excel пересчитывает лист с последними найденными значениями для настраиваемых ячеек.
Чтобы создать отчет на основе вашего решения после того, как Solver найдет решение, вы можете выбрать тип отчета в поле «Отчеты», а затем нажать «ОК». Отчет создается на новом листе рабочей книги. Если Solver не находит решения, возможность создания отчета недоступна.
Чтобы сохранить корректирующие значения ячеек в виде сценария, который вы сможете отобразить позже, нажмите «Сохранить сценарий» в диалоговом окне «Результаты решения», а затем введите имя сценария в поле «Имя сценария».
В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».
В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".
После определения проблемы в диалоговом окне "Параметры решателя" нажмите "Параметры".
Установите флажок "Показать результаты итерации", чтобы увидеть значения каждого пробного решения, а затем нажмите "ОК".
В диалоговом окне "Параметры решения" нажмите "Решить".
В диалоговом окне "Показать пробное решение" выполните одно из следующих действий:
Остановите процесс решения и отобразите диалоговое окно "Результаты решения"
Нажмите "Стоп".
Продолжить процесс решения и отобразить следующее пробное решение
Нажмите "Продолжить".
В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».
В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".
Нажмите «Параметры», а затем в диалоговом окне «Параметры» или «Параметры решения» выберите один или несколько следующих параметров:
Установите время решения и количество итераций
На вкладке "Все методы" в разделе "Пределы решения" в поле "Максимальное время (секунды)" введите количество секунд, которое вы хотите отвести на время решения. Затем в поле Итерации введите максимально допустимое количество итераций.
Примечание. Если процесс решения достигает максимального времени или количества итераций, прежде чем Solver найдет решение, Solver отобразит диалоговое окно Show Trial Solution.
Установите степень точности
На вкладке "Все методы" в поле "Точность ограничения" введите требуемую степень точности. Чем меньше число, тем выше точность.
Установите степень сходимости
На вкладке GRG Nonlinear или Evolutionary в поле Convergence введите количество относительных изменений, которые вы хотите разрешить в последних пяти итерациях, прежде чем Solver остановится с решением. Чем меньше число, тем меньше допустимых относительных изменений.
В диалоговом окне "Параметры решения" нажмите "Решить" или "Закрыть".
В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».
В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".
Нажмите "Загрузить/Сохранить", введите диапазон ячеек для области модели, а затем нажмите "Сохранить" или "Загрузить".
При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в которую вы хотите поместить проблемную модель. При загрузке модели введите ссылку для всего диапазона ячеек, содержащих модель проблемы.
Совет. Вы можете сохранить последние выборки в диалоговом окне "Параметры решателя" с листом, сохранив книгу. У каждого листа в рабочей книге могут быть собственные варианты выбора Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для листа, нажав Загрузить/Сохранить, чтобы сохранить проблемы по отдельности.
В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».
В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".
Во всплывающем меню «Выбор метода решения» выберите один из следующих вариантов:
Метод решения
Описание
GRG (Обобщенный уменьшенный градиент) Нелинейный
Выбор по умолчанию для моделей, использующих большинство функций Excel, кроме ЕСЛИ, ВЫБРАТЬ, ПРОСМОТР и других «пошаговых» функций.
Используйте этот метод для задач линейного программирования. Ваша модель должна использовать СУММ, СУММПРОИЗВ, + - и * в формулах, которые зависят от переменных ячеек.
Этот метод, основанный на генетических алгоритмах, лучше всего подходит, когда ваша модель использует ЕСЛИ, ВЫБОР или ПРОСМОТР с аргументами, которые зависят от переменных ячеек.
Примечание. Часть программного кода Solver защищена авторскими правами Frontline Systems, Inc. с 1990 по 2010 г.Авторские права на отдельные части принадлежат Optimal Methods, Inc., 1989 г.
Поскольку программы-надстройки не поддерживаются в Excel для Интернета, вы не сможете использовать надстройку "Поиск решения" для выполнения анализа "что, если" ваших данных, чтобы помочь вам найти оптимальные решения.
Если у вас есть настольное приложение Excel, вы можете использовать кнопку "Открыть в Excel", чтобы открыть книгу и использовать надстройку "Поиск решения".
Дополнительная помощь по использованию Solver
Для получения более подробной помощи по Солверу обращайтесь:
Части программного кода Solver защищены авторскими правами 1990-2009 гг. Frontline Systems, Inc. Части защищены авторскими правами 1989 г. Optimal Methods, Inc.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Вы хотите решить нелинейное уравнение, требующее итеративного решения, и не знаете, как это сделать в 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 для МЭС и статистических методов соответственно. Сделан вывод о том, что разработанный автоматизированный хлоратор является перспективным устройством на обычных очистных сооружениях поверхностных вод.
В отличие от одновременных линейных уравнений, одновременные нелинейные уравнения нельзя решить с помощью линейной алгебры. Однако мы можем расширить концепцию использования 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 м.
Читайте также: