Come utilizzare il risolutore di Excel 2019

Sebbene i comandi Tabella dati e Ricerca obiettivo di Excel funzionino perfettamente per problemi semplici che richiedono la determinazione della relazione diretta tra gli input e i risultati in una formula, è necessario utilizzare il componente aggiuntivo Risolutore quando si affrontano problemi più complessi. Ad esempio, usa il Risolutore per trovare la soluzione migliore quando devi modificare più valori di input nel tuo modello Excel e devi imporre vincoli su questi valori e/o sul valore di output.

Il componente aggiuntivo Risolutore funziona applicando metodi iterativi per trovare la soluzione "migliore" dati gli input, la soluzione desiderata e i vincoli imposti. Ad ogni iterazione, il programma applica un metodo per tentativi ed errori (basato sull'uso di equazioni e disequazioni lineari o non lineari) che tenta di avvicinarsi alla soluzione ottima.

Quando si utilizza il componente aggiuntivo Risolutore, tenere presente che molti problemi, specialmente quelli più complicati, hanno molte soluzioni. Sebbene il Risolutore restituisca la soluzione ottimale, dati i valori iniziali, le variabili che possono cambiare e i vincoli che definisci, questa soluzione spesso non è l'unica possibile e, in effetti, potrebbe non essere la soluzione migliore per te. Per essere sicuro di trovare la soluzione migliore, potresti voler eseguire il Risolutore più di una volta, regolando i valori iniziali ogni volta che risolvi il problema.

Quando si imposta il problema per il componente aggiuntivo Risolutore nel foglio di lavoro Excel, definire i seguenti elementi:

• Cella obiettivo: la cella di destinazione nel foglio di lavoro il cui valore deve essere massimizzato, ridotto a icona o fatto in modo che raggiunga un valore particolare. Nota che questa cella deve contenere una formula.
• Celle variabili: le celle che cambiano nel foglio di lavoro i cui valori devono essere modificati fino a trovare la risposta.
• Celle di vincolo: le celle che contengono i limiti imposti ai valori variabili nelle celle variabili e/o nella cella obiettivo nella cella obiettivo.

Dopo aver definito il problema con questi parametri e aver risolto il problema con il componente aggiuntivo Risolutore, il programma restituisce la soluzione ottimale modificando i valori nel foglio di lavoro. A questo punto, puoi scegliere di mantenere le modifiche nel foglio di lavoro o ripristinare i valori originali nel foglio di lavoro. È inoltre possibile salvare la soluzione come scenario da visualizzare in un secondo momento prima di ripristinare i valori originali.

È possibile utilizzare il componente aggiuntivo Risolutore con lo Scenario Manager per aiutare a impostare un problema da risolvere o per salvare una soluzione in modo da poterla visualizzare in un secondo momento. Le celle variabili che definisci per lo Scenario Manager vengono automaticamente raccolte e utilizzate dal Risolutore quando selezioni questo comando e viceversa. Inoltre, è possibile salvare la soluzione del Risolutore a un problema come scenario (facendo clic sul pulsante Salva scenario nella finestra di dialogo Risolutore) che è possibile visualizzare con lo Scenario Manager.

Impostazione e definizione del problema in Excel 2019

Il primo passaggio nell'impostazione di un problema su cui lavorare il Risolutore consiste nel creare il modello del foglio di lavoro per il quale definirete la cella obiettivo, le celle delle variabili e le celle dei vincoli.

Tieni presente che il Risolutore è un'utilità aggiuntiva. Ciò significa che, prima di poterlo utilizzare, è necessario assicurarsi che il programma aggiuntivo Risolutore sia ancora caricato, come indicato dalla comparsa del pulsante Risolutore nel gruppo Analisi alla fine della scheda Dati sulla barra multifunzione. Se questo pulsante non è presente, è possibile caricare il Risolutore aprendo la scheda Componenti aggiuntivi della finestra di dialogo Opzioni di Excel (Alt+FTAA) e quindi facendo clic sul pulsante Vai dopo essersi assicurati che i componenti aggiuntivi di Excel siano visualizzati nel menu a discesa Gestisci casella di riepilogo alla sua immediata sinistra. Quindi, selezionare la casella di controllo Componente aggiuntivo Risolutore nella finestra di dialogo Componenti aggiuntivi per inserire un segno di spunta prima di fare clic su OK per chiudere la finestra di dialogo e ricaricare il componente aggiuntivo.

Per definire e risolvere un problema con il componente aggiuntivo Risolutore dopo aver caricato il componente aggiuntivo e creato il modello del foglio di lavoro, segui questi passaggi:

Fare clic sul pulsante di comando Risolutore nel gruppo Analizza alla fine della scheda Dati della barra multifunzione.

Excel apre la finestra di dialogo Parametri del risolutore.

Specificare i parametri da applicare al modello nella finestra di dialogo Parametri del risolutore.

Fare clic sulla cella di destinazione nel foglio di lavoro o immettere il riferimento di cella o il nome dell'intervallo nella casella di testo Imposta obiettivo.

Successivamente, è necessario selezionare l'impostazione A. Fare clic sul pulsante di opzione Max quando si desidera che il valore della cella di destinazione sia il più grande possibile. Fare clic sul pulsante di opzione Min quando si desidera che il valore della cella di destinazione sia il più piccolo possibile. Fare clic sul pulsante di opzione Valore di e quindi immettere un valore nella casella di testo associata quando si desidera che il valore della cella di destinazione raggiunga un valore particolare.

Fare clic sull'opzione appropriata del pulsante di opzione nella sezione A della finestra di dialogo. Se si seleziona il pulsante di opzione Valore di, immettere il valore da abbinare nella casella di testo associata.

Quindi, designa le celle variabili, ovvero quelle che il Risolutore può modificare per raggiungere il tuo obiettivo Uguale a.

Fare clic sulla casella di testo Modificando le celle variabili e quindi selezionare le celle da modificare nel foglio di lavoro o immettere i riferimenti di cella o il nome dell'intervallo nella casella di testo.

Ricorda che per selezionare celle non adiacenti nel foglio di lavoro, devi tenere premuto il tasto Ctrl mentre fai clic su ciascuna cella nella selezione. Per fare in modo che Excel scelga per te le celle che cambiano in base alla cella di destinazione selezionata, fai clic sul pulsante Indovina a destra di questa casella di testo.

Prima di fare in modo che il Risolutore regoli il tuo modello, puoi aggiungere vincoli per la cella di destinazione o una qualsiasi delle celle che cambiano che determinano i suoi limiti durante la regolazione dei valori.

(Facoltativo) Fare clic sul pulsante Aggiungi a destra della casella di riepilogo Soggetto ai vincoli nella finestra di dialogo Parametri del risolutore.

Questa azione apre la finestra di dialogo Aggiungi vincolo. Quando si definisce un vincolo, scegliere la cella di cui si desidera vincolare il valore o selezionare la cella nel foglio di lavoro oppure immettere il riferimento di cella nella casella di testo Riferimento cella. Quindi seleziona la relazione (=, <=,>= o int per intero o bin per binario) dalla casella di riepilogo a discesa a destra e (a meno che tu non abbia scelto int o bin ) inserisci il valore appropriato o il riferimento di cella nel Casella di testo Vincolo.

Per continuare ad aggiungere vincoli per altre celle utilizzate dal Risolutore, fare clic sul pulsante Aggiungi per aggiungere il vincolo e cancellare le caselle di testo nella finestra di dialogo Aggiungi vincolo. Quindi, ripeti il ​​passaggio 5 per aggiungere un nuovo vincolo. Dopo aver definito i vincoli per la cella di destinazione e aver modificato i valori nel modello, fare clic su OK per chiudere la finestra di dialogo Aggiungi vincolo e tornare alla finestra di dialogo Parametri del risolutore (che ora elenca i vincoli nella casella di riepilogo Oggetto dei vincoli).

(Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7.

(Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.

Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.

Solving the problem with Excel’s Solver

When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.

When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.

The Solver Results dialog box showing that Solver found a solution to the problem.

Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.

Changing Excel’s Solver options

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.

When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.

Saving and loading a model problem in Excel 2019

The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.

Quando si fa clic sul pulsante Carica/Salva, Excel apre la finestra di dialogo Carica/Salva modello, contenente una casella di testo Seleziona area modello. Questa casella di testo contiene i riferimenti di cella per un intervallo sufficientemente ampio da contenere tutti i parametri del problema, a partire dalla cella attiva. Per salvare i parametri del problema in questo intervallo, fare clic su OK. Se questo intervallo include celle con dati esistenti, è necessario modificare il riferimento di cella in questa casella di testo prima di fare clic su OK per impedire a Excel di sostituire i dati esistenti.

Dopo aver fatto clic su OK, Excel copia i parametri del problema nell'intervallo specificato. Questi valori vengono quindi salvati come parte del foglio di lavoro la prossima volta che si salva la cartella di lavoro. Per riutilizzare questi parametri del problema durante la risoluzione di un problema, è sufficiente aprire la finestra di dialogo Opzioni del risolutore, fare clic sul pulsante Carica/Salva per aprire la finestra di dialogo Carica/Salva modello, fare clic sul pulsante Carica e quindi selezionare l'intervallo contenente il salvataggio parametri del problema. Quando si fa clic su OK nella finestra di dialogo Carica modello, Excel carica i parametri da questo intervallo di celle nelle caselle di testo appropriate nella finestra di dialogo Parametri del risolutore. È quindi possibile chiudere la finestra di dialogo Opzioni del risolutore facendo clic su OK e risolvere il problema utilizzando questi parametri facendo clic sul pulsante di comando Risolvi.

Ricorda che puoi usare il pulsante Reimposta tutto ogni volta che vuoi cancellare tutti i parametri definiti per il problema precedente e riportare le opzioni del Risolutore ai loro valori predefiniti.

Creazione di report del Risolutore in Excel 2019

Puoi creare tre diversi tipi di report con il Risolutore:

• Rapporto di risposta: elenca la cella di destinazione e le celle modificate con i loro valori originali e finali, insieme ai vincoli utilizzati per risolvere il problema.
• Rapporto sulla sensibilità: indica quanto è sensibile una soluzione ottimale alle modifiche nelle formule che calcolano la cella di destinazione e i vincoli. Il report mostra le celle che cambiano con i loro valori finali e il gradiente ridotto per ogni cella. (Il gradiente ridotto misura l'obiettivo per unità di aumento nella cella che cambia.) Se sono stati definiti dei vincoli, il rapporto Sensibilità li elenca con i loro valori finali e il moltiplicatore di Lagrange per ciascun vincolo. (Il moltiplicatore di Lagrange misura l'obiettivo per unità di aumento che appare nella parte destra dell'equazione del vincolo.)
• Rapporto sui limiti: mostra la cella di destinazione e le celle che cambiano con i relativi valori, i limiti inferiore e superiore e i risultati di destinazione. Il limite inferiore rappresenta il valore più basso che può avere una cella che cambia mentre fissa i valori di tutte le altre celle e continua a soddisfare i vincoli. Il limite superiore rappresenta il valore più alto che farà ciò.

Excel inserisce ogni report generato per un problema del Risolutore in un foglio di lavoro separato nella cartella di lavoro. Per generare uno (o tutti) questi report, selezionare il tipo di report (Risposta, Sensibilità o Limiti) dalla casella di riepilogo Report della finestra di dialogo Risultati del risolutore. Per selezionare più di un rapporto, fai clic sul nome del rapporto.

Quando si fa clic su OK per chiudere la finestra di dialogo Risultati Risolutore (dopo aver scelto tra le opzioni Mantieni soluzione Risolutore e Ripristina valori originali), Excel genera il rapporto (o i rapporti) selezionato in un nuovo foglio di lavoro che aggiunge all'inizio della cartella di lavoro . (Le schede del foglio dei rapporti sono denominate in base al tipo di rapporto, come in Rapporto risposte 1, Rapporto sulla riservatezza 1 e Rapporto sui limiti 1. )