Transporta uzdevums programmā Excel. Labākā transporta veida atrašana no pārdevēja līdz pircējam

Excel ir ļoti funkcionāla programma. To var izmantot, lai atrisinātu milzīgu problēmu slāni, ar ko nākas saskarties biznesā. Viens no visizplatītākajiem ir transportēšana. Iedomājieties, ka mums ir jāsaprot, kura transportēšanas metode no ražotāja līdz gala pircējam ir optimālākā laika, naudas un citu resursu ziņā. Šī problēma ir diezgan populāra neatkarīgi no tā, kurā nozarē darbojas uzņēmums. Tāpēc apskatīsim tuvāk, kā to ieviest, izmantojot Excel.

Transporta uzdevuma apraksts

Tātad mums ir divi darījuma partneri, kas pastāvīgi mijiedarbojas viens ar otru. Mūsu gadījumā tas ir pircējs un pārdevējs. Jāizdomā, kā transportēt preces tā, lai izmaksas būtu minimālas. Lai to izdarītu, jums ir jāuzrāda visi dati shematiskā vai matricas formā. Programmā Excel mēs izmantojam pēdējo iespēju. Kopumā ir divu veidu transporta uzdevumi:

1. Slēgts. Šajā gadījumā piedāvājums un pieprasījums ir līdzsvarā.
2. Atvērt. Šeit nav vienlīdzības starp piedāvājumu un pieprasījumu. Lai rastu šīs problēmas risinājumu, vispirms tas ir jānovirza uz pirmo veidu, izlīdzinot piedāvājumu un pieprasījumu. Lai to izdarītu, jums jāievieš papildu indikators - nosacīta pircēja vai pārdevēja klātbūtne. Turklāt izmaksu tabulā ir jāveic noteiktas izmaiņas.

Kā programmā Excel iespējot risinājumu Atrast risinājumu

Transporta problēmu risināšanai programmā Excel ir īpaša funkcija “Meklēt risinājumu”. Pēc noklusējuma tas nav iespējots, tāpēc jums ir jāveic šādas darbības:

1. Atveriet izvēlni “Fails”, kas atrodas programmas loga augšējā kreisajā stūrī.
2. Pēc tam noklikšķiniet uz pogas ar parametriem.
3. Tālāk mēs atrodam apakšsadaļu “Iestatījumi” un dodamies uz pievienojumprogrammu pārvaldības izvēlni. Tās ir nelielas programmas, kas darbojas Microsoft Excel vidē. Mēs redzam, ka vispirms mēs noklikšķinājām uz izvēlnes “Papildinājumi”, un pēc tam apakšējā labajā daļā iestatījām vienumu “Excel pievienojumprogrammas” un noklikšķinājām uz pogas “Aiziet”. Visas nepieciešamās darbības ir iezīmētas ar sarkaniem taisnstūriem un bultiņām.
4. Pēc tam ieslēdziet pievienojumprogrammu “Meklēt risinājumu”, pēc kura mēs apstiprinām savas darbības, nospiežot pogu Labi. Pamatojoties uz iestatījuma aprakstu, mēs redzam, ka tas ir paredzēts sarežģītu datu, piemēram, zinātnisko un finanšu, analīzei.
5. Pēc tam dodieties uz cilni “Dati”, kur mēs redzam jaunu pogu, kas tiek saukta tāpat kā pievienojumprogramma. To var atrast Analīzes rīku grupā.

Atliek tikai noklikšķināt uz šīs pogas, un mēs turpinām transporta problēmas risinājumu. Bet pirms tam mums vajadzētu nedaudz vairāk runāt par rīku Solver programmā Excel. Šis ir īpašs Excel papildinājums, kas ļauj atrast ātrāko problēmas risinājumu. Raksturīga iezīme ir to ierobežojumu apsvēršana, ko lietotājs nosaka sagatavošanas posmā. Vienkārši izsakoties, šī ir apakšprogramma, kas ļauj noteikt labāko veidu, kā sasniegt noteiktu uzdevumu. Šādi uzdevumi var ietvert šādus uzdevumus:

1. Investēšana, noliktavas iekraušana vai cita līdzīga darbība. Ieskaitot preču piegādi.
2. Labākais veids. Tas ietver tādus mērķus kā maksimālās peļņas sasniegšana ar minimālām izmaksām, kā ar pieejamajiem resursiem sasniegt vislabāko kvalitāti utt.

Papildus transporta uzdevumiem šis papildinājums tiek izmantots arī šādiem mērķiem:

1. Ražošanas plāna izstrāde. Tas ir, cik daudz produkta vienību ir jāsaražo, lai sasniegtu maksimālos ienākumus.
2. Atrodiet darbaspēka sadalījumu dažādiem darba veidiem tā, lai produkta vai pakalpojuma ražošanas kopējās izmaksas būtu vismazākās.
3. Iestatiet minimālo laiku, kas nepieciešams visu darbu pabeigšanai.

Kā redzat, uzdevumi ir ļoti dažādi. Universāls noteikums šīs pievienojumprogrammas lietošanai ir tāds, ka pirms problēmas risināšanas ir jāizveido modelis, kas atbilstu izvirzītās problēmas galvenajām īpašībām. Modelis ir funkciju kopums, kas izmanto mainīgos kā savus argumentus. Tas ir, vērtības, kas var mainīties.

Ir svarīgi atzīmēt, ka vērtību kopas optimizācija tiek veikta tikai vienam rādītājam, ko sauc par mērķa funkciju.

Risinātāja pievienojumprogramma uzskaita dažādās mainīgo vērtības, kas tiek nodotas mērķa funkcijai tā, lai tā būtu maksimālā, minimālā vai vienāda ar noteiktu vērtību (tas ir tieši ierobežojums). Ir vēl viena funkcija, kas savā darbības principā ir nedaudz līdzīga un kuru bieži sajauc ar “Risinājuma meklēšanu”. To sauc par opciju izvēli. Bet, ja jūs iedziļināsities, atšķirība starp tiem ir milzīga:

1. Funkcija Goal Seek nedarbojas ar vairāk nekā vienu mainīgo.
2. Tas neparedz iespēju noteikt ierobežojumus mainīgajiem lielumiem.
3. Tas spēj noteikt tikai mērķa funkcijas vienādību ar noteiktu vērtību, bet nedod iespēju atrast maksimumu un minimumu. Tāpēc tas nav piemērots mūsu uzdevumam.
4. Spēj efektīvi aprēķināt tikai tad, ja modelis ir lineārs. Ja modelis ir nelineārs, tas atrod vērtību, kas ir vistuvāk sākotnējai vērtībai.

Transporta uzdevums ir daudz sarežģītāks savā struktūrā, tāpēc ar papildinājumu “Parametru izvēle” tam nepietiek. Sīkāk aplūkosim, kā praktiski realizēt funkciju “Meklēt risinājumu”, izmantojot transporta problēmas piemēru.

Piemērs transporta problēmas risināšanai programmā Excel

Lai uzskatāmi parādītu, kā praksē Excel var atrisināt transporta problēmas, sniegsim piemēru.

Nosacījumu uzdevumi

Pieņemsim, ka mums ir 6 pārdevēji un 7 pircēji. Pieprasījums un piedāvājums starp tiem tiek sadalīti attiecīgi šādi: 36, 51, 32, 44, 35 un 38 vienības ir pārdevēji un 33, 48, 30, 36, 33, 24 un 32 vienības ir pircēji. Ja jūs summējat visas šīs vērtības, jūs atklāsit, ka piedāvājums un pieprasījums ir līdzsvarā. Tāpēc šī problēma ir slēgta tipa, kas tiek atrisināta ļoti vienkārši.

Turklāt mums ir informācija par to, cik daudz jums ir nepieciešams tērēt transportēšanai no punkta A uz punktu B (piemērā tie ir iezīmēti dzeltenās šūnās).

Risinājums – soli pa solim algoritms

Tagad, kad esam iepazinušies ar tabulām ar sākotnējiem datiem, mēs varam izmantot šādu algoritmu, lai atrisinātu šo problēmu:

1. Pirmkārt, mēs izveidojam tabulu, kas sastāv no 6 rindām un 7 kolonnām.
2. Pēc tam mēs ejam uz jebkuru šūnu, kurā nav vērtību un kas tajā pašā laikā atrodas ārpus jaunizveidotās tabulas, un ievietojam funkciju. Lai to izdarītu, noklikšķiniet uz pogas fx, kas atrodas pa kreisi no funkcijas ievades rindas.
3. Mums ir logs, kurā jāizvēlas kategorija “Math”. Kāda funkcija mūs interesē? Šajā ekrānuzņēmumā izceltais. Funkcija SUMPRODUCT reizina diapazonus vai masīvus savā starpā un tos summē. Tieši tas, kas mums vajadzīgs. Pēc tam nospiediet taustiņu OK.
4. Pēc tam ekrānā parādīsies logs, kurā jānorāda funkcijas parametri. Tie ir šādi:
   1. Masīvs 1. Šis ir pirmais arguments, kurā mēs ierakstām diapazonu, kas ir iezīmēts dzeltenā krāsā. Funkciju parametrus var iestatīt, izmantojot tastatūru vai izvēloties atbilstošo apgabalu ar peles kreiso taustiņu.
   2. 2. masīvs. Šis ir otrais arguments, kas ir jaunizveidotā tabula. Darbības tiek veiktas tādā pašā veidā.

Apstipriniet savu darbību, nospiežot pogu Labi.

1. Pēc tam ar peles kreiso pogu noklikšķinām uz šūnas, kas jaunizveidotajā tabulā kalpo kā augšējā kreisā puse. Tagad vēlreiz noklikšķiniet uz ievietošanas funkcijas pogas.
2. Mēs izvēlamies to pašu kategoriju kā iepriekšējā gadījumā. Bet šoreiz mūs interesē funkcija SUM.
3. Tagad nāk argumentu aizpildīšanas posms. Kā pirmo argumentu mēs rakstām tabulas augšējo rindu, kuru izveidojām sākumā. Tādā pašā veidā kā iepriekš, to var izdarīt, atlasot šīs šūnas uz lapas vai manuāli. Mēs apstiprinām savas darbības, nospiežot pogu Labi.
4. Rezultātus redzēsim šūnā ar funkciju. Šajā gadījumā tā ir nulle. Pēc tam pārvietojiet kursoru uz apakšējo labo stūri, pēc kura parādīsies automātiskās pabeigšanas marķieris. Tas izskatās kā mazs melns plīša. Ja tas parādās, turiet nospiestu peles kreiso pogu un pārvietojiet kursoru uz pēdējo mūsu tabulas šūnu.
5. Tas dod mums iespēju pārnest formulu uz visām pārējām šūnām un iegūt pareizos rezultātus, neveicot papildu aprēķinus.
6. Nākamais solis ir atlasīt augšējo kreiso šūnu un ielīmēt funkciju SUM viņā. Pēc tam mēs ievadām argumentus un izmantojam automātiskās pabeigšanas marķieri, lai aizpildītu visas atlikušās šūnas.
7. Pēc tam mēs pārejam tieši uz problēmas risināšanu. Lai to izdarītu, mēs izmantosim iepriekš iekļauto papildinājumu. Dodieties uz cilni “Dati”, un tur atrodam rīku “Risinājuma meklēšana”. Mēs noklikšķinām uz šīs pogas.
8. Tagad mūsu acu priekšā ir parādījies logs, caur kuru varat konfigurēt mūsu papildinājuma parametrus. Apskatīsim katru no šīm iespējām:
   1. Optimizējiet mērķa funkciju. Šeit mums ir jāizvēlas šūna, kurā ir funkcija SUMPRODUCT. Mēs redzam, ka šī opcija ļauj izvēlēties funkciju, kurai tiks meklēts risinājums.
   2. Pirms tam. Šeit mēs iestatām opciju “Minimums”.
   3. Mainot mainīgo šūnas. Šeit mēs norādām diapazonu, kas atbilst tabulai, kuru izveidojām pašā sākumā (izņemot kopsavilkuma rindu un kolonnu).
   4. Ievērojot ierobežojumus. Šeit mums jāpievieno ierobežojumi, noklikšķinot uz pogas Pievienot.
   5. Mēs atceramies, kāds ierobežojums mums ir jāveido – pircēju pieprasījumu un pārdevēju piedāvājumu vērtību summai jābūt vienādai.
9. Ierobežojumu uzdevums tiek veikts šādi:
   1. Saite uz šūnām. Šeit mēs ievadām tabulas diapazonu aprēķiniem.
   2. Noteikumi. Šī ir matemātiska darbība, pret kuru tiek pārbaudīts pirmajā ievades laukā norādītais diapazons.
   3. Nosacījuma vai ierobežojuma vērtība. Šeit mēs ievadām atbilstošo kolonnu avota tabulā.
   4. Kad visas darbības ir pabeigtas, noklikšķiniet uz pogas Labi, tādējādi apstiprinot mūsu darbības.

Mēs veicam tieši tādas pašas darbības augšējām rindām, uzstādot šādu nosacījumu: tām jābūt vienādām.

Nākamais solis ir nosacījumu iestatīšana. Mums ir jāiestata šādi kritēriji tabulas šūnu summai – lielāka vai vienāda ar nulli, vesels skaitlis. Rezultātā mums ir šāds nosacījumu saraksts, saskaņā ar kuriem problēma tiek atrisināta. Šeit jums jāpārliecinās, ka ir atzīmēta izvēles rūtiņa blakus opcijai “Padarīt mainīgos bez ierobežojumiem nenegatīvus”. Tāpat mūsu situācijā tiek prasīts, lai problēmas risināšanai tiktu izvēlēta metode – “OPG metožu nelineāro problēmu risinājuma meklēšana”. Tagad mēs varam droši teikt, ka iestatīšana ir pabeigta. Tāpēc atliek tikai veikt aprēķinus. Lai to izdarītu, noklikšķiniet uz pogas "Atrast risinājumu".

Pēc tam visi dati tiks aprēķināti automātiski, un pēc tam Excel parādīs logu ar rezultātiem. Tas ir nepieciešams, lai vēlreiz pārbaudītu datora darbību, jo ir iespējamas kļūdas, ja nosacījumi iepriekš tika iestatīti nepareizi. Ja viss ir pareizi, noklikšķiniet uz pogas “OK” un skatiet gatavo tabulu.

Ja izrādās, ka mūsu uzdevums ir kļuvis par atvērtu veidu, tas ir slikti, jo jums ir jārediģē avota tabula, lai uzdevums pārvērstos par slēgtu. Tomēr, kad tas ir izdarīts, atlikušais algoritms būs tāds pats.

Secinājumi

Kā redzat, Excel var izmantot arī ļoti sarežģītiem aprēķiniem, kas no pirmā acu uzmetiena nav pieejami vienkāršai datorprogrammai, kas ir instalēta gandrīz katrā. Tomēr tā ir. Šodien mēs jau esam apskatījuši progresīvo lietošanas līmeni. Šī tēma nav tik vienkārša, bet, kā saka, ceļu pārvaldīs ejošais. Galvenais ir ievērot rīcības plānu un precīzi veikt visas iepriekš norādītās darbības. Tad kļūdu nebūs, un programma patstāvīgi veiks visus nepieciešamos aprēķinus. Nebūs jādomā, kuru funkciju izmantot un tā tālāk.