Търсене на цел на 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.

  • Обхват (): В това трябва да предоставим референтната клетка там, където трябва да постигнем целевата стойност.
  • Цел: В този аргумент трябва да въведем каква е целта, която се опитваме да постигнем.
  • Промяна на клетката: В този аргумент трябва да предоставим чрез промяна коя стойност на клетката ни е необходима за постигане на целта.

Примери за Excel VBA Goal Seek

Следват примери за търсене на цели в Excel VBA.

Можете да изтеглите този шаблон на VBA за търсене на Excel оттук - Шаблон за VBA за търсене на цели в Excel

Търсене на цел на VBA - Пример # 1

Да вземем за пример само средния резултат от изпит. По-долу е очакваният резултат от 5 предмета от попълнения изпит.

Първо, трябва да стигнем до това какъв е средният резултат от попълнените 5 предмета. Приложете функцията AVERAGE в клетка B8.

В този пример нашата цел е 90, променящата се клетка ще бъде B7 . Така че Goal Seek ще ни помогне да намерим целевия резултат от крайния предмет, за да постигнем общата средна стойност от 90.

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

Код:

 Sub Goal_Seek_Example1 () Край Sub 

Сега се нуждаем от резултата в клетка B8, така че предоставете тази препратка към диапазона, като използвате RANGE обект.

Код:

 Sub Goal_Seek_Example1 () Диапазон ("B8") Край Sub 

Сега поставете точка и въведете опцията „Търсене на цел“.

Първият аргумент е „Цел“, за това трябва да въведем крайната си цел, за да стигнем до RANGE B8. В този пример се опитваме да постигнем целта от 90.

Код:

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

Следващият аргумент е „Промяна на клетката“ за това трябва да предоставим в коя клетка се нуждаем от новата стойност за постигане на целта.

Код:

 Sub Goal_Seek_Example1 () Обхват ("B8"). GoalSeek Goal: = 90, ChangingCell: = Range ("B7") End Sub 

В този пример нашата променяща се клетка е Sub 6 клетка, т.е. B7 клетка.

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

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

Търсене на цел на VBA - Пример # 2

Научихме как да прилагаме GOAL SEEK, за да намерим броя, необходим за постигане на целта. Сега ще видим някои усъвършенствани примери за намиране на крайния резултат от изпит за повече от един студент.

По-долу са очакваните резултати от 5 предмета след изпита.

Тъй като намираме целта за повече от един ученик, трябва да използваме цикли, по-долу е кодът за вас.

Код:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell 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.

Така че получихме крайния резултат сега като,

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.