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 nell'esame finale per raggiungere l'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. Successivamente potremo 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.
Ricerca obiettivo VBA - Esempio n. 1
Prendiamo solo l'esempio di un punteggio medio di 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. Applicare la funzione MEDIA nella cella B8.
In questo esempio, il nostro obiettivo è 90 e 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.
Avviare 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 accedi all'opzione "Goal Seek".
Il primo argomento è "Obiettivo" per questo. Dobbiamo inserire il nostro obiettivo finale per arrivare nella GAMMA 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 è "Modifica 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, nell'argomento finale, deve essere valutato 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 per 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 il codice che fa 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 lo Studente B & 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 gli strumenti del foglio di lavoro che con lo strumento VBA.
- La cella risultante dovrebbe sempre contenere una formula.
- Dobbiamo inserire il valore dell'obiettivo e cambiare il riferimento di cella allo strumento di ricerca dell'obiettivo.
