Kā izmantot Excel 2019 risinātāju

Lai gan Excel komandas Data Table un Goal Seek darbojas lieliski vienkāršām problēmām, kurām ir jānosaka tiešā saikne starp ievadiem un rezultātiem formulā, jums ir jāizmanto Risinātāja pievienojumprogramma, lai risinātu sarežģītākas problēmas. Piemēram, izmantojiet rīku Risinātājs, lai atrastu labāko risinājumu, ja Excel modelī ir jāmaina vairākas ievades vērtības un šīm vērtībām un/vai izvades vērtībai ir jānosaka ierobežojumi.

Risinātāja pievienojumprogramma darbojas, izmantojot iteratīvas metodes, lai atrastu “labāko” risinājumu, ņemot vērā ievades datus, vēlamo risinājumu un jūsu noteiktos ierobežojumus. Katrā iterācijā programma izmanto izmēģinājumu un kļūdu metodi (pamatojoties uz lineāru vai nelineāru vienādojumu un nevienādību izmantošanu), kas mēģina tuvoties optimālajam risinājumam.

Lietojot pievienojumprogrammu Solver, ņemiet vērā, ka daudzām problēmām, īpaši sarežģītākajām, ir daudz risinājumu. Lai gan Risinātājs atgriež optimālo risinājumu, ņemot vērā sākuma vērtības, mainīgos, kas var mainīties, un jūsu definētos ierobežojumus, šis risinājums bieži vien nav vienīgais iespējamais un patiesībā var nebūt jums labākais risinājums. Lai pārliecinātos, ka atrodat labāko risinājumu, iespējams, vēlēsities palaist Solver vairāk nekā vienu reizi, pielāgojot sākotnējās vērtības katru reizi, kad problēmu atrisinat.

Iestatot problēmu Risinātāja pievienojumprogrammai savā Excel darblapā, definējiet šādus vienumus:

• Mērķa šūna: mērķa šūna darblapā, kuras vērtība ir jāpalielina, jāsamazina vai jāsasniedz, lai sasniegtu noteiktu vērtību. Ņemiet vērā, ka šajā šūnā ir jābūt formulai.
• Mainīgās šūnas: mainīgās šūnas darblapā, kuru vērtības ir jākoriģē, līdz tiek atrasta atbilde.
• Ierobežojuma šūnas: šūnas, kurās ir ierobežojumi, ko uzliekat mainīgajām vērtībām mainīgā šūnās un/vai mērķa šūnā.

Kad esat pabeidzis problēmas definēšanu ar šiem parametriem un pievienojumprogrammai Risinātājs atrisina problēmu, programma atgriež optimālo risinājumu, mainot vērtības jūsu darblapā. Šajā brīdī varat izvēlēties saglabāt darblapā veiktās izmaiņas vai atjaunot darblapas sākotnējās vērtības. Varat arī saglabāt risinājumu kā scenāriju, lai to skatītu vēlāk, pirms atjaunojat sākotnējās vērtības.

Varat izmantot Risinātāja pievienojumprogrammu kopā ar scenāriju pārvaldnieku, lai palīdzētu iestatīt risināmu problēmu vai saglabātu risinājumu, lai to varētu skatīt vēlāk. Mainīgās šūnas, ko definējat Scenario Manager, Risinātājs automātiski uztver un izmanto, kad atlasāt šo komandu, un otrādi. Varat arī saglabāt problēmas risinātāja risinājumu kā scenāriju (noklikšķinot uz pogas Saglabāt scenāriju dialoglodziņā Risinātājs), ko pēc tam varat skatīt, izmantojot scenāriju pārvaldnieku.

Problēmas iestatīšana un definēšana programmā Excel 2019

Pirmais solis, lai iestatītu problēmu, pie kuras jāstrādā, ir izveidot darblapas modeli, kuram definēsiet mērķa šūnu, mainīgo šūnas un ierobežojuma šūnas.

Ņemiet vērā, ka Solver ir pievienojumprogramma. Tas nozīmē, ka, pirms varat to izmantot, jums ir jāpārliecinās, vai pievienojumprogramma Risinātājs joprojām ir ielādēta, par ko liecina poga Risinātājs, kas atrodas grupā Analīze lentes cilnes Dati beigās. Ja šīs pogas nav, varat ielādēt programmu Solver, atverot Excel opciju dialoglodziņa cilni Add-Ins (Alt+FTAA) un pēc tam noklikšķinot uz pogas Aiziet, pārliecinoties, ka Excel pievienojumprogrammas ir parādītas nolaižamajā izvēlnē Pārvaldīt. saraksta lodziņā tieši pa kreisi. Pēc tam dialoglodziņā Add-Ins atzīmējiet izvēles rūtiņu Risinātāja pievienojumprogramma, lai atzīmētu to, pirms noklikšķināt uz Labi, lai aizvērtu dialoglodziņu un atkārtoti ielādētu pievienojumprogrammu.

Lai definētu un atrisinātu problēmu ar pievienojumprogrammu Solver pēc pievienojumprogrammas ielādes un darblapas modeļa izveides, veiciet šīs darbības:

Noklikšķiniet uz komandas pogas Risinātājs grupā Analīze, kas atrodas lentes datu cilnes beigās.

Programmā Excel tiek atvērts dialoglodziņš Risinātāja parametri.

Modelim piemērojamo parametru norādīšana dialoglodziņā Risinātāja parametri.

Noklikšķiniet uz mērķa šūnas darblapā vai ievadiet tās atsauci uz šūnas vai diapazona nosaukumu tekstlodziņā Iestatīt mērķi.

Pēc tam jums jāizvēlas iestatījums Kam. Noklikšķiniet uz opcijas Max, ja vēlaties, lai mērķa šūnas vērtība būtu pēc iespējas lielāka. Noklikšķiniet uz opcijas Min., ja vēlaties, lai mērķa šūnas vērtība būtu pēc iespējas mazāka. Noklikšķiniet uz opcijas pogas Value Of un pēc tam ievadiet vērtību saistītajā tekstlodziņā, ja vēlaties, lai mērķa šūnas vērtība sasniegtu noteiktu vērtību.

Noklikšķiniet uz atbilstošās opcijas pogas opcijas dialoglodziņa sadaļā Kam. Ja atlasāt opcijas pogu Value Of, attiecīgajā tekstlodziņā ievadiet atbilstošo vērtību.

Pēc tam norādiet mainīgās šūnas, tas ir, tās, kuras Solver var mainīt, lai sasniegtu jūsu Vienāds ar mērķi.

Noklikšķiniet uz tekstlodziņa Mainot mainīgās šūnas un pēc tam atlasiet mainītās šūnas darblapā vai ievadiet to šūnu atsauces vai diapazona nosaukumu tekstlodziņā.

Atcerieties, ka, lai darblapā atlasītu šūnas, kas nav blakus esošajām šūnām, noklikšķinot uz katras atlases šūnas, turiet nospiestu taustiņu Ctrl. Lai programma Excel izvēlētos mainīgās šūnas jūsu vietā, pamatojoties uz atlasīto mērķa šūnu, noklikšķiniet uz pogas Uzminēt šī tekstlodziņa labajā pusē.

Pirms Solver pielāgošanas modelim varat pievienot ierobežojumus mērķa šūnai vai jebkurai mainīgajai šūnai, kas nosaka tās robežas, pielāgojot vērtības.

(Neobligāti) Dialoglodziņā Risinātāja parametri noklikšķiniet uz pogas Pievienot pa labi no sarakstlodziņa Subject to the Constraints.

Šī darbība atver dialoglodziņu Pievienot ierobežojumu. Definējot ierobežojumu, izvēlieties šūnu, kuras vērtību vēlaties ierobežot, vai atlasiet šūnu darblapā vai ievadiet tās šūnas atsauci tekstlodziņā Šūnas atsauce. Pēc tam labajā pusē esošajā nolaižamā saraksta lodziņā atlasiet relāciju (=, <=,>= vai int, ja tas ir vesels skaitlis, vai bin binārajam skaitlim ) un (ja vien neesat izvēlējies int vai bin ) ievadiet atbilstošo vērtību vai šūnas atsauci Ierobežojuma tekstlodziņš.

Lai turpinātu pievienot ierobežojumus citām šūnām, ko izmanto risinātājs, noklikšķiniet uz pogas Pievienot, lai pievienotu ierobežojumu, un notīriet tekstlodziņus dialoglodziņā Pievienot ierobežojumu. Pēc tam atkārtojiet 5. darbību, lai pievienotu jaunu ierobežojumu. Kad esat pabeidzis definēt ierobežojumus mērķa šūnai un mainīt vērtības modelī, noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu Pievienot ierobežojumu un atgrieztos dialoglodziņā Risinātāja parametri (kurā tagad ir norādīti jūsu ierobežojumi sarakstlodziņā Subject to the Constraints).

(Neobligāti) Noņemiet atzīmi no izvēles rūtiņas Padarīt neierobežotus mainīgos kā nenegatīvus, ja vēlaties atļaut negatīvas vērtības, ja mainīgā šūnas nav pakļautas ierobežojumiem.
Pēc noklusējuma Risinātāja pievienojumprogramma izmanto GRG (ģeneralizēts samazināts gradients) nelineāro metodi, lai atrisinātu modeli, kura parametrus jūs iestatāt, kas pazīstams kā ļoti efektīvs veids, kā atrisināt vienmērīgas nelineāras problēmas. Lai izmantotu LP Simplex metodi (lineārajai programmēšanai pēc Simplex algoritma) vai Evolutionary dzinēju nevienmērīgu problēmu risināšanai, jums jāveic 7. darbība.

(Neobligāti) Nolaižamajā sarakstā Select a Solving Method atlasiet LP Simplex vai Evolutionary, lai izmantotu kādu no šīm metodēm nevienmērīgu problēmu risināšanai.

Noklikšķiniet uz pogas Risināt, lai Risinātājs atrisinātu problēmu, kā to definējāt dialoglodziņā Risinātāja parametri.

Problēmas risināšana, izmantojot programmu Excel Solver

Noklikšķinot uz pogas Risināt, dialoglodziņš Risinātāja parametri pazūd, un statusa josla norāda, ka Risinātājs iestata problēmu, un pēc tam informē jūs par problēmas risināšanas gaitu, parādot starpposma (vai izmēģinājuma) numuru. risinājumi, kā tie tiek izmēģināti. Lai jebkurā brīdī pārtrauktu risinājuma procesu, pirms programma Excel aprēķina pēdējo iterāciju, nospiediet taustiņu Esc. Pēc tam programmā Excel tiek parādīts dialoglodziņš Rādīt izmēģinājuma risinājumu, informējot, ka risinājuma process ir apturēts. Lai turpinātu risinājuma procesu, noklikšķiniet uz pogas Turpināt. Lai pārtrauktu risinājuma procesu, noklikšķiniet uz pogas Apturēt.

Kad programma Excel pabeidz risināšanas procesu, tiek parādīts dialoglodziņš Risinātāja rezultāti. Šis dialoglodziņš informē, vai risinātājs varēja atrast risinājumu, ņemot vērā mērķa šūnu, mainīgās šūnas un problēmai definētos ierobežojumus. Lai saglabātu izmaiņas, ko risinātājs veic jūsu darblapas modelī, atstājiet atlasīto pogu Saglabāt risinātāja risinājumu un noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu Solver Results. Lai darblapā atgrieztu sākotnējās vērtības, noklikšķiniet uz opcijas pogas Atjaunot sākotnējās vērtības. Lai saglabātu izmaiņas kā scenāriju pirms sākotnējo vērtību atjaunošanas, noklikšķiniet uz pogas Saglabāt scenāriju un piešķiriet pašreizējam scenārijam nosaukumu, pirms noklikšķināt uz opcijas Atjaunot sākotnējās vērtības un pogas Labi.

Dialoglodziņš Risinātāja rezultāti, kas parāda, ka Risinātājs ir atradis problēmas risinājumu.

Atšķirībā no komandas Goal Seek lietošanas, pēc noklikšķināšanas uz opcijas Saglabāt risinājumu pogas dialoglodziņā Risinātāja rezultāti, jūs nevarat izmantot ātrās piekļuves rīkjoslā esošo komandas pogu Atsaukt, lai darblapā atjaunotu sākotnējās vērtības. Ja vēlaties pārslēgties starp darblapas skatu “pirms” un “pēc”, izmaiņas ir jāsaglabā, izmantojot pogu Saglabāt scenāriju un pēc tam atlasiet opcijas poga Atjaunot sākotnējās vērtības. Tādā veidā sākotnējā darblapā varat saglabāt skatu “pirms” un izmantot scenāriju pārvaldnieku, lai parādītu risinātāja izveidoto skatu “pēc”.

Programmas Excel risinātāja opciju maiņa

Lielākajai daļai problēmu ir piemērotas Risinātāja izmantotās noklusējuma opcijas. Tomēr dažās situācijās pirms risinājuma procesa sākšanas, iespējams, vēlēsities mainīt dažas Risinātāja opcijas. Lai mainītu risinājuma opcijas, dialoglodziņā Risinātāja parametri noklikšķiniet uz pogas Opcijas. Pēc tam Excel atver dialoglodziņu Opcijas ar atlasītu cilni Visas metodes, kurā varat veikt visas nepieciešamās izmaiņas.

Risinājuma opciju modificēšana dialoglodziņā Opcijas.

Excel 2019 risinātāja opciju iestatījumi

Pēc opciju maiņas noklikšķiniet uz Labi, lai atgrieztos dialoglodziņā Solver Parameters; no šejienes varat noklikšķināt uz pogas Risināt, lai sāktu risinājuma procesu ar jaunajiem risinājuma iestatījumiem, kurus tikko mainījāt.

Ja izmantojat noklusējuma GRG (ģeneralizēts samazināts gradients) nelineāro vai evolucionāro metodi, varat iestatīt papildu risinātāja iestatījumus, izmantojot opcijas dialoglodziņa Opcijas cilnēs GRG Nelineārs un Evolucionārs. Šīs opcijas ietver Konverģēšanas, Populācijas lieluma un Random Seed iestatījumu mainīšanu kādai no šīm konkrētajām metodēm.

Modeļa problēmas saglabāšana un ielāde programmā Excel 2019

Pēdējās izmantotās mērķa šūnas, mainīgās šūnas, ierobežojumu šūnas un risinātāja opcijas tiek saglabātas kā daļa no Excel darblapas, ātrās piekļuves rīkjoslā noklikšķinot uz pogas Saglabāt (Ctrl+S). Ja definējat citas problēmas tai pašai darblapai, kuru vēlaties saglabāt, dialoglodziņā Risinātāja opcijas jānoklikšķina uz pogas Saglabāt modeli un jānorāda šūnas atsauce vai diapazona nosaukums aktīvajā darblapā, kurā vēlaties atrast problēmas parametrus. ievietots.

Noklikšķinot uz pogas Ielādēt/saglabāt, programmā Excel tiek atvērts dialoglodziņš Ielādēt/saglabāt modeli, kurā ir tekstlodziņš Atlasīt modeļa apgabalu. Šajā tekstlodziņā ir šūnu atsauces diapazonam, kas ir pietiekami liels, lai saturētu visus problēmas parametrus, sākot ar aktīvo šūnu. Lai saglabātu problēmas parametrus šajā diapazonā, noklikšķiniet uz Labi. Ja šajā diapazonā ir iekļautas šūnas ar esošiem datiem, pirms noklikšķināšanas uz Labi ir jāmaina šūnas atsauce šajā tekstlodziņā, lai programma Excel neaizstātu esošos datus.

Kad esat noklikšķinājis uz Labi, programma Excel kopē problēmas parametrus norādītajā diapazonā. Šīs vērtības tiek saglabātas kā daļa no darblapas nākamreiz, kad saglabājat darbgrāmatu. Lai atkārtoti izmantotu šos problēmas parametrus, risinot problēmu, jums vienkārši jāatver dialoglodziņš Risinātāja opcijas, noklikšķiniet uz pogas Ielādēt/Saglabāt, lai atvērtu dialoglodziņu Ielādēt/saglabāt modeli, noklikšķiniet uz pogas Ielādēt un pēc tam atlasiet diapazonu, kurā ir saglabāts. problēmas parametri. Noklikšķinot uz Labi dialoglodziņā Ielādēt modeli, programma Excel ielādē parametrus no šī šūnu diapazona atbilstošajos tekstlodziņos dialoglodziņā Risinātāja parametri. Pēc tam varat aizvērt dialoglodziņu Risinātāja opcijas, noklikšķinot uz Labi, un problēmu var atrisināt, izmantojot šos parametrus, noklikšķinot uz komandas pogas Atrisināt.

Atcerieties, ka varat izmantot pogu Atiestatīt visu ikreiz, kad vēlaties notīrīt visus iepriekšējai problēmai definētos parametrus un atgriezt Risinātāja opciju noklusējuma vērtības.

Risinātāja pārskatu izveide programmā Excel 2019

Izmantojot rīku Risinātājs, varat izveidot trīs dažādu veidu pārskatus:

• Atbilžu ziņojums: uzskaita mērķa šūnu un mainīgās šūnas ar to sākotnējām un galīgajām vērtībām, kā arī problēmas risināšanā izmantotajiem ierobežojumiem.
• Jutīguma pārskats: norāda, cik jutīgs ir optimālais risinājums attiecībā uz izmaiņām formulās, kas aprēķina mērķa šūnu un ierobežojumus. Pārskatā ir parādītas mainīgās šūnas ar to galīgajām vērtībām un katras šūnas samazināto gradientu . (Samazinātais gradients mēra mērķi uz vienības pieaugumu mainīgajā šūnā.) Ja definējāt ierobežojumus, jutīguma pārskatā tie tiek uzskaitīti ar to galīgajām vērtībām un Lagranža reizinātāju katram ierobežojumam. (Lagranža reizinātājs mēra mērķi uz vienības pieaugumu, kas parādās ierobežojuma vienādojuma labajā pusē.)
• Ierobežojumu pārskats: parāda mērķa šūnu un mainīgās šūnas ar to vērtībām, apakšējo un augšējo robežu un mērķa rezultātiem. Apakšējā robeža ir zemākā vērtība, kāda var būt mainīgai šūnai, vienlaikus fiksējot visu pārējo šūnu vērtības un izpildot ierobežojumus. Augšējā robeža ir lielākā vērtība, kas to darīs.

Programma Excel ievieto katru ziņojumu, ko ģenerējat Risinātāja problēmai, atsevišķā darbgrāmatas darblapā. Lai ģenerētu vienu (vai visus) no šiem pārskatiem, dialoglodziņa Risinātāja rezultāti sarakstlodziņā Pārskati atlasiet ziņojuma veidu (Atbilde, Jutība vai Ierobežojumi). Lai atlasītu vairāk nekā vienu pārskatu, vienkārši noklikšķiniet uz pārskata nosaukuma.

Noklikšķinot uz Labi, lai aizvērtu dialoglodziņu Risinātāja rezultāti (pēc izvēles starp opcijām Saglabāt risinātāja risinājumu un Atjaunot sākotnējās vērtības), programma Excel ģenerē atskaiti (vai atskaites), ko atlasījāt jaunā darblapā, ko tā pievieno darbgrāmatas sākumam. . (Ziņojuma veidlapa cilnes ir nosaukta pēc pārskata veidu, kā atbildi ziņojumā 1, jutīgums ziņojums 1, un Limits ziņojums 1. )