A legtöbb táblázat segítségével megoldott probléma magában foglalja a kívánt eredmény megtalálását ismert forrásadatok felhasználásával. De az Excel rendelkezik olyan eszközökkel, amelyek lehetővé teszik az inverz probléma megoldását: válassza ki a kezdeti adatokat a kívánt eredmény eléréséhez.
Az egyik ilyen eszköz azMegoldás keresése, ami különösen kényelmes az úgynevezett „optimalizálási problémák” megoldására.
Ha még nem használtadMegoldás keresése, akkor telepítenie kell a megfelelő kiegészítőt.
Ezt így teheti meg:
az Excel 2007-nél régebbi verziókhoz a paranccsal menü Szolgáltatás --> Kiegészítők;
Excel 2007 óta párbeszédpanelen keresztülExcel-beállítások
Excel 2007 ótagombot az indításhozMegoldás keresése megjelenik a lapon Adat.
Az Excel 2007 előtti verziókban hasonló parancs jelenik meg a menübenSzolgáltatás
Nézzük a munkarendet Megoldás keresése egy egyszerű példa segítségével.
1. példa Bónusz elosztás
Tegyük fel, hogy Ön egy gyártási osztály vezetője, és méltányosan kell elosztania egy 100 000 rubel összegű bónuszt. osztály dolgozói között a hivatalos fizetésük arányában. Más szóval, ki kell választania egy arányossági együtthatót a fizetési bónusz nagyságának kiszámításához.
Először is készítünk egy táblázatot a kiindulási adatokkal és képletekkel, amelyekkel az eredményt meg kell kapni. Esetünkben az eredmény a prémium teljes összege. Nagyon fontos, hogy a célsejt (C8) képleteken keresztül kapcsolódjon a módosítani kívánt cellához (E2). A példában közbülső képletekkel kapcsolódnak össze, amelyek kiszámítják a bónusz összegét minden alkalmazottra (C2:C7).
Most pedig indítsuk el Megoldás kereséseés a megnyíló párbeszédablakban állítsa be a szükséges paramétereket. A párbeszédpanelek megjelenése különböző verziók kicsit másképp:
Az Excel 2010 óta
Excel 2010 előtt
A gomb megnyomása utánMegoldás keresése (Futtatás)A kapott eredményt már láthatja a táblázatban. Ezzel egyidejűleg egy párbeszédpanel jelenik meg a képernyőnA megoldás keresésének eredményei.
Az Excel 2010 óta
Excel 2010 előtt
Ha a táblázatban látható eredmény megfelel Önnek, akkor a párbeszédpanelen Megoldás keresési eredményei sajtó RENDBENés rögzítse az eredményt a táblázatban. Ha az eredmény nem felel meg Önnek, kattintson a gombra Mégsemés térjen vissza a táblázat előző állapotába.
A probléma megoldása így néz ki
Fontos: a forrásadatok bármilyen módosítására, hogy új eredményt kapjunk Megoldás kereséseújra kell futnia.
Nézzünk egy másik optimalizálási problémát (a profit maximalizálása)
2. példa: Bútorgyártás (nyereségmaximalizálás)
A cég két A és B modellt gyárt előre gyártott könyvespolcokból.
Gyártásukat korlátozza az alapanyagok (kiváló minőségű táblák) elérhetősége és a gépi feldolgozási idő.
Minden A modellhez 3 m² terület szükségestáblák, és a B termékmodellhez - 4 m². A cég akár heti 1700 m² táblát kaphat beszállítóitól.
Minden termékmodell 12 perc gépi időt igényel, és a B termékmodell esetében - 30 perc. Hetente 160 óra gépidő használható.
Hány terméket kell a vállalatnak hetente gyártania az egyes modellekből a maximális profit eléréséhez, ha az A modell minden terméke 60 rubelt hoz. profit, és a B modell minden terméke 120 rubelbe kerül. nyereség?
Az eljárást már ismerjük.
Először táblákat készítünk forrásadatokkal és képletekkel. A cellák elrendezése a lapon teljesen önkényes lehet, amilyen kényelmes a szerző számára. Például, mint a képen
Indítsuk el Megoldás kereséseés a párbeszédablakban állítsa be a szükséges paramétereket
- A B12 célcella tartalmazza a profit kiszámításának képletét
- Optimalizálási paraméter - maximum
- Módosított cellák B9:C9
- Korlátozások: a talált értékeknek egésznek kell lenniük, nem negatívnak; a teljes gépidő nem haladhatja meg a 160 órát (hivatkozás a D16 cellára); az összes nyersanyag mennyisége nem haladhatja meg az 1700 m²-t (referenciacella D15). Itt a D15 és D16 cellákra mutató hivatkozások helyett számokat lehet megadni, de hivatkozások használatakor a korlátozások bármilyen módosítása közvetlenül a táblázatban elvégezhető
- Nyomja meg a gombot Megoldás keresése (Futtatás)és megerősítés után megkapjuk az eredményt
De még ha helyesen hozta létre a képleteket és beállította a korlátozásokat, az eredmény váratlan lehet. Például a probléma megoldása során a következő eredményt láthatja:
És ez annak ellenére, hogy a korlátozást meghatározták egész. Ilyen esetekben megpróbálhatja módosítani a beállításokat Megoldás keresése. Ehhez az ablakban Megoldás keresése nyomja meg a gombot Opciókés az azonos nevű párbeszédpanelbe jutunk
A kiválasztott paraméterek közül az első felelős a számítások pontosságáért. Csökkentésével pontosabb eredményt érhet el, esetünkben - egész értékeket. A kiemelt opciók közül a második (az Excel 2010-től elérhető) választ ad arra a kérdésre: hogyan lehet töredékes eredményeket elérni korlátozáskoregész? Kiderült Megoldás kereséseezt a korlátozást egyszerűen figyelmen kívül hagyták a bejelölt jelzővel összhangban.
3. példa: Szállítási probléma (költségminimalizálás)
Egy építőipari cég megrendelésére három szállítótól (kőbánya) szállítják a homokot öt fogyasztóhoz (építési terület). A kiszállítás költségét a tárgy költsége tartalmazza, ezért építőipari cégérdekelt abban, hogy építkezései homokszükségletét a lehető legolcsóbb módon elégítse ki.
Adott: homokkészletek kőbányákban; építkezési homokigények; szállítási költségek az egyes szállító-fogyasztó párok között.
Meg kell találni egy optimális szállítási sémát az igények kielégítésére (honnan és hova), amelyben a teljes szállítási költség minimális lenne.
Az ábrán látható egy példa a cellák helyére forrásadatokkal és korlátozásokkal, a kívánt cellákkal és a célcellával
A szürke cellákban soronkénti és oszloponkénti összegek, a célcellában pedig a teljes szállítási költség kiszámítására szolgáló képlet található..
Elindítjuk a megoldás keresését és beállítjuk a szükséges paramétereket (lásd az ábrát)
Kattintson Megoldás keresése (Futtatás)és kapja meg az alábbi eredményt
Végezetül azt javaslom, próbálja ki a kezét a használatban Megoldás kereséseés egy régi probléma megoldására használja:
Egy paraszt 100 marhát vett a piacon 100 rubelért. Egy bika 10 rubel, egy tehén 5 rubel, egy borjú 50 kopejkába kerül. Hány bikát, tehenet és borjút vett a paraszt?
A megoldás keresése egy kiegészítő Microsoft Excel, amellyel megtalálhatja optimális megoldás feladatokat a felhasználó által meghatározott korlátozások figyelembevételével.
Megfontoljuk, hogy megoldást találjunk a (ez a kiegészítő néhány változáson ment keresztül a korábbi verzió V .
Ebben a cikkben megnézzük:
- optimalizálási modell készítése MS EXCEL lapon
- beállítás Megoldás keresése;
- egyszerű példa (lineáris modell).
Telepítés Keressen megoldást
Csapat Megoldás keresése a csoportban van Elemzés a lapon Adat.
Ha a csapat Megoldás keresése a csoportban Elemzés nem érhető el, engedélyeznie kell az azonos nevű bővítményt.
Ehhez tegye a következőket:
- A lapon Fájl válassza ki a csapatot Opciók, majd a kategória Kiegészítők;
- A mezőn Ellenőrzés válasszon értéket Excel bővítményekés nyomja meg a gombot Megy;
- A mezőn Elérhető kiegészítők jelölje be az elem melletti négyzetet Megoldás kereséseés kattintson az OK gombra.
Jegyzet. Ablak Kiegészítők fülön is elérhető Fejlesztő. Hogyan lehet engedélyezni ezt a lapot.
A gomb megnyomása után Megoldás keresése a csoportban Elemzés, megnyílik a párbeszédpanel .
Gyakori használat mellett Megoldás keresése kényelmesebb a Panelről elindítani gyors hozzáférés, és nem az Adatok lapról. Ha egy gombot a Panelre szeretne helyezni, kattintson rá jobb gombbal, és válassza ki Hozzáadás a Gyorselérési eszköztárhoz.
A modellekről
Ez a rész azoknak szól, akik csak most ismerkednek az optimalizálási modell fogalmával.
Tanács. Használat előtt Megoldás keresése Nyomatékosan javasoljuk az optimalizálási problémák megoldásának és a modellek felépítésének szakirodalmának tanulmányozását.
Az alábbiakban egy kis oktatási program található ebben a témában.
Felépítmény Megoldás keresése segít meghatározni legjobb módja csináld valami:
- A „valami” magában foglalhatja a pénz kiosztását beruházásokra, raktár felrakodását, áruszállítást, vagy bármilyen más tárgyi tevékenységet, ahol az optimális megoldás megtalálásához szükséges.
- A "legjobb út" vagy az optimális megoldás ebben az esetben azt jelenti: a profit maximalizálása, a költségek minimalizálása, elérése legjobb minőség stb.
Íme néhány tipikus példák optimalizálási feladatok:
- Határozza meg, hogy a legyártott termékek értékesítéséből származó bevétel melyiknél a legmagasabb;
- Határozza meg, hogy a teljes szállítási költség melynél minimális;
- Állapítsa meg, hogy az előállítás összköltsége minimális lenne;
- Határozza meg az összes projektmunka elvégzésének minimális határidejét (kritikus út).
Az adott feladat formalizálásához olyan modell megalkotása szükséges, amely tükrözi a témakör lényeges jellemzőit (és nem tartalmaz kisebb részleteket). Meg kell jegyezni, hogy a modell optimalizált Megoldás keresése csak egy mutató szerint(ezt az optimalizált mutatót hívják cél funkció).
Az MS EXCEL-ben a modell egymással összefüggő képletek gyűjteménye, amelyek változókat használnak argumentumként. Ezek a változók általában csak érvényes értékeket vehetnek fel, a felhasználó által megadott korlátozások függvényében.
Megoldás keresése ezeknek a változóknak olyan értékeit választja ki (meghatározott korlátozások függvényében), hogy a célfüggvény maximális (minimális) vagy egy adott számértékkel egyenlő.
Jegyzet. A legegyszerűbb esetben a modell egyetlen képlettel írható le. Ezen modellek egy része optimalizálható az eszköz segítségével. Az első találkozás előtt Megoldás kereséseÉrdemes először részletesen megérteni a kapcsolódó eszközt.
Fő különbségek Paraméter kiválasztása-tól Megoldás keresése:
- Paraméter kiválasztása csak egyváltozós modellekkel működik;
- lehetetlen korlátozni a változókat;
- nem a célfüggvény maximumát vagy minimumát határozzák meg, hanem annak egy bizonyos értékkel való egyenlőségét;
- csak akkor működik hatékonyan lineáris modellek, nemlineáris esetben megkeresi a lokális optimumot (a változó eredeti értékéhez legközelebbi).
Optimalizációs modell készítése MS EXCEL-ben
Megoldás keresése optimalizálja a célfüggvény értékét. A célfüggvény egy olyan képlet, amely egyetlen értéket ad vissza egy cellában. A képlet eredményének a modellváltozóktól kell függenie (nem feltétlenül közvetlenül, hanem más képletek számítási eredményén keresztül).
Modellkorlátozások vonatkozhatnak magukra a változók variációs tartományára és az ezektől a változóktól függő egyéb modellképletek számítási eredményeire is.
A modellváltozókat és megszorításokat tartalmazó összes cellának csak a munkafüzet egyik lapján kell lennie. Paraméterek megadása egy párbeszédpanelen Megoldás keresése csak erről a lapról lehetséges.
A célfüggvénynek (cellának) szintén ezen a lapon kell elhelyezkednie. De a közbenső számítások (képletek) más lapokon is elhelyezhetők.
Tanács. Rendszerezze a modelladatokat úgy, hogy egy MS EXCEL lapon csak egy modell szerepeljen. Ellenkező esetben a számítások elvégzéséhez folyamatosan mentenie és betöltenie kell a beállításokat Megoldás keresése(lásd lent).
Mutassunk be egy algoritmust a munkához Megoldás keresése, amelyet maguk a fejlesztők ajánlanak (www.solver.com):
- Cellák meghatározása modellváltozókkal (döntési változókkal);
- Hozzon létre egy képletet egy cellában, amely kiszámítja a modell célfüggvényét;
- Hozzon létre képleteket a cellákban, amelyek kiszámítják az értékeket a megszorításokhoz képest (a kifejezés bal oldala);
- A párbeszédpanel használatával Megoldás keresése hivatkozásokat adjon meg a változókat tartalmazó cellákhoz, a célfüggvényhez, a megszorítások képletéhez és maguknak a megszorításoknak az értékéhez;
- Fut Megoldás keresése hogy megtaláljuk az optimális megoldást.
Nézzük végig ezeket a lépéseket egy egyszerű példa segítségével.
Egyszerű használati példa Megoldás keresése
A konténert úgy kell megrakni áruval, hogy a konténer tömege maximális legyen. A konténer űrtartalma 32 köbméter. Az elemek dobozokban és ládákban vannak. Egy doboz áru súlya 20 kg, térfogata 0,15 m3. Doboz - 80 kg, illetve 0,5 m3. Szükséges, hogy a konténerek teljes száma legalább 110 darab legyen.
Ezeket a modelleket az alábbiak szerint rendezzük (lásd a példafájlt).
A modellváltozók (az egyes konténertípusok mennyisége) zöld színnel vannak kiemelve.
A célfüggvény (az összes doboz és láda össztömege) piros.
Modellkorlátozások: minimális konténermennyiség (>=110) és teljes térfogat (<=32) – синим.
A célfüggvény kiszámítása a képlet segítségével történik =ÖSSZEG(B8:C8,B6:C6) a konténerbe töltött összes doboz és láda össztömege.
Hasonlóképpen kiszámítjuk a teljes térfogatot - =ÖSSZEG(B7:C7,B8:C8). Ez a képlet szükséges a dobozok és ládák teljes térfogatának korlátozásához (<=32).
Ezenkívül a modellkorlátozás beállításához kiszámítjuk a konténerek teljes számát =SUM(B8:C8) .
Most a párbeszédpanel használatával Megoldás keresése Adjunk meg hivatkozásokat változókat, célfüggvényt, kényszerképleteket és maguknak a megszorításoknak az értékeit tartalmazó cellákra (vagy a megfelelő cellákra mutató hivatkozásokra).
Nyilvánvaló, hogy a dobozok és ládák számának egész számnak kell lennie - ez a modell másik korlátja.
A gomb megnyomása után Találj megoldást olyan számú dobozt és ládát találunk, amelynél összsúlyuk (objektív funkciója) maximális, ugyanakkor minden meghatározott korlátozás teljesül.
Folytatás
Valójában a fő probléma az optimalizálási problémák megoldása során Megoldás keresése Nem az elemző eszköz beállításának finomsága a lényeg, hanem az adott feladatnak megfelelő modell felépítésének helyessége. Ezért a többi cikkben kifejezetten a modellek építésére fogunk koncentrálni, mert a „görbe” modell gyakran az oka annak, hogy nem tudunk megoldást találni Megoldás keresése.
Gyakran könnyebb átnézni több tipikus problémát, megtalálni köztük egy hasonlót, majd ezt a modellt a feladatához igazítani.
Klasszikus optimalizálási feladatok megoldása segítségével Megoldás keresése megfontolt .
A Solver nem talált megvalósítható megoldást
Ez az üzenet akkor jelenik meg, amikor Megoldás keresése nem talált olyan változóérték-kombinációt, amely egyszerre teljesíti az összes korlátozást.
Ha használ Simplex módszer lineáris feladatok megoldására, akkor biztos lehet benne, hogy tényleg nincs megoldás.
Ha nemlineáris problémák megoldására olyan módszert használ, amely mindig a változók kezdeti értékeivel kezdődik, akkor ez azt is jelentheti, hogy a megvalósítható megoldás messze van ezektől a kezdeti értékektől. Ha futsz Megoldás keresése a változók más kezdeti értékeivel, akkor talán sikerül megoldást találni.
Képzeljük el, hogy egy probléma nemlineáris módszerrel történő megoldása során a változókat tartalmazó cellák üresen maradtak (azaz a kezdeti értékek 0), és Megoldás keresése nem talált megoldást. Ez nem jelenti azt, hogy valóban ne lenne megoldás (bár lehet, hogy ez a helyzet). Most egy bizonyos szakértői értékelés eredménye alapján egy másik értékkészletet írunk be a változókkal ellátott cellákba, amely az Ön véleménye szerint közel áll az optimálishoz (keresett). Ebben az esetben Megoldás keresése megoldást találhat (ha valóban létezik).
Jegyzet. A modell nemlinearitásának a számítási eredményekre gyakorolt hatásáról a cikk utolsó részében olvashat.
Mindenesetre (lineáris vagy nemlineáris) először elemezni kell a modellt a kényszerek konzisztenciájára, vagyis olyan feltételekre, amelyek nem teljesíthetők egyszerre. Ennek oka leggyakrabban az arány helytelen megválasztása (pl.<= вместо >=) vagy határérték.
Ha például a fent tárgyalt példában a maximális térfogat értéke 32 m3 helyett 16 m3-re van beállítva, akkor ez a korlátozás ellentmond a minimális férőhelyszámra vonatkozó korlátozásnak (110), mert a minimális férőhelyek száma 16,5 m3 térfogatnak felel meg (110 * 0,15, ahol 0,15 a doboz, azaz a legkisebb konténer térfogata). Ha a maximális térfogatkorlátot 16 m3-re állítja, Megoldás keresése nem talál megoldást.
17 m3-es határral Megoldás keresése talál megoldást.
Néhány beállítás Megoldás keresése
Megoldás módszere
A fent tárgyalt modell lineáris, azaz. a célfüggvényt (M a maximális összsúly) a következő egyenlettel fejezzük ki: M=a1*x1+a2*x2, ahol x1 és x2 a modellváltozók (a dobozok és ládák száma), a1 és a2 a súlyuk. Lineáris modellben a megszorításoknak a változók lineáris függvényeinek is kell lenniük. Esetünkben a V=b1*x1+b2*x2 térfogatkorlátot is egy lineáris függés fejezi ki. Nyilvánvalóan egy másik korlátozás – a konténerek maximális száma (n) – szintén lineáris x1+x2
Ebben az esetben a nemlineáris probléma megoldásának módszerét kell választani. Példák nemlineáris függőségekre: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, ahol x egy változó és V egy célfüggvény.
Gombok Hozzáadás, Szerkesztés, Törlés
Ezekkel a gombokkal modellkényszereket adhat hozzá, szerkeszthet és törölhet.
Reset gomb
Az összes beállítás eltávolításához Megoldás keresése kattintson a gombra Reset– a párbeszédablak törlődik.
Ez az opció kényelmes, ha különböző korlátozási lehetőségeket használ. A modellparaméterek mentésekor (gomb Betöltés/Mentés, majd kattintson a gombra Megtakarítás) azt javasoljuk, hogy jelölje ki a tartomány (oszlop) legfelső celláját, amelybe kerül: hivatkozás a célfüggvényre, hivatkozások változókkal, korlátozásokkal és megoldási módszerek paramétereivel rendelkező cellákra (a gombon keresztül érhető el Opciók). Mentés előtt győződjön meg arról, hogy ez a tartomány nem tartalmaz modelladatokat.
A mentett paraméterek betöltéséhez először nyomja meg a gombot Betöltés/Mentés, majd a megjelenő párbeszédpanelen a gombot Letöltés, majd adja meg a korábban elmentett beállításokat tartalmazó cellatartományt (nem adhatja meg csak a felső cellát). Kattintson az OK gombra. Erősítse meg a feladatparaméterek aktuális értékeinek visszaállítását és újakra cserélését.
Pontosság
A modell létrehozásakor a kutatónak kezdetben van némi becslése a célfüggvény és a változók variációs tartományára vonatkozóan. Figyelembe véve az MS EXCEL-ben végzett számításokat, javasolt, hogy ezek a variációs tartományok lényegesen magasabbak legyenek, mint a számítási pontosság (általában 0,001 és 0,000001 között van beállítva). A modellben szereplő adatok általában úgy vannak normalizálva, hogy a célfüggvény és a változók variációs tartománya 0,1 és 100 000 között legyen. Természetesen minden az adott modelltől függ, de ha a változók nagyobb mértékben változnak 5-6 nagyságrendnél nagyobb, akkor talán érdemes „durvítani” a modellt, például a logaritmus művelettel.
A bizonyos feltételeknek megfelelő Excel táblázatértékek optimalizálása összetett folyamat lehet. Szerencsére a Microsoft kínál egy kiegészítőt Problémamegoldás számszerű optimalizáláshoz. Bár ez a szolgáltatás nem tud minden problémát megoldani, hasznos eszköz lehet mi lenne, ha. Ez a bejegyzés a kiegészítőnek szól Problémamegoldás Excelben.
Felépítmény Problémamegoldás az Excel minden verziójában elérhető. Felhívjuk figyelmét, hogy a képernyőképek nem feltétlenül az Ön verzióját tükrözik. Bár egyes funkciók a bővítmény verziójától függően változhatnak, a funkciók gyakorlatilag változatlanok maradnak.
Mi az a Megoldások keresése
Megoldások keresése egy Excel bővítmény, amely a célcellák értékeinek megváltoztatásával segít megoldást találni. A cél lehet valamilyen célérték minimalizálása, maximalizálása vagy elérése. A problémát a beviteli feltételek vagy a felhasználó által meghatározott kényszerek módosítása oldja meg.
Hol lehet megoldást találni az Excelben
Felépítmény Megoldások keresése az Excelhez tartozik, de alapértelmezés szerint le van tiltva. Az engedélyezéséhez lépjen a lapra Fájl a csoporthoz Opciók. A megjelenő párbeszédpanelen Paraméterek válassza ki Kiegészítők -> Kezelés: KiegészítőkExcel -> Ugrás. Az ablakban Kiegészítők jelölje be a mező melletti négyzetet Megoldás keresése kattintson RENDBEN.
Most a lapon Adatúj csoport jelent meg Elemzés gombbal Megoldás keresése.
Példa a Solution Search használatára
Ez a bejegyzés egy használati eseten alapul . A fájl az Excel összes verziójával kompatibilis.
Probléma meghatározása
Tegyük fel, hogy van egy adathalmazunk, amely 8 elemből áll, és mindegyiknek megvan a maga értéke.
... és az értékeket két csoportba kell kombinálnunk, hogy ezeknek a csoportoknak az értékeinek összege megközelítőleg egybeessen.
Először minden elemet azonosítania kell egy csoportban.
Egy elem csoporthoz való csatolásának jelzésére eggyel (1), egyébként nullával (0) jelöljük.
A következő oszlopban összefoglaljuk a csoport egyes elemeinek értékeit, majd az oszlop végén összegezzük.
Az egyes csoportok egyes elemeinek értékét is fel kell dolgoznunk ehhez, az elemértéket megszorozzuk az ennek az elemnek megfelelő csoportértékkel.
Végül összegeznünk kell a csoportokat, és dolgoznunk kell a köztük lévő különbségekkel.
Feladatunk a csoportok összegei közötti különbség minimalizálása.
Mostantól az egyes csoportokhoz pontokat rendelhetünk úgy, hogy kézzel írjuk be a mértékegységeket a C és D oszlopokba. Az Excel a csoportösszegek különbségét a G11 cellában jeleníti meg.
A probléma az, hogy a lehetséges kombinációk száma 2 8, azaz. 256 lehetséges válasz a kérdésre. Ha mindegyikre 5 másodpercet szánunk, az 21,3 percet vesz igénybe, feltéve, hogy tudjuk tartani a tempót és emlékezni a legjobb kombinációra.
ott van Megoldás keresése alkalmazást talál.
Az optimális megoldás megtalálása Excelben
A szolgáltatás igénybevételéhez Megoldás keresése meg kell határoznunk egy sor követelményt, szabályokat és megszorításokat, amelyek lehetővé teszik a bővítmény számára, hogy megtalálja a megfelelő választ.
A mi szabályaink
Legfőbb követelményünk, hogy minimalizáljuk a különbséget a két csoport között. Példánkban a G11 cellában található – B csoport mínusz A csoport. Azt akarjuk, hogy a G11 cellában lévő érték a lehető legkisebb legyen, de nagyobb vagy egyenlő, mint 0.
Azt is tudjuk, hogy egy tétel lehet az A vagy a B csoportban, és nem lehet töredékes. Tehát minden elemre két korlátozásunk van:
Először: Az oszlopban lévő elem értéke A lényeg egyenlőnek kell lennie eggyel.
Másodszor: A csoportok elemeinek értékének egész számnak kell lennie.
Azt is tudjuk, hogy az elemek teljes száma 8, ami egy másik korlátozás. A következő részben megvitatjuk, hogyan kell ezeket a korlátozásokat használni.
Megoldás keresése párbeszédpanel
Ez a rész a beépülő ablakot írja le Megoldás kereséseés felhasználása a probléma meghatározására.
Ürítse ki a Megoldáskereső ablakot
Elkészült a Megoldáskereső ablak
Optimalizálja a célfüggvényt
Ez a célcella, ahol megpróbáljuk megoldani a problémát. Célcellánk a G11 – Csoportkülönbség.
To
Itt jelezzük, hogy milyen eredményeket szeretnénk elérni a célfüggvényből.
Azt szeretnénk, ha mindkét csoport összege megegyezne, i.e. így az összegek különbsége 0. Ez furcsának tűnhet, de nem kell minimalizálnunk a különbséget, mert így az összes elem az A csoportba kerül, amitől a G11 cella értéke nullánál kisebb lesz.
A megszorítás másik módja a G11 megváltoztatása =ABS(G10-F10). Ebben az esetben beállíthatjuk a jelölőt Minimális, a célfunkció elérésének eredményeként.
De most az = képletre fogunk összpontosítani G10-F10és állítsa a markert 0-val egyenlő értékre.
Változó cellák megváltoztatása
A módosított cellák azok a cellák, amelyeket a bővítmény módosítani próbál a probléma megoldása érdekében. Esetünkben ez egy elemet egy adott csoporthoz köt: $ C$2:$9 D$.
Korlátozások szerint
A megszorítások olyan szabályok, amelyek korlátozzák a probléma lehetséges megoldásait.
Fel kell adnunk néhány korlátozást a listánkhoz:
- Az oszlopban Teljes minden elemnek egyenlőnek kell lennie 1-gyel
- A csoportelemeknek egész számoknak kell lenniük
- Oszlopértékek összege Teljes 8-nak kell lennie
Korlátozások beállításához kattintson a gombra Hozzáadás
Tudod Változás vagy Töröl korlátozást, ha hibázott egy adott korlátozás kiválasztásával és a párbeszédpanel megfelelő gombjaira kattintva.
Megoldáskeresési paraméterek betöltése/mentése
A megoldáskereső szolgáltatás lehetővé teszi a bővítmények paramétereinek mentését és betöltését. Erre van egy gomb az ablakban. Betöltés/Mentés. A modell paraméterei a korábban megadott tartományba kerülnek. Ez a megközelítés lehetővé teszi a paraméterek gyors konfigurálását és módosítását Megoldás keresése.
Az optimális megoldás keresésének futtatása Excelben
Figyelmeztetés!!! A Solver bővítmény egy összetett számítási bővítmény, ezért a futtatás előtt mentse el a munkafüzetet.
A modell futtatása előtt be kell állítania néhány további paramétert, hogy megbizonyosodjon a szolgáltatás megfelelő működéséről. A fő párbeszédpanelen ellenőrizze, hogy van-e jelölő a mező mellett Tegye a nem korlátozó változókat nem negatívvá. Ugyanebben az ablakban kattintson a gombra Opciók.
Két paraméter, amelyet időről időre módosítani kell:
Pontosság határértéke:érték 0 és 1 között van, ahol minél nagyobb a szám, annál nagyobb a korlátozás
Integer optimalitás: megmutatja, hogy a korlátozásnak milyen messze lehet egy egész számtól.
A modell futtatása
A bővítmény elindításához kattintson a gombra Találj megoldást a fő ablakban.
Az állapotsorban egy sor statikus adatot fog látni, amelyek megjelenítik a bővítmény belső működését. Általában gyorsan változnak, és nehezen olvashatók. Ha a modell összetett, akkor a munka egy ideig leállhat, a bővítmény általában magától helyreáll ezekből a problémákból.
Után Megoldás keresése befejezi a munkát, az Excel megjelenít egy párbeszédpanelt Megoldás keresési eredményei némi információval. Az első dolog, amire figyelni kell, az a felirat A megoldást a megengedett eltérésen belül találtuk meg. Ha sikerül megoldást találni, a munkafüzet cellái megváltoznak a javasolt megoldással.
Most 4 lehetőség közül választhat:
— Az eredeti értékek visszaállítása
Jelentés futtatása
Jelentést úgy hozhat létre, hogy kiválasztja a rendelkezésre állókat a jelentések listájából. Új lap jön létre Eredményjelentés 1.
Felhívjuk figyelmét, hogy a beállított korlátozásoktól függően különböző jelentések állnak majd rendelkezésre.
Szkript mentése
Ahova be kell írnia a modell forgatókönyvének nevét, és kattintson a gombra RENDBEN.
Minden szkript elérhető itt Script Manager, amely a lapon található Adat a csoportban Munka adatokkal -> Mi lenne, ha elemzés -> Forgatókönyv-kezelő.
A világ rohamosan változik, akár tetszik, akár nem. Ez az igazság különösen jól ismert a PC-felhasználók számára. Végül is szoftver hihetetlen gyakorisággal változik és frissíti. Szerencsére az irodai csomagok nem szenvednek ennyit, de vannak kivételek. Mi a jelentősége az irodai szoftvercsomagoknak? Bármilyen irodai alkalmazás az praktikus eszköz, adatbázisokkal való együttműködésre készült. Ebben a rendszerben a segédelemek száma egyre több.
A vizualizációs eszközök segítségével, amelyek csak az ilyen programok új verzióiban jelentek meg, sokkal könnyebbé vált a munka. Az új keresőszűrőnek köszönhetően a munka jelentősen felgyorsult. Maga a Microsoft Excel 2010 pedig gyorsabban működik. De úgy tűnik, hogy az irodai alkalmazottak nemrégiben sajátították el az Office 2007-tel való munka bonyolultságát. Hirtelen azonban megjelent az Office 2010 bemutatója, ami csak még több gondot okozott a szerencsétlen felhasználóknak. Példa erre a „megoldás keresése” a Microsoft Excel 2010 programban.
Ez a bővítmény nem csak hasznos, hanem lehetővé teszi a táblázatszerkesztővel való munka hatékonyabbá tételét is, ezáltal lehetővé teszi számos összetett probléma megoldását. Különösen releváns az optimalizálás szempontjából, ami manapság sok vállalat számára releváns. De miért a Microsoft Excel 2010? Ha ebben a verzióban kifejezetten az Excelről beszélünk, akkor abban jelentős változások történtek. Így például javítva lett nagy számban képlethibák, amelyek miatt a program korábbi verzióiban meglehetősen gyakran előfordultak számítási hibák. De a legkisebb téves számítás néha meglehetősen kellemetlen következményekhez vezethet.
A grafikonok és diagramok előzetes megtekintését lehetővé tevő szalaginterfész segítségével, mielőtt beszúrná őket egy táblázatba, a felhasználók könnyebben készíthetnek összetett, professzionális dokumentumokat. Szintén tartalmazza új verzió A szerkesztő olyan új típusú képleteket helyezett be, amelyek nagyon hasznosak lehetnek a közgazdászok és a könyvelők számára. Ez a körülmény hangsúlyozza, hogy a Microsoft a vállalati felhasználókra összpontosít. Figyelembe véve, hogy az alább leírt helyzetek mindegyike jellemző rájuk, nincs ebben semmi meglepő.
Ha nem használta a „megoldás keresése” bővítményt, külön telepítheti. Hogyan kell telepíteni? Ez meglehetősen egyszerűen megtörténik. Ha az Excel 2003-as vagy régebbi táblázatszerkesztőjét használja, a művelet végrehajtásához lépjen az „Eszközök” elemre, és válassza ki a „Bővítmények” lehetőséget. És hol keressük a „megoldások keresését”, ha egy modernebb változatról beszélünk? Ha Excel 2007-et használ, akkor az „Adatok” lapon találja a „megoldás keresése” gombot. Hogyan lehet ezzel dolgozni? Talán mindezek a magyarázatok kissé hosszadalmasnak tűnhetnek, de ez a kiegészítő teljesen logikusan működik. Nem kell számítógépes zseninek lenni ahhoz, hogy elsajátítsa. A használat elvének teljes megértéséhez nézzünk meg egy egyszerű példát.
Hogyan működik a „megoldás keresése” az Excel 2010 programban?
Példa: Ön azt a feladatot kapja, hogy bónuszokat osszon ki egy szervezetben. A megoldás egyszerűsítése érdekében tegyük fel, hogy a bónuszt fel kell osztania az összes fióki alkalmazott között. Prémium költségvetés - 100 000 rubel. A bónusz az egyes alkalmazottak fizetésének arányában osztható fel. Hol kezdjem? Mindenekelőtt ki kell fejlesztenie egy táblázatot, bele kell írnia az egészet szükséges információkatés képlet kifejezések. A teljes prémium összeget tekintjük eredménynek. Érdemes megfontolni, hogy a célcella (például C8) a módosítandó szakaszhoz van társítva (például E2).
A C2-C7 tartományban további képletek is lehetnek, amelyekkel kiszámolhatja az egyes alkalmazottak bónuszának összegét. Ezt követően el kell indítania a „megoldás keresése” kiegészítőt. Ezután a megnyíló ablakban beállítják a kívánt értékeket. Különös figyelmet kell fordítani arra a tényre, hogy megjelenés az ablakok változatonként nagyon eltérőek lehetnek irodai csomag. Tehát ebben a helyzetben egyedül kell kitalálnia. De nincsenek alapvető különbségek, így a tanulás nem fog sok időt igénybe venni.
Milyen lehetőségek vannak a párbeszédpanelen?
A munka megkönnyítése érdekében ismernie kell azokat az értékeket, amelyek általában léteznek egy adott működési tartományban. Először is a célsejt. Kérjük, vegye figyelembe, hogy más műveletekkel ellentétben, amelyek több adatbeviteli mezőt is használhatnak, itt csak egy lehet. Ezen kívül érdemes figyelembe venni, hogy többféle optimalizálási lehetőség is létezhet. Különös figyelmet kell fordítani a lehetséges minimális és maximális összértékre. Ügyeljen a konkrét eredményre is. Ha az utóbbi lehetőségre van szüksége, akkor a kívánt eredményt pontosan meg kell adni a beviteli mezőben. Azt is figyelembe kell venni, hogy az egyes mezők vagy egy tartomány változtatható cellaként működhet. A program a tartományra veszi a végső értéket az eredeti adatokkal való összehasonlítással.
Hogyan kerülnek hozzáadásra a korlátozások?
Ha bizonyos korlátozásokat kell hozzáadnia a programhoz, használja a „Hozzáadás” gombot. Fontos figyelembe venni a következő pontot: az ilyen értékek beállításakor rendkívül óvatosnak kell lennie. Mivel az Excel „megoldáskereső” bővítményét meglehetősen fontos műveletekben használják, fontos, hogy ennek eredményeként a leghelyesebb értékeket kapjuk meg. Maguk az eredmények a korlátozásoktól függenek. Korlátozásokat állíthat be az egyes cellákra és a teljes tartományokra is.
Milyen képlet- és szimbólumváltozatok használhatók ebben az esetben? A következő jelek használhatók: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».
Hol találja ezt a lehetőséget a program régebbi verzióiban? Ha Excel 2007 vagy régebbi verziót használ, ezt a lehetőséget az „Opciók” gombra kattintva érheti el. Itt láthatja a „Megoldáskeresési beállítások” elemet.
Keressen egy kész eredményt
Ha kész megoldást szeretne keresni, kattintson a „Futtatás” gombra. Ennek eredményeként megjelenik a Megoldáskeresés eredményei párbeszédpanel. Ha elégedett a végső válasszal, csak kattintson az „Ok” gombra. Ennek eredményeként a kívánt válasz rögzítésre kerül a táblázatban. Abban az esetben, ha a kapott érték nem egyezik az Ön álláspontjával, kattintson a „Mégse” gombra. A tábla végül visszaáll az eredeti értékre. Folytathatja az optimális megoldás keresését. Ha megváltoztatta a forrásadatokat, akkor ezt a döntést újra el kell végezni.
Hol használható a „megoldáskereső” bővítmény az Excelben?
Vegyünk egy másik példát - a költségminimalizálást. Mint fentebb említettük, ez a funkció a termelési folyamatok optimalizálására használható. Nézzük meg, hogyan tudjuk csökkenteni egy kisépítéssel foglalkozó cég költségeit. Tegyük fel, hogy van egy szervezetünk és három beszállítónk, akik építőanyagokat szállítanak. Az építési költségek beleszámítanak a létesítmény költségébe, ezért a cég érdeke, hogy olyan beszállítót válasszon, akinek a munkája olcsóbb lesz.
Milyen információkat kell megadni az MS Excel „megoldáskereséséhez”? Fel kell tüntetni az építőanyagok költségeit, az építési helyszíni igényt és az építőanyag szállítás költségeit. Minden egyes „Beszállító-Vásárló” párost figyelembe kell venni. A célcellában az összes szállítási költség összegét kell feltüntetni. Ha minden helyesen történik, a „megoldáskeresés” funkció lehetővé teszi a legjövedelmezőbb stratégia létrehozását, amely a lehető legmagasabb bevételt hozza.
Microsoft Excel bővítmény "Megoldáskeresés" egy hatékony eszköz a megoldások megtalálására, és optimalizálási problémák megoldására használják. A megoldáskeresési eljárás lehetővé teszi a cellában található képlet optimális értékének megtalálását, amelyet célcellának nevezünk. Az eljárás olyan sejtcsoporton működik, amely közvetlenül vagy közvetve kapcsolódik a célcellában lévő képlethez.
A kiegészítő rövid leírása
Egy adott eredmény képlet segítségével történő eléréséhez az eljárás megváltoztatja a befolyásoló cellák értékeit. Az értékmegszorítások a modellben használt értéktartomány csökkentésére szolgálnak. A Megoldáskereső bővítmény egy szabványos Microsoft Office Excel-bővítmény, és azonnal elérhető a Microsoft Office általában vagy a Microsoft Excel telepítésekor.
Hogyan kell telepíteni a kiegészítőt?
A Megoldás keresése bővítmény kétféleképpen telepíthető. Az MS Office vagy az MS Excel mellett olyan szabványos bővítmények is telepítve vannak, mint a "Megoldáskeresés" és az "Elemzési csomag". Ha a szabványos bővítményt nem telepítette a kezdeti telepítés során, akkor újra kell futtatnia a telepítési folyamatot. Nézzük meg a „Megoldás keresése” bővítmény telepítését a Microsoft Office 2010 használatával. A 2003-as és 2007-es verziókban minden ugyanúgy történik.
Tehát indítsa el a telepítőlemezt az MS Office 2010 alkalmazáscsomaggal, és válassza az „Összetevők hozzáadása vagy eltávolítása” lehetőséget.
Ezután kattintson a „Folytatás” gombra, keresse meg a Microsoft Excel alkalmazást a telepítési paraméterekben, keresse meg a „Bővítmények” részt az alkalmazás összetevői között, válassza ki a „Megoldáskeresés” bővítményt, és állítsa be a „Futtatás a sajátomból” lehetőséget. számítógép” opciót.
Ismét kattintson a „Folytatás” gombra, és várja meg, amíg a bővítmény telepítésre kerül.
Hogyan kell csatlakoztatni a kiegészítőt?
Használat előtt először engedélyeznie kell a bővítményt úgy, hogy bejelöli a neve előtti négyzetet a Kiegészítők párbeszédpanel elérhető bővítményeinek listájában.
Az ablak meghívása az alkalmazás verziójától függően kissé eltér. Erről részletesen egy külön cikkben olvashat " Hogyan telepíthető az Excel 2003/2007/2010 bővítménye? " képernyőképekkel az Excel alkalmazás mindhárom verziójához, ezért nem ismétlem meg. Igen, csak néhány szót fűzök a kiegészítő telepítésének második módszeréhez. Az interneten talál egy fájlt a névvel Solver.xla(ez a „Megoldás keresése” kiegészítő), és telepítse a fenti linken található leírásnak megfelelően.
Tekintse át
