Excel Solver: mikä se on ja milloin sitä pitäisi käyttää

Excel-taulukkotyökalut, kuten Goal Seek, jotka muuttavat yhtä muuttujaa, ovat hyödyllisiä, mutta valitettavasti useimmat liiketoiminnan ongelmat eivät ole niin helppoja. Yleensä kohtaat kaavoja, joissa on vähintään kaksi ja joskus kymmeniä muuttujia. Usein ongelmalla on useampi kuin yksi ratkaisu, ja haasteesi on käyttää Exceliä optimaalisen ratkaisun löytämiseen (eli sellaisen, joka maksimoi tuoton tai minimoi kustannukset tai vastaa muita kriteerejä). Näitä suurempia haasteita varten tarvitset lihaksikkaamman työkalun. Excelillä on vain vastaus: Ratkaisija. Excel Solver on hienostunut optimointiohjelma, jonka avulla voit löytää ratkaisuja monimutkaisiin ongelmiin, jotka muuten edellyttäisivät korkeatasoista matemaattista analyysiä.

Excel Solverin ymmärtäminen

Ratkaisija, kuten Goal Seek , käyttää iteratiivista menetelmää laskelmiensa suorittamiseen. Iteroinnin käyttö tarkoittaa, että Excel Solver yrittää ratkaisua, analysoi tulokset, yrittää toista ratkaisua ja niin edelleen. Tämä syklinen iteraatio ei kuitenkaan ole vain Solverin arvailua. Se olisi typerää. Ei, Excel Solver tutkii, kuinka tulokset muuttuvat jokaisen uuden iteroinnin myötä, ja voi yleensä joidenkin kehittyneiden matemaattisten prosessien avulla (jotka onneksi tapahtuvat taustalla ja voidaan jättää huomioimatta) yleensä kertomaan, mihin suuntaan sen pitäisi suunnata ratkaisuun.

Excel Solverin edut

Kyllä, Goal Seek ja Solver ovat molemmat iteratiivisia, mutta se ei tee niistä tasa-arvoisia. Itse asiassa Excel Solver tuo taulukkoon useita etuja:

• Excel Solverin avulla voit määrittää useita säädettäviä soluja. Voit käyttää yhteensä jopa 200 säädettävää kennoa.
• Excel Solverin avulla voit asettaa rajoituksia säädettäville soluille. Voit esimerkiksi käskeä Solveria löytämään ratkaisun, joka ei vain maksimoi voittoa, vaan myös täyttää tietyt ehdot, kuten saavuttaa 20–30 prosentin bruttokatteen tai pitää kulut alle 100 000 dollarissa. Näiden ehtojen sanotaan olevan ratkaisun rajoituksia .
• Excel Solver ei etsi vain haluttua tulosta ("tavoite" Goal Seekissä), vaan myös optimaalista. Esimerkiksi optimaalisen tuloksen etsiminen voi tarkoittaa, että voit löytää ratkaisun, joka on suurin tai pienin mahdollinen.
• Monimutkaisiin ongelmiin Ratkaisija voi luoda useita ratkaisuja . Voit sitten tallentaa nämä erilaiset ratkaisut eri skenaarioihin.

Milloin Excel Solveria kannattaa käyttää?

Okei, mennään suoraan: Excel Solver on tehokas työkalu, jota useimmat Excel-käyttäjät eivät tarvitse. Olisi ylivoimaista esimerkiksi käyttää Solveria nettovoiton laskemiseen kiinteiden tuotto- ja kustannuslukujen perusteella. Monet ongelmat edellyttävät kuitenkin vain Ratkaisija-lähestymistapaa. Nämä ongelmat kattavat monia eri aloja ja tilanteita, mutta niillä kaikilla on seuraavat yhteiset piirteet:

• Heillä on yksi tavoite solun (kutsutaan myös kohdesolun ), joka sisältää kaavan haluat suurentaa, pienentää tai asettaa tiettyyn arvoon. Tämä kaava voi olla laskelma, kuten kokonaiskuljetuskustannukset tai nettotulos.
• Tavoitesolukaava sisältää viittauksia yhteen tai useampaan muuttuvaan soluun (kutsutaan myös tuntemattomiksi tai muuttuviksi soluiksi ). Ratkaisija säätää näitä soluja löytääkseen optimaalisen ratkaisun kohteena olevalle solukaavalle. Nämä muuttuvat solut voivat sisältää kohteita, kuten myydyt yksiköt, toimituskulut tai mainoskulut.
• Valinnaisesti on olemassa yksi tai useampi rajoitussolu, joiden on täytettävä tietyt ehdot. Saatat esimerkiksi vaatia, että mainonnan osuus on alle 10 prosenttia kokonaiskuluista tai että asiakkaille myönnettävä alennus on 40–60 prosenttia.

Esimerkiksi alla olevassa kuvassa näkyy laskentataulukon tietomalli, joka on kaikki määritetty Excel Solverille. Malli näyttää kahden tuotteen tuotot (hinta kertaa myydyt yksiköt) ja kustannukset, kunkin tuotteen tuottaman voiton ja kokonaisvoiton. Tässä on vastattava seuraavaan kysymykseen: Kuinka monta yksikköä kutakin tuotetta on myytävä, jotta kokonaisvoitto on 0 dollaria? Tämä tunnetaan liiketoiminnassa kannattavuusanalyysinä .

Tämän tietomallin tavoitteena on löytää kannattavuuspiste (jossa kokonaisvoitto on 0 dollaria).

Se kuulostaa suoraviivaiselta Goal Seek -tehtävältä, mutta tässä mallissa on hankala puoli: muuttuvat kustannukset. Normaalisti tuotteen muuttuvat kustannukset ovat sen yksikköhinta kertaa myytyjen yksiköiden määrä. Jos tuotteen A valmistaminen maksaa 10 dollaria ja myyt 10 000 yksikköä, tuotteen muuttuvat kustannukset ovat 100 000 dollaria. Todellisessa maailmassa tällaiset kustannukset sekoittuvat kuitenkin usein useiden tuotteiden kesken. Jos esimerkiksi teet yhteisen mainoskampanjan kahdelle tuotteelle, molemmat tuotteet maksavat nämä kustannukset. Siksi tässä mallissa oletetaan, että yhden tuotteen kustannukset liittyvät toisen myytyjen yksiköiden määrään. Tässä on esimerkiksi kaava, jota käytetään puhallettavan tikkataulun (solu B8) kustannusten laskemiseen:

=B7 * B4 – C4

Toisin sanoen puhallettavan tikkataulun muuttuvat kustannukset alenevat yhdellä dollarilla jokaista myytyä Dog Polisherin yksikköä kohden. Jälkimmäisen muuttuvat kustannukset käyttävät samanlaista kaavaa (solussa C8):

=C7 * C4 – B4

Useisiin tuotteisiin liittyvien muuttuvien kustannusten vuoksi tämä tietomalli jää Goal Seekin toimintojen ulkopuolelle, mutta Solver vastaa haasteeseen. Tässä ovat mallin erityiset solut, joita Solver käyttää:

• Kohdesolu on C14; tämän kaavan kokonaisvoitto ja tavoiteratkaisu on 0 (eli kannattavuusraja).
• Vaihtosolut ovat B4 ja C4, jotka sisältävät kunkin tuotteen myytyjen kappaleiden määrän.
• Rajoituksiin kannattaa lisätä, että sekä tuotteen voittosolujen (B12 että C12) tulee olla myös 0.

Ladataan Excel Solver -apuohjelmaa

Apuohjelma on ohjelmisto, joka tuo yhden tai useamman piirteen Exceliin. Apuohjelmien asentaminen antaa sinulle Excelin lisäominaisuuksia, jotka eivät ole oletuksena käytettävissä valintanauhassa. Mukana oleva apuohjelma sisältyy Exceliin, mutta sitä ei asenneta automaattisesti, kun asennat Excelin . Excelin mukana tulee vakiona useita apuohjelmia, mukaan lukien Solver, jonka avulla voit ratkaista optimointiongelmia.

Asennat mukana tulevat apuohjelmat Excelin asetukset -valintaikkunan avulla. Löydät ne Lisäosat-osiosta. Kun apuohjelmat on asennettu, ne ovat saatavilla heti. Ne näkyvät yleensä välilehdellä, joka liittyy niiden toimintoon. Esimerkiksi Ratkaisija näkyy Tiedot-välilehdessä.

Noudata seuraavia vaiheita ladataksesi Excel Solver -apuohjelman:

Valitse Tiedosto → Asetukset.
Näyttöön tulee Excelin asetukset -valintaikkuna.

Valitse Apuohjelmat.

Valitse Hallinta-luettelosta Excelin apuohjelmat ja valitse sitten Siirry.
Excel näyttää Apuohjelmat-valintaikkunan.

Valitse Ratkaisija-apuohjelma -valintaruutu

Napsauta OK.
Excel lisää Ratkaisija-painikkeen Tiedot-välilehden Analysoi-ryhmään.

Tutustu Excelin ratkaisijan käyttöön .