Risolutore Excel: ottimizzazione dei risultati, aggiunta di vincoli e salvataggio di soluzioni come scenari

Si imposta il modello del Risolutore Excel utilizzando la finestra di dialogo Parametri Risolutore. Si utilizza la casella Imposta obiettivo per specificare la cella obiettivo e si utilizza il gruppo A per dire a Risolutore Excel cosa si desidera dalla cella obiettivo: il valore massimo possibile; il valore minimo possibile; o un valore specifico. Infine, si utilizza la casella Modificando le celle variabili per specificare le celle che il Risolutore può utilizzare per inserire i valori per ottimizzare il risultato.

Ottimizzazione dei risultati del Risolutore Excel

Quando il Risolutore trova una soluzione, puoi scegliere Mantieni la soluzione del Risolutore o Ripristina i valori originali. Se scegli Keep Solver Solution, Excel modifica in modo permanente il foglio di lavoro. Non puoi annullare le modifiche.

Con il tuo modello di foglio di lavoro pronto per il Risolutore pronto per l'uso, ecco i passaggi da seguire per trovare un risultato ottimale per il tuo modello utilizzando il Risolutore:

Scegli Dati → Risolutore.
Excel apre la finestra di dialogo Parametri del risolutore.

Nella casella Imposta obiettivo, inserisci l'indirizzo della cella obiettivo del tuo modello.
Si noti che se si fa clic sulla cella per inserirla, il Risolutore inserisce automaticamente un indirizzo di cella assoluto (ad esempio, $B$14 invece di B14). Il risolutore funziona bene in entrambi i casi.

Nel gruppo A, seleziona un'opzione:

• Max: restituisce il valore massimo possibile.
• Min: restituisce il valore minimo possibile.
• Valore di: immettere un numero per impostare la cella obiettivo su quel numero.

  Per il modello di esempio, è stato selezionato Valore di e 0 è stato immesso nella casella di testo.

Nella casella Modificando le celle variabili, inserisci gli indirizzi delle celle che vuoi che il Risolutore cambi mentre cerca una soluzione.
Nell'esempio, le celle che cambiano sono B4 e C4. L'immagine seguente mostra la finestra di dialogo Parametri del risolutore completata.

La finestra di dialogo Parametri del risolutore completata.

Fare clic su Risolvi.
Il risolutore si mette al lavoro. Mentre il Risolutore lavora sul problema, potresti vedere le finestre di dialogo Mostra soluzione di prova mostrate una o più volte.

In qualsiasi finestra di dialogo Mostra soluzione di prova visualizzata, fai clic su Continua per spostare le cose.
Al termine dell'ottimizzazione, Excel visualizza la finestra di dialogo Risultati del risolutore.

La finestra di dialogo Risultati del risolutore e la soluzione al problema del pareggio.

Seleziona l'opzione Mantieni la soluzione del risolutore.
Se non vuoi accettare il risultato, seleziona invece l'opzione Ripristina valori originali.

Fare clic su OK.

Puoi chiedere al Risolutore di visualizzare uno o più rapporti che forniscono informazioni aggiuntive sui risultati. Nella finestra di dialogo Risultati del risolutore, utilizzare l'elenco Rapporti per selezionare ogni rapporto che si desidera visualizzare:

• Risposta: visualizza informazioni sulla cella obiettivo, sulle celle variabili e sui vincoli del modello. Per la cella obiettivo e le celle variabili, Risolutore mostra i valori originali e finali.
• Sensibilità: tenta di mostrare quanto una soluzione sia sensibile ai cambiamenti nelle formule del modello. Il layout del rapporto sulla sensibilità dipende dal tipo di modello che stai utilizzando.
• Limiti: visualizza la cella obiettivo e il relativo valore, nonché le celle variabili e i relativi indirizzi, nomi e valori.

Excel Solver può utilizzare uno dei diversi metodi di risoluzione. Nella finestra di dialogo Parametri del risolutore, utilizzare l'elenco Seleziona un metodo di risoluzione per selezionare una delle seguenti opzioni:

• Simplex LP: utilizzare se il modello del foglio di lavoro è lineare. Nei termini più semplici possibili, un modello lineare è quello in cui le variabili non sono elevate a nessuna potenza e non viene utilizzata nessuna delle cosiddette funzioni trascendenti, come SIN e COS.
• GRG Non lineare: utilizzare se il modello del foglio di lavoro è non lineare e liscio. In termini generali, un modello liscio è quello in cui un grafico dell'equazione utilizzata non mostra spigoli vivi o interruzioni.
• Evolutivo: utilizzare se il modello del foglio di lavoro non è lineare e non uniforme.

Ti devi preoccupare di tutto questo? Quasi certamente no. Il Risolutore Excel utilizza per impostazione predefinita GRG Non lineare e dovrebbe funzionare per quasi tutto ciò che fai con Risolutore.

Aggiunta di vincoli a Excel Solver

Il mondo reale pone restrizioni e condizioni alle formule. Una fabbrica potrebbe avere una capacità massima di 10.000 unità al giorno, il numero di dipendenti di un'azienda non può essere un numero negativo e i costi pubblicitari potrebbero essere limitati al 10% delle spese totali.

Allo stesso modo, supponi di eseguire un'analisi di pareggio su due prodotti. Se esegui l'ottimizzazione senza alcuna restrizione, il Risolutore potrebbe raggiungere un profitto totale di 0 impostando un prodotto con una leggera perdita e l'altro con un leggero profitto, dove la perdita e il profitto si annullano a vicenda. In effetti, se osservi da vicino l'immagine precedente, questo è esattamente ciò che ha fatto Risolutore. Per ottenere una vera soluzione di pareggio, potresti preferire vedere entrambi i valori di profitto del prodotto come 0.

Tali restrizioni e condizioni sono esempi di ciò che il Risolutore chiama vincoli. L'aggiunta di vincoli indica al Risolutore di trovare una soluzione in modo che queste condizioni non vengano violate.

Ecco come eseguire Risolutore con i vincoli aggiunti all'ottimizzazione:

Scegli Dati → Risolutore.
Excel apre la finestra di dialogo Parametri del risolutore.

Utilizzare la casella Imposta obiettivo, il gruppo A e la casella Modificando le celle variabili per impostare il Risolutore come descritto sopra.

Fare clic su Aggiungi.
Excel visualizza la finestra di dialogo Aggiungi vincolo.

Nella casella Riferimento cella, inserisci l'indirizzo della cella che desideri vincolare.
È possibile digitare l'indirizzo o selezionare la cella nel foglio di lavoro.

Nell'elenco a discesa, seleziona l'operatore che desideri utilizzare.
Nella maggior parte dei casi si utilizza un operatore di confronto, ad esempio uguale a (=) o maggiore di (>). Utilizzare l'operatore int (intero) quando è necessario che un vincolo, ad esempio dipendenti totali, sia un valore intero anziché un numero reale (ovvero un numero con una componente decimale; non puoi avere 10,5 dipendenti!). Utilizzare l'operatore bin (binario) quando si dispone di un vincolo che deve essere VERO o FALSO (o 1 o 0).

If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.
This image shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

The completed Add Constraint dialog box.

To specify more constraints, click Add and repeat Steps 4 through 6, as needed.

For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.

Click OK.

Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.

Click Solve.

In qualsiasi finestra di dialogo Mostra soluzione di prova visualizzata, fai clic su Continua per spostare le cose.
L'immagine seguente mostra l'esempio di una soluzione di pareggio con i vincoli aggiunti. Si noti che non solo la cella Profitto totale (B14) è impostata su 0, ma lo sono anche le due celle Profitto prodotto (B12 e C12).

La finestra di dialogo Risultati del risolutore e la soluzione finale al problema di pareggio.

Seleziona l'opzione Mantieni la soluzione del risolutore.
Se non vuoi accettare il risultato, seleziona invece l'opzione Ripristina valori originali.

Fare clic su OK.

Puoi aggiungere un massimo di 100 vincoli. Inoltre, se è necessario apportare una modifica a un vincolo prima di iniziare a risolverlo, selezionare il vincolo nella casella di riepilogo Oggetto dei vincoli, fare clic su Modifica, quindi apportare le modifiche nella finestra di dialogo Modifica vincolo visualizzata. Se desideri eliminare un vincolo che non ti serve più, seleziona il vincolo e fai clic su Elimina.

Salva una soluzione Excel Solver come scenario

Ogni volta che hai un modello di foglio di calcolo che utilizza un insieme coerente di valori di input, noto come celle che cambiano, hai quello che Excel chiama uno scenario. Con Risolutore, queste celle che cambiano sono le sue celle variabili, quindi una soluzione Risolutore equivale a una sorta di scenario in Excel . Tuttavia, Risolutore non offre un modo semplice per salvare ed eseguire nuovamente una soluzione particolare. Per ovviare a questo problema, è possibile salvare una soluzione come scenario che sarà poi possibile richiamare utilizzando la funzionalità Gestione scenari di Excel.

Segui questi passaggi per salvare una soluzione del Risolutore come scenario:

Scegli Dati → Risolutore.
Excel apre la finestra di dialogo Parametri del risolutore.

Utilizzare la casella Imposta obiettivo, il gruppo A, la casella Modificando le celle variabili e l'elenco Soggetto ai vincoli per impostare il Risolutore come descritto sopra.

Fare clic su Risolvi.

Ogni volta che viene visualizzata la finestra di dialogo Mostra soluzione di prova, scegli Continua.
Al termine dell'ottimizzazione, Excel visualizza la finestra di dialogo Risultati del risolutore.

Fare clic su Salva scenario.
Excel visualizza la finestra di dialogo Salva scenario.

Nella finestra di dialogo Nome scenario, digitare un nome per lo scenario e quindi fare clic su OK.
Excel ti riporta alla finestra di dialogo Risultati del risolutore.

Seleziona l'opzione Mantieni la soluzione del risolutore.
Se non vuoi accettare il risultato, seleziona invece l'opzione Ripristina valori originali.

Fare clic su OK.