Excel Solver -apuohjelma suorittaa matemaattisen optimoinnin. Tätä käytetään yleensä sovittamaan monimutkaisia malleja dataan tai etsimään iteratiivisia ratkaisuja ongelmiin. Voit esimerkiksi sovittaa käyrän joidenkin datapisteiden läpi yhtälön avulla. Ratkaisija voi löytää yhtälöstä vakiot, jotka sopivat parhaiten dataan. Toinen sovellus on, jos mallia on vaikea järjestää uudelleen niin, että vaadittava tulos tulee yhtälön kohteena.
Missä Solver on Excelissä?
Solver-apuohjelma sisältyy Exceliin, mutta sitä ei aina ladata osana oletusasennusta. Tarkistaaksesi, onko se ladattu, valitse DATA-välilehti ja etsi Solver-kuvake Analysis-osiosta..
Jos et löydä Ratkaisijaa TIEDOT-välilehdeltä, sinun on ladattava apuohjelma:
-
Valitse FILE-välilehti ja valitse sitten Options.
Image -
Valitse Options-valintaikkunassa Add-Ins vasemman reunan välilehdistä.
Image -
Valitse ikkunan alareunassa Excel-apuohjelmat Manage ja valitse Siirry…
Image -
Valitse valintaruutu kohdan Solver Add-in vieressä ja valitse OK.
Image -
Komennon Solver pitäisi nyt näkyä DATA-välilehdessä. Olet valmis käyttämään Solveria.
Image
Solverin käyttäminen Excelissä
Aloitetaan yksinkertaisella esimerkillä ymmärtääksesi, mitä Ratkaisija tekee. Kuvittele, että haluamme tietää, mikä säde antaa ympyrän, jonka pinta-ala on 50 neliöyksikköä. Tiedämme ympyrän alueen yhtälön (A=pi r2). Voisimme tietysti järjestää tämän yhtälön uudelleen antamaan tietylle alueelle vaaditun säteen, mutta esimerkin vuoksi oletetaan, että emme tiedä miten se tehdään.
Luo taulukko, jonka säde on kohdassa B1 ja laske pinta-ala kohdassa B2 käyttämällä yhtälöä =pi()B1^2.
Voimme säätää manuaalisesti arvoa kohdassa B1, kunnes B2 näyttää arvon, joka on tarpeeksi lähellä 50:tä. Riippuen siitä, kuinka tarkkoja olemme Tämä saattaa olla käytännöllinen lähestymistapa. Jos meidän on kuitenkin oltava erittäin tarkkoja, vaadittujen säätöjen tekeminen kestää kauan. Itse asiassa tämä on pohjimmiltaan sitä, mitä Solver tekee. Se säätää arvoja tietyissä soluissa ja tarkistaa arvon kohdesolussa:
- Valitse DATA-välilehti ja Solver ladataksesi Solver Parameters-valintaikkunan
-
Aseta tavoite solu alueeksi B2. Tämä on arvo, joka tarkistetaan ja säädellään muita soluja, kunnes tämä saavuttaa oikean arvon.
Image -
Valitse painike kohtaan Value of: ja aseta arvoksi 50. Tämä on arvo, joka B2:n tulisi saavuttaa.
Image -
Syötä kenttään Muuttamalla muuttujasoluja: kirjoita säteen sisältävä solu B1.
Image -
Jätä muut vaihtoehdot oletusarvoisesti ennalleen ja valitse Ratkaise. Optimointi suoritetaan, B1:n arvoa säädetään, kunnes B2 on 50 ja Solver Results-valintaikkuna tulee näkyviin.
Image -
Säilytä ratkaisu valitsemalla OK.
Image
Tämä yksinkertainen esimerkki osoitti, kuinka ratkaisija toimii. Tässä tapauksessa olisimme voineet saada ratkaisun helpommin muilla tavoilla. Seuraavaksi tarkastellaan joitain esimerkkejä, joissa Solver antaa ratkaisuja, joita olisi vaikea löytää muulla tavalla.
Monimutkaisen mallin sovittaminen Excelin Solver-apuohjelman avulla
Excelissä on sisäänrakennettu toiminto lineaarisen regression suorittamiseksi sovittamalla suora viiva tietojoukon läpi. Monet yleiset epälineaariset funktiot voidaan linearisoida, mikä tarkoittaa, että lineaarista regressiota voidaan käyttää sovittamaan funktioita, kuten eksponentiaaleja. Monimutkaisemmissa toiminnoissa Ratkaisijaa voidaan käyttää "pienimmän neliösumman minimoinnin" suorittamiseen. Tässä esimerkissä harkitsemme muotoa ax^b+cx^d olevan yhtälön sovittamista alla esitettyihin tietoihin.
Tämä sisältää seuraavat vaiheet:
- Järjestä tietojoukko sarakkeen A x-arvoilla ja sarakkeen B y-arvoilla.
- Luo 4 kerroinarvoa (a, b, c ja d) jonnekin laskentataulukkoon, niille voidaan antaa mieliv altaisia aloitusarvoja.
-
Luo sovitettujen Y-arvojen sarake käyttämällä yhtälöä muodossa ax^b+cx^d, joka viittaa vaiheessa 2 luotuihin kertoimiin ja sarakkeen A x-arvoihin. Huomaa, että kaavan kopioimiseksi alaspäin sarakkeessa, viittausten kertoimiin on oltava absoluuttisia, kun taas viittausten x-arvoihin on oltava suhteellisia.
Image -
Vaikka se ei ole välttämätöntä, voit saada visuaalisen osoituksen yhtälön sopivuudesta piirtämällä molemmat y-sarakkeet x-arvoja vastaan yhdelle XY-pistekaaviolle. On järkevää käyttää merkkejä alkuperäisille datapisteille, koska nämä ovat diskreettejä arvoja, joissa on kohinaa, ja käyttää sovitettua yhtälöä varten viivaa.
Image -
Seuraavaksi tarvitsemme tavan kvantifioida datan ja sovitetun yhtälömme välinen ero. Tavallinen tapa tehdä tämä on laskea neliöityjen erojen summa. Kolmannessa sarakkeessa kullakin rivillä Y:n alkuperäinen data-arvo vähennetään sovitetun yhtälön arvosta ja tulos neliötetään. Joten kohdassa D2 arvo saadaan kaavalla =(C2-B2)^2 Kaikkien näiden neliöarvojen summa lasketaan sitten. Koska arvot on neliöity, ne voivat olla vain positiivisia.
Image -
Olet nyt valmis suorittamaan optimoinnin Ratkaisijan avulla. On neljä kerrointa, joita on säädettävä (a, b, c ja d). Sinulla on myös yksi tavoitearvo minimoitavaksi, neliöityjen erojen summa. Käynnistä ratkaisija edellä kuvatulla tavalla ja aseta ratkaisijaparametrit viittaamaan näihin arvoihin alla olevan kuvan mukaisesti.
Image -
Poista valinta Tee rajoittamattomista muuttujista ei-negatiivisia, tämä pakottaisi kaikki kertoimet ottamaan positiivisia arvoja.
Image -
Valitse Solve ja tarkista tulokset. Kaavio päivittyy antamaan hyvän kuvan istuvuuden hyvyydestä. Jos ratkaisija ei tuota hyvää istuvuutta ensimmäisellä yrityksellä, voit yrittää suorittaa sen uudelleen. Jos istuvuus on parantunut, yritä ratkaista nykyiset arvot. Muussa tapauksessa voit yrittää parantaa istuvuutta manuaalisesti ennen ratkaisemista.
Image - Kun hyvä istuvuus on saatu, voit poistua ratkaisusta.
Mallin ratkaiseminen iteratiivisesti
Joskus on olemassa suhteellisen yksinkertainen yhtälö, joka antaa ulostulon jonkin syötteen perusteella. Kuitenkin, kun yritämme kääntää ongelman, ei ole mahdollista löytää yksinkertaista ratkaisua. Esimerkiksi ajoneuvon kuluttama teho saadaan likimäärin kaavalla P=av + bv^3 missä v on nopeus, a on vierintävastuksen kerroin ja b on vierintävastuksen kerroin. aerodynaaminen vastus. Vaikka tämä on melko yksinkertainen yhtälö, sitä ei ole helppo järjestää uudelleen antamaan yhtälöä nopeudesta, jonka ajoneuvo saavuttaa tietyllä teholla. Voimme kuitenkin käyttää Ratkaisijaa löytääksemme tämän nopeuden iteratiivisesti. Etsi esimerkiksi nopeus, joka saavutetaan 740 W:n teholla.
-
Aseta yksinkertainen laskentataulukko, jossa on nopeus, kertoimet a ja b sekä niistä laskettu teho.
Image -
Käynnistä Ratkaisija ja syötä teho, B5, tavoitteeksi. Aseta tavoitearvo 740 ja valitse nopeus B2 muutettaviksi soluiksi. Aloita ratkaisu valitsemalla ratkaise.
Image -
Ratkaisija säätää nopeuden arvoa, kunnes teho on hyvin lähellä arvoa 740, mikä tarjoaa tarvitsemamme nopeuden.
Image - Mallien ratkaiseminen tällä tavalla voi usein olla nopeampaa ja vähemmän virhe altista kuin monimutkaisten mallien kääntäminen.
Ratkaisijan eri vaihtoehtojen ymmärtäminen voi olla melko vaikeaa. Jos sinulla on vaikeuksia löytää järkevä ratkaisu, on usein hyödyllistä soveltaa rajaehtoja muutettaviin soluihin. Nämä ovat raja-arvoja, joiden yli niitä ei pidä muuttaa. Esimerkiksi edellisessä esimerkissä nopeuden ei tulisi olla pienempi kuin nolla ja olisi myös mahdollista asettaa yläraja. Tämä on nopeus, josta olet melko varma, että ajoneuvo ei voi ajaa sitä nopeammin. Jos pystyt asettamaan rajat vaihtuville muuttujasoluille, se saa myös muita kehittyneempiä vaihtoehtoja toimimaan paremmin, kuten monikäynnistyksen. Tämä suorittaa useita erilaisia ratkaisuja, alkaen muuttujien eri alkuarvoista.
Ratkaisumenetelmän valinta voi myös olla vaikeaa. Simplex LP sopii vain lineaarisille malleille, jos ongelma ei ole lineaarinen, se epäonnistuu ja ilmoittaa, että tämä ehto ei täyty. Molemmat kaksi muuta menetelmää sopivat epälineaarisille menetelmille. GRG Nonlinear on nopein, mutta sen ratkaisu voi olla hyvin riippuvainen aloitusolosuhteista. Siinä on se joustavuus, että se ei vaadi muuttujia asettaakseen rajoja. Evoluutioratkaisin on usein luotettavin, mutta se edellyttää, että kaikilla muuttujilla on sekä ylä- että alaraja, mikä voi olla vaikeaa selvittää etukäteen.
Excel Solver -apuohjelma on erittäin tehokas työkalu, jota voidaan soveltaa moniin käytännön ongelmiin. Voit käyttää Excelin tehoja kokonaan yhdistämällä Solverin Excel-makroihin.
