Microsoft Excel -toiminto: ratkaisun löytäminen. Lyhyt kuvaus lisäosasta

Microsoft Excel -apuohjelma "Ratkaisuhaku" on tehokas työkalu ratkaisujen etsimiseen ja sitä käytetään optimointiongelmien ratkaisemiseen. Ratkaisuhaun avulla voit löytää optimaalisen arvon solun sisältämälle kaavalle, jota kutsutaan kohdesoluksi. Toimenpide toimii soluryhmässä, joka liittyy suoraan tai epäsuorasti kohdesolun kaavaan.

Lyhyt kuvaus lisäosasta

Tietyn tuloksen saamiseksi kaavan avulla menettely muuttaa arvoja vaikuttavissa soluissa. Arvorajoituksia käytetään pienentämään mallissa käytettyjen arvojen vaihteluväliä. Solution Finder -apuohjelma on tavallinen Microsoft Office Excel -apuohjelma, ja se on käytettävissä heti, kun asennat Microsoft Officen yleensä tai Microsoft Excelin erityisesti.

Kuinka asentaa lisäosa?

Find Solution -apuohjelman voi asentaa kahdella tavalla. Vakiolisäosat, kuten "Solution Search" ja "Analysis Package", asennetaan yhdessä MS Officen tai MS Excelin kanssa. Jos vakioapuohjelmaa ei asennettu alkuperäisen asennuksen aikana, sinun on suoritettava asennusprosessi uudelleen. Katsotaanpa esimerkiksi "Search for Solution" -apuohjelman asentamista Microsoft Office 2010:n avulla. Versioissa 2003 ja 2007 kaikki tehdään samalla tavalla.

Joten käynnistä asennuslevy MS Office 2010 -sovelluspaketilla ja valitse "Lisää tai poista komponentteja" -vaihtoehto.

Napsauta seuraavaksi "Jatka" -painiketta, asennusparametreista löydämme Microsoft Excel -sovelluksen, tämän sovelluksen komponenteista löydämme "Lisäosat", valitse "Ratkaisuhaku" -lisäosa ja aseta "Suorita". tietokoneeltani" -vaihtoehto.

Napsauta jälleen "Jatka" -painiketta ja odota, kunnes lisäosa on asennettu.

Kuinka liität lisäosan?

Ennen käyttöä sinun on ensin otettava apuohjelma käyttöön valitsemalla sen nimen edessä oleva ruutu Lisäosat-valintaikkunan käytettävissä olevien lisäosien luettelosta.

Tämän ikkunan käynnistäminen vaihtelee hieman sovelluksen versiosta riippuen. Tämä on kirjoitettu yksityiskohtaisesti erillisessä artikkelissa " Kuinka asentaa Excel 2003/2007/2010 -apuohjelman? " kuvakaappauksilla jokaisesta Excel-sovelluksen kolmesta versiosta, joten en toista itseäni. Kyllä, lisään vain muutaman sanan tämän lisäosan toisesta asennustavasta. Löydät Internetistä tiedoston nimellä Solver.xla(tämä on "Etsi ratkaisua" -lisäosa) ja asenna se yllä olevan linkin kuvauksen mukaisesti.

Maailma muuttuu hurjaa vauhtia, halusimme sitä tai emme. Tämä totuus on erityisen hyvin PC-käyttäjien tiedossa. Loppujen lopuksi ohjelmisto muuttuu ja päivittyy uskomattoman usein. Onneksi toimistopaketit eivät kärsi tästä paljon, mutta poikkeuksiakin on. Mikä on toimistoohjelmistopakettien merkitys? Mikä tahansa toimistosovellus on kätevä työkalu, joka on suunniteltu toimimaan tietokantojen kanssa. Tämän järjestelmän apuelementtien määrä kasvaa koko ajan.

Visualisointityökalujen avulla, jotka ilmestyivät vain tällaisten ohjelmien uusissa versioissa, työstä on tullut paljon helpompaa. Uuden hakusuodattimen ansiosta työ on kiihtynyt huomattavasti. Ja itse Microsoft Excel 2010 toimii nopeammin. Mutta näyttää siltä, ​​​​että toimistotyöntekijät olivat juuri äskettäin oppineet Office 2007:n kanssa työskentelyn monimutkaisuuksista. Mutta yhtäkkiä oli Office 2010:n esitys, joka vain lisäsi ongelmia onnettomille käyttäjille. Esimerkki on "ratkaisun etsiminen" Microsoft Excel 2010:ssä.

Tämä apuohjelma ei ole vain hyödyllinen, vaan sen avulla voit myös tehdä laskentataulukkoeditorin kanssa työskentelystä paljon tuottavampaa, jolloin voit ratkaista valtavan määrän monimutkaisia ​​ongelmia. Se on erityisen olennaista optimoinnin kannalta, mikä on olennaista monille yrityksille nykyään. Mutta miksi Microsoft Excel 2010? Jos puhumme nimenomaan Excelistä tässä versiossa, siinä on tapahtunut merkittäviä muutoksia. Esimerkiksi suuri määrä kaavojen virheitä korjattiin, minkä vuoksi laskelmissa tapahtui melko usein virheitä ohjelman aiemmissa versioissa. Mutta pieninkin virhearviointi voi joskus johtaa melko epämiellyttäviin seurauksiin.

Käyttämällä nauhakäyttöliittymää, jonka avulla voit esikatsella kaavioita ja kaavioita ennen niiden lisäämistä taulukkoon, käyttäjien on helpompi laatia monimutkaisia, ammattimaisia ​​asiakirjoja. Lisäksi editorin uusi versio sisälsi uudentyyppisiä kaavoja, jotka voivat olla erittäin hyödyllisiä taloustieteilijöille ja kirjanpitäjille. Tämä seikka korostaa Microsoftin keskittymistä yrityskäyttäjiin. Ottaen huomioon, että kaikki alla kuvatut tilanteet ovat heille tyypillisiä, tässä ei ole mitään yllättävää.

Jos et ole käyttänyt ratkaisuhaku-lisäosaa, voit asentaa sen erikseen. Kuinka asentaa se? Tämä tehdään melko helposti. Jos käytät Excel-laskentataulukkoeditoria 2003 tai vanhempia, tämän toiminnon suorittamiseksi sinun on siirryttävä "Työkalut" -kohtaan ja valittava sieltä "Lisäosat". Ja mistä etsiä "ratkaisuja", jos puhumme nykyaikaisemmasta versiosta? Jos käytät Excel 2007:ää, löydät "Hae ratkaisua" -painikkeen "Data"-välilehdeltä. Kuinka toimia tämän kanssa? Ehkä kaikki nämä selitykset saattavat tuntua hieman pitkiltä, ​​mutta tämä lisäosa toimii melko loogisesti. Sinun ei tarvitse olla tietokonenero hallitaksesi sitä. Ymmärtääksemme täysin sen käyttöperiaatteen, tarkastellaan yksinkertaista esimerkkiä.

Miten ratkaisun etsiminen toimii Excel 2010:ssä?

Esimerkki: sinulle annetaan tehtäväksi jakaa bonuksia organisaatiossa. Ratkaisun yksinkertaistamiseksi oletetaan, että sinun on jaettava bonus kaikkien sivuliikkeen työntekijöiden kesken. Premium-budjetti - 100 000 ruplaa. Bonus voidaan jakaa kunkin työntekijän palkkaan suhteutettuna. Mistä aloittaa? Ensinnäkin on tarpeen kehittää taulukko, syöttää siihen kaikki tarvittavat tiedot ja kaavalausekkeet. Palkkion kokonaissumma katsotaan tulokseksi. On syytä ottaa huomioon, että kohdesolu (esimerkiksi C8) liittyy muutettavaan osioon (esimerkiksi E2).

Alueella C2-C7 voi olla lisäkaavoja, joilla voit laskea kunkin työntekijän bonussumman. Tämän jälkeen sinun on käynnistettävä "Hae ratkaisua" -lisäosa. Sitten vaaditut arvot asetetaan avautuvassa ikkunassa. Erityistä huomiota tulee kiinnittää siihen, että ikkunoiden ulkonäkö voi vaihdella suuresti toimistopaketin eri versioissa. Joten tässä tilanteessa sinun on selvitettävä se itse. Mutta perustavanlaatuisia eroja ei ole, joten opiskelu ei vie paljon aikaa.

Mitä vaihtoehtoja valintaikkunassa on?

Työsi helpottamiseksi sinun tulee tietää arvot, jotka yleensä ovat tietyllä toiminta-alueella. Ensinnäkin kohdesolu. Huomaa, että toisin kuin muut toiminnot, jotka voivat käyttää useita tiedonsyöttökenttiä, tässä voi olla vain yksi. Lisäksi on syytä ottaa huomioon, että optimointivaihtoehtoja voi olla useita. Erityistä huomiota tulee kiinnittää mahdolliseen vähimmäis- ja enimmäisarvoon. Kiinnitä huomiota myös tiettyyn tulokseen. Jos tarvitset jälkimmäistä vaihtoehtoa, haluttu tulos on määritettävä tarkasti syöttökentässä. On myös otettava huomioon, että joko yksittäiset kentät tai alue voivat toimia vaihtuvina soluina. Ohjelma ottaa alueen lopullisen arvon vertaamalla sitä alkuperäisiin tietoihin.

Miten rajoituksia lisätään?

Jos haluat lisätä rajoituksia ohjelmaan, sinun on käytettävä "Lisää" -painiketta. On tärkeää ottaa huomioon seuraava seikka: kun asetat tällaisia ​​arvoja, sinun on oltava erittäin varovainen. Koska Excelin "ratkaisuhaku"-apuohjelmaa käytetään varsin tärkeissä toiminnoissa, on tärkeää saada tuloksena oikeat arvot. Itse tulokset riippuvat rajoituksista. Voit asettaa rajoituksia sekä yksittäisille soluille että kokonaisille alueille.

Mitä kaavojen ja symbolien muunnelmia voidaan käyttää tässä tapauksessa? Seuraavia merkkejä voidaan käyttää: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

Mistä löydät tämän vaihtoehdon ohjelman vanhemmissa versioissa? Jos käytät Excel 2007:ää tai vanhempia, voit käyttää tätä vaihtoehtoa napsauttamalla "Asetukset" -painiketta. Täältä näet "Ratkaisuhakuasetukset" -kohdan.

Etsi valmis tulos

Jos haluat etsiä valmiin ratkaisun, sinun on napsautettava "Suorita" -painiketta. Tämän seurauksena näkyviin tulee Ratkaisuhaun tulokset -valintaikkuna. Jos olet tyytyväinen lopulliseen vastaukseen, sinun tarvitsee vain napsauttaa "Ok"-painiketta. Tämän seurauksena haluamasi vastaus kirjataan taulukkoon. Jos tuloksena oleva arvo ei ole näkemyksesi kanssa samaa mieltä, sinun on napsautettava "Peruuta" -painiketta. Taulukko palaa lopulta alkuperäiseen arvoonsa. Voit jatkaa optimaalisen ratkaisun etsimistä. Jos muutit lähdetietoja, tämä päätös on tehtävä uudelleen.

Missä "ratkaisuhaku"-apuohjelmaa voidaan käyttää Excelissä?

Harkitse toista esimerkkiä - kustannusten minimoimista. Kuten edellä mainittiin, tätä toimintoa voidaan käyttää tuotantoprosessien optimointiin. Katsotaan, kuinka voimme pienentää matalakerrosrakentamista harjoittavan yrityksen kustannuksia. Oletetaan, että meillä on organisaatio itse ja kolme toimittajaa, jotka toimittavat rakennusmateriaaleja. Rakennuskustannukset sisällytetään kiinteistön kustannuksiin, joten on yhtiön edun mukaista valita toimittaja, jonka työ maksaa vähemmän.

Mitä tietoja tulee syöttää MS Excelin "ratkaisuhakuun"? On tarpeen ilmoittaa rakennusmateriaalien kustannukset, niiden tarve rakennustyömaalla ja rakennusmateriaalien kuljetuskustannukset. Jokainen "toimittaja-ostaja" -pari on otettava huomioon. Kohdesolun tulee ilmoittaa kaikkien kuljetuskustannusten kokonaissumma. Jos kaikki tehdään oikein, "ratkaisuhaku" -toiminto mahdollistaa tuottoisimman strategian luomisen, joka tuottaa suurimmat mahdolliset tulot.

On monia ongelmia, jotka voidaan ratkaista huomattavasti helpommin Solution Finder -työkalun avulla. Mutta tätä varten sinun tulee aloittaa järjestämällä laskentataulukko ratkaisujen etsimiseen sopivan mallin mukaan, jota varten sinun on ymmärrettävä hyvin muuttujien ja kaavojen väliset suhteet. Vaikka ongelman muotoilu on yleensä suurin ongelma, mallin valmisteluun käytetty aika ja vaiva ovat täysin perusteltuja, koska saadut tulokset voivat suojata turhalta resurssien tuhlausta, virheellisen suunnittelun tapauksessa auttaa kasvattamaan voittoja optimaalisen taloushallinnon avulla. tai tunnistaa paras tuotantomäärien, varastojen ja tuotenimien suhde.

Olemuksesi takana optimointiongelma on matemaattinen malli tietystä tuotteen tuotantoprosessista, sen jakelusta, varastoinnista, käsittelystä, kuljetuksesta, ostosta tai myynnistä, palvelujen suorittamisesta jne. Tämä on yleinen annettu/etsi/ehto-tyyppinen matemaattinen ongelma, mutta sillä on monia mahdollisia ratkaisuja. Optimointiongelmana on siis tehtävä valita mahdollisista vaihtoehdoista paras, optimaalinen. Ratkaisu tällaiseen ongelmaan on ns suunnitelma tai ohjelmoida esimerkiksi sanotaan - tuotantosuunnitelma tai jälleenrakennusohjelma. Toisin sanoen nämä ovat tuntemattomia, joista meidän on löydettävä esimerkiksi tuotantomäärä, joka antaa suurimman voiton. Optimointiongelmana etsitään ääriarvoa eli tietyn funktion maksimi- tai minimiarvoa, joka on ns. kohdetoiminto Tämä voi olla esimerkiksi voittofunktio – tulot miinus kulut. Koska kaikki maailmassa on rajallista (aika, raha, luonnonvarat ja henkilöresurssit), optimointiongelmat ovat aina varmoja rajoituksia esimerkiksi metallin, työntekijöiden ja koneiden määrä yrityksessä osien valmistukseen. Seuraavassa on esimerkki hyvin yksinkertaisen optimointitehtävän suunnittelusta, mutta sen avulla voit helposti ymmärtää organisaatiota käytännön optimointiongelmien ratkaisujen tehokkuuden taulukon rakentamisesta.

Meillä on klassinen ongelma, kun yritys tuottaa kahdenlaisia ​​tuotteita (tuote A ja tuote B) tietyllä hinnalla, niiden tuotanto vaatii 4 erilaista resurssia (resurssi 1, resurssi 2, resurssi 3, resurssi 4), jotka ovat saatavilla osoitteessa yritykselle tietyssä määrässä (varasto), on myös tietoa siitä, kuinka paljon kutakin resurssia tarvitaan tuottamaan tuotantoyksikkö, vastaavasti tuote A ja tuote B. Meidän on löydettävä tuotteen A ja tuotteen B määrä. maksimoi tulot (tulot) (katso kuva).

Seuraavaksi meidän on tehtävä suhteita rajoitusten, suunnitelman ja tavoitefunktion välille. Tätä varten rakennamme lisäsarakkeen (Käytetty), johon syötämme kaavan SUMMATUOTE(normi; suunnitelma). Normi ​​on tietyn resurssin hinta tavaran A ja B tuotantoyksikön tuotannossa, ja Suunnitelma on etsimämme tuotantomäärä. Syötä kaava Tulo-soluihin SUMMATUOTE(hinta; suunnitelma). Täytimme siis Käytetty-sarakkeen ja Tulot-solun kaavoilla. Koska suunnitelma on muuttujat, joista käytettyjen resurssien määrä ja tulot riippuvat, ovat solut kaavoineen suoraan riippuvaisia ​​tiedoista, jotka tulevat näkyviin ratkaisujen etsimisen tuloksena. Yllä olevasta voimme tehdä seuraavat johtopäätökset, että jokaisessa optimointiongelmassa on oltava kolme komponenttia:

tuntematon(mitä etsimme, eli suunnitelma);

rajoitus tuntemattomille (hakualue);

tavoitefunktio(tavoite, johon etsimme ääripäätä).

Tehokas tietojen analysointityökalu Excel on lisäosa Ratkaisija (Etsi ratkaisua). Sen avulla voit määrittää, millä määritettyjen vaikuttavien solujen arvoilla kohdesolun kaava saa halutun arvon (minimi, maksimi tai jokin arvo). Ratkaisuhakuproseduurille voi asettaa rajoituksia, eikä samoja vaikuttavia soluja tarvitse käyttää. Tietyn arvon laskemiseen käytetään erilaisia ​​matemaattisia hakumenetelmiä. Voit asettaa tilan, jossa saadut muuttujan arvot syötetään automaattisesti taulukkoon. Lisäksi ohjelman tulokset voidaan esittää raportin muodossa. Search for Solutions -ohjelma (alkuperäisessä Excel Solverissa) on lisäosa MS Excel -taulukkolaskentaprosessorille, joka on suunniteltu ratkaisemaan tiettyjä yhtälöjärjestelmiä, lineaarisia ja epälineaarisia optimointitehtäviä, ja sitä on käytetty vuodesta 1991 lähtien. Tämän ohjelman perusversiolla ratkaistavan ongelman kokoa rajoittavat seuraavat rajoitukset:

tuntemattomien lukumäärä (päätösmuuttuja) – 200;

tuntemattomien kaavarajoitusten määrä – 100;

rajoittavien ehtojen (yksinkertainen rajoitus) lukumäärä tuntemattomille on 400.

Solver-ohjelman kehittäjä Frontline System on pitkään erikoistunut kehittämään tehokkaita ja käteviä optimointimenetelmiä, jotka on rakennettu eri valmistajien suosittujen taulukkolaskentaprosessorien ympäristöön (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Niiden käytön korkea tehokkuus selittyy optimointiohjelman ja taulukkolaskentaohjelman integroinnilla. MS Excel -laskentataulukkoprosessorin maailmanlaajuisen suosion ansiosta sen ympäristöön rakennettu Solver-ohjelma on yleisin työkalu optimaalisten ratkaisujen löytämiseen nykyaikaisessa liiketoiminnassa. Oletuksena Etsi ratkaisu -apuohjelma on poistettu käytöstä Excelissä. Aktivoidaksesi sen sisään Excel 2007, napsauta kuvaketta Microsoft Office -painike, klikkaus Excel-asetukset ja valitse sitten luokka Lisäosat. Kentällä Ohjaus valitse arvo Excelin lisäosat ja paina painiketta Mennä. Kentällä Saatavilla olevat lisäosat valitse kohteen vieressä oleva valintaruutu Ratkaisun löytäminen ja paina painiketta OK.

SISÄÄN Excel 2003 ja valitse alla oleva komento Palvelu/lisäosat , valitse näkyviin tulevassa Lisäosat-valintaikkunassa valintaruutu Ratkaisun löytäminen ja napsauta OK-painiketta. Jos näyttöön tulee valintaikkuna, jossa sinua pyydetään vahvistamaan aikomuksesi, napsauta Kyllä. (Saatat tarvita Office-asennus-CD:n.)

Ratkaisuhakumenettely 1. Luo taulukko kaavoilla, jotka määrittävät solujen välisiä suhteita.

2. Valitse kohdesolu, jonka tulee ottaa vaadittu arvo, ja valitse komento: - In Excel 2007 Tietojen analysointi/Ratkaisun löytäminen;

SISÄÄN Excel 2003 ja työkalut alla > Ratkaisija (Työkalut > Etsi ratkaisua). Avautuvan Ratkaisija-apuohjelman valintaikkunan Aseta kohdesolu -kenttä sisältää kohdesolun osoitteen. 3. Aseta Equal To -kytkimet asettaaksesi kohdesolun arvoksi Max (maksimiarvo), Min (minimiarvo) tai Value of (arvo). Jälkimmäisessä tapauksessa kirjoita arvo oikealla olevaan kenttään. 4. Määritä Muuttamalla soluja -kenttään, minkä solujen arvoja ohjelman tulee muuttaa optimaalisen tuloksen etsimiseksi. 5. Luo rajoituksia Subject to the Constraints -luetteloon. Voit tehdä tämän napsauttamalla Lisää-painiketta ja määrittämällä rajoitteen Lisää rajoite -valintaikkunassa.

6. Napsauta Asetukset-painikkeen painiketta ja valitse näkyviin tulevasta ikkunasta valintanappi Ei-negatiiviset arvot (jos muuttujien on oltava positiivisia lukuja), Lineaarinen malli (jos ratkaisemasi ongelma liittyy lineaariseen mallit)

7. Napsauta Ratkaisija-painiketta aloittaaksesi ratkaisun hakuprosessin.

8. Kun Ratkaisijan tulokset -valintaikkuna tulee näkyviin, valitse Säilytä ratkaisuratkaisu tai Palauta alkuperäiset arvot -valintanappi. 9. Napsauta OK.

Ratkaisutyökalun asetukset Maksimiaika- rajoittaa aikaa, joka on varattu ratkaisun etsimiseen ongelmaan. Tähän kenttään voit syöttää ajan sekunteina aina 32 767 asti (noin yhdeksän tuntia); Oletusarvo 100 sopii useimpiin yksinkertaisiin tehtäviin.

Rajoita iteraatioiden määrää- ohjaa ongelman ratkaisuaikaa rajoittamalla laskentajaksojen (iteraatioiden) määrää. Suhteellinen virhe- määrittää laskelmien tarkkuuden. Mitä pienempi tämän parametrin arvo on, sitä suurempi on laskelmien tarkkuus. Toleranssi- on tarkoitettu asettamaan toleranssi poikkeamalle optimaalisesta ratkaisusta, jos vaikuttavan solun arvojoukkoa rajoittaa kokonaislukujoukko. Mitä suurempi toleranssiarvo, sitä vähemmän aikaa kuluu ratkaisun löytämiseen. Lähentyminen- koskee vain epälineaarisia ongelmia. Kun suhteellinen arvon muutos kohdesolussa viimeisen viiden iteraation aikana on pienempi kuin Konvergenssi-kentässä määritetty luku, haku pysähtyy. Lineaarinen malli- nopeuttaa ratkaisun etsintää soveltamalla optimointitehtävään lineaarista mallia. Epälineaarisissa malleissa käytetään epälineaarisia funktioita, kasvutekijää ja eksponentiaalista tasoitusta, mikä hidastaa laskelmia. Ei-negatiiviset arvot- voit asettaa nollan alarajan niille vaikuttaville soluille, joille ei asetettu vastaavaa rajaa Lisää rajoitus -valintaikkunassa. Automaattinen skaalaus- käytetään, kun muutettavien solujen ja kohdesolun numerot eroavat merkittävästi. Näytä iterointitulokset- keskeyttää ratkaisun haun nähdäkseen yksittäisten iteraatioiden tulokset. Lataa malli- Tämän painikkeen painamisen jälkeen avautuu samanniminen valintaikkuna, johon voit syöttää linkin optimointimallin sisältävään solualueeseen. Tallenna malli- näyttää näytöllä samannimisen valintaikkunan, johon voit syöttää linkin optimointimallin tallentamiseen tarkoitettuun solualueeseen. Lineaarinen arviointi- Valitse tämä kytkin, jos haluat toimia lineaarisen mallin kanssa. Neliöllinen arvio- Valitse tämä kytkin, jos haluat toimia epälineaarisen mallin kanssa. Suorat erot- käytetään useimmissa ongelmissa, joissa rajoitusten muutosnopeus on suhteellisen alhainen. Lisää Etsi ratkaisu -työkalun nopeutta. Keskeiset erot- käytetään funktioille, joilla on epäjatkuva derivaatta. Tämä menetelmä vaatii enemmän laskelmia, mutta sen käyttö voi olla perusteltua, jos annetaan viesti, että tarkempaa ratkaisua ei ole mahdollista saada. Newtonin hakumenetelmä - vaatii enemmän muistia, mutta suorittaa vähemmän iteraatioita kuin konjugaattigradienttimenetelmä. Menetelmä konjugaattigradienttien löytämiseksi- toteuttaa konjugaattigradienttimenetelmän, joka vaatii vähemmän muistia, mutta suorittaa enemmän iteraatioita kuin Newtonin menetelmä. Tätä menetelmää tulisi käyttää, jos ongelma on tarpeeksi suuri muistin säästämiseksi tai jos iteraatiot antavat liian vähän eroa peräkkäisissä approksimaatioissa.

Suurin osa laskentataulukon avulla ratkaistavista ongelmista sisältää halutun tuloksen löytämisen tunnettujen lähdetietojen avulla. Mutta Excelillä on työkaluja, joiden avulla voit ratkaista käänteisen ongelman: valitse alkutiedot saadaksesi halutun tuloksen.

Yksi tällainen työkalu onRatkaisun löytäminen, joka on erityisen kätevä ns. "optimointiongelmien" ratkaisemiseen.

Jos et ole käyttänyt sitä ennenRatkaisun löytäminen, sinun on asennettava sopiva lisäosa.

Voit tehdä sen näin:

Excel 2007:tä vanhemmille versioille komennon kautta valikosta Palvelu --> Lisäosat;

Excel 2007:stä lähtien valintaikkunan kauttaExcel-asetukset

Excel 2007:stä lähtien-painiketta aloittaaksesiRatkaisun löytäminen tulee näkyviin välilehdelle Data.

Excel 2007:ää vanhemmissa versioissa samanlainen komento näkyy valikossaPalvelu

Katsotaanpa työprosessia Ratkaisun löytäminen käyttämällä yksinkertaista esimerkkiä.

Esimerkki 1. Bonusten jako

Oletetaan, että olet tuotantoosaston päällikkö ja sinun on jaettava reilusti 100 000 ruplan bonus. osaston työntekijöiden välillä heidän virkapalkkoihinsa suhteessa. Toisin sanoen sinun on valittava suhteellisuuskerroin palkkalisän koon laskemiseksi.

Ensinnäkin luomme taulukon lähtötiedoilla ja kaavoilla, joilla tulos pitäisi saada. Meidän tapauksessamme tulos on palkkion kokonaismäärä. On erittäin tärkeää, että kohdesolu (C8) linkitetään kaavojen kautta haluttuun muutettavaan soluun (E2). Esimerkissä ne yhdistetään välikaavojen avulla, jotka laskevat kunkin työntekijän bonussumman (C2:C7).

Nyt käynnistetään Ratkaisun löytäminen ja aseta tarvittavat parametrit avautuvassa valintaikkunassa. Valintaikkunoiden ulkoasu vaihtelee hieman versioiden välillä:

Excel 2010:stä lähtien

Ennen Excel 2010

Painikkeen painamisen jälkeenEtsi ratkaisu (Suorita)Voit jo nähdä saadun tuloksen taulukosta. Samanaikaisesti näyttöön tulee valintaikkunaRatkaisun etsinnän tulokset.

Excel 2010:stä lähtien

Ennen Excel 2010

Jos taulukossa näkyvä tulos sopii sinulle, niin valintaikkunassa Ratkaisuhaun tulokset Lehdistö OK ja kirjaa tulos taulukkoon. Jos tulos ei sovi sinulle, napsauta Peruuttaa ja palaa taulukon edelliseen tilaan.

Ratkaisu tähän ongelmaan näyttää tältä

Tärkeä: lähdetietoihin tehdyistä muutoksista uuden tuloksen saamiseksi Ratkaisun löytäminen täytyy juosta uudestaan.

Katsotaanpa toista optimointiongelmaa (voiton maksimointi)

Esimerkki 2. Huonekalujen tuotanto (voiton maksimointi)

Yritys valmistaa kahta mallia A ja B esivalmistettuja kirjahyllyjä.

Niiden tuotantoa rajoittavat raaka-aineiden saatavuus (korkealaatuiset levyt) ja koneenkäsittelyaika.

Jokainen malli A -tuote vaatii 3 m²levyt ja tuotemallille B - 4 m². Yritys voi saada toimittajiltaan jopa 1 700 m² levyä viikossa.

Jokainen malli A -tuote vaatii 12 minuuttia koneaikaa, ja tuotemallille B - 30 minuuttia. Koneaikaa voidaan käyttää 160 tuntia viikossa.

Kuinka monta tuotetta kustakin mallista yrityksen tulee tuottaa viikossa maksimaalisen voiton saavuttamiseksi, jos jokainen mallin A tuote tuo 60 ruplaa. voittoa, ja jokainen mallin B tuote maksaa 120 ruplaa. saapunut?

Tiedämme jo menettelyn.

Ensin luomme taulukoita lähdetiedoilla ja kaavoilla. Solujen järjestely arkilla voi olla täysin mielivaltainen, mikä sopii kirjoittajalle. Esimerkiksi kuten kuvassa

Aloitetaan Ratkaisun löytäminenja aseta valintaikkunassa tarvittavat parametrit

1. Kohdesolu B12 sisältää voiton laskentakaavan
2. Optimointiparametri - maksimi
3. Muokatut solut B9:C9
4. Rajoitukset: löydettyjen arvojen on oltava kokonaislukuja, ei-negatiivisia; koneen kokonaisaika ei saa ylittää 160 tuntia (viittaus soluun D16); raaka-aineiden kokonaismäärä ei saa ylittää 1700 m² (viitesolu D15). Täällä solujen D15 ja D16 linkkien sijasta voidaan määrittää numeroita, mutta linkkejä käytettäessä rajoituksiin voidaan tehdä muutoksia suoraan taulukkoon
5. painaa nappia Etsi ratkaisu (Suorita) ja vahvistuksen jälkeen saamme tuloksen

Mutta vaikka loisit kaavat oikein ja asetit rajoitukset, tulos voi olla odottamaton. Esimerkiksi, kun ratkaiset tämän ongelman, saatat nähdä seuraavan tuloksen:

Ja tämä huolimatta siitä, että rajoitus asetettiin koko. Tällaisissa tapauksissa voit yrittää säätää asetuksia Ratkaisun löytäminen. Voit tehdä tämän ikkunassa Ratkaisun löytäminen painaa nappia Vaihtoehdot ja pääsemme samannimiseen valintaikkunaan

Ensimmäinen valituista parametreista vastaa laskelmien tarkkuudesta. Pienentämällä sitä voit saavuttaa tarkemman tuloksen, meidän tapauksessamme - kokonaislukuarvot. Toinen korostetuista vaihtoehdoista (saatavilla Excel 2010:stä alkaen) vastaa kysymykseen: kuinka murto-osia voitaisiin saada rajattaessakoko? Osoittautuu Ratkaisun löytäminentämä rajoitus yksinkertaisesti ohitettiin valitun lipun mukaisesti.

Esimerkki 3. Kuljetusongelma (kustannusten minimoiminen)

Rakennusyrityksen tilauksesta hiekkaa kuljetetaan kolmelta toimittajalta (louhokselta) viidelle kuluttajalle (rakennustyömaalle). Toimituskulut sisältyvät projektin hintaan, joten rakennusyritys on kiinnostunut täyttämään työmaansa hiekkatarpeet halvimmalla tavalla.

Annettu: hiekkavarannot louhoksissa; rakennustyömaalla hiekka tarpeet; kuljetuskustannukset kunkin toimittaja-kuluttaja-parin välillä.

On tarpeen löytää tarpeita tyydyttävä optimaalinen kuljetusjärjestelmä (mistä ja minne), jossa kokonaiskuljetuskustannukset olisivat minimaaliset.

Kuvassa on esimerkki solujen sijainnista lähdetiedoilla ja rajoituksilla, halutuista soluista ja kohdesolusta

Harmaissa soluissa on kaavat summille riveittäin ja sarakkeittain ja kohdesolussa on kaava kokonaiskuljetuskustannusten laskemiseen.

Käynnistämme Etsi ratkaisun ja asetamme tarvittavat parametrit (katso kuva)

Klikkaus Etsi ratkaisu (Suorita) ja saat alla näkyvän tuloksen

Lopuksi suosittelen, että kokeilet käyttöä Ratkaisun löytäminen ja käytä sitä vanhan ongelman ratkaisemiseen:

Talonpoika osti torilta 100 nautaeläintä 100 ruplalla. Härkä maksaa 10 ruplaa, lehmä 5 ruplaa, vasikka 50 kopekkaa. Kuinka monta sonnia, lehmää ja vasikkaa talonpoika osti?

Ongelman muotoilu

Oletetaan, että yrityksessä, jossa työskentelet, on kaksi varastoa, joista tavarat toimitetaan viiteen myymäläänne hajallaan Moskovassa.

Jokainen myymälä pystyy myymään tietyn määrän meille tunnettuja tavaroita. Jokaisessa varastossa on rajoitettu kapasiteetti. Tehtävänä on rationaalisesti valita, mistä varastosta mihinkin myymälöihin tavarat on toimitettava kokonaiskuljetuskustannusten minimoimiseksi.

Ennen kuin aloitat optimoinnin, sinun on luotava yksinkertainen taulukko Excel-arkille - matemaattinen mallimme, joka kuvaa tilannetta:

Se tarkoittaa, että:

• Harmaa taulukko (B3:G5) kuvaa yksikkölähetyskustannukset kustakin varastosta kuhunkin kauppaan.
• Violet solut (C14:G14) kuvaavat kunkin myytävän myymälän tarvittavan tavaramäärän.
• Punaiset solut (J10:J11) näyttävät kunkin varaston kapasiteetin - varastoon mahtuvan tavaran enimmäismäärän.
• Keltaiset (C12:G12) ja siniset (H10:H11) solut ovat vihreiden solujen rivi- ja sarakesummat.
• Kokonaistoimitushinta (E17) lasketaan tavaramäärän tuotteiden ja niitä vastaavien toimituskulujen summana.

Siten meidän tehtävämme on valita vihreiden solujen optimaaliset arvot. Lisäksi, jotta rivin kokonaismäärä (siniset solut) ei ylitä varastokapasiteettia (punaiset solut) ja samalla jokainen myymälä saa myyntiin tarvitsemansa tavaramäärän (jokaisen myymälän määrä keltaisessa solujen tulee olla mahdollisimman lähellä vaatimuksia - violetit solut).

Ratkaisu

Matematiikassa samanlaisia ​​ongelmia resurssien optimaalisen allokoinnin valinnassa on muotoiltu ja kuvattu kauan sitten. Ja tietysti tapoja ratkaista ne on kehitetty pitkään. Excel antaa käyttäjälle yhden niistä - tehokkaan apuohjelman avulla Ratkaisun löytäminen(Ratkaisija), saatavana Excel 2003:ssa valikon kautta Palvelu(työkalut) tai välilehdeltä Data Excelin uusissa versioissa.

Jos valikossa Palvelu tai välilehdellä Data Excelissäsi ei ole tällaista komentoa – se on okei – se tarkoittaa, että apuohjelmaa ei yksinkertaisesti ole vielä yhdistetty. Liitä se seuraavasti:

• Excel 2003:ssa ja vanhemmissa - avaa valikko Palvelu – Lisäosat(Työkalut – Lisätä- Ins) , valitse avautuvassa ikkunassa valintaruutu Ratkaisun löytäminen(Ratkaisija) ja paina OK. Excel aktivoi valitun apuohjelman ja valikon Palvelu (Työkalut) uusi joukkue ilmestyy - Ratkaisun löytäminen (Ratkaisija) .
• Excel 2007:ssä ja uudemmissa - napsauta painiketta Toimisto ja valitse sitten VaihtoehdotExcel – Lisäosat – Mennä(Excel-asetukset - Apuohjelmat - Siirry) .

Käynnistetään lisäosa. Seuraava ikkuna avautuu:

Tässä ikkunassa sinun on määritettävä seuraavat asetukset:

Fyysisiin tekijöihin liittyvien ilmeisten rajoitusten (varastojen ja kuljetusajoneuvojen kapasiteetti, budjetti ja aikarajat jne.) lisäksi on joskus tarpeen lisätä rajoituksia "erityisesti Excelille". Meidän tapauksessamme meidän on esimerkiksi lisättävä seuraava rajoitus:

Se selventää lisäksi, että kuljetettujen tavaroiden määrä (vihreät solut) ei voi olla negatiivinen - henkilölle tämä on itsestään selvää, mutta tietokoneelle se on kirjoitettava nimenomaisesti.

Kun kaikki tarvittavat parametrit on asetettu, ikkunan pitäisi näyttää tältä:

Nyt kun laskentatiedot on syötetty, paina -painiketta Suorittaa(Ratkaista) aloittaaksesi optimoinnin. Vakavissa tapauksissa, joissa on paljon vaihtuvia soluja ja rajoituksia, ratkaisun löytäminen voi kestää kauan, mutta tehtävämme Excelille ei ole ongelma - muutamassa hetkessä saamme seuraavat tulokset:

Huomioithan, kuinka mielenkiintoisesti toimitusmäärät jakautuivat myymälöittäin ylittämättä varastojemme kapasiteettia ja tyydyttämättä kaikkia pyyntöjä tarvittavasta tavaramäärästä jokaiselle myymälälle.

Jos löydetty ratkaisu sopii meille, voimme tallentaa sen tai palata alkuperäisiin arvoihin ja yrittää uudelleen muilla parametreilla. Voit myös tallentaa valitun parametriyhdistelmän nimellä Skenaario. Käyttäjän pyynnöstä Excel voi rakentaa kolmea tyyppiä Raportit ratkaistavalle ongelmalle erillisillä arkeilla: raportti tuloksista, raportti ratkaisun matemaattisesta stabiilisuudesta ja raportti ratkaisun rajoista (rajoituksista), mutta useimmissa tapauksissa ne kiinnostavat vain asiantuntijoita .

On kuitenkin tilanteita, joissa Excel ei löydä sopivaa ratkaisua. Voit simuloida tällaista tapausta, jos määrität esimerkissämme myymälätarpeet suurempana kuin varastojen kokonaiskapasiteetti. Sitten optimoinnin yhteydessä Excel yrittää päästä mahdollisimman lähelle ratkaisua ja näyttää sitten viestin, että se ei löydä ratkaisua. Kuitenkin tässäkin tapauksessa meillä on paljon hyödyllistä tietoa - erityisesti voimme nähdä liiketoimintaprosessiemme "heikot lenkit" ja ymmärtää parantamisen varaa.

Tarkasteltu esimerkki on tietysti suhteellisen yksinkertainen, mutta skaalautuu helposti ratkaisemaan paljon monimutkaisempia epälineaarisia ongelmia. Esimerkiksi:

• Rahoitusallokoinnin optimointi liiketoimintasuunnitelman tai projektibudjetin kuluerien mukaan. Rajoituksia ovat tässä tapauksessa rahoituksen määrä ja projektin ajoitus, ja optimoinnin tavoitteena on maksimoida voitot ja minimoida projektin kustannukset.
• Työntekijöiden aikataulujen optimointi yrityksen palkkarahaston minimoimiseksi. Rajoitukset ovat tässä tapauksessa jokaisen työntekijän toiveita työaikataulun ja henkilöstötaulukon vaatimusten suhteen.
• Investointien optimointi – tarve jakaa viisaasti varoja useiden pankkien välillä, arvopapereita tai yritysten osakkeita tavoitteena jälleen maksimoida voitot tai (jos tämä on tärkeämpää) minimoida riskit.

Joka tapauksessa, lisä Ratkaisun löytäminen(Ratkaisija) on erittäin tehokas ja kaunis Excel-työkalu ja ansaitsee huomiosi, sillä se voi auttaa monissa vaikeissa tilanteissa, joita nykyaikaisessa liiketoiminnassa joutuu kohtaamaan.