Goal Seek è uno degli strumenti di analisi what-if di Excel che ti aiuta a trovare il valore di input corretto di una formula per ottenere l'output desiderato. Mostra come un valore in una formula influisce su un altro. In altre parole, se si desidera raggiungere un valore target, è possibile utilizzare la ricerca dell'obiettivo per trovare il valore di input corretto per ottenerlo.
Ad esempio, supponiamo che tu abbia ottenuto un totale di 75 punti in una materia e che tu abbia bisogno di almeno 90 per ottenere un voto S in quella materia. Per fortuna, hai un ultimo test che potrebbe aiutarti ad aumentare il tuo punteggio medio. Puoi usare Goal Seek per capire di quanto punteggio hai bisogno in quel test finale per ottenere un voto S.
Goal Seek utilizza un metodo per tentativi ed errori per risalire al problema inserendo ipotesi fino a quando non arriva al risultato giusto. Goal Seek può trovare il miglior valore di input per la formula in una manciata di secondi che avrebbe impiegato molto tempo a capire manualmente. In questo articolo, ti mostreremo come utilizzare lo strumento Ricerca obiettivo in Excel con alcuni esempi.
Componenti della funzione di ricerca dell'obiettivo
La funzione di ricerca obiettivo consiste di tre parametri, vale a dire:
- Imposta cella – Questa è la cella in cui viene inserita la formula. Specifica la cella in cui si desidera l'output desiderato.
- Da valutare – Questo è il valore target/desiderato che si desidera come risultato dell'operazione di ricerca obiettivo.
- Cambiando cella – Questo specifica la cella il cui valore deve essere regolato per ottenere l'output desiderato.
Come utilizzare la ricerca obiettivo in Excel: esempio 1
Per dimostrare come funziona in un semplice esempio, immagina di gestire una bancarella di frutta. Nello screenshot qui sotto, la cella B11 (sotto) mostra quanto ti è costato comprare frutta per la tua bancarella e la cella B12 mostra le tue entrate totali sulla vendita di quei frutti. E la cella B13 mostra la percentuale del tuo profitto (20%).
Se desideri aumentare il tuo profitto al "30%", ma non sei in grado di aumentare il tuo investimento, devi aumentare le tue entrate per ottenere un profitto. Ma a quanto? La ricerca dell'obiettivo ti aiuterà a trovarlo.
Si utilizza la seguente formula nella cella B13 per calcolare la percentuale di profitto:
=(B12-B11)/B12Ora, vuoi calcolare il margine di profitto in modo che la tua percentuale di profitto sia del 30%. Puoi farlo con Goal Seek in Excel.
La prima cosa da fare è selezionare la cella di cui si desidera regolare il valore. Ogni volta che utilizzi la ricerca obiettivo, devi selezionare una cella che contiene una formula o una funzione. Nel nostro caso selezioneremo la cella B13 perché contiene la formula per calcolare la percentuale.
Quindi vai alla scheda "Dati", fai clic sul pulsante "E se Analisi" nel gruppo Previsione e seleziona "Ricerca obiettivo".
Verrà visualizzata la finestra di dialogo Ricerca obiettivo con 3 campi:
- Imposta cella – Immettere il riferimento di cella che contiene la formula (B13). Questa è la cella che avrà l'output desiderato.
- Da valutare – Inserisci il risultato desiderato che stai cercando di ottenere (30%).
- Cambiando cella – Inserire il riferimento di cella per il valore di input che si desidera modificare (B12) per arrivare al risultato desiderato. Basta fare clic sulla cella o inserire manualmente il riferimento della cella. Quando selezioni la cella, Excel aggiungerà il segno "$" prima della lettera della colonna e del numero di riga per renderla una cella assoluta.
Al termine, fai clic su "OK" per testarlo.
Quindi verrà visualizzata una finestra di dialogo "Stato ricerca obiettivo" e ti informerà se ha trovato una soluzione come mostrato di seguito. Se è stata trovata una soluzione, il valore di input nella "cella di modifica (B12)" verrà adattato a un nuovo valore. Questo è ciò che vogliamo. Quindi, in questo esempio, l'analisi ha determinato che, per raggiungere l'obiettivo di profitto del 30%, è necessario ottenere un ricavo di $ 1635,5 (B12).
Fare clic su "OK" e Excel cambierà i valori della cella o fare clic su "Annulla" per scartare la soluzione e ripristinare il valore originale.
Il valore di input (1635,5) nella cella B12 è ciò che abbiamo scoperto utilizzando Goal Seek per raggiungere il nostro obiettivo (30%).
Cose da ricordare quando si utilizza Goal Seek
- La cella impostata deve sempre contenere una formula che dipende dalla cella "Cambiando cella".
- Puoi utilizzare Goal Seek solo su un valore di input di una singola cella alla volta
- L'opzione "Cambiando cella" deve contenere un valore e non una formula.
- Se Goal Seek non riesce a trovare la soluzione corretta, mostra il valore più vicino che può produrre e ti dice che il messaggio "La ricerca dell'obiettivo potrebbe non aver trovato una soluzione".
Cosa fare quando Excel Goal Seek non funziona
Tuttavia, se sei certo che ci sia una soluzione, ci sono alcune cose che puoi provare a risolvere questo problema.
Controlla i parametri e i valori di Goal Seek
Ci sono due cose che dovresti controllare: in primo luogo, controlla se il parametro "Imposta cella" si riferisce alla cella della formula. In secondo luogo, assicurati che la cella della formula (Imposta cella) dipenda, direttamente o indirettamente, dalla cella che cambia.
Regolazione delle impostazioni di iterazione
Nelle impostazioni di Excel, puoi modificare il numero di possibili tentativi che Excel può fare per trovare la soluzione giusta, nonché la sua accuratezza.
Per modificare le impostazioni di calcolo dell'iterazione, fare clic su "File" dall'elenco delle schede. Quindi, fai clic su "Opzioni" in basso.
Nella finestra Opzioni di Excel, fai clic su "Formule" nel riquadro di sinistra.
Nella sezione "Opzioni di calcolo", modifica queste impostazioni:
- Iterazioni massime – Indica il numero di possibili soluzioni che excel calcolerà; più alto è il numero, più iterazioni può eseguire. Ad esempio, se imposti "Iterazioni massime" su 150, Excel testa 150 possibili soluzioni.
- Variazione massima – Indica l'accuratezza dei risultati; il numero più piccolo fornisce una maggiore precisione. Ad esempio, se il valore della cella di input è uguale a "0" ma Goal Seek smette di calcolare a "0,001", modificarlo in "0,0001" dovrebbe risolvere il problema.
Aumentare il valore "Iterazioni massime" se si desidera che Excel verifichi più soluzioni possibili e diminuire il valore "Modifica massima" se si desidera un risultato più accurato.
La schermata seguente mostra il valore predefinito:
Nessun riferimento circolare
Quando una formula fa riferimento alla propria cella, viene chiamata riferimento circolare. Se vuoi che Excel Goal Seek funzioni correttamente, le formule non devono utilizzare riferimenti circolari.
Esempio di ricerca obiettivo in Excel 2
Supponiamo che tu stia prendendo in prestito denaro di "$ 25000" da qualcuno. Ti prestano i soldi a un tasso di interesse del 7% al mese per un periodo di 20 mesi, il che rende il rimborso di "$ 1327" al mese. Ma puoi permetterti di pagare solo "$ 1.000" al mese per 20 mesi con un interesse del 7%. Goal Seek può aiutarti a trovare l'importo del prestito che produce un pagamento mensile (EMI) di "$ 1000".
Inserisci le tre variabili di input di cui avrai bisogno per calcolare il tuo calcolo PMT, ovvero tasso di interesse del 7%, durata di 20 mesi e importo del capitale di $ 25.000.
Inserisci la seguente formula PMT nella cella B5 che ti darà un importo EMI di $ 1.327,97.
La sintassi della funzione PMT:
=PMT(Tasso di interesse/12, Termine, Capitale)La formula:
=PMT(B3/12,B4,-B2)
Seleziona la cella B5 (cella formula) e vai su Dati -> What If Analysis -> Goal Seek.
Utilizza questi parametri nella finestra "Ricerca obiettivo":
- Imposta cella – B5 (la cella che contiene la formula che calcola l'EMI)
- Da valutare – 1000 (la formula risultato/obiettivo che stai cercando)
- Cambiando cella – B2 (questo è l'importo del prestito che si desidera modificare per raggiungere il valore obiettivo)
Quindi, premi "OK" per mantenere la soluzione trovata o "Annulla" per scartarla.
L'analisi ti dice che l'importo massimo di denaro che puoi prendere in prestito è $ 18825 se vuoi superare il tuo budget.
Ecco come usi Goal Seek in Excel.