Elektron cədvəldən istifadə etməklə həll edilən problemlərin əksəriyyəti məlum mənbə məlumatlarından istifadə etməklə istənilən nəticənin tapılmasını əhatə edir. Ancaq Excel-də tərs problemi həll etməyə imkan verən alətlər var: istədiyiniz nəticəni əldə etmək üçün ilkin məlumatları seçin.
Belə vasitələrdən biriHəll tapmaq, bu sözdə "optimallaşdırma problemləri" həll etmək üçün xüsusilə əlverişlidir.
Əgər əvvəllər istifadə etməmisinizsəHəll tapmaq, sonra müvafiq əlavəni quraşdırmalı olacaqsınız.
Bunu belə edə bilərsiniz:
əmr vasitəsilə Excel 2007-dən köhnə versiyalar üçün menyu Xidmət -> Əlavələr;
Excel 2007-dən bəri dialoq qutusu vasitəsiləExcel Seçimləri
Excel 2007-dən bəribaşlamaq üçün düyməHəll tapmaq tabda görünəcək Data.
Excel 2007-dən əvvəlki versiyalarda oxşar əmr menyuda görünəcəkXidmət
İş proseduruna baxaq Həll tapmaq sadə bir misaldan istifadə etməklə.
Misal 1. Bonus paylanması
Tutaq ki, siz istehsalat şöbəsinin müdirisiniz və 100.000 rubl məbləğində mükafatı ədalətli şəkildə paylamalısınız. şöbənin işçiləri arasında onların vəzifə maaşlarına proporsional olaraq. Başqa sözlə, əmək haqqı bonusunun ölçüsünü hesablamaq üçün mütənasiblik əmsalı seçmək lazımdır.
İlk növbədə, nəticənin əldə edilməli olduğu ilkin məlumatlar və düsturlar ilə bir cədvəl yaradırıq. Bizim vəziyyətimizdə nəticə mükafatın ümumi məbləğidir. Hədəf xanasının (C8) düsturlar vasitəsilə dəyişdiriləcək istənilən xana ilə əlaqələndirilməsi çox vacibdir (E2). Nümunədə onlar hər bir işçi üçün bonus məbləğini hesablayan aralıq düsturlar vasitəsilə birləşdirilir (C2:C7).
İndi başlayaq Həll tapmaq və açılan dialoq qutusunda lazımi parametrləri təyin edin. Dialoq qutularının görünüşü müxtəlif versiyalar bir az fərqli:
Excel 2010-dan bəri
Excel 2010-dan əvvəl
Düyməni basdıqdan sonraHəll tap (Çalış)Cədvəldə əldə edilən nəticəni artıq görə bilərsiniz. Eyni zamanda, ekranda bir dialoq qutusu görünürHəll axtarışının nəticələri.
Excel 2010-dan bəri
Excel 2010-dan əvvəl
Cədvəldə gördüyünüz nəticə sizə uyğun gəlirsə, o zaman dialoq qutusunda Həll axtarış nəticələri basın OK və nəticəni cədvələ qeyd edin. Nəticə sizə uyğun gəlmirsə, vurun Ləğv et və cədvəlin əvvəlki vəziyyətinə qayıdın.
Bu problemin həlli belə görünür
Əhəmiyyətli: yeni nəticə əldə etmək üçün mənbə məlumatlarında hər hansı dəyişiklik üçün Həll tapmaq yenidən qaçmalı olacaq.
Başqa bir optimallaşdırma probleminə baxaq (mənfəəti artırmaq)
Nümunə 2. Mebel istehsalı (mənfəətin maksimumlaşdırılması)
Şirkət iki model A və B prefabrik kitab rəfləri istehsal edir.
Onların istehsalı xammalın (yüksək keyfiyyətli lövhələr) mövcudluğu və maşın emal müddəti ilə məhdudlaşır.
Hər bir model A məhsulu üçün 3 m² tələb olunurlövhələr və məhsul modeli B üçün - 4 m². Şirkət təchizatçılarından həftədə 1700 m²-ə qədər lövhə ala bilər.
Hər bir model bir məhsul üçün 12 dəqiqə maşın vaxtı tələb olunur, və məhsul modeli B üçün - 30 dəq. Həftədə 160 saat maşın vaxtından istifadə edilə bilər.
A modelinin hər bir məhsulu 60 rubl gətirirsə, maksimum qazanc əldə etmək üçün şirkət həftədə hər bir modeldən neçə məhsul istehsal etməlidir. mənfəət və B modelinin hər bir məhsulu 120 rubl təşkil edir. mənfəət?
Biz artıq proseduru bilirik.
Əvvəlcə mənbə məlumatları və düsturları olan cədvəllər yaradırıq. Vərəqdəki hüceyrələrin düzülüşü müəllif üçün əlverişli olduğu üçün tamamilə ixtiyari ola bilər. Məsələn, şəkildəki kimi
başlayaq Həll tapmaqvə dialoq qutusunda lazımi parametrləri təyin edin
- Hədəf xanası B12 mənfəətin hesablanması üçün düsturdan ibarətdir
- Optimallaşdırma parametri - maksimum
- Dəyişdirilmiş xanalar B9:C9
- Məhdudiyyətlər: tapılan dəyərlər tam, mənfi olmayan olmalıdır; maşın vaxtının ümumi miqdarı 160 saatdan çox olmamalıdır (D16 xanasına istinad); xammalın ümumi miqdarı 1700 m²-dən çox olmamalıdır (istinad xanası D15). Burada D15 və D16 xanalarına keçidlər əvəzinə nömrələr göstərilə bilərdi, lakin keçidlərdən istifadə edərkən məhdudiyyətlərə istənilən dəyişiklik birbaşa cədvəldə edilə bilər.
- Düyməni basın Həll tap (Çalış) və təsdiqdən sonra nəticəni alırıq
Ancaq düsturları düzgün yaratsanız və məhdudiyyətlər qoysanız belə, nəticə gözlənilməz ola bilər. Məsələn, bu problemi həll edərkən aşağıdakı nəticəni görə bilərsiniz:
Və bu, məhdudiyyət qoyulmasına baxmayaraq bütöv. Belə hallarda parametrləri tənzimləməyə cəhd edə bilərsiniz Həll tapmaq. Bunu pəncərədə etmək üçün Həll tapmaq düyməsini basın Seçimlər və eyni adlı dialoq qutusuna daxil oluruq
Seçilmiş parametrlərdən birincisi hesablamaların düzgünlüyünə cavabdehdir. Onu azaltmaqla, daha dəqiq nəticə əldə edə bilərsiniz, bizim vəziyyətimizdə - tam dəyərlər. Vurğulanmış variantlardan ikincisi (Excel 2010-dan başlayaraq mövcuddur) suala cavab verir: məhdudlaşdırma zamanı fraksiya nəticələrini necə əldə etmək olarbütöv? Belə çıxır Həll tapmaqbu məhdudiyyət sadəcə yoxlanılan bayrağa uyğun olaraq nəzərə alınmadı.
Nümunə 3. Nəqliyyat problemi (xərclərin minimuma endirilməsi)
Tikinti şirkətinin sifarişi ilə qum üç təchizatçıdan (karxanadan) beş istehlakçıya (tikinti sahəsi) daşınır. Buna görə də çatdırılma dəyəri obyektin dəyərinə daxildir tikinti şirkəti tikinti sahələrinin qum tələbatını ən ucuz şəkildə ödəməkdə maraqlıdır.
Verilmişdir: karxanalarda qum ehtiyatları; tikinti sahəsinin qum ehtiyacları; hər bir təchizatçı-istehlakçı cütü arasında nəqliyyat xərcləri.
Ehtiyacları (haradan və hara) ödəmək üçün optimal nəqliyyat sxemini tapmaq lazımdır ki, burada ümumi nəqliyyat xərcləri minimal olacaqdır.
Mənbə məlumatları və məhdudiyyətləri, istədiyiniz hüceyrələr və hədəf hüceyrəsi olan hüceyrələrin yerinin nümunəsi şəkildə göstərilmişdir.
Boz xanalarda sətirlər və sütunlar üzrə məbləğlər üçün düsturlar, hədəf xanada isə ümumi nəqliyyat xərclərinin hesablanması üçün düstur var..
Həll axtarışını işə salırıq və lazımi parametrləri təyin edirik (şəklə bax)
klikləyin Həll tap (Çalış) və aşağıda göstərilən nəticəni əldə edin
Yekun olaraq, istifadə etməkdə əlinizi sınamağı təklif edirəm Həll tapmaq və köhnə problemi həll etmək üçün istifadə edin:
Kəndli bazardan 100 baş mal-qaranı 100 rubla alıb. Buğa 10 rubl, inək 5 rubl, buzov 50 qəpikdir. Kəndli nə qədər öküz, inək, buzov alıb?
Həll tapmaq bir əlavədir Microsoft Excel, hansı ilə tapa bilərsiniz optimal həll istifadəçi tərəfindən müəyyən edilmiş məhdudiyyətləri nəzərə alaraq tapşırıqlar.
(Bu əlavə ilə müqayisədə bəzi dəyişikliklərə məruz qalmışdır) həllini tapmağı nəzərdən keçirəcəyik əvvəlki versiya V .
Bu yazıda baxacağıq:
- MS EXCEL vərəqində optimallaşdırma modelinin yaradılması
- qəbulu Həll yolu tapmaq;
- sadə nümunə (xətti model).
Quraşdırma Həll axtarın
Komanda Həll tapmaq qrupdadır Təhlil nişanda Data.
Əgər komanda Həll tapmaq qrupda Təhlil mövcud deyil, eyni adlı əlavəni aktivləşdirməlisiniz.
Bunu etmək üçün:
- Nişanda Fayl komanda seçin Seçimlər, sonra kateqoriya Əlavələr;
- Sahədə Nəzarət dəyəri seçin Excel əlavələri və düyməni basın Get;
- Sahədə Mövcud əlavələr elementin yanındakı qutuyu yoxlayın Həll tapmaq və OK düyməsini basın.
Qeyd. Pəncərə Əlavələr tabda da mövcuddur Tərtibatçı. Bu nişanı necə aktivləşdirmək olar.
Düyməni basdıqdan sonra Həll tapmaq qrupda Analiz, onun dialoq qutusu açılacaq .
Tez-tez istifadə ilə Həll tapmaq onu Paneldən işə salmaq daha rahatdır sürətli giriş, və Data tabından deyil. Paneldə düyməni yerləşdirmək üçün üzərinə sağ klikləyin və seçin Tez Giriş Alətlər Panelinə əlavə edin.
Modellər haqqında
Bu bölmə optimallaşdırma modeli konsepsiyası ilə yeni tanış olanlar üçündür.
Məsləhət. İstifadədən əvvəl Həll tapmaq Optimallaşdırma problemlərinin həlli və modellərin qurulması ilə bağlı ədəbiyyatı öyrənməyi şiddətlə tövsiyə edirik.
Aşağıda bu mövzuda kiçik bir təhsil proqramı var.
Üst quruluş Həll tapmaq müəyyən etməyə kömək edir ən yaxşı yol et bir şey:
- "Nəsə" investisiyalar üçün pul ayırmaq, anbar yükləmək, malların çatdırılması və ya optimal həlli tapmaq üçün lazım olan hər hansı digər mövzu fəaliyyətini əhatə edə bilər.
- Bu vəziyyətdə "ən yaxşı yol" və ya optimal həll o deməkdir: mənfəəti artırmaq, xərcləri minimuma endirmək, nail olmaq ən yaxşı keyfiyyət və s.
Budur bəziləri tipik nümunələr optimallaşdırma vəzifələri:
- İstehsal olunan məhsulların satışından əldə edilən gəlirin hansı həddə maksimum olduğunu müəyyən etmək;
- Ümumi nəqliyyat xərclərinin minimum olacağını müəyyənləşdirin;
- İstehsalın ümumi xərclərinin minimal olacağını tapın;
- Bütün layihə işlərini (kritik yol) tamamlamaq üçün minimum son tarixi müəyyənləşdirin.
Qarşıya qoyulan tapşırığı rəsmiləşdirmək üçün mövzu sahəsinin əsas xüsusiyyətlərini əks etdirən (və xırda detalları ehtiva etməyən) bir model yaratmaq lazımdır. Qeyd etmək lazımdır ki, model optimallaşdırılmışdır Həll tapmaq yalnız bir göstərici ilə(bu optimallaşdırılmış göstərici adlanır hədəf funksiyası).
MS EXCEL-də model dəyişənləri arqument kimi istifadə edən bir-biri ilə əlaqəli düsturlar toplusudur. Tipik olaraq, bu dəyişənlər yalnız istifadəçi tərəfindən müəyyən edilmiş məhdudiyyətlərə uyğun olaraq etibarlı dəyərləri qəbul edə bilər.
Həll tapmaq bu dəyişənlərin elə dəyərlərini seçir (müəyyən edilmiş məhdudiyyətlərə uyğun olaraq) məqsəd funksiyası maksimum (minimum) və ya verilmiş ədədi dəyərə bərabər olsun.
Qeyd. Ən sadə halda, model tək bir düsturla təsvir edilə bilər. Bu modellərdən bəziləri alətdən istifadə edərək optimallaşdırıla bilər. İlk görüşdən əvvəl Həll tapmaqƏvvəlcə əlaqəli aləti ətraflı başa düşmək mantiqidir.
Əsas fərqlər Parametr seçimi-dan Həll tapmaq:
- Parametr seçimi yalnız tək dəyişən modellərlə işləyir;
- dəyişənlərə məhdudiyyət qoymaq mümkün deyil;
- məqsəd funksiyasının maksimum və ya minimumu deyil, onun müəyyən qiymətə bərabərliyi müəyyən edilir;
- yalnız o halda səmərəli işləyir xətti modellər, qeyri-xətti halda, yerli optimalı tapır (dəyişənin ilkin dəyərinə ən yaxın).
MS EXCEL-də optimallaşdırma modelinin hazırlanması
Həll tapmaq məqsəd funksiyasının dəyərini optimallaşdırır. Məqsəd funksiyası xanada tək dəyəri qaytaran düsturdur. Düsturun nəticəsi model dəyişənlərindən (mütləq birbaşa deyil, digər düsturların hesablanması nəticəsində) asılı olmalıdır.
Model məhdudiyyətləri həm dəyişənlərin özlərinin dəyişmə diapazonuna, həm də bu dəyişənlərdən asılı olan digər model düsturlarının hesablanmasının nəticələrinə görə qoyula bilər.
Model dəyişənləri və məhdudiyyətləri ehtiva edən bütün xanalar iş kitabının yalnız bir vərəqində yerləşməlidir. Dialoq qutusuna parametrlərin daxil edilməsi Həll tapmaq yalnız bu vərəqdən mümkündür.
Hədəf funksiyası (xana) da bu vərəqdə yerləşməlidir. Lakin, aralıq hesablamalar (düsturlar) digər vərəqlərə yerləşdirilə bilər.
Məsləhət. Model məlumatlarını elə təşkil edin ki, bir MS EXCEL vərəqində yalnız bir model olsun. Əks halda, hesablamalar aparmaq üçün daim parametrləri saxlamaq və yükləmək məcburiyyətində qalacaqsınız Həll tapmaq(aşağıya bax).
Onunla işləmək üçün bir alqoritm təqdim edək Həll tapmaq, tərtibatçıların özləri tərəfindən tövsiyə olunur (www.solver.com):
- Model dəyişənləri olan xanaları müəyyənləşdirin (qərar dəyişənləri);
- Modelinizin məqsəd funksiyasını hesablayacaq xanada düstur yaradın;
- Məhdudiyyətlərlə müqayisədə dəyərləri hesablayacaq xanalarda düsturlar yaradın (ifadənin sol tərəfi);
- Dialoq qutusundan istifadə etməklə Həll tapmaq dəyişənləri olan xanalara, məqsəd funksiyasına, məhdudiyyətlər üçün düsturlara və məhdudiyyətlərin öz dəyərlərinə keçidlər daxil edin;
- Qaç Həll tapmaq optimal həllini tapmaq üçün.
Sadə bir nümunədən istifadə edərək bütün bu addımları nəzərdən keçirək.
Sadə istifadə nümunəsi Həll tapmaq
Konteyneri malla yükləmək lazımdır ki, qabın çəkisi maksimum olsun. Konteynerin həcmi 32 kubmetrdir. Əşyalar qutularda və yeşiklərdədir. Hər qutu malın çəkisi 20 kq, həcmi 0,15 m3-dir. Qutu - müvafiq olaraq 80kq və 0,5m3. Qabların ümumi sayının ən azı 110 ədəd olması lazımdır.
Bu modelləri aşağıdakı kimi təşkil edirik (nümunə fayla bax).
Model dəyişənləri (hər növ konteynerin miqdarı) yaşıl rənglə vurğulanır.
Məqsəd funksiyası (bütün qutuların və qutuların ümumi çəkisi) qırmızı rəngdədir.
Model məhdudiyyətləri: qabların minimum miqdarı (>=110) və ümumi həcm (<=32) – синим.
Məqsəd funksiyası düsturdan istifadə etməklə hesablanır =MƏHSUL(B8:C8,B6:C6) konteynerə yüklənmiş bütün qutuların və yeşiklərin ümumi çəkisidir.
Eynilə, ümumi həcmi hesablayırıq - =MƏHSUL(B7:C7,B8:C8). Bu düstur qutuların və qutuların ümumi həcminə məhdudiyyət qoymaq üçün lazımdır (<=32).
Həmçinin, model məhdudiyyətini təyin etmək üçün konteynerlərin ümumi sayını hesablayırıq =SUM(B8:C8) .
İndi dialoq qutusundan istifadə edin Həll tapmaq Dəyişənləri, məqsəd funksiyasını, məhdudiyyətlər üçün düsturları və məhdudiyyətlərin öz dəyərlərini (və ya müvafiq xanalara bağlantılar) ehtiva edən xanalara bağlantılar daxil edək.
Aydındır ki, qutuların və qutuların sayı tam ədəd olmalıdır - bu modelin başqa bir məhdudiyyətidir.
Düyməni basdıqdan sonra Bir həll tapın qutuların və yeşiklərin sayı tapılacaq ki, onların ümumi çəkisi (məqsəd funksiyası) maksimumdur və eyni zamanda bütün göstərilən məhdudiyyətlər yerinə yetirilir.
CV
Əslində, optimallaşdırma problemlərini həll edərkən əsas problem istifadə olunur Həll tapmaqƏhəmiyyətli olan bu analiz alətinin qurulmasının incəlikləri deyil, qarşıya qoyulan vəzifəyə adekvat olan modelin qurulmasının düzgünlüyüdür. Buna görə də, digər məqalələrdə biz xüsusi olaraq tikinti modellərinə diqqət yetirəcəyik, çünki "əyri" bir model tez-tez istifadə edərək həll tapa bilməməyin səbəbidir. Həll tapmaq.
Çox vaxt bir neçə tipik problemi nəzərdən keçirmək, onların arasında oxşarını tapmaq və sonra bu modeli öz işinizə uyğunlaşdırmaq daha asandır.
istifadə edərək klassik optimallaşdırma məsələlərinin həlli Həll tapmaq hesab olunur.
Həlledici mümkün bir həll tapa bilmədi
Bu mesaj göründüyü zaman Həll tapmaq eyni zamanda bütün məhdudiyyətləri təmin edən dəyişən dəyərlərin birləşmələrini tapa bilmədi.
Əgər istifadə edirsinizsə Xətti məsələlərin həlli üçün sadə üsul, onda əmin ola bilərsiniz ki, həqiqətən heç bir həll yoxdur.
Həmişə dəyişənlərin ilkin qiymətləri ilə başlayan qeyri-xətti problemlərin həlli üçün bir üsuldan istifadə edirsinizsə, bu, mümkün həllin bu ilkin dəyərlərdən uzaq olduğunu ifadə edə bilər. Əgər qaçsan Həll tapmaq dəyişənlərin digər ilkin qiymətləri ilə, sonra bəlkə bir həll tapılacaq.
Təsəvvür edək ki, qeyri-xətti metoddan istifadə edərək problemi həll edərkən dəyişənləri olan xanalar boş qalıb (yəni ilkin dəyərlər 0-dır) və Həll tapmaq həllini tapmadı. Bu o demək deyil ki, həqiqətən də həll yolu yoxdur (baxmayaraq ki, belə ola bilər). İndi, müəyyən ekspert qiymətləndirməsinin nəticələrinə əsasən, dəyişənləri olan xanalara başqa bir dəyər dəstini daxil edəcəyik ki, bu da sizin fikrinizcə optimal (axtaran) birinə yaxındır. Bu halda, Həll tapmaq həllini tapa bilər (əgər həqiqətən varsa).
Qeyd. Modelin qeyri-xəttiliyinin hesablama nəticələrinə təsiri haqqında məqalənin sonuncu hissəsində oxuya bilərsiniz.
İstənilən halda (xətti və ya qeyri-xətti), siz ilk növbədə məhdudiyyətlərin, yəni eyni vaxtda təmin edilə bilməyən şərtlərin ardıcıllığına görə modeli təhlil etməlisiniz. Çox vaxt bunun səbəbi səhv nisbət seçimidir (məsələn,<= вместо >=) və ya limit dəyəri.
Əgər, məsələn, yuxarıda müzakirə edilən misalda maksimum həcmin dəyəri 32 m3 əvəzinə 16 m3 olaraq təyin edilibsə, bu məhdudiyyət oturacaqların minimum sayına (110) qoyulan məhdudiyyətə zidd olacaq, çünki yerlərin minimum sayı 16,5 m3-ə bərabər bir həcmə uyğundur (110 * 0,15, burada 0,15 qutunun həcmi, yəni. ən kiçik konteyner). Maksimum həcm həddini 16 m3-ə təyin etməklə, Həll tapmaq həllini tapmayacaq.
17 m3 həddi ilə Həll tapmaq həllini tapacaq.
Bəzi parametrlər Həll tapmaq
Həll üsulu
Yuxarıda müzakirə edilən model xəttidir, yəni. məqsəd funksiyası (M maksimum ola biləcək ümumi çəkidir) aşağıdakı M=a1*x1+a2*x2 tənliyi ilə ifadə edilir, burada x1 və x2 model dəyişənləridir (qutuların və qutuların sayı), a1 və a2 onların çəkiləridir. Xətti modeldə məhdudiyyətlər həm də dəyişənlərin xətti funksiyaları olmalıdır. Bizim vəziyyətimizdə V=b1*x1+b2*x2 həcm məhdudiyyəti də xətti asılılıqla ifadə edilir. Aydındır ki, başqa bir məhdudiyyət - Konteynerlərin maksimum sayı (n) də xətti x1+x2-dir.
Bu halda qeyri-xətti məsələnin həlli metodunu seçmək lazımdır. Qeyri-xətti asılılıqların nümunələri: V=b1*x1*x1; V=b1*x1^0.9; V=b1*x1*x2, burada x dəyişən, V isə məqsəd funksiyasıdır.
Düymələr Əlavə et, Redaktə et, Sil
Bu düymələr sizə model məhdudiyyətlərini əlavə etmək, redaktə etmək və silmək imkanı verir.
Sıfırlama düyməsi
Bütün parametrləri silmək üçün Həll tapmaq düyməsini basın Sıfırlayın– dialoq qutusu silinəcək.
Bu seçim müxtəlif məhdudiyyət seçimlərindən istifadə edərkən əlverişlidir. Model parametrlərini saxlayarkən (düymə Yüklə/Saxla, sonra düyməni basın Saxla) yerləşdiriləcək diapazonun (sütun) yuxarı xanasını seçmək təklif olunur: məqsəd funksiyasına keçid, dəyişənləri olan hüceyrələrə keçidlər, məhdudiyyətlər və həll üsullarının parametrləri (düymə vasitəsilə mövcuddur) Seçimlər). Saxlamadan əvvəl bu aralığın model məlumatlarını ehtiva etmədiyinə əmin olun.
Saxlanmış parametrləri yükləmək üçün əvvəlcə düyməni basın Yüklə/Saxla, sonra görünən dialoq qutusunda düyməni basın Yüklə, sonra əvvəllər saxlanmış parametrləri ehtiva edən xanaların diapazonunu təyin edin (yalnız yuxarı xananı göstərə bilməzsiniz). OK düyməsini basın. Tapşırıq parametrlərinin cari dəyərlərini sıfırlamağı və onları yeniləri ilə əvəz etməyi təsdiqləyin.
Dəqiqlik
Bir model yaratarkən, tədqiqatçı əvvəlcə hədəf funksiyasının və dəyişənlərin variasiya diapazonlarına dair bəzi təxminlərə malikdir. MS EXCEL-də hesablamaları nəzərə alaraq, bu variasiya diapazonlarının hesablama dəqiqliyindən əhəmiyyətli dərəcədə yüksək olması tövsiyə olunur (adətən 0,001-dən 0,000001-ə qədər müəyyən edilir). Bir qayda olaraq, modeldəki məlumatlar normallaşdırılır ki, məqsəd funksiyası və dəyişənlərin dəyişmə diapazonu 0,1 - 100,000 diapazonunda olsun, əlbəttə ki, hər şey konkret modeldən asılıdır, lakin dəyişənləriniz daha çox dəyişirsə 5-6 böyüklükdən daha çox, onda bəlkə də, məsələn, logarifm əməliyyatından istifadə edərək modeli "kobudlaşdırmalısınız".
Müəyyən meyarlara cavab verən Excel cədvəli dəyərlərinin optimallaşdırılması mürəkkəb bir proses ola bilər. Xoşbəxtlikdən, Microsoft bir əlavə təklif edir Problemin həlliədədi optimallaşdırma üçün. Bu xidmət bütün problemləri həll edə bilməsə də, faydalı bir vasitə ola bilər nə olarsa. Bu yazı əlavəyə həsr olunub Problemin həlli Excel-də.
Üst quruluş Problemin həlli Excel-in bütün versiyalarında mövcuddur. Nəzərə alın ki, ekran görüntüləri sizin versiyanızı əks etdirməyə bilər. Bəzi funksiyalar əlavə versiyadan asılı olaraq yerini dəyişə bilsə də, funksionallıq praktiki olaraq dəyişməz olaraq qalır.
Həll Axtarışı nədir
Həll yollarının tapılması hədəf hüceyrələrin dəyərlərini dəyişdirərək bir həll tapmağa kömək edən bir Excel əlavəsidir. Məqsəd bəzi hədəf dəyərini minimuma endirmək, maksimuma çatdırmaq və ya nail olmaq ola bilər. Problem giriş meyarlarını və ya istifadəçi tərəfindən müəyyən edilmiş məhdudiyyətləri tənzimləməklə həll edilir.
Excel-də həll yollarını haradan tapmaq olar
Üst quruluş Həll yollarının tapılması Excel ilə gəlir, lakin standart olaraq qeyri-aktivdir. Onu aktivləşdirmək üçün nişana keçin Fayl qrupa Seçimlər. Görünən dialoq qutusunda Parametrlər seçin Əlavələr -> İdarəetmə: ƏlavələrExcel -> Get. Pəncərədə Əlavələr sahənin yanındakı qutuyu yoxlayın Həll tapmaq basın OK.
İndi tabda Data yeni qrup yaranıb Təhlil düyməsi ilə Həll tapmaq.
Həll Axtarışından istifadə nümunəsi
Bu yazı istifadə halına əsaslanır . Fayl bütün Excel versiyaları ilə uyğun gəlir.
Problemin Tərifi
Tutaq ki, hər birinin öz dəyəri olan 8 elementdən ibarət məlumat dəsti var.
... və dəyərləri iki qrupa birləşdirməliyik ki, bu qrupların dəyərlərinin cəmi təxminən üst-üstə düşsün.
Əvvəlcə hər bir elementi qrupa müəyyənləşdirməlisiniz.
Bir maddənin qrupa əlavə edilməsini göstərmək üçün biz onları bir (1), əks halda sıfır (0) ilə qeyd edəcəyik.
Növbəti sütunda qrupdakı hər bir elementin dəyərlərini ümumiləşdirəcəyik və sonra sütunun sonunda ümumiləşdirəcəyik.
Bunu etmək üçün hər bir qrupdakı hər bir elementin dəyərini emal etməliyik, biz elementin dəyərini bu maddəyə uyğun gələn qrup dəyərinə vururuq;
Nəhayət, qrupları ümumiləşdirməli və onlar arasındakı fərqlərlə işləməliyik.
Bizim vəzifəmiz qrupların cəmi arasındakı fərqi minimuma endirməkdir.
İndi biz C və D sütunlarına vahidləri əl ilə daxil etməklə hər bir qrupa xal təyin edə bilərik. Excel G11 xanasında qrup məbləğlərindəki fərqi göstərəcək.
Problem ondadır ki, mümkün birləşmələrin sayı 2 8-dir, yəni. sualına 256 mümkün cavab. Hər birinə 5 saniyə sərf etsək, tempi davam etdirə biləcəyimizi və ən yaxşı kombinasiyanı xatırlaya biləcəyimizi fərz etsək, bu, bizə 21,3 dəqiqə çəkəcək.
ordadır Həll tapmaq tətbiq tapır.
Excel-də optimal həllin tapılması
Xidməti tətbiq etmək üçün Həll tapmaqəlavənin düzgün cavabı tapmasına imkan verəcək bir sıra tələblər, qaydalar və məhdudiyyətlər müəyyən etməliyik.
Bizim qaydalarımız
Əsas tələbimiz iki qrup arasındakı fərqi minimuma endirməkdir. Bizim nümunəmizdə o, G11 xanasındadır - B qrupu mənfi qrup A. Biz G11 xanasındakı dəyərin mümkün qədər kiçik, lakin 0-dan böyük və ya ona bərabər olmasını istəyirik.
Biz həmçinin bilirik ki, bir maddə A və ya B qrupunda ola bilər və o, kəsr ola bilməz. Beləliklə, hər bir element üçün iki məhdudiyyətimiz var:
Birincisi: Sütundakı elementin dəyəri Alt xətt birinə bərabər olmalıdır.
İkincisi: Qruplardakı elementlərin dəyərləri tam ədədlər olmalıdır.
Elementlərin ümumi sayının 8 olduğunu da bilirik ki, bu da başqa bir məhdudiyyətdir. Bu məhdudiyyətlərdən necə istifadə edəcəyimizi növbəti hissədə müzakirə edəcəyik.
Həll tapın dialoq qutusu
Bu bölmə əlavə pəncərəni təsvir edir Həll tapmaq və problemi müəyyən etmək üçün istifadə olunur.
Boş Həll Axtarış pəncərəsi
Tamamlanmış Həll Axtarış pəncərəsi
Məqsəd funksiyasını optimallaşdırın
Bu, problemi həll etməyə çalışdığımız hədəf hüceyrədir. Hədəf hüceyrəmiz G11 - Qrup Fərqidir.
Kimə
Burada məqsəd funksiyasından hansı nəticələri əldə etmək istədiyimizi göstəririk.
Hər iki qrupun cəminin uyğun olmasını istəyirik, yəni. belə ki, məbləğlər arasındakı fərq 0-dır. Bu qəribə görünə bilər, lakin fərqi minimuma endirməyə ehtiyac yoxdur, çünki bu, bütün elementləri A qrupuna daxil edəcək və bu da G11 xanasının dəyərinin sıfırdan az olmasına səbəb olacaq.
Məhdudiyyət tətbiq etməyin başqa bir yolu G11-ə dəyişdirməkdir =ABS(G10-F10). Bu halda markeri təyin edə bilərik minimum, məqsəd funksiyasına nail olunması nəticəsində.
Ancaq hələlik = düsturu üzərində dayanacağıq G10-F10 və markeri 0-a bərabər qiymətə təyin edin.
Dəyişən Hüceyrələrin dəyişdirilməsi
Dəyişdirilmiş hüceyrələr, əlavənin problemi həll etmək üçün dəyişdirməyə çalışacağı hüceyrələrdir. Bizim vəziyyətimizdə bu, bir elementi müəyyən bir qrupa bağlayır: $ C$2:$9 D$.
Məhdudiyyətlərə tabedir
Məhdudiyyətlər problemin mümkün həll yollarını məhdudlaşdıran qaydalardır.
Siyahımıza bir neçə məhdudiyyət əlavə etməliyik:
- Sütunda Cəmi hər bir element 1-ə bərabər olmalıdır
- Qrup elementləri tam ədəd olmalıdır
- Sütun dəyərlərinin cəmi Cəmi 8 olmalıdır
Məhdudiyyətlər tətbiq etmək üçün düyməni basın əlavə et
edə bilərsən Dəyişmək və ya Sil müəyyən bir məhdudiyyət seçərək və dialoq qutusunda müvafiq düymələri klikləməklə səhv etmisinizsə, məhdudiyyət.
Həll axtarış parametrlərini yükləyin/saxlayın
Həll axtarış xidməti əlavə parametrləri saxlamağa və yükləməyə imkan verir. Pəncərədə bunun üçün bir düymə var. Yüklə/Saxla. Model parametrləri əvvəllər qeyd etdiyiniz diapazonda saxlanılır. Bu yanaşma parametrləri tez bir zamanda konfiqurasiya etməyə və dəyişdirməyə imkan verir Həll tapmaq.
Excel-də optimal həll üçün axtarışın aparılması
Xəbərdarlıq!!! Solver əlavəsi mürəkkəb hesablama əlavəsidir, ona görə də onu işə salmazdan əvvəl iş kitabınızı yadda saxlayın.
Modeli işə salmadan əvvəl, xidmətin düzgün işləyəcəyinə əmin olmaq üçün daha bir neçə parametr təyin etməlisiniz. Əsas dialoq qutusunda sahənin yanında markerin olduğundan əmin olun Məhdudiyyətsiz dəyişənləri mənfi olmayan edin. Eyni pəncərədə düyməni basın Seçimlər.
Zaman zaman dəyişdirilməli olan iki parametr:
Məhdud dəqiqlik: 0-dan 1-ə qədər dəyər, burada nömrə nə qədər yüksək olarsa, məhdudiyyət bir o qədər çox olar
Tam ədəd optimallığı: məhdudiyyətin tam ədəddən nə qədər uzaq olmaq hüququna malik olduğunu göstərir.
Modelin icrası
Əlavəni işə salmaq üçün düyməni basın Bir həll tapınəsas pəncərədə.
Vəziyyət çubuğunda siz əlavənin daxili işini göstərəcək bir sıra statik məlumatları görəcəksiniz. Onlar adətən tez dəyişir və oxunması çətindir. Model mürəkkəbdirsə, o zaman iş bir müddət dayana bilər, adətən bu problemlərdən öz-özünə bərpa olunur;
sonra Həll tapmaqöz işini bitirəcək, Excel dialoq qutusu göstərəcək Həll axtarış nəticələri bəzi məlumatlarla. Diqqət etməli olduğunuz ilk şey yazıdır Həll yolu icazə verilən sapma daxilində tapıldı. Həll tapılarsa, iş kitabının xanaları təklif olunan həll ilə dəyişəcək.
İndi seçmək üçün 4 seçiminiz var:
— Orijinal dəyərləri bərpa edin
Hesabatı işə salın
Siz hesabatlar siyahısından mövcud olanları seçərək hesabat yarada bilərsiniz. Yeni vərəq yaradılacaq Nəticələrin hesabatı 1.
Nəzərə alın ki, təyin etdiyiniz məhdudiyyətlərdən asılı olaraq müxtəlif hesabatlar mövcud olacaq.
Skripti yadda saxla
Model ssenarinizin adını daxil etməlisiniz və düyməni basın OK.
Bütün skriptlər mövcuddur Skript meneceri, tabda olan Data qrupda Data ilə işləmək -> What-If Analysis -> Ssenari Meneceri.
İstəsək də, istəməsək də dünya inanılmaz sürətlə dəyişir. Bu həqiqət xüsusilə PC istifadəçilərinə yaxşı məlumdur. Axı proqram təminatı inanılmaz tezliklə dəyişikliklər və yeniləmələr. Xoşbəxtlikdən, ofis dəstləri bu qədər əziyyət çəkmir, lakin bəzi istisnalar var. Ofis proqram paketlərinin əhəmiyyəti nədir? İstənilən ofis proqramıdır lazımlı alət, verilənlər bazası ilə işləmək üçün yaradılmışdır. Bu sistemdə köməkçi elementlərin sayı getdikcə artır.
Bu cür proqramların yalnız yeni versiyalarında görünən vizuallaşdırma vasitələrinin köməyi ilə işləmək xeyli asanlaşdı. Yeni axtarış filtri sayəsində iş xeyli sürətlənib. Və Microsoft Excel 2010 özü daha sürətli işləyir. Amma görünən odur ki, bu yaxınlarda ofis işçiləri Office 2007 ilə işləməyin incəliklərini mənimsəyirdilər. Amma birdən Office 2010 təqdimatı oldu ki, bu da bədbəxt istifadəçiləri daha da çətinə saldı. Məsələn, Microsoft Excel 2010-da “həll üçün axtarış”.
Bu əlavə yalnız faydalı deyil, həm də elektron cədvəl redaktoru ilə işləməyi daha məhsuldar etməyə imkan verir və bununla da çox sayda mürəkkəb problemləri həll etməyə imkan verir. Bu gün bir çox şirkətlər üçün aktual olan optimallaşdırma baxımından xüsusilə aktualdır. Bəs niyə Microsoft Excel 2010? Bu versiyada xüsusi olaraq Excel haqqında danışsaq, onda əhəmiyyətli dəyişikliklər oldu. Beləliklə, məsələn, düzəldildi çox sayda düsturlarda səhvlər, buna görə proqramın əvvəlki versiyalarında hesablamalarda səhvlər olduqca tez-tez baş verirdi. Ancaq ən kiçik səhv hesablama bəzən olduqca xoşagəlməz nəticələrə səbəb ola bilər.
Qrafik və diaqramları cədvələ daxil etməzdən əvvəl onlara baxmağa imkan verən lent interfeysindən istifadə istifadəçilər üçün mürəkkəb, peşəkar sənədlər hazırlamağı asanlaşdırır. Həmçinin daxildir yeni versiya Redaktor iqtisadçılar və mühasiblər üçün çox faydalı ola biləcək belə yeni düstur növlərini daxil etmişdir. Bu vəziyyət Microsoft-un korporativ istifadəçilərə diqqətini vurğulayır. Aşağıda təsvir olunan bütün vəziyyətlərin onlar üçün xarakterik olduğunu nəzərə alsaq, bunda təəccüblü bir şey yoxdur.
Əgər “həll üçün axtarış” əlavəsini istifadə etməmisinizsə, onu ayrıca quraşdıra bilərsiniz. Onu necə quraşdırmaq olar? Bu olduqca asanlıqla edilir. Excel cədvəl redaktoru 2003 və ya daha köhnəsini istifadə edirsinizsə, bu əməliyyatı yerinə yetirmək üçün "Alətlər" maddəsinə keçməlisiniz və orada "Əlavələr" seçin. Daha müasir versiyadan danışırıqsa, "həll axtarışını" harada axtarmaq lazımdır? Excel 2007-dən istifadə edirsinizsə, o zaman "Məlumatlar" sekmesinde "həll üçün axtarış" düyməsini tapa bilərsiniz. Bununla necə işləmək olar? Bəlkə də bütün bu izahatlar bir az uzun görünə bilər, lakin bu əlavə olduqca məntiqli işləyir. Onu mənimsəmək üçün kompüter dahisi olmaq lazım deyil. Onun istifadə prinsipini tam başa düşmək üçün sadə bir nümunəni nəzərdən keçirək.
Excel 2010-da "həll axtarışı" necə işləyir?
Misal: sizə bir təşkilatda bonusların paylanması tapşırığı verilir. Həllini sadələşdirmək üçün fərz edək ki, bonusu bütün filial işçiləri arasında paylamalısınız. Premium büdcə - 100.000 rubl. Mükafat hər bir işçinin əmək haqqına mütənasib olaraq bölünə bilər. Haradan başlamaq lazımdır? Hər şeydən əvvəl, bir cədvəl hazırlamalı, hamısını daxil etməlisiniz zəruri məlumatlar və formul ifadələri. Nəticə kimi ümumi mükafat məbləği nəzərə alınacaq. Hədəf hüceyrənin (məsələn, C8) dəyişdiriləcək bölmə ilə əlaqəli olduğunu nəzərə almağa dəyər (məsələn, E2).
C2-C7 diapazonunda hər bir işçi üçün bonus ödənişinin məbləğini hesablaya biləcəyiniz əlavə düsturlar ola bilər. Bundan sonra "həll üçün axtarış" əlavəsini işə salmalısınız. Sonra açılan pəncərədə tələb olunan dəyərlər təyin olunur. Buna xüsusi diqqət yetirilməlidir görünüş windows versiyalar arasında çox fərqli ola bilər ofis dəsti. Buna görə də, bu vəziyyətdə bunu özünüz həll etməli olacaqsınız. Ancaq fundamental fərqlər yoxdur, buna görə də öyrənmək çox vaxt aparmayacaq.
Dialoq qutusunda hansı seçimlər var?
İşinizi asanlaşdırmaq üçün, ümumiyyətlə müəyyən bir əməliyyat diapazonunda mövcud olan dəyərlər haqqında bilməlisiniz. Hər şeydən əvvəl, hədəf hüceyrə. Nəzərə alın ki, birdən çox məlumat daxiletmə sahəsindən istifadə edə bilən digər əməliyyatlardan fərqli olaraq, burada yalnız bir ola bilər. Bundan əlavə, bir neçə optimallaşdırma variantının ola biləcəyini nəzərə almağa dəyər. Minimum və maksimum mümkün ümumi dəyərə xüsusi diqqət yetirilməlidir. Xüsusi nəticəyə də diqqət yetirin. Əgər sonuncu seçimə ehtiyacınız varsa, o zaman üstünlük verilən nəticə giriş sahəsində dəqiq göstərilməlidir. Onu da nəzərə almaq lazımdır ki, istər fərdi sahələr, istərsə də diapazon dəyişən xanalar kimi çıxış edə bilər. Məhz diapazon üçün proqram ilkin verilənlərlə müqayisə edərək yekun dəyəri alır.
Məhdudiyyətlər necə əlavə olunur?
Proqrama bəzi məhdudiyyətlər əlavə etmək lazımdırsa, "Əlavə et" düyməsini istifadə etməlisiniz. Aşağıdakı məqamı nəzərə almaq vacibdir: bu cür dəyərləri təyin edərkən son dərəcə diqqətli olmaq lazımdır. Excel-də "həll axtarışı" əlavəsi olduqca vacib əməliyyatlarda istifadə edildiyi üçün nəticədə ən düzgün dəyərləri əldə etmək vacibdir. Nəticələrin özləri məhdudiyyətlərdən asılı olacaq. Siz həm fərdi hüceyrələr, həm də bütün diapazonlar üçün məhdudiyyətlər təyin edə bilərsiniz.
Bu halda düsturların və simvolların hansı variantlarından istifadə etmək olar? Aşağıdakı işarələrdən istifadə edilə bilər: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».
Proqramın köhnə versiyalarında bu seçimi haradan tapa bilərsiniz? Excel 2007 və daha köhnə versiyalardan istifadə edirsinizsə, bu seçimə "Seçimlər" düyməsini klikləməklə daxil ola bilərsiniz. Burada "Həll Axtarış Seçimləri" maddəsini görə bilərsiniz.
Bitmiş nəticəni axtarın
Hazır həlli axtarmaq üçün "Çalış" düyməsini sıxmalısınız. Nəticədə, Həll Axtarış Nəticələri dialoq qutusu görünür. Yekun cavab sizi qane edirsə, sadəcə “Ok” düyməsini sıxmağınız kifayətdir. Nəticədə bəyəndiyiniz cavab cədvəldə qeyd olunacaq. Yaranan dəyər sizin fikrinizə uyğun gəlmirsə, "Ləğv et" düyməsini sıxmalısınız. Cədvəl sonda orijinal dəyərinə qayıdacaq. Siz optimal həlli axtarmağa davam edə bilərsiniz. Mənbə məlumatını dəyişdirmisinizsə, bu qərar yenidən yerinə yetirilməli olacaq.
Excel-də “həll axtarışı” əlavəsini harada istifadə etmək olar?
Başqa bir nümunəyə nəzər salın - xərclərin minimuma endirilməsi. Yuxarıda qeyd edildiyi kimi, bu funksiya istehsal proseslərini optimallaşdırmaq üçün istifadə edilə bilər. Az mərtəbəli tikinti ilə məşğul olan bir şirkətin xərclərini necə azalda biləcəyimizi görək. Tutaq ki, bizim bir təşkilatımız və tikinti materialları ilə təmin edən üç təchizatçımız var. Tikinti xərcləri obyektin dəyərinə daxil ediləcək, ona görə də işi daha az başa gələcək təchizatçı seçmək şirkətin maraqlarına uyğundur.
MS Excel proqramında “həll axtarışına” hansı məlumatları daxil etmək lazımdır? Tikinti materiallarının xərclərini, tikinti yerində onlara olan ehtiyacı və tikinti materiallarının daşınması xərclərini göstərmək lazımdır. Hər bir “Təchizatçı-Alıcı” cütü nəzərə alınmalıdır. Hədəf xana bütün nəqliyyat xərclərinin cəmini göstərməlidir. Hər şey düzgün aparılırsa, "həll axtarışı" funksiyası mümkün olan ən yüksək gəlir gətirəcək ən sərfəli strategiyanı yaratmağa imkan verəcəkdir.
Microsoft Excel Əlavəsi "Həll Axtarışı" həllər tapmaq üçün güclü vasitədir və optimallaşdırma problemlərinin həllində istifadə olunur. Həll axtarış proseduru hədəf hüceyrə adlanan xanada olan düsturun optimal dəyərini tapmağa imkan verir. Prosedura hədəf hüceyrədəki formulla birbaşa və ya dolayı əlaqəsi olan bir qrup hüceyrə üzərində işləyir.
Əlavənin qısa təsviri
Düsturdan istifadə edərək müəyyən bir nəticə əldə etmək üçün prosedur təsir edən hüceyrələrdəki dəyərləri dəyişdirir. Dəyər məhdudiyyətləri modeldə istifadə olunan dəyərlərin diapazonunu azaltmaq üçün istifadə olunur. Solution Finder əlavəsi standart Microsoft Office Excel əlavəsidir və siz ümumi olaraq Microsoft Office-i və ya xüsusilə Microsoft Excel-i quraşdırdığınız zaman dərhal əlçatan olur.
Əlavəni necə quraşdırmaq olar?
Find Solution əlavəsi iki şəkildə quraşdırıla bilər. "Solution Search" və "Analysis Package" kimi standart əlavələr MS Office və ya MS Excel ilə birlikdə quraşdırılır. İlkin quraşdırma zamanı standart əlavə quraşdırılmayıbsa, quraşdırma prosesini yenidən işə salmalısınız. Nümunə olaraq Microsoft Office 2010-dan istifadə edərək "Həll üçün axtarış" əlavəsinin quraşdırılmasına baxaq 2003 və 2007-ci versiyalarda hər şey eyni şəkildə edilir.
Beləliklə, MS Office 2010 proqram paketi ilə quraşdırma diskini işə salın və "Kompanentləri əlavə edin və ya silin" seçimini seçin.
Sonra, "Davam et" düyməsini basın, quraşdırma parametrlərində Microsoft Excel proqramını tapırıq, bu proqramın komponentlərində "Əlavələr" bölməsini tapırıq, "Həll Axtarışı" əlavəsini seçin və "Çalış" seçin. kompüterimdən” seçimi.
Yenə də "Davam et" düyməsini basın və əlavənin quraşdırılmasını gözləyin.
Əlavəni necə bağlamaq olar?
İstifadə etməzdən əvvəl, ilk növbədə Əlavələr dialoq qutusunda mövcud əlavələr siyahısında adının qarşısındakı qutuyu qeyd edərək əlavəni aktivləşdirməlisiniz.
Bu pəncərənin işə salınması proqramın versiyasından asılı olaraq bir qədər dəyişir. Bu ayrı bir məqalədə ətraflı yazılmışdır " Excel 2003/2007/2010 üçün əlavəni necə quraşdırmaq olar? " Excel proqramının üç versiyasının hər biri üçün ekran görüntüləri ilə, ona görə də təkrarlamayacağam. Bəli, bu əlavənin quraşdırılmasının ikinci üsulu haqqında bir neçə söz əlavə edəcəyəm. İnternetdə adı olan bir fayl tapa bilərsiniz Solver.xla(bu, “Həll üçün axtarış” əlavəsidir) və yuxarıdakı linkdəki təsvirə uyğun olaraq quraşdırın.
Baxış
