Transportní úloha v Excelu. Nalezení nejlepšího způsobu dopravy od prodávajícího ke kupujícímu

Excel je velmi funkční program. Dá se s ním vyřešit obrovská vrstva problémů, kterým musí člověk v podnikání čelit. Jednou z nejčastějších je doprava. Představte si, že potřebujeme pochopit, který způsob dopravy od výrobce ke konečnému kupujícímu je nejoptimálnější z hlediska času, peněz a dalších zdrojů. Tento problém je poměrně populární, bez ohledu na to, v jakém odvětví podnikání působí. Pojďme se proto blíže podívat na to, jak jej implementovat pomocí Excelu.

Popis přepravního úkolu

Máme tedy dvě protistrany, které spolu neustále interagují. V našem případě se jedná o kupujícího a prodávajícího. Musíme vymyslet, jak přepravit zboží tak, aby náklady byly minimální. K tomu je potřeba prezentovat všechna data ve schematické nebo maticové formě. V Excelu používáme druhou možnost. Obecně existují dva typy přepravních úkolů:

1. ZAVŘENO. V tomto případě je nabídka a poptávka v rovnováze.
2. OTEVŘENO. Není zde žádná rovnost mezi nabídkou a poptávkou. Chcete-li získat řešení tohoto problému, musíte jej nejprve přivést k prvnímu typu, vyrovnat nabídku a poptávku. Chcete-li to provést, musíte zavést další ukazatel - přítomnost podmíněného kupujícího nebo prodávajícího. Kromě toho musíte provést určité změny v tabulce nákladů.

Jak povolit funkci Najít řešení v Excelu

Pro řešení dopravních problémů v Excelu existuje speciální funkce nazvaná „Hledat řešení“. Ve výchozím nastavení není povoleno, takže musíte provést následující kroky:

1. Otevřete nabídku „Soubor“, která se nachází v levém horním rohu okna programu.
2. Poté klikněte na tlačítko s parametry.
3. Dále najdeme podsekci „Nastavení“ a přejděte do nabídky správy doplňků. Jedná se o malé programy, které běží v prostředí Microsoft Excel. Vidíme, že jsme nejprve klikli na nabídku „Doplňky“ a poté v pravé dolní části nastavili položku „Doplňky Excelu“ a klikli na tlačítko „Přejít“. Všechny potřebné akce jsou zvýrazněny červenými obdélníky a šipkami.
4. Dále zapněte doplněk „Vyhledat řešení“, poté potvrdíme naše akce stisknutím tlačítka OK. Na základě popisu nastavení vidíme, že je určen k analýze složitých dat, jako jsou vědecká a finanční.
5. Poté přejděte na kartu „Data“, kde vidíme nové tlačítko, které se nazývá stejně jako doplněk. Nachází se ve skupině analytických nástrojů.

Zbývá pouze kliknout na toto tlačítko a přistoupíme k řešení dopravního problému. Předtím bychom si ale měli povědět něco více o nástroji Řešitel v Excelu. Jedná se o speciální doplněk Excel, který umožňuje najít nejrychlejší řešení problému. Charakteristickým rysem je zohlednění omezení, která si uživatel nastaví ve fázi přípravy. Jednoduše řečeno, jedná se o podprogram, který umožňuje určit nejlepší způsob, jak dosáhnout určitého úkolu. Takové úkoly mohou zahrnovat následující:

1. Investování, nakládání skladu nebo jakákoli jiná podobná činnost. Včetně dodání zboží.
2. Nejlepší způsob. To zahrnuje cíle, jako je dosažení maximálního zisku při minimálních nákladech, jak dosáhnout nejlepší kvality s dostupnými zdroji a tak dále.

Kromě přepravních úkolů se tento doplněk používá také pro následující účely:

1. Vypracování plánu výroby. To znamená, kolik jednotek produktu je třeba vyrobit, aby bylo dosaženo maximálního příjmu.
2. Najděte rozložení práce pro různé druhy práce tak, aby celkové náklady na výrobu produktu nebo služby byly co nejmenší.
3. Nastavte minimální dobu, kterou bude trvat dokončení všech prací.

Jak vidíte, úkoly jsou velmi odlišné. Univerzálním pravidlem pro aplikaci tohoto doplňku je, že před řešením problému je nutné vytvořit model, který by odpovídal klíčovým charakteristikám nastoleného problému. Model je kolekce funkcí, které používají proměnné jako své argumenty. Tedy hodnoty, které se mohou měnit.

Je důležité si uvědomit, že optimalizace souboru hodnot se provádí výhradně na jednom ukazateli, který se nazývá cílová funkce.

Doplněk Řešitel vyjmenovává různé hodnoty proměnných, které jsou předány cílové funkci, takovým způsobem, že je maximální, minimální nebo rovna určité hodnotě (toto je přesně omezení). Existuje další funkce, která je svým principem fungování poněkud podobná a která je často zaměňována s „Hledat řešení“. Říká se tomu „Výběr možností“. Ale pokud se ponoříte hlouběji, rozdíl mezi nimi je obrovský:

1. Funkce hledání cíle nefunguje s více než jednou proměnnou.
2. Neposkytuje možnost stanovit limity pro proměnné.
3. Je schopen určit pouze rovnost účelové funkce k určité hodnotě, ale neumožňuje najít maximum a minimum. Proto se pro náš úkol nehodí.
4. Dokáže efektivně vypočítat pouze v případě, že model lineárního typu. Pokud je model nelineární, pak najde hodnotu, která je nejbližší původní hodnotě.

Transportní úloha je ve své struktuře mnohem složitější, takže doplněk „Výběr parametrů“ na to nestačí. Podívejme se blíže na to, jak v praxi implementovat funkci „Hledat řešení“ na příkladu dopravního problému.

Příklad řešení dopravního problému v Excelu

Abychom názorně předvedli, jak v praxi řešit dopravní problémy v Excelu, uveďme příklad.

Úkoly podmínek

Předpokládejme, že máme 6 prodávajících a 7 kupujících. Poptávka a nabídka jsou mezi nimi rozděleny následujícím způsobem: 36, 51, 32, 44, 35 a 38 jednotek jsou prodávající a 33, 48, 30, 36, 33, 24 a 32 jednotek jsou kupující. Pokud všechny tyto hodnoty sečtete, zjistíte, že nabídka a poptávka jsou v rovnováze. Tento problém je tedy uzavřeného typu, který je řešen velmi jednoduše.

Kromě toho máme informace o tom, kolik musíte utratit za přepravu z bodu A do bodu B (v příkladu jsou zvýrazněny žlutými buňkami).

Řešení – krok za krokem algoritmus

Nyní, když jsme se seznámili s tabulkami s počátečními daty, můžeme tento problém vyřešit pomocí následujícího algoritmu:

1. Nejprve vytvoříme tabulku skládající se ze 6 řádků a 7 sloupců.
2. Poté přejdeme do libovolné buňky, která neobsahuje žádné hodnoty a zároveň leží mimo nově vytvořenou tabulku, a vložíme funkci. Chcete-li to provést, klikněte na tlačítko fx, které se nachází vlevo od řádku pro zadání funkce.
3. Máme okno, ve kterém musíme vybrat kategorii „Matematika“. O jakou funkci máme zájem? Ten, který je zvýrazněn na tomto snímku obrazovky. Funkce SUMPRODUCT násobí rozsahy nebo pole mezi sebou a sečte je. Přesně to, co potřebujeme. Poté stiskněte tlačítko OK.
4. Dále se na obrazovce objeví okno, ve kterém je třeba zadat parametry funkce. Jsou to následující:
   1. Pole 1. Toto je první argument, do kterého zapíšeme rozsah, který je zvýrazněn žlutě. Parametry funkce můžete nastavit buď pomocí klávesnice, nebo výběrem příslušné oblasti levým tlačítkem myši.
   2. Pole 2. Toto je druhý argument, kterým je nově vytvořená tabulka. Akce se provádějí stejným způsobem.

Potvrďte svou akci stisknutím tlačítka OK.

1. Poté klikneme levým tlačítkem myši na buňku, která v nově vytvořené tabulce slouží jako vlevo nahoře. Nyní znovu klikněte na tlačítko funkce vložit.
2. Vybereme stejnou kategorii jako v předchozím případě. Nás ale tentokrát zajímá funkce SOUČET.
3. Nyní přichází fáze vyplňování argumentů. Jako první argument napíšeme horní řádek tabulky, kterou jsme vytvořili na začátku. Stejným způsobem jako dříve to lze provést výběrem těchto buněk na listu nebo ručně. Naše akce potvrdíme stisknutím tlačítka OK.
4. Výsledky uvidíme v buňce s funkcí. V tomto případě je to nula. Dále přesuňte kurzor do pravého dolního rohu, po kterém se objeví značka automatického dokončování. Vypadá jako malý černý plyš. Pokud se objeví, podržte levé tlačítko myši a přesuňte kurzor na poslední buňku v naší tabulce.
5. To nám dává možnost přenést vzorec do všech ostatních buněk a získat správné výsledky, aniž bychom museli provádět další výpočty.
6. Dalším krokem je výběr levé horní buňky a vložení funkce SOUČET do ní. Poté zadáme argumenty a pomocí značky automatického doplňování vyplníme všechny zbývající buňky.
7. Poté přistoupíme přímo k řešení problému. K tomu použijeme doplněk, který jsme zahrnuli dříve. Přejděte na kartu „Data“ a tam najdeme nástroj „Hledat řešení“. Klikneme na toto tlačítko.
8. Nyní se nám před očima objevilo okno, přes které můžete konfigurovat parametry našeho doplňku. Podívejme se na každou z těchto možností:
   1. Optimalizujte účelovou funkci. Zde musíme vybrat buňku obsahující funkci SUMPRODUCT. Vidíme, že tato volba umožňuje vybrat funkci, pro kterou se bude hledat řešení.
   2. Před. Zde nastavíme možnost „Minimum“.
   3. Změnou buněk proměnných. Zde uvádíme rozsah odpovídající tabulce, kterou jsme vytvořili na samém začátku (s výjimkou sumarizujícího řádku a sloupce).
   4. Podléhá omezením. Zde musíme přidat omezení kliknutím na tlačítko Přidat.
   5. Pamatujeme si, jaký druh omezení musíme vytvořit – součet hodnot požadavků kupujících a nabídek prodávajících musí být stejný.
9. Úkol omezení se provádí takto:
   1. Odkaz na buňky. Zde zadáme rozsah tabulky pro výpočty.
   2. Podmínky. Jedná se o matematickou operaci, podle které se kontroluje rozsah zadaný v prvním vstupním poli.
   3. Hodnota podmínky nebo omezení. Zde zadáme příslušný sloupec ve zdrojové tabulce.
   4. Po dokončení všech kroků klikněte na tlačítko OK, čímž potvrdíte naše akce.

Pro horní řádky provádíme úplně stejné operace, přičemž nastavíme následující podmínku: musí se rovnat.

Dalším krokem je nastavení podmínek. Pro součet buněk v tabulce musíme nastavit následující kritéria – větší nebo roven nule, celé číslo. V důsledku toho máme takový seznam podmínek, za kterých je problém vyřešen. Zde se musíte ujistit, že je zaškrtnuto políčko vedle možnosti „Udělat proměnné bez limitů jako nezáporné“. Také v naší situaci je požadováno, aby byla zvolena metoda řešení problému – „Hledání řešení nelineárních problémů OPG metod“. Nyní můžeme bezpečně říci, že nastavení je hotovo. Zbývá tedy pouze provést výpočty. Chcete-li to provést, klikněte na tlačítko „Najít řešení“.

Poté se všechna data spočítají automaticky a poté Excel zobrazí okno s výsledky. Je nutné znovu zkontrolovat provoz počítače, protože jsou možné chyby, pokud byly podmínky dříve nastaveny nesprávně. Pokud je vše v pořádku, klikněte na tlačítko „OK“ a podívejte se na hotovou tabulku.

Pokud se ukáže, že se náš úkol stal otevřeným typem, je to špatné, protože je třeba upravit zdrojovou tabulku tak, aby se úloha změnila na uzavřenou. Když to však uděláte, zbývající algoritmus bude stejný.

Proč investovat do čističky vzduchu?

Jak vidíte, Excel lze použít i pro velmi složité výpočty, které na první pohled jednoduchý počítačový program, který má nainstalovaný téměř každý, nemá. Nicméně je. Dnes jsme již probrali pokročilou úroveň použití. Toto téma není tak jednoduché, ale jak se říká, cestu zvládne ten pěší. Hlavní věcí je dodržovat akční plán a přesně provádět všechny výše uvedené akce. Pak nebudou žádné chyby a program nezávisle provede všechny potřebné výpočty. Nebude potřeba přemýšlet, jakou funkci použít a podobně.