Problēmas formulēšana
Pieņemsim, ka uzņēmumam, kurā strādājat, ir trīs noliktavas, no kurām preces nonāk piecos jūsu veikalos, kas izkaisīti visā Maskavā.
Katrs veikals spēj realizēt noteiktu mums zināmu preču daudzumu. Katrai no noliktavām ir ierobežota ietilpība. Uzdevums ir racionāli izvēlēties, no kuras noliktavas uz kādiem veikaliem piegādāt preces, lai maksimāli samazinātu kopējās transportēšanas izmaksas.
Pirms optimizācijas uzsākšanas uz Excel lapas būs jāsastāda vienkārša tabula – mūsu matemātiskais modelis, kas apraksta situāciju:
Ir saprotams, ka:
- Gaiši dzeltenā tabula (C4:G6) apraksta vienas preces nosūtīšanas izmaksas no katras noliktavas uz katru veikalu.
- Purpursarkanās šūnas (C15:G14) apraksta preču daudzumu, kas nepieciešams katram veikalam, ko pārdot.
- Sarkanās šūnas (J10:J13) parāda katras noliktavas ietilpību – maksimālo preču daudzumu, ko noliktavā var ievietot.
- Dzeltenās (C13:G13) un zilās (H10:H13) šūnas ir attiecīgi rindu un kolonnu summas zaļajām šūnām.
- Kopējās piegādes izmaksas (J18) tiek aprēķinātas kā preču skaita produktu un tām atbilstošo piegādes izmaksu summa – aprēķinam tiek izmantota funkcija šeit SUMPRODUCT (SUMPRODUCT).
Tādējādi mūsu uzdevums ir samazināts līdz zaļo šūnu optimālo vērtību izvēlei. Un tā, lai līnijas kopsumma (zilās šūnas) nepārsniegtu noliktavas ietilpību (sarkanās šūnas), un tajā pašā laikā katrs veikals saņemtu nepieciešamo preču daudzumu (summu par katru veikalu dzeltenajām šūnām jābūt pēc iespējas tuvākām prasībām – purpursarkanām šūnām).
Šķīdums
Matemātikā šādas optimālā resursu sadalījuma izvēles problēmas ir formulētas un aprakstītas jau sen. Un, protams, veidi, kā tos atrisināt, jau sen ir izstrādāti nevis ar strupu uzskaitījumu (kas ir ļoti garš), bet gan ļoti mazā iterāciju skaitā. Excel nodrošina lietotājam šādu funkcionalitāti, izmantojot pievienojumprogrammu. Meklēšanas risinājumi (risinātājs) no cilnes Datums (Datums):
Ja cilnē Datums jūsu Excel nav šādas komandas — tas ir labi — tas nozīmē, ka pievienojumprogramma vienkārši vēl nav pievienota. Lai to aktivizētu, atveriet fileja, Pēc tam izvēlieties parametri - Add-ons - Par mums (Opcijas — pievienojumprogrammas — pāriet uz). Atvērtajā logā atzīmējiet izvēles rūtiņu blakus vajadzīgajai rindai Meklēšanas risinājumi (risinātājs).
Palaidīsim papildinājumu:
Šajā logā jāiestata šādi parametri:
- Optimizējiet mērķa funkciju (Iestatiet tnauda šūna) – šeit jānorāda mūsu optimizācijas gala galvenais mērķis, proti, rozā kaste ar kopējām piegādes izmaksām (J18). Mērķa šūnu var minimizēt (ja tie ir izdevumi, kā mūsu gadījumā), maksimizēt (ja tā ir, piemēram, peļņa) vai mēģināt to novest līdz noteiktai vērtībai (piemēram, precīzi iekļauties piešķirtajā budžetā).
- Mainīgo šūnu maiņa (By mainot šūnas) – šeit mēs norādām zaļās šūnas (C10: G12), kuru vērtības variējot vēlamies sasniegt savu rezultātu – minimālās piegādes izmaksas.
- Atbilstoši ierobežojumiem (Temats uz o Ierobežojumi) – ierobežojumu saraksts, kas jāņem vērā optimizējot. Lai sarakstam pievienotu ierobežojumus, noklikšķiniet uz pogas Pievienot (Pievienot) un parādītajā logā ievadiet nosacījumu. Mūsu gadījumā tas būs pieprasījuma ierobežojums:
un maksimālā noliktavu apjoma ierobežojums:
Papildus acīmredzamajiem ierobežojumiem, kas saistīti ar fiziskajiem faktoriem (noliktavu un transporta līdzekļu ietilpība, budžeta un laika ierobežojumi utt.), dažkārt ir nepieciešams pievienot ierobežojumus, kas ir “īpaši programmai Excel”. Tā, piemēram, Excel var viegli noorganizēt piegādes izmaksu “optimizāciju”, piedāvājot preces no veikaliem transportēt atpakaļ uz noliktavu – izmaksas kļūs negatīvas, proti, iegūsim peļņu! 🙂
Lai tas nenotiktu, vislabāk ir atstāt izvēles rūtiņu iespējotu. Neierobežotus mainīgos padariet par nenegatīviem vai pat dažkārt nepārprotami reģistrēt šādus mirkļus ierobežojumu sarakstā.
Pēc visu nepieciešamo parametru iestatīšanas logam vajadzētu izskatīties šādi:
Nolaižamajā sarakstā Atlasīt risināšanas metodi papildus ir jāatlasa atbilstošā matemātiskā metode, lai atrisinātu trīs iespējas:
- Vienkāršā metode ir vienkārša un ātra metode lineāru problēmu risināšanai, ti, problēmas, kuru izvade ir lineāri atkarīga no ievades.
- Vispārējā pazeminātā gradienta metode (OGG) – nelineārām problēmām, kur pastāv sarežģītas nelineāras atkarības starp ievades un izvades datiem (piemēram, pārdošanas apjoma atkarība no reklāmas izmaksām).
- Evolūcijas risinājuma meklējumi – salīdzinoši jauna optimizācijas metode, kas balstīta uz bioloģiskās evolūcijas principiem (labdien, Darvin). Šī metode darbojas daudzkārt ilgāk nekā pirmās divas, taču var atrisināt gandrīz jebkuru problēmu (nelineāru, diskrētu).
Mūsu uzdevums ir nepārprotami lineārs: piegādāts 1 gab – iztērēti 40 rubļi, piegādāti 2 gabali – iztērēti 80 rubļi. utt., tāpēc vienkāršā metode ir labākā izvēle.
Kad aprēķina dati ir ievadīti, nospiediet pogu Rast risinājumu (atrisināt)lai sāktu optimizāciju. Smagos gadījumos ar daudzām mainīgām šūnām un ierobežojumiem risinājuma atrašana var aizņemt ilgu laiku (īpaši ar evolucionāro metodi), taču mūsu uzdevums programmā Excel nebūs problēma – pēc pāris mirkļiem iegūsim šādus rezultātus :
Pievērsiet uzmanību tam, cik interesanti piegādes apjomi tika sadalīti pa veikaliem, vienlaikus nepārsniedzot mūsu noliktavu ietilpību un apmierinot visus pieprasījumus par nepieciešamo preču skaitu katram veikalam.
Ja atrastais risinājums mums ir piemērots, varam to saglabāt, vai arī atgriezties pie sākotnējām vērtībām un mēģināt vēlreiz ar citiem parametriem. Varat arī saglabāt atlasīto parametru kombināciju kā Scenārijs. Pēc lietotāja pieprasījuma Excel var izveidot trīs veidus Ziņojumi par risināmo problēmu uz atsevišķām lapām: ziņojumu par rezultātiem, ziņojumu par risinājuma matemātisko stabilitāti un ziņojumu par risinājuma robežām (ierobežojumiem), tomēr vairumā gadījumu tie interesē tikai speciālistus. .
Tomēr ir situācijas, kad programma Excel nevar atrast piemērotu risinājumu. Šādu gadījumu iespējams simulēt, ja savā piemērā norādām veikalu prasības apjomā, kas ir lielāks par noliktavu kopējo ietilpību. Pēc tam, veicot optimizāciju, Excel mēģinās pietuvoties risinājumam pēc iespējas tuvāk un pēc tam parādīs ziņojumu, ka risinājums nav atrodams. Tomēr arī šajā gadījumā mums ir daudz noderīgas informācijas – jo īpaši mēs varam saskatīt mūsu biznesa procesu “vājos posmus” un izprast jomas, kuras ir jāuzlabo.
Aplūkotais piemērs, protams, ir salīdzinoši vienkāršs, taču viegli pielāgojams daudz sarežģītāku problēmu risināšanai. Piemēram:
- Finanšu resursu sadales optimizācija pa izdevumu pozīcijām projekta biznesa plānā vai budžetā. Ierobežojumi šajā gadījumā būs finansējuma apjoms un projekta īstenošanas laiks, un optimizācijas mērķis ir maksimāli palielināt peļņu un samazināt projekta izmaksas.
- Darbinieku plānošanas optimizācija lai līdz minimumam samazinātu uzņēmuma algu fondu. Ierobežojumi šajā gadījumā būs katra darbinieka vēlmes atbilstoši nodarbinātības grafikam un štatu tabulas prasībām.
- Investīciju investīciju optimizācija – nepieciešamība pareizi sadalīt līdzekļus starp vairākām bankām, vērtspapīrus vai uzņēmumu akcijas, lai atkal palielinātu peļņu vai (ja svarīgāk) samazinātu riskus.
Jebkurā gadījumā papildinājums Meklēšanas risinājumi (Risinātājs) ir ļoti spēcīgs un skaists Excel rīks, un tas ir jūsu uzmanības vērts, jo tas var palīdzēt daudzās sarežģītās situācijās, ar kurām nākas saskarties mūsdienu biznesā.