Come utilizzare la ricerca e il risolutore dell'obiettivo di Excel per risolvere variabili sconosciute

Excel è molto capace quando hai tutti i dati necessari per i tuoi calcoli.

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 una singola cella con Goal Seek o un'equazione più complicata con Risolutore.

Come utilizzare la ricerca obiettivo in Excel

Goal Seek è già integrato in Excel. È sotto il Dati scheda, in Analisi What-If menù:

Per questo esempio, utilizzeremo un insieme di numeri molto semplice. Abbiamo tre quarti di vendite e un obiettivo annuale. Possiamo usare Goal Seek per capire quali devono essere i numeri nel quarto trimestre per raggiungere l'obiettivo.

Come puoi vedere, il totale delle vendite attuali è di 114.706 unità. Se vogliamo vendere 250.000 entro la fine dell'anno, quanti ne dobbiamo vendere nel quarto trimestre? Ce lo dirà la ricerca obiettivo di Excel.

Ecco come utilizzare Goal Seek, passo dopo passo:

Clic Dati > Analisi what-if > Ricerca obiettivo . Vedrai questa finestra:
Metti la parte 'uguale' della tua equazione nel Imposta cella campo. Questo è il numero che Excel cercherà di ottimizzare. Nel nostro caso, è il totale parziale dei nostri numeri di vendita nella cella B5.
Digita il valore dell'obiettivo nel Da valutare campo. Stiamo cercando un totale di 250.000 unità vendute, quindi metteremo '250.000' in questo campo.
Indica a Excel quale variabile risolvere nel Cambiando cella campo. Vogliamo vedere quali devono essere le nostre vendite nel quarto trimestre. Quindi diremo a Excel di risolvere per la cella D2. Apparirà così quando sarà pronto per l'uso:
Colpire ok per risolvere per il tuo obiettivo. Quando sembra buono, basta premere ok . Excel ti farà sapere quando Goal Seek ha trovato una soluzione.
Clic ok di nuovo e vedrai il valore che risolve la tua equazione nella cella per cui hai scelto Cambiando cella .

Nel nostro caso la soluzione è 135.294 unità. Naturalmente, avremmo potuto scoprirlo sottraendo il totale parziale dall'obiettivo annuale. Ma Goal Seek può essere utilizzato anche su una cella che contiene già dei dati . E questo è più utile.

Nota che Excel sovrascrive i nostri dati precedenti. È una buona idea esegui Goal Seek su una copia dei tuoi dati . È anche una buona idea annotare sui dati copiati che sono stati generati utilizzando Goal Seek. Non vuoi confonderlo con dati attuali e accurati.

streaming di film gratuito senza registrazione

Quindi Goal Seek è un utile funzione di Excel , ma non è poi così impressionante. Diamo un'occhiata a uno strumento molto più interessante: il componente aggiuntivo Risolutore.

Cosa fa il risolutore di Excel?

In breve, il Risolutore è come un versione multivariata di Goal Seek . Prende una variabile obiettivo e regola un numero di altre variabili finché non ottiene la risposta desiderata.

Può risolvere per un valore massimo di un numero, un valore minimo di un numero o un numero esatto.

E funziona entro i limiti, 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 utilizzare Risolutore in Excel 2016.

Come caricare il componente aggiuntivo Risolutore

Excel non ha Risolutore per impostazione predefinita. È un componente aggiuntivo quindi, come altre potenti funzionalità di Excel, devi prima caricarlo. Fortunatamente, è già sul tuo computer.

Dirigiti a File > Opzioni > Componenti aggiuntivi . Quindi fare clic su andare accanto a Gestisci: componenti aggiuntivi di Excel .

Se questo menu a discesa dice qualcosa di diverso da 'Componenti aggiuntivi di Excel', dovrai cambiarlo:

Nella finestra risultante, vedrai alcune opzioni. Assicurati che la casella accanto a Componente aggiuntivo del risolutore è selezionato e premi ok .

Ora vedrai il Risolutore pulsante nel Analisi gruppo di Dati scheda:

Se hai già utilizzato il Pacchetto di strumenti per l'analisi dei dati , vedrai il pulsante Analisi 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

Ci sono tre parti in ogni azione del Risolutore: l'obiettivo, le celle variabili ei vincoli. Esamineremo ciascuno dei passaggi.

Clic Dati > Risolutore . Vedrai la finestra Parametri del risolutore di seguito. (Se non vedi il pulsante del risolutore, vedi la sezione precedente su come caricare il componente aggiuntivo Risolutore.)
Imposta l'obiettivo della tua cella e comunica a Excel il tuo obiettivo. L'obiettivo si trova nella parte superiore della finestra del Risolutore e ha due parti: la cella dell'obiettivo e una scelta di ingrandimento, riduzione a icona o un valore specifico. Se selezioni Max , Excel regolerà le tue variabili per ottenere il maggior numero possibile nella tua cella obiettivo. min è l'opposto: il risolutore ridurrà al minimo il numero dell'obiettivo. Valore di consente di specificare un numero specifico che il Risolutore deve cercare.
Scegli le celle variabili che Excel può modificare. Le celle variabili sono impostate con il Modificando le celle variabili campo. Fare clic sulla freccia accanto al campo, quindi fare clic e trascinare per selezionare le celle con cui il Risolutore dovrebbe funzionare. Nota che questi sono tutte le cellule che può variare. Se non vuoi che una cella cambi, non selezionarla.
Imposta vincoli su variabili multiple o singole. Infine, veniamo ai vincoli. È qui che il Risolutore è davvero potente. Invece di modificare una qualsiasi delle celle variabili con qualsiasi numero desiderato, puoi specificare i vincoli che devono essere soddisfatti. Per i dettagli, vedere la sezione su come impostare i vincoli di seguito.
Una volta che tutte queste informazioni sono a posto, premi Risolvere per avere 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 rapporti, che esamineremo brevemente nel nostro esempio del Risolutore di seguito.

Come impostare i vincoli in Risolutore

Potresti 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 su Aggiungere pulsante accanto all'elenco dei vincoli. Avrai una nuova finestra. Seleziona la cella (o le celle) da vincolare nel Riferimento cella campo, quindi scegli un operatore.

Ecco gli operatori disponibili:

<= (minore o uguale a)
= (uguale a)
=> (maggiore o uguale a)
int (deve essere un numero intero)
sono (deve essere 1 o 0)
TuttoDiverso

TuttoDiverso è 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, ti ritroverai con i numeri 1, 2 e 3 (ma non necessariamente in quell'ordine)

Infine, aggiungi il valore per il vincolo.

È importante ricordare che puoi seleziona più celle per riferimento di cella. Se vuoi che sei variabili abbiano valori maggiori di 10, ad esempio, puoi selezionarle tutte e dire a Risolutore che devono essere maggiori o uguali a 11. Non devi 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 i quali non hai specificato 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, utilizzeremo il componente aggiuntivo Risolutore per fare un rapido calcolo. Ecco i dati da cui partiamo:

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 utilizzeremo:

Nessun lavoro può scendere sotto le quattro ore.
Il lavoro 2 deve essere maggiore di otto ore .
Il lavoro 5 deve essere meno di undici ore .
Il totale delle ore lavorate deve essere uguale a 40 .

Può essere utile scrivere i propri vincoli in questo modo prima di utilizzare il Risolutore.

Ecco come lo configureremmo in Risolutore:

Innanzitutto, nota che Ho creato una copia della tabella quindi non sovrascriviamo quello originale, che contiene il nostro attuale orario di lavoro.

E in secondo luogo, vedi che i valori nei vincoli maggiore di e minore di sono uno più alto o più basso rispetto a quello che ho citato sopra. Questo perché non ci sono opzioni maggiore o minore di. Esistono solo maggiore o uguale a e minore o uguale a.

colpiamo Risolvere e guarda cosa succede.

Il risolutore ha trovato una soluzione! Come puoi vedere a sinistra della finestra in alto, i nostri guadagni sono aumentati di 0. E tutti i vincoli sono stati rispettati.

come salvare i video su twitter

Per mantenere i nuovi valori, assicurati Mantieni la soluzione del risolutore è controllato e colpito ok .

Se desideri maggiori informazioni, tuttavia, puoi selezionare un rapporto dal lato destro della finestra. Seleziona tutti i report che desideri, dì a Excel se vuoi che siano delineati (lo consiglio) e premi ok .

I report vengono generati su nuovi fogli nella cartella di lavoro e forniscono informazioni sul processo che il componente aggiuntivo Risolutore ha seguito per ottenere la risposta.

Nel nostro caso, i rapporti non sono molto entusiasmanti e non ci sono molte informazioni interessanti. Ma se esegui un'equazione Risolutore più complicata, potresti trovare alcune utili informazioni sui rapporti in questi nuovi fogli di lavoro. Basta fare clic su + pulsante a 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 volerli esaminare.

Il più ovvio è il metodo risolutivo:

Puoi scegliere tra GRG Nonlinear, Simplex LP ed Evolutionary. Excel fornisce una semplice spiegazione su quando dovresti usarli. Una spiegazione migliore richiede una certa conoscenza delle statistiche e della regressione.

Per regolare impostazioni aggiuntive, premi il pulsante Opzioni pulsante. Puoi dire a Excel dell'ottimalità dei numeri interi, impostare i vincoli di tempo di calcolo (utile per enormi set di dati) e regolare il modo in cui i metodi di risoluzione GRG ed evolutivi eseguono i loro calcoli.

Di nuovo, se non sai cosa significa tutto questo, non preoccuparti. Se vuoi saperne di più su quale metodo di risoluzione utilizzare, Engineer Excel ha a buon articolo che te lo spiega . Se vuoi la massima precisione, Evolutionary è probabilmente una buona strada da percorrere. Basta essere consapevoli che ci vorrà molto tempo.

Goal Seek and Solver: portare Excel al livello successivo

Ora che sei a tuo agio con le basi della risoluzione di variabili sconosciute in Excel, ti si apre un mondo completamente nuovo di calcolo del foglio di calcolo.

Goal Seek può aiutarti a risparmiare tempo eseguendo alcuni calcoli più velocemente e il Risolutore aggiunge un'enorme quantità di potenza a Le capacità di calcolo di Excel .

È solo questione di mettersi a proprio agio con loro. Più li usi, più utili diventeranno.

Usi Goal Seek o Solver nei tuoi fogli di calcolo? Quali altri suggerimenti puoi fornire per ottenere le migliori risposte da loro? Condividi i tuoi pensieri nei commenti qui sotto!

Condividere Condividere Tweet E-mail 5 suggerimenti per potenziare le tue macchine Linux VirtualBox

Stanchi delle scarse prestazioni offerte dalle macchine virtuali? Ecco cosa dovresti fare per aumentare le prestazioni di VirtualBox.

Leggi Avanti Argomenti correlati

Produttività
Foglio di calcolo
Microsoft Excel
Suggerimenti per Microsoft Office

Circa l'autore poi Albright(506 articoli pubblicati)

Dann è un consulente di marketing e strategia dei contenuti che aiuta le aziende a generare domanda e lead. Scrive anche su strategia e content marketing su dannalbright.com.

Altro da Dann Albright

Iscriviti alla nostra Newsletter

Iscriviti alla nostra newsletter per suggerimenti tecnici, recensioni, ebook gratuiti e offerte esclusive!

Clicca qui per iscriverti