Uporaba logične funkcije, če primeri. Funkcija Microsoft Excel: Iskanje rešitve
Reševanje nelinearnih enačb in sistemov "
namen dela: Raziskovanje zmogljivosti MS Excel 2007 pri reševanju nelinearnih enačb in sistemov. Pridobitev spretnosti reševanja nelinearnih enačb in sistemov s pomočjo paketa.
Vaja 1. Poiščite korenine polinoma x 3 - 0,01-krat 2 - 0,7044x + 0,139104 \u003d 0.
Najprej enačbo rešimo grafično. Znano je, da je grafična rešitev enačbe f (x) \u003d 0 presečišče grafa funkcije f (x) z absciso, tj. vrednost x, pri kateri funkcija izgine.
Zapišimo svoj polinom na interval od -1 do 1 s korakom 0,2. Rezultati izračuna so prikazani na sliki, kjer je bila formula vnesena v celico B2: \u003d A2 ^ 3 - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0,139104. Iz grafa je razvidno, da funkcija trikrat prečka os Ox, in ker ima polinom tretje stopnje največ tri resnične korenine, je bila najdena grafična rešitev problema. Z drugimi besedami, korenine so bile lokalizirane, tj. določeni so intervali, na katerih so korenine tega polinoma: [-1, -0,8] in.
Zdaj lahko z zaporedjem približkov z ukazom poiščete korenine polinoma Podatki → Delo s podatki → Kaj-če analiza → Izbira parametra.
Po vnosu začetnih približkov in vrednosti funkcije se lahko obrnete na ukaz Podatki → Delo s podatki → Kaj-če analiza → Izbira parametra in zapolnite pogovorno okno, kot sledi.
Na terenu Nastavitev v celicidaje se sklic na celico, v katero je vnesena formula, ki izračuna vrednost leve strani enačbe (enačba mora biti zapisana tako, da njena desna stran ne vsebuje spremenljivke). Na terenu Vrednost vnesemo desno stran enačbe in v polje Spreminjanje vrednosti celic podana je referenca na celico, ki je dodeljena spremenljivki. Upoštevajte, da vnos sklicev na celice v polja pogovornega okna Izbira parametrov priročneje je ne s tipkovnice, temveč s klikom na ustrezno celico.
Po pritisku gumba OK se prikaže pogovorno okno Rezultat izbire parametra s sporočilom o uspešnem zaključku iskanja rešitve, približna vrednost korena pa bo postavljena v celico A14.
Na enak način najdemo preostali dve korenini. Rezultati izračuna bodo shranjeni v celicah A15 in A16.
Naloga 2. Reši enačbo e x - (2x - 1) 2 = 0.
Izvedimo lokalizacijo korenin nelinearne enačbe.
Za to ga predstavimo v obliki f (x) \u003d g (x), tj. e x \u003d (2x - 1) 2 ali f (x) \u003d e x, g (x) \u003d (2x - 1) 2 in rešite grafično.
Grafična rešitev enačbe f (x) \u003d g (x) bo presečišče premic f (x) in g (x).
Zgradimo grafa f (x) in g (x). Če želite to narediti, vnesite vrednosti argumenta v obseg A3: A18. V celico B3 vnesemo formulo za izračun vrednosti funkcije f (x): \u003d EXP (A3), v C3 pa za izračun g (x): \u003d (2 * A3-1) ^ 2.
Rezultati izračuna in risanje grafov f (x) in g (x):
Iz grafa je razvidno, da se črti f (x) in g (x) dvakrat sekata, t.j. ta enačba ima dve rešitvi. Eden od njih je nepomemben in ga je mogoče natančno izračunati:
Za drugo lahko določimo interval izolacije korenin: 1.5< x < 2.
Zdaj lahko najdete koren enačbe na odseku z metodo zaporednih približkov.
Vnesite začetni približek v celico H17 \u003d 1,5, enačba pa s sklicevanjem na začetni približek v celico I17 \u003d EXP (H17) - (2 * H17-1) ^ 2.
in izpolnite pogovorno okno Izbira parametrov.
Rezultat iskanja rešitve bo prikazan v celici H17.
Naloga3 . Rešite sistem enačb:
Pred uporabo zgoraj opisanih metod za reševanje sistemov enačb bomo našli grafično rešitev tega sistema. Upoštevajte, da sta obe enačbi sistema podani implicitno in je za izdelavo grafov funkcij, ki ustrezajo tem enačbam, potrebno rešiti dane enačbe glede na spremenljivko y.
Za prvo enačbo sistema imamo:
Ugotovimo ODV nastale funkcije:
Druga enačba tega sistema opisuje krog.
Fragment delovnega lista MS Excel s formulami, ki jih je treba vnesti v celice za gradnjo črt, opisanih z enačbami sistema. Presečišča prikazanih črt so grafična rešitev sistema nelinearnih enačb.
Ni težko videti, da ima dani sistem dve rešitvi. Zato je treba postopek iskanja rešitev sistema izvesti dvakrat, pri čemer smo predhodno določili interval izolacije korenin vzdolž osi Ox in Oy. V našem primeru je prvi koren v intervalih (-0,5; 0) x in (0,5; 1) y, drugi pa (0; 0,5) x in (-0,5; -1) y. Nato bomo nadaljevali, kot sledi. Uvedimo začetne vrednosti spremenljivk x in y, formuli, ki predstavljata enačbi sistema in ciljno funkcijo.
Zdaj bomo uporabili ukaz Podatki → Analiza → Poiščite rešitve dvakrat, tako da izpolnite pogovorna okna, ki se prikažejo.
Če primerjamo dobljeno rešitev sistema z grafično, poskrbimo, da je sistem pravilno rešen.
Naloge za samopomoč
Vaja 1... Poiščite korenine polinoma
Naloga 2... Poiščite rešitev nelinearne enačbe.
Naloga 3... Poiščite rešitev za sistem nelinearnih enačb.
Kot že veste, formule v Microsoft Excel vam omogočajo, da določite vrednost funkcije z njenimi argumenti. Lahko pa pride do situacije, ko je vrednost funkcije znana in je treba najti argument (tj. Za rešitev enačbe). Za reševanje takšnih težav obstaja posebna funkcija Iskanje ciljev .
Iskanje parametrov.
Posebna funkcija Iskanje ciljev omogoča definiranje parametra (argumenta) funkcije, če je njena vrednost znana. Ko je izbran parameter, se vrednost vplivne celice (parametra) spreminja, dokler formula, odvisno od te celice, ne vrne podane vrednosti.
Razmislimo o postopku iskanja parametra na preprostem primeru: rešimo enačbo 10 * x - 10 / x \u003d 15
... Tu je parameter (argument) - x
... Naj bo celica A3
... V to celico vnesite poljubno število, ki spada v obseg definicije funkcije (v našem primeru to število ne more biti enako nič). Ta vrednost bo uporabljena kot začetna vrednost. Naj bo 3
... Uvedimo formulo \u003d 10 * A3-10 / A3
, s katero je treba pridobiti zahtevano vrednost, v neko celico, na primer B3
... Zdaj lahko funkcijo iskanja parametrov zaženete z izbiro ukaza Iskanje ciljev
v meniju Orodja
... Vnesite iskalne parametre:
- Na terenu Nastavi celico vnesite sklic na celico, ki vsebuje želeno formulo.
- V polje vnesite rezultat iskanja Ovrednotiti .
- Na terenu S spreminjanjem celice vnesite sklic na celico, ki vsebuje vrednost, ki se ujema.
- Kliknite na tipko v redu .
Na koncu funkcije se na zaslonu prikaže okno, v katerem bodo prikazani rezultati iskanja. Najdeni parameter se bo pojavil v celici, ki je bila zanj rezervirana. Bodite pozorni na dejstvo, da ima enačba v našem primeru dve rešitvi, parameter pa je izbran le eden - to je zato, ker se parameter spreminja samo, dokler ni vrnjena zahtevana vrednost. Prvi tako najden argument se nam vrne kot rezultat iskanja. Če v našem primeru določimo kot začetno vrednost -3
, potem bo najdena druga rešitev enačbe: -0,5
.
Precej težko je pravilno določiti najprimernejšo začetno vrednost. Pogosteje lahko predpostavimo nekaj o želenem parametru, na primer, mora biti parameter celo število (takrat dobimo prvo rešitev naše enačbe) ali nepozitivno (druga rešitev).
Nalogi iskanja parametra z vsiljenimi robnimi pogoji bo pomagal poseben dodatek Microsoft Excel Reševalec .
Poiščite rešitev.
Dodatek za Microsoft Excel Reševalec ni samodejno nameščen v tipični namestitvi:
- V meniju Orodja izberite ekipo Dodatki ... Če je pogovorno okno Dodatki ne vsebuje ukaza Reševalec , pritisni gumb Brskaj in določite pogon in mapo, ki vsebuje datoteko dodatka Reševalec.xla (praviloma je to mapa Knjižnica \\ Reševalec ) ali zaženite program microsoftove namestitve Office, če ne najde datoteke.
- V pogovornem oknu Dodatki potrdite polje Reševalec .
Postopek iskanja rešitve vam omogoča, da poiščete optimalno vrednost formule v celici, ki se imenuje tarča. Ta postopek deluje s skupino celic, povezanih s formulo v ciljni celici. Postopek spreminja vrednosti v vplivnih celicah, dokler ne doseže optimalnega rezultata na podlagi formule v ciljni celici. Za zožitev nabora vrednosti se uporabljajo omejitve, ki se lahko sklicujejo na druge vplivne celice. Z iskanjem rešitev lahko določite tudi vrednost vplivne celice, ki ustreza ekstremu ciljne celice, na primer število vadb, ki maksimirajo akademsko uspešnost.
V pogovornem oknu Reševalec
enako kot v pogovornem oknu Iskanje ciljev
, morate določiti ciljno celico, njeno vrednost in celice, ki jih je treba spremeniti za dosego cilja. Za reševanje težav z optimizacijo je treba določiti ciljno celico, ki je enaka največji ali najmanjši vrednosti.
Če kliknete na gumb Ugani Excel bo poskusil najti vse celice, ki vplivajo na samo formulo.
Mejne pogoje lahko dodate s klikom na tipko Dodaj .
S klikom na gumb Opcije , lahko spremenite pogoje za iskanje rešitve: največji čas za iskanje rešitve, število ponovitev, natančnost rešitve, toleranca za odstopanja od optimalne rešitve, metoda ekstrapolacije (linearna ali kvadratna), optimizacija algoritem itd.
Vrnimo se k prejšnjemu primeru: da dobimo drugo (nepozitivno) rešitev, je dovolj dodati mejni pogoj A3 ... Tako kot pri izbiri parametra se bo tudi na zaslonu prikazalo okno, v katerem bo prikazano poročilo o rezultatih iskanja za zahtevano rešitev. Raztopina bo prikazana v celicah, ki so ji namenjene (v celici A3 vrednost se prikaže -0.50 ).
Dodatek za Microsoft Excel Reševalec
omogoča tudi reševanje sistemov enačb ali neenakosti. Poglejmo si preprost primer: poskusimo rešiti sistem enačb
x + y \u003d 2
x - y \u003d 0
Ena najbolj zanimivih lastnosti v microsoftov program Excel išče rešitev. Vendar je treba opozoriti, da tega orodja ni mogoče uvrstiti med najbolj priljubljena med uporabniki v tej prilogi... A zaman. Navsezadnje ta funkcija s pomočjo naštevanja s pomočjo naštevanja najde najbolj optimalno rešitev od vseh razpoložljivih. Ugotovimo, kako uporabiti funkcijo Najdi rešitev v programu Microsoft Excel.
Dolgo lahko iščete na traku, kjer je Iskanje rešitve, vendar tega orodja še vedno ne najdete. Če želite to funkcijo aktivirati, jo morate omogočiti v nastavitvah programa.
Če želite aktivirati iskanje rešitev v programu Microsoft Excel 2010 in novejših različicah, pojdite na zavihek "Datoteka". Za različico 2007 kliknite gumb Microsoft Office v zgornjem levem kotu okna. V oknu, ki se odpre, pojdite na razdelek »Parametri«.
V oknu s parametri kliknite element »Dodatki«. Po prehodu v spodnjem delu okna, nasproti parametra "Control", izberite vrednost "Excel Add-ins" in kliknite gumb "Go".
Odpre se okno z dodatki. Postavimo kljukico pred ime dodatka, ki ga potrebujemo - »Poiščite rešitev«. Kliknite gumb "V redu".
Po tem se na traku Excel na zavihku Podatki prikaže gumb za zagon funkcije Iskanje rešitev.
Priprava mize
Zdaj, ko smo aktivirali funkcijo, poglejmo, kako deluje. To si najlažje predstavljamo s konkretnim primerom. Torej imamo mizo plače zaposleni v podjetju. Izračunati moramo dodatek za vsakega zaposlenega, ki je zmnožek plač, ki so v posebnem stolpcu označene z določenim koeficientom. Hkrati je skupni znesek sredstev, dodeljenih za nagrado, enak 30.000 rubljev. Celica, v kateri se nahaja ta znesek, ima ime ciljne, saj je naš cilj natančno izbrati podatke za to številko.
Koeficient, ki se uporablja za izračun zneska premije, moramo izračunati s pomočjo funkcije Iskanje rešitev. Celica, v kateri se nahaja, se imenuje želena.
Ciljna celica in ciljna celica morata biti povezani s formulo. V našem konkretnem primeru se formula nahaja v ciljni celici in ima naslednjo obliko: "\u003d C10 * $ G $ 3", kjer je $ G $ 3 absolutni naslov iskane celice, "C10" pa je skupna plača, iz katere se izračuna dodatek zaposlenim v podjetju.
Zagon orodja za iskanje rešitve
Po pripravi tabele na zavihku "Podatki" kliknite gumb "Poišči rešitev", ki se nahaja na traku v orodni vrstici "Analiza".
Odpre se okno s parametri, v katerega morate vnesti podatke. V polje "Optimizirajte ciljno funkcijo" morate vnesti naslov ciljne celice, kjer bo skupni znesek bonusa za vse zaposlene. To lahko storite z ročnim vpisom koordinat ali s klikom na gumb na levi strani polja za vnos podatkov.
Po tem se okno s parametri zmanjša in lahko izberete želeno celico tabele. Nato morate znova klikniti isti gumb na levi strani obrazca z vnesenimi podatki, da znova razširite okno parametrov.
Pod oknom z naslovom ciljne celice morate nastaviti parametre vrednosti, ki bodo v njej. Lahko je največja, najmanjša ali določena vrednost. V našem primeru bo to zadnja možnost. Zato smo stikalo postavili v položaj »Vrednosti«, v polje levo od njega pa zapišemo številko 30 000. Kot se spomnimo, je ta številka glede na pogoje skupni znesek bonusa za vse zaposlene podjetja.
Spodaj je polje "Spreminjanje spremenljivih celic". Tukaj morate navesti naslov želene celice, kjer je, kot se spomnimo, najden koeficient, tako da pomnožite osnovno plačo, s katero bo izračunan znesek bonusa. Naslov lahko zapišemo na enak način, kot smo to storili za ciljno celico.
V polju »Glede na omejitve« lahko nastavite določene omejitve za podatke, na primer vrednosti nastavite v celoti ali nenegativne. Če želite to narediti, kliknite gumb "Dodaj".
Po tem se odpre okno za dodajanje omejitve. V polje »Povezava do celic« napišite naslov celic, za katere je uvedena omejitev. V našem primeru je to želena celica s koeficientom. Nato postavimo zahtevani znak: "manj kot ali enako", "večje ali enako", "enako", "celo število", "binarno" itd. V našem primeru bomo izbrali znak več ali enak, da bo koeficient pozitivno število. V skladu s tem v polju "Omejitev" navedite številko 0. Če želimo konfigurirati drugo omejitev, kliknite gumb "Dodaj". V nasprotnem primeru kliknite gumb »V redu«, da shranite vnesene omejitve.
Kot lahko vidite, se po tem omejitev prikaže v ustreznem polju okna s parametri iskanja rešitve. Spremenljivke lahko tudi spremenite v negativne, tako da potrdite polje poleg ustreznega parametra tik spodaj. Priporočljivo je, da tukaj nastavljeni parameter ne nasprotuje tistim, ki ste jih predpisali v omejitvah, sicer lahko pride do konflikta.
Dodatne nastavitve lahko nastavite s klikom na gumb "Parametri".
Tu lahko nastavite natančnost omejitve in meje rešitve. Ko vnesete zahtevane podatke, kliknite gumb "V redu". Toda v našem primeru teh parametrov ni treba spreminjati.
Po nastavitvi vseh nastavitev kliknite gumb "Poišči rešitev".
Nadalje program Excel v celicah izvede potrebne izračune. Hkrati z izhodom rezultatov se odpre okno, v katerem lahko shranite najdeno rešitev ali obnovite prvotne vrednosti s premikanjem stikala v ustrezen položaj. Ne glede na izbrano možnost lahko s potrditvijo polja »Vrni se v pogovorno okno z možnostmi« znova odprete nastavitve za iskanje rešitve. Ko so potrditvena polja in stikala nastavljena, kliknite gumb "V redu".
Če vas iz nekega razloga rezultati iskanja rešitev ne zadovoljijo ali program pri izračunu prikaže napako, se v tem primeru, kot je opisano zgoraj, vrnemo v pogovorno okno s parametri. Pregledujemo vse vnesene podatke, saj je morda nekje prišlo do napake. Če napake ni bilo mogoče najti, pojdite na parameter "Izberite način rešitve". Tu lahko izberete eno od treh načinov izračuna: "Iskanje rešitve nelinearnih problemov po metodi OPG", "Iskanje rešitve linearnih problemov po simpleksni metodi" in "Evolucijsko iskanje rešitve". Privzeto se uporablja prva metoda. Težavo poskušamo rešiti z izbiro katere koli druge metode. V primeru okvare poskusimo znova po zadnji metodi. Algoritem dejanj je enak, kot smo ga opisali zgoraj.
Kot lahko vidite, je funkcija Iskanje rešitve precej zanimivo orodje, ki kdaj pravilna uporaba, lahko znatno prihrani čas uporabnika pri različnih izračunih. Na žalost vsak uporabnik ne ve za njegov obstoj, da ne omenjam, kako pravilno delati s tem dodatkom. V nekaterih pogledih je to orodje podobno funkciji , hkrati pa ima s tem tudi bistvene razlike.
Če v celica Excel uvede se formula, ki vsebuje povezavo do iste celice (morda ne neposredno, ampak posredno - skozi verigo drugih povezav), potem pravijo, da obstaja ciklična referenca (cikel). V praksi se ciklični sklici uporabljajo pri izvajanju iterativnega postopka, ki se izračuna po relacijah ponavljanja. V običajnem način Excel zazna cikel in izda sporočilo o nastali situaciji ter zahteva njegovo odpravo. Excel ne more izvajati izračunov, ker krožne reference ustvarjajo neskončno število izračunov. Iz te situacije obstajata dva izhoda: odpravite krožne reference ali dovolite izračune s pomočjo formul s krožnimi referencami (v slednjem primeru mora biti število ponovitev ciklov končno).
Razmislite o problemu iskanja korena enačbe z Newtonovo metodo z uporabo cikličnih referenc. Za primer vzemimo kvadratno enačbo: x 2 - 5x + 6 \u003d 0, katerega grafični prikaz je prikazan v. Koren te (in katere koli druge) enačbe lahko najdete z uporabo samo ene celice Excel.
Za omogočanje cikličnega računalništva v meni Orodja / zavihek Možnosti / Izračuni vklopite potrditveno polje Ponavljanjepo potrebi spremenite število ponovitev zanke v polju Omejeno število ponovitev in natančnost izračunov na terenu Relativna napaka (privzeto so njihovi vrednosti 100 oziroma 0.0001). Poleg teh nastavitev izberemo še možnost izračuna: samodejno ali ročno... Kdaj samodejno izračun Excel takoj pri izračunu da končni rezultat ročno, lahko opazujete rezultat vsake ponovitve.
| Sl. 8. Graf funkcij |
Izberimo poljubno celico, ji damo novo ime, recimo - Xin vanj vnesite ponavljajočo se formulo, ki določa izračune po Newtonovi metodi:
,
kje F in F1 podajte izraze za izračun vrednosti funkcije oziroma njenega odvoda. Za našo kvadratno enačbo se bo vrednost po vnosu formule pojavila v celici 2
ki ustreza eni od korenin enačbe (). V našem primeru začetni približek ni bil določen, iterativni računski postopek se je začel s privzeto vrednostjo, shranjeno v celici X in enako nič. Kako dobite drugi koren? Običajno je to mogoče storiti s spremembo začetnega ugibanja. Problem določanja začetnih nastavitev v vsakem primeru lahko rešite na različne načine. Predstavili bomo eno tehniko, ki temelji na uporabi funkcije IF. Za izboljšanje jasnosti izračunov so celice dobile smiselna imena (). 
2.2. Izbira parametrov
Ko poznate želeni rezultat formule, vendar ne poznate vrednosti, potrebnih za pridobitev tega rezultata, lahko uporabite orodje Izbira parametrovz izbiro ukaza Izbira parametrov v meniju Storitev... Ko izberete možnost, Excel spremeni vrednost v določeni celici, dokler formula, ki se sklicuje na to celico, ne da želenega rezultata.
Vzemimo za primer isto kvadratno enačbo x 2 -5x + 6 \u003d 0... Če želite najti korenine enačbe, naredite naslednje:
Excel za izbiro parametra uporablja iterativni (krožno) postopek. Število ponovitev in natančnost sta nastavljena v meniju Zavihek Orodja / Možnosti / Izračuni... Če Excel izvaja zapleteno nalogo določanja parametra, lahko kliknete Pavza v pogovornem oknu Rezultat izbire parametra in prekinite izračun, nato pritisnite gumb Korakponoviti ponovitev in videti rezultat. Pri reševanju težave v postopnem načinu se prikaže gumb Nadaljujte - za vrnitev v običajni način izbire parametrov.
Vrnimo se k primeru. Spet se postavlja vprašanje: kako priti do drugega korena? Kot v prejšnjem primeru je treba določiti začetni približek. To je mogoče storiti tako ():
 |
| in |
 |
| b |
| Sl. 11. Iskanje drugega korena |
Vse to pa je mogoče narediti na nekoliko preprostejši način. Da bi našli drugi koren, je dovolj, da konstanto v celico C2 damo kot začetni približek () 5 in po tem začnite postopek Izbira parametrov.
2.3. Iskanje rešitve
Ekipa Izbira parametrov je primeren za reševanje problemov iskanja določene ciljne vrednosti, odvisno od enega neznanega parametra. Za bolj zapletena opravila uporabite ukaz Iskanje rešitve (Reševalec), do katerega dostopate prek menija Storitev / iskanje rešitve.
Naloge, s katerimi je mogoče rešiti Iskanje rešitve, v splošnem okolju so oblikovani tako:
Najti:
x 1, x 2, ..., x n
tako, da:
F (x 1, x 2, ..., x n)\u003e (največ; najmanj; \u003d vrednost)
z omejitvami:
G (x 1, x 2, ..., x n)\u003e (Ј vrednost; i vrednost; \u003d vrednost)
Iskane spremenljivke - celice delavcev excelov delovni list - imenujemo nastavljive celice. Ciljna funkcija F (x 1, x 2, ..., x n)včasih omenjen preprosto kot cilj, mora biti naveden kot formula v celici delovnega lista. Ta formula lahko vsebuje uporabniško določene funkcije in mora biti odvisna od (sklica) nastavljivih celic. V trenutku določanja problema je določeno, kaj storiti s ciljno funkcijo. Izberete lahko eno od možnosti:
- poiščite maksimum ciljne funkcije F (x 1, x 2, ..., x n);
- poiščite minimum ciljne funkcije F (x 1, x 2, ..., x n);
- doseči to ciljno funkcijo F (x 1, x 2, ..., x n) je imel fiksno vrednost: F (x 1, x 2, ..., x n) \u003d a.
Funkcije G (x 1, x 2, ..., x n) se imenujejo omejitve. Lahko jih določimo v obliki enakosti in neenakosti. Za regulirane celice se lahko naložijo dodatne omejitve: nenegativnost in / ali celo število, nato se iskana rešitev išče v območju pozitivnih in / ali celih števil.
Ta formulacija zajema najširši spekter optimizacijskih problemov, vključno z reševanjem različnih enačb in sistemov enačb, linearnimi in nelinearnimi programi. Takšne naloge je običajno lažje oblikovati kot rešiti. Za rešitev posebnega problema z optimizacijo je potrebna posebej zasnovana metoda. Reševalec ima v svojem arzenalu močna orodja za reševanje tovrstnih problemov: metoda splošnega gradienta, simpleksna metoda, vejna in vezana metoda.
Zgoraj je bila za iskanje korenin kvadratne enačbe uporabljena Newtonova metoda (oddelek 1.4) z uporabo cikličnih referenc () in orodja Izbira parametrov (). Poglejmo, kako uporabiti Iskanje rešitve na primeru iste kvadratne enačbe.
Po odprtju dialoga Iskanje rešitve () morate narediti naslednje:- na terenu Nastavi ciljno celico vnesite naslov celice, ki vsebuje formulo za izračun vrednosti optimizirane funkcije, v našem primeru je ciljna celica C4, formula v njej pa: \u003d C3 ^ 2 - 5 * C3 + 6;
- da maksimirate vrednost ciljne celice, nastavite izbirni gumb največja vrednost v položaj 8 se stikalo uporablja za zmanjšanje najmanjša vrednost, v našem primeru nastavite stikalo na vrednost in vnesite vrednost 0 ;
- na terenu Spreminjanje celic vnesite naslove celic, ki jih želite spremeniti, tj. argumenti ciljne funkcije (C3), ki jih loči z znakom ";" (ali s klikom na miško med pritiskom na tipko Ctrl na ustreznih celicah) za samodejno iskanje vseh celic, ki vplivajo na raztopino, uporabite gumb Ugani;
- na terenu Omejitve z gumbom Dodati k vnesite vse omejitve, ki jih mora izpolnjevati rezultat iskanja: za naš primer vam ni treba nastaviti omejitev;
- za začetek postopka iskanja rešitve pritisnite gumb Izvedite.
Če želite shraniti dobljeno rešitev, morate uporabiti stikalo Shrani najdeno rešitev v odprtem pogovornem oknu Rezultati iskanja rešitev... Po tem bo delovni list prevzel obliko, prikazano v. Nastala rešitev je odvisna od izbire začetnega približka, ki je določen v celici C4 (argument funkcije). Če vnesete vrednost, enako 1,0 , nato uporabite Iskanje rešitve poiščite drugi koren, enak 2,0 .
Možnosti, ki urejajo delo Iskanje rešitvepodano v oknu Opcije (okno se prikaže, če kliknete gumb Opcije okno Iskanje rešitve), naslednji ():
- Najdaljši čas - omejuje čas, ki je določen za postopek iskanja rešitve (privzeta vrednost je 100 sekund, kar je dovolj za težave z približno 10 omejitvami, če je težava velike razsežnosti, je treba čas povečati).
- Omejeno število ponovitev - drug način za omejitev časa iskanja z nastavitvijo največjega števila ponovitev. Privzeta vrednost je 100 in najpogosteje, če rešitve ne dobimo v 100 ponovitvah, je s povečanjem njihovega števila (v polje lahko vnesete čas, ki ne presega 32767 sekund) verjetnost, da dobite rezultat, majhna. . Bolje poskusiti spremeniti začetni približek in znova začeti postopek iskanja.
- Relativna napaka - nastavi natančnost, s katero se celica ujema s ciljno vrednostjo ali približkom na določene meje (decimalni ulomek od 0 do 1).
- Toleranca - nastavljeno v% samo za težave s celoštevilskimi omejitvami. Iskanje rešitve pri takih težavah najprej poišče optimalno neceloštevilsko rešitev, nato pa poskuša najti najbližjo celoštevilčno točko, pri kateri bi se rešitev od optimalne razlikovala za največ toliko odstotkov, kot jo označuje ta parameter.
- Konvergenca - ko relativna sprememba vrednosti v ciljni celici za zadnjih pet ponovitev postane manjša od števila (delež od intervala od 0 do 1), navedenega v tem parametru, se iskanje ustavi.
- Linearni model - to potrditveno polje mora biti omogočeno, kadar sta ciljna funkcija in omejitve linearne funkcije. To pospeši postopek iskanja rešitve.
- Nenegativne vrednosti - s to zastavico lahko nastavite omejitve na spremenljivke, kar vam bo omogočilo iskanje rešitev v pozitivnem območju vrednosti, ne da bi določili posebne omejitve na njihovi spodnji meji.
- Samodejno skaliranje - to potrditveno polje je treba omogočiti, kadar se obseg vrednosti vhodnih spremenljivk in ciljna funkcija in omejitve razlikujejo, po možnosti za velikost. Spremenljivke so na primer postavljene v kosih, ciljna funkcija, ki določa največji dobiček, pa se meri v milijardah rubljev.
- Pokaži rezultate ponovitve - to potrditveno polje omogoča omogočanje postopnega iskanja, ki prikazuje rezultate vsake ponovitve na zaslonu.
- Ocene - ta skupina služi za označevanje metode ekstrapolacije - linearne ali kvadratne -, ki se uporablja za pridobivanje začetnih ocen vrednosti spremenljivk pri vsakem enodimenzionalnem iskanju. Linearno služi za uporabo linearne ekstrapolacije vzdolž tangente vektorja. Kvadratno služi za uporabo kvadratne ekstrapolacije, ki daje boljše rezultate pri reševanju nelinearnih problemov.
- Razlike (izpeljanke) - ta skupina služi za označevanje metode numeričnega razlikovanja, ki se uporablja za izračun delnih izpeljav objektivnih in omejevalnih funkcij. Parameter Neposredno uporablja se pri večini nalog, kjer je stopnja spremembe omejitev razmeroma nizka. Parameter Osrednji uporablja se za funkcije z diskontinuiranim izpeljanko. Ta metoda zahteva več izračunov, vendar je njeno uporabo mogoče upravičiti, če se prikaže sporočilo, da natančnejše rešitve ni mogoče dobiti.
- Način iskanja - služi za izbiro algoritma za optimizacijo. Newtonova metoda je bilo prej obravnavano. IN Metoda konjugiranega gradienta zahteva se manj pomnilnika, vendar se izvede več ponovitev kot Newtonova metoda. Ta metoda je treba uporabiti, če je težava dovolj velika in je treba prihraniti pomnilnik, pa tudi če ponovitve dajejo premalo razlike v zaporednih približkih.
- pri shranjevanju Excelovega delovnega zvezka po iskanju rešitve so bile vse vrednosti vnesene v pogovorna okna Iskanje rešitvese shranijo s podatki delovnega lista. V vsakem delovnem listu v delovnem zvezku lahko shranite en niz vrednosti parametrov Iskanje rešitve;
- če je znotraj enega Excelovega delovnega lista treba upoštevati več modelov za optimizacijo (na primer poiščite največjo in najmanjšo vrednost ene funkcije ali največje vrednosti več funkcij), potem je primerneje te modele shraniti z gumbom Možnosti / Shrani model okno Iskanje rešitve... Obseg shranjenega modela vsebuje informacije o ciljni celici, o celicah, ki jih je treba spremeniti, o vsaki omejitvi in \u200b\u200bvseh vrednostih pogovornega okna. Opcije... Izbira modela za reševanje določenega optimizacijskega problema se izvede s pomočjo gumba Parametri / model obremenitve dialoga Iskanje rešitve;
- drug način za shranjevanje iskalnih parametrov je, da jih shranite kot poimenovane skripte. Če želite to narediti, kliknite gumb Shrani skript pogovorno okno Rezultati iskanja rešitev.
Poleg vstavljanja optimalnih vrednosti v celice, ki se spreminjajo Iskanje rešitve omogoča predstavitev rezultatov v obliki treh poročil: rezultatov, Trajnost in Omejitve... Če želite ustvariti eno ali več poročil, v pogovornem oknu izberite njihova imena Rezultati iskanja rešitev... Oglejmo si jih natančneje.
 |
| Sl. 15. Trajnostno poročilo |
Poročilo o omejitvah vsebuje informacije o mejah, znotraj katerih je mogoče vrednosti spremenjenih celic povečati ali zmanjšati, ne da bi kršili omejitve naloge. Za vsako celico, ki jo spremenite, to poročilo vsebuje optimalno vrednost in najmanjše vrednosti, ki jih celica lahko sprejme, ne da bi kršila omejitve.