Korištenje logičke funkcije ako primjeri. Značajka Microsoft Excel: traženje rješenja
Rješavanje nelinearnih jednadžbi i sustava "
Cilj: Istraživanje mogućnosti gospođe Excel 2007 u rješavanju nelinearnih jednadžbi i sustava. Stjecanje vještina rješavanja nelinearnih jednadžbi i sustava pomoću paketa.
Vježba 1. Pronađite korijene polinoma x 3 - 0,01x 2 - 0,7044x + 0,139104 \u003d 0.
Prvo, riješimo jednadžbu grafički. Poznato je da je grafičko rješenje jednadžbe f (x) \u003d 0 točka presjeka grafa funkcije f (x) s apscisom, tj. takva vrijednost x pri kojoj funkcija nestaje.
Tabelirajmo naš polinom na intervalu od -1 do 1 s korakom od 0,2. Rezultati izračuna prikazani su na slici, gdje je formula unesena u ćeliju B2: \u003d A2 ^ 3 - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0,139104. Grafikon pokazuje da funkcija tri puta prelazi os Ox, a budući da polinom trećeg stupnja nema više od tri stvarna korijena, pronađeno je grafičko rješenje problema. Drugim riječima, korijeni su bili lokalizirani, t.j. određuju se intervali na kojima se nalaze korijeni ovog polinoma: [-1, -0,8] i.
Sada možete pronaći korijene polinoma uzastopnim aproksimacijama pomoću naredbe Podaci → Rad s podacima → Što ako analiza → Odabir parametra.
Nakon unosa početnih aproksimacija i vrijednosti funkcije, možete se obratiti naredbi Podaci → Rad s podacima → Što ako analiza → Odabir parametra i popunite dijalog na sljedeći način.
U polju Smješten u ćelijudaje se veza do ćelije u koju se unosi formula koja izračunava vrijednost lijeve strane jednadžbe (jednadžba mora biti napisana tako da njena desna strana ne sadrži varijablu). U polju Vrijednost ulazimo u desnu stranu jednadžbe i u polje Promjena vrijednosti ćelija dana je referenca na ćeliju dodijeljenu za varijablu. Imajte na umu da unos referenci na ćelije u polja dijaloškog okvira Izbor parametara prikladnije je ne s tipkovnice, već klikom na odgovarajuću ćeliju.
Nakon pritiska gumba OK, pojavit će se dijaloški okvir Rezultat odabira parametara s porukom o uspješnom završetku potrage za rješenjem, približna vrijednost korijena stavit će se u ćeliju A14.
Na isti način pronađite dva preostala korijena. Rezultati izračuna bit će smješteni u ćelije A15 i A16.
Zadatak 2. Riješi jednadžbu e x - (2x - 1) 2 = 0.
Lokalizirajmo korijene nelinearne jednadžbe.
Da bismo to učinili, predstavljamo ga u obliku f (x) \u003d g (x), tj. e x \u003d (2x - 1) 2 ili f (x) \u003d e x, g (x) \u003d (2x - 1) 2, i riješiti grafički.
Grafičko rješenje jednadžbe f (x) \u003d g (x) bit će točka presjeka linija f (x) i g (x).
Izgradimo grafikone f (x) i g (x). Da biste to učinili, vrijednosti argumenta unesite u raspon A3: A18. U ćeliju B3 unosimo formulu za izračunavanje vrijednosti funkcije f (x): \u003d EXP (A3), a u C3 za izračunavanje g (x): \u003d (2 * A3-1) ^ 2.
Rezultati proračuna i crtanje f (x) i g (x):
Grafikon pokazuje da se linije f (x) i g (x) presijecaju dva puta, tj. ova jednadžba ima dva rješenja. Jedan od njih je trivijalan i može se točno izračunati:
Za drugu se može odrediti interval izolacije korijena: 1.5< x < 2.
Sada možete pronaći korijen jednadžbe na segmentu metodom uzastopnih aproksimacija.
Unesite početnu aproksimaciju u ćeliju H17 \u003d 1,5, a samu jednadžbu, s obzirom na početnu aproksimaciju, u ćeliju I17 \u003d EXP (H17) - (2 * H17-1) ^ 2.
i ispunite dijaloški okvir Odabir parametara.
Rezultat pretraživanja rješenja prikazat će se u ćeliji H17.
Zadatak3 . Riješite sustav jednadžbi:
Prije korištenja gore opisanih metoda za rješavanje sustava jednadžbi, pronaći ćemo grafičko rješenje tog sustava. Imajte na umu da su obje jednadžbe sustava dane implicitno te je za konstruiranje grafova koji odgovaraju tim jednadžbama potrebno riješiti zadane jednadžbe s obzirom na varijablu y.
Za prvu jednadžbu sustava imamo:
Otkrijmo ODV rezultirajuće funkcije:
Druga jednadžba ovog sustava opisuje krug.
Fragment radnog lista MS Excel s formulama koje se moraju unijeti u ćelije za izgradnju linija opisanih jednadžbama sustava. Točke presjeka prikazanih linija grafičko su rješenje sustava nelinearnih jednadžbi.
Nije teško uočiti da zadani sustav ima dva rješenja. Stoga se postupak pronalaska rješenja za sustav mora izvesti dva puta, prethodno utvrdivši interval izolacije korijena duž osi Ox i Oy. U našem slučaju, prvi korijen leži u intervalima (-0,5; 0) x i (0,5; 1) y, a drugi je (0; 0,5) x i (-0,5; -1) y. Nastavit ćemo kako slijedi. Uvedimo početne vrijednosti varijabli x i y, formule koje predstavljaju jednadžbe sustava i ciljnu funkciju.
Sada ćemo upotrijebiti naredbu Podaci → Analiza → Potražiti rješenja dva puta, ispunjavajući dijaloške okvire koji se pojavljuju.
Uspoređujući dobiveno rješenje sustava s grafičkim, osiguravamo da je sustav ispravno riješen.
Zadaci za samopomoć
Vježba 1... Pronađite korijene polinoma
Zadatak 2... Pronađite rješenje nelinearne jednadžbe.
Zadatak 3... Pronađite rješenje za sustav nelinearnih jednadžbi.
Kao što već znate, formule u Microsoft Excel omogućuju vam utvrđivanje vrijednosti funkcije njezinim argumentima. Međutim, može doći do situacije kada je vrijednost funkcije poznata i treba pronaći argument (tj. Za rješavanje jednadžbe). Za rješavanje takvih problema postoji posebna funkcija Traženje cilja .
Pretraživanje parametara.
Posebna funkcija Traženje cilja omogućuje vam definiranje parametra (argumenta) funkcije ako je njena vrijednost poznata. Kada odaberete parametar, vrijednost utječuće ćelije (parametra) mijenja se sve dok formula koja ovisi o ovoj ćeliji ne vrati navedenu vrijednost.
Razmotrimo postupak pronalaska parametra na jednostavnom primjeru: riješite jednadžbu 10 * x - 10 / x \u003d 15
... Ovdje parametar (argument) - x
... Neka to bude ćelija A3
... Unesite u ovu ćeliju bilo koji broj koji se nalazi u opsegu definicije funkcije (u našem primjeru taj broj ne može biti jednak nuli). Ova vrijednost će se koristiti kao početna vrijednost. Neka bude 3
... Uvodimo formulu \u003d 10 * A3-10 / A3
, pomoću koje se treba dobiti potrebna vrijednost, u neku ćeliju, na primjer, B3
... Sada možete pokrenuti funkciju pretraživanja parametara odabirom naredbe Traženje cilja
na jelovniku Alati
... Unesite parametre pretraživanja:
- U polju Postavi ćeliju unesite referencu na ćeliju koja sadrži željenu formulu.
- U polje unesite rezultat pretraživanja Cijeniti .
- U polju Promjenom ćelije unesite referencu na ćeliju koja sadrži vrijednost koja se podudara.
- Pritisnite tipku u redu .
Na kraju funkcije na ekranu će se pojaviti prozor u kojem će biti prikazani rezultati pretraživanja. Pronađeni parametar pojavit će se u ćeliji koja je za njega rezervirana. Obratite pažnju na to da u našem primjeru jednadžba ima dva rješenja, a parametar je odabran samo jedan - to je zato što se parametar mijenja samo dok se ne vrati tražena vrijednost. Prvi pronađeni argument na ovaj način vraća nam se kao rezultat pretraživanja. Ako u našem primjeru odredimo kao početnu vrijednost -3
, tada će se naći drugo rješenje jednadžbe: -0,5
.
Prilično je teško pravilno odrediti najprikladniju početnu vrijednost. Češće možemo napraviti neke pretpostavke o željenom parametru, na primjer, parametar mora biti cijeli broj (tada dobivamo prvo rješenje naše jednadžbe) ili nepozitivan (drugo rješenje).
Zadatak pronalaska parametra s nametnutim rubnim uvjetima pomoći će posebni dodatak Microsoft Excel Rješavač .
Potražite rješenje.
Dodatak za Microsoft Excel Rješavač nije automatski instaliran u tipičnoj instalaciji:
- Na jelovniku Alati odaberi tim Programski dodaci ... Ako se dijaloški okvir Programski dodaci ne sadrži naredbu Rješavač , pritisni gumb pretraživati i navedite pogon i mapu koja sadrži datoteku dodatka Rješavač.xla (obično je ovo mapa Biblioteka \\ Rješivač ) ili pokrenite program microsoftove instalacije Office ako ne može pronaći datoteku.
- U dijaloškom okviru Programski dodaci potvrdite okvir Rješavač .
Postupak za pronalaženje rješenja omogućuje vam pronalaženje optimalne vrijednosti formule sadržane u ćeliji koja se naziva cilj. Ovaj postupak radi sa skupinom stanica povezanih s formulom u ciljnoj stanici. Postupak mijenja vrijednosti u utjecajnim stanicama sve dok ne postigne optimalni rezultat na temelju formule sadržane u ciljnoj stanici. Da bi se suzili skup vrijednosti, primjenjuju se ograničenja koja mogu imati veze do drugih stanica koje utječu. Također možete koristiti pretraživanje rješenja da biste odredili vrijednost stanice koja utječe na ekstrem ciljne stanice, na primjer, broj treninga koji maksimiziraju akademsku izvedbu.
U dijaloškom okviru Rješavač
isto kao u dijaloškom okviru Traženje cilja
, trebate odrediti ciljnu ćeliju, njezinu vrijednost i ćelije koje treba promijeniti da bi se postigao cilj. Da bi se riješili problemi s optimizacijom, ciljna ćelija treba biti postavljena jednakom maksimalnoj ili minimalnoj vrijednosti.
Ako kliknete na gumb Pretpostavljam Excel će pokušati pronaći sve stanice koje utječu na samu formulu.
Granične uvjete možete dodati klikom na tipku Dodati .
Klikom na gumb Opcije , možete promijeniti uvjete za pronalazak rješenja: maksimalno vrijeme za pronalazak rješenja, broj iteracija, točnost rješenja, tolerancija za odstupanja od optimalnog rješenja, metoda ekstrapolacije (linearna ili kvadratna), algoritam optimizacije itd.
Vratimo se prethodnom primjeru: da bismo dobili drugo (nepozitivno) rješenje, dovoljno je dodati granični uvjet A3 ... Kao i kod odabira parametra, na ekranu će se pojaviti prozor u kojem će biti prikazano izvješće o rezultatima pretraživanja za traženo rješenje. Sama otopina bit će prikazana u ćelijama koje su joj namijenjene (u ćeliji A3 prikazuje se vrijednost -0.50 ).
Dodatak za Microsoft Excel Rješavač
također vam omogućuje rješavanje sustava jednadžbi ili nejednakosti. Razmotrimo jednostavan primjer: pokušajmo riješiti sustav jednadžbi
x + y \u003d 2
x - y \u003d 0
Jedna od najzanimljivijih značajki programa Microsoft Excel je traženje rješenja. Istodobno, valja napomenuti da se ovaj alat ne može klasificirati kao najpopularniji među korisnicima u sustavu Windows ovaj dodatak... Ali uzalud. Napokon, ova funkcija, koristeći početne podatke, pretraživanjem pronalazi najoptimalnije rješenje od svih dostupnih. Otkrijmo kako koristiti značajku Pronađi rješenje u programu Microsoft Excel.
Možete tražiti dugo na traci na kojoj se nalazi Potraga za rješenjem, ali još uvijek ne možete pronaći ovaj alat. Jednostavno, da biste aktivirali ovu funkciju, morate je omogućiti u postavkama programa.
Da biste aktivirali pretraživanje rješenja u programu Microsoft Excel 2010 i novijim verzijama, idite na karticu "Datoteka". Za verziju 2007. kliknite gumb Microsoft Office u gornjem lijevom kutu prozora. U prozoru koji se otvori idite na odjeljak "Parametri".
U prozoru parametara kliknite stavku "Dodaci". Nakon prijelaza, u donjem dijelu prozora, nasuprot parametru "Control", odaberite vrijednost "Excel Add-ins" i kliknite gumb "Go".
Otvara se prozor s dodacima. Stavili smo kvačicu ispred naziva programskog dodatka koji nam treba - "Potražite rješenje". Kliknite gumb "U redu".
Nakon toga, gumb za pokretanje funkcije Traženje rješenja pojavit će se na Excel vrpci na kartici Podaci.
Priprema tablice
Sad kad smo aktivirali funkciju, pogledajmo kako ona funkcionira. To ćete najlakše zamisliti na konkretnom primjeru. Dakle, imamo tablicu plaća zaposlenih u poduzeću. Moramo izračunati bonus za svakog zaposlenika, što je umnožak plaće naznačene u posebnom stupcu određenim koeficijentom. Istodobno, ukupan iznos sredstava dodijeljenih za premiju iznosi 30.000 rubalja. Ćelija u kojoj se nalazi taj iznos ima ime ciljane, jer je naš cilj odabrati podatke točno za taj broj.
Koeficijent koji se koristi za izračunavanje iznosa premije, moramo izračunati pomoću funkcije Traženje rješenja. Stanica u kojoj se nalazi naziva se željenom.
Ciljna stanica i ciljana stanica moraju biti povezane jedna s drugom pomoću formule. U našem konkretnom slučaju, formula se nalazi u ciljnoj ćeliji i ima sljedeći oblik: "\u003d C10 * $ G $ 3", gdje je $ G $ 3 apsolutna adresa tražene ćelije, a "C10" je ukupna plaća od koje se izračunava bonus zaposlenici u poduzeću.
Pokretanje alata Pronađi rješenje
Nakon što se tablica pripremi, na kartici "Podaci" kliknite gumb "Traži rješenje" koji se nalazi na vrpci u alatnom okviru "Analiza".
Otvara se prozor parametara u koji trebate unijeti podatke. U polje "Optimiziraj ciljnu funkciju" trebate unijeti adresu ciljne ćelije, u kojoj će se nalaziti ukupan iznos bonusa za sve zaposlenike. To se može učiniti ručnim upisivanjem koordinata ili klikom na gumb smješten s lijeve strane polja za unos podataka.
Nakon toga prozor s parametrima bit će minimiziran i možete odabrati željenu ćeliju tablice. Zatim trebate ponovo kliknuti na isti gumb lijevo od obrasca s unesenim podacima da biste ponovo proširili prozor parametara.
Ispod prozora s adresom ciljne ćelije trebate postaviti parametre vrijednosti koje će se nalaziti u njemu. To može biti maksimalna, minimalna ili određena vrijednost. U našem slučaju ovo će biti zadnja opcija. Stoga smo prekidač stavili u položaj "Vrijednosti", a u polje lijevo od njega upisujemo broj 30000. Kao što se sjećamo, ovaj je broj, prema uvjetima, ukupan iznos bonusa za sve zaposlenike poduzeća.
Ispod je polje "Promjena promjenjivih ćelija". Ovdje trebate navesti adresu željene ćelije, u kojoj se, kao što se sjećamo, nalazi koeficijent, množenjem osnovne plaće pomoću koje će se izračunati iznos bonusa. Adresa se može napisati na isti način kao što smo to učinili za ciljnu ćeliju.
U polju "Prema ograničenjima" možete postaviti određena ograničenja za podatke, na primjer, vrijednosti učiniti cjelovitim ili nenegativnim. Da biste to učinili, kliknite gumb "Dodaj".
Nakon toga otvara se prozor za dodavanje ograničenja. U polje "Veza do stanica" napišite adresu ćelija s obzirom na koje je uvedeno ograničenje. U našem slučaju ovo je željena stanica s koeficijentom. Zatim stavljamo traženi znak: „manje ili jednako“, „veće ili jednako“, „jednako“, „cijeli broj“, „binarno“ itd. U našem ćemo slučaju odabrati znak veći ili jednak da bi koeficijent bio pozitivan broj. Sukladno tome, u polje "Ograničenje" navedite broj 0. Ako želimo konfigurirati drugo ograničenje, kliknite gumb "Dodaj". U suprotnom kliknite gumb "U redu" da biste spremili unesena ograničenja.
Kao što vidite, nakon toga se ograničenje pojavljuje u odgovarajućem polju prozora parametara pretraživanja rješenja. Također, varijable možete učiniti negativnima potvrđivanjem okvira pored odgovarajućeg parametra odmah ispod. Preporučljivo je da ovdje postavljeni parametar ne proturječi onima koje ste propisali u ograničenjima, inače može doći do sukoba.
Dodatne postavke mogu se postaviti klikom na gumb "Parametri".
Ovdje možete postaviti preciznost ograničenja i ograničenja rješenja. Kada se unesu potrebni podaci, kliknite gumb "U redu". Ali, za naš slučaj, nije potrebno mijenjati ove parametre.
Nakon postavljanja svih postavki, kliknite gumb "Pronađi rješenje".
Nadalje, program Excel u ćelijama izvodi potrebne proračune. Istodobno s izlazom rezultata, otvara se prozor u kojem možete ili spremiti pronađeno rješenje ili vratiti izvorne vrijednosti pomicanjem prekidača u odgovarajući položaj. Bez obzira na odabranu opciju, potvrđivanjem okvira "Povratak na dijaloški okvir opcija", možete ponovo prijeći na postavke rješenja za pretraživanje. Nakon postavljanja potvrdnih okvira i prekidača, kliknite gumb "U redu".
Ako vas iz bilo kojeg razloga rezultati pretraživanja rješenja ne zadovoljavaju ili program pri njihovom izračunavanju dovede do pogreške, tada ćemo se, kako je gore opisano, vratiti u dijaloški okvir parametara. Pregledavamo sve unesene podatke, jer je možda negdje došlo do pogreške. Ako pogreška nije pronađena, idite na parametar "Odabir metode rješenja". Ovdje možete odabrati jednu od tri metode izračuna: "Traženje rješenja za nelinearne probleme OPG metodom", "Traženje rješenja za linearne probleme jednostavnom metodom" i "Evolucijsko traženje rješenja". Prema zadanim postavkama koristi se prva metoda. Pokušavamo riješiti problem odabirom bilo koje druge metode. Ako neuspješno, pokušajte ponovno pomoću posljednje metode. Algoritam radnji je isti kao što je gore opisano.
Kao što vidite, funkcija Pronađi rješenje prilično je zanimljiv alat koji, ako se pravilno koristi, može značajno uštedjeti vrijeme korisnika na raznim izračunima. Nažalost, nije svaki korisnik svjestan njegovog postojanja, a kamoli da može ispravno raditi s ovim dodatkom. Ovaj alat na neki način podsjeća na funkciju , ali istodobno ima značajne razlike s njim.
Ako je u excel ćelija uvodi se formula koja sadrži vezu na istu ćeliju (možda ne izravno, ali neizravno - kroz lanac drugih veza), tada kažu da postoji ciklična referenca (ciklus). U praksi se koriste cikličke reference kada je riječ o provedbi iterativnog postupka, izračunavanjem relacijama ponavljanja. U uobičajenom excel način rada detektira ciklus i izda poruku o situaciji koja se pojavila zahtijevajući njegovo uklanjanje. Excel ne može izvoditi izračune jer kružne reference generiraju beskonačan broj izračuna. Postoje dva načina izlaska iz ove situacije: eliminirati kružne reference ili dopustiti izračune pomoću formula s kružnim referencama (u potonjem slučaju, broj ponavljanja ciklusa mora biti konačan).
Razmotrite problem pronalaska korijena jednadžbe pomoću Newtonove metode pomoću cikličkih referenci. Uzmimo za primjer kvadratnu jednadžbu: x 2 - 5x + 6 \u003d 0, čiji je grafički prikaz prikazan u. Korijen ove (i bilo koje druge) jednadžbe možete pronaći koristeći samo jednu Excel ćeliju.
Da bi se omogućilo ciklično računanje u izbornik Alati / kartica Opcije / Izračuni uključite potvrdni okvir Iteracija, ako je potrebno, promijenite broj ponavljanja petlje u polju Ograničeni broj ponavljanja i točnost izračuna na terenu Relativna pogreška (zadane vrijednosti su im 100, odnosno 0,0001). Uz ove postavke odabiremo mogućnost izračuna: automatski ili ručno... Kada automatski izračun Excel odmah daje konačni rezultat, prilikom izračuna ručno, možete promatrati rezultat svake iteracije.
| Lik: 8. Graf funkcije |
Označimo proizvoljnu ćeliju, dajmo joj novo ime, recimo - x, i u nju uvesti ponavljajuću formulu koja određuje izračune Newtonovom metodom:
,
gdje F i F1 postaviti, odnosno izraze za izračunavanje vrijednosti funkcije i njenog izvoda. Za našu kvadratnu jednadžbu, nakon unosa formule, vrijednost će se pojaviti u ćeliji 2
što odgovara jednom od korijena jednadžbe (). U našem slučaju, početna aproksimacija nije navedena, iterativni proces izračuna započeo je sa zadanom vrijednošću pohranjenom u ćeliji x a jednaka nuli. Kako se dolazi do drugog korijena? To se obično može učiniti promjenom početne pretpostavke. Problem određivanja početnih postavki u svakom slučaju možete riješiti na različite načine. Pokazat ćemo jednu tehniku \u200b\u200bkoja se temelji na uporabi funkcije IF. Kako bi se povećala jasnoća izračuna, ćelijama su dodijeljena suvisla imena (). 
2.2. Odabir parametara
Kad znate željeni rezultat formule, ali ne znate vrijednosti potrebne za dobivanje ovog rezultata, možete koristiti alat Odabir parametaraodabirom naredbe Odabir parametara na jelovniku Servis... Kada odaberete opciju, Excel mijenja vrijednost u određenoj ćeliji sve dok formula koja se odnosi na tu ćeliju ne proizvede željeni rezultat.
Uzmimo za primjer istu kvadratnu jednadžbu x 2 -5x + 6 \u003d 0... Da biste pronašli korijene jednadžbe, učinite sljedeće:
Excel koristi iterativni (okrugli postupak) odabir parametra. Broj ponavljanja i preciznost postavljaju se u izborniku Kartica Alati / Opcije / Izračuni... Ako Excel izvodi složeni zadatak odabira parametara, možete kliknuti Pauza u dijaloškom okviru Rezultat odabira parametara i prekinite izračun, a zatim pritisnite gumb Korakponovno ponoviti i vidjeti rezultat. Pri rješavanju problema u postupnom načinu pojavljuje se gumb Nastavite - za povratak u normalni način odabira parametara.
Vratimo se primjeru. Opet se postavlja pitanje: kako doći do drugog korijena? Kao i u prethodnom slučaju, potrebno je postaviti početnu aproksimaciju. To se može učiniti ovako ():
 |
| i |
 |
| b |
| Lik: 11. Pronalaženje drugog korijena |
Međutim, sve se to može učiniti na nešto jednostavniji način. Da bi se pronašao drugi korijen, dovoljno je smjestiti konstantu u ćeliju C2 kao početnu aproksimaciju () 5 a nakon toga započeti postupak Odabir parametara.
2.3. Traženje rješenja
Tim Odabir parametara je prikladan za rješavanje problema pronalaženja određene ciljne vrijednosti, ovisno o jednom nepoznatom parametru. Za složenije zadatke upotrijebite naredbu Traženje rješenja (Rješavač), kojem se pristupa putem stavke izbornika Pretraživanje usluga / rješenja.
Zadaci s kojima se može riješiti Pronađite rješenje, u općem okruženju formulirani su kako slijedi:
Pronaći:
x 1, x 2, ..., x n
takav da:
F (x 1, x 2, ..., x n)\u003e (Max; Min; \u003d vrijednost)
s ograničenjima:
G (x 1, x 2, ..., x n)\u003e (Ј Vrijednost; i Vrijednost; \u003d Vrijednost)
Tražene varijable - radničke stanice excel radni list - nazivaju se reguliranim stanicama. Ciljna funkcija F (x 1, x 2, ..., x n)koji se ponekad naziva jednostavno ciljem, mora se navesti kao formula u ćeliji na radnom listu. Ova formula može sadržavati korisnički definirane funkcije i mora ovisiti o (referenci) podesivim stanicama. U trenutku postavljanja problema utvrđuje se što učiniti s ciljnom funkcijom. Možete odabrati jednu od opcija:
- pronaći maksimum ciljane funkcije F (x 1, x 2, ..., x n);
- pronaći minimum objektivne funkcije F (x 1, x 2, ..., x n);
- postići da je ciljna funkcija F (x 1, x 2, ..., x n) imao fiksnu vrijednost: F (x 1, x 2, ..., x n) \u003d a.
Funkcije G (x 1, x 2, ..., x n) nazivaju se ograničenjima. Mogu se odrediti u obliku jednakosti i nejednakosti. Dodatna ograničenja mogu se nametnuti reguliranim stanicama: nenegativnost i / ili cijeli broj, a zatim se traži rješenje u rasponu pozitivnih i / ili cijelih brojeva.
Ova formulacija pokriva najširi spektar optimizacijskih problema, uključujući rješenje različitih jednadžbi i sustava jednadžbi, linearnih i nelinearnih problema programiranja. Takve je probleme obično lakše formulirati nego riješiti. A onda je za rješavanje određenog problema optimizacije potrebna posebno dizajnirana metoda. Rješavač u svom arsenalu ima moćne alate za rješavanje takvih problema: generaliziranu gradijentnu metodu, simplex metodu, gransku i vezanu metodu.
Iznad, da bi se pronašli korijeni kvadratne jednadžbe, primijenjena je Newtonova metoda (odjeljak 1.4.) Pomoću cikličkih referenci () i alata Odabir parametara (). Pogledajmo kako koristiti Traženje rješenja koristeći primjer iste kvadratne jednadžbe.
Nakon otvaranja dijaloga Traženje rješenja () trebate učiniti sljedeće:- u polju Postavi ciljnu ćeliju unesite adresu ćelije koja sadrži formulu za izračunavanje vrijednosti optimizirane funkcije, u našem primjeru ciljana ćelija je C4, a formula u njoj je: \u003d C3 ^ 2 - 5 * C3 + 6;
- da biste maksimizirali vrijednost ciljne ćelije, postavite radio gumb maksimalna vrijednost u položaj 8, prekidač se koristi za minimiziranje minimalna vrijednost, u našem slučaju, postavite prekidač na položaj vrijednosti i unesite vrijednost 0 ;
- u polju Mijenjanje stanica unesite adrese ćelija koje će se mijenjati, tj. argumenti objektivne funkcije (C3), razdvajajući ih znakom ";" (ili klikom miša dok je tipka pritisnuta Strl na odgovarajućim stanicama), za automatsko traženje svih stanica koje utječu na rješenje upotrijebite gumb Pretpostavljam;
- u polju Ograničenja pomoću gumba Dodaj u unesite sva ograničenja koja mora zadovoljiti rezultat pretraživanja: za naš primjer ne morate postaviti ograničenja;
- da biste započeli postupak pronalaska rješenja, pritisnite gumb Izvršiti.
Za spremanje dobivenog rješenja upotrijebite prekidač Spremi pronađeno rješenje u otvorenom dijaloškom prozoru Rezultati pretraživanja rješenja... Nakon toga radni list poprimit će oblik prikazan u. Dobiveno rješenje ovisi o izboru početne aproksimacije, koja je navedena u ćeliji C4 (argument funkcije). Ako kao početnu aproksimaciju u ćeliju C4 unesete vrijednost jednaku 1,0 , zatim pomoću Pronađite rješenje naći drugi korijen jednak 2,0 .
Opcije koje reguliraju rad Pronađite rješenjepostavljen u prozoru Parametri (prozor će se pojaviti ako kliknete na gumb Parametri prozor Traženje rješenja), sljedeće ():
- Maksimalno vrijeme - ograničava vrijeme predviđeno za postupak pronalaska rješenja (zadana vrijednost je 100 sekundi, što je dovoljno za probleme s 10-ak ograničenja, ako je problem velike dimenzije, vrijeme se mora povećati).
- Ograničeni broj ponavljanja - još jedan način da ograničite vrijeme pretraživanja postavljanjem maksimalnog broja ponavljanja. Zadana vrijednost je 100 i, najčešće, ako se rješenje ne dobije u 100 ponavljanja, tada će s povećanjem njihovog broja (u polje možete unijeti vrijeme koje ne prelazi 32767 sekundi) vjerojatnost dobivanja rezultata mala. Bolje pokušati promijeniti početnu aproksimaciju i ponovno započeti postupak pretraživanja.
- Relativna pogreška - specificira preciznost kojom ćelija odgovara ciljanoj vrijednosti ili aproksimaciju na navedena ograničenja (decimalni razlomak od 0 do 1).
- Tolerancija - postavljeno u% samo za probleme s cjelobrojnim ograničenjima. Traženje rješenja u takvim problemima prvo pronalazi optimalno necjelobrojno rješenje, a zatim pokušava pronaći najbližu cijelu točku, rješenje u kojem bi se od optimalne razlikovalo ne više od broja postotaka naznačenih ovim parametrom.
- Konvergencija - kada relativna promjena vrijednosti u ciljnoj ćeliji tijekom zadnjih pet iteracija postane manja od broja (razlomka od raspona od 0 do 1) navedenog u ovom parametru, pretraživanje se zaustavlja.
- Linearni model - ovaj potvrdni okvir trebao bi biti omogućen kada su funkcija cilja i ograničenja linearne funkcije. To ubrzava proces pronalaženja rješenja.
- Nenegativne vrijednosti - ova se zastava može koristiti za postavljanje ograničenja na varijable, što će omogućiti traženje rješenja u pozitivnom rasponu vrijednosti bez postavljanja posebnih ograničenja na njihovoj donjoj granici.
- Automatsko skaliranje - ovu zastavicu treba omogućiti kada se razmjere vrijednosti ulaznih varijabli i ciljne funkcije i ograničenja razlikuju, moguće po redosljedu veličina. Na primjer, varijable se postavljaju u komadima, a ciljna funkcija koja određuje maksimalni profit mjeri se u milijardama rubalja.
- Prikaži rezultate iteracije - ovaj potvrdni okvir omogućuje vam omogućavanje postupnog postupka pretraživanja, prikazujući rezultate svake iteracije na zaslonu.
- Procjene - ova skupina služi za označavanje metode ekstrapolacije - linearne ili kvadratne - koja se koristi za dobivanje početnih procjena vrijednosti varijabli u svakom jednodimenzionalnom pretraživanju. Linearno služi za upotrebu linearne ekstrapolacije duž vektora tangente. Kvadratno služi za upotrebu kvadratne ekstrapolacije, koja daje bolje rezultate pri rješavanju nelinearnih problema.
- Razlike (izvedenice) - ova skupina služi za označavanje metode numeričke diferencijacije koja se koristi za izračunavanje djelomičnih izvedenica ciljanih i ograničavajućih funkcija. Parametar Direktno koristi se u većini zadataka gdje je stopa promjene ograničenja relativno niska. Parametar Središnji koristi se za funkcije s diskontinuiranim derivatom. Ova metoda zahtijeva više izračuna, ali njezina se primjena može opravdati ako se prikaže poruka da nije moguće dobiti točnije rješenje.
- Način pretraživanja - služi za odabir algoritma za optimizaciju. Newtonova metoda raspravljalo se ranije. U Metoda konjugiranog gradijenta traži se manje memorije, ali izvodi se više iteracija nego Newtonova metoda. Ova metoda treba koristiti ako je zadatak dovoljno velik i ako je potrebno uštedjeti memoriju, a također ako iteracije daju premalu razliku u uzastopnim aproksimacijama.
- prilikom spremanja Excelove radne knjige nakon pronalaska rješenja, sve vrijednosti unose se u dijaloške okvire Traženje rješenjaspremaju se zajedno s podacima radnog lista. Jedan skup vrijednosti parametara može se spremiti sa svakim radnim listom u radnoj knjizi Pronađite rješenje;
- ako je unutar jednog Excel radnog lista potrebno razmotriti nekoliko modela optimizacije (na primjer, pronađite maksimum i minimum jedne funkcije ili maksimalne vrijednosti nekoliko funkcija), tada je prikladnije te modele spremiti pomoću gumba Opcije / Spremi model prozor Traženje rješenja... Raspon za spremljeni model sadrži informacije o ciljnoj ćeliji, o promijenjenim stanicama, o svakom ograničenju i svim vrijednostima dijaloškog okvira Parametri... Izbor modela za rješavanje određenog problema optimizacije vrši se pomoću gumba Parametri / Model opterećenja dijalog Traženje rješenja;
- drugi način za spremanje parametara pretraživanja je spremanje kao imenovane skripte. Da biste to učinili, kliknite gumb Spremi skriptu dijaloški okvir Rezultati pretraživanja rješenja.
Pored umetanja optimalnih vrijednosti u stanice koje se mijenjaju Traženje rješenja omogućuje vam da rezultate predstavite u tri izvješća: rezultatima, Održivost i Granice... Da biste generirali jedno ili nekoliko izvješća, u dijaloškom prozoru morate odabrati njihova imena Rezultati pretraživanja rješenja... Razmotrimo detaljnije svaku od njih.
 |
| Lik: 15. Izvještaj o održivosti |
Izvješće Ograničenja sadrži informacije o granicama unutar kojih se vrijednosti modificiranih stanica mogu povećati ili smanjiti bez kršenja ograničenja zadatka. Za svaku ćeliju koju promijenite, ovo izvješće sadrži optimalnu vrijednost kao i najmanje vrijednosti koje ćelija može prihvatiti bez kršenja ograničenja.