Transportni zadatak u Excelu. Pronalaženje najboljeg načina transporta od prodavca do kupca

Excel je veoma funkcionalan program. Može se koristiti za rješavanje ogromnog sloja problema sa kojima se čovjek mora suočiti u poslovanju. Jedan od najčešćih je transport. Zamislite da treba da shvatimo koji način transporta od proizvođača do krajnjeg kupca je najoptimalniji u smislu vremena, novca i drugih resursa. Ovaj problem je prilično popularan, bez obzira u kojoj se industriji posluje. Stoga, hajde da pobliže pogledamo kako ga implementirati koristeći Excel.

Opis transportnog zadatka

Dakle, imamo dvije druge ugovorne strane koje su u stalnoj interakciji jedna s drugom. U našem slučaju to su kupac i prodavac. Moramo smisliti kako transportirati robu na način da troškovi budu minimalni. Da biste to učinili, trebate sve podatke prikazati u šematskom ili matričnom obliku. U Excelu koristimo posljednju opciju. Generalno, postoje dvije vrste transportnih zadataka:

1. Zatvoreno. U ovom slučaju ponuda i potražnja su u ravnoteži.
2. Otvori. Ovdje nema jednakosti između ponude i potražnje. Da biste dobili rješenje za ovaj problem, prvo ga morate dovesti do prvog tipa, izjednačavajući ponudu i potražnju. Da biste to učinili, morate uvesti dodatni indikator - prisustvo uslovnog kupca ili prodavca. Osim toga, potrebno je izvršiti određene promjene u tabeli troškova.

Kako omogućiti funkciju Find Solution u Excelu

Za rješavanje problema transporta u Excelu postoji posebna funkcija pod nazivom „Traži rješenje“. Nije omogućeno prema zadanim postavkama, tako da morate učiniti sljedeće korake:

1. Otvorite meni „Datoteka“ koji se nalazi u gornjem levom uglu prozora programa.
2. Nakon toga kliknite na dugme sa parametrima.
3. Zatim pronalazimo pododjeljak "Postavke" i idemo na meni za upravljanje dodacima. Ovo su mali programi koji se pokreću u Microsoft Excel okruženju. Vidimo da smo prvo kliknuli na meni „Dodaci“, a zatim u donjem desnom delu postavili stavku „Excel dodaci“ i kliknuli na dugme „Idi“. Sve potrebne radnje označene su crvenim pravokutnicima i strelicama.
4. Zatim uključite dodatak "Traži rješenje", nakon čega potvrđujemo svoje radnje pritiskom na tipku OK. Na osnovu opisa postavke vidimo da je dizajnirana za analizu složenih podataka, kao što su naučni i finansijski.
5. Nakon toga idite na karticu "Podaci", gdje vidimo novo dugme, koje se zove isto kao i dodatak. Može se naći u grupi alata za analizu.

Ostaje samo kliknuti na ovo dugme i prelazimo na rješavanje problema transporta. Ali prije toga, trebali bismo malo više razgovarati o alatu Solver u Excelu. Ovo je poseban Excel dodatak koji omogućava pronalaženje najbržeg rješenja problema. Karakteristična karakteristika je razmatranje ograničenja koja korisnik postavlja u fazi pripreme. Jednostavno rečeno, ovo je potprogram koji omogućava određivanje najboljeg načina za postizanje određenog zadatka. Takvi zadaci mogu uključivati ​​sljedeće:

1. Investicije, utovar skladišta ili bilo koja druga slična aktivnost. Uključujući isporuku robe.
2. Najbolji način. Ovo uključuje ciljeve kao što su postizanje maksimalnog profita uz minimalne troškove, kako postići najbolji kvalitet sa raspoloživim resursima, itd.

Pored transportnih zadataka, ovaj dodatak se koristi i u sljedeće svrhe:

1. Izrada plana proizvodnje. Odnosno, koliko jedinica proizvoda treba proizvesti da bi se postigao maksimalni prihod.
2. Pronađite raspodjelu rada za različite vrste posla tako da ukupni trošak proizvodnje proizvoda ili usluge bude najmanji.
3. Odredite minimalno vrijeme potrebno za završetak svih radova.

Kao što vidite, zadaci su veoma različiti. Univerzalno pravilo za primjenu ovog dodatka je da je prije rješavanja problema potrebno izraditi model koji bi odgovarao ključnim karakteristikama postavljenog problema. Model je kolekcija funkcija koje koriste varijable kao svoje argumente. Odnosno, vrijednosti koje se mogu promijeniti.

Važno je napomenuti da se optimizacija skupa vrijednosti vrši isključivo na jednom pokazatelju, koji se naziva ciljna funkcija.

Dodatak Solver nabraja različite vrijednosti varijabli koje se prosljeđuju funkciji cilja na takav način da je ona maksimalna, minimalna ili jednaka određenoj vrijednosti (upravo ovo je ograničenje). Postoji još jedna funkcija koja je donekle slična po principu rada, a koja se često miješa sa „Traženjem rješenja“. To se zove “Odabir opcija”. Ali ako kopate dublje, razlika između njih je ogromna:

1. Funkcija traženja cilja ne radi s više od jedne varijable.
2. Ne predviđa mogućnost postavljanja ograničenja na varijable.
3. Može odrediti samo jednakost ciljne funkcije određenoj vrijednosti, ali ne omogućava pronalaženje maksimuma i minimuma. Stoga nije pogodan za naš zadatak.
4. Može efikasno izračunati samo ako je model linearnog tipa. Ako je model nelinearan, tada pronalazi vrijednost koja je najbliža originalnoj vrijednosti.

Zadatak transporta je mnogo složeniji u svojoj strukturi, tako da dodatak "Odabir parametara" nije dovoljan za to. Pogledajmo pobliže kako implementirati funkciju “Traži rješenje” u praksi koristeći primjer transportnog problema.

Primjer rješavanja transportnog problema u Excelu

Da bismo jasno pokazali kako se problemi transporta rješavaju u praksi u Excelu, dajemo primjer.

Uslovi zadataka

Pretpostavimo da imamo 6 prodavaca i 7 kupaca. Potražnja i ponuda između njih se raspoređuju na sledeći način: 36, 51, 32, 44, 35 i 38 jedinica su prodavci, a 33, 48, 30, 36, 33, 24 i 32 jedinice su kupci. Ako zbrojite sve ove vrijednosti, vidjet ćete da su ponuda i potražnja u ravnoteži. Dakle, ovaj problem je zatvorenog tipa, koji se vrlo jednostavno rješava.

Osim toga, imamo informacije o tome koliko trebate potrošiti na prijevoz od tačke A do tačke B (u primjeru su istaknute žutim ćelijama).

Rješenje – korak po korak algoritam

Sada, nakon što smo se upoznali sa tabelama sa početnim podacima, možemo koristiti sledeći algoritam da rešimo ovaj problem:

1. Prvo pravimo tabelu koja se sastoji od 6 redova i 7 kolona.
2. Nakon toga idemo na bilo koju ćeliju koja ne sadrži nikakve vrijednosti, a istovremeno se nalazi izvan novostvorene tablice i ubacujemo funkciju. Da biste to učinili, kliknite na dugme fx, koje se nalazi lijevo od linije za unos funkcije.
3. Imamo prozor u kojem trebamo odabrati kategoriju “Matematika”. Koja nas funkcija zanima? Onaj istaknut na ovom snimku ekrana. Funkcija SUMPRODUCT množi opsege ili nizove među sobom i zbraja ih. Baš ono što nam treba. Nakon toga pritisnite taster OK.
4. Zatim će se na ekranu pojaviti prozor u kojem morate odrediti parametre funkcije. One su sljedeće:
   1. Niz 1. Ovo je prvi argument u koji upisujemo opseg koji je označen žutom bojom. Parametre funkcije možete podesiti bilo pomoću tipkovnice ili odabirom odgovarajućeg područja lijevom tipkom miša.
   2. Niz 2. Ovo je drugi argument, koji je novokreirana tabela. Radnje se izvode na isti način.

Potvrdite svoju radnju pritiskom na dugme OK.

1. Nakon toga, kliknemo levim mišem na ćeliju koja služi kao gornja leva u novostvorenoj tabeli. Sada ponovo kliknite na dugme funkcije umetanja.
2. Odabiremo istu kategoriju kao u prethodnom slučaju. Ali ovaj put nas zanima funkcija SUMA.
3. Sada dolazi faza popunjavanja argumenata. Kao prvi argument pišemo gornji red tabele koju smo kreirali na početku. Na isti način kao i prije, to se može učiniti odabirom ovih ćelija na listu ili ručno. Svoje radnje potvrđujemo pritiskom na dugme OK.
4. Vidjet ćemo rezultate u ćeliji s funkcijom. U ovom slučaju, to je nula. Zatim pomaknite kursor u donji desni kut, nakon čega će se pojaviti marker za automatsko dovršavanje. Izgleda kao mali crni pliš. Ako se pojavi, držite pritisnutu lijevu tipku miša i pomaknite kursor na posljednju ćeliju u našoj tabeli.
5. Ovo nam daje priliku da prenesemo formulu u sve ostale ćelije i dobijemo ispravne rezultate bez potrebe za dodatnim proračunima.
6. Sljedeći korak je odabir gornje lijeve ćelije i lijepljenje funkcije SUMA u nju. Nakon toga unosimo argumente i koristimo marker autocomplete da popunimo sve preostale ćelije.
7. Nakon toga prelazimo direktno na rješavanje problema. Da bismo to učinili, koristit ćemo dodatak koji smo ranije uključili. Idite na karticu "Podaci" i tamo nalazimo alat "Traži rješenje". Kliknemo na ovo dugme.
8. Sada se pred našim očima pojavio prozor kroz koji možete konfigurirati parametre našeg dodatka. Pogledajmo svaku od ovih opcija:
   1. Optimizirajte funkciju cilja. Ovdje trebamo odabrati ćeliju koja sadrži funkciju SUMPRODUCT. Vidimo da ova opcija omogućava odabir funkcije za koju će se tražiti rješenje.
   2. Prije. Ovdje postavljamo opciju “Minimum”.
   3. Promjenom ćelija varijabli. Ovdje označavamo raspon koji odgovara tablici koju smo kreirali na samom početku (s izuzetkom sumirajućeg reda i stupca).
   4. Podložno ograničenjima. Ovdje moramo dodati ograničenja klikom na dugme Dodaj.
   5. Pamtimo kakvu vrstu ograničenja moramo stvoriti – zbir vrijednosti potražnje kupaca i ponuda prodavača mora biti isti.
9. Zadatak ograničenja se provodi na sljedeći način:
   1. Veza sa ćelijama. Ovdje unosimo opseg tabele za proračune.
   2. Uslovi. Ovo je matematička operacija prema kojoj se provjerava opseg naveden u prvom polju za unos.
   3. Vrijednost uvjeta ili ograničenja. Ovdje unosimo odgovarajuću kolonu u izvornu tabelu.
   4. Nakon što su svi koraci dovršeni, kliknite na dugme OK, čime potvrđujemo naše radnje.

Izvodimo potpuno iste operacije za gornje redove, postavljajući sljedeći uvjet: moraju biti jednaki.

Sljedeći korak je postavljanje uslova. Moramo postaviti sljedeće kriterije za zbir ćelija u tabeli – veći ili jednak nuli, cijeli broj. Kao rezultat, imamo takvu listu uslova pod kojima je problem riješen. Ovdje morate biti sigurni da je potvrdni okvir pored opcije „Učini varijable bez ograničenja nenegativnim“ označen. Takođe, u našoj situaciji potrebno je da se izabere metoda za rešavanje problema – „Traženje rešenja za nelinearne probleme OPG metoda“. Sada možemo sa sigurnošću reći da je postavka obavljena. Stoga ostaje samo izvršiti proračune. Da biste to učinili, kliknite na dugme "Pronađi rješenje".

Nakon toga, svi podaci će se automatski izračunati, a zatim će Excel prikazati prozor sa rezultatima. Neophodno je kako bi se još jednom provjerio rad računara, jer su moguće greške ako su uslovi prethodno bili pogrešno postavljeni. Ako je sve ispravno, kliknite na dugme "OK" i pogledajte gotovu tabelu.

Ako se ispostavi da je naš zadatak postao otvoren tip, onda je to loše, jer morate urediti izvornu tabelu tako da se zadatak pretvori u zatvoreni. Međutim, kada se to uradi, preostali algoritam će biti isti.

zaključak

Kao što vidite, Excel se može koristiti i za vrlo složene proračune, koji na prvi pogled nisu dostupni jednostavnom računarskom programu koji je instaliran gotovo kod svih. Međutim, jeste. Danas smo već pokrili napredni nivo upotrebe. Ova tema nije tako jednostavna, ali kako kažu, put će savladati onaj koji hoda. Glavna stvar je slijediti plan akcije i precizno izvršiti sve gore navedene radnje. Tada neće biti grešaka, a program će samostalno izvršiti sve potrebne proračune. Neće biti potrebno razmišljati o tome koju funkciju koristiti i tako dalje.