Išspręskite funkciją „Excel“. Įgalinti, naudoti atvejį su ekrano kopijomis

„Sprendimo paieška“ – tai Excel priedas, per kurį pagal nurodytus apribojimus galima pasirinkti geriausią problemų sprendimą. Ši funkcija leidžia planuoti darbuotojus, paskirstyti išlaidas ar investicijas. Žinodami, kaip veikia ši funkcija, sutaupysite laiko ir pastangų.

Kas yra sprendimų paieška

Kartu su įvairiomis kitomis „Excel“ parinktimis yra viena mažiau populiari, bet itin reikalinga funkcija „Ieškoti sprendimo“. Nepaisant to, kad jį rasti nėra lengva, jo pažinimas ir naudojimas padeda išspręsti daugelį problemų. Parinktis apdoroja duomenis ir pateikia optimalų sprendimą iš leidžiamų. Straipsnyje aprašoma, kaip tiesiogiai veikia Sprendimo paieška.

Kaip įjungti funkciją „Ieškoti sprendimo“.

Nepaisant efektyvumo, nagrinėjama parinktis nėra gerai matomoje įrankių juostos ar kontekstinio meniu vietoje. Dauguma „Excel“ dirbančių vartotojų nežino apie jos buvimą. Pagal numatytuosius nustatymus ši funkcija išjungta, norėdami ją parodyti, atlikite šiuos veiksmus:

1. Atidarykite „Failas“ spustelėdami atitinkamą pavadinimą.
2. Spustelėkite skyrių „Nustatymai“.
3. Tada pasirinkite poskyrį „Priedai“. Čia bus rodomi visi programos priedai, apačioje pasirodys užrašas „Valdymas“. Dešinėje jo pusėje bus iššokantis meniu, kuriame turėtumėte pasirinkti „Excel“ priedai. Tada spustelėkite „Eiti“.
   

   

4. Monitoriuje bus rodomas papildomas langas „Priedai“. Pažymėkite langelį šalia norimos funkcijos ir spustelėkite Gerai.
5. Norima funkcija bus rodoma juostelėje, esančioje skilties „Duomenys“ dešinėje.

Apie modelius

Ši informacija bus itin naudinga tiems, kurie dar tik susipažįsta su „optimizavimo modelio“ sąvoka. Prieš naudojant „Sprendimo paiešką“, rekomenduojama išstudijuoti medžiagas apie statybos modelių metodus:

• svarstomas variantas leis identifikuoti geriausią būdą paskirstyti lėšas investicijoms, patalpų krovimui, prekių tiekimui ar kitiems veiksmams, kur reikia rasti geriausią sprendimą.
• „Optimalus metodas“ tokioje situacijoje reikštų: didinti pajamas, mažinti išlaidas, gerinti kokybę ir t.t.

Tipiškos optimizavimo užduotys:

• Gamybos plano nustatymas, kurio metu pelnas pardavus išleistas prekes bus maksimalus.
• Transportavimo žemėlapių nustatymas, kurio metu minimalizuojamos transportavimo išlaidos.
• Ieškokite kelių mašinų paskirstymo įvairiems darbams, kad sumažėtų gamybos sąnaudos.
• Trumpiausio darbo atlikimo laiko nustatymas.

Svarbu! Norint formalizuoti užduotį, būtina sukurti modelį, atspindintį pagrindinius dalykinės srities parametrus. Programoje „Excel“ modelis yra formulių, kuriose naudojami kintamieji, rinkinys. Svarstomas variantas ieško tokių rodiklių, kad tikslo funkcija būtų didesnė (mažesnė) arba lygi nurodytai reikšmei.

Parengiamasis etapas

Prieš įtraukdami funkciją ant juostelės, turite suprasti, kaip ši parinktis veikia. Pavyzdžiui, yra lentelėje nurodyta informacija apie prekių pardavimą. Užduotis – kiekvienai prekei priskirti nuolaidą, kuri būtų 4.5 milijono rublių. Parametras rodomas langelyje, vadinamame taikiniu. Pagal jį apskaičiuojami kiti parametrai.

Mūsų užduotis bus apskaičiuoti nuolaidą, iš kurios padauginamos sumos už įvairių prekių pardavimą. Šie 2 elementai yra sujungti formule, parašyta taip: =D13*$G$2. Kur D13 parašytas bendras diegimo kiekis, o $G$2 yra norimo elemento adresas.

Funkcijos naudojimas ir nustatymas

Kai formulė bus paruošta, turite tiesiogiai naudoti pačią funkciją:

1. Turite pereiti į skyrių „Duomenys“ ir spustelėti „Ieškoti sprendimo“.

2. Atsidarys „Parinktys“, kur bus nustatyti reikalingi nustatymai. Eilutėje „Optimizuoti tikslo funkciją:“ reikia nurodyti langelį, kuriame rodoma nuolaidų suma. Galima koordinates užsirašyti pačiam arba pasirinkti iš dokumento.

3. Tada turite pereiti prie kitų parametrų nustatymų. Skiltyje „Kam:“ galima nustatyti maksimalias ir minimalias ribas arba tikslų skaičių.

4. Tada užpildomas laukas „Kintamųjų reikšmių keitimas:“. Čia įvedami norimos ląstelės duomenys, kuriuose yra konkreti reikšmė. Koordinatės registruojamos savarankiškai arba paspaudžiamas atitinkamas langelis dokumente.

5. Tada redaguojamas skirtukas „Pagal apribojimus:“, kuriame nustatomi taikomų duomenų apribojimai. Pavyzdžiui, dešimtainės trupmenos arba neigiami skaičiai neįtraukiami.

6. Po to atsidarys langas, kuriame galėsite pridėti skaičiavimų apribojimus. Pradinėje eilutėje yra langelio arba viso diapazono koordinatės. Vadovaujantis užduoties sąlygomis, nurodomi norimos langelio duomenys, kur rodomas nuolaidos indikatorius. Tada nustatomas palyginimo ženklas. Jis nustatytas kaip „didesnis arba lygus“, kad galutinė reikšmė nebūtų su minuso ženklu. Šioje situacijoje 3 eilutėje nustatytas limitas yra 0. Taip pat galima nustatyti limitą mygtuku „Pridėti“. Tolesni veiksmai yra tokie patys.

7. Atlikus aukščiau nurodytus veiksmus, nustatyta riba rodoma didžiausioje eilutėje. Sąrašas gali būti didelis ir priklausys nuo skaičiavimų sudėtingumo, tačiau konkrečioje situacijoje pakanka 1 sąlygos.

8. Be to, galima pasirinkti kitus išplėstinius nustatymus. Apatinėje dešinėje pusėje yra parinktis „Parinktys“, kuri leidžia tai padaryti.

9. Nustatymuose galite nustatyti „Apribojimų tikslumą“ ir „Sprendimo ribas“. Mūsų situacijoje šių galimybių naudoti nereikia.

10. Atlikus nustatymus, paleidžiama pati funkcija – spustelėkite „Rasti sprendimą“.

11. Programai atlikus reikiamus skaičiavimus ir išduodant galutinius skaičiavimus reikiamose ląstelėse. Tada atsidaro langas su rezultatais, kuriame rezultatai išsaugomi / atšaukiami arba paieškos parametrai sukonfigūruojami pagal naują. Kai duomenys atitinka reikalavimus, rastas sprendimas išsaugomas. Jei iš anksto pažymėsite langelį „Grįžti į sprendimų paieškos parinkčių dialogo langą“, atsidarys langas su funkcijų nustatymais.

12. Yra tikimybė, kad skaičiavimai pasirodė klaidingi arba norint gauti kitus rodiklius, reikia pakeisti pradinius duomenis. Esant tokiai situacijai, turite iš naujo atidaryti nustatymų langą ir dar kartą patikrinti informaciją.
13. Kai duomenys tikslūs, galima naudoti kitą metodą. Norėdami tai padaryti, turite spustelėti esamą parinktį ir iš pasirodžiusio sąrašo pasirinkti tinkamiausią metodą:

• Netiesinių problemų sprendimo radimas naudojant apibendrintą gradientą. Pagal numatytuosius nustatymus ši parinktis naudojama, tačiau galima naudoti ir kitas.
• Tiesinių problemų sprendimų paieška, remiantis simplekso metodu.
• Evoliucinės paieškos naudojimas užduočiai atlikti.

Dėmesio! Nepavykus susidoroti su aukščiau nurodytomis parinktimis, turėtumėte dar kartą patikrinti duomenis nustatymuose, nes tai dažnai yra pagrindinė tokių užduočių klaida.

14. Gavus norimą nuolaidą, belieka ją pritaikyti skaičiuoti nuolaidų sumą kiekvienai prekei. Tam paryškinamas pradinis stulpelio „Nuolaidos suma“ elementas, užrašoma formulė «=D2*$G$2» ir paspauskite „Enter“. Dolerio ženklai dedami taip, kad ištempus formulę į gretimas linijas, G2 nepasikeistų.

15. Dabar bus gauta nuolaidos suma pradinei prekei. Tada reikia perkelti žymeklį virš langelio kampo, kai jis tampa „pliusu“, paspaudžiamas LMB ir formulė ištempiama iki reikiamų linijų.
16. Po to stalas pagaliau bus paruoštas.

Įkelti/išsaugoti paieškos parinktis

Ši parinktis naudinga taikant įvairias apribojimo parinktis.

1. Sprendimo ieškiklio parinkčių meniu spustelėkite Įkelti / išsaugoti.
2. Įveskite modelio srities diapazoną ir spustelėkite Išsaugoti arba Įkelti.

Išsaugant modelį, įvedama nuoroda į 1 tuščio stulpelio langelį, kuriame bus patalpintas optimizavimo modelis. Modelio įkėlimo metu įvedama nuoroda į visą diapazoną, kuriame yra optimizavimo modelis.

Svarbu! Norėdami išsaugoti paskutinius nustatymus meniu Sprendimo parinktys, išsaugoma darbaknygė. Kiekvienas jame esantis lapas turi savo Solver papildinio parinktis. Be to, norint išsaugoti atskiras užduotis, paspaudus mygtuką „Įkelti arba išsaugoti“ galima nustatyti daugiau nei 1 lapo užduotį.

Paprastas Solver naudojimo pavyzdys

Indą reikia prikrauti konteineriais, kad jo masė būtų maksimali. Bako tūris yra 32 kubiniai metrai. m. Užpildyta dėžė sveria 20 kg, jos tūris – 0,15 kub. m. Dėžutė – 80 kg ir 0,5 kub. m. Reikalaujama, kad bendras konteinerių skaičius būtų ne mažesnis kaip 110 vnt. Duomenys organizuojami taip:

Modelio kintamieji pažymėti žalia spalva. Tikslinė funkcija paryškinta raudonai. Apribojimai: pagal mažiausią konteinerių skaičių (110 arba daugiau) ir pagal svorį (=SUMMA (B8:C8,B6:C6) – bendras taros svoris konteineryje.

Pagal analogiją mes atsižvelgiame į bendrą tūrį: =SUMMA (B7:C7,B8:C8). Tokia formulė būtina norint nustatyti viso konteinerių tūrio ribą. Tada per „Sprendimo paiešką“ įvedamos nuorodos į elementus su kintamaisiais, formulėmis ir pačiais rodikliais (arba nuorodomis į konkrečias ląsteles). Žinoma, konteinerių skaičius yra sveikasis skaičius (tai taip pat yra apribojimas). Paspaudžiame „Rasti sprendimą“, ko pasekoje randame tokį konteinerių skaičių, kai bendra masė maksimali ir atsižvelgiama į visus apribojimus.

Ieškodami sprendimo nepavyko rasti sprendimų

Toks pranešimas pasirodo, kai atitinkama funkcija neranda kintamųjų balų derinių, atitinkančių kiekvieną apribojimą. Naudojant Simplex metodą, visiškai įmanoma, kad sprendimo nėra.

Kai naudojamas netiesinių uždavinių sprendimo metodas, visais atvejais pradedant nuo pradinių kintamųjų rodiklių, tai rodo, kad galimas sprendimas yra toli nuo tokių parametrų. Jei paleidžiate funkciją su kitais pradiniais kintamųjų indikatoriais, tikriausiai yra sprendimas.

Pavyzdžiui, naudojant netiesinį metodą, lentelės elementai su kintamaisiais nebuvo užpildyti, o funkcija nerado sprendimų. Tai nereiškia, kad sprendimo nėra. Dabar, atsižvelgiant į tam tikro vertinimo rezultatus, į elementus įvedami kiti duomenys, kurių kintamieji yra artimi gautiesiems.

Bet kurioje situacijoje iš pradžių turėtumėte patikrinti modelį, ar nėra apribojimų konflikto. Dažnai tai yra susiję su netinkamu santykio ar ribojančio rodiklio parinkimu.

Aukščiau pateiktame pavyzdyje didžiausias tūrio indikatorius yra 16 kubinių metrų. m vietoj 32, nes toks apribojimas prieštarauja minimalaus sėdimų vietų skaičiaus rodikliams, nes jis atitiks 16,5 kubinių metrų skaičių. m.

Išvada

Remiantis tuo, „Excel“ parinktis „Ieškoti sprendimo“ padės išspręsti konkrečias problemas, kurias įprastais būdais gana sunku arba neįmanoma išspręsti. Taikant šį metodą sunku tai, kad iš pradžių ši parinktis yra paslėpta, todėl dauguma vartotojų nežino apie jos buvimą. Be to, šią funkciją gana sunku išmokti ir naudoti, tačiau tinkamai ištyrus ji duos didelės naudos ir palengvins skaičiavimus.