Excel Solver: tulosten optimointi, rajoitusten lisääminen ja ratkaisujen tallentaminen skenaarioihin

Voit määrittää Excel Solver -mallisi Ratkaisijan parametrit -valintaikkunan avulla. Käytä Aseta tavoite -ruutua määrittääksesi tavoitesolun ja käytät Vastaanottaja-ryhmää kertoaksesi Excel Solverille, mitä haluat tavoitesolusta: suurin mahdollinen arvo; pienin mahdollinen arvo; tai tietty arvo. Lopuksi voit määrittää Muuttamalla muuttujasoluja -ruudun solut, joita Ratkaisija voi käyttää arvojen liittämiseen tuloksen optimoimiseksi.

Excel Solver -tulosten optimointi

Kun Ratkaisija löytää ratkaisun, voit valita joko Säilytä Solver Solution tai Palauta alkuperäiset arvot. Jos valitset Keep Solver Solution -vaihtoehdon, Excel muuttaa laskentataulukkoa pysyvästi. Muutoksia ei voi kumota.

Kun Solver-valmis laskentataulukkomallisi on valmis käyttöön, seuraa näitä ohjeita, jotta voit löytää mallillesi optimaalisen tuloksen Solverin avulla:

Valitse Data → Ratkaisija.
Excel avaa Ratkaisijan parametrit -valintaikkunan.

Kirjoita Aseta tavoite -ruutuun mallisi tavoitesolun osoite.
Huomaa, että jos napsautat solua syöttääksesi sen, Ratkaisija kirjoittaa automaattisesti absoluuttisen soluosoitteen (esimerkiksi $14 B14 sijasta). Ratkaisija toimii hyvin joka tapauksessa.

Valitse Vastaanottaja-ryhmästä vaihtoehto:

• Max: Palauttaa suurimman mahdollisen arvon.
• Min: Palauttaa pienimmän mahdollisen arvon.
• Arvo: Anna numero asettaaksesi tavoitesolun kyseiselle numerolle.

  Esimerkkimallissa Arvo Of on valittu ja tekstiruutuun on syötetty 0 .

Kirjoita Muuttamalla muuttujasoluja -ruutuun niiden solujen osoitteet, joita haluat Ratkaisijan muuttavan, kun se etsii ratkaisua.
Esimerkissä vaihtuvat solut ovat B4 ja C4. Seuraava kuva näyttää valmiin Ratkaisijaparametrit-valintaikkunan.

Valmiit Ratkaisijaparametrit -valintaikkuna.

Napsauta Ratkaise.
Ratkaisija ryhtyy töihin. Kun Ratkaisija käsittelee ongelmaa, saatat nähdä Näytä kokeiluratkaisu -valintaikkunat näkyviin yhden tai useamman kerran.

Napsauta missä tahansa näkyviin tulevassa Näytä kokeiluratkaisu -valintaikkunassa Jatka siirtääksesi asioita eteenpäin.
Kun optimointi on valmis, Excel näyttää Ratkaisijatulokset -valintaikkunan.

Ratkaisijatulokset -valintaikkuna ja ratkaisu kannattavuusongelmaan.

Valitse Keep Solver Solution -vaihtoehto.
Jos et halua hyväksyä tulosta, valitse sen sijaan Palauta alkuperäiset arvot -vaihtoehto.

Napsauta OK.

Voit pyytää Ratkaisijaa näyttämään yhden tai useamman raportin, jotka antavat sinulle lisätietoa tuloksista. Valitse Ratkaisijan tulokset -valintaikkunan Raportit-luettelosta jokainen raportti, jota haluat tarkastella:

• Vastaus: Näyttää tietoja mallin tavoitesolusta, muuttujasoluista ja rajoituksista. Objektiivisen solun ja muuttujasolujen osalta Ratkaisija näyttää alkuperäiset ja lopulliset arvot.
• Herkkyys: Yrittää näyttää, kuinka herkkä ratkaisu on mallin kaavojen muutoksille. Herkkyysraportin asettelu riippuu käyttämäsi mallin tyypistä.
• Rajat: Näyttää tavoitesolun ja sen arvon sekä muuttujasolut ja niiden osoitteet, nimet ja arvot.

Excel Solver voi käyttää yhtä useista ratkaisutavoista. Valitse Ratkaisijan parametrit -valintaikkunassa Valitse ratkaisumenetelmä -luettelosta jokin seuraavista:

• Simplex LP: Käytä, jos laskentataulukkomallisi on lineaarinen. Yksinkertaisimmillaan lineaarinen malli on sellainen, jossa muuttujia ei nosteta mihinkään potenssiin eikä mitään niin sanotuista transsendenttisista funktioista - kuten SIN ja COS - käytetä.
• GRG Epälineaarinen: Käytä, jos laskentataulukkomallisi on epälineaarinen ja sileä. Yleisesti ottaen sileä malli on malli, jossa käytetyn yhtälön kuvaaja ei näytä teräviä reunoja tai katkoksia.
• Evoluutio: Käytä, jos laskentataulukkomallisi on epälineaarinen ja epätasainen.

Pitääkö sinun olla huolissaan tästä? Melkein varmasti ei. Excel Solver käyttää oletuksena GRG Nonlinear -ohjelmaa, ja sen pitäisi toimia melkein kaikessa, mitä teet Solverilla.

Rajoitusten lisääminen Excel Solverille

Tosimaailma asettaa rajoituksia ja ehtoja kaavoille. Tehtaan enimmäiskapasiteetti voi olla 10 000 yksikköä päivässä, yrityksen työntekijöiden määrä ei voi olla negatiivinen luku, ja mainontakulusi saatetaan rajoittaa 10 prosenttiin kokonaiskuluista.

Oletetaan vastaavasti, että suoritat kannattavuusanalyysiä kahdelle tuotteelle. Jos suoritat optimoinnin ilman rajoituksia, Ratkaisija voi saavuttaa kokonaisvoiton 0 asettamalla yhden tuotteen lievälle tappiolle ja toisen pienelle voitolle, jolloin tappio ja voitto kumoavat toisensa. Itse asiassa, jos katsot tarkkaan edellistä kuvaa, Solver teki juuri tämän. Saadaksesi todellisen kannattavuusrajan, saatat haluta nähdä molemmat tuotteen tuottoarvot nollana.

Tällaiset rajoitukset ja ehdot ovat esimerkkejä siitä, mitä Solver kutsuu rajoituksiksi. Rajoitusten lisääminen käskee Ratkaisijaa löytämään ratkaisun, jotta näitä ehtoja ei rikota.

Näin voit ajaa Ratkaisijaa optimointiin lisätyillä rajoituksilla:

Valitse Data → Ratkaisija.
Excel avaa Ratkaisijan parametrit -valintaikkunan.

Käytä Aseta tavoite -ruutua, Vastaanottaja-ryhmää ja Muuttamalla muuttujasoluja -ruutua määrittääksesi Ratkaisija yllä kuvatulla tavalla.

Napsauta Lisää.
Excel näyttää Lisää rajoite -valintaikkunan.

Kirjoita Soluviittaus-ruutuun sen solun osoite, jota haluat rajoittaa.
Voit kirjoittaa osoitteen tai valita solun laskentataulukosta.

Valitse avattavasta luettelosta operaattori, jota haluat käyttää.
Useimmiten käytät vertailuoperaattoria, kuten yhtä suuri (=) tai suurempi kuin (>). Käytä int (kokonaisluku) -operaattoria, kun tarvitset rajoitteen, kuten työntekijöiden kokonaismäärän, olemaan kokonaisluku reaaliluvun sijaan (eli luku, jossa on desimaalikomponentti; sinulla ei voi olla 10,5 työntekijää!). Käytä bin (binääri) -operaattoria, kun sinulla on rajoitus, jonka on oltava joko TOSI tai EPÄTOSI (tai 1 tai 0).

Jos valitsit vertailuoperaattorin vaiheessa 5, kirjoita Pako-ruutuun arvo, jolla haluat rajoittaa solua.
Tässä kuvassa on esimerkki valmiista Add Constraint -valintaikkunasta. Esimerkkimallissa tämä rajoite käskee Ratkaisijaa löytämään ratkaisun, jossa puhallettavan tikkataulun (solu B12) tuotevoitto on 0.

Valmis Add Constraint -valintaikkuna.

Jos haluat määrittää lisää rajoituksia, napsauta Lisää ja toista vaiheet 4–6 tarpeen mukaan.

Esimerkkiä varten lisäät rajoitteen, joka pyytää Dog Polisher -tuotteen tuoton (solu C12) olemaan 0.

Napsauta OK.

Excel palaa Ratkaisijaparametrit-valintaikkunaan ja näyttää rajoituksesi Subject to the Constraints -luetteloruudussa.

Napsauta Ratkaise.

Napsauta missä tahansa näkyviin tulevassa Näytä kokeiluratkaisu -valintaikkunassa Jatka siirtääksesi asioita eteenpäin.
Alla olevassa kuvassa on esimerkki katettavuusratkaisusta, johon on lisätty rajoituksia. Huomaa, että kokonaisvoitto-solun (B14) arvo on 0, vaan myös tuotevoitto-solun (B12 ja C12) arvo on.

Ratkaisijatulokset -valintaikkuna ja lopullinen ratkaisu kannattavuusongelmaan.

Valitse Keep Solver Solution -vaihtoehto.
Jos et halua hyväksyä tulosta, valitse sen sijaan Palauta alkuperäiset arvot -vaihtoehto.

Napsauta OK.

Voit lisätä enintään 100 rajoitusta. Lisäksi, jos sinun on tehtävä muutos rajoitteeseen ennen ratkaisemisen aloittamista, valitse rajoite Kohde rajoituksiin -luetteloruudusta, napsauta Muuta ja tee sitten muutokset näkyviin tulevassa Muuta rajoitteita -valintaikkunassa. Jos haluat poistaa rajoitteen, jota et enää tarvitse, valitse rajoite ja napsauta sitten Poista.

Tallenna Excel Solver -ratkaisu skenaariona

Aina kun sinulla on laskentataulukkomalli, joka käyttää yhtenäistä syöttöarvojoukkoa – joka tunnetaan nimellä muuttuvat solut – sinulla on Excel kutsuu skenaariota. Solverissa nämä muuttuvat solut ovat sen muuttuvia soluja, joten Solver-ratkaisu on eräänlainen skenaario Excelissä . Ratkaisu ei kuitenkaan anna sinulle helppoa tapaa tallentaa ja suorittaa uudelleen tiettyä ratkaisua. Voit kiertää tämän ongelman tallentamalla ratkaisun skenaariona, jonka voit myöhemmin palauttaa Excelin Scenario Manager -ominaisuuden avulla.

Tallenna Solver-ratkaisu skenaariona seuraavasti:

Valitse Data → Ratkaisija.
Excel avaa Ratkaisijan parametrit -valintaikkunan.

Käytä Aseta tavoite -ruutua, Vastaanottaja-ryhmää, Muuttamalla muuttujasoluja -ruutua ja Subject to the Constraints -luetteloa määrittääksesi Ratkaisija yllä kuvatulla tavalla.

Napsauta Ratkaise.

Aina kun Show Trial Solution -valintaikkuna tulee näkyviin, valitse Jatka.
Kun optimointi on valmis, Excel näyttää Ratkaisijatulokset -valintaikkunan.

Napsauta Tallenna skenaario.
Excel näyttää Tallenna skenaario -valintaikkunan.

Kirjoita Skenaarion nimi -valintaikkunaan skenaarion nimi ja napsauta sitten OK.
Excel palauttaa sinut Ratkaisijatulokset -valintaikkunaan.

Valitse Keep Solver Solution -vaihtoehto.
Jos et halua hyväksyä tulosta, valitse sen sijaan Palauta alkuperäiset arvot -vaihtoehto.

Napsauta OK.