Mündəricat
Problemin formalaşdırılması
Tutaq ki, işlədiyiniz şirkətin üç anbarı var, oradan mallar Moskvaya səpələnmiş beş mağazanıza gedir.
Hər bir mağaza bizə məlum olan müəyyən miqdarda mal sata bilir. Anbarların hər birinin məhdud tutumu var. Vəzifə ümumi nəqliyyat xərclərini minimuma endirmək üçün malların hansı anbardan hansı mağazalara çatdırılacağını rasional seçməkdir.
Optimallaşdırmaya başlamazdan əvvəl Excel vərəqində sadə bir cədvəl tərtib etmək lazımdır - vəziyyəti təsvir edən riyazi modelimiz:
Belə başa düşülür:
- Açıq sarı cədvəl (C4:G6) hər anbardan hər mağazaya bir məhsulun göndərilməsinin dəyərini təsvir edir.
- Bənövşəyi hücrələr (C15:G14) hər bir mağazanın satması üçün tələb olunan malların miqdarını təsvir edir.
- Qırmızı hüceyrələr (J10:J13) hər bir anbarın tutumunu – anbarın saxlaya biləcəyi maksimum mal miqdarını göstərir.
- Sarı (C13:G13) və mavi (H10:H13) xanalar müvafiq olaraq yaşıl xanalar üçün sıra və sütun məbləğləridir.
- Ümumi göndərmə dəyəri (J18) malların sayının məhsullarının və onlara uyğun göndərmə xərclərinin cəmi kimi hesablanır - hesablama üçün funksiya burada istifadə olunur SUMPRODUCT (MƏHSUL).
Beləliklə, vəzifəmiz yaşıl hüceyrələrin optimal dəyərlərinin seçilməsinə qədər azaldılır. Və beləliklə, xətt üçün ümumi məbləğ (mavi xanalar) anbarın (qırmızı xanalar) tutumunu aşmasın və eyni zamanda hər bir mağaza satmalı olduğu malların miqdarını (hər mağaza üçün məbləği) alır. sarı hüceyrələr tələblərə mümkün qədər yaxın olmalıdır - bənövşəyi hüceyrələr).
Həll
Riyaziyyatda resursların optimal paylanmasının seçilməsi ilə bağlı bu cür problemlər uzun müddətdir ki, tərtib edilmiş və təsvir edilmişdir. Və təbii ki, onların həlli yolları çoxdan küt sadalama ilə deyil (bu, çox uzundur), lakin çox az sayda təkrarlamalarla işlənib hazırlanmışdır. Excel əlavədən istifadə edərək istifadəçiyə bu cür funksiyaları təqdim edir. Axtarış Həlləri (Həlledici) tabdan Tarix (Tarix):
Nişanda varsa Tarix Sizin Excel-də belə bir əmr yoxdur – hər şey qaydasındadır – bu o deməkdir ki, əlavə hələ bağlanmayıb. Aktivləşdirmək üçün açın fayl, Sonra seçin Parameters - Add-ons - haqqında (Seçimlər — Əlavələr — Get). Açılan pəncərədə bizə lazım olan xəttin yanındakı qutuyu işarələyin Axtarış Həlləri (Həlledici).
Əlavəni işə salaq:
Bu pəncərədə aşağıdakı parametrləri təyin etməlisiniz:
- Hədəf funksiyasını optimallaşdırın (Qurun tpul hüceyrə) – burada optimallaşdırmamızın son əsas məqsədini, yəni ümumi göndərmə dəyəri olan çəhrayı qutunu (J18) qeyd etmək lazımdır. Hədəf hüceyrə minimuma endirilə bilər (əgər bu, bizim vəziyyətimizdə olduğu kimi xərclərdirsə), maksimuma çatdırıla bilər (məsələn, mənfəətdirsə) və ya onu müəyyən bir dəyərə çatdırmağa çalışın (məsələn, ayrılmış büdcəyə tam uyğun).
- Dəyişən Hüceyrələrin dəyişdirilməsi (By dəyişdirmə hüceyrələr) – burada nəticəmizi əldə etmək istədiyimiz dəyərləri dəyişməklə yaşıl xanaları (C10: G12) göstəririk – minimum çatdırılma dəyəri.
- Məhdudiyyətlərə uyğundur (mövzu üçün bu Məhdudiyyətlər) – optimallaşdırma zamanı nəzərə alınmalı olan məhdudiyyətlərin siyahısı. Siyahıya məhdudiyyətlər əlavə etmək üçün düyməni basın əlavə etmək (Əlavə et) və görünən pəncərədə şərti daxil edin. Bizim vəziyyətimizdə bu tələb məhdudiyyəti olacaq:
və anbarların maksimum həcminə məhdudiyyət:
Fiziki amillərlə (anbarların və nəqliyyat vasitələrinin tutumu, büdcə və vaxt məhdudiyyətləri və s.) bağlı aşkar məhdudiyyətlərə əlavə olaraq, bəzən “Excel üçün xüsusi” məhdudiyyətlər əlavə etmək lazımdır. Beləliklə, məsələn, Excel malların mağazalardan yenidən anbara daşınmasını təklif etməklə, çatdırılma xərclərini "optimallaşdırmaq" üçün asanlıqla təşkil edə bilər - xərclər mənfi olacaq, yəni biz qazanc əldə edəcəyik! 🙂
Bunun baş verməsinin qarşısını almaq üçün qeyd qutusunu aktiv buraxmaq daha yaxşıdır. Limitsiz dəyişənləri mənfi olmayan edin və ya hətta bəzən məhdudiyyətlər siyahısında belə məqamları açıq şəkildə qeyd edin.
Bütün lazımi parametrləri təyin etdikdən sonra pəncərə belə görünməlidir:
Həll metodunu seçin açılan siyahısında siz əlavə olaraq üç variantdan birini həll etmək üçün müvafiq riyazi metodu seçməlisiniz:
- Simpleks metodu xətti məsələlərin, yəni çıxışın girişdən xətti asılı olduğu məsələlərin həlli üçün sadə və sürətli üsuldur.
- Ümumi Aşağı Qradient Metod (OGG) – giriş və çıxış məlumatları arasında mürəkkəb qeyri-xətti asılılıqların olduğu qeyri-xətti problemlər üçün (məsələn, satışın reklam xərclərindən asılılığı).
- Çözüm üçün təkamül axtarışı – bioloji təkamül prinsiplərinə əsaslanan nisbətən yeni optimallaşdırma metodu (salam Darvin). Bu üsul ilk ikisindən dəfələrlə uzun işləyir, lakin demək olar ki, hər hansı bir problemi həll edə bilər (qeyri-xətti, diskret).
Bizim vəzifəmiz aydın şəkildə xəttidir: 1 ədəd çatdırıldı - 40 rubl xərcləndi, 2 ədəd çatdırıldı - 80 rubl xərcləndi. və s., ona görə də simpleks üsulu ən yaxşı seçimdir.
İndi hesablama üçün məlumatlar daxil edildikdən sonra düyməni basın Bir həll tapın (Həll et)optimallaşdırmaya başlamaq üçün. Çox dəyişən hüceyrələr və məhdudiyyətlər olan ağır hallarda, həll yolu tapmaq uzun müddət çəkə bilər (xüsusilə təkamül metodu ilə), lakin Excel üçün tapşırığımız problem olmayacaq - bir neçə dəqiqədən sonra aşağıdakı nəticələri əldə edəcəyik. :
Anbarlarımızın tutumunu aşmamaqla və hər bir mağaza üçün tələb olunan sayda mal üçün bütün tələbləri təmin etməklə, tədarük həcminin mağazalar arasında necə maraqlı paylandığına diqqət yetirin.
Tapılan həll bizə uyğundursa, biz onu saxlaya və ya orijinal dəyərlərə qayıdıb başqa parametrlərlə yenidən cəhd edə bilərik. Siz həmçinin seçilmiş parametr birləşməsini olaraq saxlaya bilərsiniz Ssenari. İstifadəçinin istəyi ilə Excel üç növ qura bilər Hesabatlar ayrı vərəqlərdə həll olunan problem haqqında: nəticələr haqqında hesabat, həllin riyazi sabitliyi haqqında hesabat və həllin məhdudiyyətləri (məhdudiyyətləri) haqqında hesabat, lakin əksər hallarda onlar yalnız mütəxəssisləri maraqlandırır. .
Bununla belə, Excel-in uyğun bir həll tapa bilmədiyi vəziyyətlər var. Nümunəmizdə mağazaların tələblərini anbarların ümumi tutumundan daha çox miqdarda göstərsək, belə bir halı simulyasiya etmək mümkündür. Sonra, optimallaşdırma həyata keçirərkən, Excel həllə mümkün qədər yaxınlaşmağa çalışacaq və sonra həllin tapılmaması barədə bir mesaj göstərəcəkdir. Bununla belə, hətta bu halda da çoxlu faydalı məlumatımız var – xüsusən də biznes proseslərimizin “zəif əlaqələrini” görə bilirik və təkmilləşdirilməli sahələri anlaya bilərik.
Nəzərə alınan nümunə, əlbəttə ki, nisbətən sadədir, lakin daha mürəkkəb problemləri həll etmək üçün asanlıqla miqyaslanır. Misal üçün:
- Maliyyə resurslarının bölüşdürülməsinin optimallaşdırılması layihənin biznes planında və ya büdcəsində xərclər maddəsi üzrə. Məhdudiyyətlər, bu halda, maliyyələşdirmənin məbləği və layihənin vaxtı olacaq və optimallaşdırmanın məqsədi mənfəəti artırmaq və layihə xərclərini minimuma endirməkdir.
- İşçilərin iş qrafikinin optimallaşdırılması müəssisənin əmək haqqı fondunu minimuma endirmək məqsədi ilə. Məhdudiyyətlər, bu halda, məşğulluq cədvəlinə və ştat cədvəlinin tələblərinə uyğun olaraq hər bir işçinin istəkləri olacaqdır.
- İnvestisiya investisiyalarının optimallaşdırılması – yenə də mənfəəti artırmaq və ya (daha vacib olarsa) riskləri minimuma endirmək üçün bir neçə bank, qiymətli kağızlar və ya müəssisələrin səhmləri arasında vəsaitlərin düzgün bölüşdürülməsi zərurəti.
Hər halda, əlavə Axtarış Həlləri (Həlledici) çox güclü və gözəl Excel alətidir və diqqətinizə layiqdir, çünki müasir biznesdə qarşılaşdığınız bir çox çətin vəziyyətlərdə kömək edə bilər.