Sisällys
”Etsi ratkaisua” on Excel-apuohjelma, jonka avulla on mahdollista valita paras ratkaisu ongelmiin määritettyjen rajoitusten perusteella. Toiminnon avulla voidaan ajoittaa henkilöstöä, jakaa kuluja tai investointeja. Kun tiedät, miten tämä ominaisuus toimii, säästät aikaa ja vaivaa.
Mikä on ratkaisujen etsiminen
Yhdessä useiden muiden Excelin vaihtoehtojen kanssa on yksi vähemmän suosittu, mutta erittäin tarpeellinen toiminto "Etsi ratkaisua". Huolimatta siitä, että sen löytäminen ei ole helppoa, sen tunteminen ja käyttäminen auttaa ratkaisemaan monia ongelmia. Vaihtoehto käsittelee tiedot ja antaa optimaalisen ratkaisun sallituista. Artikkelissa kuvataan, miten Ratkaisun etsiminen toimii suoraan.
Kuinka ottaa "Etsi ratkaisua" -ominaisuus käyttöön
Tehokkuudesta huolimatta kyseinen vaihtoehto ei ole näkyvällä paikalla työkalupalkissa tai kontekstivalikossa. Useimmat Excelissä työskentelevät käyttäjät eivät tiedä sen olemassaolosta. Oletusarvoisesti tämä toiminto on poistettu käytöstä, jotta voit näyttää sen suorittamalla seuraavat toiminnot:
- Avaa "Tiedosto" napsauttamalla sopivaa nimeä.
- Napsauta "Asetukset" -osiota.
- Valitse sitten "Lisäosat" -alaosa. Kaikki ohjelman lisäosat näkyvät täällä, merkintä "Hallinta" näkyy alla. Sen oikealla puolella on ponnahdusvalikko, jossa sinun tulee valita "Excel-apuohjelmat". Napsauta sitten "Go".
1 - Näyttöön tulee lisäikkuna "Lisäosat". Valitse haluamasi toiminnon vieressä oleva valintaruutu ja napsauta OK.
- Haluttu toiminto tulee näkyviin "Data"-osion oikealla puolella olevaan nauhaan.
Tietoja malleista
Nämä tiedot ovat erittäin hyödyllisiä niille, jotka ovat juuri tutustumassa "optimointimallin" käsitteeseen. Ennen kuin käytät "Etsi ratkaisua", on suositeltavaa tutkia materiaaleja rakennusmallien menetelmistä:
- harkittavana vaihtoehdon avulla voidaan tunnistaa paras tapa kohdentaa varoja investointeihin, tilojen lastaukseen, tavarantoimituksiin tai muihin toimenpiteisiin, joissa on tarpeen löytää paras ratkaisu.
- "Optimaalinen menetelmä" tällaisessa tilanteessa tarkoittaisi tulojen lisäämistä, kustannusten vähentämistä, laadun parantamista jne.
Tyypilliset optimointitehtävät:
- Tuotantosuunnitelman määrittäminen, jonka aikana luovutettujen tavaroiden myynnistä saatava voitto on suurin.
- Kuljetuskarttojen määrittäminen, jonka aikana kuljetuskustannukset minimoidaan.
- Hae useiden koneiden jakelua erilaisiin töihin, jotta tuotantokustannukset pienenevät.
- Lyhimmän työn valmistumisajan määrittäminen.
Tärkeää! Tehtävän virallistamiseksi on tarpeen luoda malli, joka heijastaa aihealueen pääparametrit. Excelissä malli on joukko kaavoja, jotka käyttävät muuttujia. Tarkasteltu vaihtoehto etsii sellaisia indikaattoreita, että tavoitefunktio on suurempi (pienempi) tai yhtä suuri kuin määritetty arvo.
Valmisteluvaihe
Ennen kuin asetat toiminnon nauhaan, sinun on ymmärrettävä, miten vaihtoehto toimii. Esimerkiksi taulukossa on tietoja tavaroiden myynnistä. Tehtävänä on määrittää jokaiselle tuotteelle alennus, joka olisi 4.5 miljoonaa ruplaa. Parametri näkyy solussa nimeltä kohde. Sen perusteella lasketaan muut parametrit.
Tehtävämme on laskea alennus, jolla eri tuotteiden myyntisummat kerrotaan. Nämä 2 elementtiä yhdistetään kaavalla, joka on kirjoitettu seuraavasti: =D13*$G$2. Missä D13:ssa kirjoitetaan toteutuksen kokonaismäärä ja $G$2 on halutun elementin osoite.
Toiminnon käyttäminen ja asetukset
Kun kaava on valmis, sinun on käytettävä itse funktiota suoraan:
- Sinun on vaihdettava "Data"-osioon ja napsautettava "Hae ratkaisua".
- "Asetukset" avautuu, jossa vaaditut asetukset määritetään. Rivillä "Optimoi tavoitefunktio:" tulee määrittää solu, jossa alennusten summa näytetään. Koordinaatit on mahdollista määrätä itse tai valita asiakirjasta.
- Seuraavaksi sinun on siirryttävä muiden parametrien asetuksiin. Vastaanottaja-osiossa on mahdollista asettaa enimmäis- ja minimirajat tai tarkka luku.
- Sitten kenttä "Muuttujien arvojen muuttaminen:" täytetään. Täällä syötetään halutun solun tiedot, jotka sisältävät tietyn arvon. Koordinaatit rekisteröidään itsenäisesti tai klikataan asiakirjan vastaavaa solua.
- Sitten muokataan välilehteä ”Rajoitusten mukaan:”, jossa asetetaan käytettävien tietojen rajoitukset. Esimerkiksi desimaalimurtoluvut tai negatiiviset luvut jätetään pois.
- Tämän jälkeen avautuu ikkuna, jossa voit lisätä rajoituksia laskelmiin. Aloitusrivi sisältää solun tai koko alueen koordinaatit. Tehtävän ehtojen mukaisesti näytetään halutun solun tiedot, jossa näkyy alennusilmaisin. Sitten määritetään vertailumerkki. Se on asetettu arvoon "suurempi tai yhtä suuri kuin", jotta lopullinen arvo ei ole miinusmerkillä. Rivillä 3 asetettu "Raja" on 0 tässä tilanteessa. On myös mahdollista asettaa raja "Lisää". Seuraavat vaiheet ovat samat.
- Kun yllä olevat vaiheet on suoritettu, asetettu raja näkyy suurimmalla rivillä. Luettelo voi olla suuri ja riippuu laskelmien monimutkaisuudesta, mutta tietyssä tilanteessa yksi ehto riittää.
- Lisäksi on mahdollista valita muita lisäasetuksia. Oikeassa alakulmassa on vaihtoehto "Asetukset", jonka avulla voit tehdä tämän.
- Asetuksissa voit asettaa "Rajoitustarkkuus" ja "Ratkaisurajat". Meidän tilanteessamme näitä vaihtoehtoja ei tarvitse käyttää.
- Kun asetukset on tehty, itse toiminto käynnistyy – napsauta "Etsi ratkaisu".
- Kun ohjelma on suorittanut tarvittavat laskelmat ja antaa lopulliset laskelmat vaadituissa soluissa. Sitten avautuu ikkuna tuloksista, jossa tulokset tallennetaan / peruutetaan tai hakuparametrit konfiguroidaan uuden mukaan. Kun tiedot täyttävät vaatimukset, löydetty ratkaisu tallennetaan. Jos valitset "Palauta ratkaisun hakuvaihtoehtoihin -valintaikkunan" etukäteen, avautuu ikkuna, jossa on toimintoasetukset.
- On mahdollista, että laskelmat osoittautuivat virheellisiksi tai on tarpeen muuttaa lähtötietoja muiden indikaattoreiden saamiseksi. Tällaisessa tilanteessa sinun on avattava asetusikkuna uudelleen ja tarkistettava tiedot.
- Kun tiedot ovat tarkkoja, voidaan käyttää vaihtoehtoista menetelmää. Näitä tarkoituksia varten sinun on napsautettava nykyistä vaihtoehtoa ja valittava sopivin menetelmä näkyviin tulevasta luettelosta:
- Ratkaisun löytäminen yleisen gradientin avulla epälineaarisiin ongelmiin. Oletuksena tämä vaihtoehto on käytössä, mutta on mahdollista käyttää muitakin.
- Ratkaisujen löytäminen lineaarisiin ongelmiin simpleksimenetelmän avulla.
- Evoluutiohaun käyttäminen tehtävän suorittamiseen.
Huomio! Kun yllä olevat vaihtoehdot eivät selvinneet tehtävästä, kannattaa tarkistaa tiedot asetuksista uudelleen, koska tämä on usein suurin virhe tällaisissa tehtävissä.
- Kun haluttu alennus on saatu, jää soveltaa sitä kunkin tuotteen alennusmäärän laskemiseen. Tätä tarkoitusta varten sarakkeen "Alennussumma" alkuperäinen elementti on korostettu, kaava kirjoitetaan «=D2*$G$2» ja paina "Enter". Dollarimerkit asetetaan niin, että kun kaava venytetään vierekkäisille viivoille, G2 ei muutu.
- Alennussumma alkuperäisestä tuotteesta saadaan nyt. Sitten sinun tulee siirtää kohdistin solun kulman päälle, kun siitä tulee "plus", LMB painetaan ja kaava venytetään vaadituille viivoille.
- Sen jälkeen pöytä on vihdoin valmis.
Lataa/tallenna hakuasetukset
Tämä vaihtoehto on hyödyllinen käytettäessä erilaisia rajoitusvaihtoehtoja.
- Valitse Solution Finderin asetukset -valikosta Lataa/tallenna.
- Anna mallialueen alue ja napsauta Tallenna tai Lataa.
Mallia tallennettaessa syötetään viite tyhjän sarakkeen 1 soluun, johon optimointimalli sijoitetaan. Mallin latauksen aikana syötetään viittaus koko alueeseen, joka sisältää optimointimallin.
Tärkeää! Ratkaisuasetukset-valikon viimeisten asetusten tallentamiseksi työkirja tallennetaan. Jokaisella sen arkilla on omat Ratkaisija-apuohjelmansa. Lisäksi arkille on mahdollista asettaa useampi kuin yksi tehtävä napsauttamalla "Lataa tai tallenna" -painiketta yksittäisten tehtävien tallentamiseksi.
Yksinkertainen esimerkki Solverin käytöstä
Säiliö on täytettävä konteilla siten, että sen massa on suurin. Säiliön tilavuus on 32 kuutiometriä. m. Täytetyn laatikon paino on 20 kg, tilavuus 0,15 kuutiometriä. m. Laatikko – 80 kg ja 0,5 cu. m. Vaaditaan, että konttien kokonaismäärä on vähintään 110 kpl. Tiedot on järjestetty seuraavasti:
Mallin muuttujat on merkitty vihreällä. Tavoitefunktio on korostettu punaisella. Rajoitukset: pienimmällä määrällä säiliöitä (suurempi tai yhtä suuri kuin 110) ja painon mukaan (=SUMMATUOTE(B8:C8,B6:C6) – säiliön kokonaistaarapaino.
Analogisesti tarkastelemme kokonaismäärää: =SUMMATUOTE(B7:C7,B8:C8). Tällainen kaava on tarpeen, jotta voidaan asettaa raja säiliöiden kokonaistilavuudelle. Sitten "Etsi ratkaisua" -toiminnon kautta syötetään linkit elementteihin, joissa on muuttujia, kaavoja ja itse indikaattoreita (tai linkit tiettyihin soluihin). Tietenkin säiliöiden määrä on kokonaisluku (se on myös rajoitus). Painamme ”Etsi ratkaisu”, jonka seurauksena löydämme sellaisen määrän kontteja, kun kokonaismassa on maksimi ja kaikki rajoitukset huomioidaan.
Ratkaisun etsiminen ei löytänyt ratkaisuja
Tällainen ilmoitus ponnahtaa esiin, kun kyseinen funktio ei ole löytänyt muuttujapisteiden yhdistelmiä, jotka täyttävät kunkin rajoitteen. Simplex-menetelmää käytettäessä on täysin mahdollista, että ratkaisua ei ole.
Kun käytetään menetelmää epälineaaristen ongelmien ratkaisemiseksi, kaikissa tapauksissa muuttujien alkuindikaattoreista alkaen, tämä osoittaa, että mahdollinen ratkaisu on kaukana sellaisista parametreista. Jos suoritat funktion muiden muuttujien alkuindikaattoreiden kanssa, ratkaisu on todennäköisesti olemassa.
Esimerkiksi epälineaarista menetelmää käytettäessä taulukon muuttujia sisältäviä elementtejä ei täytetty, eikä funktio löytänyt ratkaisuja. Tämä ei tarkoita, etteikö ratkaisua olisi. Nyt, tietyn arvioinnin tulokset huomioon ottaen, elementteihin syötetään muuta dataa muuttujilla, jotka ovat lähellä saatuja.
Joka tilanteessa sinun tulee aluksi tutkia mallia rajoitusristiriidan puuttumisen varalta. Usein tämä liittyy suhteen tai rajoitusilmaisimen väärään valintaan.
Yllä olevassa esimerkissä suurin tilavuuden ilmaisin on 16 kuutiometriä. m 32 sijasta, koska tällainen rajoitus on ristiriidassa istumapaikkojen vähimmäismäärän indikaattoreiden kanssa, koska se vastaa 16,5 kuutiometrin määrää. m.
Yhteenveto
Tämän perusteella Excelin Etsi ratkaisua -vaihtoehto auttaa ratkaisemaan tiettyjä ongelmia, joita on melko vaikea tai mahdoton ratkaista tavanomaisilla tavoilla. Tämän menetelmän soveltamisen vaikeus on, että tämä vaihtoehto on alun perin piilotettu, minkä vuoksi useimmat käyttäjät eivät ole tietoisia sen olemassaolosta. Lisäksi toiminto on melko vaikea oppia ja käyttää, mutta asianmukaisella tutkimuksella se tuo suuria etuja ja helpottaa laskelmia.