Ottimizzazione con Excel Solver

Solver è un programma aggiuntivo di Microsoft Excel che puoi utilizzare per l'ottimizzazione nell'analisi what-if.

Secondo O'Brien e Marakas, optimization analysisè un'estensione più complessa dell'analisi della ricerca degli obiettivi. Invece di impostare un valore obiettivo specifico per una variabile, l'obiettivo è trovare il valore ottimale per una o più variabili obiettivo, sotto determinati vincoli. Quindi, una o più altre variabili vengono modificate ripetutamente, in base ai vincoli specificati, finché non si scoprono i valori migliori per le variabili di destinazione.

In Excel puoi usare Solver per trovare un file optimal value (massimo o minimo, o un determinato valore) per una formula in una cella chiamata cella obiettivo, soggetta a determinati vincoli o limiti, sui valori di altre celle formula sul foglio di lavoro.

Ciò significa che il Risolutore funziona con un gruppo di celle chiamate variabili di decisione che vengono utilizzate per calcolare le formule nelle celle obiettivo e vincolo. Il Risolutore regola i valori nelle celle della variabile decisionale per soddisfare i limiti sulle celle dei vincoli e produrre il risultato desiderato per la cella obiettivo.

È possibile utilizzare il Risolutore per trovare soluzioni ottimali per diversi problemi come:

  • Determinazione del mix di prodotti mensile per un'unità di produzione di farmaci che massimizza la redditività.

  • Pianificazione della forza lavoro in un'organizzazione.

  • Risolvere problemi di trasporto.

  • Pianificazione finanziaria e budgeting.

Attivazione del componente aggiuntivo Risolutore

Prima di procedere con la ricerca di una soluzione per un problema con Risolutore, assicurarsi che il file Solver Add-in viene attivato in Excel come segue:

  • Fare clic sulla scheda DATI sulla barra multifunzione. IlSolver dovrebbe apparire nel gruppo Analisi come mostrato di seguito.

Nel caso in cui non trovi il comando Risolutore, attivalo come segue:

  • Fare clic sulla scheda FILE.
  • Fare clic su Opzioni nel riquadro di sinistra. Viene visualizzata la finestra di dialogo Opzioni di Excel.
  • Fare clic su Componenti aggiuntivi nel riquadro di sinistra.
  • Seleziona Componenti aggiuntivi di Excel nella casella Gestisci e fai clic su Vai.

Viene visualizzata la finestra di dialogo Componenti aggiuntivi. Dai un'occhiataSolver Add-ine fai clic su OK. Ora dovresti essere in grado di trovare il comando Risolutore sulla barra multifunzione nella scheda DATI.

Metodi di risoluzione utilizzati dal Risolutore

È possibile scegliere uno dei seguenti tre metodi di risoluzione supportati dal Risolutore Excel, in base al tipo di problema:

LP Simplex

Utilizzato per problemi lineari. UNSolver il modello è lineare nelle seguenti condizioni:

  • La cella di destinazione viene calcolata sommando i termini della forma (cella che cambia) * (costante).

  • Ciascun vincolo soddisfa il requisito del modello lineare. Ciò significa che ogni vincolo viene valutato sommando i termini della forma (cella che cambia) * (costante) e confrontando le somme con una costante.

Gradiente ridotto generalizzato (GRG) Non lineare

Utilizzato per problemi non lineari lisci. Se la cella di destinazione, uno qualsiasi dei vincoli o entrambi contengono riferimenti a celle che cambiano che non sono nella forma (cella che cambia) * (costante), hai un modello non lineare.

Evolutivo

Utilizzato per problemi non lineari lisci. Se la cella di destinazione, uno qualsiasi dei vincoli o entrambi contengono riferimenti a celle che cambiano che non sono nella forma (cella che cambia) * (costante), hai un modello non lineare.

Comprensione della valutazione del risolutore

Il Risolutore richiede i seguenti parametri:

  • Celle variabili di decisione
  • Celle di vincolo
  • Cellule oggettive
  • Metodo di risoluzione

La valutazione del risolutore si basa su quanto segue:

  • I valori nelle celle delle variabili decisionali sono limitati dai valori nelle celle dei vincoli.

  • Il calcolo del valore nella cella dell'obiettivo include i valori nelle celle della variabile decisionale.

  • Il Risolutore utilizza il metodo di risoluzione scelto per ottenere il valore ottimale nella cella dell'obiettivo.

Definizione di un problema

Supponiamo che tu stia analizzando i profitti realizzati da un'azienda che produce e vende un determinato prodotto. Ti viene chiesto di trovare l'importo che può essere speso in pubblicità nei prossimi due trimestri fino a un massimo di 20.000. Il livello di pubblicità in ogni trimestre influisce su:

  • Il numero di unità vendute, determinando indirettamente l'ammontare del fatturato.
  • Le spese associate, e
  • Il profitto.

Puoi procedere alla definizione del problema come:

  • Trova costo unitario.
  • Trova il costo pubblicitario per unità.
  • Trova prezzo unitario.

Quindi, impostare le celle per i calcoli richiesti come indicato di seguito.

Come puoi osservare, i calcoli vengono eseguiti per Trimestre1 e Trimestre2 che sono in considerazione sono:

  • Il numero di unità disponibili per la vendita nel trimestre 1 è 400 e nel trimestre 2 è 600 (celle - C7 e D7).

  • I valori iniziali per il budget pubblicitario sono impostati su 10000 per trimestre (Celle - C8 e D8).

  • Il numero di unità vendute dipende dal costo pubblicitario per unità e quindi è il budget per il trimestre / Adv. Costo per unità. Nota che abbiamo usato la funzione Min per fare attenzione a vedere che il no. di unità vendute in <= n. di unità disponibili. (Celle - C9 e D9).

  • Il ricavo viene calcolato come prezzo unitario * numero di unità vendute (celle - C10 e D10).

  • Le spese sono calcolate come Costo unitario * N. di unità disponibili + Adv. Costo per quel trimestre (celle - C11 e D12).

  • Il profitto è entrate - spese (celle C12 e D12).

  • Il profitto totale è il profitto nel trimestre1 + il profitto nel trimestre2 (cella - D3).

Successivamente, puoi impostare i parametri per il Risolutore come indicato di seguito:

Come puoi osservare, i parametri per il Risolutore sono:

  • La cella obiettivo è D3 che contiene il profitto totale, che si desidera massimizzare.

  • Le celle Variabile decisionale sono C8 e D8 che contengono i budget per i due trimestri: Trimestre1 e Trimestre2.

  • Sono presenti tre celle Vincolo: C14, C15 e C16.

    • La cella C14 che contiene il budget totale deve impostare il vincolo di 20000 (cella D14).

    • La cella C15 che contiene il n. di unità vendute nel Trimestre 1 è quello di impostare il vincolo di <= no. di unità disponibili nel quarto (cella D15).

    • La cella C16 che contiene il n. di unità vendute nel trimestre 2 è quello di impostare il vincolo di <= no. di unità disponibili nel quarto 2 (cella D16).

Risolvere il problema

Il passaggio successivo consiste nell'usare il Risolutore per trovare la soluzione come segue:

Step 1- Vai a DATI> Analisi> Risolutore sulla barra multifunzione. Viene visualizzata la finestra di dialogo Parametri del risolutore.

Step 2 - Nella casella Imposta obiettivo, seleziona la cella D3.

Step 3 - Seleziona Max.

Step 4 - Selezionare l'intervallo C8: D8 in By Changing Variable Cells scatola.

Step 5 - Successivamente, fai clic sul pulsante Aggiungi per aggiungere i tre vincoli che hai identificato.

Step 6- Viene visualizzata la finestra di dialogo Aggiungi vincolo. Imposta il vincolo per il budget totale come indicato di seguito e fai clic su Aggiungi.

Step 7- Impostare il vincolo per il totale n. di unità vendute nel primo trimestre come indicato di seguito e fare clic su Aggiungi.

Step 8- Impostare il vincolo per il totale n. di unità vendute nel secondo trimestre come indicato di seguito e fare clic su OK.

Viene visualizzata la finestra di dialogo Parametri del risolutore con i tre vincoli aggiunti nella casella - Soggetto ai vincoli.

Step 9 - Nel Select a Solving Method casella, selezionare Simplex LP.

Step 10- Fare clic sul pulsante Risolvi. Viene visualizzata la finestra di dialogo Risultati del risolutore. SelezionareKeep Solver Solution e fare clic su OK.

I risultati verranno visualizzati nel foglio di lavoro.

Come si può osservare, la soluzione ottimale che produce il massimo profitto totale, soggetto ai vincoli dati, risulta essere la seguente:

  • Profitto totale - 30000.
  • Adv. Budget per il trimestre 1 - 8000.
  • Adv. Budget per il secondo trimestre - 12000.

Passaggio attraverso le soluzioni di prova del risolutore

È possibile scorrere le soluzioni di prova del Risolutore, esaminando i risultati dell'iterazione.

Step 1 - Fare clic sul pulsante Opzioni nella finestra di dialogo Parametri del risolutore.

Il Options appare la finestra di dialogo.

Step 2 - Selezionare la casella Mostra risultati iterazione e fare clic su OK.

Step 3 - Il Solver Parametersappare la finestra di dialogo. ClicSolve.

Step 4 - Il Show Trial Solution appare la finestra di dialogo, che mostra il messaggio - Solver paused, current solution values displayed on worksheet.

Come puoi osservare, i valori di iterazione correnti vengono visualizzati nelle celle di lavoro. È possibile impedire al Risolutore di accettare i risultati correnti o continuare con il Risolutore per cercare la soluzione in passaggi successivi.

Step 5 - Fare clic su Continua.

Il Show Trial Solutionad ogni passaggio viene visualizzata la finestra di dialogo e infine, dopo aver trovato la soluzione ottimale, viene visualizzata la finestra di dialogo Risultati del risolutore. Il tuo foglio di lavoro viene aggiornato ad ogni passaggio, mostrando finalmente i valori dei risultati.

Salvataggio delle selezioni del risolutore

Sono disponibili le seguenti opzioni di salvataggio per i problemi risolti con Risolutore:

  • È possibile salvare le ultime selezioni nella finestra di dialogo Parametri del risolutore con un foglio di lavoro salvando la cartella di lavoro.

  • Ogni foglio di lavoro in una cartella di lavoro può avere le proprie selezioni Risolutore e tutte verranno salvate quando si salva la cartella di lavoro.

  • È inoltre possibile definire più di un problema in un foglio di lavoro, ciascuno con le proprie selezioni di Risolutore. In tal caso, è possibile caricare e salvare i problemi individualmente con Carica / Salva nella finestra di dialogo Parametri del risolutore.

    • Clicca il Load/Savepulsante. Viene visualizzata la finestra di dialogo Carica / Salva.

    • Per salvare un modello problema, immettere il riferimento per la prima cella di un intervallo verticale di celle vuote in cui si desidera posizionare il modello problema. Fare clic su Salva.

    • Il modello del problema (il set di parametri del risolutore) viene visualizzato a partire dalla cella che hai fornito come riferimento.

    • Per caricare un modello problema, immettere il riferimento per l'intero intervallo di celle che contiene il modello problema. Quindi, fare clic sul pulsante Carica.