VBA målsökning

Målsökning i Excel VBA

Målsökning är verktyget tillgängligt i Excel VBA som hjälper oss att hitta det antal som krävs för att nå det uppsatta målet.

Till exempel är du student och du har riktat dig till en genomsnittlig poäng på 90% från sex tillgängliga ämnen. Från och med nu har du slutfört 5 tentor och du har bara ett ämne, dina förväntade poäng från fem avslutade ämnen är 89, 88, 91, 87, 89 och 90. Nu vill du veta hur mycket du behöver göra poäng i den slutliga undersökningen för att uppnå det totala genomsnittliga procentuella målet på 90%.

Detta kan göras genom att använda GOAL SEEK i Excel-kalkylbladet samt i VBA-kodning. Låt oss se hur det fungerar med VBA.

VBA-målsökande syntax

I VBA Målsökning måste vi ange det värde vi ändrar och nå det slutliga riktade resultatet, så ange cellreferensen med hjälp av VBA RANGE-objektet, senare kan vi komma till GOAL SEEK-alternativet.

Nedan är syntaxen för målsökning i VBA.

  • Range (): I detta måste vi ange cellreferensen där vi behöver för att uppnå det riktade värdet.
  • Mål: I detta argument måste vi ange vad som är målet vi försöker uppnå.
  • Ändra cell: I detta argument måste vi leverera genom att ändra vilket cellvärde vi behöver för att uppnå målet.

Exempel på Excel VBA Målsökning

Följande är exempel på målsökning i Excel VBA.

Du kan ladda ner denna VBA-målsökande Excel-mall här - VBA-målsökande Excel-mall

VBA Målsökning - Exempel nr 1

Låt oss ta exemplet med enbart genomsnittlig poäng. Nedan visas den förväntade poängen på 5 ämnen från den avslutade tentamen.

Först måste vi komma fram till vad som är medelvärdet för de färdiga 5 ämnena. Använd AVERAGE-funktionen i B8-cell.

I det här exemplet är vårt mål 90, att ändra cell blir B7 . Så Goal Seek hjälper oss att hitta den riktade poängen från det sista ämnet för att uppnå det totala genomsnittet på 90.

Starta delproceduren i VBA-klassmodulen.

Koda:

 Sub Goal_Seek_Example1 () Slut Sub 

Nu behöver vi resultatet i B8-celler, så ange denna intervallreferens med hjälp av RANGE-objekt.

Koda:

 Sub Goal_Seek_Example1 () Range ("B8") End Sub 

Sätt nu en punkt och ange alternativet "Målsökning".

Det första argumentet är ”Mål” för detta måste vi ange vårt slutmål för att komma fram till RANGE B8. I det här exemplet försöker vi uppnå målet 90.

Koda:

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

Nästa argument är ”Changing Cell” för detta måste vi tillhandahålla i vilken cell vi behöver det nya värdet för att uppnå målet.

Koda:

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

I detta exempel är vår föränderliga cell Sub 6-cell, dvs. B7-cell.

Ok, låt oss köra koden för att se vad som måste göras i det sista ämnet för att uppnå den totala genomsnittliga procentandelen på 90.

Så i det sista ämnet måste 95 göras för att få det totala genomsnittet på 90.

VBA Målsökning - Exempel # 2

Vi har lärt oss att tillämpa GOAL SEEK för att hitta det antal som krävs för att uppnå målet. Nu kommer vi att se några avancerade exempel på att hitta den slutliga tentamen för mer än en student.

Nedan visas de förväntade poängen på 5 ämnen efter tentamen.

Eftersom vi hittar målet för mer än en elev måste vi använda öglor, nedan är koden för dig.

Koda:

 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 

Den här koden kommer att gå igenom alla elevernas poäng och uppnå den slutliga tentamen som krävs för att uppnå det totala genomsnittet på 90.

Så vi fick slutresultatet nu som,

Student A behöver bara göra poäng 83 för att säkra den totala 90 procenten och Student D behöver göra 93.

Men titta på Student B & C de behöver göra 104 vardera i slutprovet vilket inte är möjligt alls.

Gilla detta med GOAL SEEK-analys kan vi hitta det antal som krävs för att uppnå det riktade antalet mitt i projektet eller processen.

Saker att komma ihåg

  • Målsökning finns med både kalkylverktyg och VBA-verktyg.
  • Resultatcellen ska alltid innehålla en formel.
  • Vi måste ange målvärde och ändra cellreferens till målsökningsverktyget.