Excel规划求解在经济管理中的应用

2012-04-29尹聪春

中国管理信息化 2012年4期

尹聪春

［摘要］规划求解是在一定的限制条件下，利用科学方法进行运算，使对前景的规划达到最优的方法，是现代管理科学的一种重要手段，是运筹学的一个分支。Excel规划求解在经济管理的很多方面都有应用，但它的应用在一般的教材和Excel的帮助中都没有详细的介绍。本文主要介绍用Excel规划求解工具来解决经济管理中的产品组合及投资效益问题，以供企业的经营管理者借鉴。

［关键词］Excel；规划求解；经济管理；应用

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 04. 021

［中图分类号］F275［文献标识码］A［文章编号］1673 - 0194（2012）04- 0036- 02

1分析工具及相关术语介绍

Ｅxcel规划求解是一组命令的组成部分，这些命令有时也称作假设分析工具。它就是使工作表中的某些单元格得到优化（最大化或最小化）。优化模型包括3部分：目标单元格、可变单元格和约束。

目标单元格代表目的或目标。例如，最大化每月利润、最小化每月成本等。可变单元格是电子表格中可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量、每月或每年的存款金额等。约束是置于可变单元格中的限制条件。例如，使用的资源不能超过其生产能力,生产的产量大于等于零等。

2案例资料

（1）某计算机制造企业生产鼠标、键盘和视频游戏操纵杠。各项数据见表1。

每月共提供了１３２００个人工工时和３０００小时的机器使用时间。该制造企业如何安排产量能使利润最大？

（2）某企业现有１０００００元准备存入银行，可以选择一年期、二年期和三年期存款，3种存款的年利率分别为２．５００％、２．７１４％、２．９３２％。第３年初和第５年初需要使用现金１００００元和２００００元，第４年初有５００００元的现金收入可以存入银行。试在Ｅｘｃｅｌ中建立模型，计算每年年初的到期本金、到期利息和年末现金余额；用规划求解工具求解每年年初每种存款的最优存款额，使第６年末的现金余额最大；把规划求解参数保存在合适的单元格中。

3模型建立１

3.1 计算各项指标

在ＳＨＥＥＴ１工作表中将已知各项指标填入相关单元格中，并进行相关计算。如图１所示。

其中Ｃ７：Ｅ７单元格区域假设为１，Ｃ８＝ＳＵＭ（Ｃ７：Ｅ７*Ｃ６：Ｅ６）、Ｆ３＝ＳＵＭ（Ｃ３：Ｅ３*Ｃ７：Ｅ７）、Ｆ４＝＝ＳＵＭ（Ｃ４：Ｅ４*Ｃ７：Ｅ７），这里需要用数组公式进行确定。

3.2 进行规划求解

单击“工具”菜单上的“规划求解”，在弹出的 “规划求解参数” 对话框中作如图如图２的设置。

在“设置目标单元格”框中单击，然后选择利润单元格（单元格Ｃ８），在“可变单元格”框中单击，指向区域Ｃ７：Ｅ７，该区域包含各类产品的产量。

添加约束：单击“添加”按钮，在“添加约束”对话框中，在标记为“单元格引用位置”的框中单击，选择区域Ｃ７：Ｅ７，从对话框中部的列表中选择“＞＝”，在标记为“约束值”的框中单击，选择单元格区域Ｃ５：Ｅ５。在“添加约束”对话框中单击“添加”，以输入需求约束。即可。

同理，再输入约束条件，Ｆ３：Ｆ４＜＝Ｇ３：Ｇ４。

在“规划求解选项”对话框中输入所有可变单元格都为非负值的约束，通过单击“规划求解参数”对话框中的“选项”按钮可打开该对话框。

选择“采用线性模型”和“假定非负”选项，然后单击“确定”。

注意：选择“假定非负”选项可确保规划求解只考虑每个可变单元格都采用非负值的可变单元格组合。