Excel Solver pievienojumprogramma veic matemātisko optimizāciju. To parasti izmanto, lai pielāgotu sarežģītus modeļus datiem vai atrastu iteratīvus problēmu risinājumus. Piemēram, iespējams, vēlēsities pielāgot līkni caur dažiem datu punktiem, izmantojot vienādojumu. Risinātājs vienādojumā var atrast konstantes, kas vislabāk atbilst datiem. Cita lietojumprogramma ir vieta, kur ir grūti pārkārtot modeli, lai nepieciešamo izvadi padarītu par vienādojuma priekšmetu.
Kur ir Solver programmā Excel?
Risinātāja pievienojumprogramma ir iekļauta programmā Excel, taču tā ne vienmēr tiek ielādēta kā daļa no noklusējuma instalācijas. Lai pārbaudītu, vai tas ir ielādēts, atlasiet cilni DATA un sadaļā Analysis meklējiet ikonu Solver..
Ja nevarat atrast Solver cilnē DATI, jums būs jāielādē pievienojumprogramma:
-
Atlasiet cilni FILE un pēc tam atlasiet Options.
Image -
Dialoglodziņā Options atlasiet Add-Ins no kreisās puses cilnēm.
Image -
Loga apakšā atlasiet Excel pievienojumprogrammas no Manage un atlasiet Go…
Image -
Atzīmējiet izvēles rūtiņu blakus Solver Add-in un atlasiet OK.
Image -
Komandai Solver tagad jāparādās cilnē DATA. Jūs esat gatavs lietot Solver.
Image
Risinātāja izmantošana programmā Excel
Sāksim ar vienkāršu piemēru, lai saprastu, ko dara Risinātājs. Iedomājieties, ka mēs vēlamies zināt, kāds rādiuss dos apli, kura laukums ir 50 kvadrātvienības. Mēs zinām apļa laukuma vienādojumu (A=pi r2). Mēs, protams, varētu pārkārtot šo vienādojumu, lai iegūtu konkrētajam apgabalam nepieciešamo rādiusu, taču piemēra labad izliksimies, ka nezinām, kā to izdarīt.
Izveidojiet izklājlapu ar rādiusu B1 un aprēķiniet laukumu B2, izmantojot vienādojumu =pi()B1^2.
Mēs varētu manuāli pielāgot vērtību B1, līdz B2 parāda vērtību, kas ir pietiekami tuvu 50. Atkarībā no tā, cik precīzi mēs esam tā varētu būt praktiska pieeja. Tomēr, ja mums ir jābūt ļoti precīziem, nepieciešamo korekciju veikšana prasīs daudz laika. Faktiski to būtībā dara Solver. Tas veic vērtību korekcijas noteiktās šūnās un pārbauda vērtību mērķa šūnā:
- Atlasiet cilni DATA un Solver, lai ielādētu dialoglodziņu Solver Parameters
-
Iestatīt Mērķi šūnu kā apgabalu, B2. Šī ir vērtība, kas tiks pārbaudīta, pielāgojot citas šūnas, līdz šī sasniegs pareizo vērtību.
Image -
Atlasiet pogu Value of: un iestatiet vērtību 50. Tā ir vērtība, kas B2 jāsasniedz.
Image -
Lodziņā ar nosaukumu Mainot mainīgās šūnas: ievadiet šūnu, kurā ir rādiuss, B1.
Image -
Atstājiet pārējās opcijas tādas, kādas tās ir pēc noklusējuma, un atlasiet Solve. Tiek veikta optimizācija, B1 vērtība tiek pielāgota, līdz B2 ir 50, un tiek parādīts dialogs Solver Results.
Image -
Atlasiet OK, lai saglabātu risinājumu.
Image
Šis vienkāršais piemērs parādīja, kā darbojas risinātājs. Šajā gadījumā mēs būtu varējuši vieglāk iegūt risinājumu citos veidos. Tālāk mēs apskatīsim dažus piemērus, kur Solver sniedz risinājumus, kurus būtu grūti atrast citādi.
Sarežģīta modeļa uzstādīšana, izmantojot Excel risinātāja pievienojumprogrammu
Excel ir iebūvēta funkcija, lai veiktu lineāro regresiju, iekļaujot datu kopu taisnu līniju. Daudzas izplatītas nelineāras funkcijas var linearizēt, kas nozīmē, ka lineāro regresiju var izmantot, lai pielāgotu funkcijas, piemēram, eksponenciālus. Sarežģītākām funkcijām Solver var izmantot, lai veiktu "mazāko kvadrātu samazināšanu". Šajā piemērā mēs apsvērsim vienādojumu ar formu ax^b+cx^d tālāk norādītajiem datiem.
Tas ietver šādas darbības:
- Sakārtojiet datu kopu ar x vērtībām A slejā un y vērtībām B kolonnā.
- Izveidojiet 4 koeficientu vērtības (a, b, c un d) kaut kur izklājlapā, tām var norādīt patvaļīgas sākuma vērtības.
-
Izveidojiet pielāgotu Y vērtību kolonnu, izmantojot vienādojumu formā ax^b+cx^d, kas atsaucas uz 2. darbībā izveidotajiem koeficientiem un x vērtībām A kolonnā. Ņemiet vērā, ka, lai kopētu formulu uz leju kolonnā, atsaucēm uz koeficientiem jābūt absolūtām, savukārt atsaucēm uz x vērtībām jābūt relatīvām.
Image -
Lai gan tas nav būtiski, jūs varat iegūt vizuālu norādi par vienādojuma piemērotību, attēlojot abas y kolonnas pret x vērtībām vienā XY izkliedes diagrammā. Ir lietderīgi oriģinālajiem datu punktiem izmantot marķierus, jo tās ir diskrētas vērtības ar troksni, un pielāgotajam vienādojumam izmantot līniju.
Image -
Tālāk mums ir nepieciešams veids, kā kvantitatīvi noteikt atšķirību starp datiem un mūsu pielāgoto vienādojumu. Standarta veids, kā to izdarīt, ir aprēķināt atšķirību kvadrātā summu. Trešajā kolonnā katrai rindai no pielāgotās vienādojuma vērtības tiek atņemta Y sākotnējā datu vērtība, un rezultāts tiek izlikts kvadrātā. Tātad D2 vērtību nosaka ar =(C2-B2)^2 Pēc tam tiek aprēķināta visu šo vērtību kvadrātā summa. Tā kā vērtības ir kvadrātā, tās var būt tikai pozitīvas.
Image -
Tagad esat gatavs veikt optimizāciju, izmantojot Solver. Ir četri koeficienti, kas jākoriģē (a, b, c un d). Jums ir arī viena objektīva vērtība, kas jāsamazina, starpību summa kvadrātā. Palaidiet risinātāju, kā norādīts iepriekš, un iestatiet risinātāja parametrus, lai tie atbilstu šīm vērtībām, kā parādīts tālāk.
Image -
Noņemiet atzīmi no opcijas Padarīt neierobežotos mainīgos kā nenegatīvus, tas liktu visiem koeficientiem iegūt pozitīvas vērtības.
Image -
Atlasiet Solve un pārskatiet rezultātus. Diagramma tiks atjaunināta, sniedzot labu norādi par piemērotību. Ja risinātājs pirmajā mēģinājumā nav piemērots, varat mēģināt palaist to vēlreiz. Ja atbilstība ir uzlabojusies, mēģiniet atrisināt no pašreizējām vērtībām. Pretējā gadījumā pirms atrisināšanas varat mēģināt manuāli uzlabot piemērotību.
Image - Kad ir iegūta laba atbilstība, varat iziet no risinātāja.
Modeļa atrisināšana iteratīvi
Dažreiz ir salīdzinoši vienkāršs vienādojums, kas dod izvadi kāda ievades izteiksmē. Tomēr, mēģinot novērst problēmu, nav iespējams atrast vienkāršu risinājumu. Piemēram, transportlīdzekļa patērētā jauda ir aptuveni aprēķināta ar P=av + bv^3, kur v ir ātrums, a ir rites pretestības koeficients un b ir koeficients aerodinamiskā pretestība. Lai gan šis ir diezgan vienkāršs vienādojums, to nav viegli pārkārtot, lai iegūtu vienādojumu ar ātrumu, kādu transportlīdzeklis sasniegs ar noteiktu jaudas ievadi. Tomēr mēs varam izmantot Solver, lai iteratīvi atrastu šo ātrumu. Piemēram, atrodiet ātrumu, kas sasniegts ar 740 W ieejas jaudu.
-
Izveidojiet vienkāršu izklājlapu ar ātrumu, koeficientiem a un b un no tiem aprēķināto jaudu.
Image -
Palaidiet Solver un ievadiet jaudu B5 kā mērķi. Iestatiet objektīvu vērtību 740 un atlasiet ātrumu B2 kā mainīgās šūnas, kas jāmaina. Atlasiet solve, lai sāktu risinājumu.
Image -
Risinātājs pielāgo ātruma vērtību, līdz jauda ir ļoti tuvu 740, nodrošinot mums nepieciešamo ātrumu.
Image - Modeļu risināšana šādā veidā bieži vien var būt ātrāka un mazāk pakļauta kļūdām nekā sarežģītu modeļu apvēršana.
Izpratne par dažādām risinātājā pieejamajām opcijām var būt diezgan sarežģīta. Ja jums ir grūtības iegūt saprātīgu risinājumu, bieži vien ir lietderīgi mainīt mainīgajām šūnām piemērot robežnosacījumus. Tās ir robežvērtības, kuras pārsniedzot, tās nevajadzētu koriģēt. Piemēram, iepriekšējā piemērā ātrums nedrīkst būt mazāks par nulli, un būtu iespējams arī iestatīt augšējo robežu. Tas būtu ātrums, par kuru esat pārliecināts, ka transportlīdzeklis nevar braukt ātrāk. Ja varat iestatīt robežas maināmo mainīgo šūnām, tas uzlabo arī citu uzlaboto opciju darbību, piemēram, vairāku palaišanu. Tādējādi tiks izpildīti vairāki dažādi risinājumi, sākot ar dažādām mainīgo sākotnējām vērtībām.
Risināšanas metodes izvēle var būt arī sarežģīta. Simplex LP ir piemērots tikai lineāriem modeļiem. Ja problēma nav lineāra, tā neizdosies ar ziņojumu, ka šis nosacījums nav izpildīts. Abas pārējās metodes ir piemērotas nelineārām metodēm. GRG Nonlinear ir ātrākais, taču tā risinājums var būt ļoti atkarīgs no sākotnējiem starta apstākļiem. Tam ir tāda elastība, ka tai nav nepieciešami mainīgie lielumi, lai noteiktu robežas. Evolutionary solver bieži vien ir visuzticamākais, taču tas prasa, lai visiem mainīgajiem būtu gan augšējā, gan apakšējā robeža, ko var būt grūti izstrādāt iepriekš.
Excel Solver pievienojumprogramma ir ļoti spēcīgs rīks, ko var izmantot daudzām praktiskām problēmām. Lai pilnībā piekļūtu Excel iespējām, mēģiniet apvienot Solver ar Excel makro.
