Transpordiülesanne Excelis. Parima transpordiviisi leidmine müüjalt ostjani

Excel on väga funktsionaalne programm. Seda saab kasutada tohutu hulga probleemide lahendamiseks, millega äritegevuses silmitsi peab seisma. Üks levinumaid on transport. Kujutage ette, et me peame aru saama, milline transpordiviis tootjalt lõppostjani on aja, raha ja muude ressursside poolest kõige optimaalsem. See probleem on üsna populaarne, olenemata sellest, millises valdkonnas ettevõte tegutseb. Seetõttu vaatame lähemalt, kuidas seda Exceli abil rakendada.

Transpordiülesande kirjeldus

Seega on meil kaks vastaspoolt, kes suhtlevad pidevalt üksteisega. Meie puhul on selleks ostja ja müüja. Peame välja mõtlema, kuidas transportida kaupu nii, et kulud oleksid minimaalsed. Selleks peate esitama kõik andmed skemaatilise või maatriksi kujul. Excelis kasutame viimast võimalust. Üldiselt on transpordiülesandeid kahte tüüpi:

1. Suletud. Sel juhul on nõudlus ja pakkumine tasakaalus.
2. Avatud. Siin ei ole pakkumise ja nõudluse vahel võrdsust. Sellele probleemile lahenduse leidmiseks peate esmalt viima selle esimese tüübini, võrdsustades pakkumise ja nõudluse. Selleks tuleb kasutusele võtta lisanäitaja – tingimusliku ostja või müüja olemasolu. Lisaks peate kulutabelis tegema teatud muudatusi.

Kuidas lubada Excelis lahenduse otsimise funktsiooni

Transpordiprobleemide lahendamiseks Excelis on spetsiaalne funktsioon "Otsi lahendust". See pole vaikimisi lubatud, seega peate tegema järgmised toimingud.

1. Avage menüü "Fail", mis asub programmi akna vasakus ülanurgas.
2. Pärast seda klõpsake parameetritega nuppu.
3. Järgmisena leiame alamjaotise "Seaded" ja läheme lisandmoodulite haldusmenüüsse. Need on väikesed programmid, mis töötavad Microsoft Exceli keskkonnas. Näeme, et algul klõpsasime menüül "Lisandmoodulid" ja seejärel paremas alanurgas määrasime üksuse "Exceli lisandmoodulid" ja klõpsasime nuppu "Mine". Kõik vajalikud toimingud on esile tõstetud punaste ristkülikute ja nooltega.
4. Järgmisena lülitage sisse lisandmoodul "Otsi lahendust", mille järel kinnitame oma toimingud, vajutades nuppu OK. Seadistuse kirjelduse põhjal näeme, et see on mõeldud keerukate, näiteks teaduslike ja finantsandmete analüüsimiseks.
5. Pärast seda minge vahekaardile "Andmed", kus näeme uut nuppu, mida nimetatakse samaks kui lisandmoodulit. Selle leiate tööriistarühmast Analüüsi.

Jääb vaid sellel nupul klõpsata ja jätkame transpordiprobleemi lahendamisega. Enne seda tuleks aga veidi lähemalt rääkida Exceli tööriistast Solver. See on spetsiaalne Exceli lisand, mis võimaldab leida probleemile kiireima lahenduse. Iseloomulikuks tunnuseks on kasutaja poolt ettevalmistusetapis seatud piirangute arvestamine. Lihtsamalt öeldes on see alamprogramm, mis võimaldab määrata parima viisi teatud ülesande saavutamiseks. Sellised ülesanded võivad hõlmata järgmist:

1. Investeerimine, lattu laadimine või muu sarnane tegevus. Sealhulgas kauba kohaletoimetamine.
2. Parim viis. See hõlmab selliseid eesmärke nagu maksimaalse kasumi saavutamine minimaalsete kuludega, saadaolevate ressurssidega parima kvaliteedi saavutamine jne.

Lisaks transpordiülesannetele kasutatakse seda lisandmoodulit ka järgmistel eesmärkidel:

1. Tootmisplaani väljatöötamine. See tähendab, mitu ühikut toodet on vaja toota, et saavutada maksimaalne tulu.
2. Leia tööjõu jaotus erinevate tööde jaoks nii, et toote või teenuse tootmise kogumaksumus oleks väikseim.
3. Määrake minimaalne aeg, mis kulub kogu töö tegemiseks.

Nagu näete, on ülesanded väga erinevad. Selle lisandmooduli rakendamise universaalne reegel on see, et enne probleemi lahendamist on vaja luua mudel, mis vastaks püstitatud probleemi põhiomadustele. Mudel on funktsioonide kogum, mis kasutab argumentidena muutujaid. See tähendab, väärtused, mis võivad muutuda.

Oluline on märkida, et väärtuste komplekti optimeerimine toimub ainult ühe indikaatori järgi, mida nimetatakse sihtfunktsiooniks.

Lahendaja lisandmoodul loetleb sihtfunktsioonile edastatavate muutujate erinevad väärtused nii, et see on maksimaalne, minimaalne või võrdne teatud väärtusega (see on täpselt piirang). On veel üks funktsioon, mis on oma tööpõhimõttelt mõnevõrra sarnane ja mida sageli aetakse segi "Otsi lahendusega". Seda nimetatakse "valikuvalikuks". Kuid kui süveneda, on nende erinevus tohutu:

1. Funktsioon Goal Seek ei tööta rohkem kui ühe muutujaga.
2. See ei näe ette võimalust seada muutujatele piiranguid.
3. See suudab määrata ainult sihtfunktsiooni võrdsuse teatud väärtusega, kuid ei võimalda leida maksimumi ja miinimumi. Seetõttu ei sobi see meie ülesande täitmiseks.
4. Võimalik tõhusalt arvutada ainult siis, kui mudel on lineaarne. Kui mudel on mittelineaarne, leiab see algväärtusele lähima väärtuse.

Transpordiülesanne on oma ülesehituselt palju keerulisem, nii et lisandmoodulist “Parameetrite valik” selleks ei piisa. Vaatame transpordiprobleemi näitel lähemalt, kuidas “Otsi lahendust” funktsiooni praktikas rakendada.

Näide transpordiülesande lahendamisest Excelis

Et selgelt demonstreerida, kuidas transpordiprobleeme Excelis praktikas lahendada, toome näite.

Tingimuste ülesanded

Oletame, et meil on 6 müüjat ja 7 ostjat. Nõudlus ja pakkumine nende vahel jagunevad vastavalt järgmiselt: 36, 51, 32, 44, 35 ja 38 ühikut on müüjad ning 33, 48, 30, 36, 33, 24 ja 32 ühikut ostjad. Kui võtate kõik need väärtused kokku, leiate, et pakkumine ja nõudlus on tasakaalus. Seetõttu on see probleem suletud tüüpi, mis lahendatakse väga lihtsalt.

Lisaks on meil teave selle kohta, kui palju peate kulutama transpordile punktist A punkti B (näites on need kollastes lahtrites esile tõstetud).

Lahendus – samm-sammult algoritm

Nüüd, kui oleme tutvunud algandmetega tabelitega, saame selle probleemi lahendamiseks kasutada järgmist algoritmi:

1. Esiteks teeme tabeli, mis koosneb 6 reast ja 7 veerust.
2. Pärast seda läheme mis tahes lahtrisse, mis ei sisalda väärtusi ja asub samal ajal väljaspool vastloodud tabelit, ja sisestame funktsiooni. Selleks klõpsake nuppu fx, mis asub funktsiooni sisestusreast vasakul.
3. Meil on aken, kus peame valima kategooria "Matemaatika". Milline funktsioon meid huvitab? Sellel ekraanipildil esile tõstetud. Funktsioon SUMPRODUCT korrutab omavahel vahemikud või massiivid ja summeerib need. Just see, mida me vajame. Pärast seda vajutage nuppu OK.
4. Järgmisena ilmub ekraanile aken, kus peate määrama funktsiooni parameetrid. Need on järgmised:
   1. Massiiv 1. See on esimene argument, millesse kirjutame kollasega esile tõstetud vahemiku. Funktsioonide parameetreid saab määrata kas klaviatuuri abil või hiire vasaku nupuga sobiva ala valimisega.
   2. Massiiv 2. See on teine ​​argument, mis on vastloodud tabel. Toiminguid tehakse samal viisil.

Kinnitage oma tegevus, vajutades nuppu OK.

1. Pärast seda teeme hiire vasaku klõpsuga lahtril, mis toimib vastloodud tabeli vasakpoolse ülanurgana. Nüüd klõpsake uuesti lisamisfunktsiooni nuppu.
2. Valime sama kategooria nagu eelmisel juhul. Kuid seekord oleme huvitatud funktsioonist SUM.
3. Nüüd tuleb argumentide täitmise etapp. Esimese argumendina kirjutame alguses koostatud tabeli ülemise rea. Samamoodi nagu varem, saab seda teha, valides need lahtrid lehel või käsitsi. Kinnitame oma toiminguid, vajutades nuppu OK.
4. Tulemusi näeme funktsiooniga lahtris. Sel juhul on see null. Järgmisena liigutage kursor alumisse paremasse nurka, mille järel ilmub automaatse täitmise marker. See näeb välja nagu väike must plüüs. Kui see kuvatakse, hoidke all hiire vasakut nuppu ja viige kursor meie tabeli viimasesse lahtrisse.
5. See annab meile võimaluse kanda valem kõikidesse teistesse lahtritesse ja saada õiged tulemused ilma täiendavaid arvutusi tegemata.
6. Järgmine samm on valida ülemine vasak lahter ja kleepida funktsioon SUM temasse. Pärast seda sisestame argumendid ja kasutame automaatse täitmise markerit, et täita kõik ülejäänud lahtrid.
7. Pärast seda jätkame otse probleemi lahendamisega. Selleks kasutame varem kaasatud lisandmoodulit. Minge vahekaardile "Andmed" ja sealt leiame tööriista "Otsi lahendust". Klõpsame sellel nupul.
8. Nüüd on meie silme ette ilmunud aken, mille kaudu saate meie lisandmooduli parameetreid konfigureerida. Vaatame kõiki neid valikuid.
   1. Optimeerige sihtfunktsiooni. Siin peame valima funktsiooni sisaldava lahtri SUMPRODUCT. Näeme, et see valik võimaldab valida funktsiooni, millele lahendust otsitakse.
   2. Enne. Siin määrame valiku "Minimaalne".
   3. Muutujate lahtreid muutes. Siin näitame vahemikku, mis vastab tabelile, mille me kohe alguses lõime (välja arvatud kokkuvõttev rida ja veerg).
   4. Piirangud kehtivad. Siin peame lisama piiranguid, klõpsates nuppu Lisa.
   5. Peame meeles, millist piirangut peame looma – ostjate nõudmiste ja müüjate pakkumiste väärtuste summa peab olema sama.
9. Piirangute ülesanne täidetakse järgmiselt:
   1. Link rakkudega. Siin sisestame arvutuste jaoks tabeli vahemiku.
   2. Tingimused. See on matemaatiline tehe, mille suhtes kontrollitakse esimesel sisendväljal määratud vahemikku.
   3. Tingimuse või piirangu väärtus. Siin sisestame lähtetabelisse vastava veeru.
   4. Kui kõik sammud on lõpule viidud, klõpsake nuppu OK, kinnitades sellega meie toimingud.

Ülemiste ridade jaoks teeme täpselt samad toimingud, seades järgmise tingimuse: need peavad olema võrdsed.

Järgmine samm on tingimuste seadmine. Peame seadma tabeli lahtrite summa jaoks järgmised kriteeriumid – suurem või võrdne nulliga, täisarv. Selle tulemusena on meil selline tingimuste loend, mille korral probleem lahendatakse. Siin peate veenduma, et märkeruut valiku "Muuda muutujad ilma piiranguteta mittenegatiivseks" kõrval on märgitud. Samuti on meie olukorras nõutud, et ülesande lahendamiseks oleks valitud meetod – “OPG meetodite mittelineaarsetele probleemidele lahenduse otsimine”. Nüüd võime julgelt öelda, et seadistus on tehtud. Seetõttu jääb üle ainult arvutused. Selleks klõpsake nuppu "Leia lahendus".

Pärast seda arvutatakse kõik andmed automaatselt ja seejärel kuvab Excel akent tulemustega. See on vajalik arvuti toimimise kahekordseks kontrollimiseks, kuna eelnevalt valesti seatud tingimuste korral on võimalikud vead. Kui kõik on õige, klõpsake nuppu "OK" ja vaadake valmis tabelit.

Kui selgub, et meie ülesanne on muutunud avatud tüübiks, on see halb, sest peate lähtetabelit muutma nii, et ülesanne muutuks suletud. Kuid kui see on tehtud, on ülejäänud algoritm sama.

Järeldus

Nagu näha, saab Exceli abil teha ka väga keerulisi arvutusi, mis esmapilgul pole kättesaadavad lihtsale arvutiprogrammile, mis on peaaegu kõigisse installitud. Siiski on. Täna oleme juba käsitlenud kasutuse edasijõudnute taset. See teema pole nii lihtne, kuid nagu öeldakse, saab tee käija selgeks. Peaasi on järgida tegevuskava ja teha täpselt kõik ülalnimetatud toimingud. Siis vigu ei esine ja programm teeb iseseisvalt kõik vajalikud arvutused. Pole vaja mõelda, millist funktsiooni kasutada ja nii edasi.