Come utilizzare Excel Goal Seek e Solver per risolvere le 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 il componente aggiuntivo Goal Seek e Solver, è 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, nel Analisi what-if menu:

Per questo esempio, useremo un insieme di numeri molto semplice. Abbiamo un valore di vendita di tre quarti e un obiettivo annuale. Possiamo utilizzare Goal Seek per capire quali devono essere i numeri in Q4 per raggiungere l'obiettivo.

Come puoi vedere, il totale delle vendite correnti è di 114.706 unità. Se vogliamo vendere 250.000 entro la fine dell'anno, quanti ne dobbiamo vendere nel quarto trimestre? Goal Seek di Excel ci dirà.

Ecco come utilizzare Goal Seek, passo dopo passo:

1. Clic Dati> Analisi What-If> Ricerca obiettivo. Vedrai questa finestra:
   
2. Metti il “è uguale a” parte della tua equazione in 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.
3. Digita il tuo valore obiettivo nel file Per valutare campo. Stiamo cercando un totale di 250.000 unità vendute, quindi inseriremo “250.000” in questo campo.
4. Indica a Excel quale variabile risolvere in Cambiando cella campo. Vogliamo vedere quali devono essere le nostre vendite in Q4. Quindi diremo a Excel di risolvere la cella D2. Assomiglierà a questo quando è pronto per andare:
   
5. Colpire ok risolvere per il tuo obiettivo. Quando sembra buono, basta colpire ok. Excel ti farà sapere quando Goal Seek ha trovato una soluzione.
   
6. Clic ok di nuovo e vedrai il valore che risolve la tua equazione nella cella che hai scelto Cambiando cella.
   

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

Si noti che Excel sovrascrive i nostri dati precedenti. È una buona idea eseguire Goal Seek su una copia dei tuoi dati. È anche consigliabile prendere nota dei dati copiati che sono stati generati utilizzando Goal Seek. Non vuoi confonderlo per dati attuali e accurati.

So Goal Seek è un'utile funzionalità di Excel. 16 formule di Excel che ti aiuteranno a risolvere problemi di vita reale. 16 formule di Excel che ti aiuteranno a risolvere problemi di vita reale Lo strumento giusto è metà del lavoro. Excel può risolvere calcoli ed elaborare i dati più velocemente di quanto tu possa trovare la tua calcolatrice. Vi mostriamo le formule chiave di Excel e dimostriamo come usarle. Leggi di più, 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 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 dei vincoli, quindi se una variabile non può essere modificata, o può variare solo all'interno di un intervallo specificato, il Risolutore prenderà in considerazione questo.

È 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 spieghiamo 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, Power Up Excel con 10 componenti aggiuntivi da elaborare, analizzare e visualizzare dati come un Pro. Power Up Excel con 10 componenti aggiuntivi per elaborare, analizzare e visualizzare i dati come un Pro Vanilla Excel è incredibile, ma puoi renderlo ancora più potente con i componenti aggiuntivi. Qualunque siano i dati che è necessario elaborare, è probabile che qualcuno abbia creato un'app Excel per questo. Ecco una selezione. Per saperne di più, devi prima caricarlo. Fortunatamente, è già sul tuo computer.

Testa a File> Opzioni> Componenti aggiuntivi. Quindi fare clic su Partire 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 Addetto al Risolutore viene controllato e colpito ok.

Ora vedrai il Risolutore pulsante nel Analisi gruppo del Dati tab:

Se hai già utilizzato il Data Analysis Toolpak Come eseguire l'analisi dei dati di base in Excel Come eseguire l'analisi dei dati di base in Excel Excel non è pensato per l'analisi dei dati, ma può comunque gestire le statistiche. Ti mostreremo come utilizzare il componente aggiuntivo Data Analysis Toolpak per eseguire le statistiche di Excel. Leggi altro, 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 utilizzare il Risolutore in Excel

Ci sono tre parti in ogni azione del Risolutore: l'obiettivo, le celle variabili e i vincoli. Cammineremo attraverso ciascuno dei passaggi.

1. Clic Dati> Risolutore. Vedrai la finestra Parametri del Risolutore sotto. (Se non si vede il pulsante del risolutore, vedere la sezione precedente su come caricare il componente aggiuntivo Risolutore.)
   
2. Imposta l'obiettivo della tua cella e comunica a Excel il tuo obiettivo. L'obiettivo è in cima alla finestra del Risolutore e ha due parti: la cella obiettivo e una scelta di massimizzare, minimizzare o un valore specifico.
   
   Se si seleziona Max, Excel regolerà le tue variabili per ottenere il maggior numero possibile nella tua cella obiettivo. min è il contrario: il Risolutore ridurrà al minimo il numero obiettivo. Valore di ti consente di specificare un numero specifico per il Risolutore da cercare.
3. Scegli le celle variabili che possono essere modificate da Excel. Le celle variabili sono impostate con Cambiando 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 lavorare. Nota che questi sono tutte le celle che può variare. Se non vuoi che una cella cambi, non selezionarla.
   
4. Imposta i vincoli su più o singole variabili. Infine, arriviamo ai vincoli. È qui che il Risolutore è veramente potente. Invece di modificare qualsiasi cella variabile in qualsiasi numero desiderato, è possibile specificare i vincoli che devono essere soddisfatti. Per i dettagli, vedere la sezione su come impostare i vincoli di seguito.
5. Una volta che tutte queste informazioni sono a posto, colpisci Risolvere 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 rapporti, 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 variabili diversi, Excel non andrà sotto il 201 con quella particolare variabile.

Per aggiungere un vincolo, fare clic su Inserisci pulsante accanto all'elenco dei vincoli. Avrai una nuova finestra Seleziona la cella (o le celle) da vincolare nel Riferimento di cella campo, quindi scegliere un operatore.

Ecco gli operatori disponibili:

• <= (minore o uguale a)
• = (uguale a)
• => (maggiore o uguale a)
• int (deve essere un numero intero)
• bidone (deve essere 1 o 0)
• AllDifferent

AllDifferent è un po 'di confusione. Specifica che ogni cella dell'intervallo per cui si seleziona Riferimento di 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 questo ordine)

Infine, aggiungi il valore per il vincolo.

È importante ricordare che puoi seleziona più celle per riferimento cellulare. 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.

È inoltre possibile utilizzare la casella di controllo nella finestra principale del Risolutore per assicurarsi che tutti i valori per i quali non sono stati specificati i vincoli siano non 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 un tasso diverso. Abbiamo anche il numero di ore in cui un lavoratore teorico ha lavorato a ciascuno di quei lavori in una determinata settimana. Possiamo utilizzare il componente aggiuntivo Risolutore per scoprire come massimizzare la retribuzione totale mantenendo alcune variabili all'interno di alcuni vincoli.

Ecco i vincoli che useremo:

• Nessun lavoro può cadere sotto le quattro ore.
• Il lavoro 2 deve essere più di otto ore.
• Il lavoro 5 deve essere meno di undici ore.
• Le ore totali lavorate devono essere uguale a 40.

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

Ecco come l'avremmo impostato in Solver:

Innanzitutto, nota questo Ho creato una copia del tavolo quindi non sovrascriviamo quello originale, che contiene il nostro orario di lavoro corrente.

E in secondo luogo, vedi che i valori nei vincoli "maggiore di e minore di" sono uno più alto o più basso di quello che ho menzionato sopra. Questo perché non ci sono opzioni superiori o inferiori a. Ci sono solo più di-o-uguale-e-meno-o-uguale a.

Colpire Risolvere e vedi cosa succede.

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

Per mantenere i nuovi valori, assicurati Mantieni soluzione risolutiva è controllato e colpito ok.

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

I report vengono generati su nuovi fogli nella cartella di lavoro e forniscono informazioni sul processo in cui è stato eseguito il componente aggiuntivo Risolutore 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 Risolutore più complessa, è possibile trovare alcune utili informazioni di reporting in questi nuovi fogli di lavoro. Basta fare clic sul + pulsante sul lato di qualsiasi rapporto per ottenere ulteriori informazioni:

Risolutore Opzioni avanzate

Se non sai molto delle statistiche, puoi ignorare le opzioni avanzate di Solver e basta eseguirlo così com'è. Ma se stai eseguendo calcoli complessi e di grandi dimensioni, potresti volerli esaminare.

Il più ovvio è il metodo di risoluzione:

Puoi scegliere tra GRG non lineare, Simplex LP ed evolutivo. Excel fornisce una semplice spiegazione su quando dovresti usarne uno. Una spiegazione migliore richiede una certa conoscenza delle statistiche Scopri le statistiche gratuitamente con queste 6 risorse Scopri le statistiche gratuitamente con queste 6 risorse Statistiche ha una reputazione di soggetto difficile da capire. Ma imparare dalle risorse giuste ti aiuterà a capire i risultati del sondaggio, i rapporti elettorali e le assegnazioni delle classi di statistiche in pochissimo tempo. Leggi di più e regressione.

Per regolare le impostazioni aggiuntive, basta premere il tasto Opzioni pulsante. Puoi dire a Excel l'ottimalità dei numeri interi, impostare i vincoli di tempo di calcolo (utili per enormi set di dati) e regolare come i metodi di risoluzione GRG ed Evolutionary eseguono i loro calcoli.

Di nuovo, se non sai cosa significa questo, non ti preoccupare. Se vuoi sapere di più su quale metodo di risoluzione utilizzare, l'Ingegnere Excel ha un buon articolo che lo espone a te. Se vuoi la massima precisione, Evolutionary è probabilmente un buon modo per andare. 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 per la risoluzione di variabili sconosciute in Excel, un mondo completamente nuovo di calcolo del foglio di calcolo è aperto a te.

Goal Seek può aiutarti a risparmiare tempo effettuando alcuni calcoli più velocemente e il Risolutore aggiunge un'enorme quantità di potenza alle capacità di calcolo di Excel Come calcolare statistiche di base in Excel: Guida per principianti Come calcolare statistiche di base in Excel: Guida per principianti Microsoft Excel può fare statistiche! È possibile calcolare percentuali, medie, deviazione standard, errore standard e test T dello studente. Leggi di più .

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

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

Scopri di più su: Microsoft Excel, Suggerimenti di Microsoft Office, Foglio di calcolo.