第3章Excel在数学规划和统计中的应用

1、第3章 Excel 在数学分析中的应用本章内容：本章内容：第一节第一节 Excel在线性规划中的应用在线性规划中的应用第二节第二节 Excel在统计分析中的应用在统计分析中的应用第1节 Excel 在线性规划中的应用本章重点：n学习Excel 规划求解方法n掌握Excel数学分析工具一、线性规划模型简介n线性规划模型应用程度名列前矛 。美国财富杂志对全美前500家大公司的调查表明，有85%的公司频繁地使用线性规划，并取得了显著的经济效益。n线性规划可以用单纯形法笔算求解，但计算量较大，尤其对多变量的规划求解，而且在敏感性分析中要做大量的重复性工作。一、线性规划模型简介nExcel提供了超强的数

2、学运算、统计分析功能，利用它的规划求解功能就可以快速、高效地求解线性规划问题。一、线性规划模型n 数学模型：目标函数：Max/Min S=C1X1+C2X2+.+CnXn约束条件： a11 X1 +a12 X2+.+a1n Xn(,)b1 a21 X1 +a22 X2+.+a2n Xn(,)b2 am1 X1 +am2 X2+.+amn Xn(,)bm X1 , X2. Xn0一、线性规划模型其中：nS为目标函数；nXj为决策变量；naij为技术系数；nbi为约束值；nCj为费用系数；nm为约束条件的个数;nn为变量个数。一、线性规划模型n单纯型法原理：在找出一个基可行解后，判断它是否为最优解

3、，如果不是，则另外换一个基可行解，直到得到问题的解答。整个计算过程实际上一个在基可行解上的迭代过程。n由于基可行解是有限的，如果有最优解，则经过有限步迭代可以达到。二、 Excel求解线性规划问题步骤：步骤：1、加载【规划求解】宏2、定义规划求解参数 【目标单元格】 【等于】 【可变单元格】 【约束】 【选项】3、求解 【目标单元格】是指选定计算问题的目标并含有计算公式的单元格。【等于】按问题的目标进行求解。【可变单元格】是指决策变量（不含公式）所在的单元格，可以有多个单元格或区域【约束】增加、修改、删除各个约束条件。【选项】决定采用线性规划还是非线性规划。案例研究案例研究合理利用线材问题：n

4、现要做100套钢架，每套用长度分别为2.9m、 2.1m、 1.5m的圆钢各一根，已知原料长7.4m，问如何下料，使所用的原材料最省？n为了节约原材料，需要进行套裁，有如下5套裁剪方案：套裁方案方案1方案2方案3方案4方案5下料 2.9m12010根数 2.1m002211.5m31203合计7.47.37.27.16.6料头(m)00.10.30.30.81. 建立线性规划模型n设按方案15下料的原材料根数分别为x1,x2,x3,x4,x5, 根据上表，列出数学模型: Min z = 0 x1+0.1x2+0.2x3+0.3x4+0.8x5 且满足： x1+2x2+x4100 2x3+2x4

5、+x5100 3x1+x2+2x3+x5100 x1,x2,x3,x4,x50在Excel中输入数据其中：nB3:G6为原始数据区；nB12:F12为方案15的决策变量（x1,x2,x3,x4,x5），即可变单元格；nC8为目标单元格，输入目标函数；nC14、C15、C16输入约束条件，等于约束条件的左式减去右式。合理利用线材问题的计算公式单元公式说明C8=B6*B12+C6*C12+D6*D12+E6*E12+F6*F12料头C14 =G3-B3*B12-C3*C12-D3*D12-E3*E12-F3*F122.9m圆钢C15 =G4-B4*B12-C4*C12-D4*D12-E4*E12-

6、F4*F122.1m圆钢C16 =G5-B5*B12-C5*C12-D5*D12-E5*E12-F5*F121.5m圆钢输入目标单元格、可变单元格和约束条件求解n在【选项】中选择【线性规划模型】n一切设定好以后，单击【求解】按钮，规划工具开始运行；n运算结果有三个选项：【运算结果报告】【敏感性报告】【极限值报告】，分别生成三个报告。（见图1）课堂作业n某药厂生产A、B、C三种药物，可供选择的原料有甲、乙、丙、丁四种，成本分别是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各种药物如表一所示。药厂要求每天生产A药品恰好100克、B药品至少530克、C药品不超过160克。要求选配各种原料的

7、数量，即满足生产的需要，又使总成本最少。原料需求表 原料药物甲5乙6丙 7丁8A1111B5465C2112（1）建立建立线性规划模型：设X1、X2、X3、X4分别表示甲、乙、丙、丁原料的用量，得到如下线性规划模型：目标函数： Min Z5X16X27X38X4约束条件： X1+ X2+ X3+ X4=100 5X1+4X2+6X3+5X4530 2X1+ X2+ X3+2X4160 X1, X2，X4，X30n在“规划求解参数”对话框中通过点击C8单元格使“目标单元格”出现$C$8的绝对引址，并根据本题题意在其后的小框框内选择“最小值”。n在“可变单元格”中通过从表格中选择D3:G3区域，使

8、之在文本框内出现$D$3:$G$3。n在“约束条件”处按“增加”，然后在出现的“增加约束”对话框中的“单元格引用位置”处通过点击C5单元格使之出现$C$5,在后面的框框内选“”，“约束值”编辑为$B$5。n第二、三、四个约束条件分别编辑为“$C$6$B$6”,“$C$7$B$7”,“$D$3:$G$30”. n按“确定”退出。按“求解”按钮，在弹出的“规划求解结果”对话框内可根据需要生成运算结果、敏感性分析和限制范围的报告，然后按“确定”对模型进行求解。n（6）如发现数字解为小数，可按需要该为用整数表示，方法如下： 按住Ctrl键，分别选定需改为用整数表示的单元格D3、E3、F3、G3、C8。

9、 选取“格式”、“单元格 ”、“数字”、“科学计数”。 在“小数位数”中选定“0”格式。按“确定”退出。第二节 Excel在统计分析中运用一、相关分析n相关分析是一种描述变量之间的相关程度的分析方法。n在相关分析中，所有变量都是随机变量，它们之间并不存在被解释变量和解释变量之间的关系。n相关分析有简单相关和多元相关、线性相关和非线性相关之间的关系、正相关和负相关之分。简单相关分析n总体相关系数nCov(x,y)表示随机变量x与y的协方差nVar(x)表示随机变量x的方差nVar(y)表示随机变量y的方差)()(),(yVarxVaryxCovxy总体相关系数满足如下性质：n|xy|1n|xy|

10、=1的充要条件是X和Y依概率线性相关。总体相关系数xy一般是得不到的，我们只能根据样本观测值估计。样本的相关系数n设随机变量x,y的一组样本为(xi, yi)，i=1,2,，n，则定义x的样本方差为S2x，y的样本方差为S2y，x和y的样本协方差为Sxy，则：1)(22nxxSix1)(22nyySiy1)(2nyyxxSiixyVARP返回样本总体的方差。语法：VARP(number1,number2)说明：222)(nxxnVARPCOVER返回协方差，即每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系。语法：COVER(array1,array2) array1:第一

11、个单元格区域 array2:第二个单元格区域公式：)( )(1),(1yjnjhjuyuxnYXCovCORREL相关系数函数yxyxyxCov),(,例题1n某机构调查10个公司的年龄和年销售额（万元）的统计数据。公司年龄X销售额Y13252106035254635512606156579608220995510750计算结果列 1列 2列 11列 20.9228081多元相关n对于若干变量之间的关联程度进行估计。iikkiiiuXXXY.33221n多元相关系数是度量一个变量与其他所有变量相关程度的数量指标。记为R。nR定义为最小二乘估计值与变量Y的观测值的简单相关系数。多元相关系数R22

12、.)()(21YYYYSSSrRiiYYYYYYxxxYk1)(nYYSiY1)(2nYYSiY式中：1)(nYYYYSiiYY式中：nR表示变量Y与变量x2,x3,xk的相关程度用多重相关系数案例二. 回归分析 n 回归分析是通过一定的数学表达式描述变量之间的数量变化关系，并进行预测。n回归分析分为：一元线性回归分析、多元线性回归分析、非线性回归分析、曲线估计、时间序列的曲线估计等模型。 回归分析和相关分析都是研究变量间关系的统计学课题。但两者有所侧重：n在回归分析中，变量Y成为因变量，自变量x可以是随机变量；而在相关分析中，x,y都是随机变量；n回归分析通过一定的数学表达式描述变量之间的数

13、量变化关系，并进行预测。一元线性回归分析n最简单的情形是一个自变量和一个因变量，且它们大体上有线性关系，这叫一元线性回归。计算公式xy10其中：X是自变量；y是因变量；0是回归常数；1是回归系数；是随机扰动项最小二乘法基本思想是使残差e的平方： e2=( y-)2 最小21)()(xxyyxxxy10年份粮食产量(万吨)化肥(万吨)播种面积(公顷)19882097.5291.9913943419892134.5333.6906369419902148.7386.2884238719912314.5502902339219922217.1541.5890401619932904.0610.893

14、0769219942893.5661.5901401919952710.5662.7903500019962545.7677.5937642719972948.4637.4936000019982663.0724.59057823例题1数据分析中的回归分析例题2： 旅行成本法(TCM)n森林生态系统价值评价在全球范围内成为环境经济学和生态经济学的研究热点和焦点之一.n条件价值法和旅行成本法是评价公共物品尤其是环境物品价值的两种标准方法。旅行成本法(TCM)n基本思路是将去某一景点的游客的旅行费用当作“门票”一样对待。n游客对同一景点有相同的需求曲线，旅行成本增加，旅游次数减少。n旅行成本法的目

15、的是建立旅行次数(出游率)与门票费用的对应关系(即需求曲线)。从而计算消费者剩余，以对景点进行评价。案例访问人数访问人数访客出发地访客出发地旅行里程旅行里程2501504002100450031501600420030005250出发地出发地旅行成本旅行成本(元元)访问人数访问人数 出发地总出发地总人口人口每千人访每千人访问率问率122505,000502340010,00040344500150,0003045160080,00020563000300,00010n千人访客率=-10*门票+70n依据此公式，求出不同地点在不同门票(旅行成本)下的旅行者人数。n然后对每一个门票的旅行人数加总。n求不同门票下的总收入n加总的总收入即为此旅游景点的总价值(消费者剩余)新增收新增收费值费值出发地出发地1的游客的游客数数出发地出发地2的游客的游客数数出发地出发地3的游客的游客数数出发地出发地4的游客的游客数数出发地出发地5的游客的游客数数游客总游客总数数总收入总收入030050060002400600015，200 012504004500160030009，750 975022003003000 800 04，300 860031502001500 01，850 555041