Funzione Ricerca Obiettivo in Excel: guida completa ed esempi

La Funzione Ricerca obiettivo in Excel È uno di quegli strumenti che passa inosservato finché non lo scopri, e poi non riesci a immaginare come hai fatto a vivere senza. Ti permette di dire a Excel: "Questo è ciò che voglio ottenere", e il programma calcola autonomamente il valore che una cella specifica dovrebbe avere per far funzionare i calcoli.

Invece di provare freneticamente i numeri finché non trovi quello che si adatta, puoi usare Trova l'obiettivo in modo che Excel possa fare il lavoro più duroÈ estremamente utile in ambito finanziario (prestiti, investimenti, budget personali), in questioni accademiche (voti, medie, borse di studio) e in qualsiasi calcolo in cui hai impostato una formula e ti manca un'incognita.

Cos'è esattamente la Ricerca obiettivo in Excel?

Buscar Objetivo Fa parte degli strumenti di Analisi Cosa succederebbe se… in Excel. Invece di chiederti "se modifico questi dati, cosa succede al risultato?", inverti l'approccio: dici a Excel quale risultato vuoi ottenere e gli chiedi di calcolare quale valore dovrebbe assumere una delle celle coinvolte nella formula.

In altre parole, lo strumento funziona “L’opposto” di ciò che faresti normalmenteConosci già il valore finale di cui hai bisogno e lasci che Excel trovi il valore di input che fa sì che la tua formula restituisca quel risultato. Matematicamente, è come risolvere un'equazione con una sola incognita senza doverla isolare manualmente.

Questa funzione è disponibile in Excel e anche in altre suite per ufficio come ONLYOFFICE Docdove è stato incorporato nei loro fogli di calcolo per offrire lo stesso tipo di analisi di scenario. In tutti i casi, la filosofia è la stessa: Stabilisci un obiettivo, scegli la variabile e lascia che il programma esegua l'iterazione. finché non viene trovato un valore adatto.

Alcune situazioni tipiche in cui l'utilizzo di Goal Seek è molto utile sono calcoli di ammortamento del prestito, pianificazione del risparmio pensionisticoProgettazione di budget personali o aziendali e analisi delle vendite necessarie per raggiungere un profitto specifico o un punto di pareggio.

Concetti chiave prima di utilizzare Goal Seek

Prima di iniziare a utilizzare lo strumento, è importante avere ben chiari tre concetti: la cella di destinazione, il valore desiderato e la cella che cambiaSe si identificano correttamente questi elementi, il resto è un gioco da ragazzi.

La cella di destinazione Questa è la cella in cui si trova il risultato di una formula a cui si desidera assegnare un valore specifico. Ad esempio, potrebbe essere la media dei voti finali, la rata mensile di un prestito o il profitto totale di un prodotto. Importante: questa cella deve contenere una formula, non un numero scritto a mano.

El valore desiderato Questo è il numero che vuoi che la cella target raggiunga. Potrebbe essere una media minima per mantenere una borsa di studio, la rata mensile massima consentita dal tuo budget o il profitto che hai impostato come obiettivo per la tua attività. Decidi questo valore in base al tuo obiettivo..

La cellula che cambia Questa è l'incognita del problema, ovvero i dati che vuoi che Excel calcoli. Deve essere una cella che fa parte della formula nella cella di destinazione. Potrebbe trattarsi di un saldo in sospeso, dell'importo del prestito, del numero di anni di rimborso, del prezzo di vendita, del numero di unità vendute, ecc.

È essenziale che la cellula bersaglio dipende, direttamente o indirettamente, dalla cellula che cambiaSe non c'è alcuna relazione tra i due nella formula, Goal Seek non sarà in grado di trovare alcun risultato. In sostanza, questo strumento sostituisce il classico metodo "tentativi ed errori" con un processo automatizzato di iterazioni.

Come funziona Goal Seek passo dopo passo: esempio di prestito

Un esempio classico per comprendere Goal Seek è quello di un prestito con pagamenti periodiciImmagina di sapere quanti soldi vuoi prendere in prestito, in quanti mesi vuoi restituirli e qual è la rata massima che puoi permetterti, ma non hai idea di quale tasso di interesse ti serva per far quadrare i conti.

Questo tipo di calcolo utilizza la funzione PAGAMENTO (PMT) La funzione di Excel restituisce l'importo da pagare in ogni periodo. Il punto è che conosci già la rata mensile, quindi userai Ricerca Obiettivo per far sì che Excel modifichi il tasso di interesse fino a raggiungere tale importo.

1. Preparare il foglio di calcolo del prestito

Innanzitutto è una buona idea organizzare i dati in una piccola tabella Per mantenere tutto organizzato, puoi creare qualcosa di simile a questo su un nuovo foglio:

• In formato A1: "Importo del prestito". In B1: il capitale che vuoi richiedere, ad esempio 100000.
• In formato A2: “Termine in mesi”. In B2: il numero di mesi, ad esempio 180 (che equivarrebbe a 15 anni).
• In formato A3: “Tasso di interesse”. In B3Per ora lascialo vuoto, perché sarà l'ignoto.
• In formato A4: “Pagamento”. In B4: qui andrebbe la formula con PAGAMENTO.

Ora scrivi la formula nella cella B4. =RATA(B3/12;B2;B1)Si divide B3 per 12 perché il tasso è solitamente espresso in termini annuali e si calcola la rata mensile. Poiché B3 è vuoto, Excel assumerà un interesse pari allo 0% e fornirà una rata mensile basata solo sulla durata e sull'importo.

In questo esempio specifico, potresti vedere che la commissione calcolata è di circa 555,56Ma quel valore non è ancora rilevante per te. La cosa importante è che tu abbia già il struttura della formula pronto per l'uso Trova destinazione.

2. Esegui Goal Seek per trovare il tasso di interesse

Con la formula in posizione, ora puoi aprire lo strumento di analisi. Vai alla scheda Dati e all'interno del gruppo di comandi Per strumenti di previsione o dati, selezionare Analisi delle ipotesi > Ricerca dell'obiettivoSi aprirà una finestra di dialogo con tre campi.

Nella cornice “Imposta cella” Specifica la cella che contiene la formula di cui vuoi correggere il risultato, in questo caso B4. Questa è la cella di destinazione, in cui si trova il calcolo della rata mensile tramite la funzione RATA.

Nel campo “Coraggio di” Scrivi l'importo che intendi effettivamente pagare. Se la tua idea è di pagare 900 al mese, lo inserirai qui. all'900 ottobreIl segno negativo è importante perché in Excel i pagamenti effettuati di tasca propria sono considerati importi negativi.

Nella cornice “Cellula che cambia” Inserisci il riferimento di cella che Excel deve modificare per ottenere quel pagamento. In questo esempio, sarà B3, dove verrà impostato il tasso di interesse. Quando clicchi su OK, Goal Seek inizierà a iterare, modificando il tasso finché non ne trova uno che lasci la rata mensile a -900.

Al termine, lo strumento ti mostrerà un finestra di stato con il risultatoVedrai il tasso di interesse calcolato in B3 e la nuova rata in B4. Excel di solito trova una soluzione abbastanza accurata se la formula è corretta e il problema ha senso numericamente.

Per finire, puoi formatear cella B3 in percentuale Dalla scheda Home, seleziona il formato percentuale e regola il numero di cifre decimali utilizzando le icone di aumento o diminuzione delle cifre decimali. In questo modo, il tasso di interesse verrà visualizzato in un formato più leggibile.

Altri esempi di utilizzo: note, investimenti e crediti

La potenza di Goal Seek non si limita ai prestiti. Può essere applicata a una moltitudine di situazioni quotidiane in cui Hai un obiettivo numerico e ti manca una variabile che soddisfi tale obiettivo. Esamineremo alcuni casi pratici molto comuni.

1. Sapere quale voto ti serve per l'ultimo esame

Uno degli esempi più intuitivi è quello dell' giudiziImmagina di avere già inserito diversi voti e di avere un solo esame rimasto. Conosci la media minima necessaria per superare l'esame o per mantenere una borsa di studio, ma non sei sicuro di quale voto ti serva per l'ultimo esame.

Supponiamo che ti serva una media di 70 per poter sostenere l'esame finale o continuare a ricevere la borsa di studio. Hai già sostenuto 3 esami, ne manca uno e vuoi che Excel te lo dica. Che voto devi prendere al quarto esame? in modo che la media complessiva sia almeno 70.

Per prima cosa, inserisci i voti noti in una colonna, ad esempio B2:B4. Lascia vuota la cella B5 perché conterrà il voto dell'esame in sospeso, e in un'altra cella, ad esempio B7, scrivi la formula per la media: =MEDIA(B2:B5)Quella sarà la tua cellula bersaglio.

Apri Ricerca obiettivo da Dati > Analisi what-if > Ricerca obiettivo e Lo configuri così:

• Imposta cella: B7 (la cella con la formula media).
• Valore per: 70 (la media che vuoi raggiungere).
• Cellula che cambia: B5 (voto per l'esame in sospeso).

Dopo la conferma, Excel calcolerà il valore che B5 dovrebbe avere in modo che la media in B7 sia 70. Ad esempio, potrebbe dirti che hai bisogno di un 85 nell'ultimo esame per raggiungere la media richiesta. Questa stessa idea può essere utilizzata con le medie ponderate utilizzando funzioni come SOMMA PRODOTTO y SUMA, se ogni test ha un peso diverso.

2. Medie ponderate e voti con pesi diversi

In molti corsi, non tutte le valutazioni hanno lo stesso peso. Ad esempio, i compiti potrebbero valere il 30%, i quiz il 20% e l'esame finale il 50%. In questi casi, invece di una media semplice, di solito si utilizza una formula: media ponderata.

Un modo tipico per impostarlo è quello di avere le valutazioni in una colonna e i pesi (in forma decimale o percentuale) in un'altra. È possibile utilizzare la combinazione SOMMA.PRODOTTO(voti;pesi)/SOMMA(pesi) per ottenere il voto finale.

La cella contenente la formula per la media ponderata sarà la cella di destinazione. Quindi, come in precedenza, si utilizza Ricerca obiettivo per far calcolare la media a Excel. modificare il voto di un esame specifico finché il voto complessivo non raggiunge, diciamo, il 50% o il valore necessario per superare l'esame.

Seguendo questo approccio, potresti scoprire che, ad esempio, Devi raggiungere il 57,5% all'esame finale in modo che, con i pesi di ogni componente, il voto del corso raggiunga il 50% richiesto.

3. Pianificazione degli investimenti e dei risparmi

La ricerca del bersaglio è anche un alleato quando ti prepari piani di investimento o di risparmioSe conosci già il rendimento previsto sui tuoi investimenti e il lasso di tempo, puoi porti domande come: "Quanto devo versare ogni mese per raggiungere l'importo accumulato X?" oppure "Quale importo iniziale devo investire se voglio ottenere un certo rendimento?"

Funzioni finanziarie come FV (valore futuro), PV (valore attuale), VOTA o il PAGAMENTO stesso. Imposti la formula che descrive la tua situazione (ad esempio, il valore futuro di una serie di contributi periodici) e lasci il contributo mensile o il capitale iniziale come variabile.

Una volta assemblato il modello, puoi utilizzare Target Find per bloccare la tua posizione. obiettivo di risparmio o redditività Nella cella del risultato, Excel modifica quindi il contributo periodico o l'importo dell'investimento iniziale. È la stessa logica dei prestiti, ma applicata al lato risparmio o investimento.

4. Crediti e limitazioni di budget

Tornando ai prestiti, puoi fare un ulteriore passo avanti. Immagina di aver calcolato con PAGAMENTO quanto dovresti pagare annualmente per un prestito di 12.000 € in 20 anni con un tasso di interesse del 5%. Il risultato è una rata di circa 962,91 € all'anno, ma Il tuo budget massimo è 900.

Con Goal Seek puoi inquadrare il problema al contrario: vuoi che il pagamento annuale sia -900 E vuoi che l'importo del prestito venga modificato. Configuri lo strumento in modo che la cella di destinazione sia quella contenente il risultato PAGAMENTO, il valore desiderato sia -900 e la cella che cambia sia il capitale del prestito.

Il risultato potrebbe essere, ad esempio, un importo del prestito di circa 11.216In questo modo saprai esattamente quanto puoi prendere in prestito senza superare il limite annuo di 900 €. Se, invece di fissare il capitale, vuoi mantenere invariato l'importo del prestito e modificarlo il tempo Per il ritorno, è sufficiente specificare come cella che cambia la cella che contiene il numero di anni o mesi.

5. Analisi dei benefici e delle vendite necessarie

Nel mondo degli affari, Goal Seek è utile per domande come:Quante unità devo vendere per guadagnare 50.000?"o a quale prezzo devo vendere per ottenere un certo margine, dati un costo e una capacità produttiva?"

È possibile elaborare una formula di profitto come questa: (Prezzo – Costo) × Unità vendute e usala come cella di destinazione. A seconda delle tue esigenze, imposta il profitto desiderato e lascia la cella che cambia con il numero di unità da vendere o il prezzo unitario.

Ad esempio, se si definisce un profitto target di 50.000, è possibile far calcolare a Goal Seek quante unità devono essere vendute, mantenendo fissi il prezzo e il costoOppure viceversa: stabilire un volume di vendita limitato dalla capacità produttiva e lasciare che il prezzo si adegui per ottenere il profitto desiderato.

Differenze tra Goal Seek e Solver

Un punto importante è capire dove finisce l'utilità. Buscar Objetivo e dove comincia l'uno? RisolutoreUn altro componente aggiuntivo di Excel molto potente. Entrambi vengono utilizzati per l'analisi degli scenari, ma non risolvono gli stessi tipi di problemi.

L'obiettivo di ricerca è progettato per problemi con una singola variabileIn altre parole, puoi modificare solo una cella per cercare di raggiungere il valore target. Se hai più di una incognita, il risultato non è soddisfacente, ed è qui che entra in gioco il Risolutore.

Solver ti consente di lavorare con più celle variabili contemporaneamenteÈ possibile impostare delle restrizioni (ad esempio, che alcune celle non possano essere negative, che un certo valore massimo non possa essere superato, ecc.) e decidere se l'obiettivo è massimizzare, minimizzare o uguagliare un valore specifico. È uno strumento di ottimizzazione molto più completo.

Ad esempio, se vuoi sapere quale voto devi ottenere sia in inglese che in chimica affinché la media di entrambe le materie più le altre sia 84, ti trovi di fronte a un problema con due variabiliLa Ricerca Obiettivo non funzionerà, ma il Risolutore sì. In tal caso, dovresti impostare la cella di destinazione sulla media, impostare il valore su 84 e specificare che le celle che possono essere modificate sono quelle dei voti di Inglese e Chimica.

Un altro esempio tipico con Solver è il massimizzazione del profitto in condizioni di vincolo, come quando un'azienda vende diversi servizi, ognuno con il proprio profitto, e vuole trovare la combinazione di unità da vendere che massimizzi il profitto totale rispettando determinate condizioni (vendite minime di un servizio, vendite massime di un altro, limite complessivo di unità, ecc.).

Problemi comuni quando si utilizza Goal Seek e come evitarli

Sebbene Goal Seek sia molto facile da usare, a volte potrebbe non offrire una soluzione o potrebbe restituire risultati strani. Nella maggior parte dei casi, Il difetto risiede nella configurazione o nelle proprietà numeriche del problemanon nello strumento stesso.

Una causa comune è che il la cellula bersaglio non dipende dalla cellula che cambiaSe la cella indicata come variabile non è coinvolta nella formula della cella di destinazione (direttamente o indirettamente), Excel non ha nulla da modificare e la ricerca dell'obiettivo fallirà comunque.

Possono anche causarti problemi riferimenti circolariVale a dire, formule in cui una cella finisce per dipendere da se stessa. Questo può causare la mancata convergenza dei calcoli o la disattivazione di determinate opzioni da parte di Excel. È consigliabile verificare che le formule siano coerenti e non si auto-supportino ricorsivamente senza controllo.

Un altro punto da rivedere sono i opzioni di iterazione In Excel, se il numero massimo di iterazioni o la variazione massima consentita tra le iterazioni è troppo basso, la funzione Ricerca obiettivo potrebbe interrompersi prematuramente o arrivare a un'approssimazione imprecisa. Questi parametri possono essere modificati in File > Opzioni > Formule, aumentando il numero massimo di iterazioni o regolando la variazione massima.

Infine, vale la pena verificare che il L'obiettivo che proponi dovrebbe avere un senso numerico.Se chiedi qualcosa di impossibile dati i limiti del tuo modello (ad esempio, una rata mensile irrealisticamente bassa per un prestito ingente con un tasso di interesse molto alto), è normale che Excel non riesca a trovare alcun valore adatto.

Lo strumento Ricerca obiettivo in Excel, se usato saggiamente, diventa una potente scorciatoia per rispondere a tutti i tipi di domande numeriche: ti consente di passare da "cosa succede se cambio questo?" a "questo è ciò che voglio ottenere, dimmi cosa devo cambiare", e con questo Ti fa risparmiare un sacco di tempo in tentativi ed errori. sia nella gestione delle tue finanze personali che nell'analisi della tua attività o dei tuoi studi.