Dimentica Ricerca Obiettivo — questo strumento di Excel fa molto di più

Che tu sia un principiante o che tu utilizzi Excel da abbastanza tempo, probabilmente sei familiare con Ricerca Obiettivo. Io, per esempio, non riesco a contare il numero di volte in cui mi sono affidato a esso per lavorare a ritroso fino a un numero specifico.

Tuttavia, il Ricerca Obiettivo di Excel ti porta solo fino a un certo punto, ed è proprio per questo che ho iniziato a cercare qualcosa di più capace e ho infine scoperto Risolutore. Questo avanzato strumento di analisi what-if ti offre un controllo molto maggiore, permettendoti non solo di ricavare un risultato a ritroso, ma anche di ottimizzare più variabili e arrivare al miglior risultato possibile.

I limiti di Ricerca Obiettivo e il modo migliore per andare avanti

La risoluzione inversa e le soluzioni a una variabile non sono sempre sufficienti

Immagina di fare richiesta per un prestito di 15.000 $con un interesse annuo del 7%. Sai esattamente quanto puoi permetterti: un pagamento mensile di 300 $. Goal Seek è perfetto per questo tipo di compito. Utilizzando la funzione PMT e indicando a Goal Seek la tua cella del termine, puoi chiedere di trovare il periodo di rimborso esatto (termine) necessario per rendere quel pagamento di 300 $una realtà.

Per quanto utile possa essere, Ricerca Obiettivo è limitata dalla sua semplicità. Funziona strettamente come uno strumento a variabile singola, il che significa che può modificare solo una singola cella di input per produrre un risultato. Se si volesse trovare una soluzione regolando contemporaneamente sia l'importo del prestito sia il periodo di rimborso, Ricerca Obiettivo non potrebbe essere d'aiuto.

Ha anche difficoltà con le condizioni del mondo reale. In pratica, le variabili di solito hanno dei limiti. Potresti aver bisogno che un tasso di interesse rimanga entro un certo intervallo o assicurarti che un prezzo non superi una soglia specifica. Ricerca obiettivo non ha alcun modo per tenere conto di questi confini; trova semplicemente una corrispondenza matematica, indipendentemente dal fatto che quel risultato abbia senso pratico.

È in questi momenti che devi rivolgerti a Solver. Invece di trovare semplicemente un valore specifico, Solver di Excel lavora per identificare quello ottimale, che si tratti di massimizzare o minimizzare un risultato. Può gestire più celle variabili, fino a 200 variabili decisionali, e ti permette di definire vincoli affinché la tua soluzione rimanga realistica e utilizzabile.

Poiché Solver è un componente aggiuntivo di Excel, avrai bisogno della versione desktop di Excel per usarlo. Su Windows, puoi attivarlo andando sulla scheda File, selezionando Opzioni e aprendo Componenti aggiuntivi dalla barra laterale. Sotto Componenti aggiuntivi applicazione inattivi, seleziona Componente aggiuntivo Solver, clicca Vai accanto alla casella Gestisci, spunta Componente aggiuntivo Solver nella finestra di dialogo e conferma con OK. Se richiesto, consenti a Excel di installarlo.

Su un Mac, apri il menu Strumenti, seleziona Componenti aggiuntivi di Excel, spunta Componente aggiuntivo Risolutore tra le opzioni disponibili e clicca OK.

Una volta attivato, il comando Risolutore appare nella scheda Dati all'interno del gruppo Analisi. Quando lo apri, puoi definire il tuo obiettivo, scegliere le celle variabili, applicare vincoli e selezionare un metodo di risoluzione. Anche se il Risolutore è incluso in Excel, non è abilitato di default, quindi dovrai seguire uno di questi passaggi di configurazione prima di poter iniziare a usarlo.

Casi in cui puoi usare Solver

Gestisci budget, prezzi, produzione e molto altro con Solver

Supponiamo che tu abbia un budget pubblicitario totale di 20.000 $che vuoi suddividere tra diversi trimestri. L'importo che spendi in ogni periodo influisce direttamente sulle tue vendite e, in ultima analisi, sul tuo profitto. Puoi usare Solver per regolare quei budget trimestrali come celle variabili multiple, permettendo di calcolare il massimo profitto possibile assicurando allo stesso tempo che l'allocazione totale non superi il limite di 20.000 $.

Per far funzionare questo, imposta il tuo obiettivo sulla cella contenente il tuo profitto totale e scegli Max. Sotto Cambiando le celle delle variabili, seleziona le quattro celle che rappresentano il budget di ciascun trimestre. Poi, aggiungi un vincolo selezionando la cella che somma quei valori trimestrali, impostando l'operatore su minore o uguale e definendo il limite a 20.000. Nel mio caso, ho usato il metodo di risoluzione GRG Nonlineare perché le stime dei miei profitti includono espressioni con radice quadrata come:

=10*SQRT(B2)

Tuttavia, puoi scegliere Simplex LP se le tue stime di profitto sono lineari, cioè senza esponenti, radici quadrate o variabili moltiplicate tra loro. Con tutto in posizione, Solver determinerà la distribuzione più efficiente in modo che ogni dollaro contribuisca il più possibile al risultato finale.

Puoi anche fare affidamento su Risolutore per scenari più complessi. Per esempio, se vuoi raggiungere un obiettivo di reddito specifico vendendo prodotti, potresti dover regolare contemporaneamente il numero di unità vendute, il prezzo per unità e il costo per unità. Risolutore ti permetterà di controllare tutte queste variabili applicando vincoli pratici. Puoi assicurarti che le unità vendute siano numeri interi, così da non finire con valori irrealistici come 706,5 unità, e mantenere i prezzi entro un intervallo definito, come da $3,00 a $4,00.

Per raggiungere un obiettivo di reddito specifico, ad esempio 5.000, imposta l'obiettivo sulla cella contenente il reddito totale, che potrebbe usare una formula come:

=I2*(I3-I4)

Da lì, imposta l'obiettivo su un valore fisso (Valore Di) e inserisci il tuo obiettivo. Quindi seleziona le celle per unità vendute e prezzo come Modificando Celle Variabili. Dopo di che, aggiungi questi tre vincoli per mantenere la soluzione realistica:

Imposta la cella contenente le tue unità su int
Limita il prezzo per unità a 4,00 selezionando
Imposta un prezzo minimo per unità di 3,00 selezionando >= e aggiungendo 3,00 come vincolo

Il risolutore quindi bilancia tutte e tre le variabili contemporaneamente, trovando la combinazione esatta che soddisfa il tuo obiettivo di reddito senza violare alcuna delle tue regole di prezzo o di inventario.

Se la tua cella obiettivo, le celle variabili e i vincoli sono distribuiti su schede diverse, potresti ricevere uno degli errori comuni di Excel, quindi vale la pena tenere tutto organizzato in un'unica scheda prima di eseguire il modello.

Vale la pena aggiungere Solver al tuo kit di strumenti

Ricerca Obiettivo è ottimo quando ho bisogno di lavorare a ritroso per ottenere una singola risposta, ma Risolutore è lo strumento a cui ricorro quando il problema richiede un'analisi più approfondita e una maggiore ottimizzazione. Ti offre la flessibilità di gestire scenari complessi del tipo 'e se', bilanciare più variabili e rimanere comunque nei vincoli del mondo reale.