Probleemi määratlemine ja lahendamine Solveri abil

Solver on Microsoft Exceli lisandprogramm, mida saate kasutada juhul-kui-analüüs jaoks. Solveriga saate otsida teatud lahtri (sihtlahtri) valem jaoks optimaalse (maksimaalse või minimaalse) väärtuse, lähtudes töölehe muudele valemilahtritele seatud piirangutest või piiridest. Solver kasutab siht- ja piirangulahtrite valemi väärtuste arvutamisel otsustusmuutujaid ehk muutuvaid lahtreid. Solver kohandab otsustusmuutujate lahtriväärtusi nii, et need täidaksid piirangulahtrite tingimused ja annaksid sihtlahtri jaoks teie soovitud tulemuse.

Lühidalt öeldes saate Solveri abil kindlaks teha ühe lahtri maksimum- või miinimumväärtust teiste lahtrite muutmise teel. Näiteks saate muuta kavandatud reklaamieelarve summat ja vaadata muudatuse mõju eeldatavale kasumile.

Windows macOS Veeb

Järgmises näites mõjutab iga kvartali reklaamitase müüdud ühikute arvu, määratledes kaudselt müügi aastatulu suuruse, seotud kulutused ja kasumi. Solveriga saab muuta reklaami (otsustusmuutujate lahtrid B5:C5) kvartalieelarveid eelarve kogusumma piiranguni 20 000 € (lahter F5), kuni kogukasum (sihtlahter F7) ulatub maksimaalse võimaliku suuruseni. Muutuvate lahtrite väärtuste abil arvutatakse iga kvartali kasum, seega on need seotud valemi sihtlahtriga F7 ehk =SUM(Q1 Kasum:Q2 Kasum).

Enne Solveri väärtustamist

1. Muutuvad lahtrid

2. Piirangulahter

3. Sihtlahter

Pärast Solveri käivitamist on uued väärtused järgmised.

Pärast Solveri väärtustamist

Klõpsake menüü Andmed jaotise Analüüs nuppu Solver.

Märkus.: Kui nupp Solver või jaotis Analüüs pole saadaval, tuleb Solveri lisandmoodul aktiveerida. Vt Solveri lisandmooduli aktiveerimine.
Sisestage väljal Sea eesmärk lahtriviide või sihtlahtri nimi. Sihtlahter peab sisaldama valemit.
Tehke ühte järgmistest.
- Kui soovite, et sihtlahtri väärtus oleks võimalikult suur, klõpsake raadionuppu Max.
- Kui soovite, et sihtlahtri väärtus oleks võimalikult väike, klõpsake raadionuppu Min.
- Kui soovite, et sihtlahtril oleks kindel väärtus, klõpsake raadionuppu Väärtus ja tippige seejärel väärtus väljale.
- Sisestage väljale Muutuvate lahtrite muutmise teel iga otsustusmuutuja lahtrivahemiku nimi või viide. Eraldage mittekülgnevad viited semikoolonitega. Muutuvad lahtrid peavad sihtlahtriga olema seotud kas otseselt või kaudselt. Saate määrata kuni 200 muutuvat lahtrit.
Sisestage väljale Piiratav kõik piirangud, mida soovite rakendada, tehes ühte järgmistest.
1. Klõpsake dialoogiboksi Solveri parameetrid nuppu Lisa.
2. Sisestage väljale Lahtri viide selle lahtrivahemiku viide või nimi, mille puhul soovite väärtust piirata.
3. Klõpsake seost ( <=, =, >=, int, bin või dif ), mida soovite viidatud lahtri ja kitsenduse vahel. Kui klõpsate väärtust täisarv, kuvatakse väljal Kitsendustäisarv. Kui klõpsate nuppu binaarkuju, kuvatakse väljal Kitsendusbinaarkuju. Kui klõpsate nuppu dif, kuvatakse väljal Kitsendusväärtus alldifferent.
4. Kui valite väljal Piirang seose "<=", "=" või ">=", sisestage lahtriviide või nimi või valem.
5. Tehke ühte järgmistest.
  - Piirangu aktsepteerimiseks ja veel ühe lisamiseks klõpsake nuppu Lisa.
  - Piirangu aktsepteerimiseks ja dialoogiboksi Solver Parameters naasmiseks klõpsake nuppu OK.Märkus Seoseid int, bin ja dif saab rakendada ainult otsustusmuutujate lahtrite kitsendustes.
    
    Olemasolevat kitsendust saate muuta või kustutada, tehes ühte järgmistest.
6. Klõpsake dialoogiboksi Solveri parameetrid piirangut, mida soovite muuta või kustutada.
7. Klõpsake nuppu Muuda ja tehke soovitud muudatused, või klõpsake nuppu Kustuta.
Klõpsake nuppu Lahend ja tehke ühte järgmistest.
- Solveri leitud väärtuste töölehel hoidmiseks klõpsake dialoogiboksi Solveri tulemid raadionuppu Säilita Solveri lahend.
- Enne nupu Lahenda klõpsamist olnud algväärtuste taastamiseks klõpsake raadionuppu Taasta algväärtused.
- Lahendamisprotsessi saab katkestada, vajutades paoklahvi (Esc). Microsoft Excel arvutab töölehe otsustusmuutujalahtritest viimati leitud väärtustega ümber.
- Pärast Solveri pakutavat lahendust teie lahendusel põhineva aruande loomiseks klõpsake väljal Aruanded aruandetüüpi ja siis nuppu OK. Aruanne luuakse teie töövihiku uuele töölehele. Kui Solver ei leia lahendust, on saadaval ainult teatud aruanded või mitte ühtegi aruannet.
- Kui soovite otsustusmuutujalahtri väärtused salvestada stsenaariumina, mida saaksite hiljem kuvada, klõpsake dialoogiboksis Solveri tulemid käsku Salvesta stsenaarium ning tippige stsenaariumi nimi väljale Stsenaariumi nimi.

Pärast ülesande määratlemist klõpsake dialoogiboksi Solveri parameetrid nuppu Suvandid.
Iga proovilahendi väärtuste kuvamiseks märkige dialoogiboksi Suvandid ruut Kuva iteratsiooni tulemid ja siis klõpsake nuppu OK.
Klõpsake dialoogiboksi Solveri parameetrid nuppu Lahenda.
Tehke dialoogiboksis Proovilahendi kuvamine ühte järgmistest.
- Lahendusprotsessi peatamiseks ning dialoogiboksi Solveri tulemid kuvamiseks klõpsake nuppu Peata.
- Lahendusprotsessi jätkamiseks ning järgmise proovilahendi kuvamiseks klõpsake nuppu Jätka.

Klõpsake dialoogiboksi Solveri parameetrid nuppu Suvandid.
Valige või sisestage suvandite soovitud väärtused dialoogiboksi vahekaartidel Kõik meetodid, GRG Nonlinear ja Evolutionary.

Klõpsake dialoogiboksis Solveri parameetrid nuppu Laadi/salvesta.
Sisestage mudeliala jaoks lahtrivahemik ja siis klõpsake kas käsku Salvesta või Laadi.

Mudeli salvestamisel sisestage viide selle tühjade lahtrite vertikaalse vahemiku esimesele lahtrile, kuhu soovite ülesandemudeli paigutada. Mudeli laadimisel sisestage viide ülesandemudelit sisaldavate lahtrite koguvahemikule.

Näpunäide.: Dialoogiboksi Solveri parameetrid viimaseid valikuid saate salvestada koos töölehega. Selleks salvestage töövihik. Igal töövihiku töölehel võib olla oma Solveri valikud ja kõik need salvestatakse. Töölehe jaoks saate määratleda ka mitu probleemi, klõpsates probleemide eraldi salvestamiseks nuppu Laadi/salvesta .

Dialoogiboksis Solveri parameetrid saate valida ükskõik millise kolmest järgmisest algoritmist või lahendusmeetodist.

Generalized Reduced Gradient (GRG) Nonlinear – kasutage silutud mittelineaarsete ülesannete lahendamiseks.
LP Simplex – kasutage lineaarsete ülesannete lahendamiseks.
Evolutionary – kasutage silumata ülesannete lahendamiseks.

NB!: Esmalt tuleks lubada Solveri lisandmoodul. Lisateavet leiate teemast Solveri lisandmooduli aktiveerimine.

Järgmises näites mõjutab iga kvartali reklaamitase müüdud ühikute arvu, määratledes kaudselt müügi aastatulu suuruse, seotud kulutused ja kasumi. Solveriga saab muuta reklaami (otsustusmuutujate lahtrid B5:C5) kvartalieelarveid eelarve kogusumma piiranguni 20 000 € (lahter D5), kuni kogukasum (sihtlahter D7) ulatub maksimaalse võimaliku suuruseni. Muutuvate lahtrite väärtuste abil arvutatakse iga kvartali kasum, seega on need seotud valemi sihtlahtriga D7 ehk =SUM(Q1 Kasum:Q2 Kasum).

Example Solver evaluation

Muutuvate lahtrite Viiktekst 1

Callout 2 piiratud lahter

Callout 3 sihtlahter

Pärast Solveri käivitamist on uued väärtused järgmised.

Example Solver evaluation with new values

Klõpsake nuppu Andmed > Solver.
Sisestage väljale Set Objective (Määrake eesmärk) lahtriviide või sihtlahtri nimi.

Märkus.: Sihtlahter peab sisaldama valemit.

Tehke ühte järgmistest.

Toiming	Toimimisviis
Sihtlahtri väärtuse muutmine võimalikult suureks	Valige Max.
Sihtlahtri väärtuse muutmine võimalikult väikseks	Valige Min.
Sihtlahtrile teatud väärtuse määramine	Valige Value of (Väärtus) ja seejärel tippige väljale väärtus.

Sisestage väljale By Changing Variable Cells (Muutuvaid lahtreid muutes) iga otsustusmuutujalahtrite vahemiku kohta nimi või viide. Mittekülgnevad viited saate eraldada komaga.

Muutuvad lahtrid peavad olema otseselt või kaudselt sihtlahtriga seotud. Saate määrata kuni 200 muutuvat lahtrit.

Sisestage väljale Subject to the Constraints (Piiratav) kõik piirangud, mida soovite rakendada.

Piirangu lisamiseks tehke järgmist.

Klõpsake dialoogiboksi Solveri parameetrid nuppu Lisa.
Sisestage väljale Lahtri viide selle lahtrivahemiku viide või nimi, mille puhul soovite väärtust piirata.
Valige seose hüpikmenüüs <= lahtri ja piirangu vahele soovitud seos. Kui valite sümboli <=, = või >=, siis tippige väljale Constraint (Piirang) arv, lahtriviide või -nimi või valem.

Märkus.: Seoseid int, bin ja dif saate rakendada muutuvate lahtrite piirangutele.
Tehke ühte järgmistest.

Toiming	Toimimisviis
Piirangu aktsepteerimine ja uue lisamine	Klõpsake nuppu Add (Lisa).
Piirangu aktsepteerimine ja dialoogiboksi Solver Parameters (Solveri parameetrid) naasmine	Klõpsake nuppu OK.

Klõpsake nuppu Solve (Lahend) ja tehke ühte järgmistest.

Toiming	Toimimisviis
Lahendväärtuste hoidmine lehel	Klõpsake dialoogiboksis Solver Results (Solveri tulemid) käsku Keep Solver Solution (Säilita Solveri lahend).
Lähteandmete taastamine	Klõpsake käsku Restore Original Values (Taasta algväärtused).

Märkused:

Lahendamisprotsessi saab katkestada paoklahvi (Esc) abil. Excel arvutab töölehe kohandatavatest lahtritest viimati leitud väärtustega ümber.
Pärast Solveri pakutavat lahendust teie lahendusel põhineva aruande loomiseks klõpsake väljal Reports (Aruanded) aruandetüüpi ja siis nuppu OK. Aruanne luuakse teie töövihiku uuele töölehele. Kui Solver ei leia lahendust, siis pole aruande loomise suvand saadaval.
Kui soovite kohandatud lahtriväärtused salvestada stsenaariumina, mida saaksite hiljem kuvada, klõpsake dialoogiboksis Solver Results (Solveri tulemid) käsku Save Scenario (Salvesta stsenaarium) ning tippige stsenaariumi nimi väljale Scenario Name (Stsenaariumi nimi).

Klõpsake nuppu Andmed > Solver.
Pärast ülesande määratlemist klõpsake dialoogiboksi Solver Parameters (Solveri parameetrid) nuppu Options (Suvandid).
Märkige iga proovilahendi väärtuste kuvamiseks ruut Show Iteration Results (Kuva iteratsiooni tulemid) ja siis klõpsake nuppu OK.
Klõpsake dialoogiboksi Solver Parameters (Solveri parameetrid) nuppu Solve (Lahenda).

Tehke dialoogiboksis Show Trial Solution (Proovilahendi kuvamine) ühte järgmistest.

Toiming	Toimimisviis
Lahendusprotsessi peatamine ning dialoogiboksi Solver Results (Solveri tulemid) kuvamine	Klõpsake nuppu Stop (Peata).
Lahendusprotsessi jätkamine ning järgmise proovilahendi kuvamine	Klõpsake nuppu Continue (Jätka).

Klõpsake nuppu Andmed > Solver.

Valige Options (Suvandid) ja seejärel dialoogiboksis Options (Suvandid) või Solver Options (Solveri suvandid) üks järgmistest.

Toiming	Toimimisviis
Lahendusaja ja iteratsioonide määramine	Tippige vahekaardi All Methods (Kõik meetodid) jaotise Solving Limits (Lahendamise piirväärtused) väljale Max Time (Seconds) (Maksimumaeg (sekundites)) sekundite arv, mille soovite lahendusajaks lubada. Tippige seejärel väljale Iterations (Iteratsioonid) iteratsioonide lubatud maksimumarv. Märkus.: Kui lahendusprotsess võtab enne lahenduse leidmist maksimumaja või maksimaalse iteratsioonide arvu, kuvatakse Solveris dialoogiboks Show Trial Solution (Proovilahendi kuvamine).
Täpsusastme määramine	Tippige vahekaardi All Methods (Kõik meetodid) väljale Constraint Precision (Piirangu täpsus) soovitud täpsusaste. Mida väiksem arv, seda suurem on täpsus.
Koonduvusastme määramine	Tippige vahekaardi GRG Nonlinear või Evolutionary väljale Convergence (Koonduvus) suhteliste muutuste arv, mille soovite viimasele viiele iteratsioonile lubada, enne kui Solver peatab protsessi lahendi andmisega. Mida väiksem arv, seda väiksem suhteline muutus on lubatud.

Klõpsake nuppu OK.
Klõpsake dialoogiboksi Solver Parameters (Solveri parameetrid) nuppu Solve (Lahenda) või Close (Sule).

Klõpsake nuppu Andmed > Solver.
Valige Load/Save (Salvesta/laadi), sisestage mudeliala jaoks lahtrivahemik ja siis klõpsake kas käsku Save (Salvesta) või Load (Laadi).

Mudeli salvestamisel sisestage viide selle tühjade lahtrite vertikaalse vahemiku esimesele lahtrile, kuhu soovite ülesandemudeli paigutada. Mudeli laadimisel sisestage viide ülesandemudelit sisaldavate lahtrite koguvahemikule.

Näpunäide.: Dialoogiboksi Solveri parameetrid viimaseid valikuid saate salvestada koos lehega, salvestades töövihiku. Igal töövihiku lehel võib olla oma Solveri valikud ja kõik need salvestatakse. Lehe jaoks saate määratleda ka mitu probleemi, klõpsates probleemide eraldi salvestamiseks nuppu Laadi/salvesta .

Klõpsake nuppu Andmed > Solver.
Valige hüpikmenüüs Select a Solving Method (Vali lahendusmeetod) üks järgmistest.

Lahendusmeetod	Kirjeldus
GRG (Generalized Reduced Gradient) Nonlinear	Mudelite puhul, mis kasutavad enamikku Exceli funktsioone peale funktsioonide IF, CHOOSE, LOOKUP jt etapifunktsioonide.
Simplex LP	Seda meetodit saate kasutada lineaarsete programeerimisülesannete korral. Mudeli valemites, mis sõltuvad muutuvatest lahtritest, peaksid olema SUM, SUMPRODUCT, + - ja *.
Evolutionary	See meetod, mis põhineb geneetilistel algoritmidel, sobib kõige paremini siis, kui mudelis on IF, CHOOSE või LOOKUP koos argumentidega, mis sõltuvad muutuvatest lahtritest.

Märkus.: Solveri programmikoodi teatud osadele on kehtestanud autoriõiguse 1990-2010 Frontline Systems, Inc. Teatud osadele on kehtestanud autoriõiguse 1989, Optimal Methods, Inc.

Kuna Exceli veebirakendus ei toeta lisandmooduliprogramme, ei saa lisandmoodulit Solver kasutada andmete mõjuanalüüsi tegemiseks, et leida optimaalseid lahendusi.

Kui teil on Exceli töölauarakendus, saate lisandmooduli Solver kasutamiseks avada töövihiku nupuga Ava Excelis.

Täiendav abi Solveri kasutamisel

Solveri üksikasjalikuma abi kontaktteave:

Frontline Systems, Inc. Lk lahter 4288 Kaldküla, NV 89450-4288 (775) 831-0300 Veebisait: http://www.solver.com E-post: info@solver.comSolveri spikker www.solver.com.

Solveri programmikoodi teatud osadele on kehtestanud autoriõiguse 1990-2009 Frontline Systems, Inc. Teatud osadele on kehtestanud autoriõiguse 1989, Optimal Methods, Inc.

Kas vajate rohkem abi?

Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.