photosdematures.com

Cercar objectius a Excel VBA

Objectiu de cerca és l'eina disponible a Excel VBA que ens ajuda a trobar el nombre necessari per assolir l'objectiu establert.

Per exemple, sou estudiant i us heu apuntat a una puntuació mitjana del 90% de sis assignatures disponibles. A hores d'ara ja heu completat 5 exàmens i només us queda una assignatura, les puntuacions previstes de cinc assignatures completades són 89, 88, 91, 87, 89 i 90. Ara voleu saber quant heu de puntuar l'examen final per assolir l'objectiu percentual mitjà global del 90%.

Això es pot fer utilitzant GOAL SEEK al full de treball Excel, així com a la codificació VBA. Vegem com funciona amb VBA.

Sintaxi de cerca d'objectius de VBA

A VBA Goal Seek hem d’especificar el valor que estem canviant i arribar al resultat objectiu final, de manera que proporcioneu la referència de cel·la mitjançant l’objecte VBA RANGE, més endavant podem accedir a l’opció GOAL SEEK.

A continuació es mostra la sintaxi de la cerca d'objectius a VBA.

• Rang (): En això, hem de proporcionar la referència de cel·la on hem d’assolir el valor objectiu.
• Objectiu: En aquest argument, hem d’introduir quin és l’objectiu que intentem assolir.
• Canvi de cel·la: En aquest argument, hem de subministrar canviant el valor de la cèl·lula que necessitem per assolir l'objectiu.

Exemples de cerca d'objectius de VBA d'Excel

A continuació es mostren els exemples de cerca d'objectius a Excel VBA.

Podeu descarregar aquesta plantilla Excel de VBA Goal Seek aquí: plantilla Excel de VBA Goal Seek

Cerca d'objectius VBA: exemple 1

Prenguem l’exemple de la puntuació mitjana de l’examen. A continuació es mostra la puntuació prevista de 5 assignatures de l’examen finalitzat.

En primer lloc, hem d’arribar a quina és la puntuació mitjana de les 5 assignatures completades. Apliqueu la funció MITJANA a la cel·la B8.

En aquest exemple, el nostre objectiu és 90, el canvi de cel·la serà B7. Per tant, Goal Seek ens ajudarà a trobar la puntuació objectiu de l'assignatura final per assolir la mitjana global de 90.

Inicieu el subprocés al mòdul de classe VBA.

Codi:

 Sub Objectiu_Seek_Exemple1 () Final sub 

Ara necessitem el resultat a la cel·la B8, així que proporcioneu aquesta referència de rang mitjançant l’objecte RANGE.

Codi:

 Sub Objectiu_Seek_Exemple1 () Interval ("B8") Final sub 

Ara poseu un punt i introduïu l'opció "Cerca d'objectius".

El primer argument és "Objectiu". Per a això, hem d'introduir el nostre objectiu final per arribar a la gamma B8. En aquest exemple, intentem assolir l'objectiu de 90.

Codi:

 Sub Goal_Seek_Example1 () Range ("B8"). Objectiu GoalSeek: = 90 Final Sub 

El següent argument és "Canviar de cel·la" per a això, hem de proporcionar en quina cel·la necessitem el nou valor per aconseguir el Objectiu.

Codi:

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

En aquest exemple, la nostra cel·la canviant és la cel·la Sub 6, és a dir, la cel·la B7.

D’acord, executem el codi per veure què cal fer a l’assignatura final per assolir el percentatge mitjà global de 90.

Per tant, en l’assignatura final cal puntuar 95 per obtenir la mitjana global de 90.

Cerca d'objectius VBA: exemple 2

Hem après a aplicar l'objectiu SEEK per trobar el nombre necessari per assolir l'objectiu. Ara veurem algun exemple avançat de trobar la puntuació de l’examen final per a més d’un estudiant.

A continuació es mostren les puntuacions previstes de 5 assignatures després de l’examen.

Com que busquem l'objectiu de més d'un estudiant, hem d'utilitzar bucles, a continuació es mostra el codi per a vosaltres.

Codi:

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

Aquest codi recorrerà totes les puntuacions dels estudiants i arribarà a la puntuació de l’examen final necessària per assolir la mitjana global de 90.

Així que obtenim el resultat final ara,

L’estudiant A ha de puntuar només 83 per obtenir el percentatge global de 90 i l’estudiant D ha de puntuar 93.

Però fixeu-vos en els estudiants B & C que necessiten obtenir 104 cadascun a l’examen final, cosa que no és possible.

Així, mitjançant l’anàlisi GOAL SEEK, podem trobar el nombre requerit per assolir el nombre objectiu a la meitat del projecte o procés.

Coses que cal recordar

• Objectiu de cerca està disponible tant amb eina de fulls de treball com amb eina VBA.
• La cel·la de resultats sempre ha de contenir una fórmula.
• Hem d’introduir el valor de l’objectiu i canviar la referència de la cel·la a l’eina de cerca d’objectius.