Transportná úloha v Exceli. Nájdenie najlepšieho spôsobu dopravy od predávajúceho ku kupujúcemu

Excel je veľmi funkčný program. Dá sa ním vyriešiť obrovská vrstva problémov, ktorým musí človek v biznise čeliť. Jednou z najbežnejších je doprava. Predstavte si, že musíme pochopiť, ktorý spôsob dopravy od výrobcu ku konečnému kupujúcemu je najoptimálnejší z hľadiska času, peňazí a iných zdrojov. Tento problém je pomerne populárny, bez ohľadu na to, v akom odvetví sa podnik nachádza. Pozrime sa preto bližšie na to, ako ho implementovať pomocou Excelu.

Popis prepravnej úlohy

Máme teda dve protistrany, ktoré spolu neustále interagujú. V našom prípade ide o kupujúceho a predávajúceho. Musíme prísť na to, ako prepravovať tovar tak, aby náklady boli minimálne. K tomu je potrebné prezentovať všetky údaje v schematickej alebo maticovej forme. V Exceli používame poslednú možnosť. Vo všeobecnosti existujú dva typy prepravných úloh:

1. ZATVORENÉ. V tomto prípade je ponuka a dopyt v rovnováhe.
2. OTVORENÉ. Tu neexistuje rovnosť medzi ponukou a dopytom. Ak chcete získať riešenie tohto problému, musíte ho najskôr priviesť k prvému typu, čím sa vyrovná ponuka a dopyt. Aby ste to dosiahli, musíte zaviesť ďalší ukazovateľ - prítomnosť podmieneného kupujúceho alebo predávajúceho. Okrem toho musíte vykonať určité zmeny v tabuľke nákladov.

Ako povoliť funkciu Nájsť riešenie v Exceli

Na riešenie dopravných problémov v Exceli existuje špeciálna funkcia s názvom „Hľadať riešenie“. V predvolenom nastavení nie je povolená, takže musíte vykonať nasledujúce kroky:

1. Otvorte ponuku „Súbor“, ktorá sa nachádza v ľavom hornom rohu okna programu.
2. Potom kliknite na tlačidlo s parametrami.
3. Ďalej nájdeme podsekciu „Nastavenia“ a prejdeme do ponuky správy doplnkov. Ide o malé programy, ktoré bežia v prostredí Microsoft Excel. Vidíme, že najprv sme klikli na ponuku „Doplnky“ a potom v pravej dolnej časti nastavili položku „Doplnky programu Excel“ a klikli na tlačidlo „Prejsť“. Všetky potrebné akcie sú zvýraznené červenými obdĺžnikmi a šípkami.
4. Ďalej zapnite doplnok „Vyhľadať riešenie“, po ktorom potvrdíme naše akcie stlačením tlačidla OK. Na základe popisu nastavenia vidíme, že je určený na analýzu zložitých údajov, ako sú vedecké a finančné.
5. Potom prejdite na kartu „Údaje“, kde vidíme nové tlačidlo, ktoré sa nazýva rovnako ako doplnok. Nájdete ho v skupine analytických nástrojov.

Zostáva iba kliknúť na toto tlačidlo a pristúpime k riešeniu problému dopravy. Predtým by sme si však mali povedať niečo viac o nástroji Riešiteľ v Exceli. Ide o špeciálny doplnok Excel, ktorý umožňuje nájsť najrýchlejšie riešenie problému. Charakteristickou črtou je zohľadnenie obmedzení, ktoré si užívateľ nastaví v štádiu prípravy. Zjednodušene povedané, ide o podprogram, ktorý umožňuje určiť najlepší spôsob dosiahnutia určitej úlohy. Takéto úlohy môžu zahŕňať:

1. Investovanie, nakladanie skladu alebo iná podobná činnosť. Vrátane dodania tovaru.
2. Najlepšia cesta. To zahŕňa ciele, ako je dosiahnutie maximálneho zisku pri minimálnych nákladoch, ako dosiahnuť najlepšiu kvalitu s dostupnými zdrojmi atď.

Okrem prepravných úloh sa tento doplnok používa aj na nasledujúce účely:

1. Vypracovanie výrobného plánu. To znamená, koľko jednotiek produktu je potrebné vyrobiť, aby sa dosiahol maximálny príjem.
2. Nájdite rozdelenie práce pre rôzne druhy práce tak, aby celkové náklady na výrobu produktu alebo služby boli najmenšie.
3. Nastavte minimálny čas potrebný na dokončenie celej práce.

Ako vidíte, úlohy sú veľmi odlišné. Univerzálnym pravidlom pre aplikáciu tohto doplnku je, že pred riešením problému je potrebné vytvoriť model, ktorý by zodpovedal kľúčovým charakteristikám daného problému. Model je súbor funkcií, ktoré používajú premenné ako svoje argumenty. Teda hodnoty, ktoré sa môžu meniť.

Je dôležité poznamenať, že optimalizácia súboru hodnôt sa vykonáva výlučne na jednom ukazovateli, ktorý sa nazýva cieľová funkcia.

Doplnok Riešiteľ vymenúva rôzne hodnoty premenných, ktoré sa prenášajú do cieľovej funkcie, takým spôsobom, že ide o maximálnu, minimálnu alebo rovnajúcu sa určitej hodnote (toto je presne obmedzenie). Existuje ďalšia funkcia, ktorá je vo svojom princípe činnosti trochu podobná a ktorá sa často zamieňa s „hľadaním riešenia“. Nazýva sa to „Výber možností“. Ak sa však pozriete hlbšie, rozdiel medzi nimi je obrovský:

1. Funkcia hľadania cieľa nepracuje s viac ako jednou premennou.
2. Neposkytuje možnosť stanoviť limity premenných.
3. Dokáže určiť len rovnosť účelovej funkcie k určitej hodnote, ale neumožňuje nájsť maximum a minimum. Preto nie je vhodný pre našu úlohu.
4. Dokáže efektívne vypočítať iba vtedy, ak je model lineárneho typu. Ak je model nelineárny, nájde hodnotu, ktorá je najbližšie k pôvodnej hodnote.

Transportná úloha je vo svojej štruktúre oveľa komplikovanejšia, takže doplnok „Výber parametrov“ na to nestačí. Pozrime sa bližšie na to, ako implementovať funkciu „Hľadať riešenie“ v praxi na príklade dopravného problému.

Príklad riešenia dopravného problému v Exceli

Aby sme názorne demonštrovali, ako riešiť dopravné problémy v praxi v Exceli, uvedieme príklad.

Úlohy podmienok

Predpokladajme, že máme 6 predajcov a 7 kupujúcich. Dopyt a ponuka medzi nimi sú rozdelené nasledovne: 36, 51, 32, 44, 35 a 38 jednotiek sú predávajúci a 33, 48, 30, 36, 33, 24 a 32 jednotiek sú kupujúci. Ak si zrátate všetky tieto hodnoty, zistíte, že ponuka a dopyt sú v rovnováhe. Preto je tento problém uzavretého typu, ktorý sa rieši veľmi jednoducho.

Okrem toho máme informácie o tom, koľko musíte minúť na prepravu z bodu A do bodu B (v príklade sú zvýraznené žltými bunkami).

Riešenie – krok za krokom algoritmus

Teraz, keď sme sa oboznámili s tabuľkami s počiatočnými údajmi, môžeme tento problém vyriešiť pomocou nasledujúceho algoritmu:

1. Najprv vytvoríme tabuľku pozostávajúcu zo 6 riadkov a 7 stĺpcov.
2. Potom prejdeme do ľubovoľnej bunky, ktorá neobsahuje žiadne hodnoty a zároveň leží mimo novovytvorenej tabuľky, a vložíme funkciu. Ak to chcete urobiť, kliknite na tlačidlo fx, ktoré sa nachádza naľavo od riadku na zadávanie funkcií.
3. Máme okno, v ktorom musíme vybrať kategóriu „Matematika“. O akú funkciu máme záujem? Ten, ktorý je zvýraznený na tejto snímke obrazovky. Funkcia SUMPRODUCT násobí rozsahy alebo polia medzi sebou a sčítava ich. Presne to, čo potrebujeme. Potom stlačte tlačidlo OK.
4. Ďalej sa na obrazovke zobrazí okno, v ktorom musíte zadať parametre funkcie. Sú to nasledovné:
   1. Pole 1. Toto je prvý argument, do ktorého zapíšeme rozsah, ktorý je zvýraznený žltou farbou. Parametre funkcie môžete nastaviť buď pomocou klávesnice alebo výberom príslušnej oblasti ľavým tlačidlom myši.
   2. Pole 2. Toto je druhý argument, ktorým je novovytvorená tabuľka. Akcie sa vykonávajú rovnakým spôsobom.

Potvrďte svoju akciu stlačením tlačidla OK.

1. Potom klikneme ľavým tlačidlom myši na bunku, ktorá slúži ako vľavo hore v novovytvorenej tabuľke. Teraz znova kliknite na tlačidlo funkcie vložiť.
2. Vyberieme rovnakú kategóriu ako v predchádzajúcom prípade. Nás ale tentokrát zaujíma funkcia SUM.
3. Teraz prichádza fáza vypĺňania argumentov. Ako prvý argument napíšeme horný riadok tabuľky, ktorú sme vytvorili na začiatku. Rovnakým spôsobom ako predtým to možno vykonať výberom týchto buniek na hárku alebo ručne. Svoje akcie potvrdíme stlačením tlačidla OK.
4. Výsledky uvidíme v bunke s funkciou. V tomto prípade je to nula. Potom presuňte kurzor do pravého dolného rohu, po ktorom sa zobrazí značka automatického dopĺňania. Vyzerá ako malý čierny plyš. Ak sa objaví, podržte ľavé tlačidlo myši a presuňte kurzor na poslednú bunku v našej tabuľke.
5. To nám dáva možnosť preniesť vzorec do všetkých ostatných buniek a získať správne výsledky bez toho, aby sme museli vykonávať ďalšie výpočty.
6. Ďalším krokom je vybrať ľavú hornú bunku a prilepiť funkciu SUM do nej. Potom zadáme argumenty a pomocou značky automatického dopĺňania vyplníme všetky zostávajúce bunky.
7. Potom pristúpime priamo k riešeniu problému. Na tento účel použijeme doplnok, ktorý sme zahrnuli skôr. Prejdite na kartu „Údaje“ a tam nájdeme nástroj „Hľadať riešenie“. Klikneme na toto tlačidlo.
8. Teraz sa nám pred očami objavilo okno, cez ktoré si môžete nakonfigurovať parametre nášho doplnku. Pozrime sa na každú z týchto možností:
   1. Optimalizujte účelovú funkciu. Tu musíme vybrať bunku obsahujúcu funkciu SUMPRODUCT. Vidíme, že táto možnosť umožňuje vybrať funkciu, pre ktorú sa bude hľadať riešenie.
   2. Predtým. Tu nastavíme možnosť „Minimum“.
   3. Zmenou buniek premenných. Tu uvádzame rozsah zodpovedajúci tabuľke, ktorú sme vytvorili na samom začiatku (s výnimkou sumarizujúceho riadka a stĺpca).
   4. S výhradou obmedzení. Tu musíme pridať obmedzenia kliknutím na tlačidlo Pridať.
   5. Pamätáme si, aký druh obmedzenia musíme vytvoriť – súčet hodnôt požiadaviek kupujúcich a ponúk predávajúcich musí byť rovnaký.
9. Úloha obmedzení sa vykonáva takto:
   1. Odkaz na bunky. Tu zadáme rozsah tabuľky pre výpočty.
   2. Podmienky. Ide o matematickú operáciu, podľa ktorej sa kontroluje rozsah uvedený v prvom vstupnom poli.
   3. Hodnota podmienky alebo obmedzenia. Tu zadáme príslušný stĺpec v zdrojovej tabuľke.
   4. Po dokončení všetkých krokov kliknite na tlačidlo OK, čím potvrdíte naše akcie.

Vykonávame presne rovnaké operácie pre horné riadky, pričom nastavíme nasledujúcu podmienku: musia byť rovnaké.

Ďalším krokom je nastavenie podmienok. Pre súčet buniek v tabuľke musíme nastaviť nasledujúce kritériá – väčší alebo rovný nule, celé číslo. V dôsledku toho máme taký zoznam podmienok, za ktorých je problém vyriešený. Tu sa musíte uistiť, že je začiarknuté políčko vedľa možnosti „Urobiť premenné bez limitov ako nezáporné“. Aj v našej situácii je potrebné zvoliť metódu riešenia problému – „Hľadanie riešenia nelineárnych problémov OPG metód“. Teraz môžeme bezpečne povedať, že nastavenie je hotové. Zostáva teda iba vykonať výpočty. Ak to chcete urobiť, kliknite na tlačidlo „Nájsť riešenie“.

Potom sa všetky údaje vypočítajú automaticky a potom Excel zobrazí okno s výsledkami. Je potrebné skontrolovať činnosť počítača, pretože chyby sú možné, ak boli podmienky predtým nastavené nesprávne. Ak je všetko správne, kliknite na tlačidlo „OK“ a pozrite si hotovú tabuľku.

Ak sa ukáže, že naša úloha sa stala otvoreným typom, je to zlé, pretože musíte upraviť zdrojovú tabuľku tak, aby sa úloha zmenila na uzavretú. Keď sa to však urobí, zostávajúci algoritmus bude rovnaký.

záver

Ako vidíte, Excel sa dá použiť aj na veľmi zložité výpočty, ktoré na prvý pohľad nie sú dostupné pre jednoduchý počítačový program, ktorý má nainštalovaný takmer každý. Avšak, je. Dnes sme už prebrali pokročilú úroveň používania. Táto téma nie je taká jednoduchá, ale ako sa hovorí, cestu zvládne aj ten peší. Hlavná vec je dodržiavať akčný plán a presne vykonávať všetky vyššie uvedené akcie. Potom nebudú žiadne chyby a program nezávisle vykoná všetky potrebné výpočty. Nebude potrebné premýšľať o tom, ktorú funkciu použiť a podobne.