Použitie boolovskej funkcie, ak sú uvedené príklady. Funkcia Microsoft Excel: Hľadanie riešenia
Riešenie nelineárnych rovníc a systémov "
Cieľ: Skúmanie schopností programu MS Excel 2007 pri riešení nelineárnych rovníc a systémov. Získanie zručností pri riešení nelineárnych rovníc a systémov pomocou balíka.
Cvičenie 1. Nájdite korene polynómu x 3 - 0,01x 2 - 0,7044x + 0,139104 \u003d 0.
Najskôr poďme graficky vyriešiť rovnicu. Je známe, že grafické riešenie rovnice f (x) \u003d 0 je priesečníkom grafu funkcie f (x) s úsečkou, t.j. taká hodnota x, pri ktorej funkcia zmizne.
Urobme tabuľku nášho polynómu v intervale od -1 do 1 s krokom 0,2. Výsledky výpočtu sú zobrazené na obr., Kde bol vzorec zadaný do bunky B2: \u003d A2 ^ 3 - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0,139104. Graf ukazuje, že funkcia pretína os Ox trikrát, a keďže polynóm tretieho stupňa nemá viac ako tri skutočné korene, bolo nájdené grafické riešenie problému. Inými slovami, korene boli lokalizované, t.j. určujú sa intervaly, v ktorých sa nachádzajú korene tohto polynómu: [-1, -0,8] a.
Teraz môžete nájsť korene polynómu postupnými aproximáciami pomocou príkazu Dáta → Práca s dátami → Analýza Čo-Ak → Výber parametrov.
Po zadaní počiatočných aproximácií a hodnôt funkcie sa môžete obrátiť na príkaz Dáta → Práca s dátami → Analýza Čo-Ak → Výber parametrov a vyplňte dialógové okno nasledovne.
V teréne Nachádza sa v bunkeodkazuje sa na bunku, do ktorej sa zadáva vzorec, ktorý počíta hodnotu ľavej strany rovnice (rovnica musí byť napísaná tak, aby jej pravá strana neobsahovala premennú). V teréne Hodnota zadáme pravú stranu rovnice a do poľa Zmena hodnôt buniek je uvedený odkaz na bunku pridelenú pre premennú. Upozorňujeme, že do polí dialógového okna musíte zadávať odkazy na bunky Výber parametrov je to pohodlnejšie nie z klávesnice, ale kliknutím na príslušnú bunku.
Po stlačení tlačidla OK sa zobrazí dialógové okno Výsledok výberu parametra so správou o úspešnom dokončení hľadania riešenia, približná hodnota koreňa sa umiestni do bunky A14.
Rovnakým spôsobom nájdite dva zostávajúce korene. Výsledky výpočtu sa umiestnia do buniek A15 a A16.
Úloha 2. Vyriešte rovnicu e x - (2x - 1) 2 = 0.
Lokalizujme korene nelineárnej rovnice.
Aby sme to dosiahli, reprezentujeme to v tvare f (x) \u003d g (x), t.j. e x \u003d (2x - 1) 2 alebo f (x) \u003d e x, g (x) \u003d (2x - 1) 2 a riešte graficky.
Grafické riešenie rovnice f (x) \u003d g (x) bude priesečníkom priamok f (x) a g (x).
Vytvorme grafy f (x) ag (x). Za týmto účelom zadajte hodnoty argumentu do rozsahu A3: A18. V bunke B3 zadáme vzorec na výpočet hodnôt funkcie f (x): \u003d EXP (A3) a do C3 na výpočet g (x): \u003d (2 * A3-1) ^ 2.
Výsledky výpočtu a vynesenie f (x) ag (x):
Graf ukazuje, že priamky f (x) a g (x) sa pretínajú dvakrát, t. táto rovnica má dve riešenia. Jeden z nich je triviálny a dá sa presne vypočítať:
Pre druhú možno určiť interval izolácie koreňov: 1.5< x < 2.
Teraz môžete nájsť koreň rovnice na segmente metódou postupných aproximácií.
Zadajme počiatočnú aproximáciu do bunky H17 \u003d 1,5 a samotnú rovnicu s odkazom na počiatočnú aproximáciu do bunky I17 \u003d EXP (H17) - (2 * H17-1) ^ 2.
a vyplňte dialógové okno Výber parametrov.
Výsledok hľadania riešenia sa zobrazí v bunke H17.
Úloha3 . Vyriešte sústavu rovníc:
Pred použitím vyššie popísaných metód riešenia systémov rovníc nájdeme grafické riešenie tohto systému. Všimnite si, že obe rovnice systému sú dané implicitne a na zostrojenie grafov zodpovedajúcich týmto rovniciam je potrebné dané rovnice vyriešiť vzhľadom na premennú y.
Pre prvú rovnicu systému máme:
Poďme zistiť ODV výslednej funkcie:
Druhá rovnica tohto systému popisuje kružnicu.
Fragment pracovného hárka programu MS Excel so vzorcami, ktoré je potrebné zadať do buniek, aby sa vytvorili čiary popísané rovnicami systému. Priesečníky zobrazených úsečiek sú grafickým riešením systému nelineárnych rovníc.
Je ľahké vidieť, že daný systém má dve riešenia. Preto sa postup hľadania riešení systému musí vykonať dvakrát, keď sa predtým určil interval izolácie koreňov pozdĺž osí Ox a Oy. V našom prípade prvý koreň leží v intervaloch (-0,5; 0) x a (0,5; 1) y a druhý je (0; 0,5) x a (-0,5; -1) y. Budeme postupovať nasledovne. Predstavme si počiatočné hodnoty premenných x a y, vzorce predstavujúce rovnice systému a cieľovú funkciu.
Teraz dvakrát použijeme príkaz Údaje → Analýza → Hľadať riešenia a vyplníme dialógové okná, ktoré sa zobrazia.
Porovnaním získaného riešenia systému s grafickým sa uistíme, že je systém vyriešený správne.
Svojpomocné úlohy
Cvičenie 1... Nájdite korene polynómu
Zadanie úlohy 2... Nájdite riešenie nelineárnej rovnice.
Zadanie úlohy 3... Nájdite riešenie pre systém nelineárnych rovníc.
Ako už viete, vzorce v Microsoft Excel vám umožní určiť hodnotu funkcie pomocou jej argumentov. Môže však nastať situácia, keď je známa hodnota funkcie, a treba nájsť argument (t. J. Vyriešiť rovnicu). Na riešenie takýchto problémov existuje špeciálna funkcia Hľadanie gólu .
Vyhľadávanie parametrov.
Špeciálna funkcia Hľadanie gólu umožňuje definovať parameter (argument) funkcie, ak je jej hodnota známa. Keď vyberiete parameter, hodnota ovplyvňujúcej bunky (parameter) sa zmení, kým vzorec závislý od tejto bunky nevráti zadanú hodnotu.
Uvažujme o postupe hľadania parametra pomocou jednoduchého príkladu: vyriešime rovnicu 10 * x - 10 / x \u003d 15
... Tu parameter (argument) - x
... Nech je to bunka A3
... Zadajme do tejto bunky akékoľvek číslo, ktoré leží v rozsahu definície funkcie (v našom príklade sa toto číslo nemôže rovnať nule). Táto hodnota sa použije ako počiatočná hodnota. Nechaj to tak 3
... Zavádzame vzorec \u003d 10 * A3-10 / A3
, pomocou ktorého by sa mala získať požadovaná hodnota, do nejakej bunky, napríklad B3
... Teraz môžete spustiť funkciu hľadania parametrov výberom príkazu Hľadanie gólu
v ponuke Náradie
... Zadajte parametre vyhľadávania:
- V teréne Nastaviť bunku zadajte odkaz na bunku obsahujúcu požadovaný vzorec.
- Do tohto poľa zadajte výsledok vyhľadávania Ohodnotiť .
- V teréne Zmenou bunky zadajte odkaz na bunku obsahujúcu hodnotu, ktorá sa má zhodovať.
- Kliknite na kľúč Ok .
Na konci funkcie sa na obrazovke zobrazí okno, v ktorom sa zobrazia výsledky vyhľadávania. Nájdený parameter sa objaví v bunke, ktorá bola pre neho vyhradená. Venujte pozornosť skutočnosti, že v našom príklade má rovnica dve riešenia a parameter sa zhoduje iba s jedným - je to tak preto, lebo parameter sa mení iba dovtedy, kým sa nevráti požadovaná hodnota. Prvý takto nájdený argument sa nám vráti ako výsledok vyhľadávania. Ak v našom príklade zadáme ako počiatočnú hodnotu -3
, potom sa nájde druhé riešenie rovnice: -0,5
.
Je pomerne ťažké správne určiť najvhodnejšiu východiskovú hodnotu. Častejšie môžeme o predpokladanom parametri urobiť nejaké predpoklady, napríklad parameter musí byť celočíselný (potom dostaneme prvé riešenie našej rovnice) alebo pozitívny (druhé riešenie).
Úlohe nájsť parameter s vynútenými okrajovými podmienkami pomôže špeciálny doplnok Microsoft Excel Riešiteľ .
Hľadajte riešenie.
Doplnok Microsoft Excel Riešiteľ nie je automaticky nainštalovaný v typickej inštalácii:
- V ponuke Náradie vyberte tím Doplnky ... Ak dialógové okno Doplnky neobsahuje príkaz Riešiteľ , stlač tlačidlo Prechádzať a zadajte jednotku a priečinok, ktorý obsahuje súbor doplnku Riešiteľ.xla (obvykle ide o priečinok Knižnica \\ Riešiteľ ) alebo spustite program inštalácie spoločnosti Microsoft Office, ak nemôže nájsť súbor.
- V dialógovom okne Doplnky začiarknite políčko Riešiteľ .
Postup hľadania riešenia vám umožňuje nájsť optimálnu hodnotu vzorca obsiahnutého v bunke zvanej cieľ. Tento postup pracuje so skupinou buniek spojených so vzorcom v cieľovej bunke. Postup mení hodnoty v ovplyvňujúcich bunkách, kým nezíska optimálny výsledok na základe vzorca obsiahnutého v cieľovej bunke. Na zúženie množiny hodnôt sa uplatnia obmedzenia, ktoré môžu mať väzby na ďalšie ovplyvňujúce bunky. Môžete tiež použiť hľadanie riešenia na určenie hodnoty ovplyvňujúcej bunky, ktorá zodpovedá extrému cieľovej bunky, napríklad počet tréningov, ktoré maximalizujú akademický výkon.
V dialógovom okne Riešiteľ
rovnaké ako v dialógovom okne Hľadanie gólu
, musíte určiť cieľovú bunku, jej hodnotu a bunky, ktoré by sa mali zmeniť, aby sa dosiahol cieľ. Na vyriešenie problémov s optimalizáciou by mala byť cieľová bunka zadaná ako rovná maximálnej alebo minimálnej hodnote.
Ak kliknete na tlačidlo Hádajte Excel sa pokúsi nájsť všetky bunky, ktoré ovplyvňujú samotný vzorec.
Okrajové podmienky môžete pridať kliknutím na kľúč Pridať .
Kliknutím na tlačidlo možnosti , môžete zmeniť podmienky hľadania riešenia: maximálny čas na nájdenie riešenia, počet iterácií, presnosť riešenia, tolerancia odchýlok od optimálneho riešenia, metóda extrapolácie (lineárna alebo kvadratická), optimalizácia algoritmus atď.
Vráťme sa k predchádzajúcemu príkladu: na získanie druhého (pozitívneho) riešenia stačí pridať okrajovú podmienku A3 ... Rovnako ako pri výbere parametra sa na obrazovke zobrazí okno, v ktorom sa zobrazí správa o výsledkoch vyhľadávania požadovaného riešenia. Samotné riešenie sa zobrazí v bunkách na to určených (v bunke A3 hodnota sa zobrazí -0.50 ).
Doplnok Microsoft Excel Riešiteľ
tiež vám umožňuje riešiť sústavy rovníc alebo nerovností. Uvažujme o jednoduchom príklade: skúsme vyriešiť sústavu rovníc
x + y \u003d 2
x - y \u003d 0
Jedna z najzaujímavejších funkcií v systéme Windows program Microsoft Excel nachádza riešenie. Zároveň je potrebné poznamenať, že tento nástroj nemožno klasifikovať ako najpopulárnejší medzi používateľmi v systéme Windows tejto prílohy... Ale márne. Nakoniec, táto funkcia pomocou počiatočných údajov vyhľadá najoptimálnejšie riešenie zo všetkých dostupných. Poďme zistiť, ako používať funkciu Nájsť riešenie v programe Microsoft Excel.
Môžete dlho hľadať na páske, kde sa nachádza hľadanie riešenia, ale stále nemôžete nájsť tento nástroj. Ak chcete túto funkciu aktivovať, musíte ju jednoducho povoliť v nastaveniach programu.
Ak chcete aktivovať vyhľadávanie riešení v programe Microsoft Excel 2010 a novších verziách, prejdite na kartu „Súbor“. Vo verzii 2007 kliknite na tlačidlo Microsoft Office v ľavom hornom rohu okna. V okne, ktoré sa otvorí, prejdite do časti „Parametre“.
V okne parametrov kliknite na položku „Doplnky“. Po prechode vyberte v spodnej časti okna oproti parametru „Ovládanie“ hodnotu „Doplnky programu Excel“ a kliknite na tlačidlo „Prejsť“.
Otvorí sa okno s doplnkami. Pred názov doplnku, ktorý potrebujeme, začiarkneme políčko „Vyhľadať riešenie“. Kliknite na tlačidlo „OK“.
Potom sa na páse Excel na karte Údaje zobrazí tlačidlo na spustenie funkcie Hľadať riešenia.
Príprava tabuľky
Teraz, keď sme túto funkciu aktivovali, pozrime sa, ako to funguje. Najjednoduchšie si to možno predstaviť na konkrétnom príklade. Takže máme stôl mzdy zamestnanci podniku. Musíme vypočítať odmenu pre každého zamestnanca, ktorá je súčinom miezd označených v samostatnom stĺpci určitým koeficientom. Celková suma finančných prostriedkov pridelených na poistné je zároveň 30 000 rubľov. Bunka, v ktorej sa nachádza toto množstvo, má názov cieľovej bunky, pretože naším cieľom je vybrať údaje presne pre toto číslo.
Koeficient, ktorý sa používa na výpočet výšky poistného, \u200b\u200bmusíme vypočítať pomocou funkcie Hľadať riešenia. Bunka, v ktorej sa nachádza, sa nazýva požadovaná.
Cieľová bunka a cieľová bunka musia byť navzájom spojené pomocou vzorca. V našom konkrétnom prípade je vzorec umiestnený v cieľovej bunke a má nasledujúcu formu: „\u003d C10 * $ G $ 3“, kde $ G $ 3 je absolútna adresa hľadanej bunky a „C10“ je celková mzda, z ktorej sa počíta bonus za zamestnancov podniku.
Spustenie nástroja na nájdenie riešenia
Keď je tabuľka pripravená, na karte „Údaje“ kliknite na tlačidlo „Vyhľadať riešenie“, ktoré sa nachádza na páse kariet v paneli nástrojov „Analýza“.
Otvorí sa okno s parametrami, do ktorého musíte zadať údaje. Do poľa „Optimalizovať cieľovú funkciu“ musíte zadať adresu cieľovej bunky, kde sa bude nachádzať celková výška bonusu pre všetkých zamestnancov. To je možné vykonať buď manuálnym zadaním súradníc, alebo kliknutím na tlačidlo umiestnené vľavo od poľa na zadávanie údajov.
Potom sa minimalizuje okno s parametrami a môžete vybrať požadovanú bunku tabuľky. Potom musíte znova kliknúť na to isté tlačidlo vľavo od formulára so zadanými údajmi, aby ste znova rozšírili okno s parametrami.
Pod oknom s adresou cieľovej bunky musíte nastaviť parametre hodnôt, ktoré v nej budú. Môže to byť maximálna, minimálna alebo konkrétna hodnota. V našom prípade to bude posledná možnosť. Preto sme prepínač umiestnili do polohy „Hodnoty“ a do poľa naľavo od neho napíšeme číslo 30000. Ako si pamätáme, toto číslo podľa podmienok predstavuje celkovú výšku bonusu pre všetkých zamestnancov podniku.
Nižšie je uvedené pole „Zmena premenných buniek“. Tu musíte uviesť adresu požadovanej bunky, kde sa, ako si pamätáme, nachádza koeficient, vynásobením základného platu, ktorým sa bude vypočítať výška bonusu. Adresu je možné zapísať rovnakým spôsobom ako pre cieľovú bunku.
V poli „Podľa obmedzení“ môžete nastaviť určité obmedzenia údajov, napríklad urobiť hodnoty celé alebo nezáporné. Kliknite na tlačidlo „Pridať“.
Potom sa otvorí okno na pridanie obmedzenia. Do poľa „Odkaz na bunky“ napíšte adresu buniek, pre ktoré je zavedené obmedzenie. V našom prípade ide o požadovanú bunku s koeficientom. Potom odložíme požadované znamienko: „menšie alebo rovnaké“, „väčšie alebo rovnaké“, „rovnaké“, „celé číslo“, „binárne“ atď. V našom prípade zvolíme znamienko väčšie alebo rovné, aby bol koeficient kladné číslo. Podľa toho v poli „Obmedzenie“ uveďte číslo 0. Ak chceme nakonfigurovať ďalšie obmedzenie, kliknite na tlačidlo „Pridať“. V opačnom prípade kliknite na tlačidlo „OK“, aby ste zadané obmedzenia uložili.
Ako vidíte, potom sa obmedzenie zobrazí v príslušnom poli okna s parametrami vyhľadávania riešenia. Zaškrtnutím políčka vedľa zodpovedajúceho parametra, ktorý je uvedený nižšie, môžete premenné tiež zmeniť na nezáporné. Odporúča sa, aby tu uvedený parameter nebol v rozpore s tými, ktoré ste predpísali v obmedzeniach, inak môže dôjsť ku konfliktu.
Ďalšie nastavenia je možné vykonať kliknutím na tlačidlo „Parametre“.
Tu môžete nastaviť presnosť obmedzenia a limity riešenia. Po zadaní požadovaných údajov kliknite na tlačidlo „OK“. Pre náš prípad ale nie je potrebné tieto parametre meniť.
Po nastavení všetkých nastavení kliknite na tlačidlo „Nájsť riešenie“.
Ďalej program Excel v bunkách vykoná potrebné výpočty. Súčasne s výstupom výsledkov sa otvorí okno, v ktorom môžete buď nájsť nájdené riešenie, alebo obnoviť pôvodné hodnoty presunutím prepínača do príslušnej polohy. Bez ohľadu na vybratú možnosť začiarknutím políčka „Vrátiť sa do dialógového okna možností“ môžete opäť prejsť do nastavení riešenia vyhľadávania. Po nastavení začiarkavacích políčok a prepínačov kliknite na tlačidlo „OK“.
Ak vás výsledky hľadania riešení z nejakého dôvodu neuspokojia alebo ak program pri ich výpočte urobí chybu, potom sa v takom prípade vrátime, ako je popísané vyššie, do dialógového okna parametrov. Všetky zadané údaje revidujeme, pretože je možné, že sa niekde stala chyba. Ak sa chyba nenašla, prejdite na parameter „Vyberte metódu riešenia“. Tu si môžete zvoliť jednu z troch výpočtových metód: „Hľadanie riešenia nelineárnych problémov metódou OPG“, „Hľadanie riešenia lineárnych problémov simplexnou metódou“ a „Evolučné hľadanie riešenia“. Štandardne sa používa prvá metóda. Snažíme sa problém vyriešiť výberom akejkoľvek inej metódy. Ak nebude úspešný, skúste to znova pomocou poslednej metódy. Algoritmus akcií je rovnaký, ako je popísané vyššie.
Ako vidíte, funkcia Nájsť riešenie je pomerne zaujímavý nástroj, ktorý keď správne použitie, môže používateľovi výrazne ušetriť čas pri rôznych výpočtoch. Nie všetci používatelia, bohužiaľ, vedia o jeho existencii, nehovoriac o tom, ako správne pracovať s týmto doplnkom. V niektorých ohľadoch sa tento nástroj podobá funkcii , ale zároveň má s tým značné rozdiely.
Ak v bunka programu Excel zavádza sa vzorec, ktorý obsahuje odkaz na rovnakú bunku (možno nie priamo, ale nepriamo - prostredníctvom reťazca ďalších odkazov), potom sa hovorí, že existuje cyklický odkaz (cyklus). V praxi sa cyklické odkazy používajú, pokiaľ ide o implementáciu iteračného procesu, ktorý sa počíta pomocou vzťahov opakovania. V obvyklom režim Excel zistí cyklus a vydá správu o vzniknutej situácii a požaduje jej odstránenie. Excel nemôže vykonávať výpočty, pretože kruhové odkazy generujú nekonečné množstvo výpočtov. Z tejto situácie existujú dve cesty: vylúčiť kruhové odkazy alebo povoliť výpočty pomocou vzorcov s kruhovými odkazmi (v druhom prípade musí byť počet opakovaní cyklu konečný).
Zvážte problém hľadania koreňa rovnice pomocou Newtonovej metódy pomocou cyklických odkazov. Vezmite si kvadratickú rovnicu ako príklad: x 2 - 5x + 6 \u003d 0, ktorého grafické znázornenie je zobrazené v. Koren tejto (a akejkoľvek inej) rovnice nájdete pomocou jedinej bunky programu Excel.
Povoliť cyklické výpočty v systéme Windows ponuka Nástroje / Možnosti / karta Výpočty začiarknite políčko Iterácia, ak je to potrebné, zmeňte počet opakovaní slučiek v poli Obmedzte počet iterácií a presnosť výpočtov v teréne Relatívna chyba (predvolene sú ich hodnoty 100 a 0,0001). Okrem týchto nastavení vyberieme možnosť výpočtu: automaticky alebo ručne... Kedy automatické výpočet Excel okamžite dáva pri výpočte konečný výsledok ručne, môžete sledovať výsledok každej iterácie.
| Obrázok: 8. Graf funkcií |
Vyberme ľubovoľnú bunku, dajme jej nový názov, povedzme - X, a zaviesť do neho opakujúci sa vzorec určujúci výpočty podľa Newtonovej metódy:
,
kde F a F1 nastaví respektíve výrazy na výpočet hodnôt funkcie a jej derivácie. Pre našu kvadratickú rovnicu sa po zadaní vzorca zobrazí hodnota v bunke 2
zodpovedajúci jednému z koreňov rovnice (). V našom prípade nebola zadaná počiatočná aproximácia, iteračný výpočtový proces začal s predvolenou hodnotou uloženou v bunke X a rovná sa nule. Ako získate druhý koreň? Spravidla sa to dá urobiť zmenou počiatočného odhadu. Problém so zadaním počiatočných nastavení môžete vyriešiť v každom prípade rôznymi spôsobmi. Predvedieme jednu techniku \u200b\u200bzaloženú na použití funkcie IF. Aby sa zvýšila prehľadnosť výpočtov, bunkám boli priradené zmysluplné názvy (). 
2.2. Výber parametrov
Ak poznáte požadovaný výsledok vzorca, ale nepoznáte hodnoty potrebné na dosiahnutie tohto výsledku, môžete tento nástroj použiť Výber parametrovvýberom príkazu Výber parametrov v ponuke Služby... Keď vyberiete možnosť, program Excel zmení hodnotu v jednej konkrétnej bunke, kým výpočet podľa vzorca, ktorý odkazuje na túto bunku, neprinesie požadovaný výsledok.
Vezmite si ako príklad rovnakú kvadratickú rovnicu x 2 -5x + 6 \u003d 0... Ak chcete vyhľadať korene rovnice, postupujte takto:
Excel používa na výber parametra iteračný proces (každý s každým). Počet iterácií a presnosť sa nastavujú v ponuke Karta Nástroje / Možnosti / Výpočty... Ak Excel vykonáva zložitú úlohu výberu parametrov, môžete kliknúť Pauza v dialógovom okne Výsledok výberu parametra a prerušiť výpočet a potom stlačiť tlačidlo Krokznova iterovať a vidieť výsledok. Pri riešení problému v postupnom režime sa zobrazí tlačidlo Pokračovať - návrat do normálneho režimu výberu parametrov.
Vráťme sa k príkladu. Opäť sa vynára otázka: ako získať druhý koreň? Rovnako ako v predchádzajúcom prípade je potrebné nastaviť počiatočnú aproximáciu. Dá sa to urobiť takto ():
 |
| a |
 |
| b |
| Obrázok: 11. Nájdenie druhého koreňa |
To všetko sa však dá urobiť o niečo jednoduchšie. Ak chcete nájsť druhý koreň, stačí dať konštantu do bunky C2 ako počiatočnú aproximáciu () 5 a potom začať proces Výber parametrov.
2.3. Hľadanie riešenia
Tím Výber parametrov je vhodný na riešenie problémov s hľadaním konkrétnej cieľovej hodnoty v závislosti od jedného neznámeho parametra. Pri zložitejších úlohách použite príkaz Hľadanie riešenia (Riešiteľ), ku ktorej sa dostanete cez položku ponuky Vyhľadávanie služieb / riešení.
Úlohy, ktoré je možné vyriešiť pomocou Nájsť riešenie, sú vo všeobecnom prostredí formulované takto:
Nájsť:
x 1, x 2, ..., x n
také, že:
F (x 1, x 2, ..., x n)\u003e (max; min; \u003d hodnota)
s obmedzeniami:
G (x 1, x 2, ..., x n)\u003e (Ј Hodnota; i Hodnota; \u003d Hodnota)
Hľadané premenné - pracovné bunky pracovný hárok programu Excel - nazývajú sa nastaviteľné články. Objektívna funkcia F (x 1, x 2, ..., x n)niekedy označovaný jednoducho ako cieľ, musí byť uvedený ako vzorec v bunke v pracovnom hárku. Tento vzorec môže obsahovať funkcie definované používateľom a musí závisieť od (referenčných) nastaviteľných buniek. V okamihu stanovenia problému sa určí, čo s objektívnou funkciou. Môžete si zvoliť jednu z možností:
- nájdi maximum objektívnej funkcie F (x 1, x 2, ..., x n);
- nájsť minimum objektívnej funkcie F (x 1, x 2, ..., x n);
- dosiahnuť objektívnu funkciu F (x 1, x 2, ..., x n) mala pevnú hodnotu: F (x 1, x 2, ..., x n) \u003d a.
Funkcie G (x 1, x 2, ..., x n) sa nazývajú obmedzenia. Môžu byť špecifikované vo forme rovnosti aj nerovnosti. Na regulované bunky možno uplatniť ďalšie obmedzenia: nezápornosť a / alebo celé číslo, hľadané riešenie sa potom hľadá v rozsahu kladných a / alebo celých čísel.
Táto formulácia pokrýva najširšiu škálu optimalizačných problémov vrátane riešenia rôznych rovníc a sústav rovníc, problémov lineárneho a nelineárneho programovania. Takéto problémy sa zvyčajne dajú ľahšie formulovať ako vyriešiť. A potom, aby sa vyriešil konkrétny optimalizačný problém, je potrebná špeciálne navrhnutá metóda. Riešiteľ má vo výzbroji výkonné nástroje na riešenie týchto problémov: zovšeobecnená gradientová metóda, simplexná metóda, pobočková a viazaná metóda.
Vyššie, aby sa našli korene kvadratickej rovnice, bola použitá Newtonova metóda (časť 1.4) pomocou cyklických odkazov () a nástroja Výber parametrov (). Pozrime sa, ako sa používa Hľadanie riešenia pomocou príkladu rovnakej kvadratickej rovnice.
Po otvorení dialógu Hľadanie riešenia () musíte urobiť nasledovné:- v teréne Nastaviť cieľovú bunku zadajte adresu bunky, ktorá obsahuje vzorec na výpočet hodnôt optimalizovanej funkcie, v našom príklade je cieľová bunka C4 a vzorec v nej je: \u003d C3 ^ 2 - 5 * C3 + 6;
- ak chcete maximalizovať hodnotu cieľovej bunky, nastavte prepínač maximálna hodnota do polohy 8 sa spínač používa na minimalizáciu minimálna hodnota, v našom prípade nastavte prepínač na hodnotu a zadajte hodnotu 0 ;
- v teréne Meniace sa bunky zadajte adresy buniek, ktoré sa majú zmeniť, t.j. argumenty objektívnej funkcie (C3), oddeľujúce ich znakom „;“ (alebo kliknutím myši počas stlačenia klávesu Сtrl na príslušných bunkách), na automatické vyhľadanie všetkých buniek ovplyvňujúcich riešenie, použite tlačidlo Hádajte;
- v teréne Obmedzenia pomocou tlačidla Pridať k zadajte všetky obmedzenia, ktoré musí výsledok vyhľadávania spĺňať: v našom príklade nemusíte nastavovať obmedzenia;
- ak chcete zahájiť proces hľadania riešenia, stlačte tlačidlo Vykonať.
Ak chcete získané riešenie uložiť, musíte použiť prepínač Uložiť nájdené riešenie v otvorenom dialógovom okne Výsledky hľadania riešení... Potom bude mať pracovný hárok podobu uvedenú v. Výsledné riešenie závisí od voľby počiatočnej aproximácie, ktorá je uvedená v bunke C4 (argument funkcie). Ak ako počiatočná aproximácia v bunke C4 zadajte hodnotu rovnú 1,0 , potom pomocou Nájsť riešenie nájsť druhý koreň rovný 2,0 .
Možnosti upravujúce prácu Nájsť riešenieuvedené v okne Parametre (okno sa zobrazí, keď kliknete na tlačidlo Parametre okno Hľadanie riešenia), nasledujúci ():
- Maximálny čas - obmedzuje čas pridelený procesu hľadania riešenia (predvolená hodnota je 100 sekúnd, čo stačí na problémy s približne 10 obmedzeniami, ak má problém veľký rozmer, musí sa čas predĺžiť).
- Obmedzte počet iterácií - ďalší spôsob, ako obmedziť čas hľadania nastavením maximálneho počtu iterácií. Predvolená hodnota je 100 a najčastejšie, ak sa riešenie nedosiahne v 100 iteráciách, potom s nárastom ich počtu (v poli môžete zadať čas nepresahujúci 32767 sekúnd) je pravdepodobnosť získania výsledku malá . Lepšie je pokúsiť sa zmeniť pôvodnú aproximáciu a znova spustiť proces vyhľadávania.
- Relatívna chyba - určuje presnosť, s akou bunka zodpovedá cieľovej hodnote alebo aproximácia k zadaným limitom (desatinný zlomok od 0 do 1).
- Tolerancia - nastavený v% iba pre problémy s celočíselnými obmedzeniami. Hľadanie riešenia pri takýchto problémoch najskôr nájde optimálne neceločíselné riešenie a potom sa pokúsi nájsť najbližší celočíselný bod, ktorého riešenie by sa od optimálneho líšilo najviac o počet percent označený týmto parametrom.
- Konvergencia - keď je relatívna zmena hodnoty v cieľovej bunke za posledných päť iterácií menšia ako počet (zlomok z intervalu od 0 do 1) uvedený v tomto parametri, vyhľadávanie sa zastaví.
- Lineárny model - toto začiarkavacie políčko by malo byť povolené, ak sú cieľová funkcia a obmedzenia lineárne funkcie. To urýchľuje proces hľadania riešenia.
- Nezáporné hodnoty - tento príznak je možné použiť na nastavenie obmedzení pre premenné, ktoré vám umožnia hľadať riešenia v kladnom rozsahu hodnôt bez určenia osobitných obmedzení na ich dolnej hranici.
- Automatické škálovanie - tento príznak by mal byť povolený, ak sa škála hodnôt vstupných premenných a objektívna funkcia a obmedzenia líšia, možno o rády. Napríklad premenné sú stanovené v kusoch a objektívna funkcia, ktorá určuje maximálny zisk, sa meria v miliardách rubľov.
- Zobraziť výsledky iterácie - toto začiarkavacie políčko umožňuje povoliť postupný vyhľadávací proces zobrazujúci na obrazovke výsledky jednotlivých iterácií.
- Odhady - táto skupina slúži na označenie metódy extrapolácie - lineárnej alebo kvadratickej - použitej na získanie počiatočných odhadov hodnôt premenných v každom jednorozmernom vyhľadávaní. Lineárne slúži na použitie lineárnej extrapolácie pozdĺž vektora dotyčnice. Kvadratický slúži na použitie kvadratickej extrapolácie, ktorá poskytuje lepšie výsledky pri riešení nelineárnych problémov.
- Rozdiely (deriváty) - táto skupina slúži na označenie metódy numerickej diferenciácie, ktorá sa používa na výpočet parciálnych derivácií objektívnych a obmedzujúcich funkcií. Parameter Priamy sa používa vo väčšine úloh, kde je miera zmeny obmedzení relatívne nízka. Parameter Centrálne používa sa pre funkcie s nespojitou deriváciou. Táto metóda vyžaduje viac výpočtov, ale jej použitie je možné odôvodniť, ak sa zobrazí správa, že nie je možné získať presnejšie riešenie.
- Metóda vyhľadávania - slúži na výber optimalizačného algoritmu. Newtonova metóda bolo diskutované skôr. IN Metóda konjugovaného gradientu vyžaduje sa menej pamäte, ale vykoná sa viac iterácií ako v Newtonovej metóde. Táto metóda by sa malo použiť, ak je úloha dostatočne veľká a je potrebné šetriť pamäť, a tiež ak iterácie poskytujú príliš malý rozdiel v postupných aproximáciách.
- pri ukladaní zošita programu Excel po nájdení riešenia všetky hodnoty zadané v dialógových oknách Hľadanie riešeniasa ukladajú spolu s údajmi z hárka. S každým pracovným hárkom v zošite je možné uložiť jednu sadu hodnôt parametrov Nájsť riešenie;
- ak je v rámci jedného excelového hárku potrebné brať do úvahy niekoľko optimalizačných modelov (napríklad nájsť maximum a minimum jednej funkcie alebo maximálnu hodnotu viacerých funkcií), potom je pohodlnejšie tieto modely uložiť pomocou tlačidla Možnosti / Uložiť model okno Hľadanie riešenia... Rozsah pre uložený model obsahuje informácie o cieľovej bunke, o zmenených bunkách, o každom z obmedzení a všetkých hodnotách dialógového okna Parametre... Výber modelu na riešenie konkrétneho optimalizačného problému sa vykonáva pomocou tlačidla Parametre / zaťažovací model dialóg Hľadanie riešenia;
- ďalším spôsobom, ako uložiť parametre vyhľadávania, je uložiť ich ako pomenované skripty. Kliknite na toto tlačidlo Uložiť skript dialógové okno Výsledky hľadania riešení.
Okrem vloženia optimálnych hodnôt do buniek, ktoré sa majú meniť Hľadanie riešenia umožňuje prezentovať výsledky vo forme troch správ: výsledky, Udržateľnosť a Limity... Ak chcete vygenerovať jeden alebo viac prehľadov, vyberte ich názvy v dialógovom okne Výsledky hľadania riešení... Zvážme každý z nich podrobnejšie.
 |
| Obrázok: 15. Správa o udržateľnosti |
Správa Limity obsahuje informácie o limitoch, v rámci ktorých je možné zvýšiť alebo znížiť hodnoty upravených buniek bez porušenia obmedzení úloh. Pre každú bunku, ktorú zmeníte, obsahuje tento prehľad optimálnu hodnotu a najmenšie hodnoty, ktoré bunka dokáže akceptovať bez porušenia obmedzení.