Excel Solver ntxiv-hauv ua kom zoo dua lej. Qhov no feem ntau yog siv los ua kom haum cov qauv nyuaj rau cov ntaub ntawv lossis nrhiav kev daws teeb meem rov qab rau cov teeb meem. Piv txwv li, tej zaum koj yuav xav kom haum ib qho nkhaus los ntawm qee cov ntaub ntawv cov ntsiab lus, siv qhov sib npaug. Solver tuaj yeem nrhiav qhov tsis tu ncua hauv qhov sib npaug uas muab qhov zoo tshaj plaws haum rau cov ntaub ntawv. Lwm daim ntawv thov yog qhov uas nws tsis yooj yim rau rearrange tus qauv los ua qhov yuav tsum tau tso tawm cov ntsiab lus ntawm ib tug kab zauv.
Tus Solver nyob qhov twg hauv Excel?
Tus Solver add-in suav nrog Excel tab sis nws tsis yog ib txwm thauj khoom ua ib feem ntawm kev teeb tsa lub neej ntawd. Txhawm rau kuaj xyuas seb nws puas tau thauj khoom, xaiv lub DATA tab thiab nrhiav Solver icon hauv Tshaj tawm.
Yog tias koj tsis tuaj yeem nrhiav Solver nyob rau hauv DATA tab ces koj yuav tsum tau thauj cov add-in:
-
Xaiv lub FILE tab thiab tom qab ntawd xaiv Options.
Image -
In the Options dialogue box xaiv Add-Ins los ntawm cov tab ntawm sab laug.
Image -
Nyob hauv qab ntawm qhov rais, xaiv Excel Add-ins los ntawm Manage dropdown thiab xaiv Mus …
Image -
Khuv lub npov nyob ib sab ntawm Solver Add-in thiab xaiv OK.
Image -
The Solver cov lus txib yuav tsum tam sim no tshwm rau ntawm DATA tab. Koj npaj siv Solver.
Image
Siv Solver hauv Excel
Cia peb pib nrog ib qho piv txwv yooj yim kom nkag siab tias Tus Txheej Txheem ua li cas. Xav txog tias peb xav paub dab tsi lub vojvoog yuav muab lub voj voog nrog thaj tsam ntawm 50 square units. Peb paub qhov sib npaug ntawm thaj tsam ntawm lub voj voog (A=pi r 2). Peb tuaj yeem hloov kho qhov sib npaug no kom muab lub vojvoog uas xav tau rau thaj chaw muab, tab sis rau qhov piv txwv cia peb ua piv txwv tias peb tsis paub yuav ua li cas.
Create a spreadsheet with the radius in B1 and xam thaj chaw hauv B2 siv qhov sib npaug =pi()B1^2.
Peb tuaj yeem kho tus nqi ntawm tus nqi hauv B1 txog B2 qhia tus nqi uas ze txaus rau 50. Nyob ntawm seb peb qhov tseeb npaum li cas yuav tsum tau, qhov no tej zaum yuav yog ib tug tswv yim mus kom ze. Txawm li cas los xij, yog tias peb xav tau qhov tseeb heev, nws yuav siv sij hawm ntev los ua qhov yuav tsum tau hloov kho. Qhov tseeb, qhov no yog qhov tseem ceeb uas Solver ua. Nws ua rau kev hloov pauv rau qhov tseem ceeb hauv qee lub hlwb, thiab kuaj xyuas tus nqi hauv lub hom phiaj ntawm tes:
- Xaiv DATA tab thiab Solver, txhawm rau txhawm rau txhawm rau Kev daws teeb meem dialogue box
-
Set Objective cell to be the Area, B2. Qhov no yog tus nqi uas yuav raug kuaj xyuas, kho lwm lub hlwb kom txog thaum tus nqi no mus txog qhov tseeb.
Image -
Xaiv khawm rau Tus nqi ntawm: thiab teem tus nqi ntawm 50. Qhov no yog tus nqi uas B2 yuav tsum ua tiav.
Image -
In the box titled By Changing Variable Cells: nkag mus rau lub cell uas muaj lub vojvoog, B1.
Image -
Txais lwm txoj kev xaiv raws li lawv yog los ntawm lub neej ntawd thiab xaiv Tshaj . Kev ua kom zoo dua yog ua tiav, tus nqi ntawm B1 raug kho kom txog thaum B2 yog 50 thiab daws qhov tshwm sim kev sib tham tau tshwm sim.
Image -
Xaiv OK kom khaws cov tshuaj.
Image
Qhov piv txwv yooj yim no qhia tias tus neeg daws teeb meem ua haujlwm li cas. Hauv qhov no, peb tuaj yeem yooj yim tau txais kev daws teeb meem hauv lwm txoj hauv kev. Tom ntej no peb yuav saib qee qhov piv txwv uas Solver muab cov kev daws teeb meem uas yuav nyuaj nrhiav lwm txoj hauv kev.
Txhim tus qauv nyuaj Siv Excel Solver Ntxiv-In
Excel muaj lub luag haujlwm tsim los ua cov kab rov tav, haum txoj kab ncaj nraim los ntawm cov ntaub ntawv. Ntau cov haujlwm uas tsis yog-linear tuaj yeem yog linearized lub ntsiab lus uas linear regression tuaj yeem siv los ua kom haum rau cov haujlwm xws li exponentials. Rau ntau txoj haujlwm nyuaj, Solver tuaj yeem siv los ua 'tsawg squares minimization'. Hauv qhov piv txwv no, peb yuav txiav txim siab haum qhov sib npaug ntawm daim ntawv ax^b+cx^d rau cov ntaub ntawv hauv qab no.
Qhov no suav nrog cov kauj ruam hauv qab no:
- Tau cov ntaub ntawv nrog x qhov tseem ceeb hauv kab A thiab y-tus nqi hauv kab B.
- Tsim 4 coefficient qhov tseem ceeb (a, b, c, thiab d) qhov chaw ntawm daim ntawv nthuav qhia, cov no tuaj yeem muab cov nqi pib yam tsis raug.
-
Tsim ib kab ntawm qhov haum Y qhov tseem ceeb, siv qhov sib npaug ntawm daim ntawv ax^b+cx^d uas hais txog cov coefficients tsim nyob rau hauv kauj ruam 2 thiab qhov x qhov tseem ceeb hauv kab A. Nco ntsoov tias txhawm rau luam cov qauv hauv qab kem, cov ntawv xa mus rau cov coefficients yuav tsum yog qhov tseeb thaum qhov kev xa mus rau x qhov tseem ceeb yuav tsum yog txheeb ze.
Image -
Txawm hais tias tsis yog qhov tseem ceeb, koj tuaj yeem tau txais qhov pom kev pom ntawm qhov zoo npaum li cas ntawm qhov sib npaug yog los ntawm kev kos ob kab y tiv thaiv x qhov tseem ceeb ntawm ib daim ntawv XY tawg. Nws ua rau kev txiav txim siab siv cov cim rau cov ntsiab lus ntawm cov ntaub ntawv qub, vim cov no yog cov txiaj ntsig tsis sib xws nrog lub suab nrov, thiab siv kab rau qhov sib npaug sib npaug.
Image -
Tom ntej no, peb xav tau ib txoj hauv kev los ntsuas qhov sib txawv ntawm cov ntaub ntawv thiab peb qhov sib npaug sib npaug. Tus txheej txheem ua qhov no yog xam cov lej ntawm cov squared sib txawv. Hauv kab thib peb, rau txhua kab, thawj cov ntaub ntawv tus nqi rau Y yog rho tawm los ntawm tus nqi sib npaug, thiab qhov tshwm sim yog squared. Yog li, hauv D2, tus nqi yog muab los ntawm =(C2-B2)^2 Cov lej ntawm tag nrho cov nqi squared no yog xam. Txij li cov txiaj ntsig yog squared lawv tuaj yeem tsuas yog qhov zoo.
Image -
Tam sim no koj tau npaj ua qhov ua kom zoo dua siv Solver. Muaj plaub coefficients uas yuav tsum tau kho (a, b, c thiab d). Koj kuj muaj ib lub hom phiaj tus nqi kom txo qis, qhov sib npaug ntawm qhov sib txawv squared. Tua tawm tus solver, raws li saum toj no, thiab teeb tsa cov kev daws teeb meem los siv cov txiaj ntsig no, raws li qhia hauv qab no.
Image -
Uncheck qhov kev xaiv rau Ua kom tsis txhob muaj kev sib txawv uas tsis yog-tsis zoo, qhov no yuav yuam kom txhua tus coefficients coj qhov zoo.
Image -
Xaiv Tshajthiab tshuaj xyuas cov txiaj ntsig. Daim ntawv qhia yuav hloov kho qhov qhia tau zoo ntawm qhov zoo ntawm kev haum. Yog hais tias tus solver tsis tsim ib tug zoo haum rau thawj sim koj yuav sim khiav nws dua. Yog tias qhov haum tau zoo lawm, sim daws los ntawm cov txiaj ntsig tam sim no. Txwv tsis pub, koj tuaj yeem sim manually txhim kho qhov haum ua ntej kev daws teeb meem.
Image - Thaum tau txais txiaj ntsig zoo koj tuaj yeem tawm ntawm kev daws teeb meem.
daws tus qauv Iteratively
Qee lub sij hawm muaj qhov sib npaug yooj yim uas muab cov txiaj ntsig ntawm qee cov tswv yim. Txawm li cas los xij, thaum peb sim thim qhov teeb meem nws tsis tuaj yeem nrhiav kev daws teeb meem yooj yim. Piv txwv li, lub zog siv los ntawm lub tsheb yog kwv yees li muab los ntawm P=av + bv^3 qhov twg v yog qhov nrawm, a yog tus coefficient rau dov tsis kam thiab b yog coefficient rau aerodynamic luag. Txawm hais tias qhov no yog qhov sib npaug yooj yim, nws tsis yooj yim rau kev hloov kho kom muab qhov sib npaug ntawm qhov nrawm lub tsheb yuav ncav cuag rau lub zog muab tswv yim. Txawm li cas los xij, peb tuaj yeem siv Solver los nrhiav qhov nrawm dua. Piv txwv li, nrhiav qhov nrawm nrawm nrog lub zog nkag ntawm 740 W.
-
Teeb ib daim ntawv yooj yim nrog qhov nrawm, cov coefficients a thiab b, thiab lub zog suav los ntawm lawv.
Image -
Launch the Solver thiab nkag mus rau lub hwj chim, B5, raws li lub hom phiaj. Teem lub hom phiaj tus nqi ntawm 740 thiab xaiv qhov nrawm, B2, raws li cov hlwb hloov pauv. Xaiv solve pib qhov kev daws.
Image -
Txoj kev daws teeb meem kho tus nqi nrawm kom txog thaum lub zog ze ze rau 740, muab qhov nrawm peb xav tau.
Image - Kev daws cov qauv hauv txoj kev no feem ntau tuaj yeem nrawm dua thiab tsis ua yuam kev tsawg dua li kev hloov pauv cov qauv nyuaj.
Kev nkag siab txog cov kev xaiv sib txawv uas muaj nyob hauv cov kev daws teeb meem yuav nyuaj heev. Yog tias koj muaj teeb meem tau txais cov kev daws teeb meem zoo ces nws feem ntau muaj txiaj ntsig los siv ciam teb rau cov hlwb hloov tau. Cov no yog txwv qhov tseem ceeb tshaj qhov uas lawv yuav tsum tsis txhob hloov kho. Piv txwv li, hauv qhov piv txwv yav dhau los, qhov kev tshaj tawm yuav tsum tsis txhob tsawg tshaj li xoom thiab nws kuj tseem tuaj yeem teem caij rau sab saud. Qhov no yuav yog qhov nrawm uas koj paub tseeb tias lub tsheb tsis tuaj yeem mus nrawm dua. Yog tias koj tuaj yeem teeb tsa cov ciam teb rau cov hloov pauv hloov pauv tau, ces nws kuj ua rau lwm yam kev xaiv ua haujlwm zoo dua, xws li multistart. Qhov no yuav khiav ntau txoj kev daws teeb meem, pib ntawm qhov sib txawv pib qhov tseem ceeb rau qhov sib txawv.
Xaiv txoj kev daws teeb meem kuj nyuaj. Simplex LP tsuas yog haum rau cov qauv linear, yog tias qhov teeb meem tsis yog linear nws yuav ua tsis tiav nrog cov lus hais tias tus mob no tsis tau ntsib. Lwm txoj hauv kev yog ob qho tib si haum rau cov txheej txheem tsis-linear. GRG Nonlinear yog qhov nrawm tshaj plaws tab sis nws txoj kev daws teeb meem tuaj yeem ua tau zoo nyob ntawm qhov pib pib. Nws muaj qhov hloov pauv uas nws tsis tas yuav hloov pauv kom muaj kev cia siab. Evolutionary solver feem ntau txhim khu kev qha tab sis nws yuav tsum muaj txhua qhov sib txawv kom muaj ob qho tib si sab sauv thiab sab qis, uas tej zaum yuav nyuaj rau kev ua haujlwm ua ntej.
Excel Solver add-in yog lub cuab yeej muaj zog heev uas tuaj yeem siv rau ntau yam teeb meem. Txhawm rau nkag mus rau lub zog ntawm Excel, sim ua ke Solver nrog Excel macro.
