Excel, il Risolutore

[Pubblichiamo un estratto da Costruire applicazioni con Excel 2016, dedicato a una funzione molto potente e altrettanto complessa, da usare quando le variabili sono più di una e per trovare la risposta bisogna calcolarla.] Diciamo subito che avere a disposizione il Risolutore non basta, ci vuole anche una discreta competenza in tema di modelli di gestione. Cominciamo subito con un esempio molto semplice per capire di che cosa stiamo parlando.

Mandare avanti un’impresa

Abbiamo la responsabilità di una piccola ditta che fabbrica tre prodotti, ciascuno dei quali genera un diverso margine di utile:

Alfa    137
Beta    158
Gamma   205

Il prodotto Gamma è quello che rende di più, quindi potremmo decidere di fabbricare soltanto quello, lasciando perdere gli altri due. Ma la vita non è così semplice. In base ai dati sul mercato dei nostri prodotti e sulla capacità produttiva installata sappiamo che:

• dobbiamo produrre almeno 50 pezzi del prodotto Alfa, perché c’è un ordine confermato da evadere;
• dobbiamo produrre almeno 40 pezzi del prodotto Beta perché ci è stato anticipato con e-mail un ordine di un cliente importante;
• il prodotto Gamma rende molto, è vero, ma è un prodotto di nicchia: non possiamo produrne più di 40 pezzi, perché volumi maggiori resterebbero in magazzino, dove si deteriorerebbero;
• la capacità produttiva della nostra fabbrica è di 300 pezzi al giorno, per qualsiasi combinazione di prodotti.

Stando così le cose, facciamo intervenire il Risolutore dopo aver preparato su un foglio di lavoro i dati di partenza, nell’ipotesi di suddividere in parti uguali la capacità produttiva fra i tre prodotti. Ricordiamo che lo strumento Risolutore è disponibile soltanto dopo che è stato installato come componente aggiuntivo di Excel, selezionandolo nella sezione Opzioni.

Da Dati/Analisi scegliamo Risolutore: si presenta la finestra di dialogo Parametri Risolutore. Eseguiamo le seguenti operazioni.

• Nella casella Imposta obiettivo immettiamo D6 (digitandolo o facendo clic sulla cella).
• Facciamo clic sul pulsante di opzione Max.
• Nella casella Modificando le celle variabili immettiamo B3:B5, da tastiera o trascinando il mouse sull’intervallo.

I riferimenti alle celle, comunque immessi, verranno trasformati in riferimenti assoluti. Dopo queste prime immissioni la finestra di dialogo dovrebbe presentarsi come nella prossima figura.

La finestra di dialogo Parametri Risolutore con i primi dati di partenza.

Il significato di queste impostazioni dovrebbe essere evidente, comunque precisiamolo.

1. La cella obiettivo è quella dove vogliamo che compaia la soluzione del nostro problema. Il problema è sinteticamente indicato come Max, ovvero, vogliamo ottenere il valore più elevato per D6 (la cella che contiene la somma dei margini di utile generati dai tre prodotti).
2. La casella Modificando le celle variabili contiene i valori sui quali chiediamo al Risolutore di agire: dovrà dirci quanto dobbiamo produrre di Alfa, Beta e Gamma per soddisfare l’obiettivo di massimizzare il margine totale.

Manca ancora qualcosa, importante almeno quanto i parametri che abbiamo già passato al Risolutore: dobbiamo fargli sapere i vincoli che dovrà rispettare quando proverà a cambiare i valori dell’intervallo B3:B5 per portare al massimo il valore della cella D6.

Per passare le informazioni sui vincoli, facciamo clic sul pulsante Aggiungi. Si apre una piccola finestra di dialogo, intitolata Aggiungi vincolo, nella quale si definisce un primo vincolo agendo sulle caselle e sui controlli (si veda la figura qui sotto). Per ogni nuovo vincolo da aggiungere si fa clic sul pulsante Aggiungi di questa finestra, in modo che se ne presenti una nuova. Esauriti i vincoli, si fa clic sul pulsante OK dell’ultima finestra di dialogo.

In questa finestra si definiscono uno per volta i vincoli per il Risolutore.

Nella casella Riferimento si immette la cella che contiene il valore da considerare come vincolo (nel nostro caso sono i volumi da produrre, le celle dell’intervallo B3:B5 che abbiamo già indicato nella finestra precedente).

Successivamente si fa clic sul pulsante a freccia della casella al centro, chiamata Vincolo e nell’elenco a discesa si sceglie l’operatore aritmetico che definisce la natura del vincolo. Infine, subito a destra, si scrive il valore numerico richiesto.

La formulazione aritmetica dei vincoli che abbiamo indicato prima in parole è la seguente:

B3 >= 50
B4 >= 40
B5 <= 40
B6 = 300

Dopo aver messo a punto i quattro vincoli, il clic su OK nell’ultima finestra Aggiungi vincolo ci riporta alla finestra di dialogo Parametri Risolutore, che nella casella di selezione Soggette ai vincoli ora presenta in bell’ordine e tutti insieme i vincoli che avevano immesso uno per uno. In questa finestra di dialogo premiamo il pulsante Risolvi e dopo qualche istante possiamo vedere il risultato al quale è arrivato il Risolutore.

Dalla finestra di dialogo Risultati Risolutore si può decidere se accettare la soluzione trovata (nel qual caso verranno sostituiti i dati variabili con quelli nuovi) o ripristinare i valori originali. È possibile, agendo su un opportuno pulsante, salvare la soluzione come scenario e da una casella di selezione si possono scegliere tre possibili prospetti, generati automaticamente, nei quali il Risolutore fa vedere come è arrivato ai risultati che ha ottenuto.

Premendo il pulsante di comando Opzioni si apre una finestra di dialogo omonima articolata in tre schede, nella quale si può impostare una serie di parametri dello strumento Risolutore; per questi parametri sarà bene tener presente che, se non si ha confidenza con le tecniche e la terminologia dei modelli di gestione matematico/statistici, non è neppure il caso di provare a modificare qualche opzione.

Il Risolutore ha trovato una soluzione.

Gli utenti di Excel 2016 possono utilmente visitare il sito solver.com, gestito dalla società Frontline Systems, che è l’autrice di questo prodotto concesso in licenza alla Microsoft: vi troveranno un’ottima guida passo per passo per l’utilizzo di questo strumento.

La pagina di guida passo per passo del sito web della Frontline Systems.