Otázky a odpovědi kolem spreadsheetů

Dozvěděl jsem se, že v Excelu 7.0 existuje možnost zpětného přepočítání referenčních buněk pro nějaký vzorec. ...


Dozvěděl jsem se, že v Excelu 7.0 existuje možnost zpětného přepočítání
referenčních buněk pro nějaký vzorec. Pro lepší vysvětlení: V jedné buňce mám
vložený vzorec, a potřeboval bych, aby dosahoval určité hodnoty (výsledku),
tzn., aby se podle této výsledné hodnoty přepočítala nějaká buňka, na kterou se
výše uvedený vzorec odkazuje. Jak to mám udělat?
Pokud jsem správně pochopil otázku, máte na mysli zpětné hledání řešení a
řešitele, potažmo případně "what if" tabulky, či scénáře. Podívejme se tedy na
všechna tato témata a na jejich souvislosti.
Hledání řešení s jednou změnou vstupní buňky
Pokud stačí přepočítat pouze jednu buňku, na kterou je z výsledné buňky odkaz,
aby se docílilo požadované hodnoty, je možno použít nabídku Nástroje, Hledání
řešení. Zobrazí se dialogové okno se třemi poli. Do pole Nastavit buňku zadáme
adresu buňky, ve které chceme dosáhnout požadované hodnoty. Do pole Cílová
hodnota zadáme hodnotu, které chceme dosáhnout, a do Měněné buňky zadáme adresu
buňky, která má být přepočítána na takovou hodnotu, aby vyhovovala cílové
hodnotě "nastavené buňky". Přitom zcela logicky musí být splněny 2 podmínky:
1. Měněná buňka musí být použita vzorcem v nastavené buňce.
2. V měněné buňce se musí nacházet číselná konstanta.
Příklad (obr. 1): Předpokládejme, že chceme na sortimentu pečiva docílit zisku
30 000. Vidíme, že cena housek je poměrně nízká. Proto zvolíme jako nastavenou
buňku F6, jako cílovou hodnotu 30 000 a jako měněnou buňku D3. Druhá tabulka
pak zobrazuje výsledek na základě našich požadavků.
Použití řešitele
Chceme-li použít řešitele, musíme pro něj mít zaveden příslušný doplněk.
Vyvoláme nabídku Nástroje a podíváme se, nachází--li se zde položka Řešitel.
Pokud ne, vyvoláme nabídku Nástroje, Doplňky, najdeme řádek Řešitel, zaškrtneme
vedle něj vlevo čtvereček a potvrdíme. K aplikaci MS Excel se připojí doplněk
Solver.xla, do nabídky Nástroje přibude položka Řešitel.
Jak jste si jistě všimli, v seznamu je doplňků celá řada, proto je vhodné mít
zaškrtnuty pouze ty, které stále užíváte. Zkrátíte tak dobu, po kterou se Excel
spouští a ušetříte operační paměť.
Abychom mohli úspěšně užívat řešitele, je třeba mít alespoň matnou představu o
lineární a nelineární optimalizaci. V podstatě jde o to, že chceme docílit
přesnou hodnotu nějakého ukazatele, nebo chceme jeho hodnotu maximalizovat nebo
minimalizovat. Ukazatel je přitom definován nějakou funkční závislostí na
dílčích, elementárních ukazatelích. Náš požadavek je tedy zkombinovat číselné
hodnoty těchto dílčích ukazatelů tak, aby vyhovovaly našemu požadavku (přesné
hodnotě, maximu, minimu). Přitom však možná hodnota těchto elementárních
ukazatelů může být nějakým způsobem omezena shora nebo zdola a tato omezení
nelze v příslušném směru překročit.
Příklad (obr. 2): Předpokládejme, že jsme v prvním roce uložili částku 10 000
Kč při určitém úroku, který může být průběžně proměnlivý. Úrok je zdaňován
nějakou daní, která se může též měnit zrovna tak jako procento poplatků bance
počítané z výše vkladu.
Výpočet celkového vkladu s výnosem se nachází v buňce F9 a je evidentně závislý
na úroku, dani a poplatku bance. Naše funkce bude vypadat takto:
Vklad = Počáteční_vklad * ( 1 + úrok úrok * daň poplatky)roky
V našem případě budeme počítat výši možných úspor po 4 letech. Není jisté, kdy
a jak se budou měnit úrok, daň a poplatky, lze však odhadnout, v jakých mezích
se úrok, daň a poplatky budou pohybovat, což budou naše omezující podmínky. Za
těchto omezujících podmínek poté můžeme pomocí řešitele hledat např. odhad
nejvyššího reálného výnosu.
Vyvoláme nabídku Nástroje, Řešitel. Objeví se dialogové okno Parametry
řešitele. Do pole Nastavit buňku nastavíme v našem případě buňku F9. V řádku Je
rovno vybereme Max. Do pole měněné buňky nastavíme oblast B3:D3. Potom musíme
vytvořit omezující podmínky. Předpokládejme, že úroková sazba nemůže klesnout
pod 8 %, zároveň je nereálné, aby překročila 16 %. Daň neklesne pod 15 % a
nepřekročí 30 %. Konečně poplatky neklesnou pod 0,2 % a nepřevýší 0,8 %.
Tyto požadavky musíme zahrnout do pole omezujících podmínek. Stiskneme tlačítko
Přidat. Ukáže se dialogové okno Přidat omezující podmínku. Vyznačíme buňku, pro
kterou má omezující podmínka platit, poté vybereme příslušný operátor a zadáme
omezující hodnotu.
Parametry práce řešitele (maximální čas výpočtu, počet iterací, přesnost apod.)
lze nastavit tlačítkem Možnosti v dialogovém okně Nástroje, Řešitel, Parametry
řešitele.
Máme-li vše dobře nastaveno, stiskneme tlačítko Řešit. Po určité době se v
závislosti na složitosti modelu, jeho řešitelnosti a dalších doplňkových
parametrech objeví dialogové okno Výsledky řešení. Zde se můžete rozhodnout,
zda se mají získané hodnoty řešení uložit do příslušných buněk, nebo zda mají
být v buňkách zachovány hodnoty původní.
V tomto dialogovém okně se nachází i tlačítko, s jehož pomocí lze vytvořit
scénář. Scénáře jsou vlastně souhrny informací o měněných buňkách a jejich
hodnotách, kde vyvoláním příslušného scénáře dosazujeme do stále stejné tabulky
na příslušná místa jiné skupiny údajů (můžeme je vytvořit i přímo bez pomoci
řešitele prostřednictvím nabídky Nástroje, Správce scénářů).
8 1649 / Maf









Komentáře
K tomuto článku není připojena žádná diskuze, nebo byla zakázána.