Exceli Solveri lisandmoodul teostab matemaatilist optimeerimist. Seda kasutatakse tavaliselt keerukate mudelite andmetega sobitamiseks või probleemidele iteratiivsete lahenduste leidmiseks. Näiteks võite soovida võrrandi abil sobitada kõvera läbi mõne andmepunkti. Solver suudab võrrandist leida konstandid, mis sobivad andmetega kõige paremini. Teine rakendus on see, kus mudelit on keeruline ümber korraldada, et muuta nõutav väljund võrrandi objektiks.
Kus on Solver Excelis?
Solveri lisandmoodul on Excelis kaasas, kuid seda ei laadita alati vaikeinstalli osana. Kontrollimaks, kas see on laaditud, valige vahekaart DATA ja otsige jaotisest Analysis ikooni Solver.
Kui te ei leia Solverit vahekaardilt ANDMED, peate laadima lisandmooduli:
-
Valige vahekaart FILE ja seejärel Options.
Image -
Valige dialoogiboksis Options vasakpoolsetelt vahekaartidelt Lisandmoodulid.
Image -
Akna allosas valige Exceli lisandmoodulid rippmenüüst Manage ja valige Mine…
Image -
Märkige ruut valiku Solver Add-in kõrval ja valige OK.
Image -
Käsk Solver peaks nüüd ilmuma vahekaardil DATA. Olete valmis Solverit kasutama.
Image
Solveri kasutamine Excelis
Alustame lihtsa näitega, et mõista, mida Solver teeb. Kujutage ette, et tahame teada, milline raadius annab ringile pindalaga 50 ruutühikut. Me teame ringi pindala võrrandit (A=pi r2). Muidugi võiksime seda võrrandit ümber korraldada, et anda antud ala jaoks vajalik raadius, kuid näite huvides oletagem, et me ei tea, kuidas seda teha.
Looge arvutustabel raadiusega punktis B1 ja arvutage pindala punktis B2, kasutades võrrandit =pi()B1^2.
Võime käsitsi reguleerida väärtust B1, kuni B2 näitab väärtust, mis on piisav alt lähedane 50-le. Olenev alt sellest, kui täpne me oleme see võib olla praktiline lähenemine. Kui aga peame olema väga täpsed, võtab vajalike kohanduste tegemine kaua aega. Tegelikult teeb seda Solver sisuliselt. See kohandab väärtusi teatud lahtrites ja kontrollib väärtusi sihtlahtris:
- Valige DATA vahekaart ja Solver, et laadida Solver Parameters dialoogiboks
-
Määra Eesmärk lahtriks Piirkond B2. See on väärtus, mida kontrollitakse, kohandades teisi lahtreid, kuni see jõuab õige väärtuseni.
Image -
Valige nupp Value of: ja määrake väärtuseks 50. See on väärtus, mille B2 peaks saavutama.
Image -
Kasti pealkirjaga Muutujate lahtrite muutmisega: sisestage raadiust sisaldav lahter B1.
Image -
Jätke muud valikud vaikimisi samaks ja valige Lahenda. Optimeerimine viiakse läbi, B1 väärtust reguleeritakse, kuni B2 on 50 ja kuvatakse dialoog Solver Results.
Image -
Valige lahenduse säilitamiseks OK.
Image
See lihtne näide näitas, kuidas lahendaja töötab. Sel juhul oleksime saanud lahenduse hõlpsamini muul viisil. Järgmisena vaatame mõningaid näiteid, kus Solver annab lahendusi, mida oleks muul viisil raske leida.
Keerulise mudeli sobitamine Exceli lahendaja lisandmooduli abil
Excelil on sisseehitatud funktsioon lineaarse regressiooni teostamiseks, sobitades andmehulga läbi sirgjoone. Paljusid tavalisi mittelineaarseid funktsioone saab lineariseerida, mis tähendab, et lineaarset regressiooni saab kasutada funktsioonide, näiteks eksponentsiaalide sobitamiseks. Keerulisemate funktsioonide jaoks saab Solverit kasutada vähimruutude minimeerimiseks. Selles näites kaalume võrrandi vormis ax^b+cx^d sobitamist allpool näidatud andmetega.
See hõlmab järgmisi samme:
- Korraldage andmekogum x väärtustega veerus A ja y-väärtustega veerus B.
- Looge 4 koefitsiendi väärtust (a, b, c ja d) kuskil arvutustabelis, neile saab anda suvalised algväärtused.
-
Looge kohandatud Y väärtuste veerg, kasutades võrrandit kujul ax^b+cx^d, mis viitab sammus 2 loodud koefitsientidele ja x väärtustele veerus A. Pange tähele, et valemi alla kopeerimiseks veerus peavad viited koefitsientidele olema absoluutsed, samas kui viited x väärtustele peavad olema suhtelised.
Image -
Kuigi see pole oluline, saate võrrandi sobivuse kohta visuaalse ülevaate, kui joonistada mõlemad y veerud x väärtuste suhtes ühele XY hajuvusdiagrammile. Algsete andmepunktide jaoks on mõttekas kasutada markereid, kuna need on müraga diskreetsed väärtused, ja sobitatud võrrandi jaoks joont.
Image -
Järgmiseks vajame viisi andmete ja meie sobitatud võrrandi erinevuse kvantifitseerimiseks. Tavaline viis selleks on arvutada erinevuste ruudu summa. Kolmandas veerus lahutatakse iga rea Y algandmete väärtus sobitatud võrrandi väärtusest ja tulemus ruudustatakse. Seega on D2 väärtus antud kujul =(C2-B2)^2 Seejärel arvutatakse kõigi nende ruutude väärtuste summa. Kuna väärtused on ruudus, võivad need olla ainult positiivsed.
Image -
Olete nüüd valmis Solveri abil optimeerima. Kohandamist vajavad neli koefitsienti (a, b, c ja d). Samuti on teil minimeerimiseks üks objektiivne väärtus, erinevuste ruudu summa. Käivitage lahendaja, nagu ülal, ja määrake lahendaja parameetrid nendele väärtustele viitama, nagu allpool näidatud.
Image -
Tühjendage märkeruut Muuda piiramata muutujad mittenegatiivseteks, see sunniks kõik koefitsiendid võtma positiivseid väärtusi.
Image -
Valige Lahenda ja vaadake tulemused üle. Diagrammi värskendatakse, andes hea ülevaate sobivusest. Kui lahendaja ei sobi esimesel katsel hästi, võite proovida seda uuesti käivitada. Kui sobivus on paranenud, proovige lahendada praeguste väärtuste põhjal. Vastasel juhul võite enne lahendamist proovida sobivust käsitsi parandada.
Image - Kui sobiv sobivus on saavutatud, saate lahendajast väljuda.
Mudelite iteratiivne lahendamine
Mõnikord on suhteliselt lihtne võrrand, mis annab väljundi mõne sisendi alusel. Kui aga proovime probleemi ümber pöörata, ei ole võimalik lihtsat lahendust leida. Näiteks sõiduki tarbitud võimsus on ligikaudu antud P=av + bv^3 kus v on kiirus, a on veeretakistuse koefitsient ja b on veeretakistuse koefitsient aerodünaamiline takistus. Kuigi see on üsna lihtne võrrand, ei ole seda lihtne ümber korraldada, et anda võrrand kiiruse kohta, mille sõiduk teatud sisendvõimsuse korral saavutab. Selle kiiruse iteratiivseks leidmiseks saame aga kasutada Solverit. Näiteks leidke kiirus, mis saavutatakse 740 W sisendvõimsusega.
-
Koostage lihtne arvutustabel kiiruse, koefitsientide a ja b ning nende põhjal arvutatud võimsusega.
Image -
Käivitage Solver ja sisestage eesmärgina võimsus B5. Määrake objektiivseks väärtuseks 740 ja valige muutuva lahtrina kiirus B2. Lahenduse alustamiseks valige lahenda.
Image -
Lahendaja reguleerib kiiruse väärtust, kuni võimsus on väga lähedal 740-le, pakkudes meile vajalikku kiirust.
Image - Mudelite sellisel viisil lahendamine võib sageli olla kiirem ja vähem veaohtlik kui keeruliste mudelite ümberpööramine.
Lahendajas saadaolevate erinevate valikute mõistmine võib olla üsna keeruline. Kui teil on raskusi mõistliku lahenduse leidmisega, on sageli kasulik kohaldada muudetavatele lahtritele piirtingimusi. Need on piirväärtused, millest üle ei tohiks neid reguleerida. Näiteks eelmises näites ei tohiks kiirus olla väiksem kui null ja oleks võimalik määrata ka ülemine piir. See oleks kiirus, millest olete üsna kindel, et sõiduk ei saa kiiremini liikuda. Kui saate määrata muudetava muutuja lahtrite piirid, töötab see paremini ka muudel täpsematel valikutel, näiteks multistart. See käivitab mitu erinevat lahendust, alustades muutujate erinevatest algväärtustest.
Lahendusmeetodi valimine võib samuti olla keeruline. Simplex LP sobib ainult lineaarsete mudelite jaoks, kui probleem ei ole lineaarne, siis kuvatakse teade, et see tingimus ei ole täidetud. Ülejäänud kaks meetodit sobivad mõlemad mittelineaarsete meetoditega. GRG Nonlinear on kiireim, kuid selle lahendus võib suuresti sõltuda algsetest käivitustingimustest. Sellel on paindlikkus, et see ei nõua muutujate piiride määramist. Evolutsiooniline lahendaja on sageli kõige usaldusväärsem, kuid see nõuab, et kõigil muutujatel oleks nii ülemine kui ka alumine piir, mida võib olla keeruline eelnev alt välja töötada.
Exceli lahendaja lisandmoodul on väga võimas tööriist, mida saab rakendada paljude praktiliste probleemide lahendamiseks. Exceli täielikuks kasutamiseks proovige ühendada Solver Exceli makrodega.
