|
摘要:Ms
Excel除了有强大的表处理功能外，还能强有力地进行数据处理和数据分析。
本文首先介绍了用Excel求解线性代数运算问题的基本方法。以此为工具，进而介绍了Excel用于（1）多元线性回归模型的求解。（2）线性规划问题的求解。此类经济数学问题，由于数据量大，用常规的手工方式求解往往工作量较大，易出错。用Excel求解，只需按文中介绍的格式输入待处理数据后，借助于Excel数据分析工具，可迅速地建立起相应的数学模型，并给出令人满意的求解结果。
[关键词]源矩阵source
matrix 投入产出模型input-out
model 多元线性回归 multiple
linear regression 线性规划
Linear programming
微软公司开发的Excel是一个功能强大、技术先进，使用十分方便的表格式数据处理与分析软件。它以图、文、表三者完美结合的形式，被有效地利用于大量数据处理问题。本文在经济数学模型范围内，谈谈Excel在这方面的应用。
（一）线性运算基础
在基本线性运算中，矩阵运算的主要形式有（1）求转置矩阵，（2）同型矩阵求和运算，（3）求矩阵乘积，（4）矩阵求逆，（5）求行列式的值。对于上述运算，利用Excel所提供的函数工具，可方便地求出结果。现以求两矩阵乘积为例，讨论Excel求解矩阵问题的一般方法。
问题：设A为m×t防矩阵，B为t×n阶矩阵，求A×B。求解方法为：（1）在Excel工作表空白区域分别输入矩阵A与矩阵B。
（2）由于A×B的积为m×n阶矩阵，故必须在工作表中，用鼠标拖曳选中m行n列的空白区域，待存放结果矩阵。
（3）点击“函数指南”fx按钮，在对话框中选择“数学与三角函数”类的MMULT（array1,array2）函数，继续下一步。
（4）将工作表中A矩阵所在的区域选中，使该区域标识符出现在对话框内array1所示的文本框中。用同样方法将B矩阵所在区域的标识符输入array2所示的文本框中，然后按“完成”按钮。
（5）由于A×B的结果矩阵是一个区域值，对于这样的运算，计算公式输入后，必须按Ctrl+Shift+Enter复合键，使公式两端括上{
}，才能正确完成计算。因此，此时应点击编辑栏中的公式：=MMULT（A矩阵区域，B矩阵区域），再按Ctrl+Shift+Enter，则（2）所选的结果区域，出现了AXB的乘积矩阵。要说明的是：这个{
}必须按复合键括上，不能直接按键输入，否则Excel将把计算公式认作为一个字符串来处理。
由这个例子我们可以概括出利用Excel函数求解矩阵运算的一般步骤：
（1）输入源矩阵→（2）选定结果矩阵存放区域→（3）构造计算公式，选取相应的函数对源区域数据进行运算→（4）按复合键使计算公式括上{
}，便可得到计算结果。按照这个求解步骤，如果选用MINVERSE（array），可求解逆矩阵。选“查找与引用”类的“TRANSPOSE（array），可求矩阵的转置。（任意一张带有栏目标题的二维表格作为广义矩阵，可用此函数实现行列对换。）利用MDETERM（array），可求n阶行列式的值。对于矩阵求和问题，上述步骤（3）由于无对应函数可选，可直接在编辑栏中输入公式：=A矩阵区域+B矩阵区域。此外，若干个同型矩阵还可通过“数据”菜单的“合并计算”，进行求和、求平均、行数、求最大最小、求对应元乘积、求标准差等等运算。
（二）多元线性回归模型求解
在实际经济问题中，当研究对象受多因素x1,x2…xk影响可用
yn=β0+β1x1+β2x2+…βkxik+εi（i=1,2…n）描述出来时，称为多元线性回归模型。作为影响因素的自变量xi较多时，计算多元线性回归模型可能变得十分复杂。而使用Excel计算多元线性回归模型，则十分方便简捷。
问题：设y为早稻收获量，以每亩100kg为单位。x1为春季雨量，以33mm为单位，x2为春季温度，现经实验取得资料如下：
|
|
A
|
B
|
C
|
|
1
|
收获量
|
雨量x1
|
温度x2
|
|
2
|
1.5
|
2.5
|
0.5
|
|
3
|
2.3
|
3.3
|
2.0
|
|
4
|
3.0
|
4.5
|
5.0
|
|
5
|
4.5
|
10.5
|
8.0
|
|
6
|
4.8
|
11.0
|
9.0
|
|
7
|
5.0
|
11.5
|
10.0
|
|
8
|
5.5
|
12.0
|
11.0
|
求：x1与x2对y的线性回归方程式。
求解方法：
（1）按图示坐标位置，将上述资料输入Excel工作表。
（2）先进行相关分析（根据相关系数的大小对变量进行筛选，以剔除不相关或相关性小的变量。）
（a）选“工具”菜单“加载宏”选项，装入“数据分析”加载宏，此时，在“工具”栏菜单将会出现“数据分析”菜单选项。
（b）选“工具”菜单的“数据分析”命令，在弹出的数据分析对话框中选“相关系数”工具，确定。
（c）在相关系数对话框中输入区域内，输入$A1:$H3，分组方式为逐列，标志为第1列，输出区域为$A$10:$D$13，则可得到相关分析结果为：
|
|
收获量
|
雨量x1
|
温度x2
|
|
收获量
|
1
|
|
|
|
雨量x1
|
0.983593
|
1
|
|
|
温度x2
|
0.995367
|
0.973257
|
|
可见收获是y与雨量x1，温度x2都有较强的相关性，可利用回归工具进一步建立回归方程式。
（3）建立回归模型：
选“工具”菜单中“数据分析”选项，弹出数据分析对话框，选择“回归”工具。确定，弹出回归对话框，在Y值输入区域输入$A$1:$A$8，在x值输入区域输入$B$1:$C$8，在输出区域输入$A$15:$I$46，设置置信度为95%，选中标志，残差，线性拟合图，正态概率图等复选框，确定后，便得到回归统计区域、方差分析区域、回归模型区域三个区域的一系列结果。从中可得到回归模型为：
y
= 1.240596
+ 0.101273x1 + 0.270669x2
t检验:(9.998425) (1.81539) (4.656657)
R2(R
square)=0.994932 (表征自变量x1,x2与因变量y相关程度密切)
R2（Adjusted)=0.992397
(调整后的值)
n=7
(观侧值)
F=392.5963
(F检验值，由F>Fα(k,n-k)-1)可看出回归效果显著）
S=0.13319
(标准差)
（三）线性规划问题求解
线性规划问题是利用线性代数在生产过程中实现科学管理的数学方法之一。利用Excel的“规划求解”加载宏，可方便地求解某些线性规划问题。
问题：某厂用A、B两种原料生产甲、乙两种农药。制造每吨甲农药或乙农药，需要原料A与原料B的比例，以及每吨农药可获的利润（万元）如下表所示。目前库存A原料28吨，B原料42吨。
|
|
农药甲
|
农药乙
|
库存原料（吨）
|
|
A原料
|
1
|
2
|
28
|
|
B原料
|
4
|
1
|
42
|
|
每吨农药可获利（万元）
|
7
|
5
|
|
求：（1）按现有的原料库存，生产农药甲与农药乙各多少吨，才能获得最大利润。
（2）要求获得利润24万元，该如何安排生产计划。
这个问题是线性规划中典型的任务安排问题。若用经典的单纯型方法求解，计算比较繁复，用Excel却很快地求得解答。
求解方法：
（1）将该线性规划问题的数学模型输入Excel表中。设甲、乙两种农药各为x1、x2万吨时，可获利S万元，数学模型为：
目标函数：max
S=7x1+5x2
约束条件：x1+2x2≤28
4x1+x2≤42
x1≥0,x2≥0
把此模型按下图样式输入Excel表中：
|
|
A
|
B
|
C
|
D
|
E
|
|
1
|
目标函数
|
|
|
|
|
|
2
|
|
|
|
|
|
|
3
|
|
系数矩阵
|
解
|
约束条件
|
|
4
|
x1
|
1
|
2
|
|
28
|
|
5
|
x2
|
4
|
1
|
|
42
|
|
6
|
每吨利润（万元）
|
7
|
5
|
|
|
（2）在目标函数单位格B1上，键入公式：=B6*D4+C6*D5。
（3）选择“工具”菜单中“加载宏”选项，装入“规划求解加载宏”。此时，在“工具”菜单中，就会出现“规划求解”的菜单选项。
（4）选择“工具”菜单的“规划求解”选项，将弹出“规划求解参数”对话框。在对话框“目标单元格”文本输入栏处，通过点击B1单元格，使之出现$B$1绝对引用地址。并根据本题题意，选最大值单选钮。
在“可变单元格”文本输入栏处，从表中选择D4：D5区域，使之在文本框内出现$D$4:$D$5。
在“约束条件”处，按“增加”按钮，出现“增加约束”对话框。在“单元格引用”位置处，通过点击D4单元格，出现$D$4。“约束值”编辑为“<=$E$4-$C$4*$D$5”，按“添加”按钮，产生第一个约束条件。类似地，第二个约束条件编辑为“$D$5<$E$5-$B$5*$D$4。（注意，在“单元格引用”位置处，只能输入一个单元格地址。）
（5）按动“求解”按钮，将弹出“规划求解结果”对话框，可根据需要，生成运算结果，敏感度分析和限制范围的报告。现选择“运算结果报告”和“保存规划求解结果”单选钮，确定。这样就坐成了表标签为“运算结果报告1”的规划求解结果报告。本例问题（1）可求得x1=8,x2=10。即当甲农药生产8吨，乙农药生产10吨时，可获最大利润：S=7×8+5×10=106万元。
本例问题（2）提出的是一个已知目标函数值，反求可行解的问题。在线性规划中，这是个不那么容易求解的问题。利用Excel却容易求出答案。
求解方法：选择“工具”菜单的“规划求解”选项，在“规划求解参数”对话框中，选“值为”单选钮，并输入目标值x1，点击“求解”按钮，便可得到对应的可行解：x1=7,x2=5。
用Excel的规划求解工具解线性规划问题，事实上只要求在工作表中确定两处单元格位置即可：（1）目标函数公式所在的单元格位置。（2）“可变单元格”区域位置。（即最优解的结果单元格区域）。剩下的只是要求随着求解操作的进行正确地输入约束条件，正确地确定所求的目标值是最大还是最小。其余数据可以不输入工作表。但本文采用了将系数、约束条件等数据全部输入工作表，其优点是：（1）产生的求解结果报告清晰明了。（2）当同型问题仅仅是系数、约束数发生变化时，只要在工作表中将对应数据改掉，重新求解即可得到新的结果，使Excel在求解线性规划问题上的卓越能力充分显现出来。当然，根据实际情况和具体问题，合理地设计表式，恰当地加上一些行列标题，可以使表的可续性、实用性大大增加。
|
