Obsah
„Hledat řešení“ je doplněk Excelu, jehož prostřednictvím je možné vybrat nejlepší řešení problémů na základě zadaných omezení. Funkce umožňuje rozvrhovat zaměstnance, rozdělovat náklady nebo investice. Vědět, jak tato funkce funguje, vám ušetří čas a námahu.
Co je hledání řešení
V kombinaci s různými dalšími možnostmi v Excelu existuje jedna méně oblíbená, ale nesmírně potřebná funkce „Hledat řešení“. Navzdory tomu, že jeho nalezení není snadné, jeho poznání a používání pomáhá při řešení mnoha problémů. Volba zpracovává data a dává optimální řešení z povolených. Článek popisuje, jak přímo funguje hledání řešení.
Jak zapnout funkci „Hledat řešení“.
Navzdory účinnosti není dotyčná možnost na nápadném místě na panelu nástrojů nebo kontextové nabídce. Většina uživatelů, kteří pracují v Excelu, si jeho přítomnost neuvědomuje. Ve výchozím nastavení je tato funkce zakázána, chcete-li ji zobrazit, proveďte následující akce:
- Otevřete „Soubor“ kliknutím na příslušný název.
- Klikněte na sekci „Nastavení“.
- Poté vyberte podsekci „Doplňky“. Zde se zobrazí všechny doplňky programu, níže se zobrazí nápis „Management“. Na pravé straně bude vyskakovací nabídka, kde byste měli vybrat „Doplňky aplikace Excel“. Poté klikněte na „Go“.
1 - Na monitoru se zobrazí další okno „Doplňky“. Zaškrtněte políčko vedle požadované funkce a klikněte na OK.
- Požadovaná funkce se objeví na pásu karet napravo od sekce „Data“.
O modelech
Tyto informace budou mimořádně užitečné pro ty, kteří se s pojmem „model optimalizace“ teprve seznamují. Před použitím „Hledat řešení“ se doporučuje prostudovat materiály o metodách vytváření modelů:
- zvažovaná varianta umožní určit nejlepší způsob, jak alokovat finanční prostředky na investice, nakládku prostor, zásobování zbožím nebo jiné akce, kde je nutné najít nejlepší řešení.
- „Optimální metoda“ by v takové situaci znamenala: zvýšení příjmů, snížení nákladů, zlepšení kvality atd.
Typické optimalizační úkoly:
- Stanovení plánu výroby, při kterém bude maximální zisk z prodeje uvolněného zboží.
- Stanovení dopravních map, při kterých jsou minimalizovány přepravní náklady.
- Hledejte rozmístění více strojů pro různé druhy práce tak, aby se snížily výrobní náklady.
- Stanovení nejkratší doby pro dokončení práce.
Důležité! Pro formalizaci úkolu je nutné vytvořit model, který odráží hlavní parametry předmětné oblasti. V aplikaci Excel je model sada vzorců, které používají proměnné. Uvažovaná možnost hledá takové indikátory, že účelová funkce je větší (menší) nebo rovna zadané hodnotě.
Přípravná fáze
Před umístěním funkce na pás karet musíte pochopit, jak tato možnost funguje. V tabulce jsou například uvedeny informace o prodeji zboží. Úkolem je přiřadit slevu pro každou položku, což by bylo 4.5 milionu rublů. Parametr je zobrazen uvnitř buňky s názvem target. Na jeho základě se vypočítají další parametry.
Naším úkolem bude vypočítat slevu, kterou se násobí sumy za prodej různých produktů. Tyto 2 prvky jsou spojeny vzorcem napsaným takto: =D13*$G$2. Kde v D13 je zapsáno celkové množství pro implementaci a $G$2 je adresa požadovaného prvku.
Použití funkce a její nastavení
Když je vzorec připraven, musíte přímo použít samotnou funkci:
- Musíte se přepnout do sekce „Data“ a kliknout na „Vyhledat řešení“.
- Otevře se „Možnosti“, kde se nastavují požadovaná nastavení. V řádku „Optimalizovat cílovou funkci:“ byste měli určit buňku, kde se zobrazí součet slev. Souřadnice je možné si předepsat sami nebo vybrat z dokumentu.
- Dále je třeba přejít do nastavení dalších parametrů. V sekci „Komu:“ je možné nastavit maximální a minimální limity nebo přesný počet.
- Poté je vyplněno pole „Změna hodnot proměnných:“. Zde se zadávají údaje požadované buňky, která obsahuje konkrétní hodnotu. Souřadnice se registrují nezávisle nebo se klikne na odpovídající buňku v dokumentu.
- Poté se upraví záložka „Podle omezení:“, kde se nastavují omezení aplikovaných dat. Vyloučeny jsou například desetinné zlomky nebo záporná čísla.
- Poté se otevře okno, které vám umožní přidat omezení do výpočtů. Počáteční řádek obsahuje souřadnice buňky nebo celého rozsahu. V návaznosti na podmínky úlohy jsou uvedeny údaje požadované buňky, kde je zobrazen indikátor slevy. Poté se určí srovnávací znaménko. Je nastaveno na „větší než nebo rovno“, takže konečná hodnota není se znaménkem mínus. „Limit“ nastavený na řádku 3 je v této situaci 0. Je také možné nastavit limit pomocí „Přidat“. Další kroky jsou stejné.
- Po dokončení výše uvedených kroků se nastavený limit objeví v největším řádku. Seznam může být velký a bude záviset na složitosti výpočtů, nicméně v konkrétní situaci stačí 1 podmínka.
- Kromě toho je možné zvolit další pokročilá nastavení. V pravém dolním rohu je možnost „Možnosti“, která vám to umožňuje.
- V nastavení můžete nastavit „Přesnost omezení“ a „Limity řešení“. V naší situaci není potřeba tyto možnosti využívat.
- Po dokončení nastavení se spustí samotná funkce – klikněte na „Najít řešení“.
- Poté, co program provede požadované výpočty a vydá konečné výpočty v požadovaných buňkách. Poté se otevře okno s výsledky, kde se výsledky uloží/zruší, případně se nakonfigurují parametry vyhledávání podle nového. Když data splňují požadavky, nalezené řešení se uloží. Pokud předem zaškrtnete políčko „Návrat do dialogového okna možností hledání řešení“, otevře se okno s nastavením funkcí.
- Existuje možnost, že se výpočty ukázaly jako chybné nebo je potřeba změnit výchozí údaje, aby bylo možné získat další ukazatele. V takové situaci musíte znovu otevřít okno nastavení a znovu zkontrolovat informace.
- Když jsou data přesná, lze použít alternativní metodu. Pro tyto účely musíte kliknout na aktuální možnost a vybrat nejvhodnější metodu ze seznamu, který se zobrazí:
- Hledání řešení pomocí zobecněného gradientu pro nelineární problémy. Ve výchozím nastavení je tato možnost použita, ale je možné použít i jiné.
- Hledání řešení lineárních úloh založených na simplexové metodě.
- Použití evolučního vyhledávání k dokončení úkolu.
Pozor! Když výše uvedené možnosti nezvládly úlohu, měli byste znovu zkontrolovat data v nastavení, protože to je často hlavní chyba v takových úlohách.
- Po obdržení požadované slevy zbývá její uplatnění pro výpočet výše slev pro každou položku. Za tímto účelem se zvýrazní počáteční prvek sloupce „Částka slevy“, zapíše se vzorec «=D2*$G$2» a stiskněte „Enter“. Znaky dolaru jsou umístěny tak, že když je vzorec natažen na sousední řádky, G2 se nezmění.
- Nyní bude získána částka slevy pro počáteční položku. Poté byste měli přesunout kurzor nad roh buňky, když se stane „plus“, stisknete LMB a vzorec se roztáhne na požadované řádky.
- Poté bude stůl konečně připraven.
Načíst/Uložit možnosti hledání
Tato možnost je užitečná při použití různých možností omezení.
- V nabídce Možnosti hledání řešení klikněte na Načíst/Uložit.
- Zadejte rozsah pro oblast modelu a klikněte na Uložit nebo Načíst.
Při ukládání modelu se zadá odkaz na 1 buňku prázdného sloupce, kam bude umístěn optimalizační model. Během načítání modelu se zadává odkaz na celý rozsah, který obsahuje optimalizační model.
Důležité! Chcete-li uložit poslední nastavení v nabídce Možnosti řešení, uloží se sešit. Každý list v něm má své vlastní možnosti doplňku Řešitel. Kromě toho je možné nastavit více než 1 úkol pro list kliknutím na tlačítko „Načíst nebo uložit“ a uložit jednotlivé úkoly.
Jednoduchý příklad použití Solveru
Nádobu je nutné zatížit nádobami tak, aby její hmotnost byla maximální. Nádrž má objem 32 metrů krychlových. m Naplněná bedna má hmotnost 20 kg, její objem je 0,15 m80. m Krabice – 0,5 kg a 110 cu. m Požaduje se, aby celkový počet nádob byl minimálně XNUMX ks. Data jsou uspořádána takto:
Proměnné modelu jsou označeny zeleně. Cílová funkce je zvýrazněna červeně. Omezení: nejmenším počtem nádob (větším nebo rovným 110) a hmotností (=SUMPRODUCT(B8:C8,B6:C6) – celková hmotnost obalu v nádobě.
Analogicky uvažujeme celkový objem: =SUMPRODUCT(B7:C7,B8:C8). Takový vzorec je nezbytný pro stanovení limitu na celkový objem nádob. Poté se prostřednictvím „Hledat řešení“ zadávají odkazy na prvky s proměnnými, vzorce a samotné indikátory (nebo odkazy na konkrétní buňky). Počet kontejnerů je samozřejmě celé číslo (je to také omezení). Stiskneme „Najít řešení“, v důsledku čehož najdeme takový počet kontejnerů, kdy je celková hmotnost maximální a jsou zohledněna všechna omezení.
Hledání řešení se nepodařilo najít řešení
Takové upozornění se objeví, když daná funkce nenalezla kombinace proměnných skóre, které splňují každé omezení. Při použití metody Simplex je docela možné, že neexistuje žádné řešení.
Použije-li se metoda řešení nelineárních problémů, ve všech případech počínaje počátečními ukazateli proměnných, naznačuje to, že možné řešení má k takovým parametrům daleko. Pokud funkci spustíte s jinými počátečními indikátory proměnných, pak pravděpodobně existuje řešení.
Například při použití nelineární metody nebyly vyplněny prvky tabulky s proměnnými a funkce nenacházela řešení. To neznamená, že neexistuje žádné řešení. Nyní, s přihlédnutím k výsledkům určitého hodnocení, se do prvků s proměnnými vkládají další údaje, které se blíží těm přijatým.
V každé situaci byste měli nejprve prozkoumat model, zda neexistuje konflikt omezení. Často je to spojeno s nesprávným výběrem poměru nebo limitního ukazatele.
Ve výše uvedeném příkladu je ukazatel maximálního objemu 16 metrů krychlových. m místo 32, protože takové omezení je v rozporu s ukazateli pro minimální počet míst, protože bude odpovídat počtu 16,5 metrů krychlových. m
Proč investovat do čističky vzduchu?
Na základě toho možnost „Hledat řešení“ v Excelu pomůže při řešení konkrétních problémů, které je poměrně obtížné nebo nemožné vyřešit obvyklými způsoby. Potíž při použití této metody spočívá v tom, že zpočátku je tato možnost skrytá, a proto si většina uživatelů neuvědomuje její přítomnost. Kromě toho je funkce poměrně náročná na naučení a používání, ale při správném výzkumu přinese velké výhody a usnadní výpočty.