Az optimális szorzat-kombináció meghatározása a Solver használatával

Megjegyzés: Szeretnénk, ha minél gyorsabban hozzáférhetne a saját nyelvén íródott súgótartalmakhoz. Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Kérjük, hogy a lap alján írja meg, hogy hasznos volt-e az Ön számára az itt található információ. Az eredeti angol nyelvű cikket itt találja.

Ez a cikk azt ismerteti, hogy miként állapítható meg a Microsoft Excel bővítmény a mi-ha analízishez használható Microsoft Excel bővítményekkel.

Hogyan tudom megállapítani a nyereségességet maximalizáló havi szorzatot?

A cégeknek gyakran kell meghatározniuk az egyes termékek havi rendszerességgel számított mennyiségét. A termék -összekeverési probléma a legegyszerűbben azzal jár, hogy miként állapítható meg, hogy az egyes termékek hány hónapig legyenek létrehozva a nyereség maximalizálása érdekében. A termékek keverékének általában az alábbi korlátozásokat kell betartania:

  • A Product mix nem tud további erőforrásokat használni, mint amennyit elérhető.

  • Az egyes termékekhez korlátozott kereslet szükséges. Nem lehet több terméket gyártani egy hónap alatt, mint amennyit a kereslet diktál, mert a többlet-termelés kárba vész (például egy romlandó drog).

Most már megoldja a következő példát a szorzat-összekeverési problémáról. A probléma megoldásához keresse meg a Prodmix. xlsx fájlt, amely a 27-1 ábrán látható.

Book image

Tegyük fel, hogy egy olyan gyógyszergyártó cég dolgozunk, amely hat különböző terméket gyárt a növénye számára. Az egyes termékek előÁllításához munkamennyiség és alapanyag szükséges. A 4-ös sor az 27-1 ábrán az egyes termékek egy kilójának elkészítéséhez szükséges órákat jeleníti meg, az 5-ös sor pedig az egyes termékek egy kilójának elkészítéséhez szükséges pénzanyag kilóit mutatja. Ha például egy 1 kilós terméket hoz létre, a szüléshez hat óra szükséges, és a 3,2 kiló alapanyag. Minden egyes gyógyszer esetében az ár/font a 6-os sorba kerül, a 7., a kilogrammonkénti egységár pedig a 9-es sorból van megadva. Például a 2. termék a $11,00-ban egy kilón keresztül értékesíti az-os egységárat, és a $5,70-as hozamot is támogatja. A havi keresletet minden egyes gyógyszer esetében a 8-as sor adja meg. A 3-as szorzat értéke például 1041 font. Ebben a hónapban 4500-munkaidőt és 1600 kiló alapanyag áll rendelkezésre. Hogyan tudja ez a cég maximalizálni a havi nyereséget?

Ha semmit sem tudott az Excel Solverrel kapcsolatban, akkor a probléma megoldásához egy munkalapot kell létrehoznia a szorzattal társított profit és erőforrás kihasználtságának nyomon követéséhez. Ezután próbaverzióval és hibával is elvégezheti, hogy a termékek keveréke a profit optimalizálása érdekében ne használjon több munkamennyiséget vagy nyersanyagot, mint amennyit elérhető, és ne használjon semmilyen, a keresletet meghaladó drogot. Ebben a folyamatban csak a próbaverzió és a hiba szakaszában használjuk a Solvert. A Solver egy olyan optimalizálási motor, amely hibátlanul végrehajtja a próba-és hiba-keresést.

A termékek összekeverése probléma megoldásának kulcsa az adott szorzat-összetételhez társított erőforrás-kihasználtság és haszon hatékony kiszámítása. A számítások elvégzéséhez használható fontos eszköz a SZORZATÖSSZEG függvény. A SZORZATÖSSZEG függvény összeszorozza a megfelelő értékeket a cellák tartományában, és az értékek összegét adja eredményül. A SZORZATÖSSZEG minden egyes kiértékeléséhez használt cellának azonos méretűnek kell lennie, ami azt jelenti, hogy a SZORZATÖSSZEG két sorból vagy két oszlopból is használhatja, de nem egy oszloppal és egy sorral.

Példaként, hogy hogyan tudjuk használni a SZORZATÖSSZEG függvényt a termékek összekeverése során, próbálkozzon az erőforrás-használat kiszámításával. A munkamennyiség kihasználtsága a következőképpen számítható ki:

(Munkamennyiség 1 kiló drog 1) * (1) * (gyógyszeres 1 font) +
(kitermeltmunka egy kilóval a drog 2) * (gyógyszer 2 font )
(Munkamennyiség 1 kiló a drog 6) * (a gyógyszer 6 kilót termelt)

Több unalmas módon kiszámítható a munkamennyiség, mint a D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Hasonlóképpen, a nyersanyag-használatot D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + * "H5 + I2 * i5" értékre lehet számítani. Ezek a képletek azonban hat termék munkalapjára való beírása időigényes. Képzelje el, hogy mennyi ideig fog tartani, ha egy olyan céggel (például 50-termékekkel) dolgozott, amely a gyárban van. A munkamennyiség és a nyersanyag-használat kiszámításának sokkal egyszerűbb módja, ha a D14-ról D15 a képlet SZORZATÖSSZEG ($D $2: $I $2, D4: i4). A képlet kiszámítja a D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (ez a munkamennyiség), de sokkal könnyebben beírható! Figyelje meg, hogy a $ jelet a D2: i2 tartománnyal használom, hogy a képlet másolásakor továbbra is rögzítem a terméket a 2-es sorból. A D15 cellában lévő képlet a nyersanyag-felhasználást számítja ki.

Hasonló módon a nyereséget a következőhöz hasonló módon határozzuk meg:

(Drug 1 profit/font) * (1 kilós kábítószer-előállítás) +
(Drug 2 profit per font) * (gyógyszer 2 font) +.. .
(6 gyógyszeres haszon) * (a gyógyszer 6 font )

A D12 a képlet SZORZATÖSSZEG (D9: i9, $D $2: $I $2)képlettel egyszerűen kiszámítható a profit.

Most már megadhatja a Product mix Solver modell három összetevőjét.

  • Célcella.Célunk az, hogy maximalizálja a profitot (a cella D12 kiszámítva).

  • Cellák módosításaAz egyes termékekből előállított fontok száma (a D2: i2 cellatartomány listájában)

  • Korlátozásokkal. Az alábbi feltételekkel rendelkezünk:

    • Ne használjon több munkamennyiséget vagy nyersanyagot, mint amennyit elérhető. Azaz a cellák D14: D15 (a felhasznált erőforrások) értéke nem lehet kisebb, mint a cellák f14: F15 (a rendelkezésre álló erőforrások).

    • Ne készítsen több gyógyszert, mint amennyi a keresletben van. Vagyis a D2: i2 cellákban szereplő értékek (az egyes gyógyszerekből előállított font) értéke nem lehet kisebb, mint az egyes gyógyszerek iránti kereslet (a következő cellákban D8: i8).

    • Nem lehet negatív összeget kitermelni bármely drogból.

Bemutatjuk, hogy miként adhatók meg a célcella, módosíthatja a cellákat és a kényszereket a Solverben. Ezután mindössze annyit kell tennie, hogy a megoldás gombra kattintva megkeresi a nyereség maximalizálása terméket.

A kezdéshez kattintson az adatlapra, és az elemzés csoportban kattintson a Solver gombra.

Megjegyzés:  Ahogy az "az Excel Solverrel való optimalizálás ismertetése" című 26., a Solvert a Microsoft Office gombra, majd az Excel beállításai elemre kattintva, majd a bővítmények követik. A kezelés listában kattintson az Excel-bővítmények elemre, jelölje be a Solver bővítmény jelölőnégyzetet, és kattintson az OK gombra.

Ekkor megjelenik a Solver paraméterei párbeszédpanel, az 27-2 ábra szerint.

Book image

Jelölje be a célcella jelölőnégyzetet, majd válassza ki a profit (cella D12) cellát. Jelölje be a cellák módosításával jelölőnégyzetet, majd mutasson a D2: i2 értékre, amely az egyes drogok által termelt kilókat tartalmazza. Ekkor a párbeszédpanelnek meg kell néznie az 27-3 ábrát.

Book image

Most már készen áll arra, hogy kényszereket vegyen fel a modellbe. Kattintson a Hozzáadás gombra. Megjelenik a kényszer hozzáadása párbeszédpanel a 27-4 ábrán látható módon.

Book image

Az erőforrás-használati kényszerek hozzáadásához kattintson a cellahivatkozást tartalmazó mezőre, majd válassza ki a tartomány D14: D15. Válassza a < = elemet a középső listából. Kattintson a kényszer mezőbe, majd jelölje ki a f14: F15. A kényszer hozzáadása párbeszédpanel ekkor úgy néz ki, mint a 27-5 ábra.

Book image

Most már biztos lehet benne, hogy amikor a Solver a változó cellák különböző értékeit kísérli meg használni, csak a D14< = f14 (az alkalmazott munkamennyiség kisebb vagy egyenlő) és a D15< = F15 (a felhasznált nyersanyag kisebb vagy egyenlő). a rendelkezésre álló nyersanyagok is megtekinthetők. Kattintson a Hozzáadás gombra az igény korlátainak megadásához. Töltse ki a kényszer hozzáadása párbeszédpanelt az 27-6 ábra szerint.

Book image

Ezekkel a korlátozásokkal biztosíthatja, hogy a Solver a változó cellák értékeinek eltérő kombinációját adja meg, csak az alábbi paramétereket teljesítő kombinációk lesznek figyelembe véve:

  • D2< = D8 (a kábítószer 1-es verzióban termelt mennyiség kisebb vagy egyenlő a kábítószer 1)

  • E2< = E8 (a drog 2 előállításának összege kisebb vagy egyenlő, mint a drog 2)

  • F2< = F8 (a gyógyszer 3 termékből előállított mennyiség kisebb vagy egyenlő a kábítószer-3 iránti igénnyel)

  • G2< = G8 (a drog 4 termékhez gyártott mennyiség kisebb vagy egyenlő a kábítószer-4-előfizetéssel)

  • H2< = H8 (a drog 5 termékből előállított mennyiség kisebb vagy egyenlő a kábítószer 5 igényével)

  • I2< = i8 (a gyógyszer 6 termékből előállított mennyiség kisebb, mint a kábítószer-6 kereslete)

Kattintson a kényszer hozzáadása párbeszédpanel OK gombjára. A Solver ablaka az 27-7 ábra szerint fog kinézni.

Book image

Azt a kényszert adja meg, hogy a cellák módosítása nem negatív a Solver beállításai párbeszédpanelen. A Solver paraméterei párbeszédpanelen kattintson a beállítások gombra. Jelölje be a lineáris modell feltételezése jelölőnégyzetet, és a nem negatív jelölőnégyzetet, amint az 27-8 ábra a következő oldalon látható. Kattintson az OK gombra.

Book image

A nem negatív megjelenítés jelölőnégyzet bejelölésével biztosíthatja, hogy a Solver csak a változó cellák kombinációit veszi figyelembe, amelyben az egyes változó cellák nem negatív értéket feltételeznek. Bejelöltük a lineáris modell jelölőnégyzetet, mert a termék-összekeverési probléma a Solver speciális típusa, amelyet lineáris modellneknevezünk. A Solver-modellek az alábbi feltételek szerint lineárisak:

  • A célcella értékét úgy számítja ki a program, hogy összeadja az űrlap feltételeit (változó cella) * (állandó).

  • Minden korlátozás megfelel a "lineáris modell követelményének". Ez azt jelenti, hogy minden korlátozás kiértékelésének eredménye az űrlap (változó cella) * (állandó) és az összegek állandóval való összehasonlítása.

Miért lineáris ez a Solver probléma? A célcella (haszon) kiszámítása a következő képlet szerint történik

(Drug 1 profit/font) * (1 kilós kábítószer-előállítás) +
(Drug 2 profit per font) * (gyógyszer 2 font) +.. .
(6 gyógyszeres haszon) * (a gyógyszer 6 font )

Ez a számítás a célcella értékének egy olyan mintáját követi, amely az űrlap (változó cella) * (állandó)feltételeit foglalja össze.

A munkamennyiség-megkötés kiértékelése az eredményül kapott (a gyógyszer 1 kilón alapuló munkaMennyiség) * (a gyógyszer 1 kilós) * (a gyógyszer 2 font) (Labor US a gyógyszer 6 (gyógyszer 6) * ( a gyógyszer 6 font) a rendelkezésre álló munkához.

Ezért a munkakényszer kiértékelése az űrlap (változó cella) * (állandó) feltételeit, valamint az összegek állandóval való összehasonlítását adja meg. A munkakényszer és a nyersanyag-megkötés egyaránt megfelel a lineáris modell követelményeinek.

Keresleti kényszerek az űrlapon

(Drug 1 termelt) < = (Drug 1 Demand)
(Drug 2 Produced) < = (Drug 2 Demand)
§
(Drug 6 Produced) < = (Drug 6 Demand)

Minden igény szerinti megkötés megfelel a lineáris modell követelményeinek is, mivel az egyes adatok kiértékelése az űrlap (változó cella) * (állandó) és az összegek állandóval való összevetésével történik.

Miután kimutatta, hogy a szorzat mix modell lineáris modell, miért érdemes figyelni?

  • Ha egy Solver-modell lineáris, és a lineáris modell kiválasztása beállítást választja, a Solver garantáltan optimális megoldást keres a Solver modellre. Ha egy Solver-modell nem lineáris, a Solver lehet, hogy nem találja az optimális megoldást.

  • Ha egy Solver-modell lineáris, és a lineáris modell bekapcsolása beállítást választja, a Solver nagyon hatékony algoritmust használ (a simplex módszer) a modell optimális megoldásának megkereséséhez. Ha egy Solver-modell lineáris, és nem jelöli ki a lineáris modellt, a Solver nagyon hatékony algoritmust használ (az GRG2 módszer), és nehézséget okozhat a modell optimális megoldásának megtalálása.

Miután a Solver beállításai párbeszédpanelen az OK gombra kattintott, visszatérjünk a fő Solver párbeszédpanelre, amely a 27-7 ábra korábbi részén látható. Ha a megoldás gombra kattint, a Solver kiszámítja az optimális megoldást (ha létezik) a szorzat mix modellhez. Amint azt a 26. fejezetben kifejtettem, az optimális megoldás a termék-összevonási modellnek az összes lehetséges megoldás esetében maximalizálja a nyereséget. A kivitelezhető megoldás a minden korlátozást kielégítő cella értékek halmaza. Az 27-9 ábra által megjelenített cellák értékei kivitelezhetők, mert az összes termelési szint nem negatív, a termelés szintje nem haladja meg a keresletet, és az Erőforrás kihasználtsága nem haladja meg a rendelkezésre álló erőforrásokat.

Book image

A következő oldal 27-10-as ábrán látható változó cellák értékei az alábbi okok miatt nem kivitelezhető megoldást jelentenek:

  • Több mint 5%-ot gyártunk a drog iránt.

  • A rendelkezésre állónál több munkamennyiséget használunk.

  • A rendelkezésre állónál több nyersanyagot használunk.

Book image

A megoldás gombra kattintás után a Solver gyorsan megtalálhatja az optimális megoldást az 27-11 ábra segítségével. Ha meg szeretné őrizni az optimális megoldási értékeket a munkalapon, válassza a Solver-oldat megőrzése lehetőséget.

Book image

A gyógyszergyárak maximalizálhatja a havi nyereséget az $6 625,20 szintjén, ha 596,67 kilót termel a drogokból 4, 1084 kilót a kábítószer 5, és nem a többi drogot sem! Nem lehet megállapítani, hogy az $6 625,20 maximális profitját más módon tudjuk-e elérni. Biztos lehet benne, hogy a korlátozott erőforrásokkal és a kereslettel nem lehet több mint $6 627,20 ebben a hónapban.

Tegyük fel, hogy az egyes termékek iránti keresletet teljesíteni kell. (Lásd: a nem kivitelezhető megoldás munkalap a Prodmix. xlsx fájlban.) Ezt követően módosítania kell a D2: I2< = D8: i8 a D2: I2> = D8: i8. Ehhez nyissa meg a Solvert, és válassza a D2: I2< = D8: i8 kényszer lehetőséget, majd kattintson a módosítás gombra. Megjelenik a kényszer módosítása párbeszédpanel a 27-12 ábrán látható módon.

Book image

Válassza a > = lehetőséget, majd kattintson az OK gombra. Most már meggyőződött arról, hogy a Solver csak a minden igénynek megfelelő cellák értékének módosítását fogja mérlegelni. Ha a megoldás gombra kattint, megjelenik a következő üzenet: "a Solver nem talált megoldást." Ez az üzenet nem jelenti azt, hogy a modellben hibát vétett, de a korlátozott erőforrásokkal nem tudjuk teljesíteni a keresletet az összes termék esetében. A Solver egyszerűen azt mondja, hogy ha az egyes termékek iránti keresletet meg szeretné felelniük, több munkát, további nyersanyagot vagy több nyersanyagot kell hozzáadni.

Lássuk, mi történik, Ha megengedjük, hogy minden termékhez korlátlan keresletet lehessen benyújtani, és megengedjük, hogy az egyes gyógyszerekből negatív mennyiséget állítson elő. (Ezt a Solver-problémát az értékek beállítása nem konvergáló munkalap a Prodmix. xlsx fájlban című témakörben tekintheti meg.) Ha meg szeretné keresni a helyzet optimális megoldását, nyissa meg a Solvert, kattintson a beállítások gombra, majd törölje a jelet a nem negatív feltételezések jelölőnégyzetből. A Solver paraméterei párbeszédpanelen jelölje be a D2: I2< = D8: i8, majd a Törlés gombra kattintva távolítsa el a kényszert. Ha a megoldás gombra kattint, a Solver a "cellák értékeinek beállítása nem konvergál" üzenetet adja eredményül. Ez az üzenet azt jelzi, hogy ha a célcella teljes méretű (például példaként), akkor lehetséges, hogy a célcella értéke önkényes. (Ha a célcella minimális értékre van állítva, akkor a "cellák értékeinek beállítása nem konvergál" üzenet a "nem konvergál" beállítással valósítható meg.) A helyzetünk, ha megengedi a drog negatív előállítását, valójában a "Create" erőforrásokat használjuk, amelyekkel tetszőlegesen nagy mennyiségű egyéb gyógyszert hozhat létre. A korlátlan keresletet tekintve ez lehetővé teszi számunkra, hogy korlátlan nyereséget hozzunk igénybe. Egy valós helyzetben nem lehet végtelen pénzösszeget felkínálni. Röviden: Ha az "értékek beállítása nem konvergál" üzenet jelenik meg, akkor a modellben hiba lép fel.

  1. Tegyük fel, hogy a gyógyszergyárak a jelenlegi munkaerőköltségek szerint legfeljebb 500 órányi munkamennyiséget vásárolhatnak a $1 óránként. Hogyan lehet maximalizálni a profitot?

  2. Egy chip-gyártó üzemben négy technikus (A, B, C és D) gyárt három terméket (1, 2 és 3). Ebben a hónapban a chip gyártója eladhatja a 2 termék 80-egységét, a 2-es termék 50 egységét, a 3-as számú 50-es szorzatot. A technikus csak az 1 és A 3 terméket teszi elérhetővé. A "B" technikus csak az 1 és a 2 terméket tudja kihozni. A technikus C-technikus csak a 3 terméket teheti elérhetővé. A technikus csak a 2-es terméket tudja kivenni. A termékek minden egyes gyártott egység esetében a következő nyereséget járulnak hozzá: 1 szorzat, $6; 2 szorzat, $7; és szorzat 3, $10. Az egyes technikusok által a termékek előállításához szükséges idő (óra) az alábbiak szerint történik:

    Termék

    Technikus A

    B technikus

    Technikus C

    Technikus D

    1

    2

    2,5

    Nem hajthatók végre

    Nem hajthatók végre

    2

    Nem hajthatók végre

    3

    Nem hajthatók végre

    3,5

    3

    3

    Nem hajthatók végre

    4

    Nem hajthatók végre

  3. Minden szakember havonta 120 órát dolgozhat. Hogyan tudja a chip gyártója maximalizálni a havi nyereséget? Tegyük fel, hogy tört számú egység hozható létre.

  4. A számítógép gyártója egereket, billentyűzeteket és videojáték-botkormányokat hoz létre. Az egységnyi profit, a munkaegységenkénti munkamennyiség, a havi kereslet és az egységnyi gépi használati idő a következő táblázatban látható:

    Egerek

    Billentyűzetek

    Joystick

    Haszon/egység

    $8

    $11

    $9

    Munka kihasználtsága/egysége

    .2 óra

    .3 óra

    .24 óra

    Gépi idő/egység

    .04 óra

    .055 óra

    .04 óra

    Havi kereslet

    15 000

    27 000

    11 000

  5. Havonta összesen 13 000 munkaóra és 3000 óra áll rendelkezésre. Hogyan tudja a gyártó maximalizálni a havi haszon hozzájárulást az üzemből?

  6. A kábítószeres példa kiJavítása feltételezve, hogy az 200-egységek minimális keresletét minden egyes gyógyszer esetében teljesíteni kell.

  7. A Jason gyémánt karkötőket, nyakláncokat és fülbevalókat készít. Havonta legfeljebb 160 órát szeretne dolgozni. 800 uncia gyémántot tartalmaz. Az egyes termékek elkészítéséhez szükséges gyémántok profitja, munkaideje és uncia uncia alább láthatók. Ha az egyes termékek iránti kereslet korlátlan, hogyan lehet a Jason maximalizálni a nyereséget?

    Termék

    Egységnyi haszon

    Munkaórák egységenként

    A rombuszok egységenkénti uncia

    Karkötő

    30 000 Ft

    .35

    1.2

    Nyaklánc

    20 000 Ft

    .15

    .75

    Fülbevaló

    10 000 Ft

    ,05

    0,5

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×