Решатель VBA

Решатель Excel VBA

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

VBA-решатель

Вы можете использовать это изображение на своем веб-сайте, в шаблонах и т. д. Пожалуйста, предоставьте нам ссылку на авторствоКак предоставить атрибуцию?Ссылка на статью должна быть гиперссылкой
Например:
Источник: VBA Solver (wallstreetmojo.com)

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

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

Использовать СОЛВЕР в excelПОИСК В ExcelРешатель в Excel — это инструмент анализа, который помогает находить решения сложных бизнес-задач, требующих принятия важных решений. Для каждой проблемы определяются цель (задачи), переменные и ограничения. Решатель возвращает оптимальное решение, которое устанавливает точные значения переменных, удовлетворяет всем ограничениям и соответствует цели.
читать далее
во-первых, нам нужно включить эту опцию. Выполните следующие шаги.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

К написать код VBAНапишите код VBAКод VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи.читать далее нам нужно использовать три «функции решателя» в VBA, и это функции «SolverOk, SolverAdd и SolverSolve».

SolverOk

SolverOk

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

Сетцелл: Это будет ссылка на ячейку, которую необходимо изменить, например, ячейка «Прибыль».

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

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

Значение: Этот параметр необходимо указать, если МаксМинВал аргумент 3.

По изменению: Меняя ячейки, нужно решить это уравнение.

СолверДобавить

Теперь посмотрим параметры СолверДобавить

СолверДобавить

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

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

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

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

Вы можете скачать этот шаблон Excel для VBA Solver здесь — Шаблон Excel решателя VBA

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

Пример решателя VBA 1

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

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

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

Хорошо, давайте сейчас решим это уравнение.

Шаг 1: Начать подпроцедура VBAПодпроцедура VBASUB в VBA — это процедура, которая содержит весь код, который автоматически дает оператор end sub, а средняя часть используется для кодирования. Оператор Sub может быть как общедоступным, так и частным, а имя подпроцедуры является обязательным в VBA.читать далее.

Код:

Sub Solver_Example()
End Sub
Пример решателя VBA 1.1

Шаг 2: Сначала нам нужно установить цель ссылка на ячейкуСсылка на ячейкуСсылка на ячейку в Excel ссылается на другие ячейки на ячейку, чтобы использовать ее значения или свойства. Например, если у нас есть данные в ячейке A2 и мы хотим использовать их в ячейке A1, используйте =A2 в ячейке A1, и это скопирует значение A2 в A1.читать далее с помощью SolverOk функция.

Пример решателя VBA 1.2

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

Код:

Sub Solver_Example()

    SolverOk SetCell:=Range("B8")

End Sub
Пример решателя VBA 1.3

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

Код:

Sub Solver_Example()

    SolverOk SetCell:=Range("B8"), MaxMinVal:=3

End Sub
Пример решателя VBA 1.4

Шаг 5: Следующий аргумент Значение значение должно быть 10000.

Код:

Sub Solver_Example()

  SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000

End Sub
Пример решателя VBA 1.5

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

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")

End Sub
Пример решателя VBA 1.5.1

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

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

Пример решателя VBA 1.6

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

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2")

End Sub
Пример решателя VBA 1.7

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

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3

End Sub
Пример 1.8

Шаг 9: Значение этой ячейки должно быть >=7, т.е. Текст формулы = 7.

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7

End Sub
Пример 1.9

Шаг 10: Точно так же одна и та же ячейка должна быть меньше 15, поэтому для этого связь is <= т.е. 1 в качестве значения аргумента.

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7
SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15

End Sub
Пример 1.10

Шаг 11: Первая ячейка, т.е. Единицы для продажи, должна быть целым числом, для этого также настройте критерии, как показано ниже.

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7
SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15
SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer"

End Sub
Пример 1.11

Шаг 12: В одном последний шаг, нам нужно добавить функцию SolverSolve.

Код:

Sub Solver_Example()

SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2")
SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7
SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15
SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer"

SolverSolve

End Sub
Пример 1.12

Хорошо, запустите код, нажав клавишу F5, чтобы получить результат.

Когда вы запустите код, вы увидите следующее окно.

Результат решателя

Нажмите Ok, и вы получите результат в виде листа Excel.

Решатель Результат 1

Таким образом, чтобы получить прибыль в размере 10 000, нам нужно продать 5 000 единиц по цене 7 за каждую, где себестоимость равна 5.

То, что нужно запомнить

  • Чтобы работать с Solver в Excel и VBA, сначала включите его для рабочего листа, а затем включите для ссылки VBA.
  • Как только он будет включен как на рабочих листах, так и на VBA, только мы сможем получить доступ ко всем функциям Solver.

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.