Excel Risolutore: ottimizza decisioni complesse con più variabili

L’analisi di simulazione (what-if) ti permette di testare diversi scenari per vedere come influenzano i risultati. Ti offre uno spazio sicuro per sperimentare con variabili come costi o capacità e scoprire la soluzione più efficace. Questo approccio è fondamentale per chi gestisce risorse, pianifica orari o prende decisioni strategiche di business.

Il tentativo ed errore manuale richiede ore e spesso non trova la risposta ottimale. Il Risolutore garantisce l’ottimalità matematica in pochi secondi. È la differenza tra indovinare l’allocazione delle risorse e sapere di star prendendo la migliore decisione possibile.

Questo tutorial ti guida nel Risolutore di Excel, la funzione avanzata pensata per questi problemi di ottimizzazione. Ti mostrerò come impostare il foglio di calcolo, eseguire l’analisi e interpretare i risultati con esempi reali.

Capire il Risolutore di Excel: concetti di base

Prima di mostrarti come usare il Risolutore, ti spiego i tre mattoni fondamentali di ogni problema di ottimizzazione. Non sono solo parole d’ordine: sono ciò che fa funzionare il Risolutore.

Cella obiettivo, variabili decisionali e vincoli

Pensa alla cella obiettivo come al tuo traguardo. È la formula che vuoi massimizzare o minimizzare, come il profitto totale o il costo totale. Deve essere una formula, non solo un numero, perché il Risolutore deve vedere come cambia quando modifichi le altre cose.

Le variabili decisionali sono ciò che stai cercando di determinare. Quante sedie dovremmo produrre? Quanto spendere su ciascun canale di marketing? Le imposto sempre a zero all’inizio: così è facile vedere cosa è cambiato dopo l’esecuzione del Risolutore.

I vincoli sono i tuoi limiti e le tue regole. Non puoi spendere più del budget. Non puoi produrre quantità negative. Alcuni progetti richiedono investimenti minimi. Questi vincoli rendono il problema matematico realistico, in linea con i limiti effettivi del business.

Come funziona: il Risolutore aggiusta le variabili decisionali per ottimizzare l’obiettivo assicurandosi che tutti i vincoli siano rispettati. Se hai fatto ricerca operativa, ti suonerà familiare. Altrimenti, pensa a un tentativo ed errore intelligente, ma con garanzie.

Confronto con Ricerca obiettivo

Me lo chiedono spesso. Ricerca obiettivo è più semplice. Cambia una cella per raggiungere un target specifico. Usala quando conosci il risultato che vuoi e ti serve solo trovare l’input che ci porta lì.

Il Risolutore è diverso. Trova la soluzione migliore quando hai più decisioni da prendere e vincoli da rispettare. Il Risolutore si usa se devi massimizzare o minimizzare qualcosa gestendo diverse variabili.

La mia regola pratica: se puoi dire esattamente quale numero vuoi, usa Ricerca obiettivo. Se ti chiedi "qual è il modo migliore per farlo?", usa il Risolutore.

Caricare e attivare il componente aggiuntivo Risolutore

Il Risolutore è incluso in Excel, ma va attivato. Tranquillo, ci vuole meno di un minuto.

Passaggi di installazione per Windows e Mac

Per Windows:

1. File > Opzioni > Componenti aggiuntivi
2. Seleziona Componenti aggiuntivi di Excel dal menu Gestisci e fai clic su Vai
3. Spunta Componente aggiuntivo Risolutore, poi fai clic su OK
4. Vai alla scheda Dati e trova Risolutore nel gruppo Analizza

Per Mac:

1. Strumenti > Componenti aggiuntivi di Excel
2. Spunta Componente aggiuntivo Risolutore e fai clic su OK

Se usi Excel Online, cattive notizie: non supporta il Risolutore. Ti serve la versione desktop (2016 o successiva).

Configurazione

Una volta attivato, accedi al Risolutore dalla scheda Dati > pulsante Risolutore. Le impostazioni predefinite vanno bene per la maggior parte dei problemi, quindi non pensarci troppo.

Detto questo, due impostazioni contano se riscontri problemi. Precisione controlla quanto è rigido il Risolutore nel soddisfare i vincoli. La aumento per i modelli finanziari quando i centesimi contano. Convergenza indica quando il Risolutore deve smettere di migliorare la soluzione. La abbasso se il Risolutore si ferma troppo presto con una risposta non del tutto ottimale.

Puoi trovare entrambe nel dialogo Parametri del Risolutore > pulsante Opzioni, ma sinceramente le tocco di rado.

Impostare il problema: formulazione del modello in Excel

Un foglio pulito rende facile configurare il Risolutore.

Ti guido con un esempio: un produttore di mobili realizza sedie e tavoli. Le sedie generano 60 $ di profitto (servono 4 ore di lavoro, 2 unità di legno). I tavoli generano 75 $ di profitto (servono 6 ore di lavoro, 2 unità di legno). Abbiamo 240 ore di lavoro e 100 unità di legno disponibili. Quanti di ciascuno dovremmo produrre?

Definizione del problema e impostazione del foglio

Organizzo ogni modello per il Risolutore nello stesso modo. Quattro sezioni, sempre:

1. Dati di input (info sui prodotti, limiti delle risorse)
2. Variabili decisionali (quantità da produrre)
3. Funzione obiettivo (profitto totale)
4. Calcoli dei vincoli (risorse usate vs. disponibili)

Modello completo del Risolutore con tutte e quattro le sezioni. Immagine dell’autore.

Questo layout rende tutto trasparente. Chiunque guardi il modello può vedere subito cosa stai ottimizzando e con quali limitazioni lavori.

Creo sempre quelli che si chiamano intervalli con nome per le celle importanti. Invece di scrivere =B8*B3+B9*B4, posso scrivere =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Molto più facile da capire sei mesi dopo, quando avrai dimenticato cosa significavano quei riferimenti di cella.

Gli intervalli con nome rendono le formule leggibili. Immagine dell’autore.

Per crearli: seleziona le celle, fai clic sulla Casella Nome, digita un nome e premi Invio. Due secondi ora, mal di testa evitati dopo.

Variabili decisionali e funzione obiettivo

Imposto le variabili decisionali a zero. Aiuta a fare confronti. Alcuni preferiscono partire da valori stimati, va bene, ma lo zero per me funziona.

La funzione obiettivo deve essere una formula. Per l’esempio dei mobili, uso:

=SUMPRODUCT(B8:B9,B3:B4)

Calcola: (Sedie × 60 $) + (Tavoli × 75 $)

Semplici moltiplicazioni e somme. È tutto ciò che serve per la maggior parte dei problemi di business.

Vincoli

I vincoli traducono i limiti reali del business in matematica. Il nostro produttore di mobili ha due vincoli sulle risorse:

• Lavoro: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Legno: =SUMPRODUCT(B8:B9,D3:D4) <= 100

E una cosa che ho imparato a mie spese: aggiungi sempre i vincoli di non negatività (B8:B9 >= 0). Senza di essi, il Risolutore potrebbe suggerire di produrre sedie negative, cosa che ovviamente non funziona nel mondo reale.

Aggiungo anche una colonna di stato con formule =IF() come  =IF(B16<=C16,"OK","EXCEEDED"). Mi dà un riscontro visivo immediato ancora prima di eseguire il Risolutore. Verde vuol dire via libera, rosso segnala che c’è qualcosa che non va nell’impostazione.

Passo dopo passo: come usare il Risolutore in Excel

Ok, il modello è pronto. Ora ti mostro come eseguire il Risolutore. Questa parte richiede circa 30 secondi, una volta capito il flusso.

Uso della finestra Parametri del Risolutore

Apri il Risolutore facendo clic sulla scheda Dati > pulsante Risolutore.

Finestra del Risolutore prima della configurazione. Immagine dell’autore.

Ecco cosa compilare:

Imposta obiettivo: Clicca nel campo, poi fai clic sulla cella B12 (la nostra cella Profitto totale).

A: Seleziona Max perché vogliamo massimizzare il profitto.

Modificando le celle variabili: Seleziona B8:B9 (le quantità che stiamo decidendo).

Soggetto ai vincoli: Fai clic su Aggiungi per ciascun vincolo:

• Lavoro: $B$16 <= $C$16

• Legno: $B$17 <= $C$17

• Non negatività: $B$8:$B$9 >= 0

Seleziona un metodo di risoluzione: scegli Simplex LP. Spiegherò gli altri metodi più avanti, ma per problemi lineari come questo, Simplex LP è quello giusto.

Risolutore configurato e pronto. Immagine dell’autore.

Risoluzione del modello

Prima di fare clic su Risolvi, dai un’occhiata ai valori attuali. Dovrebbe essere tutto a zero.

Stato iniziale con zeri ovunque. Immagine dell’autore.

Ora fai clic su Risolvi. Per problemi semplici come il nostro, bastano pochi secondi.

Successo! Il Risolutore ha trovato la soluzione ottimale. Immagine dell’autore.

Quando compare la finestra Risultati del Risolutore, dovrebbe dire "Il Risolutore ha trovato una soluzione." Spunta le caselle Risposta e Sensibilità sotto Rapporti. Forniscono un’analisi dettagliata che vedremo dopo. Poi fai clic su OK.

La soluzione ottimale: 30 sedie e 20 tavoli. Immagine dell’autore.

Ed eccoci. La soluzione mostra che dovremmo produrre 30 sedie e 20 tavoli per un profitto di 3.300 $. Nota che entrambi i vincoli mostrano "OK", segno che stiamo usando le risorse in modo efficiente senza superare i limiti.

Riepilogo del flusso di lavoro

Ecco il processo che seguo ogni volta:

1. Costruisci il modello (input, variabili, obiettivo, vincoli)
2. Configura il Risolutore (cosa ottimizzare e cosa variare)
3. Clicca su Risolvi e attendi
4. Verifica che la soluzione abbia senso per il business
5. Esamina i rapporti per approfondimenti

L’ultimo passaggio conta. Solo perché il Risolutore ha trovato una risposta non significa che sia quella giusta per il tuo business. Fai sempre un controllo di buon senso sui risultati.

Metodi del Risolutore, parametri e opzioni avanzate

Il Risolutore offre tre algoritmi diversi. Scegliere quello giusto fa una grande differenza in velocità e accuratezza.

Panoramica dei metodi di risoluzione

Tre metodi di risoluzione per diversi tipi di problemi. Immagine dell’autore.

Simplex LP

Simplex LP è quello che uso nell’80% dei casi. È per problemi lineari in cui tutto è somma e moltiplicazione. Mix di prodotti? Allocazione del budget? Pianificazione delle risorse? Simplex LP. È veloce e garantisce la migliore risposta.

GRG Nonlineare

GRG Nonlineare serve quando hai curve, quadrati o esponenziali nelle formule. Pensa a modelli di prezzo in cui la domanda cala all’aumentare del prezzo, ma non in linea retta. O all’ottimizzazione di portafoglio con calcoli di varianza. Trova ottimi locali, che potrebbero non essere il meglio assoluto, ma di solito sono più che sufficienti.

Evolutivo

Evolutivo è l’opzione di forza bruta. Usalo quando hai vincoli interi (puoi produrre solo unità intere), decisioni binarie (sì o no) o istruzioni =IF() nelle formule. È più lento perché in pratica prova un sacco di soluzioni casuali e tiene le migliori. Ma funziona quando gli altri metodi falliscono.

La mia regola di scelta è semplice: inizia con Simplex LP. Se le tue formule hanno esponenti o prodotti di variabili, passa a GRG Nonlineare. Se ti servono variabili intere o binarie, vai su Evolutivo.

Opzioni e impostazioni del Risolutore

Fai clic su Opzioni nella finestra del Risolutore per le impostazioni avanzate. Le modifico raramente, ma ecco cosa conta:

Limita per quanto tempo il Risolutore gira con Tempo max. Il default è 100 secondi, più che sufficienti per la maggior parte dei problemi. Lo aumento per modelli molto grandi che richiedono più tempo per "pensare".

Limita i tentativi del Risolutore con Iterazioni. Se raggiunge il limite senza risolvere, aumentalo.

Controlla quanto è rigoroso sui vincoli con Precisione. La aumento per i modelli finanziari in cui ogni centesimo conta, altrimenti la lascio com’è.

Generazione dei rapporti e analisi di sensibilità

Ricordi quando ti ho detto di spuntare le caselle dei rapporti? Ecco cosa ottieni.

Il Rapporto Risposta documenta la soluzione. Immagine dell’autore.

Il Rapporto Risposta è la tua traccia. Mostra cosa è cambiato: le variabili decisionali sono passate da 0 a 30 sedie e 20 tavoli, il profitto da 0 $ a 3.300 $. Dice anche quali vincoli sono vincolanti (usati al massimo) rispetto a quelli non vincolanti (con margine). Nel nostro caso, sia lavoro che legno sono vincolanti: stiamo usando tutta la capacità disponibile.

Di quanto possono cambiare gli input prima che cambi la soluzione. Immagine dell’autore.

Il Rapporto di sensibilità è dove diventa interessante. Le colonne Aumento/diminuzione consentiti mostrano di quanto possono variare gli input prima che serva una soluzione diversa. Intervalli ampi significano che la risposta è robusta. Intervalli stretti indicano che piccoli cambiamenti possono ribaltare tutto.

I prezzi ombra mostrano il valore delle risorse. Immagine dell’autore.

Ma il vero oro sono i prezzi ombra. Ti dicono quanto vale un’unità in più di una risorsa. Se il lavoro ha un prezzo ombra di 7,5 $, ottenere un’ora di lavoro in più aggiunge 7,5 $ al profitto. Quindi, se puoi assumere lavoro temporaneo a 5 $/ora, fallo. A 10 $/ora, lascia stare.

Importanza dell’analisi di sensibilità

Uso questi rapporti per rispondere a tre domande. In quali risorse dovrei investire? La mia soluzione è fragile? E se le previsioni fossero sbagliate? I rapporti danno risposte chiare a tutte e tre.

Per prima cosa, guardo i prezzi ombra per decidere in quali risorse investire. Un prezzo ombra alto significa alto valore.

Poi controllo gli intervalli consentiti per vedere se la soluzione è fragile. Se il profitto può cambiare solo di 1 $ prima che la soluzione cambi, sei sul filo del rasoio. Se può cambiare di 50 $, sei a posto.

Infine, mi chiedo e se le mie previsioni fossero sbagliate? Gli intervalli consentiti ti dicono quanto puoi sbagliare prima di dover ricalcolare.

Il mio consiglio: controlla sempre i prezzi ombra prima di approvare un budget. Se una risorsa ha prezzo ombra pari a zero, significa che hai capacità in eccesso. Investire lì è uno spreco. Metti i soldi dove i prezzi ombra sono più alti.

Risoluzione dei problemi ed errori

I messaggi di errore del Risolutore suonano minacciosi ma di solito sono facili da sistemare. Ecco quelli che vedo più spesso.

Errori e messaggi comuni

Il Risolutore non ha trovato una soluzione ammissibile

Significa che i tuoi vincoli sono in conflitto tra loro.

 Nessuna soluzione soddisfa tutti i vincoli. Immagine dell’autore.

Di solito accade quando chiedi qualcosa di impossibile, come cercare di raggiungere un target di profitto che le risorse non possono sostenere. La soluzione è rimuovere i vincoli uno alla volta finché il Risolutore non funziona; questo processo individua esattamente quale vincolo causa il conflitto.

I valori della cella impostata non convergono

Significa che il tuo obiettivo può crescere all’infinito. Ti manca un limite superiore da qualche parte. Le aziende reali hanno sempre limiti: aggiungili.

Il Risolutore non può migliorare la soluzione attuale

Accade di solito con problemi non lineari. Prova valori iniziali diversi o passa al metodo Evolutivo. A volte un ottimo locale è abbastanza.

Errori di formulazione del modello

Riferimenti circolari

Succede quando la Cella A dipende dalla Cella B, che dipende dalla Cella A. Excel ti avviserà. Usa la scheda Formule > Traccia precedenti per trovare l’anello e romperlo.

Vincoli interi

Aggiunta di vincoli interi per numeri interi. Immagine dell’autore.

Limitazioni del Risolutore e alternative per problemi su larga scala

Il Risolutore di Excel è ottimo, ma ha limiti di cui dovresti essere consapevole.

Capire le limitazioni del Risolutore

Il Risolutore standard si ferma a 200 variabili decisionali e 100 vincoli. Sembra tanto, finché non provi a pianificare 200 lavoratori su 50 fasce orarie. Sono 10.000 variabili, ben oltre ciò che il Risolutore può gestire.

Uso il Risolutore per piani di produzione mensili, budget trimestrali e selezione progetti. È perfetto per decisioni tattiche. Ma non è fatto per problemi di scala enterprise come reti di supply chain con migliaia di sedi.

Alternative per problemi grandi

OpenSolver

OpenSolver è gratuito e rimuove quei limiti. Gestisce tante variabili quanta memoria ha il tuo computer, usa la stessa interfaccia del Risolutore di Excel e puoi scaricarlo da OpenSolver.org. Lo consiglio quando superi i limiti del Risolutore standard.

Frontline Solver Premium

Frontline Solver è a pagamento (300-1.500 $/anno) ma supporta migliaia di variabili con algoritmi migliori. È quello che userei se lo facessi professionalmente a tempo pieno.

Software stand-alone

Strumenti come CPLEX o Gurobi gestiscono milioni di variabili ma richiedono conoscenze di programmazione e un investimento serio. È roba da enterprise.

Secondo me, per la maggior parte degli analisti aziendali, il Risolutore di Excel copre il 95% delle esigenze. Se ne superi i limiti, in realtà è un buon segno: stai facendo un lavoro così sofisticato da giustificare strumenti migliori.

Conclusione

Abbiamo coperto molto, dall’impostazione di base all’interpretazione dei prezzi ombra. 

Ora, invece di discutere opinioni in sala riunioni, puoi mettere i numeri sullo schermo. "Ecco il piano matematicamente ottimale." Cambia la conversazione. Smetti di tirare a indovinare e inizi a dimostrare.

Provalo sul tuo prossimo problema spinoso, quel conflitto di scheduling o quella stretta di budget che stai rimandando. Potresti scoprire che la risposta perfetta era nascosta nel tuo foglio di calcolo da sempre.

E se vuoi imparare altre tecniche per impressionare il capo, segui il nostro corso Data Analysis in Excel per continuare a crescere.