EXCEL在數(shù)學(xué)建模中的應(yīng)用解析

#(一)X2檢驗(yàn)例9下表數(shù)據(jù)來自于CUMCM1999A題——自動化車床管理，問這批數(shù)據(jù)服從什么樣的分布？表5100次刀具故障記錄(完成的零件數(shù))45936262454250958443374881550561245243498264074256570659368092665316448773460842811535938445275525137814743888245388626597758597556496975156289547716094029608856102928374736773586386996345555708441660610624841204476545643392802466875397905816217245315125774964684995446457645583787656667632177153108511.數(shù)據(jù)分析將上述數(shù)據(jù)排成一列，作描述性統(tǒng)計(jì)，得結(jié)果如圖所示:ABCDE1245213653K552列11585916平均bJU17634標(biāo)準(zhǔn)誤差19.6629218654中位數(shù)599.519724介數(shù)59320553196.629221方差3S663.0322434峰度0.44139823164偏度-0.0111724513區(qū)域10592D755最/卜值3426885最大值115327555求和6uuju28564觀測數(shù)DO29531最大⑴115330373最?、?431542置信度(95.0%)39.0154932AR22.數(shù)據(jù)分組⑴確定分組數(shù)k考慮數(shù)據(jù)的跨度1069^1100,如果分成11組，恰好組距為100,所以取分組數(shù)k=11,由此得11個(gè)區(qū)間為：〈150,(150,250],(250,350],(350,450],(450,550],(550,650],(650,750],(750,850],(850,950],(950,1050],〉1050。⑵統(tǒng)計(jì)各組頻數(shù)，畫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頻率-■-累積%從直方圖可以看出刀具故障記錄數(shù)據(jù)比較接近正態(tài)分布。故假設(shè)X服從正態(tài)分布，其率頻120.00%100.00%80.00%60.00%40.00%20.00%0.00%匚二I頻率-■-累積%b2=才(X-X)/n，另外一種是樣本方差估計(jì)b2=S2=才(X-X)/(n-1)。這兩iii=1i=1種估計(jì)調(diào)用函數(shù)STDEVP或STDEV來計(jì)算，在彈出的對話框內(nèi)輸入數(shù)據(jù)所在的區(qū)域，可得結(jié)果。⑶計(jì)算各區(qū)間的理論頻率假如原假設(shè)X?N(映2)成立，則x落入?yún)^(qū)間(f］的理論概率為p=P{t<X<t}=F(t)-F(t)ii-1iii-1這可用NORMDIST函數(shù)來計(jì)算，該項(xiàng)函數(shù)相當(dāng)于查正態(tài)分布表，它有四個(gè)參數(shù)：兀,M,b,1，其中x是分布函數(shù)的自變量，M是數(shù)學(xué)期望，此處用估計(jì)值600，b是標(biāo)準(zhǔn)差，采用矩法估計(jì)量b=195.6436。求得各區(qū)間上的理論概率p后，再計(jì)算各區(qū)間理論頻數(shù)np。iik(m-np)2⑷計(jì)算統(tǒng)計(jì)量X2=Eiinpi=1i在新列中利用自定義公式加拖動來計(jì)算。⑸根據(jù)X2值作出判斷X2=￡一——X2（k-r-1）,式中的r是被估計(jì)參數(shù)的個(gè)數(shù)。本例中k=11,npi=1ir=2，故X2?X2（8）,查表得X2（8）=15.5073,該值也可用CHINV（0.05,8）=15.50730.05得到，由于X2=4.71647，小于臨界值，故接受原假設(shè)：總體X服從正態(tài)分布。⑹X2值的另一種計(jì)算法用函數(shù)CHITEST,它需要兩列數(shù)據(jù)，一組是各小區(qū)間內(nèi)樣本點(diǎn)的個(gè)數(shù)，另一組是理論頻數(shù)。用CHITEST函數(shù)時(shí)需要兩個(gè)參數(shù)：在Actual_range欄目輸入存放各小區(qū)間內(nèi)樣本點(diǎn)的個(gè)數(shù)的數(shù)據(jù)塊的地址索引（如下圖中的B2：B12），Expected_range欄目輸入第二組數(shù)據(jù)（理論頻數(shù)）的直址索引，得到結(jié)果是0.90929593°Excel規(guī)定的自由度為k-1，即11-1=10.以上結(jié)果的含義是：P{X2（10）>X2}=0.90929593，由上式可以求出統(tǒng)計(jì)量X2的值，方法是用CHINV函數(shù)，它需要兩個(gè)參數(shù)，在Probability欄目輸入剛才的結(jié)果0.90929593（鼠標(biāo)點(diǎn)一下剛才的結(jié)果即可），在Deg_freedom（自由度）欄目內(nèi)輸入10，得到結(jié)果4.716468，這就是統(tǒng)計(jì)量X2的值，與前面求得的結(jié)果相同.〖注〗本例分組的結(jié)果前兩個(gè)組和最后兩個(gè)組的數(shù)據(jù)個(gè)數(shù)比較少，可以將前兩個(gè)區(qū)間合并成一個(gè)區(qū)間，將后兩個(gè)區(qū)間也合并成一個(gè)區(qū)間，此時(shí)k=9，檢驗(yàn)結(jié)果不變。四、回歸分析1?一元線性回歸工具一數(shù)據(jù)分析卜回歸卜確定回歸輸出選頂O輸出區(qū)或（Q）：①新工作表爼二I戒差圖辺〕］二I戒差圖辺〕］線性擬含圖□獨(dú)差⑥二I標(biāo)椎瑋差①正態(tài)分布□正態(tài)擾率團(tuán)①）按要求輸完后，點(diǎn)確定即可得回歸分析的結(jié)果:Ai：ZE?GHZ1r；jii「口口錯一4YultipLcF.D.038^811535RSquareD.001^807926AdjustedR-D.008708172標(biāo)推誤差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農(nóng)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結(jié)果中的項(xiàng)目比較多，其中“回歸統(tǒng)計(jì)”中的主要項(xiàng)目解釋如下：⑴MultipleR：相關(guān)系數(shù)r，其值越大，越接近1線性關(guān)系越顯著;⑵RSquare:即r2；⑶標(biāo)準(zhǔn)誤差：均方差的估計(jì)值方差分析表中的主要項(xiàng)目如下表:表6方差分析表的重要項(xiàng)目df（自由度）SSMSF回歸分析fSS/fF值殘差fQeQ丿f總計(jì)fS-yy1

其中，殘差平方和Q=￡（y一a一bx）2,冷2=Q（n一2）是總體參數(shù)的無偏估計(jì)。若eiiei=1記S=S（x一x）2,S=S（y一y）2,S=工（x一x）（y一y），則Q=S一浜x