51Excel在数理统计中的应用

ExcelExcelExcelExcel在数理统计中的应用在数理统计中的应用在数理统计中的应用在数理统计中的应用2.1概率分布Excel提供概率函数P{X=x}和概率分布函数P{X≤x}或P{Xx}的一般形式xDIST（但泊松分布POISSON和韦伯分布WEIBULL例外），也提供累积概率分布函数的逆函数的一般形式xINV(但累积二项分布CRITBINOM例外)。具体相关函数请参考表2-2。按随机变量取值的特点，概率分布可以分为离散型随机变量的概率分布和连续型随机变量的概率分布。2.1.1离散概率分布以二项分布为例，Excel提供的常用二项分布函数有3个：二项分布函数BINOMDIST、累积二项分布的反函数CRITBINOM和负二项分布函数（即Pascal分布）NEGBINOMDIST。BINOMDIST(number_s,trials,probability_s,cumulative)返回一元二项式分布的概率值。Number_s为试验成功的次数。Trials为独立试验的次数。Probability_s为每次试验中成功的概率。Cumulative若为0，相当于FALSE,返回为概率函数，若为1，相当于TRUE,返回为概率分布函数。•一元二项式概率密度函数的计算公式为：，相当于(,,)(1)xnxnbxnpppx−⎛⎞=−⎜⎟⎝⎠BINOMDIST（x，n，p，0）•一元二项式累积分布函数的计算公式为：，相当于0(,,)(,,)nxBxnpbxnp==∑BINOMDIST（n，x，p，1）CRITBINOM(trials,probability_s,alpha)返回使累积二项式分布大于等于临界值的最小值。Trials伯努利试验次数。Probability_s每次试验中成功的概率。Alpha临界值。NEGBINOMDIST(number_f,number_s,probability_s)返回负二项式分布。当成功概率为常量probability_s时，函数NEGBINOMDIST返回在到达number_s次成功之前，出现number_f次失败的概率。Number_f失败次数。Number_s成功的极限次数。Probability_s成功的概率。•负二项式（Pascal）分布概率为：，相当于1(;,)(1)1rxxrfxrrpPpr+−⎛⎞+=−⎜⎟−⎝⎠NEGBINOMDIST（x，r，p）例2.1.1某车间有200台车床，它们独立地工作着，开工率各为0.6，开工时耗电为1千瓦，第一问：至少需要120千瓦电力的概率是多少？第二问：供电所至少要供给给这个车间多少电力才能以99.9%的概率保证这个车间不会因供电不足而影响生产。步骤1：“插入”=“函数”=选择常用函数下选择函数中的BINOMDIST=在“函数参数”窗口中，Number_s内输入119，Trials内输入200，Probability_s内输入0.6，Cumulative输入1（或直接输入=BINOMDIST(119,200,0.6,1)），得46.93%步骤2：“插入”=“函数”=选择常用函数下选择函数中的CRITBINOM=在“函数参数”窗口中，Trials内输入200，Probability_s内输入0.6，Alpha输入0.999（或直接输入=CRITBINOM(200,0.6,0.999)），得141至少需要120千瓦电力的概率是1-46.93%=53.07%，对于第二问，用积分极限定理算出的与二项分布算出的一致，即同时开工的车床数不超过141台的概率大于99.9%。例2.1.2某仓库有两个同类型系统，每个系统中都有50个备件，使用时，从两个系统中任取一个，然后从中抽取一个备件。问当第一次取到了一个空系统时，另一个系统中仍有10个备件的概率是多少？步骤：“插入”=“函数”=选择常用函数下选择函数中的NEGBINOMDIST=在“函数参数”窗口中，Number_f内输入40，，Number_s内输入51，Probability_s内输入0.5，（或直接输入=NEGBINOMDIST(40,51,0.5)），得2.41%当第一次取到了一个空系统时，另一个系统中仍有10个备件的概率是2.41%2.1.2对于连续概率分布以正态分布为例，Excel提供的正态分布函数有4个：正态分布函数NORMDIST、标准正态分布函数NORMSDIST、正态分布函数的反函数NORMINV和标准正态分布函数的反函数NORMSINV.NORMDIST(x,mean,standard_dev,cumulative)返回指定平均值和标准偏差的正态分布函数。X为需要计算其分布的数值。Mean分布的算术平均值。Standard_dev分布的标准偏差。如果Mean=0，Standard_dev=1且cumulative=1，则函数NORMDIST返回标准正态分布。�正态累积分布函数计算公式为：，相当于dxexfxx222)(21)(σµσπ−−∞−∫=NORMDIST（x，，，1）µσNORMSDIST(z)返回标准正态累积分布函数，该分布的平均值为0，标准偏差为1。Z为需要计算其分布的数值。�正态累积分布函数计算公式为：，相当于NORMSDIST（Z）221(;0,1)2zfzeπ−=NORMSINV(probability)返回标准正态累积分布函数的反函数。该分布的平均值为0，标准偏差为1。如果已给定概率值，则NORMSINV使用NORMSDIST(z)=probability求解数值z。Probability正态分布的概率值。NORMINV(probability,mean,standard_dev)返回指定平均值和标准偏差的正态累积分布函数的反函数。如果已给定概率值，则NORMINV使用NORMDIST(x,mean,standard_dev,TRUE)=probability求解数值x。Probability正态分布的概率值。Mean分布的算术平均值。Standard_dev分布的标准偏差。例2.1.3假定某支股票的收益率呈正态分布，对应的正态分布的均值为5%，标准差为2%，试确定：（1）收益率为4%对应的概率密度函数值和股票收益率小于等于4%的概率。（2）股票获得收益率80%的可能性不超过某值，求该临界收益率。解：（1）在Excel单元格输入“=NORMDIST(0.04，0.05，0.02，0)”，回车得到收益率为4%对应的概率密度函数值17.60.在另一单元格输入“=NORMDIST(0.04,0.05,0.02,1)”，得到股票收益率小于等于4%的概率为30.85%.(2)在Excel单元格输入“=NORMINV(0.8,0.05,0.02)”，得到临界收益率为6.68%。2.2数字特征随机变量的概率分布函数或概率密度函数完整地描述了随机变量的统计特征。但是，在统计学应用中，往往不易求出随机变量的概率分布或概率密度函数，这时，就要研究随机变量的数字特征。本节主要研究的有数学期望、方差、协方差、相关系数。2.2.1期望与方差Excel只提供离散随机变量的数学期望，AVERAGE是计算算术平均值函数，SUMPRODUCT是成绩求和函数。Excel还提供函数VAR和VARP计算样本和总体的方差。具体函数用法如下：AVERAGE(number1,number2,...)返回参数的平均值（算术平均值）。Number1,number2,...为需要计算平均值的1到30个参数。SUMPRODUCT(array1,array2,array3,...)在给定的几组数组中Array1,array2,array3,...为2到30个数组，，将数组间对应的元素相乘，并返回乘积之和。例如，计算，可以在单元100.590.280.170.160.0550.0500×+×+×+×+×+×+×格B6输入“=SUMPRODUCT(B1:H1,B2:H2)”回车得到8.85.如下图2-1图2-1VAR(number1,number2,...)计算基于给定样本的方差。Number1,number2,...为对应于样本的1到30个参数计算基于给定样本的方差。VAR＝∑=−−niiXXn12)(11例如假设有10件工具在制造过程中是由同一台机器制造出来的，并取样为随机样本进行抗断强度检验。如图2-2如图2-2VARP(number1,number2,...)计算基于样本的另一个方差。Number1,number2,...为对应于样本总体的1到30个参数。VARP＝∑=−niiXXn12)(1例如假设全部10件工具在制造过程中是由同一台机器制造出来的，并取样进行抗断强度检验。如图2-3图2-3例2.2.1福建师范大学数计学院05级数本专业成绩数据（具体数据附表2-1）,计算数理统计成绩的平均值、方差和标准差。步骤1：“插入”=“函数”=选择常用函数下选择函数中的AVERAGE=在“函数参数”窗口参数选择数据区域S4:S204（或直接输入=AVERAGEZ(S4:S204)），得68.37分步骤2：“插入”=“函数”=选择常用函数下选择函数中的VARP=在“函数参数”窗口参数选择数据区域S4:S204（或直接输入=VARP(S4:S204)），得237.43步骤3：“插入”=“函数”=选择常用函数下选择函数中的STDEVP=在“函数参数”窗口参数选择数据区域S4:S204（或直接输入=STDEVP(S4:S204)），得15.41数理统计的平均成绩68.37分，方差为237.43，标准差为15.412.2.2协方差与相关系数�协方差计算公式为11(,)()()niiiCovXYxxyyn==−−∑COVAR(array1,array2)返回协方差。Array1第一个所含数据为整数的单元格区域。Array2第二个所含数据为整数的单元格区域。�相关系数计算公式为,(,)xyxyCovXYρσσ=CORREL(array1,array2)返回相关系数。Array1第一组数值单元格区域。Array2第二组数值单元格区域。例2.2.2（续例2.2.1）数理统计成绩和概率论成绩它们之间是否有关系呢？计算它们的协方差和相关系数。步骤1：“插入”=“函数”=选择常用函数下选择函数中的COVAR=在“函数参数”窗口参数选择数据区域O4:O204和S4:S204（或直接输入=COVAR(O4:O204,S4:S204)），得122.60步骤2：“插入”=“函数”=选择常用函数下选择函数中的CORREL=在“函数参数”窗口参数选择数据区域O4:O204和S4:S204（或直接输入=CORREL(O4:O204,S4:S204)），得0.64数理统计成绩和概率论成绩它们的协方差为122.60，相关系数为0.64，表明数理统计成绩和概率论成绩还是有一定的相关性。3.点估计矩法估计和极大似然估计都是点估计。设是取自正态母体的一个子1,,nεεL2(,)Nµσ样，和未知，。则均值的矩估计和极大似然估计都是µ2σ2{,0}µσΘ=−∞∞µ，方差的矩估计和极大似然估计都是11niinεε==∑2σ2211()nniiSnεε==−∑3.1甲、乙两班各有10名同学参加一场智力测验，其成绩如下表3-1所示。[2]表3-1智力测验成绩表假设各班成绩服从正态分布，试用矩估计和极大似然的方法估计正态分布的均值和方差µ。2σ解:由于和是正态分布的均值和方差，因此样本均值和样本方差既是它们的矩估µ2σ计，又是它们的极大似然估计。求样本均值，在Excel单元格中输入“=AVERAGE(B2:K2)”，回车得到样本均值为86.30在另一个单元格中输入“=AVERAGE(B3:K3)”，回车得到样本均值为91.40求样本方差，在Excel单元格中输入“=VAR(B2:K2)”，回车得到样本方差为123.12，在另一个单元格中输入“=VAR(B3:K3)”，回车得到样本方差为13.60.估计甲班平均成绩86.30