ExcelVBAでのゴールシーク
ゴールシークは、Excel VBAで利用可能なツールであり、設定された目標を達成するために達成する必要のある数を見つけるのに役立ちます。
たとえば、あなたは学生であり、6つの利用可能な科目から平均90％のスコアを目標としています。現在、5つの試験を完了し、1つの科目しか残っていないため、5つの完了した科目から予想されるスコアは、89、88、91、87、89、および90です。全体の平均パーセンテージ目標である90％を達成するための最終試験。
これは、ExcelワークシートおよびVBAコーディングでGOALSEEKを使用して実行できます。VBAでどのように機能するか見てみましょう。
VBAゴールシーク構文
VBAゴールシークでは、変更する値を指定して最終的なターゲット結果に到達する必要があるため、VBA RANGEオブジェクトを使用してセル参照を指定すると、後でGOALSEEKオプションにアクセスできます。
以下は、VBAでのゴールシークの構文です。
- Range（）：これでは、目標値を達成するために必要なセル参照を提供する必要があります。
- 目標：この議論では、達成しようとしている目標を入力する必要があります。
- セルの変更：この引数では、目標を達成するために必要なセル値を変更して供給する必要があります。
ExcelVBAゴールシークの例
以下は、ExcelVBAでのゴールシークの例です。
このVBAゴールシークExcelテンプレートはここからダウンロードできます–VBAゴールシークExcelテンプレートVBAゴールシーク–例1
試験の平均スコアのみを例にとってみましょう。以下は、完了した試験からの5人の被験者の予想スコアです。
まず、完了した5つの科目の平均スコアを確認する必要があります。B8セルにAVERAGE関数を適用します。
この例では、目標は90で、セルの変更はB7になります。したがって、ゴールシークは、最終的な科目から目標スコアを見つけて、全体の平均である90を達成するのに役立ちます。
VBAクラスモジュールでサブプロシージャを開始します。
コード：
Sub Goal_Seek_Example1（）End Sub
ここで、B8セルに結果が必要なので、RANGEオブジェクトを使用してこの範囲参照を指定します。
コード：
Sub Goal_Seek_Example1（）Range（ "B8"）End Sub
次に、ドットを入れて「ゴールシーク」オプションを入力します。
最初の議論は「目標」です。これについては、範囲B8に到達するために最終目標を入力する必要があります。この例では、90という目標を達成しようとしています。
コード：
Sub Goal_Seek_Example1（）Range（ "B8"）。GoalSeek Goal：= 90 End Sub
次の議論は「セルの変更」です。これには、目標を達成するために新しい値が必要なセルを指定する必要があります。
コード：
Sub Goal_Seek_Example1（）Range（ "B8"）。GoalSeek Goal：= 90、ChangingCell：= Range（ "B7"）End Sub
この例では、変化するセルはサブ6セル、つまりB7セルです。
さて、コードを実行して、全体の平均パーセンテージ90を達成するために、最終的な主題で何をする必要があるかを確認しましょう。
したがって、最終的な科目では、全体の平均を90にするために、95をスコアリングする必要があります。
VBAゴールシーク–例2
We have learned how to apply GOAL SEEK to find the number required to achieve the goal. Now we will see some advanced example of finding the final examination score for more than one student.
Below are the anticipated scores of 5 subjects after the exam.
Since we are finding the goal for more than one student we need to use loops, below is the code for you.
Code:
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
This code will loop through all the students’ scores and arrive final examination score required to achieve the overall average of 90.
So we got the end result now as,
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.