Řešení optimalizačních úloh v Excelu
Nejen tabulky a grafy
Matematické modelování ekonomických jevů má dlouhodobou historii, jejíž počátky spadají už do sedmnáctého a osmnáctého století. V průběhu druhé světové války byly matematické modely využívány hlavně pro válečné účely. Odtud je také odvozen původní název tohoto vědního odvětví (Operation Research, v českém překladu operační výzkum).

Jednou z nejstarších disciplín operačního výzkumu je lineární programování. Vyplývá to z poměrně jednoduchého matematického aparátu, který je používán k řešení matematických modelů úloh lineárního programování. Tyto modely jsou optimalizační, tj. ze všech možných řešení problému hledají řešení podle určitého kritéria nejlepší (optimální). 
Formulaci optimalizačního modelu lze rozdělit do dvou fází. V první fázi formulace tvoříme ekonomický model, ve kterém řešený problém po důkladné analýze definujeme a popisujeme ekonomickými termíny. Formulace ekonomického modelu vyžaduje důkladnou znalost modelované skutečnosti a je většinou záležitostí zkušených odborníků z praxe. 
Ve druhé fázi formulujeme na základě ekonomického modelu matematický model, který pak řešíme pomocí Excelu.
Je velice efektivní, když všechny tyto fáze zvládne jedna osoba (např. manažer). Celý proces se tím podstatně zrychlí, protože odpadá komunikace mezi odborníkem z praxe a matematikem. Manažer, který nejlépe zná problém, si sám sestaví ekonomický a matematický model. Matematický model pak vyřeší jednoduše pomocí Excelu a získané výsledky použije pro optimální rozhodování.

Model lineární úlohy
Lineární úlohou rozumíme optimalizační problém hledání extrému (maxima nebo minima) lineární funkce na množině nezáporných řešení soustavy lineárních rovnic nebo nerovnic.

Tedy při zavedení označení pro daná reálná čísla

aij … strukturní koeficient
cj…. cena jednotky j-té proměnné
xj…. strukturní proměnná vyjadřující úroveň j-té proměnné
bi…. pravá strana i-tého omezení	           
Ri  . . . relační operátor (jeden ze symbolů , , )        (1   i   m, 1   j   n)

je možné danou úlohu formulovat takto:  na množině řešení lineární soustavy
 
	a11 + a12 + … +  a1nxn       R1      b1 
	a21 + a22 + … +  a1nxn        R2      b2 					
.	. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .						(2.1)
am1 + am2 + … +  amnxn      Rm      bm 
	
	                xj                     0                         	(1   j   n)			(2.2)

hledáme extrém lineární funkce

	c1x1  + c2x2 + … + cnxn.							                (2.3)

Jedná-li se o maximum, mluvíme o maximalizační úloze lineárního programování ve smíšeném tvaru, v případě minima o minimalizační úloze. Lineární  funkci  (2.3) nazýváme účelová funkce.
Vektor x = (x1, x2,…, xn )T, který vyhovuje všem omezujícím podmínkám  (2.1)  i (2.2), nazýváme přípustné řešení úlohy lineárního programování. Optimální řešení úlohy je přípustné řešení maximalizující či minimalizující hodnotu účelové funkce (2.3).
Úloha lineárního programování má obvykle jedno optimální řešení. Je však možné nalézt více než jedno optimální řešení, tedy více různých vektorů x(k) =(x1(k), x2(k),…, xn(k) )T, pro něž nabývá účelová funkce stejné hodnoty. V takovém případě existuje teoreticky nekonečně mnoho optimálních řešení. Může však nastat i případ, kdy optimální řešení neexistuje. Například tehdy, hledáme-li extrém neomezené funkce na neomezené množině přípustných řešení ( úloha pak má nekonečně mnoho konečných optimálních řešení) nebo vzhledem k zadání omezujících podmínek je množina přípustných řešení prázdná, tedy nemá-li úloha přípustné řešení, nemá ani optimální. Je proto třeba podstatě úlohy a její formulaci věnovat mimořádnou pozornost.

Formulace optimalizačního modelu
Chceme-li objektivně určit nejvýhodnější řešení rozhodovací situace, je třeba správně analyzovat a popsat danou skutečnost. Předpokladem je samozřejmě mít dostatek potřebných a kvalitních informací o řešeném problému. 
Uvědomme si, že matematický model je určitým zjednodušením modelované skutečnosti. Před jeho formulací proto musíme pečlivě uvážit, které vztahy z modelované skutečnosti do něho zahrneme a které vynecháme. Nepodaří-li se nám vystihnout skutečnou situaci, řešíme vlastně jiný problém. Potom nemusí být získané výsledky (byť správné) použitelné v praxi. Je tedy rozhodující zvážit, která hlediska jsou pro vyřešení našeho úkolu důležitá. Hledání optimálního řešení úlohy lze rozdělit do několika etap:

1. Formulace ekonomického a matematického modelu
2. Výpočet optimálního řešení

Při tvorbě ekonomického modelu analyzujeme obsah úlohy a popisujeme procesy úlohy – činnosti při vhodně zvolené jednotkové úrovni. Dále formulujeme cíl úlohy. Cíl v ekonomickém modelu stanoví kritérium, podle kterého posuzujeme vhodnost nebo nevhodnost jednotlivých činností. Vybereme omezující podmínky, tedy činitele ekonomického modelu. 
Je tedy třeba stanovit:
1.všechny činnosti, které v modelované skutečnosti probíhají a které považujeme za podstatné (např. výroba určitého výrobku, přeprava zboží po určité trase, rozdělení pracovníků na pracoviště apod.);
2.všechny podmínky (tzv. činitele ekonomického modelu), kterými jsou tyto činnosti ovlivňovány:
podmínky na straně vstupu (např. zásoby surovin, množství strojového času, spotřeba energie, počet pracovníků apod.),
podmínky na straně výstupu (např. maximální odbyt výrobků, minimální požadované množství produktu apod.);
3.  cíl, kterého chceme realizací činnosti dosáhnout:
maximalizace nebo minimalizace hodnoty určitého ekonomického ukazatele (např. zisk, náklady, tržba, počet výrobků apod.).

Při tvorbě matematického modelu popíšeme rozhodovací  situaci matematicky, přitom vycházíme z již formulovaného ekonomického modelu. Určíme proměnné, omezení a účelovou funkci:
1.každé činnosti v ekonomickém modelu přiřadíme jednu proměnnou, pro tuto proměnnou i podmínku nezápornosti;
2.každé podmínce, tedy tzv. činiteli ekonomického modelu, přiřadíme nerovnici (s operátory , ), popř. rovnici;
3.formulujeme účelovou funkci, která je matematickým vyjádřením hledaného cíle.

Pro převedení ekonomického modelu do matematického lze využít i tabulky:

Ekonomický model	Matematický model	
činnost	proměnná	
podmínka	omezení	
cíl	účelová funkce	

Postup si ilustrativně ukážeme na jednoduchém příkladě.

Máme navrhnout takový výrobní program, který zabezpečí nejvyšší zisk z výroby a prodeje výrobků A a B při omezeném disponibilním množství suroviny S (24 000 kg), při omezeném využitelném časovém fondu zařízení K (32 000 hodin) a při omezeném počtu pracovníků P (disponibilní časový fond je 12 000 hodin). Požadujeme dále, vzhledem k smluvním závazkům, výrobu alespoň 20 kusů výrobků A.
Norma spotřeby surovin S činí 6 kg na jeden výrobek A, 4 kg  na jeden výrobek B. Na zařízení K je  jeden výrobek A opracováván čtyři hodiny, jeden výrobek B osm hodin. Souhrnná norma času pracovníků P na zhotovení jednoho výrobku A je dvě hodiny, stejně jako na zhotovení jednoho výrobku B. Zisk z  jednoho výrobku A je 8 Kč, z jednoho výrobku B 10 Kč. Předpokládáme, že všechny vyrobené výrobky prodáme.

V ekonomickém modelu nejdříve popíšeme všechny činnosti a vybereme omezující podmínky, které je bezpodmínečně nutné zahrnout do modelu. Při posouzení naší úlohy z věcného hlediska dospějeme k závěru, že jde vlastně o sestavení takového výrobního programu, který při omezených disponibilních množstvích některých činitelů zabezpečí nejvyšší zisk a zajistí produkci určitého počtu výrobků. Výrobní program zahrnuje výrobu výrobků A a B. Máme tedy určit taková množství výrobků A a B, která můžeme z daných činitelů vyrobit, a chceme, abychom dosáhli maximálního zisku.

Poznámka: Dílčí procesy úlohy jsme popisovali pro výrobu a prodej jednoho výrobku A nebo B. Procesy však můžeme popsat i při zcela jiné veličině, než jakou je množství vyráběných výrobků. Množství, při kterém popisujeme procesy v úloze, nazýváme jednotková úroveň procesů.
Zvolená jednotková úroveň se nemusí lišit pouze kvantitativně. Může jít o zcela jinou veličinu, kterou zvolíme za základ popisu procesů – činností. Například v naší úloze bychom mohli zvolit za jednotkovou úroveň dosažení jedné koruny zisku při přeměně činitelů S, K, P na Z.

                 Ekonomický model dané úlohy

Omezení	Činnosti		Úroveň	Jednotky	
	A	B			
Surovina S	6	4	24000	kg	
Časový fond K	4	8	32000	hod	
Počet pracovníků P	2	2	12000	hod	
Zisk	8	10	max	Kč	

Při formulaci matematického modelu vycházíme z již formulovaného ekonomického modelu:
1.každé činnosti v ekonomickém modelu přiřadíme jednu proměnnou, pro tuto proměnnou i podmínku nezápornosti;
2.každé podmínce, tedy tzv. činiteli ekonomického modelu, přiřadíme nerovnici (s operátory , ), popř. rovnici;
3.formulujeme účelovou funkci, která je matematickým vyjádřením hledaného cíle.

Formulujme nyní matematický model úlohy. 
Podle schématu přiřadíme jednotlivým komponentám ekonomického modelu jejich ekvivalenty matematického modelu. 
Dvěma činnostem odpovídá zavedení dvou proměnných:
x1 vyjadřuje počet vyrobených kusů výrobků A, 
x2 vyjadřuje počet vyrobených kusů výrobků B. 
Dále je zapotřebí matematicky vyjádřit omezení, která se týkají disponibility suroviny S, časového fondu zařízení K a počtu pracovníků P:

6x1 + 4x2   24 000
4x1 + 8x2   32 000
2x1 + 2x2   12 000

Uvědomme si však, že hledáme počet výrobků, tedy  pouze nezáporná řešení této soustavy. Je proto třeba omezit i proměnné, které tento počet vyjadřují. Proměnnou  x1 však omezujeme hodnotou 20, neboť je požadováno vyrobit (viz smluvní závazky) minimálně 20 kusů výrobků. Proto

x1  20
x2  0 

Matematický model tedy představuje následující formulace:

max  z = 8x1 + 10x2

na množině řešení soustavy:

6x1 + 4x2   24 000
4x1 + 8x2   32 000
2x1 + 2x2   12 000
                 x1           20
                      x2     0 

Řešení optimalizačního modelu
Naši úlohu vyřešíme v Excelu nástrojem Řešitel.  Do nového listu  přichystáme výchozí údaje podle tabulky ekonomického modelu. Do buněk E2, E3 a E4 zapíšeme vzorce skutečné spotřeby suroviny S, časového fondu zařízení K a časového fondu pracovníků P s odkazem na buňky B6 a C6, do nichž bude spočteno množství vyráběných výrobků A a B. Do buňky E5 přichystáme vzorec pro výpočet dosaženého zisku. (Je analogický jako vzorce pro výpočet spotřeby suroviny a časového fondu.)

Příprava aplikace Řešitele při hledání optimálního výrobního plánu

	A	B	C	D	E		

1		Výrobek A	Výrobek B	Úroveň	Spotřeba		
2	Surovina S	6	4	24000	0	V buňce E2 je vzorec:B2*$B$6+C2*$C$6	
3	Časový fond K	4	8	32000	0	Tento vzorec je zkopírován do E3, E4 a E5	
4	Počet pracovníků P	2	2	12000	0		
5	Zisk	8	10	Max	0		
6	Optimální výroba						

Kurzor umístíme do buňky E5, jejíž hodnotu maximalizujeme. Z menu volíme Nástroje, Řešitel  zobrazí se dialogové okno Parametry řešitele.

                     Dialogové okno Parametry řešitele

V okně už je vyplněn parametr Nastavit buňku, který je nastaven na buňku, v níž jsme měli přichystán kurzor před použitím Řešitele. Vytyčením zadáme Měněné buňky, tj. buňky, do nichž bude spočtena výroba výrobků A (B6) a B (C6). Nyní musíme vytvořit Omezující podmínky. Klepneme na tlačítko Přidat.  Zobrazí se dialogové okno Přidat podmínku.

                                  Dialogové okno Přidat omezující podmínku	

Klepnutím do buňky v sešitu zadáme adresu buňky i podmínku. Klepneme na tlačítko Přidat a postupně takto definujeme podmínky uvedené v dialogovém okně Parametry řešitele. Klepnutím na tlačítko Řešit v dialogovém okně Parametry řešitele zahájíme řešení. Po skončení výpočtu se v sešitu do proměnlivých buněk B6, C6 zapsalo optimální  řešení  (2000 kusů výrobku A,  3000 kusů výrobku B).  V buňce E5 je zapsán maximální zisk (46 000 Kč). 

Výsledek aplikace Řešitele
 
	A	B	C	D	E		

1		Výrobek A	Výrobek B	Úroveň	Spotřeba		
2	Surovina S	6	4	24000	24 000	V buňce E2 je vzorec:B2*$B$6+C2*$C$6	
3	Časový fond K	4	8	32000	32 000	Tento vzorec je zkopírován do E3, E4 a E5	
4	Počet pracovníků P	2	2	12000	10 000		
5	Zisk	8	10	max.	46 000		
6	Optimální výroba	2000	3000				

Další důležité údaje získáme porovnáním sloupců Úroveň a Spotřeba. Vidíme, že se surovina S spotřebovala beze zbytků a  byl vyčerpán časový fond zařízení K. Časový fond pracovníků P (12 000 hod.) však vyčerpán nebyl, jelikož bylo spotřebováno celkem 10 000 hod.

Pomocí Excelu však lze řešit i jiné (daleko složitější) úlohy z praxe. Záleží vždy na řešiteli, jak složitý model zvolí. Čím je model složitější, tím přesnější informace získáme. Proto se vždy rozhodujeme podle toho, kolik máme času na vyřešení problému a jaký efekt výsledná optimalizace přinese.

Jiří Barilla,  Romana Hejkrlíková