Vytvoření makra jazyka Visual Basic pomocí Řešitele Excel v aplikaci Excel 97

Prohlášení o odmítnutí odpovědnosti pro už nepodporovaný obsah KB

Tento článek byl napsán pro produkty, ke kterým již Microsoft nadále nenabízí podporu. Článek je tedy poskytován „tak, jak je“ a nebude už nadále aktualizován.

Souhrn

Tento článek popisuje způsob použití Řešitel aplikace Microsoft Excel makra Microsoft Visual Basic v aplikaci Microsoft Excel 97. Řešitel aplikace Microsoft Excel je doplněk aplikace Microsoft Excel.

Kromě toho tento článek obsahuje informace o vytváření maker, navrhnout makro a jak pracovat s omezeními makro. Tento článek také popisuje algoritmus a metody používané v Řešiteli Microsoft Excel. Následující seznam obsahuje všechna témata, která jsou popsána v článku.

ÚVOD

Tento článek obsahuje informace o Řešitel aplikace Microsoft Excel.

Další informace

Popis Řešitel aplikace Microsoft Excel

Řešitel aplikace Microsoft Excel je že Řešitel aplikace Microsoft Excel doplněk aplikace Microsoft Excel umožňuje zjistit optimální hodnotu pro vzorec v konkrétní cílové buňky na listu aplikace Microsoft Excel. Řešitel aplikace Microsoft Excel nastaví hodnoty jiné buňky, které se vztahují k cílové buňky pomocí rovnice. Po vytvoření rovnice a definovat sadu parametrů nebo omezení u proměnných v rovnici, pokusí se Řešitel aplikace Microsoft Excel různá řešení k dosažení odpovědí, které by splňovalo všechny omezující podmínky. Řešitel aplikace Microsoft Excel používá následující prvky "vyřešit" rovnice:
  • Cílem je Cílová buňka – cílové buňky. Je buňka v listu model, který bude minimalizovat, maximalizovat nebo nastavit na určitou hodnotu.
  • Měněné buňky - měněné buňky jsou proměnné rozhodnutí. Tyto buňky mají vliv na hodnotu cílové buňky. Tyto buňky jsou změněny Řešitel aplikace Microsoft Excel nalézt optimální řešení pro cílové buňky.
  • Omezení - omezení se na obsah buněk. Jednu buňku v listu modelu například může být omezeno na celočíselné hodnoty, zatímco jiné buňky může být omezeno na menší než daná hodnota je.
Můžete automatizovat vytváření a manipulaci s modely Řešitel aplikace Microsoft Excel pomocí jazyka Microsoft Visual Basic pro makro Applications (VBA). Tento článek popisuje použití funkce Řešitel aplikace Microsoft Excel v aplikaci Microsoft Excel 97 použít makro jazyka VBA. Tento článek předpokládá, že jste obeznámeni s jazyka VBA a editoru jazyka Visual Basic v aplikaci Microsoft Excel 97. Příklady, které jsou použity v tomto článku jsou k dispozici ke stažení na následujícím webu společnosti Microsoft:Poznámka: Můžete také použít makra a příklady, které jsou popsány v tomto článku v aplikaci Microsoft Excel verze 5.0 a 7.0.

zpět na horní

Použití funkce Řešitel aplikace Microsoft Excel makra VBA

Chcete-li použít funkce doplňku Řešitel aplikace Microsoft Excel makra VBA, musí odkazovat doplněk z projektu VBA sešitu, který obsahuje makra. Pokud není odkaz doplněk Řešitel aplikace Microsoft Excel, obdržíte následující chyby při pokusu o spuštění makra:
Chyba kompilace: Procedura Sub nebo Function není definována.
Chcete-li doplněk Řešitel aplikace Microsoft Excel makra v sešitu, použijte následující kroky:
  1. Otevřete sešit.
  2. V nabídce Nástroje přejděte na příkaz
    Makroa potom klepněte na příkaz Editor jazyka Visual Basic.
  3. V nabídce Nástroje klepněte na tlačítko
    Odkazy.
  4. V seznamu Dostupné odkazy klepnutím zaškrtněte políčko Solver.xls a potom klepněte na tlačítko OK.

    Poznámka: Pokud není zobrazen v Dostupné odkazy Solver.xls
    seznam, klepněte na tlačítko Procházet. V dialogovém okně Přidat odkaz vyhledejte a vyberte soubor Solver.xla a klepněte na tlačítko Otevřít. Soubor Solver.xla je obvykle umístěn v podsložce C:\Program Files\Microsoft Office\Office\Library\Solver.
Nyní jste připraveni používat funkce Řešitel aplikace Microsoft Excel makra VBA.

zpět na horní

Jak navrhnout makro jazyka VBA, který vytváří a řeší jednoduchý model Řešitel aplikace Microsoft Excel

Ačkoli Řešitel aplikace Microsoft Excel nabízí mnoho funkcí, jsou zásadní pro vytváření a řešení modelu následující tři funkce:
  • Funkce SolverOK
  • Funkce SolverSolve
  • Funkce SolverFinish

Funkce SolverOK

Funkce SolverOK definuje základní model Řešitel aplikace Microsoft Excel. Funkce SolverOK je obvykle první funkce, který použijete k vytvoření modelu Řešitel aplikace Microsoft Excel. Funkce SolverOK je ekvivalentní klepnutím na příkaz Řešitel v nabídce Nástroje a zadáním možnosti, které jsou v dialogovém okně Parametry Řešitele . Syntaxe funkce SolverOK je následující:
SolverOK (SetCell, MaxMinVal, ValueOf, ByChange)
Následující informace popisují syntaxi funkce SolverOK :
  • SetCell Určuje cílovou buňku.
  • MaxMinVal shoduje na tom, zda chcete řešení pro maximální hodnotu (1), minimální hodnota (2) nebo (3) pro konkrétní hodnotu cílové buňky.
  • ValueOf Určuje hodnotu, na kterou má odpovídající cílové buňky. Pokud nastavíte MaxMinVal na 3, je nutné zadat argument. Pokud nastavíte na hodnotu 1 nebo 2 MaxMinVal , můžete tento argument vynechat.
  • ByChange určuje buňku nebo oblast buněk, která bude změněna.
Obrázek 1 přiřadí argumenty funkce SolverOK parametry v dialogovém okně Parametry Řešitele .


Obrázek 1. Parametry, které jsou spojeny s argumenty SolverOK

 Figure 1. Parameters that are associated with the SolverOK arguments

Funkce SolverSolve

Funkce SolverSolve byl odstraněn pomocí parametrů, které jsou určeny pomocí funkce SolverOK modelu. Provádění funkce SolverSolve odpovídá kliknutí na tlačítko řešit v Parametry Řešitele
Dialogové okno. Syntaxe funkce SolverSolve je následující:
SolverSolve (UserFinish, ShowRef)

Následující informace popisuje syntaxe pro funkci SolverSolve :
  • UserFinish označuje, zda má uživatel k dokončení řešení modelu.

    Chcete-li vrátit výsledky bez zobrazení dialogového okna Výsledky řešení , nastavte tento argument na hodnotu TRUE. Pokud chcete výsledky zobrazit dialogové okno Výsledky řešení , nastavte tento argument na hodnotu FALSE
  • ShowRef identifikuje makra, která je volána, když Řešitel aplikace Microsoft Excel vrátí dílčí řešení.

    ShowRef argument by měla sloužit pouze při TRUE je předán argument StepThru funkce SolverOptions .

Funkce SolverFinish

Funkce SolverFinish Určuje, co dělat s výsledky a jaký typ kontingenční sestavy chcete vytvořit po dokončení procesu řešení. Syntaxe funkce SolverFinish je následující:
SolverFinish (KeepFinal, ReportArray)

Následující informace popisuje syntaxe pro funkci SolverFinish :
  • KeepFinal Určuje, co dělat s konečným výsledkem. Pokud KeepFinal je 1, hodnoty konečné řešení jsou uloženy v měněných buňkách nahrazení hodnoty. Pokud KeepFinal 2, konečný roztok hodnoty ztraceny a budou obnoveny původní hodnoty.
  • ReportArray určuje pole, které označuje typ sestavy, kterou aplikace Microsoft Excel vytvoří při dosažení řešení. Pokud ReportArray je nastavena na 1, vytvoří aplikace Microsoft Excel sestavu odpovědí. Pokud je nastavena na hodnotu 2, aplikace Microsoft Excel vytvoří sestavu citlivost a nastavena na hodnotu 3 v aplikaci Microsoft Excel vytvoří sestavu limity. Další informace o těchto sestav naleznete v části "generování sestav pro řešení".
Obrázek 2. Řešitel aplikace Microsoft Excel výsledky možnosti, které jsou spojeny s SolverFinish argumenty

 Figure 2. Solver results options that are associated with SolverFinish arguments

Tento článek popisuje, jak vytvořit jednoduchý model Řešitel aplikace Microsoft Excel interaktivně. Prvním krokem je vytvoření listu pro model. List obsahuje některé buňky a nejméně jednu buňku obsahující vzorec. Tento vzorec závisí na jiné buňky v listu. Po nastavení listu, klepněte na tlačítko
Řešitel v nabídce Nástroje . V dialogovém okně Parametry Řešitele zadejte cílové buňky, hodnota, která jsou řešení pro, oblast buněk, které budou změněny a omezení. Klepněte na tlačítko řešení ke spuštění procesu řešení. Jakmile Řešitel aplikace Microsoft Excel nalezl řešení, výsledky se zobrazí v listu a Řešitel aplikace Microsoft Excel zobrazí okno s výzvou, pokud chcete zachovat konečné výsledky nebo pokud chcete zrušit je. Klepnutím na jednu z těchto možností, dokončí Řešitel aplikace Microsoft Excel.

Obrázek 3 ukazuje jednoduchý model, který můžete vytvořit pomocí následujících kroků.

Obrázek 3. Jednoduchý model: model druhou odmocninu

 Figure 3. A simple model: The Square Root model

V tomto příkladu změnit buňku A1, obsahující vzorec = A1 ^ 2 na hodnotu, která způsobí, že buňky A2 rovna hodnotě 50. Jinými slovy Najděte druhou odmocninu z 50. V modelu odmocnina nejsou žádná omezení. Find_Square_Root makro provede následující úkoly:
  • Vytvoří model, který vyřeší tím, že změníte hodnotu v buňce A1 hodnota buňky A2 hodnotu 50.
  • Je vyřešen jako model.
  • Konečné výsledky uloží do listu bez zobrazení dialogového okna Výsledky řešení .
Toto jednoduché makro vytvoří model Řešitel aplikace Microsoft Excel a vyřeší bez zásahu uživatele. Následující kód obsahuje makro Find_Square_Root :
    Sub Find_Square_Root()
' Set up the parameters for the model.
' Set the target cell A2 to a value of 50 by changing cell A1.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
ByChange:=Range("A1")

' Solve the model but do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub

Find_Square_Root2 makro je upravenou verzí Find_Square_Root makra. Pokud používáte funkce InputBox , Find_Square_Root2 makro výzvu pro hodnotu, kterou chcete vyřešit cílové buňky. Po můžete zadat hodnotu, Find_Square_Root2 makro nastaví tento parametr jako hodnotu argumentuvalueof SolverOK, problém byl odstraněn, výsledky uloží do proměnné odmocninu zahodí řešení a obnoví hodnoty v listu do původního stavu. Makro Find_Square_Root2 v podstatě ukazuje, jak uložit výsledky v jedné nebo více proměnných a potom obnovit na původní hodnotu měněných buněk.


Následující kód obsahuje makro Find_Square_Root2 :
    Sub Find_Square_Root2()
Dim val
Dim sqroot

' Request the value for which you want to obtain the square root.
val = Application.InputBox( _
prompt:="Please enter the value for which you want " & _
"to find the square root:", Type:=1)

' Set up the parameters for the model.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
ByChange:=Range("A1")

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of cell A1 (the changing cell) before you discard
' the results.
sqroot = Range("a1")

' Finish and discard the results.
SolverFinish KeepFinal:=2

' Show the result in a message box.
MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

End Sub

zpět na horní

Generování sestav pro řešení

Řešitel aplikace Microsoft Excel nabízí několik typů sestav, které popisují, jak změnit výsledky a jak zavřít omezení pochází kritické hodnoty. Každá sestava je uložena na samostatném listu v sešitu. Po následují typy sestav, které nabízí Řešitel aplikace Microsoft Excel:
  • Sestava odpovědí - The sestavu odpovědí uvádí cílové buňky a měněné buňky s odpovídající původními a výslednými hodnotami, omezující podmínky a informace o omezeních.
  • Citlivostní sestava - The citlivost sestava poskytuje informace o citlivosti řešení k malým změnám ve vzorci pro cílové buňky.
  • Limitní sestava - limitní sestava uvádí cílové buňky a jejich hodnot, horních a dolních mezí a cílové hodnoty měněných buněk.
K vytvoření sestav pro modely, zadejte matici hodnot v argumentu funkce SolverFinish ReportArray . Další informace o argumentu ReportArray naleznete v části "SolverFinish (KeepFinal, ReportArray)". Pokud chcete generovat sestavu pro model, který vytvoří makro Find_Square_Root2 a řeší omezení, změna funkce SolverFinish v makru, takže vypadá podobně jako následující příklad kódu:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Generovat několik sestav, upravte funkce SolverFinish tak, že vypadá podobně jako v následujícím ukázkovém kódu:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)
zpět na horní

Použití funkcí aplikace Microsoft ExcelSolver opakování makra.

V mnoha situacích je vhodné řešit cílovou buňku více hodnot Řešitel aplikace Microsoft Excel. Obecně to lze provádět pomocí jedné z existující cyklické struktury, které jsou k dispozici s VBA.


Makro Create_Square_Root_Table ukazuje, jak funkce Řešitel aplikace Microsoft Excel v opakování makra. Create_Square_Root_Table makro vytvoří tabulku do nového listu. Vloží číslo jedna až deset a každé číslo odpovídající druhé odmocniny. Makro Create_Square_Root_Table vytvoří tabulku iterovat čísla 1 až 10 a cílové buňky v modelu odmocnina pro hodnotu, která odpovídá počtu iterace vyřešit pomocí cyklu For . Následující kód obsahuje makro Create_Square_Root_Table :
    Sub Create_Square_Root_Table()
' Add a new worksheet to the workbook.
Set w = Worksheets.Add

' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
w.Range("C1").Value = 2
w.Range("C2").Formula = "=C1^2"

' A loop that will make 10 iterations, starting with the number 1,
' and finishing at the number 10.
For i = 1 To 10

' Set the Solver parameters that indicate that Solver should
' solve the cell C2 for the value of i (where i is the number
' of the iteration) by changing cell C1.
SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
MaxMinVal:=3, ValueOf:=i

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of i in column A and the results of the
' changing cell in column B.
w.Cells(i, 1) = i
w.Cells(i, 2) = Range("C1")

' Finish and discard the final results.
SolverFinish KeepFinal:=2

Next

' Clear the range C1:C2
w.Range("C1:C2").Clear

End Sub


Create_Square_Root_Table makro generuje tabulky znázorněné na obrázku 4.


Obrázek 4. Výstup, který je generován pomocí makra Create_Square_Root_Table


Figure 4. Output that is generated by the Create_Square_Root_Table macro

zpět na horní

Práce s omezeními

Omezení je omezení na obsah jedné nebo více buněk. Model může mít jedno nebo více omezení. Omezení sada je sada nerovností nebo sadu equalities, které určité kombinace hodnot pro proměnné rozhodnutí odebrat z řešení. Omezení může například vyžadovat, aby jedné buňky větší než nula a že jiné buňky obsahují pouze celočíselná hodnota.

Druhá odmocnina model, který můžeme projednat v tomto okamžiku je jednoduchý model, který neobsahuje žádná omezení. Obrázek 5 ukazuje model, který používá omezení. Účelem tohoto modelu je nalezení optimální kombinace produktů pro maximální zisk.

Obrázek 5. Produkt se smísí s výhledem na ziskové rozpětí

Figure 5. Product mix with diminishing profit margin

Pokud například společnost vyrábí reproduktory, televizory a přehrávačů a používá společné části zásob, napájecí zdroje, kužely reproduktorů, atd. Části jsou omezené dodávky. Vaším cílem je určit nejziskovější sortiment produktů k sestavení. Váš zisk na jednotku zmenší objem vzhledem k tomu, že další cenové pobídky jsou zapotřebí pro načtení tohoto distribučního kanálu. Diminishing Vrátí exponent je 0,9. Tento exponent se používá k výpočtu zisku produktu v rozsahu G11:I11.

Vaším cílem je najít maximální zisk (buňka G14). Hodnoty, které se budou měnit najít maximální zisk je počet jednotek, které vytvoříte. Rozsah G9:G11 představuje v tomto modelu měněné buňky. Jediné omezení je, že počet díly nesmí překročit počet částí, které máte k dispozici. S Řešitel aplikace Microsoft Excel, zobrazí se toto omezení jako E3:E7 < = B3:B7. Kdyby tento model Řešitel aplikace Microsoft Excel vytvořit interaktivní vypadat podobné těm, které jsou v obrázku 6 parametry Řešitel aplikace Microsoft Excel.

Obrázek 6. Řešitel aplikace Microsoft Excel parametry pro sortiment výrobků s výhledem na ziskové rozpětí modelu

Figure 6. Microsoft Excel Solver parameters for the product mix with Diminishing Profit Margin model

K vytvoření a řešení sortiment výrobků s výhledem na ziskové rozpětí modelu, budete používat nové funkce, funkce SolverAdd , kromě funkce VBA Řešitel aplikace Microsoft Excel, které byly popsány výše. Funkce SolverAdd přidá omezení modelu. Provádění funkce SolverAdd odpovídá kliknutí na tlačítko Přidat
tlačítko v dialogovém okně Parametry Řešitele . Na
Funkce SolverAdd má následující syntaxi:
SolverAdd (FormulaText Odkaz_na_buňku vztah)

Následující informace popisují syntaxi funkce SolverAdd :
  • Odkaz_na_buňku odkazuje na jednu nebo více buněk, které tvoří na levé straně omezení.
  • Vztah je aritmetický vztah mezi levé a pravé straně omezení.
  • Vztah může být hodnota mezi 1 a 5, jako v následujícím příkladu:
    • Hodnota 1 je menší než nebo rovno (< =).
    • Vaue 2, rovná se (=).
    • Hodnota 3 je větší než nebo rovno (> =).
    • Hodnota 4 je celé číslo.
    • Hodnota 5 je binární (hodnota nula nebo jedna).
  • FormulaText odkazuje na jednu nebo více buněk, které tvoří pravé straně constraint.* *
** Při zadávání rozsahu buněk FormulaText argumentu funkce SolverAdd , Všimněte si, zda je odkaz na relativní nebo absolutní. Obecně je nutné zadat argument FormulaText absolutní odkaz. Však Pokud zadáte relativní odkazy pro FormulaText argument, si uvědomíte, že odkaz bude vzhledem k cílové buňky a aktivní buňky.

Poznámka: V aplikaci Microsoft Excel verze 5.0 a 7.0, použijte při zadání do buňky nebo oblasti buněk s argumentem FormulaText notace R1C1. Naopak v aplikaci Microsoft Excel 97, styl A1 zápis slouží k zadání argumentu FormulaText .

Obrázek 7. Pole, které jsou přidruženy
SolverAdd argumenty

 Figure 7. Fields that are associated with the SolverAdd arguments

Makro Maximum_Profit , které generuje model pro sortiment výrobků s modelem Diminishing vrátí. Toto makro provede následující funkce nebo argumenty:

  • Funkce SolverOK Nastaví maximální hodnota cílové buňky a určí buňky, které chcete změnit.
  • Funkce SolverAdd přidá omezení modelu.
  • SolverSolve funkce najde řešení bez zobrazení
    Dialogové okno Výsledky řešení .
  • Funkce SolverFinish vrací konečné výsledky na listu.
Popisuje následující kód pro makro Maximum_Profit :
    Sub Maximum_Profit()
' Set up the parameters for the model.
' Determine the maximum value for the sum of profits in cell G14
' by changing the number of units to build in cells G9:I9.
Solverok setcell:=Range("G14"), maxminval:=1, _
bychange:=Range("G9:I9")

' Add the constraint for the model. The only constraint is that the
' number of parts used does not exceed the parts on hand--
' E3:E7<=B3:B7
SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub


Poznámka: V aplikaci Microsoft Excel verze 5.0 a 7.0, použijte při zadávání buňku nebo oblast buněk s argumentem FormulaText notace R1C1. Naopak v aplikaci Microsoft Excel 97, styl A1 zápis slouží k zadání argumentu FormulaText .

Při spuštění makra Maximum_Profit , bude Řešitel aplikace Microsoft Excel nalézt řešení v budově 160 televizorů, přehrávačů 200 a 80 reproduktory pro maximální zisk z $14,917 dolarů.


zpět na horní

Jak změnit a odstranit omezení

Omezení v modelu lze programově změnit nebo odstranit. Omezení jsou určeny jejich argumenty Odkaz_na_buňku a vztah .

Chcete-li programově změnit stávající omezení, použijte funkci SolverChange . Syntaxe funkce SolverChange je následující:
SolverChange (FormulaText Odkaz_na_buňku vztah)
Všimněte si, že argumentů pro funkci SolverChange jsou stejné ty, které pomocí funkce SolverAdd .

Pokud chcete změnit omezení sortimentu výrobků s modelem Diminishing vrátí, použijete funkci SolverChange . Například právě omezení, která je určena je, že číslo použité díly je menší než počet částí na straně (E3:E7 < = B3:B7). Pokud chcete změnit toto omezení tak, aby číslo použité díly je menší než nebo roven počtu částí předpokládané (počet částí na skladě) plus počet objednaných dílů. Tato nová omezení vypadat E3:E7 < = D3:D7. Následující makro změní existující omezení E3:E7 < = B3:B7 do E3:E7 < = D3:D7 a řešení pro řešení.

Následující kód obsahuje makro Change_Constraint_and_Solve :
    Sub Change_Constraint_and_Solve()
' Change the constraint.
SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$D$3:$D$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Vzhledem k tomu, že omezení jsou označeny Odkaz_na_buňku a vztah argumenty, můžete pouze změnit FormulaText argument pro omezení pomocí funkce SolverChange . Odkaz_na_buňku a vztah hodnoty neodpovídají stávající omezení, musíte odstranit omezení a poté přidat upravené omezení. Chcete-li odstranit omezení, použijte funkci SolverDelete . Syntaxe funkce SolverDelete je následující:


SolverDelete (FormulaText Odkaz_na_buňku vztah)



Všimněte si, že argumentů pro funkci SolverDelete jsou shodné těm, které používáte s funkcí SolverChange a SolverAdd .


Následující makro ilustruje, jak odstranit a přidat omezení. V tomto příkladu Change_Constraint_and_Solve2 makro odebere omezení E3:E7 < = B3:B7 z kombinace výrobků s modelem Diminishing vrátí a přidá nové omezení. Nové omezení je úprava původní omezení, kde jsou přehozené levé a pravé straně omezení.

Následující kód obsahuje makro Change_Constraint_and_Solve2 :
    Sub Change_Constraint_and_Solve2()
' Reverse the left and right sides of the constraint...
' Delete the constraint E3:E7<=B3:B7 and add the
' constraint B3:B7>=E3:E7.
SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"
SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
FormulaText:="$E$3:$E$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Poznámka: V aplikaci Microsoft Excel verze 5.0 a 7.0, použijte při zadávání buňku nebo oblast buněk s argumentem FormulaText notace R1C1. Naopak v aplikaci Microsoft Excel 97, styl A1 zápis slouží k zadání argumentu FormulaText .

zpět na horní

Jak načíst a uložit vaše modely

Při uložení sešitu poslední parametry, které jste zadali v dialogovém okně Parametry Řešitele jsou ukládány společně se sešitem. Proto při otevření sešitu, parametry jsou stejné jako při posledním uložení sešitu.

Můžete definovat více než jeden problém na jednom listu. Každý problém se skládá z buněk a omezení, které zadáte v Parametru Řešitele a v dialogovém okně Možnosti Řešitele . Vzhledem k tomu, že s listu je uložen pouze poslední problém, ztratíte jiné problémy, dokud je výslovně neuložíte. Chcete-li je uložit, klepněte na tlačítko Uložit Model v dialogovém okně Možnosti Řešitele . Podobně pokud chcete obnovit dříve uložené parametry, klepněte na tlačítko Načíst Model v dialogovém okně Možnosti Řešitele .

Modely jsou uloženy v oblasti buněk v listu. První buňku v oblasti obsahuje vzorec cílové buňky. Druhé buňky v oblasti obsahuje vzorec, který identifikuje měněných buněk v modelu. Poslední buňka v oblast obsahuje pole představující možnosti nastavit v dialogovém okně Možnosti Řešitele . Buňky mezi druhou buňku a poslední buňky obsahují vzorce, které představují omezení v modelu.

Obrázek 8 ukazuje model pro plánování zaměstnanců. Předpokládá, že pracujete pro malé výrobce. Tato tabulka zobrazuje jednotlivé zaměstnance hodinová sazba mzdy, počet hodin, které jsou naplánovány a Plánovaný počet jednotek, které každý zaměstnanec může způsobit v jednu hodinu. Vaším cílem je splnit určitou kvótu pro počet jednotek vyrobených při současné minimalizaci nákladů práce.

Obrázek 8. Režim plánování zaměstnanců
l
 Figure 8. Employee Scheduling model

Dva další faktory (nebo omezení), že je třeba zvážit, jsou minimální a maximální počet hodin pracovat libovolný jeden ze zaměstnanců a počet jednotek, které máte v úmyslu vyrobit. Pokud mají zaměstnanci pracují mezi 30 a 45 hodin pro určený týden je nutné vyrobit 3975 jednotek, Řešitel aplikace Microsoft Excel parametry vypadat podobné těm, které jsou uvedeny v následující tabulce:

ParametrOblast buněkPopis
Cílová buňka$D$12Náklady na práci.
Měněné buňky$C$2:$C$8Hodin odpracovaných zaměstnanci.
Omezení$C$ 2: $C$ 8 < = 45Maximální počet hodin na jednoho zaměstnance je 45.
$C$ 2: $C$ 8 > = 30Minimální počet hodin na jednoho zaměstnance je 35.
$G$. 12 = 3975Počet jednotek je. 3975.


Vaše cíle jsou řešení pro optimální mzdové náklady na týdenním týdně uložit každý model a být schopen načíst všechny týdenní model, když ji potřebujete.

V makru lze uložit a načíst pomocí funkce SolverLoad a SolverSave v uvedeném pořadí parametrů Řešitel aplikace Microsoft Excel pro model. SolverSave a SolverLoad funkce mají následující syntaxi:

SolverSave (SaveArea)

SolverLoad (LoadArea)


SolverSave a SolverLoad funkce mají pouze jeden argument, SaveArea a LoadArea argumenty. Tyto argumenty zadejte oblast listu, kde jsou uloženy informace o modelu.

Následující makro New_Employee_Schedule ukazuje, jak vytvořit řešení a uložit model založený na vstupu uživatele. Uživatel je vyzván k zadání data, model, počet jednotek pro výrobu a minimální a maximální počet hodin na jednoho zaměstnance. Tyto údaje pak slouží k vytvoření modelu. Model je vyřešen a pak s vstup uživatele.

Následující kód obsahuje makro New_Employee_Schedule :
    Sub New_Employee_Schedule()    
' Prompt the user for the date of the model, the units to produce,
' and the maximum and minimum number of hours per employee.
ModelDate = Application.InputBox( _
Prompt:="Date of Model:", Type:=2)
Units = Application.InputBox( _
Prompt:="Projected Number of Units:", Type:=1)
MaxHrs = Application.InputBox( _
Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
MinHrs = Application.InputBox( _
Prompt:="Minimum Number of Hours Per Employee:", Type:=1)

' Clear any previous Solver settings.
SolverReset

' Set the target cell, D12, to a minimum value by changing
' the range, C2:C8.
SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
ByChange:=Range("C2:C8")

' Add the constraint that number of hours worked <= MaxHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs

' Add the constraint that number of hours worked >=MinHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs

' Add the constraint that number of units produced = Units.
SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units

' Solve the model and keep the final results.
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
' in columns I:L.
Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
Units, MaxHrs, MinHrs)

' Save the model parameters to the range M:R in the worksheet.
SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)

End Sub

Poznámka: V aplikaci Microsoft Excel verze 5.0 a 7.0, použijte při zadávání buňku nebo oblast buněk s argumentem FormulaText notace R1C1. Naopak v aplikaci Microsoft Excel 97, styl A1 zápis slouží k zadání argumentu FormulaText .

Obrázek 9 ukazuje, jak model uložené informace zobrazí v listu.

Obrázek 9. Informace o modelu uložený makro New_Employee_Schedule

Figure 9. Model information that is saved by the New_Employee_Schedule macro

Makro New_Employee_Schedule uloží každý nový model listu. Load_Employee_Schedule makro můžete načíst jeden z těchto modelů uložené. Toto makro vyzve uživatele pro model načíst a pak prohledá sloupec I data modelu. Pokud je nalezena data modelu, Load_Employee_Schedule makro načte odpovídající model, řeší ji a pak udržuje konečné výsledky.

Následující kód obsahuje makro New_Employee_Schedule:
    Sub Load_Employee_Schedule()    
' Prompt for the date of the model.
ModelDate = Application.InputBox( _
Prompt:="Date of Model to Load:", Type:=2)

' Locate the date in column I.
Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
r = Application.Match(ModelDate, DateRange, 0)

If IsError(r) Then
' Display a message if the model date is not found
MsgBox "Cannot find a model with the date " & ModelDate
Else
' If the model date is found, load the model into Solver,
' solve the model, and keep the final results.
SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End If

End Sub

New_Employee_Schedule makro obsahuje funkci SolverReset . Chcete-li odstranit všechny vybrané buňky a omezení lze použít funkci SolverReset
Dialogové okno Parametry Řešitele a chcete-li obnovit všechna nastavení v SolverReset funkce nemá žádné argumenty.

zpět na horní

Jak najít další informace o Řešitel aplikace Microsoft Excel

Následující zdroje obsahují informace o tom, jak použít doplněk Řešitel aplikace Microsoft Excel.

  • Nápovědu pro konkrétní řešení zprávy v tématu
    Frontline Systems.
  • Tipy na vytváření čitelného spravovatelných modely v tématu
    Frontline Systems.
  • Další informace o Řešitele limity pro omezení a klepněte na následující číslo článku databáze Microsoft Knowledge Base:

    75714 Řešitele omezení pro omezení

  • Několik příkladů použití doplňku Řešitel aplikace Microsoft Excel v aplikaci Microsoft Excel naleznete v tématu ukázkový soubor s názvem Solvsamp.xls.
  • Následuje ukázkový soubor, který je součástí aplikace Microsoft Excel 97 výchozí umístění:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Následuje ukázkový soubor, který je součástí aplikace Microsoft Excel 7.0 výchozí umístění:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Následuje ukázkový soubor, který je součástí aplikace Microsoft Excel 5.0 výchozí umístění:
    \Excel\Examples\Solver\SolvSamp.xls
zpět na horní

Jak získat další informace o algoritmus a metody používané v Řešiteli Microsoft Excel

Řešitel aplikace Microsoft Excel používá nelineárním optimalizačním kódem Generalized Reduced Gradient (GRG2), který byl vyvinut, který vytvořili Leon Lasdon z University of Texas v Austinu a Allan Waren z Cleveland State University.

Další informace o algoritmu používaného daným Řešitel aplikace Microsoft Excel klepněte na následující číslo článku databáze Microsoft Knowledge Base:

82890 použití Řešitele generalized snížené



Lineárních problémů a se používá simplexní metoda s proměnnými, větev a vazby implementované John Watson a Dan ohraničenými, Frontline Systems, Inc. Další informace o procesech vnitřních řešení používaných v Řešiteli získáte na adrese:

Frontline Systems, Inc.P.O. Box 4288
Incline Village, NV 89450-4288
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com



Výběr kódu programu Řešitel aplikace Microsoft Excel jsou copyright 1990, 1991, 1992 a 1995 Frontline Systems, Inc společnost Optimal metody, Inc. copyright 1989

Poznámka: Doplněk Řešitel aplikace Microsoft Excel, která je popsána v tomto článku je poskytován tak, jak je"a nezaručujeme, že lze použít ve všech situacích. Ačkoli pracovníci podpory společnosti Microsoft můžete pomoci s instalace a existující funkce tohoto doplňku, nemohou doplněk poskytuje nové funkce.


Žádné záruky. Software je poskytován "jako-je" bez záruky jakéhokoli druhu a jakékoli použití tohoto softwaru je produkt na vlastní nebezpečí.


zpět na horní
Vlastnosti

ID článku: 843304 - Poslední kontrola: 16. 1. 2017 - Revize: 1

Váš názor