Transportna naloga v Excelu. Iskanje najboljšega načina prevoza od prodajalca do kupca

Excel je zelo funkcionalen program. Uporablja se lahko za reševanje velikega števila težav, s katerimi se mora soočiti v poslu. Eden najpogostejših je prevoz. Predstavljajte si, da moramo razumeti, kateri način transporta od proizvajalca do končnega kupca je najbolj optimalen z vidika časa, denarja in drugih virov. Ta problem je zelo priljubljen, ne glede na to, v kateri panogi je podjetje. Zato si poglejmo podrobneje, kako ga implementirati z uporabo Excela.

Opis transportne naloge

Imamo torej dve nasprotni stranki, ki sta med seboj nenehno v interakciji. V našem primeru sta to kupec in prodajalec. Ugotoviti moramo, kako prevažati blago tako, da bodo stroški minimalni. Če želite to narediti, morate vse podatke predstaviti v shematski ali matrični obliki. V Excelu uporabljamo slednjo možnost. Na splošno obstajata dve vrsti transportnih nalog:

1. Zaprto. V tem primeru sta ponudba in povpraševanje v ravnovesju.
2. Odprto. Tukaj ni enakosti med ponudbo in povpraševanjem. Da bi dobili rešitev tega problema, ga morate najprej pripeljati do prve vrste, izenačiti ponudbo in povpraševanje. Če želite to narediti, morate uvesti dodaten indikator - prisotnost pogojnega kupca ali prodajalca. Poleg tega morate narediti nekatere spremembe v tabeli stroškov.

Kako omogočiti funkcijo Najdi rešitev v Excelu

Za reševanje transportnih težav v Excelu obstaja posebna funkcija, imenovana »Išči rešitev«. Privzeto ni omogočeno, zato morate narediti naslednje:

1. Odprite meni »Datoteka«, ki se nahaja v zgornjem levem kotu okna programa.
2. Po tem kliknite na gumb s parametri.
3. Nato najdemo pododdelek »Nastavitve« in gremo v meni za upravljanje dodatkov. To so majhni programi, ki delujejo v okolju Microsoft Excel. Vidimo, da smo najprej kliknili na meni »Dodatki«, nato pa v spodnjem desnem delu nastavili postavko »Dodatki Excel« in kliknili na gumb »Pojdi«. Vsa potrebna dejanja so označena z rdečimi pravokotniki in puščicami.
4. Nato vklopimo dodatek »Iskanje rešitve«, nato potrdimo svoja dejanja s pritiskom na gumb V redu. Na podlagi opisa nastavitve lahko vidimo, da je zasnovana za analizo kompleksnih podatkov, kot so znanstveni in finančni.
5. Po tem pojdite na zavihek »Podatki«, kjer vidimo nov gumb, ki se imenuje enako kot dodatek. Najdete ga v skupini orodij za analizo.

Ostaja samo klik na ta gumb in nadaljujemo z rešitvijo transportnega problema. Pred tem pa bi morali nekaj več govoriti o orodju Solver v Excelu. To je poseben dodatek za Excel, ki omogoča najhitrejšo rešitev težave. Značilna lastnost je upoštevanje omejitev, ki jih uporabnik določi v fazi priprave. Preprosto povedano, to je podprogram, ki omogoča določitev najboljšega načina za doseganje določene naloge. Take naloge lahko vključujejo naslednje:

1. Investiranje, polnjenje skladišča ali katera druga podobna dejavnost. Vključno z dostavo blaga.
2. Najboljši način. To vključuje cilje, kot so doseganje največjega dobička z minimalnimi stroški, kako doseči najboljšo kakovost z razpoložljivimi sredstvi itd.

Poleg transportnih nalog se ta dodatek uporablja tudi za naslednje namene:

1. Izdelava proizvodnega načrta. To pomeni, koliko enot izdelka je treba proizvesti, da bi dosegli največji dohodek.
2. Poiščite porazdelitev dela za različne vrste dela tako, da je skupni strošek proizvodnje izdelka ali storitve najmanjši.
3. Določite najkrajši čas, ki bo potreben za dokončanje celotnega dela.

Kot vidite, so naloge zelo različne. Univerzalno pravilo za uporabo tega dodatka je, da je treba pred reševanjem problema ustvariti model, ki bi ustrezal ključnim značilnostim postavljenega problema. Model je zbirka funkcij, ki uporabljajo spremenljivke kot svoje argumente. To so vrednote, ki se lahko spremenijo.

Pomembno je omeniti, da se optimizacija niza vrednosti izvaja izključno na enem indikatorju, ki se imenuje funkcija cilja.

Dodatek Solver našteje različne vrednosti spremenljivk, ki se posredujejo ciljni funkciji tako, da je največja, najmanjša ali enaka določeni vrednosti (prav to je omejitev). Obstaja še ena funkcija, ki je po načelu delovanja nekoliko podobna in jo pogosto zamenjujejo z "Iskanje rešitve". Imenuje se »Izbira možnosti«. Toda če se poglobite, je razlika med njima ogromna:

1. Funkcija Goal Seek ne deluje z več kot eno spremenljivko.
2. Ne predvideva možnosti določanja omejitev za spremenljivke.
3. Zmožen je določiti le enakost ciljne funkcije na določeno vrednost, ne omogoča pa iskanja maksimuma in minimuma. Zato ni primeren za našo nalogo.
4. Sposoben učinkovitega izračuna le, če je model linearnega tipa. Če je model nelinearen, potem najde vrednost, ki je najbližja prvotni vrednosti.

Transportna naloga je po svoji strukturi veliko bolj zapletena, zato dodatek »Izbira parametrov« za to ni dovolj. Oglejmo si podrobneje, kako v praksi izvajati funkcijo »Iskanje rešitve« na primeru transportnega problema.

Primer reševanja transportnega problema v Excelu

Da bi jasno prikazali, kako v praksi rešiti transportne probleme v Excelu, navedimo primer.

Naloge pogojev

Recimo, da imamo 6 prodajalcev in 7 kupcev. Povpraševanje in ponudba med njimi sta porazdeljena na naslednji način: 36, 51, 32, 44, 35 in 38 enot so prodajalci, 33, 48, 30, 36, 33, 24 in 32 enot pa kupci. Če seštejete vse te vrednosti, boste ugotovili, da sta ponudba in povpraševanje v ravnovesju. Zato je ta problem zaprtega tipa, ki se rešuje zelo preprosto.

Poleg tega imamo podatke o tem, koliko morate porabiti za prevoz od točke A do točke B (v primeru so označeni z rumenimi celicami).

Rešitev – algoritem korak za korakom

Zdaj, ko smo se seznanili s tabelami z začetnimi podatki, lahko za rešitev tega problema uporabimo naslednji algoritem:

1. Najprej naredimo tabelo, sestavljeno iz 6 vrstic in 7 stolpcev.
2. Po tem gremo v katero koli celico, ki ne vsebuje nobenih vrednosti in hkrati leži zunaj novo ustvarjene tabele, in vstavimo funkcijo. To storite tako, da kliknete gumb fx, ki se nahaja levo od vrstice za vnos funkcije.
3. Imamo okno, v katerem moramo izbrati kategorijo "Matematika". Kakšna funkcija nas zanima? Tisti, ki je označen na tem posnetku zaslona. funkcija SUMPRODUCT pomnoži obsege ali nize med seboj in jih sešteje. Samo tisto, kar potrebujemo. Po tem pritisnite tipko OK.
4. Nato se na zaslonu prikaže okno, v katerem morate določiti parametre funkcije. To so naslednje:
   1. Niz 1. To je prvi argument, v katerega zapišemo obseg, ki je označen z rumeno. Funkcijske parametre lahko nastavite s tipkovnico ali z izbiro ustreznega območja z levim gumbom miške.
   2. Niz 2. To je drugi argument, ki je na novo ustvarjena tabela. Dejanja se izvajajo na enak način.

Svoje dejanje potrdite s pritiskom na gumb OK.

1. Nato z levim klikom miške kliknemo na celico, ki služi kot zgornja leva v novo ustvarjeni tabeli. Zdaj znova kliknite gumb za vstavljanje funkcije.
2. Izberemo isto kategorijo kot v prejšnjem primeru. Toda tokrat nas zanima funkcija SUM.
3. Zdaj prihaja faza izpolnjevanja argumentov. Kot prvi argument zapišemo zgornjo vrstico tabele, ki smo jo ustvarili na začetku. Na enak način kot prej lahko to storite z izbiro teh celic na listu ali ročno. Svoja dejanja potrdimo s pritiskom na gumb OK.
4. Rezultate bomo videli v celici s funkcijo. V tem primeru je nič. Nato premaknite kazalec v spodnji desni kot, po katerem se prikaže oznaka za samodokončanje. Izgleda kot majhen črn pliš. Če se pojavi, držite levi gumb miške in premaknite kazalec na zadnjo celico v naši tabeli.
5. To nam daje možnost, da prenesemo formulo v vse druge celice in dobimo pravilne rezultate, ne da bi morali izvajati dodatne izračune.
6. Naslednji korak je, da izberete zgornjo levo celico in prilepite funkcijo SUM vanjo. Po tem vnesemo argumente in z oznako za samodokončanje zapolnimo vse preostale celice.
7. Po tem nadaljujemo neposredno z reševanjem problema. Za to bomo uporabili dodatek, ki smo ga vključili prej. Pojdite na zavihek »Podatki« in tam najdemo orodje »Išči rešitev«. Kliknemo na ta gumb.
8. Zdaj se je pred našimi očmi pojavilo okno, skozi katerega lahko konfigurirate parametre našega dodatka. Oglejmo si vsako od teh možnosti:
   1. Optimizirajte funkcijo cilja. Tukaj moramo izbrati celico, ki vsebuje funkcijo SUMPRODUCT. Vidimo, da ta možnost omogoča izbiro funkcije, za katero se bo iskala rešitev.
   2. prej. Tukaj nastavimo možnost "Minimalno".
   3. S spreminjanjem celic spremenljivk. Tukaj navedemo obseg, ki ustreza tabeli, ki smo jo ustvarili na samem začetku (z izjemo povzemajoče vrstice in stolpca).
   4. Veljajo omejitve. Tukaj moramo dodati omejitve s klikom na gumb Dodaj.
   5. Zapomnimo si, kakšno omejitev moramo ustvariti – vsota vrednosti povpraševanja kupcev in ponudb prodajalcev mora biti enaka.
9. Naloga omejitev se izvaja na naslednji način:
   1. Povezava do celic. Tukaj vnesemo obseg tabele za izračune.
   2. Pogoji. To je matematična operacija, s katero se preveri obseg, določen v prvem vnosnem polju.
   3. Vrednost pogoja ali omejitve. Tukaj vnesemo ustrezen stolpec v izvorno tabelo.
   4. Ko so vsi koraki končani, kliknite gumb V redu in s tem potrdite naša dejanja.

Izvajamo popolnoma enake operacije za zgornje vrstice, pri čemer določimo naslednji pogoj: morajo biti enake.

Naslednji korak je postavitev pogojev. Nastaviti moramo naslednje kriterije za vsoto celic v tabeli – večja ali enaka nič, celo število. Kot rezultat imamo takšen seznam pogojev, pod katerimi je problem rešen. Tukaj se morate prepričati, da je potrditveno polje zraven možnosti »Naredi spremenljivke brez omejitev nenegativne«. Prav tako je v naši situaciji zahtevana izbira metode za reševanje problema – “Iskanje rešitve nelinearnih problemov metod OPG”. Zdaj lahko mirno rečemo, da je nastavitev opravljena. Zato ostane samo izračune. Če želite to narediti, kliknite gumb »Najdi rešitev«.

Po tem bodo vsi podatki samodejno izračunani, nato pa bo Excel prikazal okno z rezultati. Potrebno je še enkrat preveriti delovanje računalnika, saj so možne napake, če so bili pogoji predhodno nastavljeni nepravilno. Če je vse pravilno, kliknite gumb »V redu« in si oglejte končano tabelo.

Če se izkaže, da je naša naloga postala odprtega tipa, potem je to slabo, ker morate urediti izvorno tabelo, tako da se naloga spremeni v zaprto. Vendar ko bo to storjeno, bo preostali algoritem enak.

zaključek

Kot lahko vidite, lahko Excel uporabite tudi za zelo zapletene izračune, ki na prvi pogled niso na voljo preprostemu računalniškemu programu, ki je nameščen skoraj v vsakem. Vendar pa je. Danes smo že obdelali napredno raven uporabe. Ta tema ni tako preprosta, a kot pravijo, bo cesto obvladal tisti, ki hodi. Glavna stvar je slediti akcijskemu načrtu in natančno izvesti vsa zgoraj navedena dejanja. Potem ne bo nobenih napak in program bo samostojno izvedel vse potrebne izračune. Ne bo treba razmišljati, katero funkcijo uporabiti in podobno.