Hledání řešení v Excelu je excelový nástroj, který je poměrně málo využívaný. Jak už název funkce napovídá, jedná se o možnost hledání řešení v Excelu. Tato funkce je skrytá pod záložkou prognózy v citlivostní analýze. Tedy záložkou, která se zdá být pro běžného uživatele Excelu naprosto zbytečná. Opak je ale pravdou. Funkce hledání řešení vám v mnoha ohledech může ušetřit spoustu času.
Excelový soubor ke stažení
Hledání řešení v Excelu | Citlivostní analýza
Pojďme si na praktických příkladech ukázat, jak hledání řešení v Excelu funguje a k čemu je tato funkce vlastně dobrá?
Citlivostní analýza s možností hledání řešení se skvěle hodí tam, kde potřebujete měnit proměnné na vstupu, tedy ve vzorci, ale nechce se vám to dělat metodou pokus omyl.
Citlivostní analýzu a hledání řešení najdeme v Excelu na záložce Data > Prognóza > Citlivostní analýza > Hledání řešení. Ve vyskakovacím okně Hledání řešení potřebujeme vyplnit několik parametrů. Nastavená buňka je buňka, která obsahuje vzorec. Proto, aby Excel mohl najít vhodné řešení, tak mu musíme dát vzorec s proměnnými, pomocí kterých najde správné řešení. Označíme tedy buňku, ve které máme vzorec ($B$8). Cílová hodnota je v tomto případě oněch 300 Kč. A měněná buňka je proměnná, kterou chceme změnit ($B$5).
Výběr potvrdíme a Excel začne počítat. Když je Excel s výpočtem hotový, objeví se tabulka Stav hledání řešení. Zde se dočteme, že pro buňku B8, což je buňka se vzorcem, který nám počítá celkovou marži, bylo nalezeno řešení, a to řešení se nám propsalo rovnou do buňky B5. Předtím bylo v buňce B5 celkem 10 kusů a teď je zde 15 kusů. Stejně tak se přepočítala celková marže v buňce B8.
Jak to Excel udělal? Tím, že jsme jako nastavenou buňku označili buňku se vzorcem, tak Excel byl schopný pochopit vztah mezi jednotlivými proměnnými, neboli vstupními údaji. Pak jsme označili buňku, kterou chceme změnit a nastavili cílovou hodnotu a Excel provedl příslušný výpočet za nás. Pokud bychom s řešením nebyli spokojení, zmáčkneme zde Zrušit a proměnné se změní na výchozí hodnoty.
Stejně tak nás ale může zajímat, na kolik bychom museli zvýšit naši marži, abychom vydělali 555 Kč. Zkusíme to tedy znovu za pomoci Citlivostní analýzy. V okně Hledání řešení nastavíme jako Nastavenou buňku $B$8, cílová hodnota je 555 Kč a měněná buňka je tentokrát marže, tedy pole $B$7. Výběr potvrdíme a Excel najde vhodné řešení. Stave řešení vypočetl, že abychom při 10 prodaných kusech za 100 Kč kus vydělali 555 Kč, tak bychom museli uplatnit marži ve výši 56 %.
Hledání řešení v Excelu – splátka úvěru
Hledání řešení využijeme například i u výpočtu splátky úvěru. V našem příkladu máme vstupní údaje pro úvěr (obrázek č.4). Chceme si půjčit 150 000 Kč, za 8 % úrok na 60 měsíců. Pro výpočet splátky využijeme funkci PLATBA (B17). Ve funkci PLATBA označíme jako první parametr sazbu, což je buňka s úrokovou sazbou (B15). Jelikož ale počítáme měsíční splátku, tak musíme úrok vydělit číslem 12. Jako druhý parametr je zde pper, což je celková doba trvání úvěru, tedy celková splatnost (B16). Posledním povinným parametrem je současná hodnota, což je výše úvěru (B14).
Měsíční splátka nám vyšla ve výši 3 041 Kč (B17). Všimněte si, že je před splátkou uvedené znaménko mínus, je to proto, že jsme ve funkci PLATBA uvedli současnou hodnotu kladně, kdybychom chtěli, aby celková splátka vyšla jako kladné číslo, tak před současnou hodnotu musíme napsat mínus.
Měsíční splátku tedy máme spočítanou v buňce B17. Co když jsme ovšem schopní splácet pouze 2 800 Kč měsíčně? Jak zjistíme, jakou částku si tedy můžeme půjčit bez toho abychom to opět zkoušeli pokus omyl? I zde můžeme využít citlivostní analýzu a hledání řešení.
V prvním případu chceme zjistit, kolik by nám za úrok 8 % na 60 měsíců půjčili, když by měsíční splátka byla 2 800 Kč. Na kartě Data najdeme Citlivostní analýzu a vybereme Hledání řešení. Jako nastavenou buňku musíme vždy vybrat buňku, která obsahuje vzorec. Vybereme tedy pole splátka (E17). Jako cílovou hodnotu nastavíme -2 800 Kč, což je měsíční splátka, kterou jsme schopni splácet. Jelikož máme funkci PLATBA nastavenou s mínusem, tak i zde musíme vyplnit splátku s mínusovým znaménkem. Měněná buňka je v tomto případě výše půjčky (E14). Potvrdíme a Excel bude provádět chvíli výpočet a nakonec vypočte cílovou částku půjčky, kterou si můžeme půjčit, aby byla splátka 2 800 Kč měsíčně při zadaných parametrech. Výsledná půjčka je 138 092 Kč.
Co kdybychom ale potřebovali půjčit přesně 150 000 Kč, nemůžeme splácet více jak 2 800 Kč ale možná bychom našli banku s jiným úrokem. Budeme tedy postupovat úplně stejně. Na kartě Data najdeme Citlivostní analýzu a Hledání řešení a jako nastavenou buňku vybereme opět buňku se splátkou (H17). Jako cílovou hodnotu nastavíme -2 800 Kč a jako měněnou buňku označíme úrok (H15). Úrok se přepočítá na 5 %. Museli bychom tedy najít banku, která nám půjčí za 5 % úrok.
Jako poslední možnost můžeme ještě změnit délku splatnost úvěru. Zkusíme tedy i tento způsob. Opět vybereme Data, Hledání řešení a zde jako nastavenou buňku vybereme buňku se splátkou (K17), cílovou hodnotu nastavíme -2 800 Kč a jako měněnou buňku nastavíme splatnost (K16). Potvrdíme a výsledkem je místo 60 měsíců splácení 66,5 měsíců splácení.
2 komentáře
Video na Vaší stránce se nezobrazuje. Zobrazuje se Video Placeholder:Choose your video.
Senior
Dobrý den, děkuji za upozornění, již opraveno 🙂