Ano ang Excel Solver?

2024 May -akda: Abigail Brown | [email protected]. Huling binago: 2023-12-17 07:03

Ang Excel Solver add-in ay gumaganap ng mathematical optimization. Ito ay karaniwang ginagamit upang magkasya ang mga kumplikadong modelo sa data o maghanap ng mga umuulit na solusyon sa mga problema. Halimbawa, maaaring gusto mong magkasya ang isang curve sa pamamagitan ng ilang mga punto ng data, gamit ang isang equation. Mahahanap ng Solver ang mga constant sa equation na nagbibigay ng pinakamahusay na akma sa data. Ang isa pang application ay kung saan mahirap ayusin muli ang isang modelo upang gawing paksa ng isang equation ang kinakailangang output.

Nasaan ang Solver sa Excel?

Ang Solver add-in ay kasama sa Excel ngunit hindi ito palaging nilo-load bilang bahagi ng default na pag-install. Para tingnan kung na-load ito, piliin ang tab na DATA at hanapin ang icon na Solver sa seksyong Analysis.

Kung hindi mo mahanap ang Solver sa ilalim ng DATA tab, kakailanganin mong i-load ang add-in:

1. Piliin ang tab na FILE at pagkatapos ay piliin ang Options.

   

2. Sa Options dialogue box piliin ang Add-Ins mula sa mga tab sa kaliwang bahagi.

   

3. Sa ibaba ng window, piliin ang Excel Add-ins mula sa dropdown na Manage at piliin ang Go…

   

4. Lagyan ng check ang check-box sa tabi ng Solver Add-in at piliin ang OK.

   

5. Ang Solver na command ay dapat na ngayong lumabas sa tab na DATA. Handa ka nang gamitin ang Solver.

   

Paggamit ng Solver sa Excel

Magsimula tayo sa isang simpleng halimbawa para maunawaan kung ano ang ginagawa ng Solver. Isipin na gusto nating malaman kung anong radius ang magbibigay ng bilog na may lawak na 50 square units. Alam natin ang equation para sa lugar ng isang bilog (A=pi r²). Siyempre, maaari nating muling ayusin ang equation na ito upang maibigay ang radius na kinakailangan para sa isang partikular na lugar, ngunit para sa halimbawa, magkunwaring hindi natin alam kung paano iyon gagawin.

Gumawa ng spreadsheet na may radius sa B1 at kalkulahin ang lugar sa B2 gamit ang equation na =pi()B1^2.

Maaari naming manual na isaayos ang value sa B1 hanggang ang B2 ay nagpapakita ng value na malapit sa 50. Depende sa kung gaano tayo katumpak kailangan, ito ay maaaring isang praktikal na diskarte. Gayunpaman, kung kailangan nating maging napaka-eksakto, aabutin ng mahabang panahon upang gawin ang mga kinakailangang pagsasaayos. Sa totoo lang, ito talaga ang ginagawa ng Solver. Gumagawa ito ng mga pagsasaayos sa mga value sa ilang partikular na cell, at sinusuri ang value sa isang target na cell:

1. Piliin ang DATA tab at Solver, para i-load ang Solver Parameter dialogue box

2. Itakda ang Layunin cell upang maging Area, B2. Ito ang value na susuriin, na nagsasaayos ng ibang mga cell hanggang sa maabot nito ang tamang halaga.

   

3. Piliin ang button para sa Halaga ng: at magtakda ng value na 50. Ito ang value na dapat makamit ng B2.

   

4. Sa kahon na pinamagatang By Changing Variable Cells: ilagay ang cell na naglalaman ng radius, B1.

   

5. Iwanan ang iba pang mga opsyon bilang default at piliin ang Solve. Isinasagawa ang pag-optimize, ang halaga ng B1 ay inaayos hanggang ang B2 ay 50 at ang Resulta ng Paglutas ay ipinapakita.

   

6. Piliin ang OK upang panatilihin ang solusyon.

   

Ang simpleng halimbawang ito ay nagpakita kung paano gumagana ang solver. Sa kasong ito, maaari nating mas madaling makuha ang solusyon sa ibang mga paraan. Susunod, titingnan natin ang ilang halimbawa kung saan nagbibigay ang Solver ng mga solusyon na mahirap maghanap ng ibang paraan.

Pag-aayos ng Kumplikadong Modelo Gamit ang Excel Solver Add-In

Ang

Excel ay may built-in na function upang magsagawa ng linear regression, na umaangkop sa isang tuwid na linya sa pamamagitan ng isang set ng data. Maraming mga karaniwang non-linear na function ang maaaring linearized ibig sabihin na ang linear regression ay maaaring gamitin upang magkasya ang mga function tulad ng exponentials. Para sa mas kumplikadong mga pag-andar ang Solver ay maaaring gamitin upang magsagawa ng 'pinakamababang mga parisukat na minimization'. Sa halimbawang ito, isasaalang-alang namin ang paglapat ng equation ng form na ax^b+cx^d sa data na ipinapakita sa ibaba.

Kabilang dito ang mga sumusunod na hakbang:

1. Ayusin ang dataset na may mga x value sa column A at y-values sa column B.
2. Gumawa ng 4 na coefficient value (a, b, c, at d) sa isang lugar sa spreadsheet, maaari itong bigyan ng mga arbitrary na panimulang value.

3. Gumawa ng column ng mga fitted Y values, gamit ang isang equation ng form na ax^b+cx^d na tumutukoy sa mga coefficient na ginawa sa hakbang 2 at sa x value sa column A. Tandaan na para kopyahin ang formula pababa sa column, ang mga reference sa mga coefficient ay dapat na absolute habang ang mga reference sa x value ay dapat na relative.

   

4. Bagama't hindi mahalaga, makakakuha ka ng visual na indikasyon kung gaano kahusay ang pagkakatugma ng equation sa pamamagitan ng pag-plot ng parehong y column laban sa mga x value sa iisang XY scatter chart. Makatuwirang gumamit ng mga marker para sa orihinal na mga punto ng data, dahil ito ay mga discrete value na may ingay, at gumamit ng linya para sa fitted equation.

   

5. Susunod, kailangan namin ng paraan ng pagbibilang ng pagkakaiba sa pagitan ng data at ng aming fitted equation. Ang karaniwang paraan upang gawin ito ay upang kalkulahin ang kabuuan ng mga parisukat na pagkakaiba. Sa isang ikatlong hanay, para sa bawat hilera, ang orihinal na halaga ng data para sa Y ay ibinabawas mula sa karapat-dapat na halaga ng equation, at ang resulta ay naka-squad. Kaya, sa D2, ang value ay ibinibigay ng =(C2-B2)^2 Ang kabuuan ng lahat ng mga squared value na ito ay kinakalkula. Dahil ang mga halaga ay squared maaari lamang silang maging positibo.

   

6. Handa ka na ngayong isagawa ang pag-optimize gamit ang Solver. Mayroong apat na coefficient na kailangang ayusin (a, b, c at d). Mayroon ka ring isang layunin na halaga na bawasan, ang kabuuan ng mga squared na pagkakaiba. Ilunsad ang solver, tulad ng nasa itaas, at itakda ang mga parameter ng solver upang i-reference ang mga value na ito, tulad ng ipinapakita sa ibaba.

   

7. Alisin ang check sa opsyong Gawing Hindi Negatibo ang Mga Hindi Pinipigilang Variable, mapipilitan nito ang lahat ng coefficient na kumuha ng mga positibong halaga.

   

8. Piliin ang Solve at suriin ang mga resulta. Ang tsart ay mag-a-update na nagbibigay ng magandang indikasyon ng goodness of fit. Kung ang solver ay hindi gumawa ng isang mahusay na akma sa unang pagtatangka maaari mong subukang patakbuhin itong muli. Kung bumuti ang akma, subukang lutasin mula sa kasalukuyang mga halaga. Kung hindi, maaari mong subukang manu-manong pahusayin ang akma bago malutas.

   

9. Kapag nakuha na ang isang magandang bagay, maaari kang lumabas sa solver.

Paulit-ulit na Paglutas ng Modelo

Minsan may medyo simpleng equation na nagbibigay ng output sa mga tuntunin ng ilang input. Gayunpaman, kapag sinubukan nating baligtarin ang problema, hindi posible na makahanap ng isang simpleng solusyon. Halimbawa, ang kapangyarihang natupok ng isang sasakyan ay tinatayang ibinibigay ng P=av + bv^3 kung saan ang v ay ang bilis, ang a ay isang koepisyent para sa rolling resistance at ang b ay isang koepisyent para sa aerodynamic drag. Bagama't ito ay medyo simpleng equation, hindi madaling muling ayusin upang magbigay ng equation ng bilis na maaabot ng sasakyan para sa isang ibinigay na power input. Gayunpaman, maaari naming gamitin ang Solver upang paulit-ulit na mahanap ang bilis na ito. Halimbawa, hanapin ang bilis na natamo gamit ang power input na 740 W.

1. Mag-set up ng isang simpleng spreadsheet na may bilis, mga coefficient a at b, at ang kapangyarihan na kinakalkula mula sa mga ito.

   

2. Ilunsad ang Solver at ilagay ang kapangyarihan, B5, bilang layunin. Magtakda ng layunin na value na 740 at piliin ang bilis, B2, bilang variable na mga cell na babaguhin. Piliin ang solve para simulan ang solusyon.

   

3. Isinasaayos ng solver ang value ng velocity hanggang ang power ay napakalapit sa 740, na nagbibigay ng velocity na kailangan namin.

   

4. Ang paglutas ng mga modelo sa paraang ito ay kadalasang maaaring mas mabilis at mas madaling magkaroon ng error kaysa sa pagbabaligtad ng mga kumplikadong modelo.

Ang pag-unawa sa iba't ibang opsyon na available sa solver ay maaaring maging mahirap. Kung nahihirapan kang makakuha ng makatwirang solusyon, kadalasan ay kapaki-pakinabang na maglapat ng mga kundisyon sa hangganan sa mga nababagong cell. Ang mga ito ay naglilimita sa mga halaga kung saan hindi sila dapat ayusin. Halimbawa, sa nakaraang halimbawa, ang bilis ay hindi dapat mas mababa sa zero at posible ring magtakda ng upper bound. Ito ay isang bilis na sigurado ka na ang sasakyan ay hindi makakatakbo nang mas mabilis kaysa sa. Kung nagagawa mong magtakda ng mga hangganan para sa mga nababagong variable na cell, ginagawa rin nitong mas mahusay na gumana ang iba pang mas advanced na mga opsyon, gaya ng multistart. Tatakbo ito ng maraming iba't ibang solusyon, simula sa iba't ibang mga inisyal na halaga para sa mga variable.

Ang pagpili ng Paraan ng Paglutas ay maaari ding maging mahirap. Ang Simplex LP ay angkop lamang para sa mga linear na modelo, kung ang problema ay hindi linear, ito ay mabibigo sa isang mensahe na ang kundisyong ito ay hindi natugunan. Ang iba pang dalawang pamamaraan ay parehong angkop sa mga non-linear na pamamaraan. Ang GRG Nonlinear ay ang pinakamabilis ngunit ang solusyon nito ay maaaring lubos na nakadepende sa mga paunang kondisyon ng pagsisimula. Mayroon itong kakayahang umangkop na hindi nangangailangan ng mga variable upang magkaroon ng mga hangganan na nakatakda. Ang Evolutionary solver ang kadalasang pinaka-maaasahan ngunit nangangailangan ito ng lahat ng variable na magkaroon ng upper at lower bounds, na maaaring mahirap gawin nang maaga.

Ang Excel Solver add-in ay isang napakalakas na tool na maaaring magamit sa maraming praktikal na problema. Upang ganap na ma-access ang kapangyarihan ng Excel, subukang pagsamahin ang Solver sa Excel macros.