VYSOKÁ ŠKOLA EKONOMICKÁ V PRAZE FAKULTA INFORMATIKY A STATISTIKY BAKALÁŘSKÁ PRÁCE Petr Lhoták

VYSOKÁ ŠKOLA EKONOMICKÁ V PRAZE FAKULTA INFORMATIKY A STATISTIKY

BAKALÁŘSKÁ PRÁCE

2010

Petr Lhoták

VYSOKÁ ŠKOLA EKONOMICKÁ V PRAZE FAKULTA INFORMATIKY A STATISTIKY

Název bakalářské práce:

Systém pro řešení distribučních úloh LP – LINGO/Excel interface

Autor:

Petr Lhoták

Katedra:

Katedra ekonometrie

Obor:

Matematické metody v ekonomii

Vedoucí práce:

Prof. Ing. Josef Jablonský, CSc.

Prohlášení: Prohlašuji, že jsem bakalářskou práci na téma „Systém pro řešení distribučních úloh LP LINGO/Excel interface“ zpracoval samostatně. Veškerou použitou literaturu a další podkladové materiály uvádím v seznamu použité literatury. V Praze dne 13. května 2010

................................ Petr Lhoták

Poděkování: Rád bych na tomto místě poděkoval Prof. Ing. Josefu Jablonskému, CSc. za vedení mé bakalářské práce a za podnětné návrhy, které ji obohatily. Také mým rodičům za podporu po celou dobu studia.

Abstrakt Název práce: Autor: Katedra: Vedoucí práce:

Systém pro řešení distribučních úloh LP – Lingo/Excel interface Petr Lhoták Katedra ekonometrie Prof. Ing. Josef Jablonský, CSc.

Tato práce se zabývá popisem jednotlivých typů distribučních úloh a jejich řešením. Distribuční úlohy jsou nejtypičtějšími úlohami lineárního programování, které řeší optimalizaci přepravy materiálů, lidí či výrobků. Pro tento účel jsem navrhnul aplikaci, která kombinuje dva počítačové systémy a usnadňuje řešení těchto úloh. Konkrétně spojením systémů LINGO a MS Excel pomocí Visual Basic for Applications. Dále jsou v této práci vyřešeny vzorové příklady dopravního problému, alokačního problému, přiřazovacího problému a okružního dopravního problému. Jsou zde popsané hlavní procedury systému LINGO a také jeho připojení k MS Excel pomocí VBA. V neposlední řadě je uvedeno programové řešení samotné aplikace a její vizuální zhotovení. Klíčová slova: Distribuce, Distribuční modely, Lingo, MS Excel, Optimalizace.

Abstract Title: Author: Department: Supervisor:

System for computing distribution problems LP – Lingo/Excel interface Petr Lhoták Department of Econometrics Prof. Ing. Josef Jablonský, CSc.

This thesis deals with description of several distribution problems and their solutions. Distribution problems are the most typical problems of linear programming, which solves the optimalization of transportation of materials, people or goods. For this purpose I created an application, which combines two computer systems and makes computing of these problems easier. Concretely by combining of the systems LINGO and MS Excel through Visual Basic for Applications. Further in this work you will find solved examples of transportation problem, allocation problem, assignment problem and travelling salesman problem. You will also find the description of the main procedures of LINGO system and its connection to MS Excel through VBA. Last but not least there is brought up the programming solution of the application and its visual construction. Keywords: Distribution, Distribution models, Lingo, MS Excel, Optimalization

Obsah

1.

Úvod ................................................................................................................................. 7

2.

Formulace jednotlivých distribučních úloh .................................................................. 8 2.1 Dopravní problém .................................................................................................... 8 2.2 Kontejnerový dopravní problém ............................................................................ 10 2.3 Alokační problém................................................................................................... 12 2.4 Přiřazovací problém ............................................................................................... 13 2.5 Úloha o pokrytí ...................................................................................................... 15 2.6 Okružní dopravní problém ..................................................................................... 16 2.7 Obecný distribuční problém ................................................................................... 18

3.

Systém Lingo .................................................................................................................. 20 3.1 Představení a funkce systému Lingo...................................................................... 20 3.2 Zápis funkcí v systému Lingo ................................................................................ 21 3.3 Řešení jednotlivých distribučních úloh v systému Lingo ...................................... 22

4.

Tvorba aplikace ............................................................................................................. 27 4.1 Obecné zásady psaní jazykem VBA ...................................................................... 27 4.2 Interface a ovládání aplikace ................................................................................. 28 4.3 Tvorba vstupních a výstupních tabulek ................................................................. 30 4.4 Propojení systému Lingo a MS Excel .................................................................... 33

5.

Řešení úloh v aplikaci.................................................................................................... 35 5.1 Řešení distribuční úlohy ........................................................................................ 35 5.2 Řešení alokační úlohy ............................................................................................ 36 5.2 Řešen přiřazovací úlohy ......................................................................................... 38 5.2 Řešení okružní dopravní úlohy .............................................................................. 39

6.

Závěr ............................................................................................................................... 41

Seznam použité literatury .................................................................................................... 42 Přílohy ................................................................................................................................... 43

1 Úvod Téma bakalářské práce jsem si nezvolil náhodou. Již jako studenta střední školy mě fascinovalo programování a také tajemství optimalizace. V této práci se pokusím skloubit obojí. Konkrétně propojením tabulkového procesoru MS Excel a modelovacího systému Lingo pomocí Visual Basic for Aplications, a zároveň optimalizací jednotlivých typů distribučních úloh. Cílem této práce tedy bude seznámit čtenáře se základy distribučních úloh a vytvoření aplikace, která bude jednoduchá pro obsluhování. Uživatel této aplikace nebude potřebovat znalost ani jednoho z výše uvedených systémů.

Na začátek uvedu, co si čtenář může představit pod pojmem distribuce. Podle internetové knihovny wikipedie Distribuce (z lat. dis-tribuere, rozdělovat) znamená obecně činnost rozdělování i výsledek, určité rozdělení či rozložení. Distribuční úlohy se tedy zabývají optimálním rozdělením či rozložením ať už materiálu, výrobků, nebo lidí na základě minimalizace nákladů, nebo naopak maximalizace přepraveného subjektu. Distribuční úlohy můžeme rozdělit do několika základních typů. Je to například dopravní problém, kontejnerový dopravní problém, alokační problém, přiřazovací problém, úloha o pokrytí, okružní dopravní problém a obecný distribuční problém. Tyto uvedené jsou nejtypičtější úlohy lineárního programování, které patří do distribučních úloh, a proto se jim dále budeme podrobně věnovat.

Nejdříve si v druhé kapitole rozebereme každou úlohu pomocí ekonomického a matematického modelu. Dále zvolíme příklad, pomocí kterého na konci otestujeme již hotovou aplikaci. Ve třetí kapitole se podíváme na systém Lingo. Čtvrtá kapitola se bude věnovat vývoji aplikace a propojení systému Excel a Lingo ve Visual Basic for Aplications. Pátá kapitola bude obsahovat řešení úloh v této aplikaci. Nakonec krátce shrnu všechny poznatky v závěru.

Distribuční úlohy se samozřejmě dají řešit celou řadou alternativních systémů, například MPL nebo přímo v MS Excelu funkcí řešitel. Tyto programy ale vyžadují znalost lineárního programování a mnou vytvořená aplikace má být příjemná k užívání i pro laika neznalého programování. Po celé aplikaci bude uživatele provázet navigace ve formě „klikacích“ odkazů, což znamená snadnou a rychlou ovladatelnost. -7-

2 Formulace jednotlivých distribučních úloh Ještě jednou pro shrnutí vyjmenuji nejtypičtější úlohy LP patřící do distribučních úloh, kterými se v této kapitole budeme podrobně zabývat. Jsou to: •

Dopravní problém

•

Kontejnerový dopravní problém

•

Alokační problém

•

Přiřazovací problém

•

Úloha o pokrytí

•

Okružní dopravní problém

•

Obecný distribuční problém

Typů distribučních úloh je poměrně velký počet, my se zde ovšem budeme zabývat pouze těmito sedmi typy. Pro zjednodušení jsem si do svého programu z těchto sedmi vybral dopravní problém, alokační problém, přiřazovací problém a okružní dopravní problém. Aplikace se samozřejmě dá posléze o zbývající rozšířit. V této kapitole jsem čerpal z literatury [1], [2].

2.1 Dopravní problém Dopravní problém se zabývá rozvržením rozvozu nějakého zboží či materiálu od dodavatele (ty pokládáme za zdroje toho materiálu) a odběratele (cílové místa pro materiál) tak, abychom minimalizovali celkové náklady na distribuci. Je v něm definováno mdodavatelů (zdrojů), které budeme označovat D1, D2, …, Dm s omezenými kapacitami a1, a2, ..., am (což nám označuje maximální množství, které nám dodavatel může dodat). Dále máme definováno n-odběratelů (tedy cílových míst), které budeme označovat O1, O2, …, On se stanovenými požadavky označenými b1, b2, …, bn (což je množství, které odběratel požaduje). Každá dvojice dodavatel-odběratel je ohodnocen většinou nákladem na přepravu, jednotkou zdroje, nebo vzdáleností mezi jejich umístěním. Ocenění této dvojice dodavatel-odběratel budeme označovat jako cij, i = 1, 2, …, m, j = 1, 2, …, n. Cílem tohoto problému tedy bude minimalizovat náklady na přepravený obnos, zároveň ale nesmíme překročit kapacity zdrojů a musíme uspokojit odběratele. Množství přepraveného materiálu nám bude značit hodnota proměnných xij, i = 1, 2, …, m, j = 1, 2, …, n, které značí objem přepravy mezi i-tým -8-

dodavatelem a j-tým odběratelem. Proměnných tedy bude m.n. Když jsme si objasnili zadané parametry a značení jednotlivých proměnných, přejděme rovnou k matematickému modelu. Jeho zápis bude následovný:

minimalizovat ௠

௡

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ij ,

(2.1)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij ≤ ܽi ,

݅ = 1,2, … , ݉,

(2.2)

෍ ‫ݔ‬ij = ܾj ,

݆ = 1,2, … , ݊,

(2.3)

‫ݔ‬ij ≥ 0,

݅ = 1,2, … , ݉,

݆ = 1,2, … , ݊,

௝ୀଵ ௠

௜ୀଵ

kde




ai jsou kapacity dodavatelů,




bj jsou požadavky odběratelů,




cij jsou cenové koeficienty,




xij je počet přepraveného materiálu,




m je počet dodavatelů,




n je počet odběratelů, Pro i = 1, 2, …, m, j = 1, 2, …,n.

Po zhlédnutí těchto vztahů vidíme, že (2.1) je účelová funkce minimalizující celkové přepravní náklady. (2.2) jsou omezující podmínky pro každého dodavatele. Tyto podmínky nám zajistí, že přepravené množství i-tého dodavatele nepřekročí jeho kapacitní možnosti. (2.3) nám zajišťuje, aby každý j-tý odběratel dostal množství odpovídající jeho požadavku. Tato formulace má ovšem řešení pouze v případě, že kapacity dodavatelů jsou větší nebo rovné požadavkům odběratelů. To nás přivádí k dvěma pojmům. Vyrovnaný dopravní problém je situace, kdy kapacity a požadavky jsou vyrovnané. Nevyrovnaný dopravní problém naopak popisuje situaci, kdy požadavky jsou menší, respektive větší než kapacity. To lze samozřejmě upravit pomocí fiktivních odběratelů / dodavatelů. -9-

Nyní, když známe matematický model a víme, o čem v dopravním problému jde, pojďme si představit jeden vzorový příklad pro ilustraci. V této kapitole uvedu pouze zadání a výpočet. Výsledek uvedu v kapitole číslo 4, která se zabývá jednotlivými řešeními.

Příklad 2-1. Společnost Flavour s.r.o. kupuje koření do svých třech prodejen v Brně, Olomouci a Praze. V Brně se prodá 320 Kg týdně, v Olomouci 240 Kg týdně a v Praze 420 Kg týdně. Firma má možnost odebírat ze čtyř skladů umístěných v Hodoníně, Zlíně, Nymburce a Karviné. Kapacity těchto skladů jsem vzhledem k týdenní poptávce následující - Hodonín může dodávat 300 Kg, Zlín 200 Kg, Nymburk 250 Kg a Karviná 250 Kg. Distribuční náklady na kilogram koření jsou dané v tabulce 2-1, spolu s kapacitami a požadavky.

Tabulka 2-1: Tabulka nákladů, kapacit a požadavků příkladu 2-1. Brno Hodonín Zlín Nymburk Karviná

9 5 12 8

Požadavky

320

Olomouc 8 7 14 10 240

Praha 12 12 5 16

Kapacity 300 200 250 250

420

2.2 Kontejnerový dopravní problém Kontejnerový problém je pouze modifikací předchozího dopravního problému. Doprava mezi odběratelem a dodavatelem je realizována pomocí kontejnerů, tedy přepravních jednotek o určité kapacitě K. Náklady na přepravu tedy nebudou vztaženy k jednotce přepraveného materiálu, ale k jednotce přepraveného kontejneru. S jeho přepravou jsou spjaty také určité náklady, které nezáleží na množství přepraveného média. Pokud tedy přepravíme kontejner poloprázdný, cena jeho přepravy bude stejná jako u plného. Značení proměnných zachováme stejné, jako v předchozím případě. Je však nutné zavést novou proměnnou yij, i = 1, 2, …, m, j = 1, 2, …, n, která bude představovat počet kontejnerů. Tato proměnná musí být z logických důvodů celočíselná. Matematický model tedy bude vypadat následovně:

- 10 -

minimalizovat ௠

௡

‫ = ݖ‬෍ ෍ ܿij‫ݕ‬ij ,

(2.4)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij ≤ ܽi , ௝ୀଵ ௠

෍ ‫ݔ‬ij = ܾj, ௜ୀଵ

‫ݔ‬ij ≤ ‫ݕܭ‬ij, ‫ݔ‬ij ≥ 0,

‫ݕ‬ij − ݈ܿ݁é

݅ = 1, 2, … , ݉,

(2.5)

݆ = 1, 2, … , ݊,

(2.6)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

(2.7)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

(2.9)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

(2.8)

kde




ai jsou kapacity dodavatelů,




bj jsou požadavky odběratelů,




cij jsou cenové koeficienty,




yij je počet vyexpedovaných kontejnerů,




xij je počet přepraveného materiálu,




K je kapacita jednoho kontejneru,




m je počet dodavatelů,




n je počet odběratelů, Pro i = 1, 2,…, m, j = 1, 2,… ,n.

Jak můžeme vidět ze vztahu (2.4), minimalizovat budeme náklady na převoz materiálu. Podmínky (2.5) a (2.6) zůstávají nezměněny od dopravního problému. (2.7) až (2.9) zabezpečují dostatečný počet kontejnerů na přepravu materiálu.

Pro tento typ úlohy příklad uvádět nebudu, má aplikace tento typ problému neobsahuje, tudíž postačí teoretické informace.

- 11 -

2.3 Alokační problém Alokační problém je opět modifikací dopravního problému, zde ale ve smyslu párování odběratele a dodavatelů. Každý odběratel může být obsluhován pouze jedním dodavatelem. Proto v našem modelu zavedeme binární proměnnou xij, jejíž hodnoty jsou pouze 0 a 1. Pokud je odběratel obsluhován příslušným dodavatelem, proměnná tohoto vztahu bude 1. Pokud nebude obsluhován, proměnná bude 0. Matematický model alokačního problému je následovný.

minimalizovat ௠

௡

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ijܾj ,

(2.10)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ijܾij ≤ ܽi , ௝ୀଵ

௠

෍ ‫ݔ‬ij = 1, ௜ୀଵ

‫ݔ‬ij = 0 (1),

݅ = 1,2, … , ݉,

(2.11)

݆ = 1,2, … , ݊,

(2.12)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

kde




ai jsou kapacity dodavatelů,




bj jsou požadavky odběratelů,




cij jsou cenové koeficienty,




xij je binární proměnná,




m je počet dodavatelů,




n je počet odběratelů, Pro i = 1, 2, …, m, j = 1, 2, …,n.

Podívejme se blíže na jednotlivé vztahy. Minimalizační funkce (2.10) nám zajišťuje, aby byly vybrány optimální kombinace dodavatel-odběratel. V případě, že bude proměnná xij nulová, bude nulový i součin. V případě jednotkové hodnoty této bivalentní proměnné bude

- 12 -

platit součin cijbij. (2.11) zajišťuje, že pokud součin xijbij je roven jedné, uskutečňuje se celý objem přepravy bij. Zároveň ale, pokud je tento součin nula, neuskutečňuje se přeprava žádná. Nyní, když známe matematický model, pojďme si představit jeden vzorový příklad pro ilustraci. V této kapitole uvedu opět pouze zadání, výpočet a výsledek uvedu v kapitole čtyři, která se zabývá jednotlivými řešeními.

Příklad 2-2. Agentura pro průzkum trhu dostala data šesti zpracovatelů ocelových trubek, ve kterých uvádějí ceny jednotlivých trubek tak, jak je mají možnost získat od hutí. V ČR jsou tři velké hutě, které tyto ocelové trubky vyrábí. Každý odběratel si může zvolit pouze jednoho z těchto tří dodavatelů (hutí), z důvodu velké konkurence. Pokud by se přišlo na to, že bere trubky od jiného dodavatele, oba dodavatelé by ukončili spolupráci. Jaké by bylo optimální rozvržení obchodních cest, pokud by se těchto šest zpracovatelů domluvilo?

Tabulka 2-2: Tabulka dat příkladu 2-2. Odběratel Odběratel Odběratel Odběratel Odběratel Odběratel 1 2 3 4 5 6 7 12 6 12 24 7 9 8 15 11 22 8 5 9 11 13 26 16

Huť 1 Huť 2 Huť 3 Požadavky

52

48

11

61

15

Kapacity 110 90 80

13

2.4 Přiřazovací problém Přiřazovací problém je úloha, ve které jde o přiřazení dvojice jednotek ze dvou skupin. Jednu skupinu pojmenujme skupina A, čítající jednotky A1, A2, …, Am. Druhou skupinu pojmenujme skupina B, čítající jednotky B1, B2, …, Bn. Ve výsledku nám jde o to, aby toto vzájemné přiřazení přineslo co nejvyšší efekt. Účelovou funkci budeme tedy maximalizovat. Ohodnocení přiřazení každé dvojice těchto jednotek označíme koeficientem cij, i = 1, 2, …, m, j = 1, 2, …, n. Cílem úlohy je zjistit, zda bude jednotka Ai ze skupiny A přiřazena k jednotce Bj ze skupiny B. Pro toto zjištění použijeme opět binární proměnnou xij, i = 1, 2, …, m, j = 1, 2, …, n, která bude nabývat hodnot 1 a 0. Hodnota jedna bude značit přiřazení dvojice, hodnota nula opak. Při přiřazování může nastat problém, že jednotek jedné skupiny bude více - 13 -

než jednotek skupiny druhé. Pak nezbývá než nechat některé jednotky nepřiřazené. Matematický model pak bude následovný.

maximalizovat ௠

௡

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ij ,

(2.13)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij ≤ 1, ௝ୀଵ ௠

෍ ‫ݔ‬ij = 1, ௜ୀଵ

‫ݔ‬ij ≤ 0 (1),

݅ = 1,2, … , ݉,

(2.14)

݆ = 1,2, … , ݊,

(2.15)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

kde




cij jsou cenové koeficienty,




xij je binární proměnná,




m je počet jednotek skupiny A,




n je počet jednotek skupiny B, Pro i = 1, 2, …, m, j = 1, 2, …, n.

Tato matematická formulace je použitelná i pro případ, že jednotek ze skupiny A je více nebo stejně jako jednotek ze skupiny B. V opačném případě musíme zaměnit znaménka v podmínkách (2.14) a (2.15). Nyní si uveďme příklad, který budeme řešit v kapitole číslo 4.

Příklad 2-3. Konzultační firma Konzult s.r.o. hledá pět dodavatelských firem zajišťující výlov ryb pro pět tržišť s rybími produkty. Podle průzkumu jsme zjistili, kolik tun ryb měsíčně je která firma schopná dodat. Zjištěné údaje nalezneme v tabulce. Firmy zajišťující výlov mají dopravní kapacity pouze pro zásobení právě jednoho tržiště. Jak rozdělíme tržiště mezi dodavatele?

- 14 -

Tabulka 2-3: Tabulka dat příkladu 2-3. Tržiště 1 Tržiště 2 Tržiště 3 Tržiště 4 Tržiště 5

Rybolov 1 11 14 9 19 18

Rybolov 2 17 8 14 14 13

Rybolov 3 19 11 9 17 9

Rybolov 4 17 19 15 11 18

Rybolov 5 15 8 17 13 15

2.5 Úloha o pokrytí Úloha o pokrytí se zabývá budováním obslužných stanic. Mohou to být například čerpací stanice, požární či policejní stanice nebo stanice rychlé pomoci. Tyto obslužné stanice mohou být umístěny v místech M1, M2, …, Mm, kde m>K, kde K je počet těchto stanic. V této úloze je také definováno n obvodů - odběratelů, kteří získávají užitek z obslužných stanic. Značíme je O1, O2, …, On. Každý z těchto obvodů musí být přiřazen právě jednomu obslužnému místu, kterému poskytuje služby. Proto si zavedeme dvě binární proměnné yij a xij. Bude-li stanice zřízena v místě Mi, pak proměnná yij bude 1, v opačném případě 0. Bude-li obvod Oj obsluhován stanicí zřízenou v Mi, pak bude hodnota xij 1, v opačném případě 0. Z tohoto plyne, že ve sloupci součet proměnné xij musí být právě 1, tedy každý obvod musí být přiřazen některé z obslužných stanic. Řádkový součet bude buď nulový, nebo jedničkový. Podle toho, je – li v příslušném obvodě zřízena stanice. Matematický model úlohy o pokrytí je poté následovný.

minimalizovat ௠

௡

௠

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ij݂j+ ෍ ‫݌‬i‫ݕ‬i , ௜ୀଵ ௝ୀଵ

(2.16)

௜ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij ≤ (݊ − ‫ ܭ‬+ 1)‫ݕ‬i , ௝ୀଵ ௠

෍ ‫ݔ‬ij = 1, ௜ୀଵ

݅ = 1,2, … , ݉,

(2.17)

݆ = 1,2, … , ݊,

(2.18)

௡

෍ ‫ݕ‬i ≤ ‫ܭ‬, ௜ୀଵ

(2.19)

‫ݔ‬ij = 0 (1), ‫ݕ‬i = 0 (1),

- 15 -

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊, ݅ = 1,2, … , ݉

kde




cij jsou náklady jedné obsluhy v obvodě Oj ze stanice zřízené v Mi,




xij jsou binární proměnná značící zřízení stanic,




yij jsou binární proměnná značící obsluhu míst,




fj představuje četnost obsluhy v obvodě Oj,




pi jsou náklady na provoz obslužné stanice v místě Mi,




K je počet obslužných stanic,




m je počet míst k umístění stanic,




n je počet obvodů, Pro i = 1, 2, …, m, j = 1, 2, …, n.

Nyní si rozeberme jednotlivé vzorce. Účelová funkce (2.16) je součtem všech nákladů, jak na zřízení obslužných stanic, tak i na jejich provoz. Tento součet chceme samozřejmě minimalizovat. Podmínka (2.17) nám zajišťuje, aby řádkové součty byly rovny nule, pokud není v místě Mi zřízená stanice (yi v tomto případě bude nulové). Pokud v místě Mi stanice zřízena je, pak musí být součet těchto stanic menší než výraz (n – K + 1). (2.18) zajišťuje, že každý obvod náleží právě jedné obslužné stanici. Omezení (2.19) zajišťuje, že nebude překročen stanovený počet zřízených stanic.

2.6 Okružní dopravní problém Okružní dopravní problém, někdy také označován jako úloha obchodního cestujícího, má za cíl navštívit postupně všechna místa právě jednou, a s co největší úsporou (například peněz, času nebo ujetých kilometrů). Výchozí místo nazveme M1, místa následující M2, M3, …, Mn. Tyto místa můžeme projet v libovolném pořadí, ale skončit musíme v původním místě M1. Jde tedy o to, najít co nejrychlejší či nejlevnější okruh z místa M1 zpět do tohoto místa při projetí všech ostatních míst. Pro toto řešení se dají použít vztahy z přiřazovacího problému, ale musíme je ještě doplnit dalším vztahem zajišťujícím návaznost cestování, aby byl dodržen celý okruh. Bez této podmínky by se nám lehce mohlo vytvořit několik menších, samostatných okruhů. Z výše popsaného vyplývá, že musíme zavést binární proměnnou xij = 1, 2, …, n, j = 1, 2, …, n, která bude udávat návaznost místa Mi a Mj. Pokud hodnota xij bude rovna jedné, cesta povede z místa Mi do místa Mj. Pokud bude hodnota xij rovná nule, cesta těmito místy nepovede. Protože každé místo bude navštíveno právě jednou, řádkové a - 16 -

sloupcové součty matice Xij budou jednotkové. S těmito znalostmi si pojďme ukázat matematický model.

minimalizovat ௡

௡

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ij ,

(2.20)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij = 1, ௝ୀଵ ௡

෍ ‫ݔ‬ij = 1, ௜ୀଵ

ߜi − ߜi + ݊‫ݔ‬ij ≤ ݊ − 1

‫ݔ‬ij = 0 (1),

݅ = 1,2, … , ݊,

(2.21)

݆ = 1,2, … , ݊,

(2.22)

݅ = 1,2, … , ݊, ݆ = 2,3, … , ݊, (2.23) ݅, ݆ = 1,2, … , ݊,

kde




cij jsou náklady na dopravu mezi místy,




xij je binární proměnná značící návaznost míst,




ߜi je proměnná přiřazená každému místu,




n je počet míst, Pro i = 1, 2, …, m, j = 1, 2, …, n.

Účelová funkce (2.20) zajišťuje výběr nejlevnějšího, tedy minimálního okruhu. Podmínky (2.21) a (2.22) zajišťují, že řádkové a sloupcové součty matice xij budou jednotkové. Nejzajímavější jsou ovšem podmínky (2.23). Právě tyto podmínky zamezují tvoření dílčích okruhů v řešení. Jsou nicméně velmi náročné na výpočet pro jejich množství. Pro představu, v úloze kde je počet míst n = 50 bude celkem více než 2500 proměnných i omezujících podmínek. Nyní, když jsme si vysvětlili použití jednotlivých proměnných a matematický model, přejděme k praktickému využití v ilustračním příkladu (2.4). Jen dodávám, že příklad bude řešen v kapitole 4.

- 17 -

Příklad 2-4. Pro firmu Kontrola a.s. pracuje několik auditorů objíždějících výrobní závody potravin. Tuto firmu si najímá Česká zemědělská a potravinářská inspekce a Česká obchodní inspekce, pokud je podána stížnost na kvalitu výrobku. Auditorovi byly tento týden přiděleny ke kontrole čtyři pekárny. Podle tabulky vzdáleností (2.4) určete, jakou trasu by bylo nejvhodnější zvolit pro minimalizaci počtu najetých kilometrů. Po auditu všech závodů se auditor musí vrátit zpět na centrálu.

Tabulka 2-4: Tabulka dat pro příklad 2-4. Centrála Závod 1 Závod 2 Závod 3 Závod 4

Centrála 0 35 14 98 117

Závod 1 35 0 56 42 57

Závod 2 14 56 0 66 96

Závod 3 98 42 66 0 45

Závod 4 117 57 96 45 0

2.7 Obecný distribuční problém Je opět velmi podobný dopravnímu problému, jediná změna je v zadaných jednotkách kapacit a požadavků, které nejsou ve stejných jednotkách. Je tedy nutné převést je na společnou jednotku. V modelu se proto objeví určité převodní koeficienty, které tento převod zajistí. Vzhledem k podobnosti a vysvětlení předchozích modelů si dovolím rovnou uvést matematický model a následně ho rozebrat. Jeho zápis bude následovný.

minimalizovat ௠

௡

‫ = ݖ‬෍ ෍ ܿij‫ݔ‬ij ,

(2.24)

௜ୀଵ ௝ୀଵ

za podmínek ௡

෍ ‫ݔ‬ij ≤ ܽi, ௝ୀଵ

௠

෍ ݇ij‫ݔ‬ij = ܾj , ௜ୀଵ

‫ݔ‬ij ≥ 0,

- 18 -

݅ = 1,2, … , ݉,

(2.25)

݆ = 1,2, … , ݊,

(2.26)

݅ = 1,2, … , ݉, ݆ = 1,2, … , ݊,

kde




ai jsou kapacity dodavatelů,




bj jsou požadavky odběratelů,




cij jsou cenové koeficienty,




xij je počet přepraveného materiálu,




kij jsou přepočítací koeficienty,




m je počet dodavatelů,




n je počet odběratelů, Pro i = 1, 2, …, m, j = 1, 2, …, n.

Vztahy (2.24) a (2.25) jsou společné s dopravním problémem. Účelová funkce minimalizuje náklady, a podmínky (2.25) zajišťují, že přepravené množství i-tého dodavatele nepřekročí jeho kapacitní možnosti. Za prozkoumání ovšem stojí podmínky (2.26), které díky přepočítacím koeficientům převedou hodnoty na společnou jednotku.

- 19 -

3 Systém Lingo V této kapitole bych rád představil Lingo, jako systém pro řešení úloh lineárního programování, základní pravidla pro zapisování modelů a zápis funkcí. Dále uvedu jednotlivé programy vytvořené v programovacím jazyce Lingo pro řešení distribučních problémů.

3.1 Představení a funkce systému Lingo Lingo je systém pro řešení lineárních, ale i nelineárních úloh, vyrobený společností Lindo Systems. Je jedním z mnoha programů určených pro tento účel. Velmi dobře spolupracuje s programy MS Excel a MS Acces. Díky tomu lze vytvořit i grafickou prezentaci výstupů. Firma Lindo systems umožňuje stáhnout si na svých www stránkách studentskou verzi Lingo 12, kterou jsem použil pro svou aplikaci. Tato studentská verze je ovšem omezena pouze na podporu třiceti proměnných typu integer a třiceti nelineárních proměnných. Pro řešení úloh uvedených výše je však tato kapacita dostačující. Nyní zde uvedu pár základních pravidel pro zápis modelů do systému Lingo. •

Celý model musí obsahovat základní sekce, které se nazývají: MODEL -

používá se na začátku modelu, vše mezi tímto klíčovým slovem a příkazem END je považováno za jeden ucelený model.

SETS -

toto klíčové slovo používáme pro definování množin, jejich prvků a atributů.

ENDSETS -

ukončí definování množin a po tomto příkazu dále pokračuje obecný zápis vlastního modelu.

DATA -

v této části specifikujeme vstupní data modelu, v případě externích dat uvedeme příkaz pro export dat z jiného programu a uložení výstupních dat opět do externího programu.

ENDDATA -

ukončuje sekci DATA.

END -

nezbytné klíčové slovo pro ukončení celého modelu. - 20 -

•

Optimalizační kritérium, tedy minimalizace nebo maximalizace úlohy se udává jako MIN = respektive MAX =. Za rovnítkem následuje zápis účelové funkce, kterou chceme minimalizovat / maximalizovat.

•

Každý řádek je ukončen středníkem, toto je nezbytně nutné z důvodu absence ukončení jedné řádkové funkce enterem.

•

Pro zápis komentářů se používá vykřičník, ukončení komentáře je opět značeno středníkem. Komentáře je vhodné uvádět u všech důležitějších funkcí pro jejich snadnou identifikovatelnost a přehlednost.

•

V zápisu rovnic můžeme využívat všechny operátory, které jsou zapisovatelné do prostředí jazyku Lingo. Mezi numerickou hodnotu a označení proměnné vkládáme znak násobení *. Příklad: omezení 2x1 + 3x2 > 5 uvedeme jako 2*x1 + 3*x2 > 5;

3.2 Zápis funkcí v systému Lingo Pojďme se nyní podívat na zápis funkcí v systému Lingo. Jejich množství je opravdu značné, proto uvedu jen ty, které jsem použil při řešení distribučních úloh nebo ty, které považuji za důležité.

Indexové funkce – patří k nejpoužívanějším, umožňují manipulaci s atributy množin @SUM - provádí součet pro všechny prvky dané množiny. @FOR - říká, pro určené prvky množiny proveď tuto operaci (operací je v našem případě omezující podmínka). V případě potřeby zápisu více operací je oddělíme středníkem.

Funkce pro popis vlastností proměnných – tato kategorie funkcí popisuje vlastnosti proměnných modelu. @BIN - takto definovaná proměnná nabývá hodnot pouze 0 a 1, je tedy bivalentní (říká se jí také binární proměnná). @GIN – další velmi užitečná funkce, udává celočíselnost proměnné.

- 21 -

Funkce pro import a export dat – zde uvedu funkci pro import a export dat z/do Excelu @OLE – tato funkce má dva parametry, první parametr je název excelovského souboru. Druhý parametr je název oblasti, která se vztahuje k určitému atributu. Pro přiblížení uvedu dva příklady:

NAKL = @OLE('Bakalarska_prace.xls','zadani'); - atributu NAKL přiřadíme v excelovském souboru Bakalarska_prace.xls oblast zadáni. Načítají se tedy data atributu.

@OLE('Bakalarska_prace.xls') = výsledky, Ucefu; - zde se jedná o export dat z výsledků do excelovského souboru Bakalarska_prace.xls, oblasti výsledky a Ucefu.

3.3 Řešení jednotlivých distribučních úloh v systému Lingo Nyní, když víme, jak zapisovat model a známe základní funkce, pojďme se podívat a rozebrat řešení jednotlivých distribučních úloh v systému Lingo. U každého programu vysvětlím nejdůležitější funkce řádek po řádku.

1. Dopravní problém MODEL: SETS: DOD/@OLE('Bakalarska_prace.xls','omezeni')/:KAP; ODB/@OLE('Bakalarska_prace.xls','promene')/:POZ; TRANS(DOD,ODB):VYSLEDKY, NAKL; ENDSETS [UCEFU] MIN = @SUM(TRANS: VYSLEDKY*NAKL); @FOR(DOD(I): @SUM(ODB(J): VYSLEDKY(I,J)) <= KAP(I)); @FOR(ODB(J): @SUM(DOD(I): VYSLEDKY(I,J)) = POZ(J)); DATA: KAP = @OLE('Bakalarska_prace.xls','kapacity'); POZ = @OLE('Bakalarska_prace.xls','pozadavky'); NAKL = @OLE('Bakalarska_prace.xls','zadani'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu; ENDDATA END Jako u každé úlohy začínáme klíčovým slovem MODEL. V sekci SETS definujeme primitivní množinu dodavatelů, odběratelů, kapacit a požadavků. Definujeme také odvozenou množinu výsledků a nákladů, což bude kombinace dvojic odběratel/dodavatel. Sekci ukončíme ENDSETS. Následuje účelová funkce, která minimalizuje součet výsledků a

- 22 -

nákladů na převoz materiálu. Po účelové funkci zapíšeme dvě omezení. První nám zajistí, abychom při optimalizaci nepřekročili kapacity dodavatelů. Druhý říká, že každý odběratel musí mít materiálu podle svého požadavku. Sekce DATA odkazuje na excelovský soubor Bakalarska_prace.xls a vybírá z něj data pro výpočet. Celý program následně ukončíme klíčovým slovem END.

2. Kontejnerový dopravní problém MODEL: SETS: DOD/@OLE('Bakalarska_prace.xls','omezeni')/:KAP; ODB/@OLE('Bakalarska_prace.xls','promene')/:POZ; TRANS(DOD,ODB): VYSLEDKY, NAKL, X; ENDSETS [UCEFU] MIN = @SUM(TRANS: NAKL*X); @FOR(DOD(I): @SUM(ODB(J): VYSLEDKY(I,J)) <= KAP(I)); @FOR(ODB(J): @SUM(DOD(I): VYSLEDKY(I,J)) = POZ(J)); @FOR(TRANS:K*X >= VYSLEDKY); @FOR(TRANS: @GIN(X)); @FOR(TRANS: @GIN(VYSLEDKY)); DATA: KAP = @OLE('Bakalarska_prace.xls','kapacity'); POZ = @OLE('Bakalarska_prace.xls','pozadavky'); NAKL = @OLE('Bakalarska_prace.xls','zadani'); K = @OLE('Bakalarska_prace.xls','kapkontejneru'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu,X; ENDDATA END Zde, již klíčová slova nechme stranou a zaměřme se přímo na účelovou funkci a jednotlivé omezení. Pro objasnění proměnných, Xij je počet kontejnerů, K je kapacita kontejnerů. Účelová funkce minimalizuje sumu nákladů na převoz materiálu a počtu kontejnerů. První a druhé omezení je stejné, jako u dopravního problému. Třetí omezení nám zajišťuje dostatek kontejnerů pro převoz materiálu. Poslední dva řádky před klíčovým slovem DATA, nám udávají celočíselnost počtu kontejnerů a výsledných dat přepravovaného materiálu.

3. Alokační problém MODEL: SETS: DOD/@OLE('Bakalarska_prace.xls','omezeni')/:KAP; ODB/@OLE('Bakalarska_prace.xls','promene')/:POZ; TRANS(DOD,ODB):VYSLEDKY, NAKL; - 23 -

ENDSETS [UCEFU] MIN = @SUM(TRANS: VYSLEDKY * NAKL * POZ); @FOR(DOD(I): @SUM(ODB(J): VYSLEDKY(I,J) * POZ(J)) <= KAP(I)); @FOR(ODB(J): @SUM(DOD(I): VYSLEDKY(I,J)) = 1); @FOR(TRANS: @BIN(VYSLEDKY)); DATA: KAP = @OLE('Bakalarska_prace.xls','kapacity'); POZ = @OLE('Bakalarska_prace.xls','pozadavky'); NAKL = @OLE('Bakalarska_prace.xls','zadani'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu; ENDDATA END V tomto případě alokačního problému opět definujeme množiny odběratelů, dodavatelů, výsledků a nákladů. Účelová funkce minimalizuje sumu součinů nákladů (cenových koeficientů), požadavků a výsledků (v binární podobě). První omezení zajišťuje, že nebudou překročeny kapacity dodavatelů – násobením binární výsledkové tabulky s požadavky odběratelů a jejich porovnání s kapacitami. Druhé omezení říká, že sloupcové součty výsledkové matice musí být jedna. Poslední řádek před daty udává bivalentnost výsledkových proměnných.

4. Přiřazovací problém MODEL: SETS: DOD/@OLE('Bakalarska_prace.xls','omezeni')/:KAP; ODB/@OLE('Bakalarska_prace.xls','promene')/:POZ; TRANS(DOD,ODB):VYSLEDKY, NAKL; ENDSETS [UCEFU]MAX = @SUM(TRANS: VYSLEDKY * NAKL); @FOR(DOD(I): @SUM(ODB(J): VYSLEDKY(I,J)) <= 1); @FOR(ODB(J): @SUM(DOD(I): VYSLEDKY(I,J)) = 1); @FOR(TRANS: @BIN(VYSLEDKY)); DATA: NAKL = @OLE('Bakalarska_prace.xls','zadani'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu; ENDDATA END V tomto případě účelová funkce maximalizuje užitek z přiřazení dvojic dodavatelů a odběratelů. Dvě další omezení říkají, že sloupcové a řádkové součty matice výsledků mají být 1. Poslední funkce udává bivalentnost proměnným tabulky výsledků.

- 24 -

5. Úloha o pokrytí MODEL: SETS: OBI/@OLE('Bakalarska_prace.xls','omezeni')/:Y; OBJ/@OLE('Bakalarska_prace.xls','promene')/:F; TRANS(OBI,OBJ):X,T; ENDSETS [UCEFU] MIN=@SUM(TRANS(I,J):F(J)*T(I,J)*X(I,J)); @FOR(OBI(I):@SUM(OBJ(J):X(I,J))<=5*Y(I)); !5=poč.obvodů; @FOR(OBJ(J):@SUM(OBI(I):X(I,J))=1); @FOR(OBJ(J):@SUM(OBI(I):Y(I))=2); !2=počet stanic; @FOR(TRANS(I,J):@BIN(X(I,J))); @FOR(OBI(I):@BIN(Y(I))); DATA: F = @OLE('Bakalarska_prace.xls','F'); T = @OLE('Bakalarska_prace.xls','T'); @OLE('Bakalarska_prace.xls') = X,Y,UCEFU; ENDDATA END Opět definujeme základní a odvozenou množinu. V tomto případě, účelová funkce minimalizuje náklady na obsluhu jednotlivých míst v závislosti na umístění stanice. V tomto modelu znamená Tij=doba odezvy, Xij =obvod bude obsloužen stanicí v Oj, Fj prům. denní četnost požadavků, Yi =BIN stanice bude zřízena. První omezení nám zajišťuje, že řádkové součty jsou rovny nule, pokud není v místě zřízená stanice Yi, v tomto případě bude nulové. Druhá podmínka udává, že každý obvod bude obsluhován pouze jednou stanicí. Třetí podmínka nám říká, že součet Yi, tedy zřízených stanic se bude rovnat číslu, jaké určíme. Místo čísla bychom také mohli zadat proměnnou, kterou bychom volali z excelovského souboru

6. Okružní dopravní problém MODEL: SETS: MISTO /@OLE('Bakalarska_prace.xls','omezeni')/:DELTA; KOMBINACE (MISTO,MISTO): VYSLEDKY, KM; ENDSETS [UCEFU] MIN = @SUM(KOMBINACE: KM * VYSLEDKY); @FOR(MISTO(I): @SUM(MISTO(J): VYSLEDKY(I,J)) = 1); @FOR(MISTO(J): @SUM(MISTO(I): VYSLEDKY(I,J)) = 1); @FOR(MISTO(I):@FOR(MISTO(J)|J#GE#2:DELTA(I)DELTA(J)+N*VYSLEDKY(I,J) <= N-1)); @FOR(KOMBINACE: @BIN(VYSLEDKY)); DATA: KM = @OLE('Bakalarska_prace.xls','zadani'); - 25 -

N = @OLE('Bakalarska_prace.xls','pocet'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu; ENDDATA END Opět definujeme základní a odvozenou množinu. V tomto modelu, účelová funkce minimalizuje součet ujetých kilometrů. Atribut VYSLEDKY obsahuje binární proměnné, které určují spojnice jednotlivých míst. První a druhé omezení určují, že řádkový a sloupcový součet bude jednotkový. Třetí omezení zajišťuje právě jeden okruh, tedy zajistí, aby jich nevzniklo více.

7. Obecný distribuční problém MODEL: SETS: LINKA/@OLE('Bakalarska_prace.xls','linky')/: KAP; VYROBEK /@OLE('Bakalarska_prace.xls','vyrobky')/: POZ; TRANS(LINKA,VYROBEK): VYKON, NAKL, VYSLEDKY; ENDSETS [UCEFU] MIN = @SUM(TRANS: NAKL*VYSLEDKY); @FOR(LINKA(I): @SUM(VYROBEK(J): VYSLEDKY(I,J)) <= KAP(I)); @FOR(VYROBEK(J): @SUM(LINKA(I): VYKON(I,J)*VYSLEDKY(I,J)) >= POZ(J)); DATA: KAP = @OLE('Bakalarska_prace.xls','kapacity'); POZ = @OLE('Bakalarska_prace.xls','pozadavky'); NAKL = @OLE('Bakalarska_prace.xls','naklady'); VYKON = @OLE('Bakalarska_prace.xls','koeficienty'); @OLE('Bakalarska_prace.xls') = vysledky,Ucefu; ENDDATA END Opět definujeme základní a odvozenou množinu. Účelová funkce minimalizuje náklady. První omezení udává, že nelze překročit časovou kapacitu linek. Druhé omezení zajišťuje splnění požadavků produkce. V tomto druhém omezení používáme VYKON jako množinu převodních koeficientů.

- 26 -

4 Tvorba aplikace Nyní, když máme všechny nezbytné znalosti systému Lingo, můžeme se pustit do rozboru aplikace. V této kapitole si objasníme postup tvorby a také strukturu aplikace vytvořené ve Visual Basic for Aplications.

4.1 Obecné zásady psaní jazykem VBA Než se začneme věnovat struktuře aplikace, rád bych objasnil základy práce s jazykem Visual Basic for Applications. Visual Basic je programovací jazyk, který vytvořila firma Microsoft pro operační prostředí Windows. Tento jazyk nám umožní efektivně pracovat s mnoha programy a systémy společnosti Microsoft. V našem případě nás však bude zajímat pouze spolupráce s MS Excel. Obecné zásady a tipy pro psaní kódu jsou následující: •

Velikost písmen je v kódu irelevantní, rozpoznává se pouze u proměnných. Pro lepší čitelnost kódu je však vhodné oddělovat velkými písmeny složená slova. Například: vbYesNo místo vbyesno.

•

Odsazování je v kódu důležité především pro přehlednost. Počítač kód čte po řádcích.

•

Komentáře se značí apostrofem, pro pozdější dohledání chyb a částí kódu je vhodné ho uvádět u každého makra.

•

Zapsání více příkazů na jeden řádek je možný, ale tyto příkazy musí být odděleny dvojtečkou.

•

Nejrychleji spustíme VBA v MS Excel prostřednictvím ALT + F11.

•

V tvoření programu pomůže také nápověda, nejlépe ji vyvoláme z editoru.

•

Každé makro začíná příkazem Sub název_makra a končí příkazem End Sub.

•

Používejte dostatečně dlouhé názvy proměnných. Nic neříkající proměnná P bude určitě lépe vystižena jako proměnná Podminka nebo Podmin.

V kostce jsem tedy nastínil zásady psaní jazykem Visual Basic for Applications. Samozřejmě ne vždy je musíme uplatnit, ale ve výsledku by měl být kód přehledný a srozumitelný. Pojďme se tedy podívat na mnou vytvořenou aplikaci a její části.

- 27 -

4.2 Interface a ovládání aplikace Interface aplikace by měl ěl být jednoduchý a přehledný. p ehledný. Proto jsem si zvolil bílý podklad s modrým motivem (viz obrázek rázek 4-1). 1). Ovládání probíhá pomocí navigačních tlačítek. tla

Obrázek 4-1: Zobrazení úvodní stránky aplikace

Samotná aplikace obsahuje tři t listy s názvy „úvod“, „zadání“ adání“ a „výsledky“. List „úvod“ obsahuje dvě tlačítka, čítka, „Nabídka“ a „Konec“. „Nabídka“ vyvolá dialogové okno pro výběr typu aplikace,, ale ještě před tím vymaže veškeré data v listech „zadání“ a „výsledky“. „vý Tlačítko „Konec“ se zeptá, zda uživatel opravdu chce ukončit ukon it aplikaci, při př kladné odpovědi se aplikace ukončí.

Makro pro tlačítko ítko Konec: Sub Konec() ' Makro, které se zeptá na ukončení ení aplikace a případně př ji ukončí Dim kon kon = MsgBox("Opravdu MsgBox("Opr chcete ukončit it aplikaci?", vbQuestion + vbYesNo, "Konec aplikace") If kon = vbYes Then Application.Quit End If - 28 -

End Sub Makro pro tlačítko Nabídka: Sub Nabídka() ' Makro, které vyvolá nabídku a zároveň vymaže Zadání i Výsledky Sheets("Zadani").Select Rows("14:90").Select Selection.Delete Shift:=xlUp Sheets("Vysledky").Select Rows("14:90").Select Selection.Delete Shift:=xlUp Sheets("Uvod").Select Load Hlavni_nabidka Hlavni_nabidka.Show End Sub Po stisknutí tlačítka „Nabídka“ se zobrazí hlavní nabídka (viz obrázek 4-2), ve které si uživatel může vybrat ze čtyř typů distribučních úloh. Po výběru jedné z úloh se uloží číslo úlohy do listu úvod, pole A41. Toto číslo je následně použito v programu pro identifikaci problému a typu řešení. Dále se spustí další dialogové okno (viz obrázek 4-3), ve kterém uživatel zadá počet proměnných a počet omezení.

Obrázek 4-2: Nabídkové menu

Obrázek 4-3: Zadání rozsahu úlohy

Po zadání počtu omezení a proměnných se tyto data uloží jako proměnné m a n. Po aktivaci tlačítka OK je volána procedura pro tvorbu tabulek v listech „zadání“ a „výsledky“. Blíže si tyto procedury ukážeme v sekci 4.3. U okružního dopravního problému je ještě volána procedura Test_m_n, která určí, zda je počet omezení roven počtu zadaných proměnných. Jak jsme si vysvětlili výše, okružní dopravní problém je zadán symetricky. Pokud procedura zjistí, že se tyto proměnné nerovnají, uživateli vyhodí zprávu "Počet proměnných musí být stejný jako počet omezení " a schová všechny Dialogová okna. Tedy - 29 -

uživatel bude začínat znova. V případě, že se tyto proměnné rovnají, jsou spuštěny procedury pro tvorbu tabulek. To nás automaticky přenese na list zadání. V listech zadani a vysledky se budeme pohybovat pomocí navigačních tlačítek, které máme dvě. Na listu zadání je to tlačítko Zpět na úvod, které používáme v případě, že jsme se rozhodli pro jiný typ úlohy. Na listu vysledky je opět tlačítko Zpět na úvod, spolu s Opravit data, které používáme, pokud chceme modifikovat zadaná data, či jsme je zadali špatně. Tyto tlačítka jsou řešeny systémem objektů MS Excel, kterým jsou přiřazeny makra.

Příklad navigačního makra: Sub NaUvod()

' Makro které provádí navigaci

Sheets("Uvod").Select Range("A1").Select End Sub

Uvedené procedury / makra by se daly samozřejmě udělat více způsoby. Mnou použitý způsob ale považuji za nejjednodušší. Ovládání je řešeno víceméně intuitivně a dá se snadno modifikovat či předělat k obrazu konkrétního uživatele. K ovládání a interface aplikace již bylo řečeno snad vše. Pojďme se nyní podívat na tvorbu vstupních a výstupních tabulek.

4.3 Tvorba vstupních a výstupních tabulek Hlavní součást programu tvoří tvorba zadávací a také výstupní tabulky. Tyto tabulky jsou tvořeny podle typu úlohy. V případě aktivace tlačítka OK v dialogovém okně Zadání proměnných je spuštěn nejdříve test, který zjišťuje hodnotu buňky A41 na listu Uvod. Podle tohoto jednoduchého testu je pak spuštěna procedura příslušná zadávací a výsledkové tabulce daného problému.

Část procedury pro test typu úlohy: Sub Test() 'Otestuje jakou úlohu si uživatel vybral a k tomu vybere příslušnou proceduru Range("A41").Select If ActiveCell.Value = "1" And m > 0 And n > 0 Then Call Tabulka1

- 30 -

Pro demonstraci fungování tvorby tabulek jsem si zvolil první typ úlohy, což je dopravní problém. Předpokládejme tedy, že v buňce A41 bude hodnota 1. V případě vyhodnocení testu ve prospěch dopravního problému je volána procedura Tabulka 1.

Sub Tabulka1() Sheets("Zadani").Select Call pozadavky Call kapacity Call hlavni_tabulka_zadani Call pojmenovani_tabulky_zadani Call ucelova_fce Call hlavni_tabulka_vysledky Call pojmenovani_tabulky_vysledku Call Pojmenovani_odberatele_promenne Call Pojmenovani_dodavatele_omezeni Call Pojmenovani_odberatele Call Pojmenovani_dodavatele End Sub

Pojďme si teď krátce rozebrat jednotlivé procedury. Nebudu zde již uvádět celé části programu, pouze jednotlivé procedury popíši. Důležitější části programu budou uvedeny v příloze a celý pak bude dostupný na přiloženém CD. Jak můžeme vidět, nejdříve se přemístíme na list Zadani. Dále voláme proceduru pozadavky.

Call pozadavky Tato procedura uloží proměnné m a n (tedy počet proměnných a omezení), které jsou zadané textovými poli v dialogovém okně do dvou proměnných Prom a Omeze. Základní ukotvovací buňkou je B14, od které budeme počítat velikost tabulky dle zadaných rozměrů. Tato procedura vepíše text Požadavky do vzdálenosti [Omeze + 2, 0] od buňky B14. Dále pojmenuje oblast mezi [Omeze + 2, 1] a [Omeze + 2, Prom] jako požadavky. Tato pole budou prázdná a právě zde uživatel zadá požadavky odběratelů. Také samozřejmě upraví všechny tyto buňky dle mnou definovaného stylu. Pro neznalost příkazů, které upravují buňky, jsem využil nahrávání makra, jehož části jsem následně použil.

- 31 -

Call kapacity Tato procedura je podobná proceduře pozadavky. Opět uložíme proměnné do Prom a Omeze. Vepíšeme text Kapacity do vzdálenosti [0, Prom + 2] od vzdálenosti buňky B14. Pojmenujeme oblast mezi [1, Prom + 2] a [Omeze, Prom + 2] jako kapacity. Do těchto polí bude uživatel vyplňovat kapacity dodavatelů. Následně upravíme styl.

Call hlavni_tabulka_zadani Tato procedura vytvoří tabulku pro zadávání dat. Je to jednoduchá procedura, která pouze vytvoří ohraničení mezi buňkou B14 a vzdáleností [Omeze, Prom] od této buňky. Samozřejmě by se dala sloučit s následující procedurou pojmenovani_tabulky_zadani, ale pro přehlednost jsem je nechal oddělené.

Call pojmenovani_tabulky_zadani Procedura pouze pojmenuje prostor mezi C15 a [Omeze, Prom] od této buňky jako zadani. Tyto buňky budou sloužit jako cenové koeficienty, které zadá uživatel. Dále zarovná buňky v daném prostoru na střed.

Call ucelova_fce Nyní již tedy přejdeme k procedurám, které tvoří tabulku výsledků v listě Vysledky. Nejdříve se tedy na tento list musíme přemístit. Dále vložíme do buňky ve vzdálenosti [Omeze + 2, 0] od buňky B14 text Účelová funkce. O jednu buňku doprava, tedy [Omeze + 2, 1] nám Lingo zapíše výsledek účelové funkce daného problému. Pojmenujeme ji Ucefu. Opět zarovnáme na střed a upravíme styl.

Call hlavni_tabulka_vysledky Stejná procedura jako hlavni_tabulka_zadani. Pouze s tím rozdílem, že je na jiném listě.

Call pojmenovani_tabulky_vysledku Opět stejná procedura jako pojmenovani_tabulky_zadani, pouze pojmenujeme prostor buněk jako vysledky.

Call Pojmenovani_odberatele_promenne Pro Lingo musíme mít stanovenou množinu odběratelů. Toho dosáhneme pojmenováním oblasti C14 až [0, Prom] od B14 jako promenne. - 32 -

Call Pojmenovani_dodavatele_omezeni Stejně

jako

v Pojmenovani_dodavatele_omezeni

musíme

mít

stanovenou

množinu

dodavatelů. Toho dosáhneme pojmenováním oblasti B15 až [Omeze, 0] od B14 jako omezeni.

Call Pojmenovani_odberatele Tato procedura v listech Zadani a Vysledky vepíše texty Odběratel 1 – Odběratel n. Tato procedura byla spolu s Pojmenovani_dodavatele nejnáročnější na tvorbu. Nejdříve jsou definovány proměnné Soucet spolu s Prom a Omez. Všechny tyto proměnné definujeme jako Integer. Dále nastavíme Soucet = 1. Nyní přichází na řadu smyčka, kdy vždy označíme pole se souřadnicemi [B14, Součet]. Do tohoto pole vepíšeme Odběratel a číslo momentální hodnoty proměnné Součet. Na konci smyčky musíme sečíst Soucet = Soucet + 1, a to tak dlouho dokud se Soucet nebude rovnat Omez + 1. Smyčka pak vypadá následovně:

Do Range("b14").Select ActiveCell.Offset(0, Soucet).Select ActiveCell.FormulaR1C1 = "Odběratel " & Soucet & " " Soucet = Soucet + 1 Loop Until Soucet = Omez + 1

Call Pojmenovani_dodavatele Viz procedura Pojmenovani_odberatele. Tato procedura je velmi podobná. Označovat budeme buňku se souřadnicemi [Součet, B14], vepisovat do daných buněk Dodavatel a momentální hodnotu proměnné Soucet. Cyklovat budeme, dokud se hodnota Soucet nebude rovnat Prom + 1. V tomto momentu máme připraveny tabulky pro zadávání a také tabulky, kde se budou ukládat výsledky ze systému Lingo. Stačí zadat data a spustit tlačítko Vyřeš. Problematice propojení systému Lingo a MS Excel se budu věnovat v následující kapitole 4.4.

4.4 Propojení systému Lingo a MS Excel Poslední, ne však nejméně důležitá součást programu je propojení systémů Lingo s MS Excel. Nejdříve musíme definovat Lingo jako objekt. To uděláme na začátku modulu příkazem: - 33 -

Dim LINGO As Object.

Poté musíme vyřešit vytvoření souboru systému Lingo, ve kterém budeme řešit naši úlohu. Procedura Auto_Open zajistí tvorbu tohoto souboru při otevření excelovského souboru Bakalarska_prace. Její znění je následující:

Sub Auto_Open() Set LINGO = CreateObject("LINGO.Document.4") End Sub

Dále

si

zavedeme

LINGOSolve_dopravni,

procedury

pro

řešení

LINGOSolve_alokacni,

jednotlivých

úloh.

Názvy

jsou

LINGOSolve_prirazovaci

a

LINGOSolve_okruzni. Jejich obsah si můžeme prohlédnout v příloze, nás bude hlavně zajímat, že kopírují do Linga určité oblasti z listu Uvod. Od buňky A57 jsou v listu Uvod zapsané jednotlivé programy Linga a pojmenované jako oblasti DOPRAVNI, ALOKACNI, PRIRAZOVACI a OKRUZNI. Každý tento program začíná příkazem SET ECHOIN 1 a končí příkazy GO a QUIT. Pokud je tedy aktivováno tlačítko Vyřeš na listě Zadani, je spuštěn test, který opět prohledá buňku A41 v listu Uvod a podle vyhodnocení čísla zadané úlohy spustí některou z těchto procedur.

- 34 -

5 Řešení distribučních úloh V této kapitole si ukážeme řešení jednotlivých úloh zadaných výše (viz kapitola 2) ve vytvořeném programu Excelu na bázi Visual Basic for Applications. Pro připomenutí, u každé úlohy opět uvedu zadání. Následně tabulku zadaných dat a tabulku výsledků. Podrobně popíši postup ovládání pouze u dopravní úlohy, práce s ostatními typy úloh je totožná.

5.1 Řešení dopravní úlohy Společnost Flavour s.r.o. kupuje koření do svých třech prodejen v Brně, Olomouci a Praze. V Brně se prodá 320 Kg týdně, v Olomouci 240 Kg týdně a v Praze 420 Kg týdně. Firma má možnost odebírat ze čtyř skladů umístěných v Hodoníně, Zlíně, Nymburce a Karviné. Kapacity těchto skladů jsou vzhledem k týdenní poptávce následující. Hodonín může dodávat 300 Kg, Zlín 200 Kg, Nymburk 250 Kg a Karviná 250 Kg. Distribuční náklady na kilogram koření jsou dané v tabulce 2-1, spolu s kapacitami a požadavky. (viz Příklad 2-1).

Po spuštění souboru Bakalarska_prace.xls klikneme na tlačítko Nabídka. Zvolíme dopravní problém, a v tabulce pro zadání zvolíme 3 proměnné a 4 omezení. Po stisknutí tlačítka OK se dostaneme na list Zadani. Po zapsání dat do tabulky vypadá naše aplikace následovně: (Viz Obrázek 5-1).

Obrázek 5-1: Zadání distribuční úlohy

- 35 -

Pokud se rozhodneme změnit typ zadání, je možno aktivovat tlačítko Zpět do menu, což nás přesune na úvodní obrazovku aplikace. V případě, že máme zadány data a chceme úlohu řešit, stiskneme tlačítko Vyřeš. Tím se spustí řešení v aplikaci Lingo a dostáváme se na list Vysledky (viz. Obrazek 5-2).

Obrázek 5-2: Výsledky distribuční úlohy

Nyní nám nezbývá nic jiného, než interpretovat výsledky. Celkové náklady na zásobování tří prodejen jsou 7390 Kč. Dodavatel 1, což je sklad v Hodoníně, bude zásobovat 130 kily koření prodejnu v Olomouci a 170 kily koření prodejnu v Praze. Dodavatel 2, což je Zlín, bude zásobovat 90 kily Brno a 110 kily Olomouc. Dodavatel 3, což je Nymburk, bude zásobovat pouze Prahu a to 250 kilogramy koření. Dodavatel 4 což, je Karviná, bude dodávat 230 kilogramů prodejně v Brně. V případě, že jsme udělali chybu v zadání nebo chceme modifikovat zadání pro zlepšení účelové funkce, máme možnost stisknout tlačítko Opravit data, které nás přemístí zpět na list pro zadání dat. Pokud chceme aplikaci ukončit či změnit typ úlohy, v obou případech stiskneme tlačítko Zpět do menu.

5.2 Řešení alokační úlohy Agentura pro průzkum trhu dostala data šesti zpracovatelů ocelových trubek, ve kterých uvádějí ceny jednotlivých trubek tak, jak je mají možnost získat od hutí. V ČR jsou tři velké hutě, které tyto ocelové trubky vyrábí. Každý odběratel si může zvolit pouze jednoho z těchto tří dodavatelů (hutí), z důvodu velké konkurence. Pokud by se přišlo na to, že bere trubky

- 36 -

od jiného dodavatele, oba dodavatelé by ukončili spolupráci. Jaké by bylo optimální rozvržení obchodních cest, pokud by se těchto šest zpracovatelů domluvilo?(viz Příklad 2-2). Opět spustíme soubor Bakalarska_prace.xls a klikneme na tlačítko Nabídka. Zvolíme alokační problém a v tabulce pro zadání zvolíme 5 proměnných a 3 omezení. Po stisknutí tlačítka OK se dostaneme na list Zadani. Po zapsání dat vypadá tabulka zadání následovně: (Viz Obrázek 5-3).

Obrázek 5-3: Zadání alokační úlohy

Jak můžeme vidět v zadání, Odběratel 1 až Odběratel 6 jsou jednotliví zpracovatelé ocelových trubek. Dodavatel 1 až Dodavatel 3 jsou hutě, které tyto trubky dodávají. Samotná tabulka cenových koeficientů udává cenu jedné trubky, za kterou ji huť prodává kterému zpracovateli. Kapacity a požadavky jsou jasně dané. Nyní nám stačí stisknout tlačítko Vyřeš. Poté dostáváme výsledky (viz Obrázek 5-4).

Obrázek 5-4: Výsledky alokační úlohy

Z výsledků úlohy je patrné, že huť 1 bude zásobovat závody 3, 4 a 6. Huť 2 bude zásobovat závody 2 a 5. Huť 3 bude zásobovat závod 1. Celkové náklady na měsíční dodávku ocelových trubek pro všechny zpracovatele poté budou 1863 tis. Kč. V případě nespokojenosti s výsledkem či chybou v zadání je možné opět díky tlačítku Opravit data tyto parametry změnit. V opačném případě aktivujeme tlačítko Zpět do menu, kde ukončíme aplikaci.

- 37 -

5.3 Řešení přiřazovací úlohy Konzultační firma Konzult s.r.o. hledá pět dodavatelských firem, zajišťující výlov ryb pro pět tržišť s rybími produkty. Podle průzkumu jsme zjistili, kolik tun ryb měsíčně, je která firma schopná dodat. Zjištěné údaje nalezneme v tabulce. Firmy zajišťující výlov mají dopravní kapacity pouze pro zásobení právě jednoho tržiště. Jak rozdělíme tržiště mezi dodavatele?(viz Příklad 2-3).

V této úloze nám půjde o přiřazení právě jednoho dodavatele právě jednomu odběrateli a maximalizaci výsledku. Nejdříve si musíme spustit soubor Bakalarska_prace.xls a aktivovat tlačítko Nabídka. Vybereme přiřazovací problém. Zvolíme pět omezení a pět proměnných. Po stisknutí tlačítka OK se dostáváme na list Zadani, kde přepíšeme tuny ryb do tabulky. Zadané data vypadají následovně. (viz Obrázek 5-5).

Obrázek 5-5: Zadání přiřazovací úlohy

Z obrázku 5-5 vidíme, že Dodavatel 1 až Dodavatel 5 jsou rybárny a Odběratel 1 až Odběratel 5 jsou tržiště s rybími produkty. Nyní, pokud máme zadány data, stiskneme tlačítko Vyřeš. Po zpracování systémem Lingo, dostáváme tyto výsledky. (viz Obrázek 5-6).

Obrázek 5-6: Výsledky přiřazovací úlohy

Interpretace výsledků je následovná. Účelová funkce nám říká, že celkově bude dodáno 88 tun ryb na všech pět tržišť. První rybárna bude dodávat druhému tržišti, druhá rybárna - 38 -

čtvrtému tržišti atd. V případě nespokojenosti s výsledkem či chybou v zadání je možné opět díky tlačítku Opravit data tyto parametry změnit. V opačném případě aktivujeme tlačítko Zpět do menu, kde ukončíme aplikaci.

5.4 Řešení okružní dopravní úlohy Pro firmu Kontrola a.s. pracuje několik auditorů objíždějících výrobní závody potravin. Tuto firmu si najímá Česká zemědělská a potravinářská inspekce a Česká obchodní inspekce, pokud je podána stížnost na kvalitu výrobku. Auditorovi byly tento týden přiděleny ke kontrole čtyři pekárny. Podle tabulky vzdáleností (2.4) určete, jakou trasu by bylo nejvhodnější zvolit pro minimalizaci počtu najetých kilometrů. Po auditu všech závodů se auditor musí vrátit zpět na centrálu. (viz Příklad 2-4).

Ze zadání příkladu vyplývá, že se jedná o okružní dopravní problém. Proto si jako obvykle spustíme soubor MS Excel s názvem Bakalarska_prace.xls. Aktivujeme tlačítko Nabídka a zvolíme příslušný typ úlohy. Počet proměnných zde musí být stejný jako počet omezení a to pět. Jedno místo je výchozí bod, tedy centrála a další čtyři místa jsou pekárny. Program je koncipován tak, že v případě nerovnosti by nás to do zadání nepustilo. V případě potvrzení tlačítka OK se dostáváme na tabulku pro zadávání dat, která vypadá po zapsání kilometrových vzdáleností následovně. (viz Obrázek 5-7).

Obrázek 5-7: Zadání okružní dopravní úlohy

Jak můžeme pozorovat, zadání okružního dopravního problému musí být logicky symetrické. Na diagonále budou nuly. Místo 1 si představíme jako centrálu, místa 2 až 5 jako pekárny, které má auditor navštívit. Nyní stiskneme tlačítko Vyřeš a dostáváme tabulku výsledků. (viz Obrázek 5-8).

- 39 -

Obrázek 5-8: Výsledky okružní dopravní úlohy

Z účelové funkce plyne, že auditor najede 217 kilometrů. Po cestě navštíví pekárny v tomto pořadí. Centrála -> Pekárna 2 -> Pekárna 5 -> Pekárna 4 -> Pekárna 3. Následně se vrací z pekárny 3 zpět na centrálu. V případě zadání jiných kilometrových údajů, můžeme stisknout tlačítko Opravit data v opačném případě můžeme aplikaci ukončit.

- 40 -

6 Závěr Cílem této práce bylo seznámit čtenáře se základními typy distribučních úloh v rámci lineárního programování a vytvoření jednoduché avšak užitečné aplikace kombinující systémy Lingo a MS Excel.

Aplikaci jsem navrhnul pro řešení dopravního, alokačního, přiřazovacího a okružního dopravního problému. Na začátku jsem uvedl matematický model a popis každého problému. Popsal jsem, z čeho se jednotlivé modely skládají a také význam jejich proměnných. Ve třetí kapitole jsem představil systém Lingo, strukturu s jakou se do tohoto programovacího jazyku zapisuje, ale i řešení jednotlivých problémů v tomto systému. Čtvrtá kapitola pojednávala o tvorbě aplikace pomocí Visual Basic for Applications. Nastínil jsem tvorbu procedur, propojení systémů Lingo s MS Excel a také strukturu aplikace uvedené v příloze. V páté kapitole jsem vyřešil úlohy představené v kapitole dvě a interpretoval výsledky.

Mojí osobu tato práce obohatila především o poznatky z programování ve Visual Basic for Applications. Dále jsem si osvojil psaní formálních textů. Také jsem se zamyslel nad praktickým využitím programů pro lineární modelování, jakož i srovnání jednotlivých systémů pro řešení těchto úloh. Neubránil jsem se srovnání systému Lingo s MPL for Windows. Tyto systémy jsou v mnohém podobné, pouze MPL for Windows je uživatelsky příjemnější. Bohužel, v něm nemám takové zkušenosti, jako v systému Lingo. Oba systémy se ale spíše hodí pro řešení malých a středních úloh. Větší a komplikovanější problémy umí vyřešit pouze specializované komerční systémy. Těmi jsou, pokud jde o distribuci, například Inventory Wizard, Planning Wizard a také PLANTOUR Logistic.

Distribuční úlohy jsou praktickou ukázkou využití lineárního programování v praxi. S mnoha příklady z praxe jsem se také setkal i v kurzu Praktikum z operačního výzkumu, který považuji pro tvorbu své práce za velice přínosný, vzhledem k tvorbě úloh zde uvedených. Mé práci také výrazně pomohla literatura popsaná níže v seznamu použité literatury, kterou každému se zájmem o tuto problematiku vřele doporučuji.

Pevně doufám, že má práce byla pro čtenáře přínosem a dostatečně osvětlila problematiku distribučních úloh, případně čtenáře podnítila k modifikaci či rozšíření vytvořené aplikace o další typy nejen distribučních problémů. - 41 -

Seznam použité literatury [1]

JABLONSKÝ, J.: Programy pro matematické modelování. Nakladatelství Oeconomica, 2007.

[2]

LAGOVÁ, M., JABLONSKÝ, J.: Lineární modely. Nakladatelství Oeconomica, 2009.

[3]

BENÁČOVÁ, H. : Tvorba aplikací v MS EXCEL – Materiály ke cvičení. Nakladatelství Oeconomica, 2005.

[4]

MORKES, D.: Učebnice Visual Basicu 6.0. Computer Press, 2000.

- 42 -

Přílohy Zde uvedu pouze některé části programu. Do celého programu je možno nahlédnout v přiloženém CD. Modul pro ovládání: Sub Nabídka() ' Makro které vyvolá nabídku a zároveň vymaže Zadání i Výsledky Sheets("Zadani").Select Rows("14:90").Select Selection.Delete Shift:=xlUp Sheets("Vysledky").Select Rows("14:90").Select Selection.Delete Shift:=xlUp Sheets("Uvod").Select Load Hlavni_nabidka Hlavni_nabidka.Show End Sub

Sub Konec()

' Makro které se zeptá na ukončení aplikace a případně ji ukončí

Dim kon kon = MsgBox("Opravdu chcete ukončit aplikaci?", vbQuestion + vbYesNo, "Konec aplikace") If kon = vbYes Then Application.Quit End If End Sub

Sub NaUvod() ' Makro které provádí navigaci Sheets("Uvod").Select Range("A1").Select End Sub

Sub NaZadani() ' Makro které provádí navigaci Sheets("Zadani").Select Range("A1").Select End Sub

- 43 -

Modul pro test okružního dopravního problému: Sub Test_m_n() Var = m Var2 = n If Var = Var2 Then Call Tabulka3 If Var > Var2 Then Call Test_pokrac If Var < Var2 Then Call Test_pokrac2 End Sub

Sub Test_pokrac() Range("A1").Select Dim kon kon = MsgBox("Počet proměnných musí být stejný jako počet omezení ") End Sub Sub Test_pokrac2() Range("A1").Select Dim kon kon = MsgBox("Počet proměnných musí být stejný jako počet omezení ") End Sub

Modul pro tvorbu tabulek: Public m As Integer 'Počet omezeni - radku Public n As Integer 'Počet proměnných – sloupcu

Sub Test() 'Otestuje jakou úlohu si uživatel vybral a k tomu vybere příslušnou proceduru Range("A41").Select If ActiveCell.Value = "1" And m > 0 And n > 0 Then Call Tabulka1 If ActiveCell.Value = "2" And m > 0 And n > 0 Then Call Tabulka1

- 44 -

If ActiveCell.Value = "3" And m > 0 And n > 0 Then Call Tabulka2 If ActiveCell.Value = "4" And m > 0 And n > 0 Then Call Test_m_n End Sub

Sub Tabulka1() Sheets("Zadani").Select Call pozadavky Call kapacity Call hlavni_tabulka_zadani Call pojmenovani_tabulky_zadani Call ucelova_fce Call hlavni_tabulka_vysledky Call pojmenovani_tabulky_vysledku Call Pojmenovani_odberatele_promenne Call Pojmenovani_dodavatele_omezeni Call Pojmenovani_odberatele Call Pojmenovani_dodavatele End Sub Sub Tabulka2() Sheets("Zadani").Select Call hlavni_tabulka_zadani Call pojmenovani_tabulky_zadani Call ucelova_fce Call hlavni_tabulka_vysledky Call pojmenovani_tabulky_vysledku Call Pojmenovani_odberatele_promenne Call Pojmenovani_dodavatele_omezeni Call Pojmenovani_odberatele Call Pojmenovani_dodavatele End Sub

- 45 -

Sub Tabulka3() Sheets("Zadani").Select Call hlavni_tabulka_zadani Call pojmenovani_tabulky_zadani Call ucelova_fce Call hlavni_tabulka_vysledky Call pojmenovani_tabulky_vysledku Call pocet_pro_okruzni Call Pojmenovani_dodavatele_omezeni Call Pojmenovani_miston Call Pojmenovani_mistom End Sub

Modul pro propojení LINGO s MS Excel: Dim LINGO As Object

Sub Auto_Open() Set LINGO = CreateObject("LINGO.Document.4") End Sub Sub Test_vyreseni() 'Otestuje jakou úlohu si uživatel vybral a k tomu spustí příslušné řešení Sheets("Uvod").Select Range("A41").Select If ActiveCell.Value = "1" Then Call LINGOSolve_dopravni If ActiveCell.Value = "2" Then Call LINGOSolve_alokacni If ActiveCell.Value = "3" Then Call LINGOSolve_prirazovaci If ActiveCell.Value = "4" Then Call LINGOSolve_okruzni End Sub Sub LINGOSolve_dopravni()

- 46 -

Dim iErr As Integer iErr = LINGO.RunScriptRange("DOPRAVNI") ' Possible Error codes from RunScriptRange: ' 0 NO_ERR, ' 1 ERR_INVALID_INPUT, ' 2 ERR_UNUSED, ' 3 ERR_UNABLE_TO_OPEN_LOG_FILE, ' 4 ERR_NULL_SCRIPT, ' 5 ERR_INVALID_ARRAY_FORMAT, ' 6 ERR_INVALID_ARRAY_DIMENSION, ' 7 ERR_INVALID_ARRAY_BOUNDS, ' 8 ERR_UNABLE_TO_LOCK_DATA, ' 9 ERR_UNABLE_TO_ALLOCATE_MEMORY, '10 ERR_UNABLE_TO_CONFIGURE_SCRIPT_READER, '11 ERR_LINGO_IS_BUSY, '12 ERR_OLE_EXCEPTION, '13 ERR_UNABLE_TO_INITIALIZE_EXCEL, '14 ERR_UNABLE_TO_READ_EXCEL_RANGE, '15 ERR_UNABLE_TO_FIND_EXCEL_RANGE,

If (iErr > 0) Then MsgBox ("Unable to solve model") End If Sheets("Vysledky").Select Range("B14").Select End Sub

- 47 -