EXCEL在数学建模中的应用解析

#(一)X2检验例9下表数据来自于CUMCM1999A题——自动化车床管理，问这批数据服从什么样的分布？表5100次刀具故障记录(完成的零件数)45936262454250958443374881550561245243498264074256570659368092665316448773460842811535938445275525137814743888245388626597758597556496975156289547716094029608856102928374736773586386996345555708441660610624841204476545643392802466875397905816217245315125774964684995446457645583787656667632177153108511.数据分析将上述数据排成一列，作描述性统计，得结果如图所示:ABCDE1245213653K552列11585916平均bJU17634标准误差19.6629218654中位数599.519724介数59320553196.629221方差3S663.0322434峰度0.44139823164偏度-0.0111724513区域10592D755最/卜值3426885最大值115327555求和6uuju28564观测数DO29531最大⑴115330373最小⑴3431542置信度(95.0%)39.0154932AR22.数据分组⑴确定分组数k考虑数据的跨度1069^1100,如果分成11组，恰好组距为100,所以取分组数k=11,由此得11个区间为：〈150,(150,250],(250,350],(350,450],(450,550],(550,650],(650,750],(750,850],(850,950],(950,1050],〉1050。⑵统计各组频数，画150接收频率累积%1502.00%5.00%

35049.00%4501019.00%5502039.00%6502463.00%7501578.00%8501290.00%950595.00%1050398.00%12100.00%率频120.00%100.00%80.00%60.00%40.00%20.00%0.00%匚二I频率-■-累积%从直方图可以看出刀具故障记录数据比较接近正态分布。故假设X服从正态分布，其率频120.00%100.00%80.00%60.00%40.00%20.00%0.00%匚二I频率-■-累积%b2=才(X-X)/n，另外一种是样本方差估计b2=S2=才(X-X)/(n-1)。这两iii=1i=1种估计调用函数STDEVP或STDEV来计算，在弹出的对话框内输入数据所在的区域，可得结果。⑶计算各区间的理论频率假如原假设X〜N(映2)成立，则x落入区间(f］的理论概率为p=P{t<X<t}=F(t)-F(t)ii-1iii-1这可用NORMDIST函数来计算，该项函数相当于查正态分布表，它有四个参数：兀,M,b,1，其中x是分布函数的自变量，M是数学期望，此处用估计值600，b是标准差，采用矩法估计量b=195.6436。求得各区间上的理论概率p后，再计算各区间理论频数np。iik(m-np)2⑷计算统计量X2=Eiinpi=1i在新列中利用自定义公式加拖动来计算。⑸根据X2值作出判断X2=£一——X2（k-r-1）,式中的r是被估计参数的个数。本例中k=11,npi=1ir=2，故X2〜X2（8）,查表得X2（8）=15.5073,该值也可用CHINV（0.05,8）=15.50730.05得到，由于X2=4.71647，小于临界值，故接受原假设：总体X服从正态分布。⑹X2值的另一种计算法用函数CHITEST,它需要两列数据，一组是各小区间内样本点的个数，另一组是理论频数。用CHITEST函数时需要两个参数：在Actual_range栏目输入存放各小区间内样本点的个数的数据块的地址索引（如下图中的B2：B12），Expected_range栏目输入第二组数据（理论频数）的直址索引，得到结果是0.90929593°Excel规定的自由度为k-1，即11-1=10.以上结果的含义是：P{X2（10）>X2}=0.90929593，由上式可以求出统计量X2的值，方法是用CHINV函数，它需要两个参数，在Probability栏目输入刚才的结果0.90929593（鼠标点一下刚才的结果即可），在Deg_freedom（自由度）栏目内输入10，得到结果4.716468，这就是统计量X2的值，与前面求得的结果相同.〖注〗本例分组的结果前两个组和最后两个组的数据个数比较少，可以将前两个区间合并成一个区间，将后两个区间也合并成一个区间，此时k=9，检验结果不变。四、回归分析1•一元线性回归工具一数据分析卜回归卜确定回归输出选顶O输出区或（Q）：①新工作表爼二I戒差图辺〕］二I戒差图辺〕］线性拟含图□独差⑥二I标椎玮差①正态分布□正态擾率團①）按要求输完后，点确定即可得回归分析的结果:Ai：ZE?GHZ1r；jii「口口錯一4YultipLcF.D.038^811535RSquareD.001^807926AdjustedR-D.008708172标推误差197.4834541胃观测值in'9LU方差分析LI■：：V：；卜?nifir.anccF回归分析15667.966D.ld5334|J.70^61133B219T23E999.71越15■J.-17Coefficients杯推耘:；7atP^alueLower95%Upper嘲1邛艮95.0^JJ农9L0'<Imereept5T&9113DS372珈勺.0563171.3：E<445D.的557?3.3271450.4955703.327129XVariable-■.-沖刈"-.10094040.3SL226'.'i-r.l-0.161330.232794■n.：r.l^：：n.以•W结果中的项目比较多，其中“回归统计”中的主要项目解释如下：⑴MultipleR：相关系数r，其值越大，越接近1线性关系越显著;⑵RSquare:即r2；⑶标准误差：均方差的估计值方差分析表中的主要项目如下表:表6方差分析表的重要项目df（自由度）SSMSF回归分析fSS/fF值残差fQeQ丿f总计fS-yy1

其中，残差平方和Q=£（y一a一bx）2,冷2=Q（n一2）是总体参数的无偏估计。若eiiei=1记S=S（x一x）2,S=S（y一y）2,S=工（x一x）（y一y），则Q=S一浜x