Поиск цели VBA | Как использовать поиск цели для поиска ценности в Excel VBA?

Поиск цели в Excel VBA

Goal Seek - это инструмент, доступный в Excel VBA, который помогает нам найти необходимое число, которое необходимо достичь, чтобы достичь поставленной цели.

Например, вы студент и получили целевой средний балл 90% по шести доступным предметам. На данный момент вы сдали 5 экзаменов, и у вас остался только один предмет, ваши ожидаемые баллы по пяти завершенным предметам составляют 89, 88, 91, 87, 89 и 90. Теперь вы хотите знать, сколько вам нужно набрать по заключительный экзамен для достижения общей средней процентной цели 90%.

Это можно сделать, используя GOAL SEEK на листе Excel, а также в кодировке VBA. Посмотрим, как это работает с VBA.

Синтаксис поиска цели VBA

В VBA Goal Seek нам нужно указать значение, которое мы изменяем, и прийти к окончательному целевому результату, поэтому укажите ссылку на ячейку с помощью объекта VBA RANGE, позже мы сможем получить доступ к опции GOAL SEEK.

Ниже приведен синтаксис поиска цели в VBA.

  • Range (): здесь нам нужно предоставить ссылку на ячейку, где нам нужно достичь целевого значения.
  • Цель: в этом аргументе нам нужно указать, какую цель мы пытаемся достичь.
  • Изменение ячейки: в этом аргументе мы должны предоставить, изменив значение ячейки, которое нам нужно для достижения цели.

Примеры поиска цели в Excel VBA

Ниже приведены примеры поиска цели в Excel VBA.

Вы можете скачать этот шаблон Excel для поиска цели VBA здесь - Шаблон Excel для поиска цели VBA

Поиск цели VBA - пример №1

Возьмем только средний балл экзамена. Ниже приведены ожидаемые баллы по 5 предметам на завершенном экзамене.

Во-первых, нам нужно определить средний балл по завершенным 5 предметам. Примените функцию СРЕДНИЙ в ячейке B8.

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

Запустите подпроцедуру в модуле класса VBA.

Код:

 Дополнительная цель_Просмотр_Пример1 () Конец подпрограммы

Теперь нам нужен результат в ячейке B8, поэтому предоставьте эту ссылку на диапазон с помощью объекта RANGE.

Код:

 Дополнительная цель_Просмотр_Пример1 () Диапазон ("B8") Конец подпрограммы

Теперь поставьте точку и войдите в опцию «Goal Seek».

Первый аргумент - «Цель», для этого нам нужно ввести нашу конечную цель, чтобы попасть в ДИАПАЗОН B8. В этом примере мы пытаемся достичь цели 90.

Код:

 Sub Goal_Seek_Example1 () Диапазон ("B8"). GoalSeek Goal: = 90 End Sub

Следующим аргументом является «Изменение ячейки», для этого нам нужно указать, в какой ячейке нам нужно новое значение для достижения цели.

Код:

 Sub Goal_Seek_Example1 () Range ("B8"). GoalSeek Goal: = 90, ChangingCell: = Range ("B7") End Sub

В этом примере наша изменяющаяся ячейка - это ячейка Sub 6, то есть ячейка B7.

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

Итак, по последнему предмету нужно набрать 95 баллов, чтобы получить общее среднее значение 90.

Поиск цели VBA - пример №2

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

Ниже приведены ожидаемые баллы по 5 предметам после экзамена.

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

Код:

 SubGoal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim ChangingCell As Range Dim TargetScore As Integer TargetScore = 90 Для k = 2–5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

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

Итак, мы получили конечный результат:

Student A needs to score just 83 to secure the overall 90 percentage and Student D needs to score 93.

But look at Student B & C they need to score 104 each in the final examination which is not possible at all.

Like this using GOAL SEEK analysis we can find the required number to achieve the targeted number mid through the project or process.

Things to Remember

  • Goal Seek is available with both worksheet tool as well as VBA tool.
  • Result cell should always contain a formula.
  • We need to enter goal value and changing cell reference to the goal seek tool.