Transportni zadatak u Excelu. Pronalaženje najboljeg načina prijevoza od prodavatelja do kupca

Excel je vrlo funkcionalan program. Može se koristiti za rješavanje ogromnog sloja problema s kojima se čovjek mora suočiti u poslu. Jedan od najčešćih je prijevoz. Zamislimo da trebamo razumjeti koji je način transporta od proizvođača do krajnjeg kupca najoptimalniji u smislu vremena, novca i drugih resursa. Ovaj problem je prilično popularan, bez obzira u kojoj se industriji posao nalazi. Stoga, pogledajmo pobliže kako ga implementirati pomoću programa Excel.

Opis transportnog zadatka

Dakle, imamo dvije druge strane koje su u stalnoj međusobnoj interakciji. U našem slučaju, to su kupac i prodavač. Moramo smisliti kako transportirati robu na takav način da troškovi budu minimalni. Da biste to učinili, morate prikazati sve podatke u shematskom ili matričnom obliku. U Excelu koristimo potonju opciju. Općenito, postoje dvije vrste transportnih zadataka:

1. Zatvoreno. U ovom slučaju ponuda i potražnja su u ravnoteži.
2. Otvorena. Ovdje nema jednakosti između ponude i potražnje. Da biste dobili rješenje za ovaj problem, prvo ga morate dovesti do prve vrste, izjednačavajući ponudu i potražnju. Da biste to učinili, morate uvesti dodatni pokazatelj - prisutnost uvjetnog kupca ili prodavatelja. Osim toga, potrebno je napraviti određene izmjene u tablici troškova.

Kako omogućiti značajku Pronađi rješenje u programu Excel

Za rješavanje problema s prijevozom u Excelu postoji posebna funkcija pod nazivom "Traži rješenje". Nije omogućeno prema zadanim postavkama, pa trebate učiniti sljedeće korake:

1. Otvorite izbornik "Datoteka" koji se nalazi u gornjem lijevom kutu prozora programa.
2. Nakon toga kliknite na gumb s parametrima.
3. Zatim pronalazimo pododjeljak "Postavke" i idemo na izbornik za upravljanje dodacima. To su mali programi koji rade unutar Microsoft Excel okruženja. Vidimo da smo najprije kliknuli na izbornik “Add-ins”, a zatim u donjem desnom dijelu postavili stavku “Excel Add-ins” i kliknuli na gumb “Go”. Sve potrebne radnje označene su crvenim pravokutnicima i strelicama.
4. Zatim uključite dodatak "Traži rješenje", nakon čega potvrđujemo naše radnje pritiskom na gumb U redu. Na temelju opisa postavke vidimo da je dizajnirana za analizu složenih podataka, poput znanstvenih i financijskih.
5. Nakon toga idite na karticu "Podaci", gdje vidimo novi gumb, koji se zove isto kao i dodatak. Nalazi se u grupi alata za analizu.

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

1. Investiranje, utovar skladišta ili bilo koja druga slična djelatnost. Uključujući isporuku robe.
2. Najbolji način. To uključuje ciljeve poput postizanja maksimalnog profita uz minimalne troškove, kako postići najbolju kvalitetu s raspoloživim resursima i tako dalje.

Osim transportnih zadataka, ovaj se dodatak također koristi u sljedeće svrhe:

1. Izrada plana proizvodnje. Odnosno, koliko jedinica proizvoda treba proizvesti da bi se ostvario maksimalan prihod.
2. Nađite raspodjelu rada za različite vrste poslova tako da ukupni trošak proizvodnje proizvoda ili usluge bude najmanji.
3. Postavite minimalno vrijeme potrebno za dovršetak cijelog posla.

Kao što vidite, zadaci su vrlo 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 skup funkcija koje koriste varijable kao svoje argumente. Odnosno vrijednosti koje se mogu mijenjati.

Važno je napomenuti da se optimizacija skupa vrijednosti provodi isključivo na jednom pokazatelju, koji se naziva funkcija cilja.

Dodatak Solver nabraja različite vrijednosti varijabli koje se prosljeđuju funkciji cilja na način da je maksimalna, minimalna ili jednaka određenoj vrijednosti (to je upravo ograničenje). Postoji još jedna funkcija koja je donekle slična u svom principu rada, a koja se često brka s "Potragom za rješenjem". Zove se “Odabir opcije”. Ali ako kopate dublje, razlika između njih je ogromna:

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

Transportni zadatak je mnogo kompliciraniji u svojoj strukturi, tako da dodatak "Odabir parametara" nije dovoljan za to. Pogledajmo pobliže kako implementirati funkciju "Traži rješenje" u praksi na primjeru transportnog problema.

Primjer rješavanja transportnog problema u Excelu

Kako bismo jasno pokazali kako u praksi riješiti transportne probleme u Excelu, navest ćemo primjer.

Zadaci uvjeta

Pretpostavimo da imamo 6 prodavača i 7 kupaca. Potražnja i ponuda između njih su raspoređene redom na sljedeći način: 36, 51, 32, 44, 35 i 38 jedinica su prodavači, a 33, 48, 30, 36, 33, 24 i 32 jedinice su kupci. Ako zbrojite sve te 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 podatke o tome koliko trebate potrošiti na prijevoz od točke A do točke B (u primjeru su označeni žutim poljima).

Rješenje – algoritam korak po korak

Sada, nakon što smo se upoznali s tablicama s početnim podacima, možemo upotrijebiti sljedeći algoritam za rješavanje ovog problema:

1. Prvo napravimo tablicu koja se sastoji od 6 redaka i 7 stupaca.
2. Nakon toga idemo u bilo koju ćeliju koja ne sadrži nikakve vrijednosti, a istovremeno se nalazi izvan novostvorene tablice i umetnemo funkciju. Da biste to učinili, kliknite na gumb fx koji se nalazi lijevo od retka za unos funkcije.
3. Imamo prozor u kojem trebamo odabrati kategoriju “Matematika”. Koja funkcija nas zanima? Onaj koji je istaknut na ovoj snimci zaslona. Funkcija SUMPRODUCT međusobno množi raspone ili nizove i zbraja ih. Baš ono što nam treba. Nakon toga pritisnite tipku OK.
4. Zatim će se na zaslonu pojaviti prozor u kojem trebate odrediti parametre funkcije. Oni su sljedeći:
   1. Niz 1. Ovo je prvi argument u koji upisujemo raspon koji je označen žutom bojom. Funkcijske parametre možete postaviti pomoću tipkovnice ili odabirom odgovarajućeg područja lijevom tipkom miša.
   2. Niz 2. Ovo je drugi argument, a to je novostvorena tablica. Radnje se izvode na isti način.

Potvrdite svoju akciju pritiskom na tipku OK.

1. Nakon toga, lijevim klikom miša napravimo ćeliju koja služi kao gornja lijeva u novonastaloj tablici. Sada ponovno kliknite gumb za umetanje funkcije.
2. Odabiremo istu kategoriju kao u prethodnom slučaju. Ali ovaj put nas zanima funkcija IZNOS.
3. Sada dolazi faza popunjavanja argumenata. Kao prvi argument upisujemo gornji red tablice 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 gumb OK.
4. Rezultate ćemo vidjeti u ćeliji s funkcijom. U ovom slučaju, to je nula. Zatim pomaknite kursor u donji desni kut, nakon čega će se pojaviti oznaka za automatsko dovršavanje. Izgleda kao mali crni pliš. Ako se pojavi, držite lijevu tipku miša i pomaknite kursor na posljednju ćeliju u našoj tablici.
5. To nam daje priliku prenijeti formulu u sve ostale ćelije i dobiti točne rezultate bez potrebe za dodatnim izračunima.
6. Sljedeći korak je odabir gornje lijeve ćelije i lijepljenje funkcije IZNOS u nju. Nakon toga unosimo argumente i pomoću markera za automatsko dovršavanje popunjavamo sve preostale ćelije.
7. Nakon toga prelazimo izravno 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 ovaj gumb.
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ćuje 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 izradili na samom početku (s izuzetkom retka i stupca sažimanja).
   4. Podložno ograničenjima. Ovdje moramo dodati ograničenja klikom na gumb Dodaj.
   5. Sjećamo se kakvog ograničenja trebamo stvoriti – zbroj vrijednosti zahtjeva kupaca i ponuda prodavača mora biti isti.
9. Zadatak ograničenja provodi se na sljedeći način:
   1. Veza na ćelije. Ovdje unosimo raspon tablice za izračune.
   2. Pojmovi. Ovo je matematička operacija u odnosu na koju se provjerava raspon naveden u prvom polju za unos.
   3. Vrijednost uvjeta ili ograničenja. Ovdje unosimo odgovarajući stupac u izvornu tablicu.
   4. Nakon što su svi koraci dovršeni, kliknite gumb U redu, čime potvrđujemo naše radnje.

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

Sljedeći korak je postavljanje uvjeta. Moramo postaviti sljedeće kriterije za zbroj ćelija u tablici – veći ili jednak nuli, cijeli broj. Kao rezultat toga, imamo takav popis uvjeta pod kojima se problem rješava. Ovdje morate biti sigurni da je potvrdni okvir pokraj opcije "Učini varijable bez ograničenja nenegativnima". Također, u našoj situaciji potrebno je izabrati metodu rješavanja problema – “Traženje rješenja nelinearnih problema OPG metoda”. Sada sa sigurnošću možemo reći da je postavka gotova. Stoga ostaje samo izvršiti izračune. Da biste to učinili, kliknite na gumb "Pronađi rješenje".

Nakon toga će se svi podaci automatski izračunati, a zatim će Excel prikazati prozor s rezultatima. Potrebno je još jednom provjeriti rad računala, jer su moguće pogreške ako su prethodno netočno postavljeni uvjeti. Ako je sve ispravno, kliknite gumb "U redu" i pogledajte gotovu tablicu.

Ako se ispostavi da je naš zadatak postao otvorenog tipa, onda je to loše, jer morate urediti izvornu tablicu tako da se zadatak pretvori u zatvoreni. Međutim, kada se to učini, preostali algoritam će biti isti.

Zaključak

Kao što vidite, Excel se također može koristiti za vrlo složene izračune, koji na prvi pogled nisu dostupni jednostavnom računalnom programu koji je instaliran u gotovo svima. Međutim, jest. Danas smo već pokrili naprednu razinu korištenja. Ova tema nije tako jednostavna, ali kako kažu, cestu će svladati onaj koji hoda. Glavna stvar je slijediti akcijski plan i točno izvršiti sve gore navedene radnje. Tada neće biti pogrešaka, a program će samostalno izvršiti sve potrebne izračune. Neće biti potrebno razmišljati koju funkciju koristiti i tako dalje.