Excel 在经济数学模型中的应用

[table=560][tr][td=2,1]提高企业的经济效益是现代化管理的根本任务，各个领域中的大量问题都可以归结为线性规划问题。近几十年来，线性规划在各个行业中都得到了广泛的应用。根据美国《财富》杂志对全美前500家大公司的调查表明，线性规划的应用程度名列前矛，有85%的公司频繁地使用线性规划，并取得了显著提高经济效益的效果。

　　线性规划的求解可以用单纯形法笔算求解，但计算量较大，尤其对多变量的规划求解，而且在敏感性分析中要做大量的重复性工作。它还可以用Matlab数学软件及上海理工大学或国防科技大学研究出版的运筹学软件包求解，但这类软件相对来说难以掌握，而且运用不便。而Excel提供了超强的数学运算、统计分析等实用程序，利用它的规划求解功能就可以快速、高效地求解线性规划问题。

　　以下仅以一具体实例来阐明Excel在经济数学模型中的应用。

  　　原料配比问题

表  一

[table=98%,#ffffff][tr][td=1,2,31]原 料 [/td][td=4,1,204]药物 [/td][/tr][tr][td=1,1,48]甲 [/td][td=1,1,60]乙 [/td][td=1,1,48]丙 [/td][td=1,1,48]丁 [/td][/tr][tr][td=1,1,31]A [/td][td=1,1,48]1 [/td][td=1,1,60]1 [/td][td=1,1,48]1 [/td][td=1,1,48]1 [/td][/tr][tr][td=1,1,31]B [/td][td=1,1,48]5 [/td][td=1,1,60]4 [/td][td=1,1,48]6 [/td][td=1,1,48]5 [/td][/tr][tr][td=1,1,31]C [/td][td=1,1,48]2 [/td][td=1,1,60]1 [/td][td=1,1,48]1 [/td][td=1,1,48]2 [/td][/tr][/table]　　某药厂生产A、B、C三种药物，可供选择的原料有甲、乙、丙、丁四种，成本分别是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各种药物如表一所示。药厂要求每天生产A药品恰好100克、B药品至少530克、C药品不超过160克。要求选配各种原料的数量，即满足生产的需要，又使总成本最少。
[b]求解方法：[/b]

　　（1）建立简单的数学模型。根据题意，设X1、X2、X3、X4分别表示甲、乙、丙、丁原料的用量，易得到如下线性规划：

　　目标函数： Min Z＝5X1＋6X2＋7X3＋8X4
　　  约束条件： X1+X2+X3+X4=100
　　5X1+4X2+5X3+6X4≥530
　　2X1+X2+X3+2X4≤160
　　X1≥0,X2≥0，X3≥0

　　（2）将该线性规划问题的数学模型按表二样式输入Excel中，在表二中，有关单元格所含公式如下：

　　单元格 公 式

　　C5 =D3*D5+E3*E5+F3*F5+G3*G5
　　C6 =D3*D6+E3*E6+F3*F6+G3*G6
　　C7 =D3*D7+E3*E7+F3*F7+G3*G7
　　C8 =D2*D3+E2*E3+F2*F3+G2*G3

　　（3）选择“工具”菜单中“加载宏”选项，在安装提示下装入“规划求解”（注意要插入安装盘）。也可以把安装盘中“Pfiles\Office\Library”下的Solver文件夹及其目录下的Solver.xla、Solvr32.dll复制到Office安装目录“Office\Library”下，然后加载即可。

　　（4）在“工具”菜单中选择“规划求解”，然后在弹出的“规划求解参数”对话框中通过点击C8单元格使“目标单元格”出现$C$8的绝对引址，并根据本题题意在其后的小框框内选择“最小值”。在“可变单元格”中通过从表格中选择D3:G3区域，使之在文本框内出现$D$3:$G$3。在“约束条件”处按“增加”，然后在出现的“增加约束”对话框中的“单元格引用位置”处通过点击C5单元格使之出现$C$5,在后面的框框内选“＝”，“约束值”编辑为$B$5。类似地，第二、三、四个约束条件分别编辑为“$C$6≥$B$6”,“$C$7≤$B$7”,“$D$3:$G$3≥0”. 按“确定”退出。

　　（5）按“求解”按钮，在弹出的“规划求解结果”对话框内可根据需要生成运算结果、敏感性分析和限制范围的报告，然后按“确定”对模型进行求解。

　　（6）如发现数字解为小数，可按需要该为用整数表示，方法如下：

　　 ① 按住Ctrl键，分别选定需改为用整数表示的单元格D3、E3、F3、G3、C8。
　　 ② 选取“格式”、“单元格… …”、“数字”、“科学计数”。
　　 ③ 在“小数位数”中选定“0”格式。按“确定”退出。
　　（7）根据以上步骤，可得到本模型的计算结果如表三所示。从表三可以看出，当甲30公斤、丙40公斤、丁30>公斤而乙为0时，成本达到最小，最小成本为670元。

表 二 [table=322,#ffffff][tr][td=1,1,39]
[/td][td=1,1,36][align=center][b]A[/b][/align][/td][td=1,1,39][align=center][b]B[/b][/align][/td][td=1,1,38][align=center][b]C[/b][/align][/td][td=1,1,35][align=center][b]D[/b][/align][/td][td=1,1,39][align=center][b]E[/b][/align][/td][td=1,1,39][align=center][b]F[/b][/align][/td][td=1,1,26][align=center][b]G[/b][/align][/td][/tr][tr][td=1,1,39][align=center]1[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38] [/td][td=1,1,35]甲[/td][td=1,1,39]乙[/td][td=1,1,39]丙[/td][td=1,1,26]丁[/td][/tr][tr][td=1,1,39][align=center]2[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]数
量[/align][/td][td=1,1,35]5[/td][td=1,1,39]6[/td][td=1,1,39]7[/td][td=1,1,26]8[/td][/tr][tr][td=1,1,39][align=center]3[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]单
价[/align][/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]1[/td][/tr][tr][td=1,1,39][align=center]4[/align][/td][td=1,1,36] [/td][td=1,1,39][align=center]约
束
条
件[/align][/td][td=1,1,38][align=center]最
适
结
果[/align][/td][td=1,1,35] [/td][td=1,1,39] [/td][td=1,1,39] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,39][align=center]5[/align][/td][td=1,1,36]a[/td][td=1,1,39]100[/td][td=1,1,38] [/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]1[/td][/tr][tr][td=1,1,39][align=center]6[/align][/td][td=1,1,36]b[/td][td=1,1,39]530[/td][td=1,1,38] [/td][td=1,1,35]5[/td][td=1,1,39]4[/td][td=1,1,39]5[/td][td=1,1,26]6[/td][/tr][tr][td=1,1,39][align=center]7[/align][/td][td=1,1,36]c[/td][td=1,1,39]160[/td][td=1,1,38] [/td][td=1,1,35]2[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]2[/td][/tr][tr][td=1,1,39][align=center]8[/align][/td][td=2,1]总成本[/td][td=1,1,38] [/td][td=1,1,35] [/td][td=1,1,39] [/td][td=1,1,39] [/td][td=1,1,26] [/td][/tr][/table]

表 三

[table=322,#ffffff][tr][td=1,1,39]
[/td][td=1,1,36][align=center][b]A[/b][/align][/td][td=1,1,39][align=center][b]B[/b][/align][/td][td=1,1,38][align=center][b]C[/b][/align][/td][td=1,1,35][align=center][b]D[/b][/align][/td][td=1,1,39][align=center][b]E[/b][/align][/td][td=1,1,39][align=center][b]F[/b][/align][/td][td=1,1,26][align=center][b]G[/b][/align][/td][/tr][tr][td=1,1,39]
[/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38] [/td][td=1,1,35]甲[/td][td=1,1,39]乙[/td][td=1,1,39]丙[/td][td=1,1,26]丁[/td][/tr][tr][td=1,1,39][align=center]2[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]数
量[/align][/td][td=1,1,35]5[/td][td=1,1,39]6[/td][td=1,1,39]7[/td][td=1,1,26]8[/td][/tr][tr][td=1,1,39][align=center]3[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]单
价[/align][/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]1[/td][/tr][tr][td=1,1,39][align=center]4[/align][/td][td=1,1,36] [/td][td=1,1,39][align=center]约
束
条
件[/align][/td][td=1,1,38][align=center]最
适
结
果[/align][/td][td=1,1,35] [/td][td=1,1,39] [/td][td=1,1,39] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,39][align=center]5[/align][/td][td=1,1,36]a[/td][td=1,1,39]100[/td][td=1,1,38]100[/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]1[/td][/tr][tr][td=1,1,39][align=center]6[/align][/td][td=1,1,36]b[/td][td=1,1,39]530[/td][td=1,1,38]530[/td][td=1,1,35]5[/td][td=1,1,39]4[/td][td=1,1,39]5[/td][td=1,1,26]6[/td][/tr][tr][td=1,1,39][align=center]7[/align][/td][td=1,1,36]c[/td][td=1,1,39]160[/td][td=1,1,38]160[/td][td=1,1,35]2[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]2[/td][/tr][tr][td=1,1,39][align=center]8[/align][/td][td=2,1]总成本[/td][td=1,1,38]670[/td][td=1,1,35] [/td][td=1,1,39] [/td][td=1,1,39] [/td][td=1,1,26] [/td][/tr][/table]

　　用Excel的规划求解工具线性规划问题，简单易行，很容易掌握。其规律及技巧可归纳为：在实际的求解过程中，只需确定目标函数单元格及“可变单元格”区域位置两处单元格位置，然后正确地输入约束条件和确定所求的目标是最大还是最小即可求得正确结果。

　　利用Excel提供的规划求解法可以解运筹学中的许多问题，譬如线性规划、指派问题、运输问题、机器分配问题、人事安排… …等，只要是对生产、制造、投资、财务、工程等求最大利润、最小成本等问题，就基本上可以用规划求解法快速得到答案。h=39> [/td][td=1,1,38] [/td][td=1,1,35]甲[/td][td=1,1,39]乙[/td][td=1,1,39]丙[/td][td=1,1,26]丁[/td][/tr][tr][td=1,1,39][align=center]2[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]数
量[/align][/td][td=1,1,35]5[/td][td=1,1,39]6[/td][td=1,1,39]7[/td][td=1,1,26]8[/td][/tr][tr][td=1,1,39][align=center]3[/align][/td][td=1,1,36] [/td][td=1,1,39] [/td][td=1,1,38][align=center]单
价[/align][/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1[/td][td=1,1,26]1[/td][/tr][tr][td=1,1,39][align=center]4[/align][/td][td=1,1,36] [/td][td=1,1,39][align=center]约
束
条
件[/align][/td][td=1,1,38][align=center]最
适
结
果[/align][/td][td=1,1,35] [/td][td=1,1,39] [/td][td=1,1,39] [/td][td=1,1,26] [/td][/tr][tr][td=1,1,39][align=center]5[/align][/td][td=1,1,36]a[/td][td=1,1,39]100[/td][td=1,1,38]100[/td][td=1,1,35]1[/td][td=1,1,39]1[/td][td=1,1,39]1</T  [/td][/tr][/table]