Excel2010中规划求解的应用

【摘 要】excel2010具有强大的电子表格处理功能，使用它可以进行各种数据处理、统计分析和辅助决策等。规划求解是一组命令中的一部分，有时称为假设分析工具。通过使用规划求解，可以根据对工作表中单元格值的约束或限制找出目标单元格中公式的最优（最大或最小）值。

【关键词】Excel2010；规划求解；值班表

Microsoft Excel中的规划求解工具取自德克萨斯大学奥斯汀分校的 Leon Lasdon 和克里夫兰州立大学的 Allan Waren 共同开发的 Generalized Reduced Gradient （GRG2） 非线性最优化代码。规划求解是一组命令的组成部分，这些命令有时也称作假设分析工具，即该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响，规划求解主要是为工作表中的目标单元格中的公式找到一个优化值，在保证工作表中的其他数据保持在设置的范围之内时通过改变输入值从而求出最优解。

1 特点

1.1 规划问题的特点

一般来讲，适合使用规划求解的问题具有如下特点：

（1）目标单元格的解都有单一的目标，如求运输的最佳路线，求值班人员的最佳安排时间表，求产品的最低成本等等。

（2）对于目标单元格的解存在有明确的可以用不等式表达的约束条件和限制。

（3）可以把问题的表达描述为：一组约束条件及限制（不等式），一个目标方程。

（4）输入值直接或间接地影响约束条件和目标单元格的解。

（5）利用Excel可以简单的求得问题满足约束条件和限制求得的目标最优解。

1.2 规划求解的特点

（1）有多个可以调整的单元格。

（2）可以通过更改其他单元格来确定某个单元格的最大值或最小值。

（3）可以指定可调整单元格可能的数值约束。

（4）一个问题可以有多个解。

2 安装规划求解插件

由于在Excel2010中规划求解是一个插件，并不显示在选项卡中，因此在使用规划求解之前要先进行安装。安装规划求解插件的方法有两种，第一种方法具体步骤如下。

（1）在Excel2010窗口单击【文件】选项卡，在打开的菜单中点击【选项】命令。

（2）在弹出的【Excel选项】对话框中，左侧列表选择【加载项】，然后打开右侧的【管理】下拉菜单，选择【Excel加载项】，单击【转到】按钮。

（3）在弹出的【加载宏】对话框中，勾选【规划求解加载项】，单击【确定】按钮。

（4）在功能区切换到【数据】选项卡，可以看到【规划求解】在【分析】组中，鼠标悬停在【规划求解】按钮上，可得到该功能的即时帮助信息。

第二种方法具体步骤如下。

（1）第一步和第一种方法相同，在Excel2010窗口单击【文件】选项卡，在打开的菜单中点击【选项】命令。

（2）在弹出的【Excel选项】对话框中，左侧列表选择【自定义功能区】，然后勾选右侧主选项卡中的【开发工具】，单击【确定】按钮。

（3）回到Excel2010主界面，此时选项卡上就多出了一项【开发工具】，单击其中的【加载项】按钮，在弹出的【加载宏】对话框中，勾选【规划求解加载项】，单击【确定】按钮。

3 规划求解的应用

通过应用Excel规划求解，可求得工作表上某个目标单元格中公式的最优值。规划求解将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。在创建模型过程中，可以对规划求解模型中的可变单元格数值应用约束条件，即规划求解中设置的限制条件，可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。而且约束条件可以引用其他影响目标单元格公式的单元格。下面通过一个具体的例子来说明如何使用Excel2010中的规划求解功能。

在大学学生会里需要安排干事值班，由于干事本身也是一名学生，在安排值班时必须考虑到该干事何时有课程，何时有其他活动安排等，因此做一份值班安排表也是学生会工作的重要内容之一。在安排值班表的过程中，常常会遇到值班时间与干事上课或活动时间冲突的情况，因此需要统筹规划根据干事的具体情况来安排。如我们假设在一个星期内各个干事的具体情况如下：

（1）小立只有周五有空，可以去学生会值班。

（2）小新只能紧跟着小王，也就是比小王晚一天值班。

（3）小倩要比小新早两天值班。

（4）小苏又要比小倩早两天值班。

（5）小鹏只要比小立早值班就行。

（6）小巍只要比小立晚值班就行。

（7）小辉哪天值班都可以。

步骤一：首先根据上面的情况，在Excel表格中输入如下内容：

图1

由于小鹏和小巍的情况无法用具体的数值来描述，所以在输入公式时引用了单元格E1作为变量来代表早或晚几天。

步骤二：设定规划求解参数

由于星期1到7之间的乘积值是固定的，等于5040，所以选定星期数的乘积作为目标值，在目标单元格B12内计算出当前各干事值班时间的乘积，等于6000，可以看出目标单元格内的值与目标值尚不符合。然后设置约束条件，设置参数如图2所示。

图2

步骤三：创建规划求解报告

单击【求解】按钮即可弹出【划归求解结果】对话框，在此对话框在红选中“运算结果报告”，最后单击【确定】按钮即可得到规划求解的结果，同时生成一份规划求解报告，如图3所示。

4 小结

由于在实际安排过程中，要求各异，会出现与本文所举实例不一致的情况，这就需要适当对其进行修改，使之能符合实际要求。

图3

总之利用Excel2010提供的规划求解功能可以各类线性规划问题、目标问题、运输问题、最短路径问题、最大流问题、数据包分析及其他运筹学问题，给工作带来了极大的方便，也提高了工作效率。

【参考文献】

[1]孙国俊.用Excel“规划求解”解决应用问题[J].微电脑世界，2001：8.

[2]何锦源.解析基于Excel2003进行规划求解[J].电脑编程技巧与维护，2010：14.

[3]孙爱萍，王瑞梅.如何利用Excel求解线性规划问题及其灵敏度分析[J].办公自动化，2009：22.

作者简介：王侃（1975.06―），女，汉族，讲师，主要从事计算机基础及软件编程的教学。