Excel risinātājs: rezultātu optimizēšana, ierobežojumu pievienošana un risinājumu saglabāšana kā scenāriji

Jūs iestatāt savu Excel risinātāja modeli, izmantojot dialoglodziņu Risinātāja parametri. Varat izmantot lodziņu Iestatīt mērķi, lai norādītu mērķa šūnu, un jūs izmantojat grupu Kam, lai norādītu programmai Excel Solver, ko vēlaties no mērķa šūnas: maksimālo iespējamo vērtību; minimālā iespējamā vērtība; vai noteikta vērtība. Visbeidzot, izmantojiet lodziņu Mainot mainīgās šūnas, lai norādītu šūnas, kuras Solver var izmantot, lai pievienotu vērtības, lai optimizētu rezultātu.

Excel Solver rezultātu optimizēšana

Kad Solver atrod risinājumu, varat izvēlēties Saglabāt Solver Solution vai Atjaunot sākotnējās vērtības. Ja izvēlaties Keep Solver Solution, programma Excel neatgriezeniski maina darblapu. Izmaiņas nevar atsaukt.

Kad jūsu Solver-gatavs darblapas modelis ir gatavs lietošanai, veiciet tālāk norādītās darbības, lai, izmantojot Solver, atrastu savam modelim optimālu rezultātu.

Izvēlieties Dati → Risinātājs.
Programmā Excel tiek atvērts dialoglodziņš Risinātāja parametri.

Lodziņā Iestatīt mērķi ievadiet sava modeļa mērķa šūnas adresi.
Ņemiet vērā: ja noklikšķināsit uz šūnas, lai to ievadītu, Solver automātiski ievada absolūtu šūnas adresi (piemēram, $14, nevis B14). Risinātājs darbojas labi jebkurā gadījumā.

Grupā Kam atlasiet opciju:

• Max: atgriež maksimālo iespējamo vērtību.
• Min: atgriež minimālo iespējamo vērtību.
• Vērtība: ievadiet skaitli, lai mērķa šūnā iestatītu šo skaitli.

  Piemēra modelim ir atlasīta vērtība Vērtība un tekstlodziņā ievadīta 0 .

Lodziņā Mainot mainīgās šūnas ievadiet to šūnu adreses, kuras vēlaties mainīt Solver, kamēr tas meklē risinājumu.
Piemērā mainīgās šūnas ir B4 un C4. Nākamajā attēlā parādīts pabeigtais Risinātāja parametru dialoglodziņš.

Aizpildīts Risinātāja parametru dialoglodziņš.

Noklikšķiniet uz Atrisināt.
Risinātājs ķeras pie lietas. Tā kā risinātājs strādā pie problēmas, vienu vai vairākas reizes var tikt parādīti dialoglodziņi Rādīt izmēģinājuma risinājumu.

Jebkurā parādītajā dialoglodziņā Rādīt izmēģinājuma risinājumu noklikšķiniet uz Turpināt, lai pārvietotu lietas tālāk.
Kad optimizācija ir pabeigta, programmā Excel tiek parādīts dialoglodziņš Risinātāja rezultāti.

Dialoglodziņš Risinātāja rezultāti un līdzsvara problēmas risinājums.

Atlasiet opciju Keep Solver Solution.
Ja nevēlaties pieņemt rezultātu, atlasiet opciju Atjaunot sākotnējās vērtības.

Noklikšķiniet uz Labi.

Varat lūgt Solver parādīt vienu vai vairākus pārskatus, kas sniedz papildu informāciju par rezultātiem. Dialoglodziņā Risinātāja rezultāti izmantojiet sarakstu Pārskati, lai atlasītu katru pārskatu, ko vēlaties skatīt:

• Atbilde: parāda informāciju par modeļa mērķa šūnu, mainīgajām šūnām un ierobežojumiem. Objektīvajām šūnām un mainīgajām šūnām Solver parāda sākotnējās un galīgās vērtības.
• Jutīgums: mēģina parādīt, cik jutīgs risinājums ir modeļa formulu izmaiņām. Jutīguma pārskata izkārtojums ir atkarīgs no izmantotā modeļa veida.
• Ierobežojumi: parāda mērķa šūnu un tās vērtību, kā arī mainīgās šūnas un to adreses, nosaukumus un vērtības.

Excel Solver var izmantot vienu no vairākām risināšanas metodēm. Dialoglodziņā Risinātāja parametri izmantojiet sarakstu Atlasīt risināšanas metodi, lai atlasītu kādu no šīm iespējām:

• Simplex LP: izmantojiet, ja darblapas modelis ir lineārs. Pēc iespējas vienkāršāk sakot, lineārais modelis ir tāds, kurā mainīgie netiek paaugstināti līdz pakāpēm un netiek izmantota neviena no tā sauktajām transcendentajām funkcijām, piemēram, SIN un COS.
• GRG Nelineārs: izmantojiet, ja darblapas modelis ir nelineārs un gluds. Vispārīgi runājot, vienmērīgs modelis ir tāds, kurā izmantotā vienādojuma diagrammā nav redzamas asas malas vai pārtraukumi.
• Evolucionārs: izmantojiet, ja darblapas modelis ir nelineārs un nevienmērīgs.

Vai jums ir jāuztraucas par kaut ko no tā? Gandrīz noteikti nē. Programmā Excel Solver pēc noklusējuma tiek izmantots GRG Nelinear, un tam vajadzētu darboties gandrīz jebko, ko darāt ar Solver.

Ierobežojumu pievienošana programmai Excel Solver

Reālā pasaule formulām liek ierobežojumus un nosacījumus. Rūpnīcas maksimālā jauda var būt 10 000 vienību dienā, darbinieku skaits uzņēmumā nevar būt negatīvs, un jūsu reklamēšanas izmaksas var būt ierobežotas līdz 10 procentiem no kopējiem izdevumiem.

Tāpat pieņemsim, ka veicat divu produktu rentabilitātes analīzi. Ja optimizāciju veicat bez jebkādiem ierobežojumiem, Solver var sasniegt kopējo peļņu 0, vienam produktam iestatot nelielus zaudējumus, bet otram - nelielu peļņu, kur zaudējumi un peļņa viens otru izslēdz. Faktiski, ja paskatās uz iepriekšējo attēlu, tas ir tieši tas, ko darīja Solver. Lai iegūtu patiesu līdzsvara risinājumu, iespējams, vēlēsities redzēt abas produkta peļņas vērtības kā 0.

Šādi ierobežojumi un nosacījumi ir piemēri tam, ko Solver sauc par ierobežojumiem. Ierobežojumu pievienošana liek Solveram atrast risinājumu, lai šie nosacījumi netiktu pārkāpti.

Lūk, kā palaist Solver ar optimizācijai pievienotiem ierobežojumiem.

Izvēlieties Dati → Risinātājs.
Programmā Excel tiek atvērts dialoglodziņš Risinātāja parametri.

Izmantojiet lodziņu Iestatīt mērķi, grupu Kam un lodziņu Mainot mainīgās šūnas, lai iestatītu risinātāju, kā aprakstīts iepriekš.

Noklikšķiniet uz Pievienot.
Programmā Excel tiek parādīts dialoglodziņš Pievienot ierobežojumu.

Lodziņā Šūnas atsauce ievadiet tās šūnas adresi, kuru vēlaties ierobežot.
Varat ierakstīt adresi vai atlasīt šūnu darblapā.

Nolaižamajā sarakstā atlasiet operatoru, kuru vēlaties izmantot.
Lielāko daļu laika jūs izmantojat salīdzināšanas operatoru, piemēram, vienāds ar (=) vai lielāks par (>). Izmantojiet operatoru int (vesels skaitlis), ja jums ir nepieciešams ierobežojums, piemēram, kopējais darbinieku skaits, lai tas būtu vesels skaitlis, nevis reāls skaitlis (tas ir, skaitlis ar decimāldaļu; jums nevar būt 10,5 darbinieki!). Izmantojiet bin (bināro) operatoru, ja jums ir ierobežojums, kuram ir jābūt TRUE vai FALSE (vai 1 vai 0).

Ja 5. darbībā izvēlējāties salīdzināšanas operatoru, lodziņā Ierobežojums ievadiet vērtību, ar kuru vēlaties ierobežot šūnu.
Šajā attēlā ir parādīts aizpildīta ierobežojuma pievienošanas dialoglodziņa piemērs. Piemēra modelī šis ierobežojums liek Solveram atrast tādu risinājumu, lai piepūšamā šautriņu dēļa (šūna B12) produkta peļņa būtu vienāda ar 0.

Aizpildīts dialoglodziņš Pievienot ierobežojumu.

Lai norādītu vairāk ierobežojumu, noklikšķiniet uz Pievienot un pēc vajadzības atkārtojiet 4.–6. darbību.

Piemēram, jūs pievienojat ierobežojumu, kas pieprasa, lai Dog Polisher produkta peļņa (šūna C12) būtu 0.

Noklikšķiniet uz Labi.

Programma Excel atgriežas dialoglodziņā Risinātāja parametri un parāda ierobežojumus sarakstlodziņā Subject to the Constraints.

Noklikšķiniet uz Atrisināt.

Jebkurā parādītajā dialoglodziņā Rādīt izmēģinājuma risinājumu noklikšķiniet uz Turpināt, lai pārvietotu lietas tālāk.
Tālāk esošajā attēlā ir parādīts līdzsvara risinājuma piemērs ar pievienotajiem ierobežojumiem. Ņemiet vērā, ka ne tikai kopējā peļņas šūna (B14) ir iestatīta uz 0, bet arī divas produkta peļņas šūnas (B12 un C12).

Risinātāja rezultātu dialoglodziņš un līdzsvara problēmas galīgais risinājums.

Atlasiet opciju Keep Solver Solution.
Ja nevēlaties pieņemt rezultātu, atlasiet opciju Atjaunot sākotnējās vērtības.

Noklikšķiniet uz Labi.

Varat pievienot ne vairāk kā 100 ierobežojumus. Turklāt, ja pirms risināšanas uzsākšanas ir jāveic izmaiņas ierobežojumā, atlasiet ierobežojumu sarakstlodziņā Ierobežojumu priekšmets, noklikšķiniet uz Mainīt un pēc tam veiciet pielāgojumus dialoglodziņā Mainīt ierobežojumu. Ja vēlaties dzēst ierobežojumu, kas jums vairs nav vajadzīgs, atlasiet ierobežojumu un pēc tam noklikšķiniet uz Dzēst.

Saglabājiet Excel Solver risinājumu kā scenāriju

Ikreiz, kad jums ir izklājlapas modelis, kurā tiek izmantota saskaņota ievades vērtību kopa, kas pazīstama kā mainīgās šūnas, jums ir tas, ko Excel sauc par scenāriju. Izmantojot Solver, šīs mainīgās šūnas ir tās mainīgās šūnas, tāpēc Solver risinājums programmā Excel ir sava veida scenārijs . Tomēr Solver nesniedz vienkāršu veidu, kā saglabāt un atkārtoti palaist konkrētu risinājumu. Lai apietu šo problēmu, varat saglabāt risinājumu kā scenāriju, ko vēlāk varat atsaukt, izmantojot Excel scenāriju pārvaldnieka līdzekli.

Lai saglabātu Solver risinājumu kā scenāriju, veiciet šīs darbības:

Izvēlieties Dati → Risinātājs.
Programmā Excel tiek atvērts dialoglodziņš Risinātāja parametri.

Izmantojiet lodziņu Iestatīt mērķi, grupu Kam, Mainot mainīgās šūnas un sarakstu Ierobežojumu priekšmets, lai iestatītu risinātāju, kā aprakstīts iepriekš.

Noklikšķiniet uz Atrisināt.

Ikreiz, kad tiek parādīts dialoglodziņš Rādīt izmēģinājuma risinājumu, izvēlieties Turpināt.
Kad optimizācija ir pabeigta, programmā Excel tiek parādīts dialoglodziņš Risinātāja rezultāti.

Noklikšķiniet uz Saglabāt scenāriju.
Programmā Excel tiek parādīts dialoglodziņš Saglabāt scenāriju.

Dialoglodziņā Scenārija nosaukums ierakstiet scenārija nosaukumu un pēc tam noklikšķiniet uz Labi.
Programma Excel atgriež jūs dialoglodziņā Risinātāja rezultāti.

Atlasiet opciju Keep Solver Solution.
Ja nevēlaties pieņemt rezultātu, atlasiet opciju Atjaunot sākotnējās vērtības.

Noklikšķiniet uz Labi.