Пошук цілей VBA | Як за допомогою Goal Seek знайти значення в 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.

  • Діапазон (): Для цього нам потрібно надати посилання на клітинку там, де нам потрібно досягти цільового значення.
  • Мета: У цьому аргументі нам потрібно вказати, яку мету ми намагаємось досягти.
  • Зміна клітинки: У цьому аргументі нам потрібно запропонувати, змінивши значення клітинки, яке нам потрібно для досягнення мети.

Приклади пошуку цілей Excel VBA

Нижче наведено приклади пошуку мети в Excel VBA.

Завантажити цей шаблон VBA Goal Seek Excel можна тут - Шаблон Excel VBA Goal Seek

Пошук цілей VBA - Приклад №1

Візьмемо для прикладу лише середній бал іспиту. Нижче наведено очікуваний бал з 5 предметів із складеного іспиту.

По-перше, нам потрібно визначити, який середній бал складають 5 виконаних предметів. Застосувати функцію AVERAGE у клітинці B8.

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

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

Код:

 Підціль цілі_Пошук_Приклад1 () Кінець Під

Тепер нам потрібен результат у комірці B8, тому вкажіть це посилання на діапазон, використовуючи об’єкт RANGE.

Код:

 Підціль цілі_Пошук_Приклад1 () Діапазон ("B8") Кінець Під

Тепер поставте крапку та введіть опцію "Шукання мети".

Перший аргумент - "Ціль", для цього нам потрібно ввести свою кінцеву мету, щоб досягти діапазону B8. У цьому прикладі ми намагаємось досягти цілі 90.

Код:

 Підціль Goal_Seek_Example1 () Діапазон ("B8"). GoalSeek Ціль: = 90 Кінець Sub

Наступним аргументом є "Зміна комірки", для цього нам потрібно вказати, в якій клітинці нам потрібне нове значення для досягнення Цілі.

Код:

 Підціль Goal_Seek_Example1 () Діапазон ("B8"). GoalSeek Ціль: = 90, ChangingCell: = Діапазон ("B7") Кінець Sub

У цьому прикладі наша мінлива комірка - це клітина Sub 6, тобто клітина B7.

Добре, давайте запустимо код, щоб побачити, що потрібно зробити в кінцевій темі, щоб досягти загального середнього відсотка 90.

Отже, у фінальній темі 95 потрібно набрати бал, щоб отримати загальне середнє значення 90.

Пошук цілей VBA - Приклад №2

Ми навчились застосовувати GOAL SEEK, щоб знайти кількість, необхідну для досягнення мети. Зараз ми побачимо кілька просунутих прикладів пошуку підсумкової оцінки для іспиту для більш ніж одного студента.

Нижче наведені очікувані бали з 5 предметів після іспиту.

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

Код:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResceCell As Range Dim ChangingCell As Range Dim TargetScore As Integer TargetScore = 90 For k = 2 to 5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

Цей код буде циклічно перераховувати всі бали студентів та отримувати остаточну оцінку іспиту, необхідну для досягнення загального середнього значення 90.

Отже, ми отримали кінцевий результат, як,

Студент А повинен набрати лише 83 бали, щоб забезпечити загальний 90 відсотків, а студент Д - 93.

Але подивіться на студентські B & C, вони повинні набрати 104 кожного на підсумковому іспиті, що взагалі неможливо.

Подібно до цього, використовуючи аналіз GOAL SEEK, ми можемо знайти необхідну кількість для досягнення цільової кількості в середині проекту або процесу.

Що слід пам’ятати

  • Пошук цілей доступний як з інструментом робочого аркуша, так і з інструментом VBA.
  • Осередок результату завжди повинен містити формулу.
  • Нам потрібно ввести значення цілі та змінити посилання на клітинку до інструменту пошуку мети.