试论EXCEL的规划求解在经济管理中的应用

摘要：提高企业的经济效益是现代化管理的根本任务，各个领域中的大量问题都可以归结为线性规划问题。近几十年来，线性规划在各个行业中都得到了广泛的应用，如最优化问题的求解。所谓最优化问题，就是在给定条件下寻找最佳方案的问题。即在资源给定时寻找最好的目标，或在目标确定下使用最少的资源。通常最优化求解方法有公式法、用规划求解工具求解以及查表法等。本文利用Excel提供的规划求解工具解决了在生产经营管理中的仓库运输最低费用以及利润最大化的问题，以供同行参考。

关键词：EXCEL；规划求解；应用

0引言

“规划求解”是Office 2000提供的一个加载宏。如果在安装Office 2000时没有选择加载宏，就必须重新启动Office 2000安装程序并且选择Excel选项，在加载宏区段中选择“规划求解”，然后进行安装。安装了“规划求解”之后，在“工具”菜单下可能仍然找不到“规划求解”，此时可以选择“工具”-“加载宏”，在打开的“加载宏”对话框中选中“规划求解”复选框，确定后，就可以将“规划求解”命令添加到“工具”菜单栏中。

Excel的“规划求解”有很强的功能，可以对有多个变量的线性和非线性规划问题进行求解，省去了人工编制程序和手工计算的麻烦。

1应用实例⑴

某公司有甲、乙、丙3个企业，生产的产品需运送到6个仓库，3个企业的生产能力为320、270和280，每个仓库的需求量为180、80、195、130、200和85。从企业运到各仓库的运费如表1所示。试在Excel中建立规划求解模型，并且按如下要求操作：计算各企业的运出量、各仓库的运入量和总费用；用规划求解工具求解各企业向各仓库的最优运送量和总费用的最小值。

首先在EXCEL某一工作表中建立数据清单，如图1所示。假设每个企业向每个仓库都运送1个产品，设置各项指标的公式如下：

I8=SUM（C8:H8），复制公式至I11；C11=SUM（C8:C10），复制公式至H11；

C13={SUM（C8:G10*C3:G5）}，注意这是一个数组函数。

选中数据清单中的任一单元格，单击“工具”-“规划求解”，在弹出的“规划求解”对话框中作如图2的设置，单击“求解”按扭，所形成的规划求解结果如图3所示。

从图中可以看出，总费用最低为3460时，各仓库的运送量分布。

如果问题没有可行解，规划求解将会显示明确的信息“规划求解找不到有用的解”。如果最优目标值是无界的，规划求解将会显示不太明确的信息“设置目标单元格的值未收敛”。这些情况都表明模型构造的公式有错误。当然，规划求解工具还可以得出更详细的报告。

2应用实例⑵

某企业生产A、B、C三种产品，其成本、生产时间、每件产品的利润以及各限定条件如表2所示：要求各产品的实际产品为多少时能使利润最大。

首先在EXCEL某一工作表中建立数据清单，如图4所示。

假设实际产量为1，设置各项指标的公式如下：

F2=D2*G2，复制公式至F4；H2=G2*B2，复制公式至H4；

I2= C2/60*G2，复制公式至I4；F5=SUM（F2:F4），复制公式至I5；

选中数据清单中的任一单元格，单击“工具”-“规划求解”，在弹出的“规划求解”对话框中作如图5的设置。其单击“求解”按扭，所形成的规划求解结果如图6所示。

从图中可以看以，最大利润为13440时的各产品的实际产量分布。

3小结

用户不但可以利用Excel中的[工具]-[规划求解]命令对目标规划进行求解，而且可以运用Excel的[工具][方案]命令来保存计算结果，利用Excel提供的这一功能用户可以完成对目标规划的灵敏度分析，因而轻易地获得由于调整目标优先权（Pl）、资源约束（bi）或者资源消耗系数（aij）而产生的新方案，从而帮助管理者甄别各种不同方案，为其做出科学决策提供支持。

参考文献：

[1] 周德镇. Excel在现代管理中的应用[M].北京：电子工业出版社，1997.

[2] EXCEL HOME.EXCEL应用大全.中国邮电出版社,2008.

注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文