Contenuti
Formulazione del problema
Supponiamo che l'azienda in cui lavori abbia tre magazzini, da dove le merci vanno a cinque dei tuoi negozi sparsi per Mosca.
Ogni negozio è in grado di vendere una certa quantità di merce a noi nota. Ciascuno dei magazzini ha una capacità limitata. Il compito è scegliere razionalmente da quale magazzino a quale magazzino consegnare la merce in modo da ridurre al minimo i costi totali di trasporto.
Prima di iniziare l'ottimizzazione, sarà necessario compilare una semplice tabella su un foglio Excel – il nostro modello matematico che descrive la situazione:
Resta inteso che:
- La tabella giallo chiaro (C4:G6) descrive il costo di spedizione di un articolo da ciascun magazzino a ciascun negozio.
- Le celle viola (C15:G14) descrivono la quantità di beni necessaria per la vendita di ciascun negozio.
- I riquadri rossi (J10:J13) mostrano la capacità di ciascun magazzino, ovvero la quantità massima di merci che il magazzino può contenere.
- Le celle gialle (C13:G13) e blu (H10:H13) sono rispettivamente le somme di righe e colonne per le celle verdi.
- Il costo di spedizione totale (J18) è calcolato come somma dei prodotti del numero di merci e dei relativi costi di spedizione – per il calcolo, qui viene utilizzata la funzione MATR.SOMMA.PRODOTTO (SOMMAPRODOTTO).
Pertanto, il nostro compito si riduce alla selezione dei valori ottimali delle cellule verdi. E in modo che l'importo totale della riga (celle blu) non ecceda la capacità del magazzino (celle rosse) e allo stesso tempo ogni negozio riceva la quantità di merce che deve vendere (l'importo per ogni negozio nel le celle gialle dovrebbero essere il più vicino possibile ai requisiti – celle viola).
Soluzione
In matematica, tali problemi di scelta della distribuzione ottimale delle risorse sono stati formulati e descritti per lungo tempo. E, naturalmente, i modi per risolverli sono stati a lungo sviluppati non mediante un'enumerazione smussata (che è molto lunga), ma in un numero molto ridotto di iterazioni. Excel fornisce all'utente tale funzionalità utilizzando un componente aggiuntivo. Soluzioni di ricerca (Risolutore) dalla scheda Dati (Data):
Se nella scheda Dati il tuo Excel non ha un tale comando - va bene - significa che il componente aggiuntivo semplicemente non è ancora connesso. Per attivarlo apri Compila il , Quindi seleziona parametri - Add-ons
- Chi siamo (Opzioni — Componenti aggiuntivi — Vai a). Nella finestra che si apre, seleziona la casella accanto alla riga di cui abbiamo bisogno Soluzioni di ricerca (Risolutore).
Eseguiamo il componente aggiuntivo:
In questa finestra è necessario impostare i seguenti parametri:
- Ottimizza la funzione di destinazione (Imposta tsoldi cellula) – qui è necessario indicare l'obiettivo principale finale della nostra ottimizzazione, ovvero la scatola rosa con il costo totale di spedizione (J18). La cella obiettivo può essere ridotta al minimo (se si tratta di spese, come nel nostro caso), massimizzata (se si tratta, ad esempio, di profitto) o provare a portarla a un determinato valore (ad esempio, rientrare esattamente nel budget allocato).
- Modifica delle celle variabili (By cambiando cellule) – qui indichiamo le celle verdi (C10: G12), variando i valori di cui vogliamo ottenere il nostro risultato – il costo minimo di consegna.
- Coerente con le restrizioni (Oggetto a , il vincoli) – un elenco di restrizioni che devono essere prese in considerazione durante l'ottimizzazione. Per aggiungere restrizioni all'elenco, fare clic sul pulsante Aggiungi (Aggiungere) e inserisci la condizione nella finestra che appare. Nel nostro caso, questo sarà il vincolo della domanda:
e limite al volume massimo dei magazzini:
Oltre alle ovvie limitazioni legate a fattori fisici (capacità di magazzini e mezzi di trasporto, vincoli di budget e di tempo, ecc.), a volte è necessario aggiungere restrizioni “speciali per Excel”. Quindi, ad esempio, Excel può facilmente farti "ottimizzare" il costo di consegna offrendoti il trasporto delle merci dai negozi al magazzino: i costi diventeranno negativi, ovvero realizzeremo un profitto! 🙂
Per evitare che ciò accada, è meglio lasciare la casella di controllo abilitata. Rendi variabili illimitate non negative o anche a volte registrare esplicitamente tali momenti nell'elenco delle restrizioni.
Dopo aver impostato tutti i parametri necessari, la finestra dovrebbe apparire così:
Nell'elenco a discesa Seleziona un metodo di risoluzione, è inoltre necessario selezionare il metodo matematico appropriato per risolvere una scelta di tre opzioni:
- Metodo Simplex è un metodo semplice e veloce per risolvere problemi lineari, cioè problemi in cui l'output dipende linearmente dall'input.
- Metodo generale del gradiente declassato (OGG) – per problemi non lineari, dove esistono complesse dipendenze non lineari tra dati di input e output (ad esempio, la dipendenza delle vendite dai costi pubblicitari).
- Ricerca evolutiva di una soluzione – un metodo di ottimizzazione relativamente nuovo basato sui principi dell'evoluzione biologica (ciao Darwin). Questo metodo funziona molte volte più a lungo dei primi due, ma può risolvere quasi tutti i problemi (non lineare, discreto).
Il nostro compito è chiaramente lineare: consegnato 1 pezzo – speso 40 rubli, consegnato 2 pezzi – speso 80 rubli. ecc., quindi il metodo simplex è la scelta migliore.
Ora che sono stati inseriti i dati per il calcolo, premere il pulsante Trovare una soluzione (Risolvere)per iniziare l'ottimizzazione. Nei casi più gravi con molte celle e vincoli che cambiano, trovare una soluzione può richiedere molto tempo (soprattutto con il metodo evolutivo), ma il nostro compito per Excel non sarà un problema: in un paio di momenti otterremo i seguenti risultati :
Prestare attenzione all'interessante distribuzione dei volumi di fornitura tra i negozi, pur non eccedendo la capacità dei nostri magazzini e soddisfacendo tutte le richieste per il numero di merci richiesto per ogni negozio.
Se la soluzione trovata è adatta a noi, possiamo salvarla o ripristinare i valori originali e riprovare con altri parametri. È anche possibile salvare la combinazione di parametri selezionata come Scenario. Su richiesta dell'utente, Excel può creare tre tipi Report sul problema da risolvere su fogli separati: una relazione sui risultati, una relazione sulla stabilità matematica della soluzione e una relazione sui limiti (restrizioni) della soluzione, tuttavia, nella maggior parte dei casi, interessano solo agli specialisti .
Ci sono, tuttavia, situazioni in cui Excel non riesce a trovare una soluzione adatta. È possibile simulare un caso del genere se indichiamo nel nostro esempio il fabbisogno dei magazzini in misura maggiore della capacità totale dei magazzini. Quindi, durante l'esecuzione di un'ottimizzazione, Excel cercherà di avvicinarsi il più possibile alla soluzione e quindi visualizzerà un messaggio che indica che non è possibile trovare la soluzione. Tuttavia, anche in questo caso, abbiamo molte informazioni utili – in particolare, possiamo vedere gli “anelli deboli” dei nostri processi aziendali e comprendere le aree di miglioramento.
L'esempio considerato, ovviamente, è relativamente semplice, ma è facilmente scalabile per risolvere problemi molto più complessi. Per esempio:
- Ottimizzazione della distribuzione delle risorse finanziarie per voce di spesa nel business plan o nel budget del progetto. Le restrizioni, in questo caso, saranno l'importo del finanziamento e la tempistica del progetto, e l'obiettivo dell'ottimizzazione è massimizzare i profitti e ridurre al minimo i costi del progetto.
- Ottimizzazione della pianificazione dei dipendenti al fine di ridurre al minimo il fondo salariale dell'impresa. Le restrizioni, in questo caso, saranno i desideri di ciascun dipendente in base al programma di lavoro e ai requisiti del tavolo del personale.
- Ottimizzazione degli investimenti di investimento – la necessità di distribuire correttamente i fondi tra più banche, titoli o azioni di imprese al fine, ancora, di massimizzare i profitti o (se più importante) minimizzare i rischi.
In ogni caso, add-on Soluzioni di ricerca (Risolutore) è uno strumento Excel molto potente e bello e degno della tua attenzione, in quanto può aiutarti in molte situazioni difficili che devi affrontare negli affari moderni.