Formulace problému

Předpokládejme, že společnost, kde pracujete, má dva sklady, odkud se zboží dodává do vašich pěti obchodů roztroušených po celé Moskvě.

Každý obchod je schopen prodat určité množství nám známého zboží. Každý sklad má omezenou kapacitu. Úkolem je racionálně vybrat, ze kterého skladu do kterých prodejen je potřeba zboží dodat, aby se minimalizovaly celkové náklady na dopravu.

Před zahájením optimalizace budete muset vytvořit jednoduchou tabulku na list Excelu– náš matematický model popisující situaci:

Rozumí se, že:

• Šedá tabulka (B3:G5) popisuje jednotkové přepravní náklady z každého skladu do každého obchodu.
• Fialové buňky (C14:G14) popisují množství zboží potřebné k prodeji pro každý obchod.
• Červené buňky (J10:J11) zobrazují kapacitu každého skladu – maximální množství zboží, které sklad pojme.
• Žluté (C12:G12) a modré (H10:H11) buňky jsou součty řádků a sloupců pro zelené buňky.
• Celkové náklady na dodání (E17) se vypočítávají jako součet součinů počtu zboží a jim odpovídajících nákladů na dodání.

Naším úkolem tedy je vybrat optimální hodnoty zelených buněk. Navíc, aby celková částka za řádek (modré buňky) nepřesáhla kapacitu skladu (červené buňky) a zároveň každá prodejna dostala množství zboží, které potřebuje k prodeji (částka pro každou prodejnu ve žlutém buňky by měly být co nejblíže požadavkům - fialové buňky).

Řešení

V matematice byly podobné problémy volby optimální alokace zdrojů formulovány a popsány již dávno. A samozřejmě způsoby, jak je řešit, byly již dlouho vyvinuty. Excel dává uživateli jeden z nich – s pomocí výkonného doplňku Hledání řešení(řešitel), dostupné v Excelu 2003 prostřednictvím nabídky Servis(Nástroje) nebo ze záložky Data v nových verzích Excelu.

Pokud je v nabídce Servis nebo na kartě Data váš Excel takový příkaz nemá – to je v pořádku – to znamená, že doplněk ještě není připojen. Chcete-li jej připojit:

• v Excelu 2003 a starších - otevřete nabídku Služba – doplňky(Nástroje – Přidat- Ins) , v okně, které se zobrazí, zaškrtněte políčko Hledání řešení(Řešitel) a stiskněte OK. Excel aktivuje vybraný doplněk a v nabídce Služba (Nástroje) objeví se nový tým - Hledání řešení (Řešitel) .
• v Excelu 2007 a novějších - klikněte na tlačítko Kancelář a poté vyberte MožnostiVynikat – Doplňky – Jít(Možnosti aplikace Excel – Doplňky – Přejít na) .

Pojďme spustit doplněk. Otevře se následující okno:

V tomto okně musíte nastavit následující nastavení:

Kromě zřejmých omezení spojených s fyzickými faktory (kapacita skladů a dopravních prostředků, rozpočet a časové limity atd.) je někdy nutné přidat omezení „speciálně pro Excel“. V našem případě například budeme muset přidat následující omezení:

Dodatečně se upřesní, že objem přepravovaného zboží (zelené buňky) nemůže být záporný - pro člověka je to samozřejmé, ale pro počítač to musí být výslovně zapsáno.

Po nastavení všech potřebných parametrů by okno mělo vypadat takto:

Nyní, když jsou data výpočtu zadána, stiskněte tlačítko Vykonat(Řešit) pro zahájení optimalizace. V závažných případech s velkým počtem měnících se buněk a omezení může nalezení řešení trvat dlouho, ale náš úkol pro Excel nebude problém - během několika okamžiků získáme následující výsledky:

Všimněte si, jak zajímavě byly objemy dodávek rozloženy po prodejnách, aniž by došlo k překročení kapacity našich skladů a uspokojení všech požadavků na požadované množství zboží pro každou prodejnu.

Pokud nám nalezené řešení vyhovuje, můžeme jej uložit, nebo se vrátit k původním hodnotám a zkusit to znovu s jinými parametry. Vybranou kombinaci parametrů můžete také uložit jako Scénář. Na žádost uživatele může Excel sestavit tři typy Zprávy k řešenému problému na samostatných listech: zpráva o výsledcích, zpráva o matematické stabilitě řešení a zpráva o limitech (omezeních) řešení, ve většině případů však zajímají pouze specialisty .

Existují však situace, kdy Excel nemůže najít vhodné řešení. Takový případ můžete nasimulovat, pokud v našem příkladu zadáte požadavky na prodejnu v množství větším, než je celková kapacita skladů. Poté se Excel při optimalizaci pokusí přiblížit řešení co nejblíže a poté zobrazí zprávu, že nemůže najít řešení. Nicméně i v tomto případě máme hodně užitečné informace– zejména můžeme vidět „slabá místa“ našich obchodních procesů a porozumět oblastem pro zlepšení.

Uvažovaný příklad je samozřejmě relativně jednoduchý, ale lze jej snadno škálovat pro řešení mnohem složitějších nelineárních problémů. Například:

• Optimalizace finanční alokace podle výdajových položek v podnikatelském plánu nebo rozpočtu projektu. Omezením v tomto případě bude množství finančních prostředků a načasování projektu a cílem optimalizace bude maximalizace zisku a minimalizace nákladů na projekt.
• Optimalizace rozvrhů zaměstnanců aby se fond minimalizoval mzdy podniky. Omezení v tomto případě bude na přání každého zaměstnance, pokud jde o rozvrh práce a požadavky personálního stolu.
• Optimalizace investic – potřeba moudře rozdělovat finanční prostředky mezi několik bank, cenné papíry nebo akcie podniků s cílem opět maximalizovat zisk nebo (je-li to důležitější) minimalizovat rizika.

Každopádně addon Hledání řešení(řešitel) je velmi výkonný a krásný nástroj Excel a zaslouží si vaši pozornost, protože může pomoci v mnoha obtížných situacích, kterým člověk musí čelit v moderním podnikání.

Svět se mění závratnou rychlostí, ať se nám to líbí nebo ne. Tato pravda je zvláště dobře známá uživatelům PC. Po všem software změny a aktualizace s neuvěřitelnou frekvencí. Kancelářské balíky tím naštěstí moc netrpí, ale najdou se i výjimky. Proč jsou balíčky důležité? kancelářské programy? Žádný kancelářská aplikace je pohodlný nástroj určený pro práci s databázemi. Počet pomocných prvků v tomto systému je stále větší.

S pomocí vizualizačních nástrojů, které se objevily pouze v nových verzích takových programů, je mnohem jednodušší pracovat. Díky novému vyhledávacímu filtru se práce výrazně zrychlila. A samotný Microsoft Excel 2010 pracuje rychleji. Zdálo by se ale, že ještě nedávno zaměstnanci kanceláře ovládali složitosti práce s Office 2007. Ale najednou tu byla prezentace Office 2010, která nešťastným uživatelům jen přidala další potíže. Příkladem je „hledání řešení“ v aplikaci Microsoft Excel 2010.

Tento doplněk je nejen užitečný, ale také vám umožní mnohem produktivnější práci s tabulkovým editorem, a tím vám umožní rozhodnout se velké množství složité úkoly. Je to relevantní zejména z pohledu optimalizace, která je dnes pro mnoho firem aktuální. Ale proč Microsoft Excel 2010? Pokud v této verzi mluvíme konkrétně o Excelu, došlo v něm k významným změnám. Opraveno bylo například velké množství chyb ve vzorcích, kvůli kterým se v předchozích verzích programu poměrně často vyskytovaly chyby ve výpočtech. Ale sebemenší špatný odhad může někdy vést k docela nepříjemným následkům.

Díky použití pásového rozhraní, které umožňuje zobrazení předběžná forma grafy a schémata před vložením do tabulky, je pro uživatele jednodušší připravit složité odborné dokumenty. Také v ceně nová verze Editor zahrnul nové typy vzorců, které mohou být velmi užitečné pro ekonomy a účetní. Tato okolnost zdůrazňuje zaměření Microsoftu na firemní uživatele. Vzhledem k tomu, že všechny níže popsané situace jsou pro ně typické, pak na tom není nic překvapivého.

Pokud jste nepoužili doplněk „hledání řešení“, můžete jej nainstalovat samostatně. Jak to nainstalovat? To se provádí docela snadno. Pokud používáte tabulkový editor Excel 2003 nebo starší, k provedení této akce musíte přejít na položku „Nástroje“ a vybrat tam „Doplňky“. A kde hledat „hledání řešení“, pokud se bavíme o modernější verzi? Pokud používáte Excel 2007, můžete najít tlačítko „hledat řešení“ v záložce „Data“. Jak s tím pracovat? Možná se vám všechna tato vysvětlení mohou zdát trochu zdlouhavá, ale tento doplněk funguje celkem logicky. Abyste to zvládli, nemusíte být počítačový génius. Abychom plně porozuměli principu jeho použití, uvažujme jednoduchý příklad.

Jak funguje „hledání řešení“ v Excelu 2010?

Příklad: máte za úkol rozdělovat bonusy v organizaci. Pro zjednodušení řešení předpokládejme, že je potřeba rozdělit bonus mezi všechny zaměstnance pobočky. Prémiový rozpočet - 100 000 rublů. Bonus lze rozdělit v poměru ke mzdě každého zaměstnance. Kde začít? Nejprve je nutné vytvořit tabulku, zadat do ní všechny potřebné informace a vzorové výrazy. Za výsledek bude považována celková částka pojistného. Stojí za zvážení, že cílová buňka (například C8) je spojena s sekcí, která bude změněna (například E2).

V rozsahu C2-C7 mohou být další vzorce, pomocí kterých můžete vypočítat výši bonusu pro každého zaměstnance. Poté musíte spustit doplněk „hledat řešení“. Poté se v okně, které se otevře, nastaví požadované hodnoty. Zvláštní pozornost by měla být věnována skutečnosti, že vzhled okna se mohou velmi lišit různé verze kancelářský oblek. Takže v této situaci budete muset přijít na to sami. Zásadní rozdíly tu ale nejsou, takže studium nezabere moc času.

Jaké možnosti jsou v dialogovém okně?

Abyste si usnadnili práci, měli byste vědět o hodnotách, které obecně existují v konkrétním provozním rozsahu. Za prvé, cílová buňka. Upozorňujeme, že na rozdíl od jiných operací, které mohou používat více polí pro zadávání dat, zde může být pouze jedno. Kromě toho stojí za zvážení, že může existovat několik možností optimalizace. Zvláštní pozornost by měla být věnována minimální a maximální možné celkové hodnotě. Pozor také na konkrétní výsledek. Pokud potřebuješ poslední možnost, pak musí být ve vstupním poli přesně specifikován preferovaný výsledek. Je třeba také vzít v úvahu, že buď jednotlivá pole nebo rozsah mohou fungovat jako vyměnitelné buňky. Právě pro rozsah získá program konečnou hodnotu porovnáním s původními daty.

Jak se přidávají omezení?

Pokud potřebujete do programu přidat nějaká omezení, musíte použít tlačítko „Přidat“. Je důležité vzít v úvahu následující bod: při nastavování takových hodnot musíte být velmi opatrní. Vzhledem k tomu, že doplněk „hledání řešení“ v aplikaci Excel se používá v poměrně důležitých operacích, je důležité získat co nejpřesnější hodnoty. Samotné výsledky budou záviset na omezeních. Omezení můžete nastavit jak pro jednotlivé buňky, tak pro celé rozsahy.

Jaké varianty vzorců a symbolů lze v tomto případě použít? Lze použít následující znaky: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

Kde tuto možnost najdete ve starších verzích programu? Pokud používáte Excel 2007 a starší, můžete tuto možnost otevřít kliknutím na tlačítko „Možnosti“. Zde můžete vidět položku „Možnosti hledání řešení“.

Hledejte hotový výsledek

Chcete-li vyhledat hotové řešení, musíte kliknout na tlačítko „Spustit“. V důsledku toho se zobrazí dialogové okno Výsledky hledání řešení. Pokud jste s konečnou odpovědí spokojeni, stačí kliknout na tlačítko „OK“. V důsledku toho bude odpověď, která se vám líbí, zaznamenána do tabulky. V případě, že výsledná hodnota nesouhlasí s vaším názorem, musíte kliknout na tlačítko „Zrušit“. Tabulka se nakonec vrátí na původní hodnotu, můžete pokračovat v hledání optimálního řešení. Pokud jste změnili zdrojová data, bude nutné toto rozhodnutí provést znovu.

Kde lze v Excelu použít doplněk „hledání řešení“?

Zvažte další příklad – minimalizaci nákladů. Jak bylo uvedeno výše, tuto funkci lze využít k optimalizaci výrobních procesů. Pojďme se podívat, jak můžeme snížit náklady společnosti zabývající se nízkopodlažní výstavbou. Předpokládejme, že máme samotnou organizaci a tři dodavatele, kteří dodávají stavební materiály. Náklady na výstavbu budou zahrnuty do nákladů na zařízení, takže je v zájmu společnosti vybrat dodavatele, jehož práce bude stát méně.

Jaké informace je potřeba zadat do „hledání řešení“ v MS Excel? Je nutné uvést náklady na stavební materiál, jeho potřebu na staveništi a náklady na dopravu stavebního materiálu. Je třeba vzít v úvahu každý pár „dodavatel-kupující“. Cílová buňka by měla uvádět součet všech nákladů na dopravu. Pokud je vše provedeno správně, funkce „hledání řešení“ umožní vytvořit nejziskovější strategii, která přinese nejvyšší možný příjem.

Solution Finder není standardním doplňkem v Excelu 2007. Je nezbytný pro složité výpočty, kde je více neznámých. Není tedy součástí běžné sady parametrů programu. Pokud však existuje potřeba, doplněk nabízí uživateli efektivní provoz a vysokou produktivitu.

Co je to „hledání řešení“?

Toto je doplněk programu. Tento balíček není poskytován ve standardní konfiguraci vyráběné výrobcem. Je nutné jej stáhnout a nakonfigurovat samostatně. Uživatelé se bez něj často obejdou. Kromě toho je doplněk často nazýván „Řešitel“, protože provádí přesné a rychlé výpočty bez ohledu na to, jak složitý je problém. V případě původní verze Microsoft Office problémy s instalací nevznikají. Uživatel musí provést následující přechody: Možnosti – Nástroje – Doplňky – Správa – Doplňky Excelu. V důsledku toho se na obrazovce objeví okno s přechodovým tlačítkem. Pokud na něj kliknete, zobrazí se seznam všech doplňků, které jsou uživateli nabízeny, nainstalovaných i nenárokovaných. Dále musíte najít „Hledat řešení“ a zaškrtnout políčko vedle něj. Nástroj se stane aktivním a lze jej použít, kdykoli budete chtít.

Proč je Řešitel potřeba? Proč se v Excelu 2007 používá „Solution Finder“ a proč je nutné jej instalovat? Pokud má uživatel cílovou funkci, která závisí na několika parametrech, doplněk vybere řešení problému odpovídající zdrojovým datům. Mohou to být proměnná, neznámá nebo, řekněme, konečná hodnota. Jinými slovy, uživatel bude mít počáteční vlastnosti i odezvu. Pokud jde o samotný program, vybere průběh řešení a poskytne vzorec. Stojí za zmínku, že pomocí doplňku je možné najít následující:

Úspěšné rozdělení pracovních zdrojů k dosažení maximálního zisku při provozu společnosti nebo jejího jednotlivého oddělení nebo pobočky;
distribuce investic za podmínek minimalizace rizik;
řešení problémů, kde je více než jedna neznámá;
uložení a načtení modelu řešení je nejlepší možností, kterou využívají zaměstnanci, kteří jsou nuceni neustále měnit svůj počítač nebo notebook;
současné řešení několika úloh s různými proměnnými, neznámými, formulemi a integrály.

Program tedy otevírá široké možnosti, ale musíte se naučit, jak jej správně používat.

Jak Řešitel funguje? Kromě Řešitele má Excel funkci nazvanou výběr parametrů. Je nutné použít, když existuje pouze jedna neznámá hodnota. Tato příležitost vyžaduje méně zdrojů, takže výsledek je dodán rychleji. Hledání řešení v Excelu 2007 se používá pro problémy s největší složitostí, ve kterých existuje několik neznámých a často jsou pozorovány proměnné. Vše tedy může být znázorněno takto: Najít neznámé - několik „x“. Za předpokladu, že se jedná o vzorec nebo funkci. Omezení v tomto případě obvykle značí nerovnost nebo minimální/maximální hodnoty. Kromě toho byste měli označit buňky, se kterými chcete provádět výpočty. Je možné řešit několik různých problémů najednou, za předpokladu, že program dostane odpovídající parametry.

Nastavení parametrů „Solution Search“ Aby funkce „Solution Search“ fungovala správně v Excelu 2007, musíte zadat správné parametry. Zpravidla jsou omezeny na několik (1-3) charakteristik, ale u složitějších úkolů je potřeba globální nastavení. Možnosti v „Solution Search“ aplikace Office Excel 2007 jsou následující:

1. Maximální čas – počet sekund, které uživatel přidělí programu k vyřešení, závisí na úrovni složitosti problému.
2. Maximální počet integrací. V tomto případě mluvíme o počtu tahů provedených programem při řešení problému. Když se parametr zvýší, odpověď nepřijde.
3. Chyba nebo přesnost, často používaná při řešení desetinných zlomků (například do 0,0001).
4. Přípustná odchylka. Používá se v procesu práce se zájmem.
5. Nezáporné hodnoty. Používá se při řešení funkce, která má dvě správné odpovědi (například +/-X).
6. Zobrazte výsledky integrace. Toto nastavení je k dispozici, když je důležitý výsledek rozhodnutí i jejich průběh.
7. Metoda vyhledávání – výběr optimalizačního algoritmu. Obvykle se používá Newtonova metoda. Když jsou všechna nastavení vybrána, musíte kliknout na tlačítko Uložit.

Parametry problému ve funkci „Vyhledávání řešení“.

Tento doplněk funguje podle specifikovaných charakteristik výpočtu. Nejdůležitější z nich je metoda. Jsou dvě možnosti. Výchozí nastavení je Newtonova metoda. Pracuje s větší pamětí, ale méně integrací. Je tedy vhodný pro standardní a jednoduché rovnice. Navíc existuje „metoda konjugovaného gradientu“. V tomto případě je požadováno méně paměti, ale je zapotřebí více integrací. Pokud jej tedy použijete, je možné řešit i ty nejsložitější rovnice a aplikovat rozsáhlé vzorce a funkce.

Vzorec v Excelu

Za zmínku stojí povinný prvek, bez kterého nemůže fungovat doplněk „Solution Search“ v Excelu 2007. V tomto případě mluvíme o vzorcích. Jsou výrazem, který provádí konkrétní výpočet. Bez rovnosti nemohou vzorce fungovat. Program jej tedy není schopen rozpoznat, pokud neexistuje odpovídající znak. Vzorec se skládá z následujících složek:

1. Funkce. Standardní vzorec, ve kterém existuje určitá a konkrétní posloupnost akcí, nelze změnit.
2. Odkaz. Označuje počet buněk, které mají být vyřešeny. Buňky mohou být umístěny náhodně nebo v určitém pořadí.
3. Operátor. Je to symbol, který určuje typ výpočtu (+ – sčítání, * – násobení atd.).
4. Konstantní. Je konstantní hodnota, která zůstává vždy stejná. K jeho získání nejsou potřeba žádné výpočty.

Vzorce se řeší zleva doprava, je důležité dodržovat všechna matematická pravidla.

Vytvoření vzorce

Vzorce jsou rovnice, které jsou nezbytné k provádění výpočtů programu. Pokud nejsou zadány, nebude funkce Najít řešení v Excelu fungovat. Problémy se také nevyřeší. Aby tedy fungoval správně, musíte vzorec zadat správně. Výpočet začíná rovností. Pokud buňka obsahuje „=ROOT(číslo buňky)“, použije se odpovídající funkce. Když napíšete hlavní vzorec se znaménkem „=“, musíte uvést data, se kterými interaguje. Chcete-li najít požadované informace, měli byste použít funkci vyhledávání.

Solution Finder je doplněk aplikace Microsoft Excel, který vám pomůže najít optimální řešení problému s ohledem na uživatelsky specifikovaná omezení.

Zvážíme nalezení řešení v (tento doplněk doznal některých změn oproti předchozí verzi v .
V tomto článku se podíváme na:

• vytvoření optimalizačního modelu na listu MS EXCEL
• nastavení Hledání řešení;
• jednoduchý příklad (lineární model).

Instalace Hledejte řešení

tým Hledání řešení je ve skupině Analýza na kartě Data.

Pokud tým Hledání řešení ve skupině Analýza není k dispozici, musíte povolit doplněk se stejným názvem.
Pro tohle:

• Na kartě Soubor vybrat tým Možnosti a poté kategorii Doplňky;
• V terénu Řízení vyberte hodnotu Doplňky aplikace Excel a stiskněte tlačítko Jít;
• V terénu Dostupné doplňky zaškrtněte políčko vedle položky Hledání řešení a klepněte na OK.

Poznámka. Okno Doplňky k dispozici také na kartě Vývojář. Jak povolit tuto kartu.

Po stisknutí tlačítka Hledání řešení ve skupině Analýza, otevře se jeho dialogové okno .

Při častém používání Hledání řešení Je pohodlnější spustit jej z panelu nástrojů Rychlý přístup než ze záložky Data. Chcete-li umístit tlačítko na Panel, klikněte na něj pravým tlačítkem a vyberte Přidat na panel nástrojů Rychlý přístup.

O modelech

Tato část je určena těm, kteří se s konceptem optimalizačního modelu teprve seznamují.

Rada. Před použitím Hledání řešení Důrazně doporučujeme prostudovat literaturu o řešení optimalizačních problémů a sestavování modelů.

Níže je malý vzdělávací program na toto téma.

Nástavba Hledání řešení pomáhá určit Nejlepší způsob dělat něco:

• „Něco“ může zahrnovat alokaci peněz na investice, nakládku skladu, rozvoz zboží nebo jinou předmětnou činnost, kde je potřeba najít optimální řešení.
• „Nejlepší způsob“ neboli optimální řešení v tomto případě znamená: maximalizace zisku, minimalizace nákladů, dosažení nejlepší kvality atd.

Zde je několik typických příkladů optimalizačních problémů:

• Určete, kdy je maximální příjem z prodeje vyrobených výrobků;
• Určete, při kterých by byly celkové náklady na dopravu minimální;
• Zjistěte, že celkové náklady na výrobu by byly minimální;
• Určete minimální termín dokončení všech projektových prací (kritická cesta).

Pro formalizaci daného úkolu je nutné vytvořit model, který by odrážel základní charakteristiky předmětné oblasti (a nezahrnoval by drobné detaily). Je třeba poznamenat, že model je optimalizován Hledání řešení pouze jedním ukazatelem(tento optimalizovaný indikátor se nazývá cílová funkce).
V MS EXCEL je model souborem propojených vzorců, které používají proměnné jako argumenty. Tyto proměnné mohou obvykle přijímat pouze platné hodnoty, s výhradou omezení zadaných uživatelem.
Hledání řešení vybere takové hodnoty těchto proměnných (s výhradou specifikovaných omezení), aby účelová funkce byla maximální (minimální) nebo rovna dané číselné hodnotě.

Poznámka. V nejjednodušším případě lze model popsat pomocí jediného vzorce. Některé z těchto modelů lze pomocí tohoto nástroje optimalizovat. Před prvním setkáním Hledání řešení Má smysl nejprve podrobně porozumět souvisejícímu nástroji.
Hlavní rozdíly Výběr parametrů z Hledání řešení:

• Výběr parametrů funguje pouze s modely s jednou proměnnou;
• je nemožné nastavit omezení na proměnné;
• neurčuje se maximum nebo minimum účelové funkce, ale její rovnost určité hodnotě;
• funguje efektivně pouze v případě lineárních modelů, v nelineárním případě najde lokální optimum (nejblíže původní hodnotě proměnné).

Příprava optimalizačního modelu v MS EXCEL

Hledání řešení optimalizuje hodnotu účelové funkce. Objektivní funkce je vzorec, který vrací jednu hodnotu v buňce. Výsledek vzorce by měl záviset na proměnných modelu (ne nutně přímo, ale prostřednictvím výsledku výpočtu jiných vzorců).
Omezení modelu lze uložit jak na rozsah variací samotných proměnných, tak na výsledky výpočtu jiných vzorců modelu, které na těchto proměnných závisí.
Všechny buňky obsahující proměnné modelu a omezení musí být umístěny pouze na jednom listu sešitu. Zadávání parametrů v dialogovém okně Hledání řešení možné pouze z tohoto listu.
Na tomto listu musí být umístěna i cílová funkce (buňka). Mezivýpočty (vzorce) však lze umístit na jiné listy.

Rada. Uspořádejte data modelu tak, aby na jednom listu MS EXCEL byl pouze jeden model. V opačném případě budete muset pro provádění výpočtů neustále ukládat a načítat nastavení Hledání řešení(viz. níže).

Pojďme si představit algoritmus pro práci s Hledání řešení, kterou doporučují samotní vývojáři (www.solver.com):

• Definujte buňky s proměnnými modelu (rozhodovací proměnné);
• Vytvořte v buňce vzorec, který vypočítá účelovou funkci vašeho modelu;
• Vytvořte vzorce v buňkách, které budou počítat hodnoty ve srovnání s omezeními (levá strana výrazu);
• Pomocí dialogového okna Hledání řešení zadejte odkazy na buňky obsahující proměnné, na cílovou funkci, na vzorce pro omezení a hodnoty samotných omezení;
• Běh Hledání řešení najít optimální řešení.

Pojďme si všechny tyto kroky projít na jednoduchém příkladu.

Jednoduchý příklad použití Hledání řešení

Kontejner je nutné naložit zbožím tak, aby hmotnost kontejneru byla maximální. Kontejner má objem 32 metrů krychlových. Položky jsou uloženy v krabicích a bednách. Každá krabice zboží váží 20 kg, její objem je 0,15 m3. Krabice - 80 kg a 0,5 m3, resp. Je nutné, aby celkový počet nádob byl minimálně 110 kusů.

Tyto modely organizujeme následovně (viz ukázkový soubor).

Proměnné modelu (množství každého typu kontejneru) jsou zvýrazněny zeleně.
Cílová funkce (celková hmotnost všech krabic a beden) je červeně.
Omezení modelu: minimální množství nádob (>=110) a celkový objem (<=32) – синим.
Účelová funkce se vypočítá pomocí vzorce =SUMPRODUCT(B8:C8,B6:C6) je celková hmotnost všech krabic a přepravek naložených do kontejneru.
Podobně vypočítáme celkový objem - =SUMPRODUCT(B7:C7,B8:C8). Tento vzorec je potřeba k nastavení limitu celkového objemu krabic a přepravek (<=32).
Pro nastavení omezení modelu také vypočítáme celkový počet kontejnerů =SUM(B8:C8) .
Nyní pomocí dialogového okna Hledání řešení Zadáme odkazy na buňky obsahující proměnné, účelovou funkci, vzorce pro omezení a hodnoty samotných omezení (nebo odkazy na odpovídající buňky).
Je jasné, že počet krabic a beden musí být celé číslo – to je další omezení modelu.

Po stisknutí tlačítka Najít řešení budou nalezeny takové počty krabic a přepravek, při kterých je jejich celková hmotnost (objektivní funkce) maximální a zároveň jsou splněna všechna stanovená omezení.

souhrn

Ve skutečnosti je hlavním problémem při řešení optimalizačních problémů pomocí Hledání řešení Nezáleží na jemnosti nastavení tohoto analytického nástroje, ale na správnosti sestavení modelu adekvátního danému úkolu. Proto se v dalších článcích zaměříme konkrétně na stavbu modelů, protože „křivý“ model je často důvodem neschopnosti najít řešení pomocí Hledání řešení.
Často je snazší prozkoumat několik typických problémů, najít mezi nimi podobný a poté tento model přizpůsobit svému úkolu.
Řešení klasických optimalizačních úloh pomocí Hledání řešení považováno .

Řešitel nemohl najít proveditelné řešení

Tato zpráva se zobrazí, když Hledání řešení nemohl najít kombinace proměnných hodnot, které současně splňují všechna omezení.
Pokud používáte Simplexní metoda řešení lineárních úloh, pak si můžete být jisti, že žádné řešení opravdu neexistuje.
Pokud používáte metodu pro řešení nelineárních problémů, která vždy začíná počátečními hodnotami proměnných, pak to může také znamenat, že proveditelné řešení je daleko od těchto počátečních hodnot. Pokud běžíš Hledání řešení s jinými počátečními hodnotami proměnných, pak se možná najde řešení.
Představme si, že při řešení problému pomocí nelineární metody byly buňky s proměnnými ponechány prázdné (tj. počáteční hodnoty jsou 0) a Hledání řešení nenašel řešení. To neznamená, že skutečně neexistuje řešení (i když to tak může být). Nyní, na základě výsledků určitého odborného posouzení, zadáme do buněk s proměnnými další sadu hodnot, která se dle vašeho názoru blíží té optimální (hledané). V tomto případě, Hledání řešení dokáže najít řešení (pokud nějaké skutečně existuje).

Poznámka. O vlivu nelinearity modelu na výsledky výpočtů si můžete přečíst v poslední části článku.

V každém případě (lineární nebo nelineární) musíte nejprve analyzovat model na konzistenci omezení, tedy podmínek, které nelze splnit současně. Nejčastěji je to kvůli špatné volbě poměru (např.<= вместо >=) nebo mezní hodnota.
Pokud je např. ve výše diskutovaném příkladu nastavena hodnota maximálního objemu na 16 m3 místo 32 m3, pak bude toto omezení v rozporu s omezením minimálního počtu míst (110), protože minimálnímu počtu míst odpovídá objem rovný 16,5 m3 (110 * 0,15, kde 0,15 je objem boxu, tedy nejmenší nádoby). Nastavením limitu maximálního objemu na 16 m3, Hledání řešení nenajde řešení.

S limitem 17 m3 Hledání řešení najde řešení.

Některá nastavení Hledání řešení

Metoda řešení
Výše diskutovaný model je lineární, tzn. účelová funkce (M je celková hmotnost, která může být maximální) je vyjádřena následující rovnicí M=a1*x1+a2*x2, kde x1 a x2 jsou modelové proměnné (počet krabic a zásuvek), a1 a a2 jsou jejich váhy. V lineárním modelu musí být omezení také lineárními funkcemi proměnných. V našem případě je objemové omezení V=b1*x1+b2*x2 vyjádřeno také lineární závislostí. Další omezení – Maximální počet kontejnerů (n) – je samozřejmě také lineární x1+x2 Lineární úlohy se obvykle řeší metodou Simplex. Výběrem této metody řešení v okně Hledání řešení Můžete také zkontrolovat linearitu samotného modelu. V případě nelineárního modelu obdržíte následující zprávu:

V tomto případě je nutné zvolit metodu řešení nelineární úlohy. Příklady nelineárních závislostí: V=b1*x1*x1; V=bl*xl^0,9; V=b1*x1*x2, kde x je proměnná a V je účelová funkce.

Tlačítka Přidat, Upravit, Smazat
Tato tlačítka umožňují přidávat, upravovat a odstraňovat omezení modelu.

Tlačítko reset
Chcete-li odstranit všechna nastavení Hledání řešení klikněte na tlačítko Resetovat– dialogové okno se vymaže.

Tato možnost je vhodná při použití různých možností omezení. Při ukládání parametrů modelu (tlačítko Načíst/Uložit, potom klikněte na tlačítko Uložit) navrhuje se vybrat horní buňku rozsahu (sloupec), ve kterém bude umístěn: odkaz na účelovou funkci, odkazy na buňky s proměnnými, omezení a parametry metod řešení (dostupné přes tlačítko Možnosti). Před uložením se ujistěte, že tato řada neobsahuje údaje o modelu.
Pro načtení uložených parametrů nejprve stiskněte tlačítko Načíst/Uložit a poté v zobrazeném dialogovém okně tlačítko Stažení a poté zadejte rozsah buněk obsahujících dříve uložená nastavení (nelze zadat pouze horní buňku). Klepněte na tlačítko OK. Potvrďte resetování aktuálních hodnot parametrů úlohy a jejich nahrazení novými.

Přesnost
Při vytváření modelu má výzkumník zpočátku určitý odhad rozsahů variací cílové funkce a proměnných. S přihlédnutím k výpočtům v MS EXCEL se doporučuje, aby tyto variační rozsahy byly výrazně vyšší než přesnost výpočtu (obvykle se nastavuje od 0,001 do 0,000001). Data v modelu jsou zpravidla normalizována tak, aby variační rozsahy účelové funkce a proměnných byly v rozmezí 0,1 - 100 000. Vše samozřejmě závisí na konkrétním modelu, ale pokud se vaše proměnné změní o více než 5-6 řádů, pak byste možná měli model „zdrsnit“, například pomocí logaritmické operace.

Doplněk Solver Excel je analytický nástroj, který nám umožňuje rychle a snadno určit, kdy a jakého výsledku za určitých podmínek dosáhneme. Možnosti nástroje pro vyhledávání řešení jsou mnohem vyšší, než jaké může poskytnout „výběr parametrů“ v Excelu.

Hlavní rozdíly mezi nalezením řešení a výběrem parametru:

1. Výběr několika parametrů v Excelu.
2. Vynucování podmínek pro omezení změn v buňkách, které obsahují proměnné hodnoty.
3. Možnost použití v případech, kdy může být mnoho řešení jednoho problému.

Příklady a problémy k nalezení řešení v Excelu

Podívejme se na analytické schopnosti doplňku. Například potřebujete ušetřit 14 000 $ za 10 let. Po dobu 10 let chcete každý rok vložit 1 000 $ na bankovní vkladový účet s 5 % ročně. Na obrázku níže je tabulka v Excelu, která přehledně zobrazuje stav nashromážděných prostředků za jednotlivé roky. Jak je vidět, za takových podmínek vkladového účtu a spořících příspěvků nebude cíle dosaženo ani po 10 letech. Při řešení tohoto problému můžete postupovat dvěma způsoby:

1. Najděte si banku, která nabízí vyšší úročení vkladů.
2. Zvyšte výši ročních příspěvků na spoření na svůj bankovní účet.

Hodnoty proměnných v buňkách B1 a B2 můžeme změnit tak, abychom zvolili nezbytné podmínky pro akumulaci požadovaného množství peněz.

Doplněk „Solution Search“ nám umožňuje současně používat 2 z těchto možností k rychlé simulaci nejoptimálnějších podmínek pro dosažení cíle. Pro tohle:

Jak vidíte, program mírně zvýšil úrokovou sazbu a výši ročních příspěvků.



Omezující parametry při hledání řešení

Řekněme, že jdete do banky s touto tabulkou, ale banka vám odmítne zvýšit úrokovou sazbu. V takových případech musíme zjistit, o kolik budeme muset navýšit roční investice. Musíme nastavit omezení buňky s jednou hodnotou proměnné. Než ale začnete, změňte hodnoty v buňkách proměnných na původní: v B1 o 5% a v B2 o -1000 $. Nyní udělejme následující.