Úloha dopravy v aplikaci Microsoft Excel

Dopravní úlohou je hledat nejoptimálnější variantu přepravy stejného druhu zboží od dodavatele ke spotřebiteli. Jeho základem je model široce používaný v různých oblastech matematiky a ekonomie. V aplikaci Microsoft Excel existují nástroje, které značně usnadňují řešení přepravního úkolu. Zjistíme, jak je používat v praxi.

Obecný popis přepravní úlohy

Hlavním cílem přepravního úkolu je najít optimální dopravní plán od dodavatele k spotřebiteli za minimální cenu. Podmínky takového problému jsou psány ve formě obvodu nebo matice. Pro aplikaci Excel se používá typ matice.

Pokud se celkový objem zboží v dodavatelských skladech rovná poptávkové hodnotě, dopravní úkol se nazývá uzavřený. Pokud tyto ukazatele nejsou stejné, pak se taková dopravní úloha nazývá otevřená. K vyřešení je třeba omezit podmínky na uzavřený typ. Chcete-li to provést, přidejte fiktivní prodejce nebo fiktivní kupující se zásobami nebo potřebami, které se rovnají rozdílu mezi nabídkou a poptávkou v reálné situaci. V tomto případě je do tabulky nákladů přidán další sloupec nebo řádek s nulovými hodnotami.

Nástroje pro řešení problému dopravy v aplikaci Excel

Chcete-li vyřešit dopravní problém v aplikaci Excel, použije se funkce "Najít řešení" . Problém je, že je ve výchozím nastavení zakázán. Chcete-li povolit tento nástroj, musíte provést určité akce.

1. Přejdeme na kartu "Soubor" .
2. Klepněte na podsekci "Parametry" .
3. V novém okně přejděte na nápis "Doplňky".
4. V rozevíracím seznamu "Správa" , který se nachází v dolní části otevřeného okna, zastavíme výběr v položce "Doplňky aplikace Excel" . Klikněte na tlačítko "jít ..." .
5. Otevře se okno pro aktivaci doplňků. Zaškrtněte políčko vedle možnosti "Hledání řešení" . Klikněte na tlačítko "OK" .
6. V důsledku těchto akcí se na kartě "Data" v bloku nastavení "Analýza" na pásu objeví tlačítko "Najít řešení" . To budeme potřebovat při hledání řešení dopravního problému.

Lekce: Najděte řešení v aplikaci Excel

Příklad řešení dopravní úlohy v aplikaci Excel

Nyní se podívejme na konkrétní příklad řešení dopravního problému.

Pracovní podmínky

Máme 5 dodavatelů a 6 kupujících. Objem výroby těchto dodavatelů činí 48, 65, 51, 61, 53 jednotek. Potřeba kupujících: 43, 47, 42, 46, 41, 59 jednotek. Celkový objem dodávek se tedy rovná poptávkové hodnotě, tj. Jedná se o uzavřený přepravní úkol.

Kromě toho je podmínkou uvedena matice nákladů na dopravu z jednoho místa do druhého, což je na obrázku níže znázorněno zeleně.

Řešení problému

Před námi je za výše uvedených podmínek úkolem snížit náklady na dopravu na minimum.

1. Abychom tento problém vyřešili, sestavujeme tabulku s přesně stejným počtem buněk jako výše popsaná matice nákladů.
2. Vyberte libovolnou prázdnou buňku na listu. Klikněte na ikonu "Vložit funkci" umístěnou vlevo od řádku vzorce.
3. Otevře se "Průvodce funkcí". V seznamu, který navrhuje, bychom měli najít funkci SUMPRODUCT . Vyberte jej a klikněte na tlačítko "OK" .
4. Otevírá okno vstupu pro funkci SUMPROSE . Jako první argument představujeme rozsah buněk v matici nákladů. Chcete-li to provést, stačí zvýraznit data buňky kurzorem. Druhým argumentem je rozsah buněk v tabulce, která byla připravena pro výpočty. Poté klikněte na tlačítko "OK" .
5. Klikněte na buňku, která je umístěna nalevo od levého horního sloupce tabulky pro výpočty. Stejně jako naposledy voláme Průvodce funkcí, otevřeme v něm argumenty funkce SUM . Kliknutím na pole prvního argumentu vyberete pro výpočty celý horní řádek buněk tabulky. Po zadání jejich souřadnic do příslušného pole klikněte na tlačítko "OK" .
6. Stane se pravý dolní roh článku s funkcí SUM . Zobrazí se popisovač výplně. Klepněte na levé tlačítko myši a přetáhněte rukojeť výplně dolů na konec tabulky pro výpočet. Tak jsme zformulovali vzorec.
7. Klepněte na buňku umístěnou nad levou horní buňkou tabulky pro výpočty. Stejně jako v předchozím čase nazýváme funkci SUM , ale tentokrát použijeme první argument sloupce tabulky pro výpočty. Klikněte na tlačítko "OK" .
8. Zkopírujte token výplně na vzorec pro celý řádek.
9. Přejděte na kartu "Data" . V panelu nástrojů "Analýza" klikněte na tlačítko "Najít řešení" .
10. Zobrazí se možnosti hledání řešení. V poli "Optimalizovat cílovou funkci" zadejte buňku obsahující funkci SUMPRODUCT . V bloku "Do" nastavte hodnotu na hodnotu "Minimum" . V poli "Změna buněk proměnných" určujeme pro výpočet celý rozsah tabulky. V bloku nastavení "V souladu s omezeními" klikněte na tlačítko "Přidat" a přidejte několik důležitých omezení.
11. Otevře se okno pro přidání omezení. Nejprve je třeba přidat podmínku, že součet dat v řádcích tabulky pro výpočty by měl být roven součtu dat v řádcích tabulky s podmínkou. Do pole "Odkaz buňky" zadejte rozsah součtu v řádcích tabulky výpočtů. Potom nastavte značku rovnosti (=). Do pole "Limit" zadejte rozsah součtů v řádcích tabulky s podmínkou. Poté klikněte na tlačítko "OK" .
12. Podobně přidáváme podmínku, že sloupce dvou tabulek by se měly rovnat navzájem. Přidáme omezení, že součet rozsahu všech buněk v tabulce pro výpočet by měl být větší nebo roven 0, a také podmínku, že musí být celé číslo. Obecný názor na omezení by měl být uveden na následujícím obrázku. Nezapomeňte zkontrolovat, zda v blízkosti položky "Provést proměnné bez omezení bez vylučování" byla zaškrtávací značka a metoda řešení byla "Vyhledat řešení nelineárních problémů metodou OPG" . Po zadání všech nastavení klikněte na tlačítko "Najít řešení" .
13. Poté se provede výpočet. Data se vyvedou do buněk tabulky pro výpočet. Zobrazí se okno výsledků hledání řešení. Pokud jsou výsledky uspokojivé, klikněte na tlačítko "OK" .

Jak je vidět, řešení přepravní úlohy v aplikaci Excel je omezeno na správnou tvorbu vstupních dat. Výpočty jsou prováděny programem místo uživatele.

• ← Částka ve slovech v aplikaci Microsoft Excel
• Logické funkce v aplikaci Microsoft Excel →