《第3章excel在数学规划和统计中的应用讲义》由会员分享，可在线阅读，更多相关《第3章excel在数学规划和统计中的应用讲义（59页珍藏版）》请在金锄头文库上搜索。
1、第3章 Excel 在数学分析中的应用,本章内容： 第一节 Excel在线性规划中的应用 第二节 Excel在统计分析中的应用,第1节 Excel 在线性规划中的应用,本章重点： 学习Excel 规划求解方法 掌握Excel数学分析工具,一、线性规划模型简介,线性规划模型应用程度名列前矛 。美国财富杂志对全美前500家大公司的调查表明，有85%的公司频繁地使用线性规划，并取得了显著的经济效益。 线性规划可以用单纯形法笔算求解，但计算量较大，尤其对多变量的规划求解，而且在敏感性分析中要做大量的重复性工作。,一、线性规划模型简介,Excel提供了超强的数学运算、统计分析功能，利用它的规划求解功能就
2、可以快速、高效地求解线性规划问题。,一、线性规划模型,数学模型： 目标函数：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,一、线性规划模型,其中： S为目标函数； Xj为决策变量； aij为技术系数； bi为约束值； Cj为费用系数； m为约束条件的个数; n为变量个数。,一、线性规划模型,单纯型法原理：在找出一个基可行解后，判断它是否为最优解，如果不是，则另外换一个基可行解，直到
3、得到问题的解答。整个计算过程实际上一个在基可行解上的迭代过程。 由于基可行解是有限的，如果有最优解，则经过有限步迭代可以达到。,二、 Excel求解线性规划问题,步骤： 1、加载【规划求解】宏 2、定义规划求解参数 【目标单元格】 【等于】 【可变单元格】 【约束】 【选项】 3、求解,【目标单元格】是指选定计算问题的目标并含有计算公式的单元格。 【等于】按问题的目标进行求解。 【可变单元格】是指决策变量（不含公式）所在的单元格，可以有多个单元格或区域 【约束】增加、修改、删除各个约束条件。 【选项】决定采用线性规划还是非线性规划。,案例研究,合理利用线材问题： 现要做100套钢架，每套用长度
4、分别为2.9m、 2.1m、 1.5m的圆钢各一根，已知原料长7.4m，问如何下料，使所用的原材料最省？ 为了节约原材料，需要进行套裁，有如下5套裁剪方案：,套裁方案,1. 建立线性规划模型,设按方案15下料的原材料根数分别为x1,x2,x3,x4,x5, 根据上表，列出数学模型: Min z = 0x1+0.1x2+0.2x3+0.3x4+0.8x5 且满足： x1+2x2+x4100 2x3+2x4+x5100 3x1+x2+2x3+x5100 x1,x2,x3,x4,x50,在Excel中输入数据,其中：,B3:G6为原始数据区； B12:F12为方案15的决策变量（x1,x2,x3,x
5、4,x5），即可变单元格； C8为目标单元格，输入目标函数； C14、C15、C16输入约束条件，等于约束条件的左式减去右式。,合理利用线材问题的计算公式,输入目标单元格、可变单元格和约束条件,求解,在【选项】中选择【线性规划模型】 一切设定好以后，单击【求解】按钮，规划工具开始运行； 运算结果有三个选项：【运算结果报告】【敏感性报告】【极限值报告】，分别生成三个报告。（见图1）,课堂作业,某药厂生产A、B、C三种药物，可供选择的原料有甲、乙、丙、丁四种，成本分别是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各种药物如表一所示。药厂要求每天生产A药品恰好100克、B药品至少530克、
6、C药品不超过160克。要求选配各种原料的数量，即满足生产的需要，又使总成本最少。,原料需求表,（1）建立线性规划模型：,设X1、X2、X3、X4分别表示甲、乙、丙、丁原料的用量，得到如下线性规划模型： 目标函数： Min Z5X16X27X38X4 约束条件： X1+ X2+ X3+ X4=100 5X1+4X2+6X3+5X4530 2X1+ X2+ X3+2X4160 X1, X2，X4，X30,在“规划求解参数”对话框中通过点击C8单元格使“目标单元格”出现$C$8的绝对引址，并根据本题题意在其后的小框框内选择“最小值”。 在“可变单元格”中通过从表格中选择D3:G3区域，使之在文本框内
7、出现$D$3:$G$3。 在“约束条件”处按“增加”，然后在出现的“增加约束”对话框中的“单元格引用位置”处通过点击C5单元格使之出现$C$5,在后面的框框内选“”，“约束值”编辑为$B$5。,第二、三、四个约束条件分别编辑为“$C$6$B$6”,“$C$7$B$7”,“$D$3:$G$30”. 按“确定”退出。 按“求解”按钮，在弹出的“规划求解结果”对话框内可根据需要生成运算结果、敏感性分析和限制范围的报告，然后按“确定”对模型进行求解。,（6）如发现数字解为小数，可按需要该为用整数表示，方法如下： 按住Ctrl键，分别选定需改为用整数表示的单元格D3、E3、F3、G3、C8。 选取“格式
8、”、“单元格 ”、“数字”、“科学计数”。 在“小数位数”中选定“0”格式。按“确定”退出。,第二节 Excel在统计分析中运用,一、相关分析,相关分析是一种描述变量之间的相关程度的分析方法。 在相关分析中，所有变量都是随机变量，它们之间并不存在被解释变量和解释变量之间的关系。 相关分析有简单相关和多元相关、线性相关和非线性相关之间的关系、正相关和负相关之分。,简单相关分析,总体相关系数 Cov(x,y)表示随机变量x与y的协方差 Var(x)表示随机变量x的方差 Var(y)表示随机变量y的方差,总体相关系数满足如下性质：,|xy|1 |xy|=1的充要条件是X和Y依概率线性相关。 总体相关
9、系数xy一般是得不到的，我们只能根据样本观测值估计。,样本的相关系数,设随机变量x,y的一组样本为(xi, yi)，i=1,2,，n，则定义x的样本方差为S2x，y的样本方差为S2y，x和y的样本协方差为Sxy，则：,VARP,返回样本总体的方差。 语法：VARP(number1,number2) 说明：,COVER,返回协方差，即每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系。 语法：COVER(array1,array2) array1:第一个单元格区域 array2:第二个单元格区域 公式：,CORREL,相关系数函数,例题1,某机构调查10个公司的年龄和年销售额（
10、万元）的统计数据。,计算结果,多元相关,对于若干变量之间的关联程度进行估计。,多元相关系数是度量一个变量与其他所有变量相关程度的数量指标。记为R。 R定义为最小二乘估计值与变量Y的观测值的简单相关系数。,多元相关系数R,式中：,式中： R表示变量Y与变量x2,x3,xk的相关程度用多重相关系数,案例,二. 回归分析,回归分析是通过一定的数学表达式描述变量之间的数量变化关系，并进行预测。 回归分析分为：一元线性回归分析、多元线性回归分析、非线性回归分析、曲线估计、时间序列的曲线估计等模型。,回归分析和相关分析都是研究变量间关系的统计学课题。但两者有所侧重： 在回归分析中，变量Y成为因变量，自变量
11、x可以是随机变量；而在相关分析中，x,y都是随机变量； 回归分析通过一定的数学表达式描述变量之间的数量变化关系，并进行预测。,一元线性回归分析,最简单的情形是一个自变量和一个因变量，且它们大体上有线性关系，这叫一元线性回归。,计算公式,其中： X是自变量；y是因变量； 0是回归常数；1是回归系数； 是随机扰动项,最小二乘法,基本思想是使残差e的平方： e2=( y-)2 最小,例题1数据分析中的回归分析,例题2： 旅行成本法(TCM),森林生态系统价值评价在全球范围内成为环境经济学和生态经济学的研究热点和焦点之一. 条件价值法和旅行成本法是评价公共物品尤其是环境物品价值的两种标准方法。,旅行成
12、本法(TCM),基本思路是将去某一景点的游客的旅行费用当作“门票”一样对待。 游客对同一景点有相同的需求曲线，旅行成本增加，旅游次数减少。 旅行成本法的目的是建立旅行次数(出游率)与门票费用的对应关系(即需求曲线)。从而计算消费者剩余，以对景点进行评价。,案例,千人访客率=-10*门票+70 依据此公式，求出不同地点在不同门票(旅行成本)下的旅行者人数。 然后对每一个门票的旅行人数加总。 求不同门票下的总收入 加总的总收入即为此旅游景点的总价值(消费者剩余),作业1,一贸易公司专门经营某种杂粮的批发业务。公司现有库容5000担的仓库。1月1日，公司拥有库存1000担杂粮，并有资金20000元。估计第一季度杂粮价格如下表所示。,各月份的进货单价及出货单价,如买进的杂粮当月到货，但需到下月才能卖出，且规定“货到付款”。公司希望本季末库存为2000担。问：应采取什么样的买进与卖出的策略使3个月总的获利最大？,
