Kuljetustehtävä Excelissä. Parhaan kuljetustavan löytäminen myyjältä ostajalle

Excel on erittäin toimiva ohjelma. Sitä voidaan käyttää ratkaisemaan valtava joukko ongelmia, joita yritys joutuu kohtaamaan. Yksi yleisimmistä on kuljetus. Kuvittele, että meidän on ymmärrettävä, mikä kuljetustapa valmistajalta lopulliselle ostajalle on optimaalinen ajan, rahan ja muiden resurssien suhteen. Tämä ongelma on melko suosittu riippumatta siitä, millä toimialalla yritys toimii. Katsotaanpa siksi tarkemmin, kuinka se toteutetaan Excelin avulla.

Kuljetustehtävän kuvaus

Meillä on siis kaksi vastapuolta, jotka ovat jatkuvasti vuorovaikutuksessa toistensa kanssa. Meidän tapauksessamme tämä on ostaja ja myyjä. Meidän on selvitettävä, kuinka tavaroita kuljetetaan niin, että kustannukset ovat minimaaliset. Tätä varten sinun on esitettävä kaikki tiedot kaavio- tai matriisimuodossa. Excelissä käytämme jälkimmäistä vaihtoehtoa. Yleisesti ottaen kuljetustehtäviä on kahdenlaisia:

1. Suljettu. Tässä tapauksessa kysyntä ja tarjonta ovat tasapainossa.
2. Avata. Tässä ei ole kysynnän ja tarjonnan tasa-arvoa. Saadaksesi ratkaisun tähän ongelmaan, sinun on ensin siirrettävä se ensimmäiseen tyyppiin, tasaamalla tarjonta ja kysyntä. Tätä varten sinun on otettava käyttöön lisäindikaattori - ehdollisen ostajan tai myyjän läsnäolo. Lisäksi sinun on tehtävä tiettyjä muutoksia kustannustaulukkoon.

Etsi ratkaisu -ominaisuuden käyttöönotto Excelissä

Kuljetusongelmien ratkaisemiseksi Excelissä on erityinen toiminto nimeltä "Etsi ratkaisua". Se ei ole oletusarvoisesti käytössä, joten sinun on suoritettava seuraavat vaiheet:

1. Avaa "Tiedosto"-valikko, joka sijaitsee ohjelmaikkunan vasemmassa yläkulmassa.
2. Napsauta sen jälkeen parametrien painiketta.
3. Seuraavaksi löydämme "Asetukset" -aliosion ja siirrymme lisäosien hallintavalikkoon. Nämä ovat pieniä ohjelmia, jotka toimivat Microsoft Excel -ympäristössä. Näemme, että napsautimme ensin "Lisäosat"-valikkoa, ja sitten oikeassa alakulmassa asetimme "Excel-apuohjelmat" -kohdan ja napsautimme "Go" -painiketta. Kaikki tarvittavat toiminnot on korostettu punaisilla suorakulmioilla ja nuolilla.
4. Ota seuraavaksi käyttöön "Etsi ratkaisua" -apuohjelma, jonka jälkeen vahvistamme toimintamme painamalla OK-painiketta. Asetuksen kuvauksen perusteella voimme nähdä, että se on suunniteltu analysoimaan monimutkaisia ​​tietoja, kuten tieteellistä ja taloudellista.
5. Siirry sen jälkeen "Data" -välilehteen, jossa näemme uuden painikkeen, jota kutsutaan samaksi kuin apuohjelma. Se löytyy Analyysityökaluryhmästä.

Jää vain napsauttaa tätä painiketta, ja siirrymme kuljetusongelman ratkaisuun. Mutta ennen sitä meidän pitäisi puhua hieman enemmän Excelin Solver-työkalusta. Tämä on erityinen Excel-lisäosa, jonka avulla on mahdollista löytää nopein ratkaisu ongelmaan. Tyypillinen piirre on rajoitusten huomioiminen, jotka käyttäjä asettaa valmisteluvaiheessa. Yksinkertaisesti sanottuna tämä on aliohjelma, jonka avulla voidaan määrittää paras tapa saavuttaa tietty tehtävä. Tällaisia ​​tehtäviä voivat olla seuraavat:

1. Investointi, varaston lastaus tai muu vastaava toiminta. Sisältää tavaroiden toimituksen.
2. Paras tapa. Tämä sisältää tavoitteet, kuten maksimaalisen voiton saavuttamisen pienin kustannuksin, parhaan laadun saavuttamisen käytettävissä olevilla resursseilla ja niin edelleen.

Kuljetustehtävien lisäksi tätä lisäosaa käytetään myös seuraaviin tarkoituksiin:

1. Tuotantosuunnitelman laatiminen. Eli kuinka monta yksikköä tuotetta on tuotettava maksimitulon saavuttamiseksi.
2. Etsi työvoiman jakautuminen eri tyyppisiin töihin siten, että tuotteen tai palvelun tuotannon kokonaiskustannukset ovat pienimmät.
3. Aseta vähimmäisaika, joka kuluu kaikkien töiden suorittamiseen.

Kuten näet, tehtävät ovat hyvin erilaisia. Tämän lisäosan soveltamisen yleinen sääntö on, että ennen ongelman ratkaisemista on luotava malli, joka vastaisi esitetyn ongelman keskeisiä ominaisuuksia. Malli on kokoelma funktioita, jotka käyttävät muuttujia argumentteinaan. Eli arvot, jotka voivat muuttua.

On tärkeää huomata, että arvojoukon optimointi suoritetaan yksinomaan yhdelle indikaattorille, jota kutsutaan tavoitefunktioksi.

Solver-apuohjelma luettelee tavoitefunktiolle välitettävien muuttujien eri arvot siten, että se on maksimi, minimi tai tietty arvo (tämä on juuri rajoitus). On toinenkin toiminto, joka on hieman samanlainen toimintaperiaatteeltaan ja joka usein sekoitetaan "Etsi ratkaisua". Sitä kutsutaan "vaihtoehdon valinnaksi". Mutta jos kaivaa syvemmälle, ero niiden välillä on valtava:

1. Tavoitteenhakutoiminto ei toimi useamman kuin yhden muuttujan kanssa.
2. Siinä ei säädetä mahdollisuudesta asettaa rajoja muuttujille.
3. Se pystyy määrittämään vain tavoitefunktion yhtäläisyyden tiettyyn arvoon, mutta ei anna mahdollisuutta löytää maksimi- ja minimiarvoa. Siksi se ei sovellu tehtäväämme.
4. Pystyy laskemaan tehokkaasti vain, jos malli on lineaarinen. Jos malli on epälineaarinen, se löytää arvon, joka on lähinnä alkuperäistä arvoa.

Kuljetustehtävä on rakenteeltaan paljon monimutkaisempi, joten "Parameter selection" -lisäosa ei riitä tähän. Katsotaanpa tarkemmin, kuinka "Etsi ratkaisu" -toiminto toteutetaan käytännössä kuljetusongelman esimerkin avulla.

Esimerkki kuljetusongelman ratkaisemisesta Excelissä

Havainnollistaaksemme selkeästi, kuinka kuljetusongelmia ratkaistaan ​​käytännössä Excelissä, annetaan esimerkki.

Ehdot tehtävät

Oletetaan, että meillä on 6 myyjää ja 7 ostajaa. Kysyntä ja tarjonta jakautuvat vastaavasti seuraavasti: 36, 51, 32, 44, 35 ja 38 yksikköä ovat myyjiä ja 33, 48, 30, 36, 33, 24 ja 32 yksikköä ostajia. Jos lasket kaikki nämä arvot yhteen, huomaat, että tarjonta ja kysyntä ovat tasapainossa. Siksi tämä ongelma on suljettu tyyppi, joka ratkaistaan ​​hyvin yksinkertaisesti.

Lisäksi meillä on tietoa siitä, kuinka paljon sinun tulee kuluttaa kuljetuksiin pisteestä A pisteeseen B (ne on korostettu keltaisilla soluilla esimerkissä).

Ratkaisu – askel askeleelta algoritmi

Nyt kun olemme tutustuneet taulukoihin, joissa on lähtötiedot, voimme käyttää seuraavaa algoritmia tämän ongelman ratkaisemiseen:

1. Ensin tehdään taulukko, joka koostuu 6 rivistä ja 7 sarakkeesta.
2. Sen jälkeen siirrymme mihin tahansa soluun, joka ei sisällä arvoja ja joka on samalla juuri luodun taulukon ulkopuolella, ja lisää funktio. Voit tehdä tämän napsauttamalla fx-painiketta, joka sijaitsee funktion syöttörivin vasemmalla puolella.
3. Meillä on ikkuna, jossa meidän on valittava luokka "Math". Mistä toiminnoista olemme kiinnostuneita? Tässä kuvakaappauksessa korostettu. Toiminto SUMPRODUCT kertovat alueet tai taulukot keskenään ja summaavat ne. Juuri mitä tarvitsemme. Paina sen jälkeen OK-näppäintä.
4. Seuraavaksi näytölle tulee ikkuna, jossa sinun on määritettävä toimintoparametrit. Ne ovat seuraavat:
   1. Taulukko 1. Tämä on ensimmäinen argumentti, johon kirjoitamme keltaisella korostetun alueen. Voit asettaa toimintoparametrit joko näppäimistöllä tai valitsemalla sopivan alueen hiiren vasemmalla painikkeella.
   2. Taulukko 2. Tämä on toinen argumentti, joka on juuri luotu taulukko. Toiminnot suoritetaan samalla tavalla.

Vahvista toiminto painamalla OK-painiketta.

1. Tämän jälkeen napsautamme hiiren vasemmalla painikkeella solua, joka toimii äskettäin luodun taulukon vasemmana yläkulmana. Napsauta nyt uudelleen lisäystoimintopainiketta.
2. Valitsemme saman kategorian kuin edellisessä tapauksessa. Mutta tällä kertaa olemme kiinnostuneita toiminnosta SUMMA.
3. Nyt tulee argumenttien täyttämisen vaihe. Ensimmäiseksi argumentiksi kirjoitamme alussa luomamme taulukon ylimmän rivin. Samalla tavalla kuin aiemmin, tämä voidaan tehdä valitsemalla nämä solut arkilta tai manuaalisesti. Vahvistamme toimintamme painamalla OK-painiketta.
4. Näemme tulokset solussa, jossa on funktio. Tässä tapauksessa se on nolla. Siirrä seuraavaksi kohdistin oikeaan alakulmaan, jonka jälkeen automaattisen täydennyksen merkki tulee näkyviin. Se näyttää pieneltä mustalta pehmoilta. Jos se tulee näkyviin, pidä hiiren vasenta painiketta painettuna ja siirrä kohdistin taulukon viimeiseen soluun.
5. Tämä antaa meille mahdollisuuden siirtää kaava kaikkiin muihin soluihin ja saada oikeat tulokset tarvitsematta suorittaa lisälaskelmia.
6. Seuraava vaihe on valita vasemman yläkulman solu ja liittää funktio SUMMA häneen. Sen jälkeen syötämme argumentit ja käytämme automaattisen täydennyksen merkkiä kaikkien jäljellä olevien solujen täyttämiseen.
7. Tämän jälkeen siirrymme suoraan ongelman ratkaisemiseen. Tätä varten käytämme aiemmin sisällyttämäämme lisäosaa. Siirry "Data" -välilehteen, ja sieltä löydämme "Hae ratkaisua" -työkalun. Napsautamme tätä painiketta.
8. Nyt silmiemme eteen on ilmestynyt ikkuna, jonka kautta voit määrittää lisäosamme parametrit. Tarkastellaan jokaista näistä vaihtoehdoista:
   1. Optimoi tavoitefunktio. Täällä meidän on valittava funktion sisältävä solu SUMPRODUCT. Näemme, että tämä vaihtoehto mahdollistaa toiminnon valitsemisen, jolle ratkaisua etsitään.
   2. Ennen. Tässä asetamme vaihtoehdon "Minimi".
   3. Muuttamalla muuttujien soluja. Tässä osoitamme heti alussa luomaamme taulukkoa vastaavan alueen (poikkeuksena yhteenveto rivi ja sarake).
   4. Rajoitettu. Täällä meidän on lisättävä rajoituksia napsauttamalla Lisää-painiketta.
   5. Muistamme, millainen rajoitus meidän on luotava – ostajien vaatimusten ja myyjien tarjousten arvojen summan on oltava sama.
9. Rajoitustehtävä suoritetaan seuraavasti:
   1. Linkki soluihin. Tässä syötetään taulukon alue laskelmia varten.
   2. Ehdot. Tämä on matemaattinen operaatio, jota vastaan ​​tarkistetaan ensimmäisessä syöttökentässä määritetty alue.
   3. Ehdon tai rajoitteen arvo. Tässä syötetään oikea sarake lähdetaulukkoon.
   4. Kun kaikki vaiheet on suoritettu, napsauta OK-painiketta ja vahvista toimintamme.

Suoritamme täsmälleen samat toiminnot ylimmille riveille asettamalla seuraavan ehdon: niiden on oltava yhtä suuret.

Seuraava vaihe on ehtojen asettaminen. Meidän on asetettava seuraavat kriteerit taulukon solujen summalle – suurempi tai yhtä suuri kuin nolla, kokonaisluku. Tämän seurauksena meillä on tällainen luettelo ehdoista, joissa ongelma ratkaistaan. Täällä sinun on varmistettava, että valintaruutu "Tee muuttujat ilman rajoituksia ei-negatiivisiksi" on valittuna. Myös meidän tilanteessamme edellytetään, että ongelman ratkaisumenetelmäksi valitaan ”Ratkaisun etsiminen OPG-menetelmien epälineaarisiin ongelmiin”. Nyt voimme turvallisesti sanoa, että asetus on tehty. Siksi jää vain suorittaa laskelmat. Voit tehdä tämän napsauttamalla "Etsi ratkaisu" -painiketta.

Sen jälkeen kaikki tiedot lasketaan automaattisesti, ja sitten Excel näyttää ikkunan tuloksista. Se on tarpeen tietokoneen toiminnan tarkistamiseksi uudelleen, koska virheet ovat mahdollisia, jos ehdot on asetettu aiemmin väärin. Jos kaikki on oikein, napsauta "OK" -painiketta ja katso valmis taulukko.

Jos käy ilmi, että tehtävästämme on tullut avoin tyyppi, tämä on huono, koska sinun on muokattava lähdetaulukkoa niin, että tehtävästä tulee suljettu. Kuitenkin, kun tämä on tehty, jäljelle jäävä algoritmi on sama.

Yhteenveto

Kuten näet, Exceliä voidaan käyttää myös erittäin monimutkaisiin laskelmiin, jotka eivät ensi silmäyksellä ole yksinkertaisen tietokoneohjelman käytettävissä, joka on asennettu melkein kaikille. Kuitenkin se on. Tänään olemme jo käsitelleet edistyneen käytön. Tämä aihe ei ole niin yksinkertainen, mutta kuten sanotaan, kävely hallitsee tien. Tärkeintä on noudattaa toimintasuunnitelmaa ja suorittaa tarkasti kaikki yllä mainitut toimet. Sitten ei tule virheitä, ja ohjelma suorittaa itsenäisesti kaikki tarvittavat laskelmat. Sinun ei tarvitse miettiä, mitä toimintoa käyttää ja niin edelleen.