Použitie Riešiteľa v Exceli



Pomocou nástroja Excelu Riešiteľ (Solver) je možné zistiť optimálnu hodnotu výsledku vzorca. Vzorec je v bunke nazývanej cieľová bunka (target cel). Riešiteľ skúma skupinu buniek, ktoré sú v priamom alebo nepriamom vzťahu k vzorcu v cieľovej bunke. Riešiteľ nastaví hodnoty v bunkách (nastaviteľné bunky), ktoré sa majú meniť a ktoré užívateľ špecifikuje, aby sa získal výsledok zadaný vzorcom v cieľovej bunke. Užívateľ môže uplatniť obmedzenia na bunky, ktoré sa majú meniť, alebo na cieľovú bunku, ako aj na bunky, ktoré sú priamo alebo nepriamo vo vzťahu k cieľovej bunke, aby sa obmedzili hodnoty, ktoré môže Riešiteľ použiť v modeli. Obmedzenia môžu sa vzťahovať na iné bunky, ktoré majú vplyv na cieľovú bunku. Program Microsoft Excel pracuje s nelineárnym optimalizačným kódom Generalized Reduced Gradient (GRG2).

Najlepšie bude ak si ukážeme jednoduchý príklad. Majme trojuholník ABC (Obr. 1). Poznáme dĺžku strán r2, r3, r4. Použijeme solver pre výpočet uhlov Θ2 a Θ3.

troj3 (24K)
solv2 (36K)

Ako vidíme na obr. 2 zapíšeme si navrhované premenné (červené pozadie buniek). Na obrázku už máme výsledné hodnoty, ale užívateľ zadáva do týchto buniek odhad, napr. Θ2 = 50 °, Θ3= 250 °. Do bunky E12 zapíšeme vzorec (1):

(1)      =B9*COS(RADIANS(E9))+B10*COS(RADIANS(E10))-B11

a do bunky E13 vzorec (2):

(2)      =B9*SIN(RADIANS(E9))-B10*SIN(RADIANS(360-E10))

solv3 (15K)

Ak už máme zadané odhady hľadaných premenných, a zapísané vzorce, klikneme na DATA, ako je to zobrazené na obr. 2 a potom na SOLVER.

solv4 (15K)
solv6 (11K)

Môžeme skúsiť SOLV, t.j. či solver najde riešenie. Tiež môžeme upresniť voľby kliknutím na OPTIONS a zadať napr. väčšiu presnosť alebo zmeniť niektoré iné parametre. Tiež, ako vidíme na obr. 4, niekedy je nutné, zadať pre cieľovú funkciu obmedzenia (constraints), napr. aby výsledok bol menší alebo rovný ako 0,000001.Túto hodnotu sme zadali do bunky I13.

Existuje len jedno riešenie?

Preklopme trojuholník okolo strany AB. V tomto prípade výpočet nám vráti uhol - Θ2 a uhol 360 - Θ3. Zadajme záporný uhol Θ2 napr. -30 °, ako to ukazuje obrázok 7, a Θ3 zadajme 0 °. Pomocou solvera získaváme výsledok, ktorý je zobrazený na obr. 8.

obrtroj (24K)
solv7 (4K)
solv8 (8K)
solv9 (5K)

Z obrázku 9 vidíme, že ak spočítame hodnoty v bunke A5 a A3, pričom výsledok v bunke A3 bol získaný inou počiatočnou hodnotou ako výsledok v bunke A5, dostávame 360° Riešenie dosahuje presnosť na 3 destinné miesta. Tak isto po spočítaní hodnôt v bunkách A2 a A4 dostávame výsledok 0 ° s presnosťou na 2 desatinné miesta.

Riešenie závisí od začiatočných hodnôt, ktoré boli zvolené. Platí to obecne pre problémy optimalizácie.

Príklady boli vypracované v Excel 2007 pod OS Windows XP


Kontakty

    Stránka je v súlade s aktuálnymi normami.

   Valid HTML 4.01 Transitional

   Domov




© Klára Mrázová