Excel Solver-ის დანამატი ასრულებს მათემატიკურ ოპტიმიზაციას. ეს ჩვეულებრივ გამოიყენება კომპლექსური მოდელების მონაცემებთან მოსარგებად ან პრობლემების განმეორებითი გადაწყვეტის მოსაძებნად. მაგალითად, შეიძლება დაგჭირდეთ მრუდის მორგება მონაცემთა ზოგიერთ წერტილში, განტოლების გამოყენებით. ამოხსნას შეუძლია განტოლებაში მოძებნოს ის მუდმივები, რომლებიც საუკეთესოდ შეესაბამება მონაცემებს. კიდევ ერთი პროგრამაა, სადაც რთულია მოდელის გადაწყობა, რათა საჭირო გამომავალი განტოლების საგნად აქციოს.
სად არის Solver Excel-ში?
Solver-ის დანამატი შედის Excel-ში, მაგრამ ის ყოველთვის არ არის ჩატვირთული, როგორც ნაგულისხმევი ინსტალაციის ნაწილი. იმის შესამოწმებლად, ჩატვირთულია თუ არა, აირჩიეთ DATA ჩანართი და მოძებნეთ Solver ხატულა Analysis განყოფილებაში.
თუ ვერ იპოვით Solver-ს DATA ჩანართში, მაშინ დაგჭირდებათ დანამატის ჩატვირთვა:
-
აირჩიეთ FILE ჩანართი და შემდეგ აირჩიეთ ოფციები.
Image -
ოფციები დიალოგურ ფანჯარაში აირჩიეთ Add-Ins მარცხენა მხარეს ჩანართებიდან.
Image -
ფანჯრის ბოლოში აირჩიეთ Excel დანამატები Manage ჩამოსაშლელი მენიუდან და აირჩიეთ გადასვლა…
Image -
მოანიშნეთ მოსანიშნი ველი Solver Add-in-ის გვერდით და აირჩიეთ OK.
Image -
Solver ბრძანება ახლა უნდა გამოჩნდეს DATA ჩანართზე. თქვენ მზად ხართ გამოსაყენებლად.
Image
გამოყენება Excel-ში
დავიწყოთ მარტივი მაგალითით, რათა გავიგოთ რას აკეთებს Solver. წარმოიდგინეთ, რომ გვინდა ვიცოდეთ, რა რადიუსს მისცემს წრეს 50 კვადრატული ერთეულის ფართობი. ჩვენ ვიცით წრის ფართობის განტოლება (A=pi r2). ჩვენ შეგვიძლია, რა თქმა უნდა, გადავაწყოთ ეს განტოლება, რათა მივცეთ რადიუსი, რომელიც საჭიროა მოცემული ფართობისთვის, მაგრამ მაგალითისთვის მოდით ვიფიქროთ, რომ არ ვიცით როგორ გავაკეთოთ ეს.
შექმენით ცხრილი რადიუსით B1-ში და გამოთვალეთ ფართობი B2 განტოლების გამოყენებით =pi()B1^2.
ჩვენ შეგვიძლია ხელით დაარეგულიროთ მნიშვნელობა B1-ში, სანამ B2 არ აჩვენებს მნიშვნელობას, რომელიც საკმარისად ახლოს არის 50-თან. დამოკიდებულია იმაზე, თუ რამდენად ზუსტია ჩვენ უნდა იყოს, ეს შეიძლება იყოს პრაქტიკული მიდგომა. თუმცა, თუ ძალიან ზუსტი უნდა ვიყოთ, საჭირო კორექტირებას დიდი დრო დასჭირდება.სინამდვილეში, ეს არის ის, რასაც Solver აკეთებს. ის არეგულირებს მნიშვნელობებს გარკვეულ უჯრედებში და ამოწმებს მნიშვნელობას სამიზნე უჯრედში:
- აირჩიეთ DATA ჩანართი და Solver, რომ ჩატვირთოთ Solver Parameters დიალოგური ფანჯარა
-
დააყენეთ მიზანი უჯრედი, როგორც ფართობი, B2. ეს არის მნიშვნელობა, რომელიც შემოწმდება სხვა უჯრედების კორექტირებამდე, სანამ ეს არ მიაღწევს სწორ მნიშვნელობას.
Image -
აირჩიეთ ღილაკი მნიშვნელობა: და დააყენეთ მნიშვნელობა 50. ეს არის მნიშვნელობა, რომელსაც B2-მა უნდა მიაღწიოს.
Image -
ველში სათაურით ცვლადი უჯრედების შეცვლით: შეიყვანეთ რადიუსის შემცველი უჯრედი, B1..
Image -
დატოვეთ სხვა ვარიანტები ნაგულისხმევად და აირჩიეთ Solve. ოპტიმიზაცია განხორციელდა, B1-ის მნიშვნელობა დარეგულირდება B2-მდე 50-მდე და გამოჩნდება დიალოგი Solver Results.
Image -
აირჩიეთ OK გამოსავლის შესანარჩუნებლად.
Image
ამ მარტივმა მაგალითმა აჩვენა, თუ როგორ მუშაობს ამომხსნელი. ამ შემთხვევაში, ჩვენ უფრო მარტივად შეგვეძლო გამოსავალი სხვა გზებით მოგვეპოვებინა. შემდეგ ჩვენ განვიხილავთ რამდენიმე მაგალითს, სადაც Solver იძლევა გადაწყვეტილებებს, რომლებიც ძნელია სხვა გზით იპოვოთ.
კომპლექსური მოდელის დაყენება Excel Solver დანამატის გამოყენებით
Excel-ს აქვს ჩაშენებული ფუნქცია ხაზოვანი რეგრესიის შესასრულებლად, სწორი ხაზის მორგება მონაცემთა ნაკრების მეშვეობით. ბევრი ჩვეულებრივი არაწრფივი ფუნქცია შეიძლება იყოს წრფივი, რაც ნიშნავს, რომ წრფივი რეგრესია შეიძლება გამოყენებულ იქნას ისეთი ფუნქციების მოსარგებად, როგორიცაა ექსპონენცია.უფრო რთული ფუნქციებისთვის, Solver შეიძლება გამოყენებულ იქნას "უმცირესი კვადრატების მინიმიზაციის" შესასრულებლად. ამ მაგალითში განვიხილავთ ax^b+cx^d ფორმის განტოლების დაყენებას ქვემოთ მოცემულ მონაცემებთან.
ეს მოიცავს შემდეგ ნაბიჯებს:
- მოაწყეთ მონაცემთა ნაკრები x მნიშვნელობებით A სვეტში და y-მნიშვნელობები სვეტში B.
- შექმენით 4 კოეფიციენტის მნიშვნელობა (a, b, c და d) სადმე ელცხრილზე, მათ შეიძლება მიეცეს თვითნებური საწყისი მნიშვნელობები.
-
შექმენით მორგებული Y მნიშვნელობების სვეტი ax^b+cx^d ფორმის განტოლების გამოყენებით, რომელიც მიუთითებს მე-2 ნაბიჯში შექმნილ კოეფიციენტებზე და A სვეტში x მნიშვნელობებზე. გაითვალისწინეთ, რომ ფორმულის ქვემოთ კოპირებისთვის სვეტში, კოეფიციენტებზე მითითებები უნდა იყოს აბსოლუტური, ხოლო მითითებები x მნიშვნელობებზე უნდა იყოს ფარდობითი.
Image -
მიუხედავად იმისა, რომ არსებითი არ არის, შეგიძლიათ მიიღოთ ვიზუალური მითითება იმის შესახებ, თუ რამდენად კარგად შეესაბამება განტოლება ორივე y სვეტის გამოსახვით x მნიშვნელობებთან ერთ XY სკატერ დიაგრამაზე. აზრი აქვს გამოიყენოს მარკერები ორიგინალური მონაცემების წერტილებისთვის, რადგან ეს არის დისკრეტული მნიშვნელობები ხმაურით და გამოვიყენოთ ხაზი დამონტაჟებული განტოლებისთვის.
Image -
შემდეგ, ჩვენ გვჭირდება გზა, რომ დავადგინოთ განსხვავება მონაცემებსა და ჩვენს დაყენებულ განტოლებას შორის. ამის გაკეთების სტანდარტული გზაა კვადრატული განსხვავებების ჯამის გამოთვლა. მესამე სვეტში, თითოეული სტრიქონისთვის, Y-ის თავდაპირველი მონაცემთა მნიშვნელობა აკლდება დაყენებულ განტოლების მნიშვნელობას და შედეგი კვადრატდება. ასე რომ, D2-ში, მნიშვნელობა მოცემულია =(C2-B2)^2-ით, შემდეგ გამოითვლება ყველა ამ კვადრატული მნიშვნელობის ჯამი. ვინაიდან მნიშვნელობები კვადრატულია, ისინი შეიძლება იყოს მხოლოდ დადებითი.
Image -
ახლა მზად ხართ განახორციელოთ ოპტიმიზაცია Solver-ის გამოყენებით. არსებობს ოთხი კოეფიციენტი, რომლებიც უნდა დარეგულირდეს (a, b, c და d). თქვენ ასევე გაქვთ ერთი ობიექტური მნიშვნელობა მინიმუმამდე, კვადრატული განსხვავებების ჯამი. გაუშვით ამომხსნელი, როგორც ზემოთ, და დააყენეთ ამოხსნის პარამეტრები ამ მნიშვნელობებზე მითითებისთვის, როგორც ნაჩვენებია ქვემოთ.
Image -
მოხსნათ მონიშვნა შეზღუდული ცვლადების არაუარყოფითი აქცია, ეს აიძულებს ყველა კოეფიციენტს მიიღოს დადებითი მნიშვნელობები.
Image -
აირჩიეთ Solve და გადახედეთ შედეგებს. დიაგრამა განახლდება, რაც კარგად მიუთითებს მორგების სიკეთეზე. თუ ამომხსნელი არ იძლევა კარგ მორგებას პირველივე მცდელობისას, შეგიძლიათ სცადოთ მისი ხელახლა გაშვება. თუ მორგება გაუმჯობესდა, სცადეთ ამოხსნა მიმდინარე მნიშვნელობებიდან.წინააღმდეგ შემთხვევაში, შეგიძლიათ სცადოთ ხელით გააუმჯობესოთ მორგება გადაწყვეტამდე.
Image - როდესაც კარგი მორგება მიიღება, შეგიძლიათ გამოხვიდეთ ამომხსნელიდან.
მოდელის განმეორებითი გადაჭრა
ზოგჯერ არის შედარებით მარტივი განტოლება, რომელიც იძლევა გამომავალს გარკვეული შეყვანის თვალსაზრისით. თუმცა, როდესაც ვცდილობთ პრობლემის შებრუნებას, მარტივი გამოსავლის პოვნა შეუძლებელია. მაგალითად, ავტომობილის მიერ მოხმარებული სიმძლავრე დაახლოებით მოცემულია P=av + bv^3, სადაც v არის სიჩქარე, a არის კოეფიციენტი გორვა წინააღმდეგობისთვის და b არის კოეფიციენტი აეროდინამიკური წევა. მიუხედავად იმისა, რომ ეს საკმაოდ მარტივი განტოლებაა, ადვილი არ არის გადაწყობა სიჩქარის განტოლებაზე, რომელსაც ავტომობილი მიაღწევს მოცემული სიმძლავრის შეყვანისთვის. თუმცა, ჩვენ შეგვიძლია გამოვიყენოთ Solver ამ სიჩქარის განმეორებით საპოვნელად. მაგალითად, იპოვეთ სიჩქარე, რომელიც მიღწეულია 740 ვტ სიმძლავრის შეყვანით.
-
შეადგინეთ მარტივი ცხრილი სიჩქარით, a და b კოეფიციენტებით და მათგან გამოთვლილი სიმძლავრით.
Image -
გაუშვით Solver და შეიყვანეთ ძალა, B5, როგორც მიზანი. დააყენეთ ობიექტური მნიშვნელობა 740 და აირჩიეთ სიჩქარე, B2, როგორც ცვლადი უჯრედების შეცვლა. ამოხსნის დასაწყებად აირჩიეთ solve.
Image -
ამომხსნელი არეგულირებს სიჩქარის მნიშვნელობას მანამ, სანამ სიმძლავრე არ მიახლოვდება 740-ს, რაც უზრუნველყოფს ჩვენ საჭირო სიჩქარეს.
Image - მოდელების ამ გზით გადაჭრა ხშირად შეიძლება იყოს უფრო სწრაფი და ნაკლებად შეცდომის მიდრეკილება, ვიდრე რთული მოდელების ინვერსია.
ამოხსნის სხვადასხვა ვარიანტების გაგება შეიძლება საკმაოდ რთული იყოს.თუ გაგიჭირდათ გონივრული გადაწყვეტის მიღება, მაშინ ხშირად სასარგებლოა ცვალებად უჯრედებზე სასაზღვრო პირობების გამოყენება. ეს არის შემზღუდველი მნიშვნელობები, რომელთა მიღმაც ისინი არ უნდა დარეგულირდეს. მაგალითად, წინა მაგალითში სიჩქარე არ უნდა იყოს ნულზე ნაკლები და ასევე შესაძლებელი იქნება ზედა ზღვარის დაყენება. ეს იქნება სიჩქარე, რომელზედაც დარწმუნებული ხართ, რომ მანქანა ვერ ახერხებს უფრო სწრაფად. თუ თქვენ შეგიძლიათ დააყენოთ საზღვრები ცვლადი ცვლადი უჯრედებისთვის, მაშინ ეს ასევე აუმჯობესებს სხვა უფრო მოწინავე ვარიანტებს, როგორიცაა multistart. ეს გაუშვებს სხვადასხვა გადაწყვეტილებებს, დაწყებული ცვლადების სხვადასხვა საწყისი მნიშვნელობებით.
გადაჭრის მეთოდის არჩევა ასევე შეიძლება რთული იყოს. Simplex LP განკუთვნილია მხოლოდ ხაზოვანი მოდელებისთვის, თუ პრობლემა არ არის წრფივი, ის ვერ იქნება შეტყობინებით, რომ ეს პირობა არ დაკმაყოფილდა. დანარჩენი ორი მეთოდი ორივე შეეფერება არაწრფივ მეთოდებს. GRG არაწრფივი ყველაზე სწრაფია, მაგრამ მისი გამოსავალი შეიძლება დიდად იყოს დამოკიდებული საწყის საწყის პირობებზე.მას აქვს მოქნილობა, რომ მას არ სჭირდება ცვლადების საზღვრების დაყენება. ევოლუციური ამომხსნელი ხშირად ყველაზე საიმედოა, მაგრამ ის მოითხოვს ყველა ცვლადს ჰქონდეს ზედა და ქვედა საზღვრები, რაც შეიძლება რთული იყოს წინასწარ შემუშავება.
Excel Solver-ის დანამატი არის ძალიან ძლიერი ინსტრუმენტი, რომელიც შეიძლება გამოყენებულ იქნას მრავალი პრაქტიკული პრობლემის გადასაჭრელად. Excel-ის სიმძლავრეზე სრულად წვდომისთვის, სცადეთ Solver-ის კომბინირება Excel მაკროებთან.
