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

Märkus.: Solveri varasemates versioonides (enne Excel 2007) nimetati otsustusmuutujate lahtreid ka kohandatavateks või muutuvateks lahtriteks. Solveri lisandmoodulile tehti Excel 2010-s palju täiustusi, nii et kui Excel 2007 puhul on Solveri kasutamine pisut erinev.

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 hindamist

1. Muutuvad lahtrid

2. Piirangulahter

3. Sihtlahter

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

Pärast Solveri hindamist

  1. Klõpsake menüü Andmed jaotise Analüüs nuppu Solver.
    Exceli lindi pilt

    Märkus.: Kui nupp Solver või jaotis Analüüs pole saadaval, tuleb Solveri lisandmoodul aktiveerida. Vt Solveri lisandmooduli aktiveerimine.

    Excel 2010+ Solveri dialoogiboksi pilt

  2. Sisestage väljal Sea eesmärk lahtriviide või sihtlahtri nimi. Sihtlahter peab sisaldama valemit.

  3. 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.

  4. 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 viidatud lahtri ja kitsenduse vahele vastavat seost ( <=, =, >=, int, binvõi erinevus ). Kui klõpsate nuppu int, kuvatakse väljal piirang väärtus täisarv . Kui klõpsate nuppu alus, kuvatakse väljal piirang väärtus binaar . Kui klõpsate nuppu erinevus, kuvatakse väljal kitsendusalldifferent .

    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.

      • Kitsenduse aktsepteerimiseks ja dialoogiboksi Solver parameeters naasmiseks klõpsake nuppu OK.
        Märkus    Saate rakendada int-, aluse-ja vahe-seoseid ainult otsuse muutuvate 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.

  5. 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.

  1. Pärast ülesande määratlemist klõpsake dialoogiboksi Solveri parameetrid nuppu Suvandid.

  2. Iga proovilahendi väärtuste kuvamiseks märkige dialoogiboksi Suvandid ruut Kuva iteratsiooni tulemid ja siis klõpsake nuppu OK.

  3. Klõpsake dialoogiboksi Solveri parameetrid nuppu Lahenda.

  4. 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.

  1. Klõpsake dialoogiboksi Solveri parameetrid nuppu Suvandid.

  2. Valige või sisestage suvandite soovitud väärtused dialoogiboksi vahekaartidel Kõik meetodid, GRG Nonlinear ja Evolutionary.

  1. Klõpsake dialoogiboksis Solveri parameetrid nuppu Laadi/salvesta.

  2. 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 Solver Parameters (Solver Parameters ) viimatiste valikute salvestamiseks töölehe salvestamisega saate töövihiku salvestada. Töövihiku igal töölehel võib olla oma Solveri Valikud ja kõik salvestatakse. Töölehele saate määrata ka mitu probleemi, kui klõpsate ükshaaval käsku laadi ja Salvesta, et salvestada probleeme.

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).

Solveri hindamise näide

Viiktekst 1 muutuvate lahtrite

Viiktekst 2 piiratud lahter

Viiktekst 3 objektiivne lahter

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

Näide Solveri hindamisest uute väärtustega

  1. Mac-arvuti jaoks ette nähtud rakenduses Excel 2016 tehke järgmist. Valige Data (Andmed) > Solver.

    Solver

    Rakenduses Excel for Mac 2011 tehke järgmist. Klõpsake menüüd Data (Andmed) ja jaotises Analysis (Analüüs) nuppu Solver.

    Menüü Andmed jaotis Analüüs, lisandmoodul Solver

  2. Sisestage väljale Set Objective (Määrake eesmärk) lahtriviide või sihtlahtri nimi.

    Märkus.: Sihtlahter peab sisaldama valemit.

  3. 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.

  4. 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.

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

    Piirangu lisamiseks tehke järgmist.

    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. 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.

    4. 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.

  6. 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: 

  1. Lahendamisprotsessi saab katkestada paoklahvi (Esc) abil. Excel arvutab töölehe kohandatavatest lahtritest viimati leitud väärtustega ümber.

  2. 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.

  3. 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).

  1. Mac-arvuti jaoks ette nähtud rakenduses Excel 2016 tehke järgmist. Valige Data (Andmed) > Solver.

    Solver

    Rakenduses Excel for Mac 2011 tehke järgmist. Klõpsake menüüd Data (Andmed) ja jaotises Analysis (Analüüs) nuppu Solver.

    Menüü Andmed jaotis Analüüs, lisandmoodul Solver

  2. Pärast ülesande määratlemist klõpsake dialoogiboksi Solver Parameters (Solveri parameetrid) nuppu Options (Suvandid).

  3. Märkige iga proovilahendi väärtuste kuvamiseks ruut Show Iteration Results (Kuva iteratsiooni tulemid) ja siis klõpsake nuppu OK.

  4. Klõpsake dialoogiboksi Solver Parameters (Solveri parameetrid) nuppu Solve (Lahenda).

  5. 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).

  1. Mac-arvuti jaoks ette nähtud rakenduses Excel 2016 tehke järgmist. Valige Data (Andmed) > Solver.

    Solver

    Rakenduses Excel for Mac 2011 tehke järgmist. Klõpsake menüüd Data (Andmed) ja jaotises Analysis (Analüüs) nuppu Solver.

    Menüü Andmed jaotis Analüüs, lisandmoodul Solver

  2. 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.

  3. Klõpsake nuppu OK.

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

  1. Mac-arvuti jaoks ette nähtud rakenduses Excel 2016 tehke järgmist. Valige Data (Andmed) > Solver.

    Solver

    Rakenduses Excel for Mac 2011 tehke järgmist. Klõpsake menüüd Data (Andmed) ja jaotises Analysis (Analüüs) nuppu Solver.

    Menüü Andmed jaotis Analüüs, lisandmoodul Solver

  2. 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 Solver Parameters (Solver Parameters ) viimatiste valikute salvestamiseks, kui salvestate töövihiku, saate selle salvestada. Töövihiku igal lehel võib olla oma Solveri Valikud ja kõik salvestatakse. Kui soovite lehe jaoks määrata ka mitu probleemi, klõpsake eraldi probleemide salvestamiseks käsku Laadi/Salvesta .

  1. Mac-arvuti jaoks ette nähtud rakenduses Excel 2016 tehke järgmist. Valige Data (Andmed) > Solver.

    Solver

    Rakenduses Excel for Mac 2011 tehke järgmist. Klõpsake menüüd Data (Andmed) ja jaotises Analysis (Analüüs) nuppu Solver.

    Menüü Andmed jaotis Analüüs, lisandmoodul Solver

  2. 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 lisandmoodulite programme Exceli veebirakendus ei toetata, ei saa te lisandmoodulit Solver kasutada andmete analüüsimiseks, mis aitavad teil leida optimaalseid lahendusi.

Kui teil on Exceli töölauarakendus, saate töövihiku avamiseks kasutada nuppu Ava Excelis , et avada lisandmoodul Solver.

Täiendav abi Solveri kasutamisel

Solveri üksikasjalikuma abi kontaktteave:

Edetabeli süsteemid, Inc
. P.O. Box 4288
Kallak Village, NV 89450-4288
(775) 831-0300
Veebisait: http://www.solver.com
E-post
: info@solver.comSolveri spikker aadressil 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?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Vt ka

Solveri kasutamine kapitali eelarvestamiseks

Solveri abil optimaalse tootevaliku määratlemine

Mõjuanalüüsi tutvustus

Exceli valemite ülevaade

Katkiste valemite ärahoidmine

Valemivigade tuvastamine

Kiirklahvid Excelis

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Märkus.:  See leht on tõlgitud automaatselt ning sellel võib leiduda grammatikavigu ja ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Andke meile teada, kui see teave oli teile abiks. Soovi korral saate ingliskeelset artiklit lugeda siit.​

Facebook LinkedIn Meilisõnum

Kas vajate veel abi?

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×