Excel规划求解加载项

一. 前言

运用" 规划求解" 定义并求解问题 - Microsoft 支持

打开excel => 开发项 => 勾选确定

正常情况下在数据标签下, 会出现一个"分析"的组.

由于excel中集成的规划求解模块来自于:

" 规划求解" 程序代码的部分为 Frontline Systems, Inc 公司 1990-2009 年版权所有, 部分为 Optimal Methods, Inc 公司 1989 年版权所有.

Excel Solver - Using Solver from VBA | solver

鉴于微软对于VBA的摆烂态度, 64位的excel加载会直接报错.

fuck microsoft

使用32位excel, 没有此问题.

二. 示例

2.1 产品组合

以非常简单也最为常见的最大化利润问题为例:

项目
生产数量(预期)	x1	x2	x3	x4	x5	x6
产品编号	1	2	3	4	5	6
生产时间消耗	6	5	4	3	2.5	1.5
原材料消耗	3.2	2.6	1.5	0.8	0.7	0.3
需求	960	928	1041	977	1084	1055
利润	6	5.3	5.4	4.2	3.8	1

(在excel中 x1,.. x6使用任意数字替代, 这一组是变量, 填入任意数字不影响后续的计算.)

假设某工厂需要生成产品1 - 6, 已知现在有4500个单位的工作时长可以安排, 物料有1600个单位, 如何实现利润的最大化.

可以建立数学模型:

目标函数:\\ \max\; z= x_1 * 6 + x_2 * 5.3 + x_3 * 5.4 + x_4 * 4.2 + x_5 * 3.8+ x_6 * 1 \\ 约束:\\ s.t = \begin{cases} x_1 \in [0, 960]; \\ x_2 \in [0, 928]; \\ x_3 \in [0, 1041]; \\ x_4 \in [0, 977]; \\ x_5 \in [0, 1084]; \\ x_6 \in [0, 1055]; \\ x_1 * 6 + x_2 * 5 + x_3 * 4 + x_4 * 3 + x_5 * 2.5 + x_6 * 1.5 <= 4500;\\ x_1 * 3.2 + x_2 * 2.6 + x_3 * 1.5 + x_4 * 0.8 + x_5 * 0.7 + x_6 * 0.3 <= 1600;\\ x_1,...,x_6 \in \mathbb{N}^+,\; (上述变量均为正整数) \end{cases}

在计算之前, 最好了解一下Excel有趣函数系列1 - sumproduct() | Lian (kyouichirou.github.io), sumproduct()函数

在excel中的格式, 如图.

上述的目标函数max z的计算, 让生产数量 * 利润逐个加起来, 但是传统的计算过于麻烦, 这里使用sumproduct函数非常适合这个问题.

# 即目标函数的excel表达
= SUMPRODUCT(D9:I9,$D$2:$I$2)

# 约束条件 - 生产需要的劳动时间投入限制
=SUMPRODUCT(D4:I4,D2:I2)

# 约束条件 - 生产需要的原材料限制
=SUMPRODUCT(D5:I5, D2:I2)

调出操作窗体.

注意约束条件中的整数约束, 生产的产品是整数的.

即上述的执行步骤分为:

目标函数(即, 执行一个格子, 即相当于一个变量, 输入需要的公式).
创建辅助列(或者行), 即用于表示决策变量.
添加约束.

即完成规划模型的创建, 基本上所有的模型的解析均遵循上述的操作步骤.

求解结果.

在python中, 使用pyscipopt对问题进行求解:

from pyscipopt import Model

# 变量创建
x1 = model.addVar(vtype='I', name='x1')
x2 = model.addVar(vtype='I', name='x2')
x3 = model.addVar(vtype='I', name='x3')
x4 = model.addVar(vtype='I', name='x4')
x5 = model.addVar(vtype='I', name='x5')
x6 = model.addVar(vtype='I', name='x6')

# 约束添加
model.addCons(x1 * 6 + x2 * 5 + x3 * 4 + x4 * 3 + x5 * 2.5 + x6 * 1.5 <= 4500)
model.addCons(x1 * 3.2 + x2 * 2.6 + x3 * 1.5 + x4 * 0.8 + x5 * 0.7 + x6 * 0.3 <= 1600)
model.addCons(0<= (x1  <= 960)) # 表示上下限的方法, 必须加括号
model.addCons(0<= (x2  <= 928))
model.addCons(0<= (x3  <= 1041))
model.addCons(0<= (x4  <= 977))
model.addCons(0<= (x5  <= 1084))
model.addCons(0<= (x6  <= 1055))

# 目标函数
model.setObjective(x1 * 6 + x2 * 5.3 + x3 * 5.4 + x4 * 4.2 + x5 * 3.8+ x6 * 1, 'maximize')

# 求解
model.optimize()
sol = model.getBestSol()
objVal=model.getObjVal(model)

# 6624.8

2.2 时间序列

这里使用的是指数平滑(一次指数平滑)模型作为演示:

F_{t+1} = \alpha A_t + (1 - \alpha) F_t = (1 - \beta) A_t + \beta F_t\\ A_t,\; t时刻的实际值\\ F_t,\; t时刻的平滑预测值\\ 平滑系数: \alpha \in (0, 1)\\ 阻尼系数: \beta = 1 - \alpha\\ t = 1,....,T\\ \\ 这里的目标就在于得到最佳的参数: \alpha 或者 \beta, \;注意别搞混了

一般而言, 平滑系数遵守:

指数平滑法中, 平滑值的计算受平滑系数α的影响很大, α的取值不同, 计算出的平滑值差异也较大.

因此, 使用指数平滑方法, 平滑系数α的选择是个很重要的问题. α值的选择并没有一个固定的模式, 究竟怎样选择恰当的α值, 应视研究的目的任务和对象的性质特点灵活决定.

α值越小, 对序列的平滑作用越强, 跟踪数据越慢, 因而, 序列中随机波动较大时, 为了消除随机波动的影响, 可选择较小的α, 使序列较少受随机波动的影响. α值越大, 对序列的平滑作用越弱, 跟踪数据越快, 因而, 为了反映出序列的变动状况, 可选择较大的α, 使数据的变化很快反映出来. 如果主要依靠近期信息, α宜选择得大一些; 如果希望充分重视历史信息, α宜选择得小一些. 对初始值的正确性把握不大, 希望减小初始值的影响, 则α值宜大些; 反之, 对初始值的正确性把握性较大, 希望突出初始值的影响, 则α值宜小些. 总之, 选择使实际值和估计值均方误差最小的α.

首先随便生成一组数据

 = int( 100 * rand() )

time	sales(real)
1	45
2	68
3	33
4	20
5	83
6	61
7	85
8	53
9	65
10	87
11	30
12	81

在一文中提及, excel的另一个加载项-分析工具库

随意输入一个阻尼系数: 0.01

这个只是测试别搞乱公式, 在E2中选一个格子, 作为决策变量

# 公式根据, 上面的参数来修改

=$E$2 * C3 + (1 - $E$2) * B3

这里使用rmse(均方根误差)用以判断拟合值的好坏

rmse = \sqrt{\frac{1}{n}\sum_{i=1}^{n}{(y_i - \hat{y_i}) ^ 2}} = \sqrt{mse} = \sqrt{\frac{1}{n}rss}

# excel没有直接可以求rmse的公式

# 目标函数
= SQRT(SUMPRODUCT((B3:B13-D3:D13)^2)/COUNTA(B3:B13))

即, 可以拟合得到最优系数.

三. 小结

从使用上, excel的规划求解的使用并不难, 但是假如不了解sumproduct()函数, 该函数在各类型计算中高频使用, 计算上可能较为麻烦而已.

目标函数(即, 执行一个格子, 即相当于一个变量, 输入需要的公式).
创建辅助列(或者行), 即用于表示决策变量.
添加约束.

记住三板斧即可.

从微软的文档来看, 规划求解主要针对实际生产中的一些常见问题, 如产品组合, 投资组合等.