Ricerca obiettivo VBA

Ricerca obiettivo in Excel VBA

Goal Seek è lo strumento disponibile in excel VBA che ci aiuta a trovare il numero richiesto da raggiungere per arrivare al target prefissato.

Ad esempio, sei uno studente e hai raggiunto un punteggio medio del 90% tra sei materie disponibili. A partire da ora hai completato 5 esami e ti rimane solo una materia, i tuoi punteggi previsti da cinque materie completate sono 89, 88, 91, 87, 89 e 90. Ora vuoi sapere quanto devi segnare in l'esame finale per il raggiungimento dell'obiettivo percentuale medio complessivo del 90%.

Questo può essere fatto utilizzando GOAL SEEK nel foglio di lavoro Excel e nella codifica VBA. Vediamo come funziona con VBA.

Sintassi VBA Goal Seek

In VBA Goal Seek dobbiamo specificare il valore che stiamo modificando e arrivare al risultato finale mirato, quindi fornire il riferimento di cella utilizzando l'oggetto VBA RANGE, in seguito possiamo accedere all'opzione GOAL SEEK.

Di seguito è riportata la sintassi della ricerca dell'obiettivo in VBA.

  • Range (): in questo caso, dobbiamo fornire il riferimento di cella in cui dobbiamo ottenere il valore target.
  • Obiettivo: in questo argomento, dobbiamo inserire qual è l'obiettivo che stiamo cercando di raggiungere.
  • Cambiare cella: in questo argomento, dobbiamo fornire cambiando il valore della cella di cui abbiamo bisogno per raggiungere l'obiettivo.

Esempi di Excel VBA Goal Seek

Di seguito sono riportati gli esempi di ricerca dell'obiettivo in Excel VBA.

Puoi scaricare questo modello VBA Goal Seek Excel qui - Modello VBA Goal Seek Excel

Ricerca obiettivo VBA - Esempio n. 1

Prendiamo solo l'esempio del punteggio medio dell'esame. Di seguito è riportato il punteggio previsto di 5 soggetti dall'esame completato.

Per prima cosa, dobbiamo arrivare a qual è il punteggio medio dei 5 soggetti completati. Applica la funzione MEDIA nella cella B8.

In questo esempio il nostro obiettivo è 90, il cambio di cella sarà B7 . Quindi Goal Seek ci aiuterà a trovare il punteggio mirato dal soggetto finale per raggiungere la media complessiva di 90.

Avvia la sottoprocedura nel modulo di classe VBA.

Codice:

 Sub Goal_Seek_Example1 () End Sub

Ora abbiamo bisogno del risultato nella cella B8, quindi fornisci questo riferimento di intervallo utilizzando l'oggetto RANGE.

Codice:

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

Ora metti un punto e inserisci l'opzione "Goal Seek".

Il primo argomento è "Obiettivo" per questo dobbiamo inserire il nostro obiettivo finale per arrivare in RANGE B8. In questo esempio, stiamo cercando di raggiungere l'obiettivo di 90.

Codice:

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

Il prossimo argomento è "Cambiare cella" per questo dobbiamo fornire in quale cella abbiamo bisogno del nuovo valore per raggiungere l' obiettivo.

Codice:

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

In questo esempio, la nostra cella che cambia è la cella Sub 6, ovvero la cella B7.

Ok, eseguiamo il codice per vedere cosa è necessario fare nell'argomento finale per ottenere la percentuale media complessiva di 90.

Quindi, nel soggetto finale si deve ottenere un punteggio di 95 per ottenere la media complessiva di 90.

Ricerca obiettivo VBA - Esempio n. 2

Abbiamo imparato come applicare GOAL SEEK per trovare il numero necessario per raggiungere l'obiettivo. Ora vedremo alcuni esempi avanzati di come trovare il punteggio finale dell'esame per più di uno studente.

Di seguito sono riportati i punteggi previsti di 5 soggetti dopo l'esame.

Poiché stiamo trovando l'obiettivo per più di uno studente, dobbiamo utilizzare i loop, di seguito è riportato il codice per te.

Codice:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim ChangingCell As Range Dim Target Score 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

Questo codice ripercorrerà tutti i punteggi degli studenti e arriverà il punteggio dell'esame finale richiesto per raggiungere la media complessiva di 90.

Quindi ora abbiamo il risultato finale come,

Lo studente A deve ottenere solo 83 punti per ottenere la percentuale complessiva di 90 e lo studente D deve ottenere un punteggio di 93.

Ma guarda gli studenti B e C, devono ottenere 104 punti ciascuno nell'esame finale, il che non è affatto possibile.

In questo modo, utilizzando l'analisi GOAL SEEK possiamo trovare il numero richiesto per raggiungere il numero target a metà del progetto o del processo.

Cose da ricordare

  • Goal Seek è disponibile sia con lo strumento foglio di lavoro che con lo strumento VBA.
  • La cella dei risultati dovrebbe sempre contenere una formula.
  • Dobbiamo inserire il valore dell'obiettivo e cambiare il riferimento di cella allo strumento di ricerca dell'obiettivo.