Макрос найти решение в Excel

Возвращаемое значение SolverSolve

Пример

В этом примере функции Решателя используются для максимизации валовой прибыли при решении бизнес-задачи. Функция SolverSolve начинает выполнение решения Solver. Решатель вызывает функцию ShowTrial при возникновении любого из пяти описанных выше условий; функция просто отображает сообщение с целочисленным значением от 1 до 5.

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

В этой статье приведено несколько примеров того, как использовать решатель Excel и вызывать его с помощью макроса VBA. Если вы хотите поэкспериментировать с использованием Решателя на финансовых моделях, вы можете попробовать его на одном из моих финансовых калькуляторов, например, на калькуляторе ипотечного кредита. Встроенного в Excel инструмента Goal Seek часто бывает достаточно для решения x при заданном y, но когда вы хотите добавить несколько критериев и разрешить изменение нескольких входных данных, именно здесь приходит Solver дюймов.

Примечание. Вам не нужно загружать надстройку Solver. Если вы не видите его как один из пунктов меню в меню «Инструменты» (в Excel), вам нужно перейти в «Инструменты»> «Надстройки». и установите флажок "Добавить Solver".

Примеры решения Excel

Пример 1: «Нахождение локального минимума с помощью решателя Excel»

Наш первый пример будет очень простым, но он представит общие термины, используемые в оптимизации, такие как целевая функция, переменные проекта и ограничения. Допустим, у нас есть следующее уравнение, и мы хотим найти значение x, которое минимизирует f с учетом -1 Рис. 1: Скриншот примера задачи 1.

Ячейка B11 ​​(целевая функция): =B15^2-B15+2

Чтобы использовать надстройку решателя Excel (Инструменты > Решатель . ), мы выбираем нашу целевую функцию, ячейку B11, в качестве «Целевой ячейки» и выбираем параметр «Минимум» (см. рис. 2 ниже). Наша единственная проектная переменная — x, поэтому единственная ячейка, которую мы собираемся изменить, — это B15. После добавления двух ограничений мы нажимаем кнопку «Решить» и находим наш ответ (x=0,5).

Рисунок 2. Снимок экрана: добавление решателя в диалоговое окно для примера задачи 1.

Пример 2: "Решение системы нелинейных уравнений"

В следующей практической задаче решатель используется для поиска значений неизвестных углов ( q ₂ и q ₃) в следующей системе уравнений.

Обратите внимание, что эти уравнения имеют неявную форму (равны нулю). Чтобы решить систему, мы создадим целевую функцию, которая при минимизации сводит оба уравнения к нулю. Этого можно добиться, минимизируя сумму квадратов каждого неявного уравнения.

Схема решения этой задачи показана на снимке экрана ниже. Известные переменные называются переменными анализа и будут рассматриваться как константы. Наши неизвестные, q ₂ и q ₃, являются переменными проекта. Для этого примера задачи у нас нет никаких ограничений.

Рисунок 3: Скриншот примера проблемы 2.

Есть только ОДНО решение?

На снимке экрана выше показано одно решение проблемы, но решение будет зависеть от начальных значений, которые вы выбрали для неизвестных углов. Например, попробуйте использовать начальные значения q ₂ = -30 градусов и q ₃ = 0 градусов. Вы должны получить другое решение! На рисунке ниже показан пример механизма, который можно описать с помощью этих уравнений. Второе решение представлено пунктирными линиями.

Рисунок 4. Две возможные конфигурации механизма.

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

Запуск решателя с помощью макроса VBA

Если вам нужно решить одну и ту же систему уравнений или запустить процедуру оптимизации несколько раз, используя одну и ту же модель, удобно создать макрос, который можно запускать нажатием одной кнопки. Простой способ настроить это — сначала записать все шаги, используемые для настройки и запуска решателя. Давайте воспользуемся задачей из примера 2 выше.

• Шаг 1. Запустите средство записи макросов ("Инструменты" > "Макрос" > "Записать новый макрос" ).
• Шаг 2. Откройте диалоговое окно решателя (Инструменты > Решатель . ).
• Шаг 3. Очистите все существующие настройки решателя (нажмите кнопку «Сбросить все»).
• Шаг 4. Выберите целевую ячейку, переменные проекта и ограничения и нажмите кнопку «Решить». Затем нажмите OK, чтобы принять результаты.
• Шаг 5. Остановите запись макросов ("Инструменты" > "Макрос" > "Остановить запись").
• Шаг 6. Добавьте кнопку на лист с помощью кнопки на панели инструментов «Формы». (Если панель инструментов «Формы» не отображается, щелкните правой кнопкой мыши любую панель инструментов и выберите «Формы».)
• Шаг 7. Назначьте созданный вами макрос кнопке. (Щелкните правой кнопкой мыши кнопку и выберите «Назначить макрос».)

Прежде чем макрос заработает, необходимо добавить ссылку на функции надстройки Solver VBA.

• Шаг 1. Отредактируйте только что созданный макрос (Инструменты > Макрос > Макросы или Alt+F8). Это откроет Visual Basic. Вы также можете нажать Alt + F11, чтобы открыть VBA.
• Шаг 2. Добавьте ссылку на решение в Visual Basic (Инструменты > Ссылки, затем убедитесь, что установлен флажок ПОИСК).

Код VBA для макроса Solver, который был записан для примера 2, показан ниже.

Чтобы диалоговое окно "Результаты решателя" не отображалось, в функцию SolverSolve был добавлен параметр userFinish:=True. Чтобы получить дополнительные сведения об использовании функций решения в VBA, выполните поиск по слову "решатель" в справочной системе VBA.

Ссылки

Процитировать эту статью

Чтобы сослаться на эту статью на своем веб-сайте или в блоге, используйте ссылку, аналогичную следующей:

Поиск решения – это надстройка, входящая в состав Excel, которая используется для выполнения анализа "что, если" путем предоставления альтернативных ответов на формулу в ячейке на основе значений, которые вы можете передать в формулу из других ячеек в вашем рабочая тетрадь.

Включение надстройки «Поиск решения» в Excel

Выберите файл на ленте Excel, а затем перейдите к параметрам.

Выберите «Надстройки» и нажмите кнопку «Перейти» рядом с надстройками Excel.

Убедитесь, что выбрана надстройка Solver.

Кроме того, нажмите надстройки Excel на ленте разработчика, чтобы открыть диалоговое окно надстроек.

Включение надстройки Solver в VBA

После того как вы включили надстройку Solver в Excel, вам нужно добавить ссылку на нее в свой проект VBA, чтобы использовать ее в VBA.

Убедитесь, что вы щелкнули в проекте VBA, где вы хотите использовать Solver. Нажмите на меню "Инструменты", а затем на "Ссылки".

В ваш проект будет добавлена ​​ссылка на надстройку Solver.

Теперь вы можете использовать надстройку Solver в коде VBA!

Использование функций решения в VBA

Нам нужно использовать 3 функции Solver VBA, чтобы использовать Solver в VBA. Это SolverOK, SolverAdd и SolverSolve.

Поиск решения

• SetCell — необязательный — это должно относиться к ячейке, которую нужно изменить, — она должна содержать формулу. Это соответствует полю «Установить целевую ячейку» в диалоговом окне «Параметры решателя».
• MaxMinVal — необязательный — можно установить значение 1 (развернуть), 2 (свернуть) или 3. Это соответствует параметрам Max, Min и Value в диалоговом окне Solver Parameters.< /li>
• ValueOf — необязательный. Если для MaxMinValue установлено значение 3, вам необходимо указать этот аргумент.
• ByChange — необязательный. Это сообщает решающей программе, какие ячейки он может изменить, чтобы получить требуемое значение. Это соответствует полю «Изменение ячеек переменных» в диалоговом окне «Параметры решателя».
• Engine — необязательно — указывает метод решения, который необходимо использовать для получения решения. 1 для метода Simplex LP, 2 для нелинейного метода GRG или 3 для эволюционного метода. Это соответствует раскрывающемуся списку Select a Solving Method в диалоговом окне Solver Parameters.
• EngineDesc — необязательный — это альтернативный способ выбора метода решения — здесь вы должны ввести строки «Simplex LP», «GRG Nonlinear» или «Evolutionary». Это также соответствует раскрывающемуся списку Select a Solving Method в диалоговом окне Solver Parameters.

Поиск решения

• CellRef — обязательно — это ссылка на ячейку или диапазон ячеек, которые необходимо изменить для решения проблемы.
• Отношение – обязательно – это целое число, которое должно быть от 1 до 6 и указывает разрешенное логическое отношение.
  • 1 меньше (=)
  • 4 должно иметь целые окончательные значения.
  • 5 должен иметь значения от 0 до 1.
  • 6 должен иметь окончательные значения, которые все разные и целые числа.

  Создание примера решателя

  Рассмотрите следующий рабочий лист.

  На приведенном выше листе нам нужно добиться безубыточности в первом месяце, установив в ячейке B14 нулевое значение, изменив критерии в ячейках F1 на F6.

  Как вы решаете сложные проблемы? Если вы не знаете, как решить эти проблемы, не о чем беспокоиться, у нас есть решение в нашем Excel. В нашей предыдущей статье «Решатель Excel» мы узнали, как решать уравнения в Excel. Если вы не в курсе, «SOLVER» доступен и в VBA. В этой статье мы расскажем вам, как использовать «Поиск решения» в VBA.

  Включить решатель на листе

  Решатель — это скрытый инструмент, доступный на вкладке данных в Excel (если он уже включен).

  Шаг 1. Перейдите на вкладку ФАЙЛ. На вкладке ФАЙЛ выберите «Параметры».

  Шаг 2. В окне параметров Excel выберите «Надстройки».

  Шаг 3. Внизу выберите «Надстройки Excel» и нажмите «Перейти».

  Шаг 4. Теперь установите флажок «Надстройка Solver» и нажмите «ОК».

  Теперь вы должны увидеть «Решатель» на вкладке данных.

  Включить решатель в VBA

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

  Шаг 1. Перейдите в Инструменты >>> Справочник в окне редактора Visual Basic.

  Шаг 2. В списке ссылок выберите «Решатель» и нажмите «ОК», чтобы использовать его.

  Теперь мы можем использовать Solver и в VBA.

  Функции решения в VBA

  Поиск решения

  SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

  SetCell: это будет ссылка на ячейку, которую необходимо изменить, т. е. ячейка "Прибыль".

  MaxMinVal: это необязательный параметр, ниже приведены числа и спецификаторы.

  • 1 = Развернуть
  • 2 = Свернуть
  • 3 = соответствует определенному значению

  ValueOf: этот параметр необходимо указать, если аргумент MaxMinVal равен 3.

  ByChange: Путем изменения ячеек необходимо решить это уравнение.

  Поиск решения

  Теперь давайте посмотрим на параметры SolverAdd

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

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

  • 1 меньше (=)
  • 4 должно иметь целые окончательные значения.
  • 5 должен иметь значения от 0 до 1.
  • 6 должен иметь окончательные значения, которые все разные и целые числа.

  Пример решателя в Excel VBA

  Для примера посмотрите на приведенный ниже сценарий.

  Используя эту таблицу, нам нужно определить сумму «Прибыли», которая должна быть не менее 10000. Чтобы получить это число, у нас есть определенные условия.

  • Количество единиц для продажи должно быть целым числом.
  • Цена за единицу должна быть от 7 до 15.

  Исходя из этих условий, нам нужно определить, сколько единиц нужно продать по какой цене, чтобы получить прибыль в размере 10 000.

  Хорошо, а теперь решим это уравнение.

  Код:

  Шаг 3: Первый аргумент этой функции — «SetCell», в этом примере нам нужно изменить значение ячейки «Прибыль», т. е. ячейки B8.

  Код:

  Шаг 4. Теперь нам нужно установить для этой ячейки значение 10000, поэтому для MaxMinVal используйте 3 в качестве значения аргумента.

  Код:

  Шаг 5. Значение следующего аргумента ValueOf должно быть равно 10000.

  Код:

  Следующий аргумент — ByChange, т. е. путем изменения ячеек, которые необходимо решить для этого уравнения. В этом случае необходимо изменить ячейки «Единицы» на «Продажа» (B1) и «Цена за единицу» (B2).

  Код:

  Примечание: остальные аргументы здесь не требуются.

  Шаг 6. После того, как целевая ячейка установлена, теперь нам нужно построить другие критерии. Для этого откройте функцию SolverAdd.

  Шаг 7. Первая ссылка на ячейку, которую нам нужно изменить, — это цена за единицу, т. е. ячейка B2.

  Код:

  Шаг 8. Значение этой ячейки должно быть >= 7, поэтому аргумент Relation будет равен 3.

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

    
  • Как удалить morphvox pro с компьютера, если он не удаляется
    
  • ПО для восстановления данных с SSD
    
  • Как построить график функции в Excel
    
  • Как добавить конструктор в Excel
    
  • Приложение для переноса данных с iphone на компьютер