Cuprins
Formularea problemei
Să presupunem că firma în care lucrezi are trei depozite, de unde mărfurile merg la cinci dintre magazinele tale împrăștiate în toată Moscova.
Fiecare magazin este capabil să vândă o anumită cantitate de mărfuri cunoscute nouă. Fiecare dintre depozite are o capacitate limitată. Sarcina este de a alege rațional din ce depozit la ce magazine să livreze mărfurile pentru a minimiza costurile totale de transport.
Înainte de a începe optimizarea, va fi necesar să compilați un tabel simplu pe o foaie Excel - modelul nostru matematic care descrie situația:
Se intelege ca:
- Tabelul galben deschis (C4:G6) descrie costul expedierii unui articol din fiecare depozit la fiecare magazin.
- Celulele violet (C15:G14) descriu cantitatea de mărfuri necesară pentru vânzarea fiecărui magazin.
- Celulele roșii (J10:J13) afișează capacitatea fiecărui depozit – cantitatea maximă de mărfuri pe care o poate deține depozitul.
- Celulele galbene (C13:G13) și albastre (H10:H13) sunt sumele de rând și, respectiv, de coloană pentru celulele verzi.
- Costul total de transport (J18) este calculat ca suma produselor din numărul de mărfuri și costurile de transport corespunzătoare acestora - pentru calcul, funcția este folosită aici SUMPRODUCT (SUMAPRODUS).
Astfel, sarcina noastră se reduce la selectarea valorilor optime ale celulelor verzi. Și astfel încât suma totală pentru linie (celule albastre) să nu depășească capacitatea depozitului (celule roșii) și, în același timp, fiecare magazin să primească cantitatea de mărfuri pe care trebuie să o vândă (suma pentru fiecare magazin din celulele galbene ar trebui să fie cât mai aproape posibil de cerințe – celule violete).
Soluţie
În matematică, astfel de probleme de alegere a repartiției optime a resurselor au fost formulate și descrise de mult timp. Și, bineînțeles, modalitățile de rezolvare a acestora au fost dezvoltate de mult timp nu prin enumerare directă (care este foarte lungă), ci într-un număr foarte mic de iterații. Excel oferă utilizatorului o astfel de funcționalitate folosind un add-in. Soluții de căutare (Rezolvator) din filă Date (Data):
Dacă pe filă Date Excel-ul tău nu are o astfel de comandă – este în regulă – înseamnă că programul de completare pur și simplu nu este încă conectat. Pentru a-l activa deschide Fișier, Apoi selectați parametrii - Firefox - Despre Noi (Opțiuni — Suplimente — Accesați). În fereastra care se deschide, bifați caseta de lângă linia de care avem nevoie Soluții de căutare (Rezolvator).
Să rulăm suplimentul:
În această fereastră, trebuie să setați următorii parametri:
- Optimizați funcția țintă (Setați tbani celula) – aici este necesar să se indice scopul principal final al optimizării noastre, adică cutia roz cu costul total de transport (J18). Celula țintă poate fi minimizată (dacă este vorba de cheltuieli, ca în cazul nostru), maximizată (dacă este, de exemplu, profit) sau încercați să o aduceți la o anumită valoare (de exemplu, să se încadreze exact în bugetul alocat).
- Schimbarea celulelor variabile (By schimbarea celule) – aici indicăm celulele verzi (C10: G12), variind valorile cărora dorim să obținem rezultatul – costul minim de livrare.
- În concordanță cu restricțiile (Subiect la il Constrângeri) – o listă de restricții care trebuie luate în considerare la optimizare. Pentru a adăuga restricții la listă, faceți clic pe butonul Adăuga (Adăuga) și introduceți condiția în fereastra care apare. În cazul nostru, aceasta va fi constrângerea cererii:
și limitarea volumului maxim de depozite:
Pe lângă limitările evidente asociate factorilor fizici (capacitatea depozitelor și a mijloacelor de transport, constrângeri de buget și de timp etc.), uneori este necesar să adăugați restricții „speciale pentru Excel”. Deci, de exemplu, Excel vă poate aranja cu ușurință să „optimizați” costul livrării, oferindu-vă să transportați mărfurile din magazine înapoi la depozit – costurile vor deveni negative, adică vom obține profit! 🙂
Pentru a preveni acest lucru, cel mai bine este să lăsați caseta de selectare activată. Faceți ca variabilele nelimitate să nu fie negative sau chiar uneori înregistrează în mod explicit astfel de momente în lista de restricții.
După setarea tuturor parametrilor necesari, fereastra ar trebui să arate astfel:
În lista derulantă Selectați o metodă de rezolvare, mai trebuie să selectați metoda matematică adecvată pentru rezolvarea a trei opțiuni:
- Metoda simplex este o metodă simplă și rapidă de rezolvare a problemelor liniare, adică probleme în care rezultatul este dependent liniar de intrare.
- Metoda generală degradată a gradului (OGG) – pentru probleme neliniare, unde există dependențe neliniare complexe între datele de intrare și de ieșire (de exemplu, dependența vânzărilor de costurile de publicitate).
- Căutarea evolutivă a unei soluții – o metodă de optimizare relativ nouă bazată pe principiile evoluției biologice (bună ziua Darwin). Această metodă funcționează de multe ori mai mult decât primele două, dar poate rezolva aproape orice problemă (neliniară, discretă).
Sarcina noastră este clar liniară: livrat 1 bucată – cheltuit 40 de ruble, livrat 2 bucăți – cheltuit 80 de ruble. etc., deci metoda simplex este cea mai bună alegere.
Acum că datele pentru calcul sunt introduse, apăsați butonul Gaseste o solutie (Rezolva)pentru a începe optimizarea. În cazurile severe, cu multe celule în schimbare și constrângeri, găsirea unei soluții poate dura mult timp (în special cu metoda evolutivă), dar sarcina noastră pentru Excel nu va fi o problemă - în câteva momente vom obține următoarele rezultate :
Atenție la cât de interesant au fost distribuite volumele de aprovizionare între magazine, fără a depăși capacitatea depozitelor noastre și a satisface toate solicitările pentru numărul necesar de mărfuri pentru fiecare magazin.
Dacă soluția găsită ni se potrivește, atunci o putem salva sau reveni la valorile originale și încerca din nou cu alți parametri. De asemenea, puteți salva combinația selectată de parametri ca Scenariu. La cererea utilizatorului, Excel poate construi trei tipuri Rapoarte asupra problemei care se rezolvă pe foi separate: un raport asupra rezultatelor, un raport privind stabilitatea matematică a soluției și un raport privind limitele (restricțiile) soluției, totuși, în cele mai multe cazuri, sunt de interes doar pentru specialiști .
Există, totuși, situații în care Excel nu poate găsi o soluție adecvată. Este posibil să simulăm un astfel de caz dacă indicăm în exemplul nostru cerințele magazinelor în cantitate mai mare decât capacitatea totală a depozitelor. Apoi, atunci când efectuează o optimizare, Excel va încerca să se apropie cât mai mult de soluție, apoi va afișa un mesaj că soluția nu poate fi găsită. Cu toate acestea, chiar și în acest caz, avem o mulțime de informații utile – în special, putem vedea „legăturile slabe” ale proceselor noastre de afaceri și înțelegem zonele de îmbunătățire.
Exemplul luat în considerare, desigur, este relativ simplu, dar ușor de scalat pentru a rezolva probleme mult mai complexe. De exemplu:
- Optimizarea distribuirii resurselor financiare pe post de cheltuieli din planul de afaceri sau bugetul proiectului. Restricțiile, în acest caz, vor fi valoarea finanțării și calendarul proiectului, iar scopul optimizării este de a maximiza profiturile și de a minimiza costurile proiectului.
- Optimizarea programării angajaților pentru a minimiza fondul de salarii al întreprinderii. Restricțiile, în acest caz, vor fi dorințele fiecărui angajat în funcție de programul de angajare și de cerințele din tabloul de personal.
- Optimizarea investițiilor investiționale – necesitatea distribuirii corecte a fondurilor între mai multe bănci, valori mobiliare sau acțiuni ale întreprinderilor pentru, din nou, a maximiza profiturile sau (dacă este mai important) a minimiza riscurile.
În orice caz, supliment Soluții de căutare (Rezolvator) este un instrument Excel foarte puternic și frumos și merită atenția dumneavoastră, deoarece vă poate ajuta în multe situații dificile cu care trebuie să vă confruntați în afacerile moderne.