Formulazione di u prublema
Suppone chì a cumpagnia induve u travagliu hà trè magazzini, da induve a merchenzie vai à cinque di i vostri magazzini spargugliati in Mosca.
Ogni tenda hè capaci di vende una certa quantità di merchenzie cunnisciute da noi. Ogni magazzini hà una capacità limitata. U compitu hè di sceglie raziunale da quale magazzinu à quale magazzini consegna a merchenzie per minimizzà i costi totali di trasportu.
Prima di inizià l'ottimisazione, serà necessariu di cumpilà una tavola simplice nantu à una foglia Excel - u nostru mudellu matematicu chì descrive a situazione:
Hè capitu chì:
- U tavulu giallu chjaru (C4: G6) descrive u costu di spedizione un articulu da ogni magazzinu à ogni tenda.
- Cellule viola (C15: G14) descrizanu a quantità di merchenzie necessaria per ogni tenda per vende.
- E cellule rosse (J10: J13) mostranu a capacità di ogni magazzinu - a quantità massima di merchenzie chì u magazzinu pò cuntene.
- I celi gialli (C13: G13) è blu (H10: H13) sò i sume di fila è colonna per e cellule verdi, rispettivamente.
- U costu di trasportu tutale (J18) hè calculatu cum'è a summa di i prudutti di u numeru di merchenzie è i so spesi di spedizione currispondenti - per u calculu, a funzione hè aduprata quì. SUMPRODUCT (SUMPRODUCT).
Cusì, u nostru compitu hè ridutta à a selezzione di i valori ottimali di e cellule verdi. È cusì chì a quantità tutale per a linea (cellule blu) ùn supera a capacità di u magazzinu (cellule rosse), è à u stessu tempu ogni tenda riceve a quantità di merchenzie chì deve vende (a quantità per ogni tenda in u i celluli gialli duveranu esse u più vicinu pussibule à i bisogni - cellule viole).
Vergogna à tè
In matematica, tali prublemi di sceglie a distribuzione ottima di risorse sò stati formulati è descritti per un bellu pezzu. E, sicuru, e manere di risolviri sò longu sviluppati micca da una enumerazione smussata (chì hè assai longa), ma in un pocu numeru di iterazioni. Excel furnisce l'utilizatori cun tali funziunalità utilizendu un add-in. Soluzioni di ricerca (solutore) da a tabulazione Dati (Data):
Sì nantu à a tabulazione Dati u vostru Excel ùn hà micca un tali cumandamentu - hè bè - significa chì l'add-in hè simplicemente micca cunnessu ancu. Per attivà si apre File, dopu selezziunate login - Add-ons - prupòsitu di lu (Opzioni - Add-Ins - Vai à). In a finestra chì apre, verificate a casella accantu à a linea chì avemu bisognu Soluzioni di ricerca (solutore).
Eseguimu l'add-on:
In questa finestra, avete bisognu di stabilisce i seguenti parametri:
- Optimizà a funzione di destinazione (Set tsoldi cellula) - quì hè necessariu indicà u scopu principale finali di a nostra ottimisazione, vale à dì scatula rosa cù u costu tutale di spedizione (J18). A cellula di destinazione pò esse minimizata (s'ellu hè spese, cum'è in u nostru casu), maximizatu (s'ellu hè, per esempiu, prufittu) o pruvate à purtà à un valore determinatu (per esempiu, fitte esattamente in u budgetu attribuitu).
- Cambiamentu di e Celle Variabili (By cambià cellule) - quì indichemu e cellule verdi (C10: G12), variendu i valori di quale vulemu ottene u nostru risultatu - u costu minimu di consegna.
- In cunfurmità cù e restrizioni (sughjettu à l limitazioni) - una lista di restrizioni chì deve esse cunsideratu quandu uttimisimu. Per aghjunghje restrizioni à a lista, cliccate nantu à u buttone Ajouter (aghjunghje) è entre a cundizione in a finestra chì appare. In u nostru casu, questu serà a limitazione di a dumanda:
è limite à u voluminu massimu di magazzini:
In più di e limitazioni evidenti assuciati cù fatturi fisichi (capacità di magazzini è mezzi di trasportu, budget è limitazione di u tempu, etc.), qualchì volta hè necessariu aghjunghje restrizioni "special for Excel". Cusì, per esempiu, Excel pò facilmente organizà per voi per "ottimisà" u costu di spedizione offrendu à trasportà e merchenzie da i magazzini à u magazzinu - i costi diventeranu negativu, vale à dì, faremu un prufittu! 🙂
Per impediscenu chì questu succede, hè megliu lascià a casella di spunta attivata. Fate Variabili Illimitati Non Negativi o ancu qualchì volta registrà esplicitamente tali mumenti in a lista di restrizioni.
Dopu avè stabilitu tutti i paràmetri necessarii, a finestra deve esse cusì:
In a lista di scelta di u metudu di risolve, avete bisognu di selezziunà u metudu matematicu adattatu per risolve una scelta di trè opzioni:
- Metudu Simplex hè un metudu simplice è veloce per risolve i prublemi lineari, vale à dì prublemi induve l'output hè linearmente dipendente da l'input.
- Metudu generale di gradiente degradatu (OGG) - per i prublemi non-linearii, induve ci sò cumplessi dependenzii non-lineari trà e dati di input è output (per esempiu, a dependenza di vendita à i costi di publicità).
- Ricerca evolutiva per una suluzione - un metudu di ottimisazione relativamente novu basatu annantu à i principii di l'evoluzione biologica (ciao Darwin). Stu metudu travaglia parechje volte più di i primi dui, ma pò risolve quasi ogni prublema (non lineari, discretu).
U nostru compitu hè chjaramente lineare: spentu 1 pezza - spentu 40 rubli, spediti 2 pezzi - spent 80 rubles. etc., cusì u metudu simplex hè a megliu scelta.
Avà chì i dati per u calculu sò inseriti, appughjà u buttone Truvà una suluzione (risolve)per inizià l'optimizazione. In i casi severi cù assai celluli cambianti è limitazioni, truvà una suluzione pò piglià assai tempu (in particulare cù u metudu evolutivu), ma u nostru compitu per Excel ùn serà micca un prublema - in un paru di mumenti avemu da ottene i seguenti risultati. :
Prestate attenzione à quantu interessante i volumi di furnizzioni sò stati distribuiti trà e magazzini, senza sopra à a capacità di i nostri magazzini è satisfacendu tutte e dumande per u numeru necessariu di merchenzie per ogni tenda.
Se a suluzione truvata ci cunvene, allora pudemu salvà, o rinvià à i valori originali è pruvate di novu cù altri parametri. Tù dinù ponu salvà a cumminazzioni sceltu di paràmetri cum'è Scenario. À a dumanda di l'utilizatore, Excel pò custruisce trè tippi Rivista nantu à u prublema chì si risolve nantu à fogli separati: un rapportu nantu à i risultati, un rapportu nantu à a stabilità matematica di a suluzione è un rapportu nantu à i limiti (restrizioni) di a suluzione, ma in a maiò parte di i casi, sò d'interessu solu per i specialisti. .
Tuttavia, ci sò situazioni induve Excel ùn pò truvà una suluzione adatta. Hè pussibule simule un tali casu se indichemu in u nostru esempiu i bisogni di i magazzini in quantità più grande di a capacità tutale di i magazzini. Allora, quandu eseguisce una ottimisazione, Excel pruvarà à avvicinassi u più vicinu à a suluzione pussibule, è dopu mostra un missaghju chì a suluzione ùn pò micca esse truvata. In ogni casu, ancu in questu casu, avemu assai infurmazioni utili - in particulare, pudemu vede i "ligami dèbuli" di i nostri prucessi di cummerciale è capiscenu i spazii per migliurà.
L'esempiu cunsideratu, sicuru, hè relativamente simplice, ma facilmente scala per risolve prublemi assai più cumplessi. Per esempiu:
- Ottimisazione di a distribuzione di risorse finanziarii per voce di spesa in u pianu di cummerciale o u budgetu di u prugettu. E restrizioni, in questu casu, seranu a quantità di finanziamentu è u timing di u prugettu, è u scopu di l'ottimisazione hè di maximizà i prufitti è minimizzà i costi di u prugettu.
- Ottimisazione di a pianificazione di l'impiegati per minimizzà u fondu di u salariu di l'impresa. E restrizioni, in questu casu, seranu i desideri di ogni impiigatu secondu u calendariu di u travagliu è i requisiti di a tavola di staffing.
- Ottimisazione di l'investimenti d'investimentu - a necessità di distribuisce bè i fondi trà parechji banche, securities o azzioni di l'imprese per, di novu, per maximizà i prufitti o (se più impurtante) minimizzà i risichi.
In ogni casu, add-on Soluzioni di ricerca (Risolutore) hè un strumentu Excel assai putente è bellu è degne di a vostra attenzione, postu chì pò aiutà in parechje situazioni difficili chì avete da affruntà in l'affari muderni.