Excel Solver papildinys atlieka matematinį optimizavimą. Tai paprastai naudojama sudėtingiems modeliams pritaikyti prie duomenų arba rasti kartotinių problemų sprendimus. Pavyzdžiui, galbūt norėsite pritaikyti kreivę per kai kuriuos duomenų taškus, naudodami lygtį. Solver gali rasti lygties konstantas, kurios geriausiai atitinka duomenis. Kita programa yra ta, kai sunku pertvarkyti modelį, kad reikiama išvestis būtų lygties objektas.
Kur yra Solver programoje Excel?
Solver priedas yra įtrauktas į „Excel“, tačiau jis ne visada įkeliamas kaip numatytojo diegimo dalis. Norėdami patikrinti, ar jis įkeltas, pasirinkite skirtuką DATA ir ieškokite Solver piktogramos Analysis skiltyje.
Jei nerandate Solver skirtuke DUOMENYS, turėsite įkelti priedą:
-
Pasirinkite skirtuką FILE, tada pasirinkite Options.
Image -
Dialogo lange Options pasirinkite Add-Ins iš kairėje pusėje esančių skirtukų.
Image -
Lango apačioje pasirinkite Excel Add-ins Tvarkyti ir pasirinkite Pirmyn…
Image -
Pažymėkite žymimąjį laukelį šalia Solver Add-in ir pasirinkite OK.
Image -
Komanda Solver dabar turėtų būti rodoma skirtuke DATA. Esate pasirengę naudoti Solver.
Image
Solver naudojimas programoje Excel
Pradėkime nuo paprasto pavyzdžio, kad suprastume, ką veikia Solver. Įsivaizduokite, kad norime sužinoti, koks spindulys duos apskritimą, kurio plotas yra 50 kvadratinių vienetų. Mes žinome apskritimo ploto lygtį (A=pi r2). Žinoma, galėtume pertvarkyti šią lygtį, kad gautume spindulį, reikalingą tam tikroje srityje, bet dėl pavyzdžio apsimeskime, kad nežinome, kaip tai padaryti.
Sukurkite skaičiuoklę su spinduliu B1 ir apskaičiuokite plotą B2 naudodami lygtį =pi()B1^2.
Galėtume rankiniu būdu koreguoti reikšmę B1, kol B2 parodys vertę, kuri yra pakankamai artima 50. Priklausomai nuo to, kaip tiksliai mes tai gali būti praktiškas požiūris. Tačiau, jei reikia būti labai tikslūs, reikiamų koregavimų atlikimas užtruks ilgai. Tiesą sakant, tai iš esmės daro Solver. Jis koreguoja vertes tam tikruose langeliuose ir tikrina reikšmę tiksliniame langelyje:
- Pasirinkite DATA skirtuką ir Solver, kad įkeltumėte Solver Parameters dialogo langą
-
Nustatykite tikslą langelį kaip sritį, B2. Tai vertė, kuri bus patikrinta, koreguojant kitus langelius, kol šis pasieks teisingą reikšmę.
Image -
Pasirinkite mygtuką Value of: ir nustatykite reikšmę 50. Tai vertė, kurią turėtų pasiekti B2.
Image -
Lauke pavadinimu Keisdami kintamuosius langelius: įveskite langelį, kuriame yra spindulys, B1.
Image -
Palikite kitas parinktis tokias, kokios jos yra pagal numatytuosius nustatymus, ir pasirinkite Solve. Atliekamas optimizavimas, B1 reikšmė koreguojama, kol B2 bus 50 ir bus rodomas dialogas Solver Results.
Image -
Pasirinkite Gerai, kad sprendimas liktų.
Image
Šis paprastas pavyzdys parodė, kaip veikia sprendėjas. Tokiu atveju būtų lengviau rasti sprendimą kitais būdais. Toliau apžvelgsime keletą pavyzdžių, kai Solver pateikia sprendimus, kuriuos būtų sunku rasti kitaip.
Sudėtingo modelio pritaikymas naudojant „Excel Solver“priedą
„Excel“turi integruotą funkciją, leidžiančią atlikti tiesinę regresiją, pritaikant tiesią liniją per duomenų rinkinį. Daugelis įprastų netiesinių funkcijų gali būti tiesinės, tai reiškia, kad tiesinė regresija gali būti naudojama funkcijoms, pvz., eksponentams, pritaikyti. Sudėtingesnėms funkcijoms atlikti Solver galima naudoti „mažiausių kvadratų sumažinimą“. Šiame pavyzdyje apsvarstysime galimybę pritaikyti formos ax^b+cx^d lygtį prie toliau pateiktų duomenų.
Tai apima šiuos veiksmus:
- Sutvarkykite duomenų rinkinį su x reikšmėmis A stulpelyje ir y reikšmėmis B stulpelyje.
- Sukurkite 4 koeficientų reikšmes (a, b, c ir d) kur nors skaičiuoklėje, joms gali būti pateiktos savavališkos pradinės reikšmės.
-
Sukurkite pritaikytų Y reikšmių stulpelį naudodami ax^b+cx^d formos lygtį, kuri nurodo koeficientus, sukurtus 2 veiksme, ir x reikšmes A stulpelyje. Atminkite, kad norėdami nukopijuoti formulę žemyn stulpelyje, nuorodos į koeficientus turi būti absoliučios, o nuorodos į x reikšmes turi būti santykinės.
Image -
Nors tai nėra būtina, galite gauti vaizdinį požymį, kaip gerai tinka lygtis, nubraižydami abu y stulpelius x verčių atžvilgiu vienoje XY taškinėje diagramoje. Tikslinga naudoti žymeklius pradiniams duomenų taškams, nes tai yra diskrečios reikšmės su triukšmu, o pritaikytai lygčiai naudoti liniją.
Image -
Toliau mums reikia būdo kiekybiškai įvertinti skirtumą tarp duomenų ir mūsų pritaikytos lygties. Standartinis būdas tai padaryti yra apskaičiuoti skirtumų kvadratu sumą. Trečiame kiekvienos eilutės stulpelyje pradinė Y duomenų reikšmė atimama iš pritaikytos lygties vertės, o rezultatas pavaizduojamas kvadratu. Taigi, D2 reikšmė pateikiama taip: =(C2-B2)^2 Tada apskaičiuojama visų šių kvadratinių verčių suma. Kadangi reikšmės yra kvadratinės, jos gali būti tik teigiamos.
Image -
Dabar esate pasirengę atlikti optimizavimą naudodami Solver. Yra keturi koeficientai, kuriuos reikia koreguoti (a, b, c ir d). Taip pat turite vieną objektyvią vertę, kurią reikia sumažinti, skirtumų kvadratu sumą. Paleiskite sprendiklį, kaip nurodyta aukščiau, ir nustatykite sprendiklio parametrus, kad jie nurodytų šias reikšmes, kaip parodyta toliau.
Image -
Atžymėkite parinktį Padaryti neapribotus kintamuosius neneigiamus, tai priverstų visus koeficientus gauti teigiamas reikšmes.
Image -
Pasirinkite Solve ir peržiūrėkite rezultatus. Diagrama bus atnaujinta ir gerai parodys tinkamumą. Jei pirmuoju bandymu sprendimas netinka, galite pabandyti jį paleisti dar kartą. Jei tinkamumas pagerėjo, pabandykite nustatyti pagal dabartines vertes. Priešingu atveju prieš išspręsdami galite pabandyti rankiniu būdu pagerinti tinkamumą.
Image - Kai bus pasiektas tinkamas derinys, galite išeiti iš sprendiklio.
Iteratyvus modelio sprendimas
Kartais yra gana paprasta lygtis, kuri suteikia išvestį tam tikros įvesties atžvilgiu. Tačiau kai bandome apversti problemą, neįmanoma rasti paprasto sprendimo. Pavyzdžiui, transporto priemonės suvartojama galia apytiksliai apskaičiuojama iš P=av + bv^3, kur v yra greitis, a yra pasipriešinimo riedėjimui koeficientas ir b yra koeficientas aerodinaminis pasipriešinimas. Nors tai gana paprasta lygtis, ją nelengva pertvarkyti taip, kad būtų sudaryta greičio, kurį transporto priemonė pasieks esant tam tikrai galiai, lygtis. Tačiau galime naudoti Solver, kad iteratyviai rastume šį greitį. Pavyzdžiui, suraskite greitį, pasiektą esant 740 W įvesties galiai.
-
Sudarykite paprastą skaičiuoklę su greičiu, koeficientais a ir b ir pagal juos apskaičiuota galia.
Image -
Paleiskite Solver ir kaip tikslą įveskite galią B5. Nustatykite objektyvią reikšmę 740 ir pasirinkite greitį B2 kaip kintamuosius langelius, kuriuos norite keisti. Pasirinkite solve, kad pradėtumėte sprendimą.
Image -
Spręstiklis koreguoja greičio reikšmę tol, kol galia bus labai artima 740, užtikrindamas mums reikalingą greitį.
Image - Šiuo būdu modelių sprendimas dažnai gali būti greitesnis ir mažiau klaidų nei sudėtingų modelių apvertimas.
Suprasti įvairias sprendiklio parinktis gali būti gana sunku. Jei kyla sunkumų ieškant protingo sprendimo, dažnai naudinga keisti ląstelėms taikyti ribines sąlygas. Tai yra ribinės vertės, kurias viršijus jos neturėtų būti koreguojamos. Pavyzdžiui, ankstesniame pavyzdyje greitis neturi būti mažesnis už nulį ir taip pat būtų galima nustatyti viršutinę ribą. Tai būtų greitis, kuriuo esate tikras, kad transporto priemonė negali važiuoti greičiau. Jei galite nustatyti ribas keičiamiems kintamiesiems langeliams, tai taip pat pagerins kitų sudėtingesnių parinkčių, pvz., kelių paleidimų, veikimą. Bus paleista daug skirtingų sprendimų, pradedant nuo skirtingų pradinių kintamųjų verčių.
Sprendimo metodo pasirinkimas taip pat gali būti sudėtingas. Simplex LP tinka tik linijiniams modeliams, jei problema nėra linijinė, ji nepavyks ir bus rodomas pranešimas, kad ši sąlyga neįvykdyta. Kiti du metodai tinka nelinijiniams metodams. GRG Netiesinis yra greičiausias, tačiau jo sprendimas gali labai priklausyti nuo pradinių paleidimo sąlygų. Jis turi tokį lankstumą, kad nereikalauja kintamųjų, kad būtų nustatytos ribos. Evoliucinis sprendimas dažnai yra patikimiausias, tačiau jam reikia, kad visi kintamieji turėtų ir viršutinę, ir apatinę ribas, o tai gali būti sunku nustatyti iš anksto.
Excel Solver priedas yra labai galingas įrankis, kurį galima pritaikyti daugeliui praktinių problemų. Norėdami visapusiškai naudotis „Excel“galia, pabandykite „Solver“derinti su „Excel“makrokomandomis.
