Transportná úloha v programe Microsoft Excel
Prepravnou úlohou je hľadanie najoptimálnejšieho variantu prepravy toho istého druhu tovaru od dodávateľa po spotrebiteľa. Jeho základom je model široko používaný v rôznych oblastiach matematiky a ekonómie. V programe Microsoft Excel existujú nástroje, ktoré výrazne uľahčujú riešenie dopravnej úlohy. Zistíme, ako ich používať v praxi.
obsah
Všeobecný opis prepravnej úlohy
Hlavným cieľom dopravnej úlohy je nájsť optimálny prepravný plán od dodávateľa k spotrebiteľovi za minimálne náklady. Podmienky takéhoto problému sú napísané vo forme obvodu alebo matice. Pre program Excel sa používa typ matice.
Ak sa celkový objem tovaru v skladoch dodávateľa rovná požadovanej hodnote, dopravná úloha sa nazýva uzavretá. Ak nie sú tieto ukazovatele rovnaké, takáto prepravná úloha sa nazýva otvorená. Na vyriešenie tohto problému by mali byť podmienky obmedzené na uzavretý typ. Za týmto účelom pridajte fiktívneho predajcu alebo fiktívneho kupujúceho so zásobami alebo potrebami, ktoré sa rovnajú rozdielu medzi ponukou a dopytom v reálnej situácii. V tomto prípade sa do tabuľky s nákladmi pridá ďalší stĺpec alebo riadok s nulovými hodnotami.
Nástroje na riešenie dopravného problému v programe Excel
Na vyriešenie problému dopravy v aplikácii Excel sa používa funkcia "Nájsť riešenia" . Problém je, že je predvolene vypnutý. Ak chcete tento nástroj povoliť, musíte vykonať určité kroky.
- Prejdeme na kartu "Súbor" .
- Klikneme na podsekciu "Parametre" .
- V novom okne prejdite na nápis "Doplnky" .
- V rozbaľovacom zozname "Správa" , ktorý sa nachádza v dolnej časti otvoreného okna, zastavíme výber na položke "Doplnky programu Excel" . Kliknite na tlačidlo "Choď ..." .
- Otvorí sa okno aktivácie doplnkov. Začiarknite políčko vedľa položky "Hľadanie riešenia" . Kliknite na tlačidlo "OK" .
- Kvôli týmto činnostiam sa na karte "Data" v bloku nastavení "Analýza" na pásiku objaví tlačidlo "Nájsť riešenia" . Budeme to potrebovať pri hľadaní riešenia dopravného problému.
Lekcia: Nájdite riešenie v programe Excel
Príklad riešenia dopravnej úlohy v programe Excel
Teraz sa pozrime na konkrétny príklad riešenia dopravného problému.
Podmienky úlohy
Máme 5 dodávateľov a 6 kupcov. Objem výroby týchto dodávateľov je 48, 65, 51, 61, 53 jednotiek. Potreba kupujúcich: 43, 47, 42, 46, 41, 59 jednotiek. Celkový objem dodávky sa teda rovná dopytu, to znamená, že ide o uzavretú dopravnú úlohu.
Okrem toho je podmienkou uvedená matica nákladov na dopravu z jedného miesta do druhého, čo je na zelenom obrázku nižšie znázornené.
Riešenie problému
Pred našou úlohou je za vyššie uvedených podmienok úlohou znížiť náklady na dopravu na minimum.
- Aby sme tento problém vyriešili, vytvoríme tabuľku s presne rovnakým počtom buniek ako vyššie opísaná matica nákladov.
- Vyberte prázdnu bunku na hárku. Kliknite na ikonu "Vložiť funkciu" umiestnenú vľavo od riadku vzorca.
- Otvorí sa "Sprievodca funkciami". V zozname, ktorý navrhuje, by sme mali nájsť funkciu SUMPRODUCT . Vyberte ho a kliknite na tlačidlo "OK" .
- Otvorí okno vstupu pre funkciu SUMPROSE . Ako prvý argument predstavujeme rozsah buniek v matici nákladov. Ak to chcete urobiť, stačí, ak kurzorom zvýrazníte údaje o bunkách. Druhým argumentom je rozsah buniek v tabuľke, ktorá bola pripravená na výpočty. Potom kliknite na tlačidlo "OK" .
- Kliknite na bunku, ktorá sa nachádza naľavo od ľavej hornej bunky tabuľky pre výpočty. Tak ako naposledy voláme funkciu Wizard, otvoríme v ňom argumenty funkcie SUM . Kliknutím na pole prvého argumentu vyberiete celú hornú rad buniek tabuľky pre výpočty. Po zadaní ich súradníc do príslušného poľa kliknite na tlačidlo "OK" .
- Stane sa pravý dolný roh článku s funkciou SUM . Zobrazí sa popisovač plnenia. Kliknite na ľavé tlačidlo myši a presuňte rukoväť výplne smerom nadol na koniec tabuľky a vypočítajte. Tak sme skopírovali vzorec.
- Klikneme na bunku umiestnenú nad hornou ľavou bunkou tabuľky pre výpočty. Rovnako ako v predchádzajúcom čase nazývame funkciu SUM , ale tentoraz použijeme prvý stĺpec tabuľky na výpočty ako argument. Kliknite na tlačidlo "OK" .
- Skopírujte tok výplne do vzorca pre celý riadok.
- Prejdite na kartu "Dáta" . V karte "Analýza" kliknite na tlačidlo "Nájsť riešenie" .
- Zobrazia sa možnosti hľadania riešenia. V poli "Optimalizovať cieľovú funkciu" zadajte bunku obsahujúcu funkciu SUMPRODUCT . V bloku "To" nastavte hodnotu na "Minimum" . V poli "Zmena buniek premenných" určujeme celý rozsah tabuľky na výpočet. V bloku nastavení "V súlade s obmedzeniami" kliknite na tlačidlo "Pridať" a pridajte niekoľko dôležitých obmedzení.
- Otvorí sa okno pre pridanie obmedzenia. Po prvé, musíme pridať podmienku, že súčet údajov v riadkoch tabuľky pre výpočty by sa mal rovnať súčtu dát v riadkoch tabuľky s podmienkou. V poli "Odkaz na bunku" zadajte rozsah súčtu v riadkoch výpočtovej tabuľky. Potom nastavte značku rovnosti (=). Do poľa "Limit" zadajte rozsah súčtov v riadkoch tabuľky s podmienkou. Potom kliknite na tlačidlo "OK" .
- Podobne pridáme podmienku, že stĺpce dvoch tabuliek by mali byť rovnaké. Pridáme obmedzenie, že súčet rozsahu všetkých buniek v tabuľke pre výpočet by mal byť väčší alebo rovný 0 a tiež podmienka, že musí byť celé číslo. Všeobecný pohľad na obmedzenia by mal byť taký, ako je znázornené na obrázku nižšie. Nezabudnite skontrolovať, či sa v blízkosti položky "Make variables without restrictions nonnegative" nachádza začiarknutie a metóda riešenia bola "Hľadanie riešenia nelineárnych problémov metódou OPG" . Po zadaní všetkých nastavení kliknite na tlačidlo "Nájsť riešenie" .
- Potom sa uskutoční výpočet. Údaje sa vyvedú do buniek tabuľky na výpočet. Zobrazí sa okno výsledkov vyhľadávania riešení. Ak sú výsledky uspokojivé, kliknite na tlačidlo "OK" .
Ako vidíte, riešenie úlohy prepravy v aplikácii Excel sa znižuje na správnu tvorbu vstupných údajov. Výpočty sú vykonávané programom namiesto používateľa.