Függvény megoldása Excelben. Engedélyezés, eset használata képernyőképekkel

A „Megoldás keresése” egy Excel-bővítmény, amelyen keresztül lehetőség nyílik a legjobb megoldás kiválasztására a problémákra a megadott korlátozások alapján. A funkció lehetővé teszi az alkalmazottak ütemezését, a költségek vagy beruházások felosztását. A funkció működésének ismerete időt és erőfeszítést takarít meg.

Mi az a Megoldások keresése

Az Excel számos más lehetőségével kombinálva van egy kevésbé népszerű, de rendkívül szükséges funkció „Megoldás keresése”. Annak ellenére, hogy megtalálása nem egyszerű, megismerése, használata sok probléma megoldásában segít. Az opció feldolgozza az adatokat és a megengedettek közül az optimális megoldást adja. A cikk leírja, hogyan működik közvetlenül a Megoldás keresése.

A „Megoldás keresése” funkció bekapcsolása

A hatékonyság ellenére a szóban forgó lehetőség nincs jól látható helyen az eszköztáron vagy a helyi menüben. A legtöbb felhasználó, aki Excelben dolgozik, nincs tudatában annak jelenlétének. Alapértelmezés szerint ez a funkció le van tiltva, megjelenítéséhez hajtsa végre a következő műveleteket:

1. Nyissa meg a „Fájl”-t a megfelelő névre kattintva.
2. Kattintson a „Beállítások” szakaszra.
3. Ezután válassza ki a „Kiegészítők” alszakaszt. Itt megjelenik a program összes kiegészítője, alul megjelenik a „Management” felirat. A jobb oldalon található egy felugró menü, ahol ki kell választania az „Excel-bővítmények” lehetőséget. Ezután kattintson a „Go” gombra.
   

   

4. A monitoron megjelenik egy további „Bővítmények” ablak. Jelölje be a kívánt funkció melletti négyzetet, majd kattintson az OK gombra.
5. A kívánt funkció megjelenik az „Adatok” rész jobb oldalán lévő szalagon.

A modellekről

Ez az információ rendkívül hasznos lesz azok számára, akik csak most ismerkednek az „optimalizálási modell” fogalmával. A „Megoldás keresése” használata előtt ajánlott tanulmányozni az anyagokat a modellek építési módszereiről:

• a vizsgált lehetőség lehetővé teszi a legjobb módszer meghatározását a beruházásokra, a helyiségek berakodására, áruszállításra vagy egyéb olyan tevékenységekre, ahol a legjobb megoldás megtalálása szükséges.
• Az „optimális módszer” egy ilyen helyzetben azt jelentené, hogy növeli a bevételt, csökkenti a költségeket, javítja a minőséget stb.

Tipikus optimalizálási feladatok:

• Gyártási terv meghatározása, amely során a kibocsátott áruk értékesítéséből származó haszon maximális lesz.
• Szállítási térképek meghatározása, melynek során a szállítási költségek minimalizálódnak.
• Keressen több gép elosztását különféle típusú munkákhoz, hogy a termelési költségek csökkenjenek.
• A munkavégzés legrövidebb időtartamának meghatározása.

Fontos! A feladat formalizálásához olyan modellt kell készíteni, amely tükrözi a tárgykör főbb paramétereit. Az Excelben a modell változókat használó képletek halmaza. A figyelembe vett opció olyan mutatókat keres, amelyeknél a célfüggvény nagyobb (kisebb) vagy egyenlő a megadott értékkel.

Előkészítő szakasz

Mielőtt elhelyezne egy funkciót a szalagon, meg kell értenie az opció működését. Például vannak információk a táblázatban feltüntetett áruk értékesítéséről. A feladat az, hogy minden tételhez engedményt rendeljünk, ami 4.5 millió rubel lenne. A paraméter egy cél nevű cellában jelenik meg. Ez alapján más paramétereket számítanak ki.

A mi feladatunk az lesz, hogy kiszámoljuk azt a kedvezményt, amellyel a különféle termékek eladási összegei megszorozódnak. Ezt a 2 elemet a következő képlettel kapcsoljuk össze: =D13*$G$2. Ahol a D13-ban a megvalósítás teljes mennyisége van írva, és $G$2 a kívánt elem címe.

A funkció használata és beállítása

Amikor a képlet készen áll, magát a függvényt közvetlenül kell használnia:

1. Váltson az „Adatok” szakaszra, és kattintson a „Megoldás keresése” elemre.

2. Megnyílik az „Opciók” menü, ahol beállítja a szükséges beállításokat. A „Célfüggvény optimalizálása:” sorban meg kell adni azt a cellát, ahol a kedvezmények összege megjelenik. Lehetőség van a koordináták önálló előírására vagy a dokumentumból való választásra.

3. Ezután más paraméterek beállításaihoz kell lépnie. A „Címzett:” részben lehetőség van a maximális és minimális korlát vagy pontos szám megadására.

4. Ezután a „Változók értékeinek módosítása:” mező kitöltésre kerül. Ide kerül a kívánt cella adata, amely egy adott értéket tartalmaz. A koordinátákat önállóan regisztrálja, vagy a dokumentum megfelelő cellájára kattint.

5. Ezután a „Korlátozások szerint:” fül szerkesztésre kerül, ahol az alkalmazott adatokra vonatkozó korlátozások vannak beállítva. Például a tizedes törtek vagy a negatív számok ki vannak zárva.

6. Ezt követően megnyílik egy ablak, amely lehetővé teszi korlátozások hozzáadását a számításokhoz. A kezdő sor egy cella vagy egy teljes tartomány koordinátáit tartalmazza. A feladat feltételeit követve megjelenik a kívánt cella adatai, ahol megjelenik a kedvezményjelző. Ezután meghatározzák az összehasonlító előjelet. A „nagyobb vagy egyenlő” értékre van beállítva, hogy a végső érték ne legyen mínuszjellel. A 3. sorban beállított „Limit” ebben a helyzetben 0. A „Hozzáadás” gombbal korlátot is beállíthat. A következő lépések ugyanazok.

7. A fenti lépések végrehajtása után a beállított határérték megjelenik a legnagyobb sorban. A lista nagy lehet, és a számítások összetettségétől függ, azonban egy adott helyzetben 1 feltétel elegendő.

8. Ezen kívül lehetőség van más speciális beállítások kiválasztására is. A jobb alsó sarokban található az „Opciók” opció, amely lehetővé teszi ezt.

9. A beállításokban beállíthatja a „Korlátozás pontossága” és a „Megoldás korlátai”. A mi helyzetünkben nincs szükség ezeknek a lehetőségeknek a használatára.

10. A beállítások elvégzése után a funkció elindul – kattintson a „Megoldás keresése” gombra.

11. Miután a program elvégzi a szükséges számításokat és kiadja a végső számításokat a szükséges cellákban. Ezután megnyílik egy ablak az eredményekkel, ahol a találatok mentése / törlése történik, vagy a keresési paraméterek egy újnak megfelelően konfigurálódnak. Amikor az adatok megfelelnek a követelményeknek, a megtalált megoldás mentésre kerül. Ha előzetesen bejelöli a „Vissza a megoldáskeresési lehetőségek párbeszédpanelhez” négyzetet, megnyílik egy ablak a funkcióbeállításokkal.

12. Előfordulhat, hogy a számítások hibásnak bizonyultak, vagy a kiindulási adatok módosítására van szükség más mutatók megszerzéséhez. Ilyen helyzetben újra meg kell nyitnia a beállítások ablakot, és még egyszer ellenőriznie kell az információkat.
13. Ha az adatok pontosak, alternatív módszer is használható. Ehhez kattintson az aktuális opcióra, és válassza ki a legmegfelelőbb módszert a megjelenő listából:

• Megoldás keresése általánosított gradiens használatával nemlineáris problémákra. Alapértelmezés szerint ez az opció használatos, de lehetséges másokat is használni.
• Lineáris problémák megoldásának keresése szimplex módszer alapján.
• Evolúciós keresés használata egy feladat elvégzéséhez.

Figyelem! Ha a fenti opciók nem tudták megbirkózni a feladattal, érdemes újra ellenőrizni az adatokat a beállításokban, mivel gyakran ez a fő hiba az ilyen feladatoknál.

14. A kívánt kedvezmény megérkezésekor marad hátra annak alkalmazása az egyes cikkekre vonatkozó engedmények összegének kiszámításához. Ehhez a „Kedvezmény összege” oszlop kezdő eleme kiemelve, a képlet megírása «=D2*$G$2» és nyomja meg az „Enter” gombot. A dollárjeleket úgy helyezik el, hogy amikor a képletet a szomszédos vonalakra feszítik, a G2 nem változik.

15. A kezdeti tételre vonatkozó kedvezmény összege most megtörténik. Ezután vigye a kurzort a cella sarka fölé, amikor „plusz” lesz, megnyomja az LMB-t, és a képletet a kívánt vonalakra nyújtja.
16. Ezt követően végre kész lesz az asztal.

Keresési beállítások betöltése/mentése

Ez a beállítás akkor hasznos, ha különféle kényszerbeállításokat alkalmaz.

1. A Megoldáskereső beállításai menüben kattintson a Betöltés/Mentés gombra.
2. Adja meg a modellterület tartományát, majd kattintson a Mentés vagy a Betöltés gombra.

A modell elmentésekor hivatkozás kerül egy üres oszlop 1 cellájára, ahová az optimalizálási modell kerül. A modell betöltése során a rendszer hivatkozást ír be az optimalizálási modellt tartalmazó teljes tartományra.

Fontos! A Megoldásbeállítások menü utolsó beállításainak mentéséhez a rendszer egy munkafüzetet ment. Minden benne lévő lapnak megvannak a saját Solver-bővítményei. Ezen kívül lehetőség van egy lapon több feladat beállítására is a „Betöltés vagy mentés” gombra kattintva, az egyes feladatok mentése érdekében.

Egy egyszerű példa a Solver használatára

A tartályt konténerekkel meg kell tölteni, hogy tömege maximális legyen. A tartály térfogata 32 köbméter. m. Egy megtöltött doboz súlya 20 kg, térfogata 0,15 köbméter. m. Doboz – 80 kg és 0,5 cu. m. A konténerek összlétszámának legalább 110 db-nak kell lennie. Az adatok a következőképpen vannak elrendezve:

A modellváltozók zölddel vannak jelölve. A célfüggvény piros színnel van kiemelve. Korlátozások: a legkisebb konténerszám (110 vagy annál nagyobb) és tömeg szerint (=ÖSSZEG(B8:C8,B6:C6) – teljes tára tömeg a tartályban.

Analógia útján a teljes mennyiséget tekintjük: =ÖSSZEG(B7:C7,B8:C8). Egy ilyen képlet szükséges a tartályok teljes térfogatának korlátozásához. Ezután a „Megoldás keresése” segítségével hivatkozások kerülnek be a változókkal, képletekkel és magukkal az indikátorokkal rendelkező elemekre (vagy adott cellákra mutató hivatkozásokra). Természetesen a konténerek száma egész szám (ez is egy korlátozás). Nyomjuk meg a „Megoldást találni” gombot, aminek eredményeként akkora konténerszámot találunk, amikor az össztömeg maximális és minden korlátozást figyelembe veszünk.

A megoldás keresése nem talált megoldást

Egy ilyen értesítés akkor jelenik meg, ha a kérdéses függvény nem talált olyan változó pontszámok kombinációit, amelyek megfelelnek az egyes megszorításoknak. Simplex módszer alkalmazásakor nagyon valószínű, hogy nincs megoldás.

Ha nemlineáris problémák megoldási módszerét alkalmazzuk, minden esetben a változók kezdeti mutatóiból kiindulva, ez azt jelzi, hogy a lehetséges megoldás messze áll ezektől a paraméterektől. Ha a függvényt a változók más kezdeti mutatóival futtatja, akkor valószínűleg van megoldás.

Például a nemlineáris módszer alkalmazásakor a táblázat változós elemei nem lettek kitöltve, és a függvény nem talált megoldást. Ez nem jelenti azt, hogy nincs megoldás. Most egy bizonyos értékelés eredményeit figyelembe véve más adatok kerülnek az elemekbe olyan változókkal, amelyek közel állnak a kapottakhoz.

Minden helyzetben először meg kell vizsgálnia a modellt, hogy nincs-e kényszerkonfliktus. Ez gyakran összefügg az arány vagy a korlátozó mutató helytelen kiválasztásával.

A fenti példában a maximális térfogatjelző 16 köbméter. m 32 helyett, mert egy ilyen korlátozás ellentmond a minimális ülőhelyszámra vonatkozó mutatóknak, mivel az 16,5 köbméternek felel meg. m.

Következtetés

Ennek alapján az Excel „Megoldás keresése” opciója segít olyan konkrét problémák megoldásában, amelyeket a szokásos módon meglehetősen nehéz vagy lehetetlen megoldani. A módszer alkalmazásának nehézsége az, hogy kezdetben ez az opció rejtve van, ezért a legtöbb felhasználó nem tud a jelenlétéről. Ezenkívül a funkciót meglehetősen nehéz megtanulni és használni, de megfelelő kutatással nagy előnyökkel jár és megkönnyíti a számításokat.