Sisu
“Otsi lahendust” on Exceli lisandmoodul, mille kaudu on võimalik valida probleemidele parim lahendus lähtuvalt etteantud piirangutest. Funktsioon võimaldab ajastada töötajaid, jaotada kulusid või investeeringuid. Selle funktsiooni toimimise teadmine säästab teie aega ja vaeva.
Mis on lahenduste otsimine
Koos erinevate muude Exceli valikutega on üks vähem populaarne, kuid äärmiselt vajalik funktsioon “Otsi lahendust”. Vaatamata sellele, et selle leidmine pole lihtne, aitab selle tundmaõppimine ja kasutamine paljude probleemide lahendamisel kaasa. Valik töötleb andmeid ja annab lubatutest optimaalse lahenduse. Artiklis kirjeldatakse, kuidas lahenduse otsimine otse toimib.
Kuidas funktsioon „Otsi lahendust” sisse lülitada
Vaatamata tõhususele ei ole kõnealune suvand tööriistaribal ega kontekstimenüüs silmapaistvas kohas. Enamik Excelis töötavaid kasutajaid pole selle olemasolust teadlikud. Vaikimisi on see funktsioon keelatud, selle kuvamiseks tehke järgmised toimingud:
- Avage "Fail", klõpsates sobival nimel.
- Klõpsake jaotist "Seaded".
- Seejärel valige alamjaotis "Lisandmoodulid". Siin kuvatakse kõik programmi lisandmoodulid, allpool kuvatakse kiri "Haldamine". Selle paremal küljel on hüpikmenüü, kus peaksite valima "Exceli lisandmoodulid". Seejärel klõpsake nuppu "Mine".
1 - Monitoril kuvatakse täiendav aken "Lisandmoodulid". Märkige soovitud funktsiooni kõrval olev ruut ja klõpsake nuppu OK.
- Soovitud funktsioon kuvatakse jaotisest "Andmed" paremal asuval lindil.
Mudelite kohta
See teave on äärmiselt kasulik neile, kes alles tutvuvad optimeerimismudeli kontseptsiooniga. Enne "Otsi lahendust" kasutamist on soovitatav uurida materjale mudelite ehitamise meetodite kohta:
- vaadeldav variant võimaldab välja selgitada parima meetodi raha eraldamiseks investeeringuteks, ruumide laadimiseks, kauba tarnimiseks või muudeks toiminguteks, kus on vaja leida parim lahendus.
- “Optimaalne meetod” tähendaks sellises olukorras: sissetulekute suurendamist, kulude vähendamist, kvaliteedi parandamist jne.
Tüüpilised optimeerimisülesanded:
- Tootmisplaani koostamine, mille käigus saavutatakse maksimaalne kasum vabastatud kauba müügist.
- Transpordikaartide määramine, mille käigus transpordikulud on viidud miinimumini.
- Otsige mitme masina jaotust erinevat tüüpi tööde jaoks, et tootmiskulud väheneksid.
- Lühima töö lõpetamise aja määramine.
Tähtis! Ülesande vormistamiseks on vaja luua mudel, mis kajastab ainevaldkonna põhiparameetreid. Excelis on mudel valemite kogum, mis kasutab muutujaid. Vaadeldav variant otsib selliseid näitajaid, et sihtfunktsioon on määratud väärtusega suurem (vähem) või sellega võrdne.
Ettevalmistav etapp
Enne funktsiooni lindile asetamist peate mõistma, kuidas see valik töötab. Näiteks on teave tabelis märgitud kaupade müügi kohta. Ülesanne on määrata igale kaubale allahindlus, mis oleks 4.5 miljonit rubla. Parameeter kuvatakse lahtris nimega siht. Selle põhjal arvutatakse muud parameetrid.
Meie ülesandeks saab olema välja arvutada allahindlus, millega korrutatakse erinevate toodete müügisummad. Need 2 elementi on ühendatud valemiga, mis on kirjutatud järgmiselt: =D13*$G$2. Kus D13-s on kirjas teostuse kogukogus ja $G$2 on soovitud elemendi aadress.
Funktsiooni kasutamine ja seadistamine
Kui valem on valmis, peate funktsiooni ise otse kasutama:
- Peate lülituma jaotisesse "Andmed" ja klõpsake "Otsi lahendust".
- Avaneb "Valikud", kus on määratud vajalikud sätted. Real “Optimeeri sihtfunktsiooni:” tuleks määrata lahter, kus kuvatakse allahindluste summa. Koordinaadid on võimalik ise ette kirjutada või valida dokumendist.
- Järgmisena peate minema muude parameetrite sätetesse. Jaotises “To:” on võimalik määrata maksimum- ja miinimumlimiiti või täpset arvu.
- Seejärel täidetakse väli "Muutujate väärtuste muutmine:". Siin sisestatakse soovitud lahtri andmed, mis sisaldavad konkreetset väärtust. Koordinaadid registreeritakse iseseisvalt või klõpsatakse dokumendis vastavat lahtrit.
- Seejärel redigeeritakse vahekaarti “Vastavalt piirangutele:”, kus määratakse piirangud rakendatavatele andmetele. Näiteks kümnendmurrud või negatiivsed arvud on välistatud.
- Pärast seda avaneb aken, mis võimaldab arvutustes piiranguid lisada. Algrida sisaldab lahtri või terve vahemiku koordinaate. Järgides ülesande tingimusi, näidatakse soovitud lahtri andmed, kus kuvatakse allahindluse indikaator. Seejärel määratakse võrdlusmärk. See on seatud väärtusele "suurem või võrdne", et lõppväärtus ei oleks miinusmärgiga. Real 3 seatud "Limit" on selles olukorras 0. Limiit on võimalik määrata ka nupuga “Lisa”. Järgmised sammud on samad.
- Kui ülaltoodud sammud on tehtud, kuvatakse seatud limiit suurimale reale. Loetelu võib olla suur ja sõltub arvutuste keerukusest, kuid konkreetses olukorras piisab ühest tingimusest.
- Lisaks on võimalik valida muid täpsemaid seadeid. Paremas alanurgas on valik "Valikud", mis võimaldab teil seda teha.
- Seadetes saate määrata "Piiramise täpsus" ja "Lahenduspiirangud". Meie olukorras pole neid võimalusi vaja kasutada.
- Kui seaded on lõpetatud, käivitub funktsioon ise – klõpsake nuppu "Leia lahendus".
- Pärast seda, kui programm sooritab vajalikud arvutused ja väljastab lõplikud arvutused vajalikes lahtrites. Seejärel avaneb tulemustega aken, kus tulemused salvestatakse/tühistatakse või konfigureeritakse otsinguparameetrid uue järgi. Kui andmed vastavad nõuetele, salvestatakse leitud lahendus. Kui märgite eelnevalt kasti "Tagasi lahendusotsingu valikute dialoogiboksi", avaneb funktsiooni seadetega aken.
- On võimalus, et arvutused osutusid ekslikeks või on muude näitajate saamiseks vaja lähteandmeid muuta. Sellises olukorras peate seadete akna uuesti avama ja teavet uuesti kontrollima.
- Kui andmed on täpsed, võib kasutada alternatiivset meetodit. Nendel eesmärkidel peate klõpsama praegusel valikul ja valima kuvatavast loendist kõige sobivama meetodi:
- Lahenduse leidmine mittelineaarsete probleemide jaoks üldistatud gradiendi abil. Vaikimisi on see valik kasutusel, kuid võimalik on kasutada ka teisi.
- Lineaarülesannete lahenduste leidmine simpleksmeetodil.
- Evolutsioonilise otsingu kasutamine ülesande täitmiseks.
Tähelepanu! Kui ülaltoodud valikud ei saanud ülesandega hakkama, peaksite seadetes olevaid andmeid uuesti kontrollima, kuna see on sageli selliste ülesannete peamine viga.
- Kui soovitud allahindlus on laekunud, jääb üle seda rakendada, et arvutada iga kauba allahindluste summa. Selleks tõstetakse esile veeru “Allahindluse summa” esialgne element, kirjutatakse valem «=D2*$G$2» ja vajutage "Enter". Dollarimärgid pannakse maha nii, et valemi venitamisel külgnevatele joontele G2 ei muutu.
- Nüüd saadakse esialgse kauba allahindlussumma. Seejärel peaksite kursori viima üle lahtri nurga, kui see muutub "plussiks", vajutatakse LMB-d ja valem venitatakse vajalikele joontele.
- Peale seda saab lõpuks laud valmis.
Laadi/salvesta otsingusuvandid
See valik on kasulik erinevate piiranguvalikute rakendamisel.
- Menüüs Solution Finder Options klõpsake nuppu Laadi/salvesta.
- Sisestage mudeliala vahemik ja klõpsake nuppu Salvesta või Laadi.
Mudeli salvestamisel sisestatakse viide tühja veeru 1 lahtrile, kuhu optimeerimismudel paigutatakse. Mudeli laadimise ajal sisestatakse viide kogu optimeerimismudelit sisaldavale vahemikule.
Tähtis! Viimaste sätete salvestamiseks menüüs Lahendussuvandid salvestatakse töövihik. Igal selles oleval lehel on oma Solveri lisandmooduli valikud. Lisaks on võimalik üksikute ülesannete salvestamiseks määrata lehele rohkem kui 1 ülesanne, klõpsates nuppu “Laadi või Salvesta”.
Lihtne näide Solveri kasutamisest
Konteiner on vaja laadida konteineritega nii, et selle mass oleks maksimaalne. Paagi maht on 32 kuupmeetrit. m. Täidetud kasti kaal on 20 kg, selle maht on 0,15 kuupmeetrit. m. Kast – 80 kg ja 0,5 tk. m. Nõutav konteinerite koguarv on vähemalt 110 tk. Andmed on korraldatud järgmiselt:
Mudeli muutujad on tähistatud rohelisega. Eesmärgi funktsioon on punasega esile tõstetud. Piirangud: väikseima mahutite arvu järgi (110 või rohkem) ja kaalu järgi (=SUMMA(B8:C8,B6:C6) – kogu taara mass mahutis.
Analoogia põhjal käsitleme kogumahtu: =SUMMA(B7:C7,B8:C8). Selline valem on vajalik mahutite kogumahu piiramiseks. Seejärel sisestatakse läbi “Otsi lahenduse” lingid muutujate, valemite ja indikaatoritega elementide juurde (või lingid konkreetsetele lahtritele). Muidugi on konteinerite arv täisarv (see on ka piirang). Vajutame “Leia lahendus”, mille tulemusena leiame sellise hulga konteinereid, kui kogumass on maksimaalne ja kõik piirangud on arvesse võetud.
Lahenduse otsimine ei leidnud lahendusi
Selline teade ilmub siis, kui kõnealune funktsioon ei ole leidnud muutujate skooride kombinatsioone, mis vastavad igale piirangule. Simplexi meetodi kasutamisel on täiesti võimalik, et lahendust polegi.
Kui kasutatakse mittelineaarsete ülesannete lahendamise meetodit, siis kõigil juhtudel alates muutujate algnäitajatest, näitab see, et võimalik lahendus on sellistest parameetritest kaugel. Kui käivitate funktsiooni muutujate muude algnäitajatega, on tõenäoliselt lahendus olemas.
Näiteks mittelineaarse meetodi kasutamisel jäid tabeli elemendid muutujatega täitmata ning funktsioon ei leidnud lahendusi. See ei tähenda, et lahendust poleks. Nüüd, võttes arvesse teatud hindamise tulemusi, sisestatakse elementidesse muud andmed, mille muutujad on lähedased saadud muutujatele.
Igas olukorras peaksite kõigepealt uurima mudelit piirangute konflikti puudumise suhtes. Sageli on see seotud suhte või piirava indikaatori ebaõige valikuga.
Ülaltoodud näites on maksimaalne mahunäidik 16 kuupmeetrit. m 32 asemel, kuna selline piirang on vastuolus minimaalse istekohtade arvu näitajatega, kuna see vastab 16,5 kuupmeetri arvule. m.
Järeldus
Sellest lähtuvalt aitab Exceli valik “Otsi lahendust” lahendada konkreetseid probleeme, mida on tavapärastel viisidel üsna raske või võimatu lahendada. Selle meetodi rakendamise raskus seisneb selles, et algselt on see valik peidetud, mistõttu enamik kasutajaid pole selle olemasolust teadlikud. Lisaks on funktsiooni üsna raske õppida ja kasutada, kuid korraliku uurimistööga toob see palju kasu ja hõlbustab arvutusi.