Szállítási feladat Excelben. A legjobb szállítási mód megtalálása az eladótól a vevőig

Az Excel egy nagyon funkcionális program. Hatalmas problémaréteg megoldására használható, amelyekkel az embernek szembe kell néznie az üzleti életben. Az egyik leggyakoribb a szállítás. Képzeljük el, hogy meg kell értenünk, hogy a gyártótól a végső vásárlóig melyik szállítási mód a legoptimálisabb idő, pénz és egyéb erőforrások tekintetében. Ez a probléma meglehetősen népszerű, függetlenül attól, hogy milyen iparágban működik a vállalkozás. Ezért nézzük meg közelebbről, hogyan lehet megvalósítani az Excel használatával.

A szállítási feladat leírása

Tehát van két szerződő felünk, akik folyamatosan érintkeznek egymással. Esetünkben ez egy vevő és egy eladó. Ki kell találnunk, hogyan szállítsunk árut úgy, hogy a költségek minimálisak legyenek. Ehhez az összes adatot sematikus vagy mátrix formában kell bemutatni. Excelben az utóbbi lehetőséget használjuk. Általában kétféle szállítási feladat létezik:

1. Zárva. Ebben az esetben a kereslet és a kínálat egyensúlyban van.
2. Nyisd ki. Itt nincs egyenlőség a kereslet és a kínálat között. A probléma megoldásához először az első típusba kell hozni, kiegyenlíteni a keresletet és a kínálatot. Ehhez be kell vezetnie egy további mutatót - egy feltételes vevő vagy eladó jelenlétét. Ezenkívül bizonyos változtatásokat kell végrehajtania a költségtáblázaton.

A Megoldás keresése funkció engedélyezése az Excelben

A szállítási problémák megoldására az Excelben van egy speciális „Megoldás keresése” funkció. Alapértelmezés szerint nincs engedélyezve, ezért a következő lépéseket kell végrehajtania:

1. Nyissa meg a „Fájl” menüt, amely a programablak bal felső sarkában található.
2. Ezt követően kattintson a paramétereket tartalmazó gombra.
3. Ezután keressük meg a „Beállítások” alszakaszt, és menjünk a kiegészítők kezelési menüjébe. Ezek kis programok, amelyek a Microsoft Excel környezetben futnak. Azt látjuk, hogy először a „Kiegészítők” menüre kattintottunk, majd a jobb alsó részben beállítottuk az „Excel-bővítmények” elemet, és rákattintunk a „Go” gombra. Az összes szükséges művelet piros téglalapokkal és nyilakkal van kiemelve.
4. Ezután kapcsolja be a „Megoldás keresése” bővítményt, amely után az OK gomb megnyomásával erősítjük meg tevékenységeinket. A beállítás leírása alapján azt látjuk, hogy összetett, például tudományos és pénzügyi adatok elemzésére készült.
5. Ezután lépjen az „Adatok” fülre, ahol egy új gombot látunk, amelynek neve ugyanaz, mint a bővítmény. Az Elemzés eszközcsoportban található.

Csak rá kell kattintani erre a gombra, és folytatjuk a szállítási probléma megoldását. De előtte beszéljünk egy kicsit többet az Excel Solver eszközéről. Ez egy speciális Excel-kiegészítő, amely lehetővé teszi egy probléma leggyorsabb megoldását. Jellemző tulajdonsága a felhasználó által az előkészítés szakaszában beállított korlátozások figyelembevétele. Egyszerűen fogalmazva, ez egy szubrutin, amely lehetővé teszi egy adott feladat megvalósításának legjobb módját. Ilyen feladatok a következők lehetnek:

1. Befektetés, raktár rakodás vagy bármilyen más hasonló tevékenység. Beleértve az áruk kiszállítását is.
2. A legjobb mód. Ez magában foglalja az olyan célokat, mint a maximális profit elérése minimális költséggel, a legjobb minőség elérése a rendelkezésre álló erőforrásokkal stb.

A szállítási feladatokon kívül ez a kiegészítő a következő célokra is használható:

1. Gyártási terv kidolgozása. Vagyis hány darab terméket kell előállítani a maximális bevétel eléréséhez.
2. Keresse meg a munkaerő elosztását a különböző típusú munkákhoz úgy, hogy egy termék vagy szolgáltatás előállításának összköltsége a legkisebb legyen.
3. Állítsa be az összes munka elvégzéséhez szükséges minimális időt.

Mint látható, a feladatok nagyon eltérőek. A bővítmény alkalmazásának univerzális szabálya az, hogy a probléma megoldása előtt létre kell hozni egy olyan modellt, amely megfelel a feltett probléma fő jellemzőinek. A modell olyan függvények gyűjteménye, amelyek argumentumaként változókat használnak. Vagyis olyan értékek, amelyek változhatnak.

Fontos megjegyezni, hogy egy értékkészlet optimalizálása kizárólag egy indikátoron történik, amelyet célfüggvénynek neveznek.

A Solver bővítmény felsorolja a változók különböző értékeit, amelyeket a célfüggvénynek továbbítanak oly módon, hogy az legyen a maximum, minimum vagy egyenlő egy bizonyos értékkel (pontosan ez a korlátozás). Van egy másik funkció, amely működési elvét tekintve némileg hasonló, és amelyet gyakran összetévesztenek a „Megoldás keresésével”. Ezt „Opció kiválasztása”-nak hívják. De ha mélyebbre ásunk, óriási a különbség köztük:

1. A Goal Seek függvény nem működik egynél több változóval.
2. Nem rendelkezik a változók korlátozásának lehetőségéről.
3. Csak a célfüggvény egyenlőségét képes meghatározni egy bizonyos értékkel, de nem teszi lehetővé a maximum és minimum megtalálását. Ezért nem alkalmas a mi feladatunkra.
4. Csak lineáris modell esetén képes hatékonyan számolni. Ha a modell nemlineáris, akkor az eredeti értékhez legközelebb eső értéket találja meg.

A szállítási feladat felépítésében jóval bonyolultabb, ezért ehhez nem elég a „Paraméterválasztás” kiegészítő. Nézzük meg közelebbről a „Megoldás keresése” funkció gyakorlati megvalósítását egy szállítási probléma példáján.

Példa egy szállítási probléma megoldására Excelben

Annak érdekében, hogy egyértelműen bemutassuk, hogyan lehet a közlekedési problémákat a gyakorlatban Excelben megoldani, mondjunk egy példát.

Feltételek feladatok

Tegyük fel, hogy 6 eladónk és 7 vásárlónk van. A kereslet és a kínálat a következőképpen oszlik meg közöttük: 36, 51, 32, 44, 35 és 38 db eladó, 33, 48, 30, 36, 33, 24 és 32 db vevő. Ha ezeket az értékeket összeadja, azt találja, hogy a kereslet és a kínálat egyensúlyban van. Ezért ez a probléma zárt típusú, ami nagyon egyszerűen megoldható.

Ezen kívül arról is rendelkezünk információval, hogy mennyit kell költenie az A pontból B pontba történő szállításra (a példában sárga cellákkal vannak kiemelve).

Megoldás – lépésről lépésre algoritmus

Most, miután megismerkedtünk a kezdeti adatokat tartalmazó táblázatokkal, a következő algoritmussal oldhatjuk meg ezt a problémát:

1. Először egy 6 sorból és 7 oszlopból álló táblázatot készítünk.
2. Ezután minden olyan cellába lépünk, amely nem tartalmaz értékeket, és ugyanakkor kívül esik az újonnan létrehozott táblán, és beillesztjük a függvényt. Ehhez kattintson az fx gombra, amely a függvénybeviteli sor bal oldalán található.
3. Van egy ablakunk, amelyben ki kell választanunk a „Matek” kategóriát. Milyen funkcióra vagyunk kíváncsiak? Az ezen a képernyőképen kiemelt. Funkció SUMPRODUCT tartományokat vagy tömböket szoroz meg egymás között, és összegzi azokat. Pont amire szükségünk van. Ezt követően nyomja meg az OK gombot.
4. Ezután egy ablak jelenik meg a képernyőn, amelyben meg kell adnia a funkció paramétereit. Ezek a következők:
   1. 1. tömb. Ez az első argumentum, amelyben a sárgával kiemelt tartományt írjuk. A funkció paramétereit akár a billentyűzet segítségével, akár a megfelelő terület kiválasztásával az egér bal gombjával állíthatja be.
   2. 2. tömb. Ez a második argumentum, ami az újonnan létrehozott tábla. A műveleteket ugyanúgy hajtják végre.

Erősítse meg a műveletet az OK gomb megnyomásával.

1. Ezután bal egérgombbal kattintunk arra a cellára, amely az újonnan létrehozott táblázatban bal felsőként szolgál. Most kattintson újra a beszúrás funkciógombra.
2. Ugyanazt a kategóriát választjuk, mint az előző esetben. De ezúttal a funkció érdekel minket ÖSSZEG.
3. Most jön az érvek kitöltésének szakasza. Első argumentumként az elején létrehozott táblázat legfelső sorát írjuk. A korábbiakhoz hasonlóan ez is megtehető ezen cellák kijelölésével a lapon, vagy manuálisan. Mûveleteinket az OK gomb megnyomásával erõsítjük meg.
4. Az eredményeket a függvényt tartalmazó cellában fogjuk látni. Ebben az esetben ez nulla. Ezután vigye a kurzort a jobb alsó sarokba, amely után megjelenik az automatikus kiegészítés jelölője. Úgy néz ki, mint egy kis fekete plüss. Ha megjelenik, tartsa lenyomva a bal egérgombot, és vigye a kurzort a táblázatunk utolsó cellájára.
5. Ez lehetőséget ad arra, hogy a képletet átvigyük az összes többi cellába, és a megfelelő eredményeket kapjuk anélkül, hogy további számításokat kellene végeznünk.
6. A következő lépés a bal felső cella kijelölése és a függvény beillesztése ÖSSZEG belé. Ezt követően beírjuk az argumentumokat, és az automatikus kiegészítés jelölőjével kitöltjük az összes többi cellát.
7. Ezt követően közvetlenül folytatjuk a probléma megoldását. Ehhez a korábban beépített kiegészítőt fogjuk használni. Lépjen az „Adatok” fülre, és ott megtalálja a „Megoldás keresése” eszközt. Erre a gombra kattintunk.
8. Most egy ablak jelent meg a szemünk előtt, amin keresztül konfigurálhatjuk kiegészítőnk paramétereit. Vessünk egy pillantást az alábbi lehetőségek mindegyikére:
   1. Optimalizálja a célfüggvényt. Itt ki kell választanunk a függvényt tartalmazó cellát SUMPRODUCT. Látjuk, hogy ez az opció lehetővé teszi egy olyan funkció kiválasztását, amelyre megoldást keres.
   2. Előtt. Itt beállítjuk a „Minimum” opciót.
   3. A változók celláinak megváltoztatásával. Itt a legelején elkészített táblázatnak megfelelő tartományt jelöljük (az összesítő sor és oszlop kivételével).
   4. Korlátozások szerint. Itt a Hozzáadás gombra kattintva kényszereket kell hozzáadnunk.
   5. Emlékezzünk arra, hogy milyen kényszert kell létrehoznunk – a vevői igények és az eladók ajánlatainak összegének azonosnak kell lennie.
9. A korlátozások feladata a következőképpen történik:
   1. Link a cellákhoz. Itt megadjuk a táblázat tartományát a számításokhoz.
   2. Feltételek. Ez egy matematikai művelet, amelyhez képest az első beviteli mezőben megadott tartományt ellenőrzik.
   3. A feltétel vagy megszorítás értéke. Itt írjuk be a megfelelő oszlopot a forrástáblázatba.
   4. Miután az összes lépést elvégezte, kattintson az OK gombra, ezzel megerősítve tevékenységünket.

A legfelső sorokra pontosan ugyanazokat a műveleteket hajtjuk végre, a következő feltétellel: egyenlőnek kell lenniük.

A következő lépés a feltételek meghatározása. A következő kritériumokat kell beállítanunk a táblázat celláinak összegére – nullánál nagyobb vagy egyenlő, egész szám. Ennek eredményeként van egy ilyen listánk a feltételekről, amelyek mellett a probléma megoldódik. Itt meg kell győződnie arról, hogy a „Korlátozás nélküli változók nem negatívvá tétele” opció melletti jelölőnégyzet be van jelölve. Szintén a mi helyzetünkben szükséges a problémamegoldás módszerének kiválasztása – „Megoldás keresése az OPG módszerek nemlineáris problémáira”. Most már nyugodtan kijelenthetjük, hogy a beállítás megtörtént. Ezért csak a számítások elvégzése marad. Ehhez kattintson a „Megoldás keresése” gombra.

Ezt követően minden adat automatikusan kiszámításra kerül, majd az Excel megjelenít egy ablakot az eredményekkel. Szükséges a számítógép működésének kétszeri ellenőrzése, mivel hibák lehetségesek, ha a feltételeket korábban helytelenül állította be. Ha minden helyes, kattintson az „OK” gombra, és tekintse meg a kész táblázatot.

Ha kiderül, hogy a feladatunk nyitott típusú lett, akkor ez rossz, mert a forrástáblázatot úgy kell szerkeszteni, hogy a feladat zárttá váljon. Ha azonban ez megtörténik, a fennmaradó algoritmus ugyanaz lesz.

Következtetés

Mint látható, az Excel nagyon összetett számításokhoz is használható, amelyek első pillantásra nem érhetők el egy egyszerű számítógépes program számára, amely szinte mindenkire telepítve van. Azonban az. Ma már foglalkoztunk a magasabb szintű használattal. Ez a téma nem olyan egyszerű, de ahogy mondani szokás, az utat a gyalogos fogja úrrá lenni. A lényeg az, hogy kövesse a cselekvési tervet, és pontosan hajtsa végre a fent jelzett összes műveletet. Ezután nem lesznek hibák, és a program önállóan elvégzi az összes szükséges számítást. Nem kell gondolkodnia azon, hogy melyik funkciót használja és így tovább.