Excel è molto capace quando hai tutti i dati necessari per i tuoi calcoli.
Ma non sarebbe bello se potesse risolvere 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 la scheda Dati, nel menu What-If Analysis :
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:
- Fai clic su Dati> Analisi what-if> Ricerca obiettivo . Vedrai questa finestra:
- Metti la parte "uguale" della tua equazione nel campo Imposta cella . 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 campo Valore . Stiamo cercando un totale di 250.000 unità vendute, quindi metteremo "250.000" in questo campo.
- Indica a Excel quale variabile risolvere nel campo Cambiando cella . 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:
- Colpisci OK per risolvere il tuo obiettivo. Quando sembra buono, premi OK . Excel ti farà sapere quando Goal Seek ha trovato una soluzione.
- Fai di nuovo clic su OK 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 utilizzato su una cella che 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 una 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 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 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.
Vai a File> 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", dovrai modificarlo:
Nella finestra risultante, vedrai alcune opzioni. Assicurati che la casella accanto a Solver Add-In sia selezionata e premi OK .
Ora vedrai il pulsante Risolutore nel gruppo Analisi della scheda Dati :
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.
- Fai clic su 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.)
- 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 selezioni Max, Excel modificherà le tue variabili per ottenere il maggior numero possibile nella tua cella obiettivo. Min è il contrario: il Risolutore ridurrà al minimo il numero obiettivo. Value Of consente di specificare un numero specifico per il Risolutore da cercare. - Scegli le celle variabili che possono essere modificate da Excel. Le celle variabili sono impostate con il campo Cambiando le celle variabili . Fare clic sulla freccia accanto al campo, quindi fare clic e trascinare per selezionare le celle con cui il Risolutore dovrebbe lavorare. Si noti che queste sono tutte celle che possono variare. Se non vuoi che una cella cambi, non selezionarla.
- 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.
- Una volta che tutte queste informazioni sono a posto, premi Solve 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, fai clic sul pulsante Aggiungi accanto all'elenco dei vincoli. Avrai una nuova finestra Selezionare la cella (o le celle) da vincolare nel campo Riferimento cella, quindi scegliere 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)
- AllDifferent
AllDifferent è un po 'confuso. Specifica che ogni cella dell'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 questo ordine)
Infine, aggiungi il valore per il vincolo.
È importante ricordare che è possibile selezionare più celle per il riferimento di 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.
È 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ò scendere sotto le quattro ore.
- Il lavoro 2 deve essere superiore a otto ore .
- Il lavoro 5 deve essere inferiore alle undici ore .
- Il totale delle ore lavorate deve 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 che ho creato una copia del tavolo in modo da non sovrascrivere quella originale, che contiene il nostro orario di lavoro corrente.
E in secondo luogo, vedi che i valori nei vincoli maggiore di e minore di uno sono più alti o più bassi di quanto ho detto sopra. Questo perché non ci sono opzioni superiori o inferiori a. Ci sono solo più di-o-uguale-e-meno-o-uguale a.
Colpiamo Solve e vediamo 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 che Keep Solver Solution sia selezionato e premi OK .
Se vuoi maggiori informazioni, però, puoi selezionare un rapporto dal lato destro della finestra. Seleziona tutti i report che vuoi, 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 una 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 pulsante Opzioni . Puoi dire a Excel sull'intero numero di ottimali, impostare i vincoli di tempo di calcolo (utile per enormi set di dati) e regolare come i metodi di risoluzione GRG ed evolutivi 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!