Lecture01_线性规划及应用开发

a21 x1 + a22 x2 + … + a2n xn ( =, ) b2
: am1 x1+ am2 x2 + … + amn xn ( =, ) bm x1, x2 ,… , xn 0
通常称 x1,x2 ,… ,xn 为 决策变量， c1,c2 ,… ,cn 为 目标系数，
6
You can search by the keyword “solver” in VBE for helps
on SolverOptions function,
19
Solver
Solver Functions
The SolverSolve function performs the optimization. There are two things you should know about SolverSolve:
The SolverSolve function returns an integer value that indicates
Solver’s “success”:
The integer value 0 means that Solver was successful; The integer value 1 and 2 also indicate success; The integer value 4 means that Solver did not converge;
The MaxMinVal is 1 for a maximization problem and 2 for a
minimization problem
If there are several changing cell ranges, you can use Union in
the ByChange argument.
有一个目标函数，并表示为决策变量的线性函数。
存在若干个约束条件，且约束条件用决策变量的线性等 式或线性不等式来表达。
要求目标函数实现极大化(Max)或极小化(Min)。
5
线性规划模型
线性规划模型形式
目标函数 Max (Min) z = c1 x1 + c2 x2 + … + cn xn 约束条件：a11 x1 + a12 x2 + … + a1n xn ( =, ) b1
Lecture 01
线性规划及应用开发
1
内容提要


线性规划模型
Solver A Product Mix Application
2
线性规划模型
问题的提出
在生产管理的经营活动中，需要对有限的资源寻
求最佳的利用或分配方式。
任何资源都是有限的，如劳动力、原材料、设备
或资金等。因此，必须进行合理的配置，寻求最 佳的利用方式。
SolverOptions AssumeLinear:=True, AssumeNonneg:=True
In general, any number of options can follow the
SolverOptions function (all separated by commas).
各种系数（ c1 , c2 , … , cn； a11 , a12 , … , amn； b1 , b2 , … , bm）
G
决策模型
——
在若干个约束条件下，使目标函数达到极大
或极小的决策问题
7
线性规划模型
一个线性规划实例

决策变量：x1 = 产品 I 的计划生产量 目标函数： （Max）总获利 = 2x1 + 1x2
9
Solver
Solve the Model by Menu-driven way
Excel’s Solver is invoked to obtain an optimal solution for
optimization models.
It has a very user-friendly Excel interface, shown by the dialog
5 for “binary” (note that there is no right-hand-side argument for the latter two options).
o A right-hand side: must be specified as a string or a number.
加载 “规划求解” (Solver)
如果在您的Excel中，没有在“工具”菜单中发现“规划求解”，
请在下图所示的界面中点击“加载宏”。
11
Solver
Solve the Model by Menu-driven way
加载 “规划求解” (Solver)
点击“加载宏”后，显示界面如下：
Specially, this is true of the Solver optimization add-in
that is part of Microsoft Office.
The Solver can be manipulated not only through the
familiar menu interface, but it can also be manipulated “behind the scenes” with VBA code.
“规划求解” 的 “选项” 设置及运行
典型的选择：
采用线性模型 假定非负
15
Solver
Solver Functions
All of the Solver functions begin with the word Solver. The ones used most often are: SoloverReset, SolverOk,
minimization problem
o Identifies the changing cells
The following line is typical:
SolverOk SetCell:=Range(“Cost”), MaxMinVal:=2, ByChange:=Range(“Prod”)
SolverAdd, and SolverSolve.
To reset the Solver, use the line:
SolverReset
This clears all previous settings and lets you start with a
clean slate.
16
Solver
所谓最佳方式，必须有一个标准或目标，这个标
准或目标就是使利润达到最大或成本达到最小。
3
线性规划模型
问题的提出
有限资源配置问题可归纳为以下两类问题：

如何合理地使用有限的资源，使生产经营的效益达到最大 在生产或经营任务确定的情况下，如何合理地组织生产，安 排经营活动，使资源消耗最少。
与规划问题有关的数学模型总由以下两部分组成：
Solver Functions
The SolverOk function does three things:
o Identifies the target cell (the objective) o Specifies whether the problem is a maximization or
Here are several possibilities:
SolverAdd CellRef:=Range(“Used”), Relation:=1, FormulaText:=“Available” SolverAdd CellRef:=Range(“EndInventory”), Relation:=3, FormulaText:=0 SolverAdd CellRef:=Range(“Investments”), Relation:=5
18
Solver
Solver Functions
The SolverOptions function allows you to set options. The two you will probably set most frequently are the
Assume Linear Model and Assume NonNegative options. They can be set as follows.
17
Solver
Solver Functions
The SolverAdd function adds a new constraint each time it
is called. It takes three arguments: o A left-hand side: must be specified as a range. o A relation index: 1 for “<=“, 2 for “=“, 3 for “>=“, 4 for “integer”,
If it is used with the argument UserFinish:=True, then the dialog
box, which probably appears just after the optimization has been finished, will not appear. (note that UserFinish:=False is the default value.)
x2 = 产品 II 的计划生产量