Excel può risolvere variabili sconosciute, sia per una singola cella con Goal Seeker che per più celle con Solver. Ti mostreremo come funziona.
Excel è uno strumento potente quando i tuoi dati sono completi. Ma non sarebbe bello se potesse risolvere per variabili sconosciute?
Con Goal Seek e il componente aggiuntivo Risolutore, è possibile. E ti mostreremo come. Continua a leggere per una guida completa su come risolvere per una singola cella con Goal Seek o un’equazione più complicata con Solver.
Come usare Goal Seek in Excel
Goal Seek è già integrato in Excel. Si trova nella scheda Dati, nel menu Analisi what-if:
Per questo esempio, useremo un set di numeri molto semplice. Abbiamo tre quarti dei numeri di vendita e un obiettivo annuale. Possiamo usare Goal Seek per capire quali devono essere i numeri nel Q4 per raggiungere l’obiettivo.
Come puoi vedere, il totale delle vendite attuali è di 114.706 unità. Se vogliamo venderne 250.000 entro la fine dell’anno, quanti ne dobbiamo vendere nel Q4? L’Obiettivo di Excel Ci dirà.
Ecco come usare Goal Seek, passo dopo passo:
- Fare clic su Dati > Analisi di simulazione > obiettivo. Verrà visualizzata la finestra Ricerca obiettivo.
- Inserisci la parte “uguale” dell’equazione nel campo Imposta cella. Questo è il numero che Excel cercherà di ottimizzare. Nel nostro caso, è il totale corrente dei nostri numeri di vendita nella cella A5.
- Digitare il valore dell’obiettivo nel campo Valore A. Stiamo cercando un totale di 250.000 unità vendute, quindi metteremo “250.000” in questo campo.
- Indicare a Excel quale variabile risolvere nel campo Modificando cella. Vogliamo vedere quali devono essere le nostre vendite nel Q4. Quindi diremo a Excel di risolvere per la cella D2. Sarà simile a questo quando sarà pronto per partire:
- Premi OK per risolvere il tuo obiettivo. Quando sembra buono, basta premere OK. Excel ti farà sapere quando Goal Seek ha trovato una soluzione.
- Fai di nuovo clic su OK e vedrai il valore che risolve l’equazione nella cella scelta per Cambiando cella.
Nel nostro caso, la soluzione è di 135.294 unità. Certo, avremmo potuto scoprirlo sottraendo il totale corrente dall’obiettivo annuale. Ma Goal Seek può essere utilizzato anche su una cella che contiene già dati. E questo è più utile.
Si noti che Excel sovrascrive i dati precedenti. È una buona idea eseguire Goal Seek su una copia dei tuoi dati. È anche una buona idea prendere nota dei dati copiati che sono stati generati utilizzando Goal Seek. Non vuoi confonderlo con dati aggiornati e accurati.
Quindi Goal Seek è un’utile funzionalità di Excel, ma non è poi così impressionante. Puoi usarlo solo su una singola cella alla volta. Se si desidera utilizzare Goal Seek di Excel su più celle contemporaneamente, è necessario uno strumento molto più potente. Fortunatamente, uno di questi strumenti viene fornito con Excel. Diamo un’occhiata al componente aggiuntivo Risolutore.
Cosa fa il Risolutore di Excel?
In breve, Solver è come una versione multivariata di Goal Seek. Se ti stavi chiedendo come usare Goal Seek in Excel per più celle contemporaneamente, questo è tutto. Prende una variabile obiettivo e regola una serie di altre variabili fino a ottenere la risposta desiderata.
Può risolvere per un valore massimo di un numero, un valore minimo di un numero o un numero esatto. E funziona all’interno di vincoli, quindi se una variabile non può essere modificata o può variare solo all’interno di un intervallo specificato, il Risolutore ne terrà conto.
È un ottimo modo per risolvere più variabili sconosciute in Excel. Ma trovarlo e usarlo non è semplice. Diamo un’occhiata al caricamento del componente aggiuntivo Risolutore, quindi passiamo a come usare il Risolutore nella versione corrente di Microsoft 365 di Excel.
Come caricare il componente aggiuntivo Risolutore
Excel non ha il Risolutore per impostazione predefinita. È un componente aggiuntivo, quindi devi prima caricarlo. Fortunatamente, è già sul tuo computer.
Vai a File > ( Altro… >) Opzioni > componenti aggiuntivi. Quindi fare clic su Vai accanto a Gestisci: componenti aggiuntivi di Excel.
Se questo menu a discesa indica qualcosa di diverso da “Componenti aggiuntivi di Excel”, sarà necessario modificarlo:
Nella finestra risultante, vedrai alcune opzioni. Assicurati che la casella accanto a Componente aggiuntivo Risolutore sia selezionata e premi OK.
Ora vedrai il pulsante Risolutore nel gruppo Analisi della scheda Dati:
Se hai già utilizzato strumenti di analisi dei dati, vedrai il pulsante Analisi dei dati. In caso contrario, il Risolutore apparirà da solo.
Ora che hai caricato il componente aggiuntivo, diamo un’occhiata a come usarlo.
Come usare il Risolutore in Excel
Ogni azione del Risolutore è suddivisa in tre parti: l’obiettivo, le celle variabili e i vincoli. Esamineremo ciascuno dei passaggi.
- Fare clic su Risolutore dati >. Vedrai la finestra Parametri del Risolutore qui sotto. Se non vedi il pulsante del risolutore, consulta la sezione precedente su come caricare il componente aggiuntivo Risolutore.
- Imposta l’obiettivo della cella e comunica a Excel il tuo obiettivo. L’obiettivo si trova nella parte superiore della finestra Risolutore e ha due parti: la cella obiettivo e una scelta di massimizzare, ridurre a icona o un valore specifico.
Se si seleziona Max, Excel regolerà le variabili per ottenere il numero più grande possibile nella cella dell’obiettivo. Min è l’opposto: solver ridurrà al minimo il numero obiettivo. Value Of consente di specificare un numero specifico da cercare per il Risolutore.
Scegliere le celle variabili che Excel può modificare. Le celle variabili vengono impostate con il campo Modificando celle variabili. Fare clic sulla freccia accanto al campo, quindi fare clic e trascinare per selezionare le celle con cui il Risolutore deve lavorare. Si noti che queste sono tutte le celle che possono variare. Se non vuoi che una cella cambi, non selezionarla.
3. Scegliere le celle variabili che Excel può modificare. Le celle variabili vengono impostate con il campo Modificando celle variabili. Fare clic sulla freccia accanto al campo, quindi fare clic e trascinare per selezionare le celle con cui il Risolutore deve lavorare. Si noti che queste sono tutte le celle che possono variare. Se non vuoi che una cella cambi, non selezionarla.
4. Impostare vincoli su più variabili o su singole variabili. Infine, veniamo ai vincoli. È qui che Solver è davvero potente. Invece di modificare una qualsiasi delle celle variabili in qualsiasi numero desiderato, è possibile specificare i vincoli che devono essere soddisfatti. Per informazioni dettagliate, vedere la sezione su come impostare i vincoli di seguito.
5. Una volta che tutte queste informazioni sono a posto, premi Risolvi per ottenere la tua risposta. Excel aggiornerà i tuoi dati per includere le nuove variabili (questo è il motivo per cui ti consigliamo di creare prima una copia dei tuoi dati).
Puoi anche generare report, che esamineremo brevemente nel nostro esempio di Risolutore di seguito.
Come impostare i vincoli nel Risolutore
Si potrebbe dire a Excel che una variabile deve essere maggiore di 200. Quando si provano valori di variabili diversi, Excel non andrà sotto 201 con quella particolare variabile.
Per aggiungere un vincolo, fare clic sul pulsante Aggiungi accanto all’elenco dei vincoli. Otterrai una nuova finestra. Selezionate la cella (o le celle) da vincolare nel campo Riferimento cella (Cell Reference), quindi scegliete un operatore.
Ecco gli operatori disponibili:
- <= (minore o uguale a)
- = (uguale a)
- => (maggiore o uguale a)
- int (deve essere un numero intero)
- bin (deve essere 1 o 0)
- TuttiDifferenti
AllDifferent è un po ‘confuso. Specifica che ogni cella nell’intervallo selezionato per Riferimento cella deve essere un numero diverso. Ma specifica anche che devono essere compresi tra 1 e il numero di celle. Quindi, se hai tre celle, finirai con i numeri 1, 2 e 3 (ma non necessariamente in quell’ordine)
Infine, aggiungere il valore per il vincolo.
È importante ricordare che è possibile selezionare più celle per Riferimento cella. Se si desidera che sei variabili abbiano valori superiori a 10, ad esempio, è possibile selezionarle tutte e indicare al Risolutore che devono essere maggiori o uguali a 11. Non è necessario aggiungere un vincolo per ogni cella.
Puoi anche utilizzare la casella di controllo nella finestra principale del Risolutore per assicurarti che tutti i valori per cui non hai specificato i vincoli non siano negativi. Se vuoi che le tue variabili diventino negative, deseleziona questa casella.
Un esempio di Risolutore
Per vedere come funziona tutto questo, useremo il componente aggiuntivo Risolutore per fare un rapido calcolo. Ecco i dati con cui iniziamo:
In esso, abbiamo cinque diversi lavori, ognuno dei quali paga una tariffa diversa. Abbiamo anche il numero di ore che un lavoratore teorico ha lavorato in ciascuno di questi lavori in una determinata settimana. Possiamo utilizzare il componente aggiuntivo Risolutore per scoprire come massimizzare la retribuzione totale mantenendo determinate variabili entro alcuni vincoli.
Ecco i vincoli che useremo:
- Nessun lavoro può scendere al di sotto delle quattro ore.
- Il lavoro 4 deve essere superiore a 12 ore.
- Il lavoro 5 deve essere inferiore a undici ore.
- Il totale delle ore lavorate deve essere pari a 40.
Può essere utile scrivere i tuoi vincoli in questo modo prima di usare Solver.
Ecco come l’abbiamo impostato in Risolutore:
Innanzitutto, tieni presente che ho creato una copia della tabella, quindi non sovrascriviamo quella originale, che contiene le nostre ore di lavoro correnti.
E in secondo luogo, vedi che i valori nei vincoli maggiore e minore di sono uno più alto o più basso di quello che ho menzionato sopra. Questo perché non ci sono opzioni maggiori o minori. Ci sono solo maggiore-di-o-uguale-a e minore-di-o-uguale-a.
Premiamo Risolvi e vediamo cosa succede.
Solver ha trovato una soluzione! Come puoi vedere a sinistra della finestra qui sopra, i nostri guadagni sono aumentati di $ 130. E tutti i vincoli sono stati soddisfatti.
Per mantenere i nuovi valori, assicurati che Keep Solver Solution sia selezionato e premi OK.
Se si desidera ulteriori informazioni, tuttavia, è possibile selezionare un report dal lato destro della finestra. Seleziona tutti i report che desideri, comunica a Excel se vuoi che siano delineati (lo consiglio) e premi OK.
I report vengono generati in nuovi fogli della cartella di lavoro e forniscono informazioni sul processo che il componente aggiuntivo Risolutore ha attraversato per ottenere la risposta.
Nel nostro caso, i rapporti non sono molto eccitanti e non ci sono molte informazioni interessanti lì. Ma se si esegue un’equazione del Risolutore più complicata, è possibile trovare alcune informazioni utili sui rapporti in questi nuovi fogli di lavoro. Basta fare clic sul pulsante + sul lato di qualsiasi rapporto per ottenere maggiori informazioni:
Opzioni avanzate del risolutore
Se non sai molto sulle statistiche, puoi ignorare le opzioni avanzate del Risolutore ed eseguirlo così com’è. Ma se stai eseguendo calcoli grandi e complessi, potresti voler esaminarli.
Il più ovvio è il metodo di risoluzione:
Puoi scegliere tra GRG Nonlinear, Simplex LP ed Evolutionary. Excel fornisce una semplice spiegazione su quando è necessario utilizzare ciascuno di essi. Una spiegazione migliore richiede una certa conoscenza delle statistiche e della regressione di Excel.
Per regolare impostazioni aggiuntive, basta premere il pulsante Opzioni. È possibile comunicare a Excel l’ottimizzazione dei numeri interi, impostare vincoli temporali di calcolo (utili per set di dati di grandi dimensioni) e regolare il modo in cui i metodi di risoluzione GRG ed evolutiva eseguono i loro calcoli.
Ancora una volta, se non sai cosa significa tutto questo, non preoccuparti. Se vuoi saperne di più su quale metodo di risoluzione utilizzare, Engineer Excel ha un buon articolo che lo espone per te. Se vuoi la massima precisione, Evolutionary è probabilmente un buon modo per andare. Basta essere consapevoli del fatto che ci vorrà molto tempo.
Ricerca di obiettivi e risolutore: portare Excel al livello successivo
Ora che ti senti a tuo agio con le basi della risoluzione di variabili sconosciute in Excel, ti viene aperto un mondo completamente nuovo di calcolo dei fogli di calcolo. Goal Seek può aiutarti a risparmiare tempo facendo alcuni calcoli più velocemente e Solver aggiunge un’enorme quantità di potenza alle capacità di calcolo di Excel.
È solo una questione di sentirsi a proprio agio con loro. Più li usi, più utili diventeranno.
