用Excel做數(shù)據(jù)分析

1、用Exce I做數(shù)據(jù)分析我們已經(jīng)知道在Excel自帶的數(shù)據(jù)庫中已有線性擬合工具，但是它還 稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對此類數(shù)據(jù)進 行處理。在數(shù)據(jù)分析中，對于成對成組數(shù)據(jù)的擬合是經(jīng)常遇到的， 涉及到 的任務有線性描述，趨勢預測和殘差分析等等。很多專業(yè)讀者遇見此 類問題時往往尋求專業(yè)軟件，比如在化工中經(jīng)常用到的Origin和數(shù)學中常見的MATLAB等。它們雖很專業(yè)，但其實使用 Excel就完全 夠用了。我們已經(jīng)知道在 Excel自帶的數(shù)據(jù)庫中已有線性擬合工具， 但是它還稍顯單薄，今天我們來嘗試使用較為專業(yè)的擬合工具來對此 類數(shù)據(jù)進行處理。注：本功能需要使用Excel擴展功能，

2、如果您的Excel尚未安裝 數(shù)據(jù)分析，請依次選擇“工具”-“加載宏”，在安裝光盤支持下加 載“分析數(shù)據(jù)庫”。加載成功后，可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項實例 某溶液濃度正比對應于色譜儀器中的峰面積，現(xiàn)欲建立不 同濃度下對應峰面積的標準曲線以供測試未知樣品的實際濃度。已知8組對應數(shù)據(jù)，建立標準曲線，并且對此曲線進行評價，給出殘差等 分析數(shù)據(jù)。這是一個很典型的線性擬合問題，手工計算就是采用最小二乘法 求出擬合直線的待定參數(shù)，同時可以得出R的值，也就是相關(guān)系數(shù)的 大小。在Excel中，可以采用先繪圖再添加趨勢線的方法完成前兩步 的要求。選擇成對的數(shù)據(jù)列，將它們使用“ X、丫散點圖”制成散

3、點圖。1憔虔2U0 1T1加29152 3 :42 37£512蜩門 ：bE12J324M6712.19200和2 253J7S77S4& J9沁刖A I B I用茨向?qū)?4步黑N 1粕未養(yǎng)空斥準英型自毎史類型101：121J141517181920占清岡凰篩|二加口玄7碘啞應誑總勺港怖在數(shù)據(jù)點上單擊右鍵，選擇“添加趨勢線”-“線性”，并在選 項標簽中要求給出公式和相關(guān)系數(shù)等，可以得到擬合的直線KBCD1Ftore200302 22詡財IJSM臨上2 3747025 314l DJ+D55 1£'04:,E伽廬雖臣5比悴辛d(4.51+054 F*Q53 5

4、E+05?83,101+05畳 2 51+05IS些 Z 09 *051 上 E+OSg io n.1718192526272S出4561797M99858生也£3021222324F = 15620r + 舷 I冷:0 9994風屈墾htip :/7'www7vi t < n. c tr：i由圖中可知，擬合的直線是y=15620x+6606.1 ,R2的值為0.9994。因為R2 >0.99，所以這是一個線性特征非常明顯的實驗模型，即說 明擬合直線能夠以大于99.99%地解釋、涵蓋了實測數(shù)據(jù)，具有很好 的一般性，可以作為標準工作曲線用于其他未知濃度溶液的測量。為

5、了進一步使用更多的指標來描述這一個模型， 我們使用數(shù)據(jù)分 析中的“回歸”工具來詳細分析這組數(shù)據(jù)。在選項卡中顯然詳細多了，注意選擇 X、Y對應的數(shù)據(jù)列?！俺?shù)為零”就是指明該模型是嚴格的正比例模型，本例確實是這樣，因為在濃度為零時相應峰面積肯定為零。先前得出的回歸方程雖然擬合 程度相當高，但是在x=0時，仍然有對應的數(shù)值，這顯然是一個可笑 的結(jié)論。所以我們選擇“常數(shù)為零”。回歸”工具為我們提供了三張圖， 分別是殘差圖、線性擬合圖和正態(tài)概率圖。重點來看殘差圖和線性擬合圖在線性擬合圖中可以看到，不但有根據(jù)要求生成的數(shù)據(jù)點，而且 還有經(jīng)過擬和處理的預測數(shù)據(jù)點，擬合直線的參數(shù)會在數(shù)據(jù)表格中詳 細顯示。本

6、實例旨在提供更多信息以起到拋磚引玉的作用， 由于涉及 到過多的專業(yè)術(shù)語，請各位讀者根據(jù)實際，在具體使用中另行參考各 項參數(shù)，此不再對更多細節(jié)作進一步解釋。殘差圖是有關(guān)于世紀之與預測值之間差距的圖表， 如果殘差圖中的散 點在中州上下兩側(cè)零亂分布，那么擬合直線就是合理的，否則就需要 重新處理。1 W產(chǎn)* 2 CJ CE-i®I Q仙o izriMb HE HU售CM3IIFft Liu Fil U9l TP*Z.GW3 '3i級刪H*nt r. ie-fc v<!W2清岡屐堵:HjK fi C GITi更多的信息在生成的表格中，詳細的參數(shù)項目完全可以滿足回歸分析的各項要求。下圖提供的是擬合直線的得回歸分析中方差、 標準差等各項信息DEF|SUHMAKY OUTPUT回歸軌計Multiple R0 999602689R Square099205535Adjus