1、用Excel做數據分析回歸分析2006-12-04 14:02作者：大鳥 原創出處：天極軟件責任編輯：still-在數據分析中，關于成對成組數據的擬合是經常遇到的，涉及到的任務有線性描述，趨勢預測和殘差分析等等。專門多專業讀者遇見此類問題時往往尋求專業軟件，比如在化工中經常用到的Origin和數學中常見的MATLAB等等。它們雖專門專業，但事實上使用Excel就完全夠用了。我們差不多明白在Excel自帶的數據庫中已有線性擬合工具，然而它還稍顯單薄，今天我們來嘗試使用較為專業的擬合工具來對此類數據進行處理。 HYPERLINK /lesson/313/2701313.shtml 點那個地點看專題

2、：用Excel完成專業化數據統計、分析工作 注：本功能需要使用Excel擴展功能，假如您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤支持下加載“分析數據庫”。加載成功后，能夠在“工具”下拉菜單中看到“數據分析”選項實例 某溶液濃度正比對應于色譜儀器中的峰面積，現欲建立不同濃度下對應峰面積的標準曲線以供測試未知樣品的實際濃度。已知8組對應數據，建立標準曲線，同時對此曲線進行評價，給出殘差等分析數據。這是一個專門典型的線性擬合問題，手工計算確實是采納最小二乘法求出擬合直線的待定參數，同時能夠得出R的值，也確實是相關系數的大小。在Excel中，能夠采納先繪圖再添加趨勢線的

3、方法完成前兩步的要求。選擇成對的數據列，將它們使用“X、Y散點圖”制成散點圖。在數據點上單擊右鍵，選擇“添加趨勢線”-“線性”，并在選項標簽中要求給出公式和相關系數等，能夠得到擬合的直線。由圖中可知，擬合的直線是y=15620 x+6606.1，R2的值為0.9994。因為R2 0.99，因此這是一個線性特征特不明顯的實驗模型，即講明擬合直線能夠以大于99.99%地解釋、涵蓋了實測數據，具有專門好的一般性，能夠作為標準工作曲線用于其他未知濃度溶液的測量。為了進一步使用更多的指標來描述這一個模型，我們使用數據分析中的“回歸”工具來詳細分析這組數據。在選項卡中顯然詳細多了，注意選擇X、Y對應的數據

4、列?！俺禐榱恪贝_實是指明該模型是嚴格的正比例模型，本例確實是如此，因為在濃度為零時相應峰面積確信為零。先前得出的回歸方程盡管擬合程度相當高，然而在x=0時，仍然有對應的數值，這顯然是一個可笑的結論。因此我們選擇“常數為零”?！盎貧w”工具為我們提供了三張圖，分不是殘差圖、線性擬合圖和正態概率圖。重點來看殘差圖和線性擬合圖。在線性擬合圖中能夠看到，不但有依照要求生成的數據點，而且還有通過擬和處理的預測數據點，擬合直線的參數會在數據表格中詳細顯示。本實例旨在提供更多信息以起到拋磚引玉的作用，由于涉及到過多的專業術語，請各位讀者依照實際，在具體使用中另行參考各項參數，此不再對更多細節作進一步解釋。殘

5、差圖是有關于世紀之與預測值之間差距的圖表，假如殘差圖中的散點在中州上下兩側零亂分布，那么擬合直線確實是合理的，否則就需要重新處理。更多的信息在生成的表格中，詳細的參數項目完全能夠滿足回歸分析的各項要求。下圖提供的是擬合直線的得回歸分析中方差、標準差等各項信息。用Excel做數據分析相關系數與協方差2006-11-15 05:24作者：大鳥 原創出處：天極軟件責任編輯：still-化學合成實驗中經常需要考察壓力隨溫度的變化情況。某次實驗在兩個不同的反應器中進行同一條件下實驗得到兩組溫度與壓力相關數據，試分析它們與溫度的關聯關系，并對在不同反應器內進行同一條件下反應的可靠性給出依據。 HYPERL

6、INK /lesson/313/2701313.shtml 點那個地點看專題：用Excel完成專業化數據統計、分析工作 相關系數是描述兩個測量值變量之間的離散程度的指標。用于推斷兩個測量值變量的變化是否相關，即，一個變量的較大值是否與另一個變量的較大值相關聯(正相關)；或者一個變量的較小值是否與另一個變量的較大值相關聯(負相關)；依舊兩個變量中的值互不關聯(相關系數近似于零)。設(X,Y)為二元隨機變量，那么:為隨機變量X與Y的相關系數。p是度量隨機變量X與Y之間線性相關緊密程度的數字特征。注:本功能需要使用Excel擴展功能，假如您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”

7、，在安裝光盤中加載“分析數據庫”。加載成功后，能夠在“工具”下拉菜單中看到“數據分析”選項。操作步驟1. 打開原始數據表格，制作本實例的原始數據需要滿足兩組或兩組以上的數據，結果將給出其中任意兩項的相關系數。2. 選擇“工具”-“數據分析”-“描述統計”后，出現屬性設置框，依次選擇:輸入區域:選擇數據區域，注意需要滿足至少兩組數據。假如有數據標志，注意同時勾選下方“標志位于第一行”；分組方式:指示輸入區域中的數據是按行依舊按列考慮，請依照原數據格式選擇；輸出區域能夠選擇本表、新工作表組或是新工作簿；3.點擊“確定”即可看到生成的報表。能夠看到，在相應區域生成了一個33的矩陣，數據項目的交叉處確

8、實是其相關系數。顯然，數據與本身是完全相關的，相關系數在對角線上顯示為1；兩組數據間在矩陣上有兩個位置，它們是相同的，故右上側重復部分不顯示數據。左下側相應位置分不是溫度與壓力A、B和兩組壓力數據間的相關系數。從數據統計結論能夠看出，溫度與壓力A、B的相關性分不達到了0.95和0.94，這講明它們呈現良好的正相關性，而兩組壓力數據間的相關性達到了0.998，這講明在不同反應器內的相同條件下反應一致性專門好，能夠忽略因為更換反應器造成的系統誤差。協方差的統計與相關系數的活的方法相似，統計結果同樣返回一個輸出表和一個矩陣，分不表示每對測量值變量之間的相關系數和協方差。不同之處在于相關系數的取值在

9、-1 和 +1 之間，而協方差沒有限定的取值范圍。相關系數和協方差差不多上描述兩個變量離散程度的指標。用Excel做數據分析抽樣分析工具2006-11-17 05:29作者：大鳥 原創出處：天極軟件責任編輯：still-省教育廳派專家組進行某校檢查學生考試試卷，專家組擬對總體進行抽樣調查，對學校某班的全體同學隨機抽取25名作為調查樣本。為了保證結果的非人為性，采納Excel關心專家組做出抽查的結果。 HYPERLINK /lesson/313/2701313.shtml 點那個地點看專題：用Excel完成專業化數據統計、分析工作 抽樣分析工具以數據源區域為總體，從而為其創建一個樣本。當總體太大

10、而不能進行處理或繪制時，能夠選用具有代表性的樣本。假如確認數據源區域中的數據是周期性的，還能夠對一個周期中特定時刻段中的數值進行采樣。也能夠采納隨機抽樣，滿足用戶保證抽樣的代表性的要求。注：本功能需要使用Excel擴展功能，假如您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤的支持下加載“數據分析庫”。加載成功后，能夠在工具的下拉菜單中看到“數據分析”選項。操作步驟：1. 打開原始數據表格，制作本實例的原始數據無專門要求，只要滿足行或列中為同一屬性數值即可。實例中顯示的是學生學號。2. 選擇“工具”“數據分析”“抽樣”后，出現對話框，依次選擇：輸入區域：把原始總體數據

11、放在此區域中，數據類型不限，數值型或者文本型均可；抽樣方法：有間隔和隨機兩種。間隔抽樣需要輸入周期間隔，輸入區域中位于間隔點處的數值以及此后每一個間隔點處的數值將被復制到輸出列中。當到達輸入區域的末尾時，抽樣將停止。(在本例題中沒有采納)；隨機抽樣是指直接輸入樣本數，電腦自行進行抽樣，不用受間隔的規律限制；樣本數：在此輸入需要在輸出列中顯示需要抽取總體中數據的個數。每個數值是從輸入區域中的隨機位置上抽取出來的，請注意：任何數值都能夠被多次抽取!因此抽樣所得數據實際上會有可能小于所需數量。本文末尾給出了一種處理方法；輸出區域：在此輸入對輸出表左上角單元格的引用。所有數據均將寫在該單元格下方的單列

12、里。假如選擇的是“周期”，則輸出表中數值的個數等于輸入區域中數值的個數除以“間隔”。假如選擇的是“隨機”，則輸出表中數值的個數等于“樣本數”；3.然后單擊確定就能夠顯示結果了(這是電腦自行隨機抽樣的結果)。 HYPERLINK /imagesnew/software/office/06116/1.5.ori.xls 原始數據文檔在那個地點下載 HYPERLINK /imagesnew/software/office/061116/1.5.res.xls 操作結果文檔在那個地點下載 需要講明的情況：由于隨機抽樣時總體中的每個數據都能夠被多次抽取，因此在樣本中的數據一般都會有重復現象，解決此問題有

13、待于程序的完善。能夠使用“篩選”功能對所得數據進行篩選。選中樣本數據列，依次執行“數據”-“篩選”-“高級篩選”，如下圖所示。最后的樣本結果如下圖所示，請您依照經驗適當調整在數據樣本選取時的數量設置，以使最終所得樣本數量許多于所需數量。如有問題能夠發信至： 與我交流。用Excel做數據分析移動平均2006-11-18 06:01作者：大鳥 原創出處：天極軟件責任編輯：still-某化工反應過程，每隔2分鐘對系統測取一次壓力數據。由于反應的專門性，需要考察每8分鐘的壓力平均值，假如該壓力平均值高于15MPa，則認為自屬于該平均值計算范圍內的第一個壓力數據出現時進入反應時期，請使用Excel給出反

14、應時期時刻的區間。 HYPERLINK /lesson/313/2701313.shtml 點那個地點看專題：用Excel完成專業化數據統計、分析工作 移動平均確實是對一系列變化的數據按照指定的數據數量依次求取平均，并以此作為數據變化的趨勢供分析人員參考。移動平均在生活中也不乏見，氣象意義上的四季界定確實是移動平均最好的應用。注:本功能需要使用Excel擴展功能，假如您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤支持下加載“分析數據庫”。加載成功后，能夠在“工具”下拉菜單中看到“數據分析”選項。操作步驟1.打開原始數據表格，制作本實例的原始數據要求單列，請確認數據的

15、類型。本實例為壓力隨時刻變化成對數據，在數據分析時僅采納壓力數據列。需要注意的是，因為平均值的求取需要一定的數據量，那么就要求原始數據量許多于求取平均值的個數，在Excel中規定數據量許多于4。2.選擇“工具”-“數據分析”-“直方圖”后，出現屬性設置框，依次選擇:輸入區域:原始數據區域;假如有數據標簽能夠選擇“標志位于第一行”;輸出區域:移動平均數值顯示區域;間隔:指定使用幾組數據來得出平均值;圖表輸出;原始數據和移動平均數值會以圖表的形式來顯示，以供比較;標準誤差:實際數據與預測數據(移動平均數據)的標準差，用以顯示預測與實際值的差距。數字越小則表明預測情況越好。3.輸入完畢后，則可立即生

16、成相應的數據和圖表。從生成的圖表上能夠看出專門多信息。依照要求，生成的移動平均數值在9:02時差不多達到了15.55MPa，也確實是講，包含本次數據在內的四個數據前就差不多達到了15MPa，那么講明在8分鐘前，也確實是8:56時，系統進入反應時期;采納同樣的分析方法能夠明白，反映時期結束于9:10，反應時期時刻區間為8:56-9:10，共持續14分鐘。單擊其中一個單元格“D6”，能夠看出它是“B3-B6”的平均值，而單元格“E11”則是“SQRT(SUMXMY2(B6:B9,D6:D9)/4)”，它的意思是B6-B9，D6-D9對應數據的差的平方的平均值再取平方根，也確實是數組的標準差。我們差

17、不多明白在Excel自帶的數據庫中已有線性擬合工具，然而它還稍顯單薄，今天我們來嘗試使用較為專業的擬合工具來對此類數據進行處理。 在數據分析中，關于成對成組數據的擬合是經常遇到的，涉及到的任務有線性描述，趨勢預測和殘差分析等等。專門多專業讀者遇見此類問題時往往尋求專業軟件，比如在化工中經常用到的Origin和數學中常見的MATLAB等等。它們雖專門專業，但事實上使用Excel就完全夠用了。我們差不多明白在Excel自帶的數據庫中已有線性擬合工具，然而它還稍顯單薄，今天我們來嘗試使用較為專業的擬合工具來對此類數據進行處理。 注：本功能需要使用Excel擴展功能，假如您的Excel尚未安裝數據分析

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

19、趨勢線”-“線性”，并在選項標簽中要求給出公式和相關系數等，能夠得到擬合的直線。 由圖中可知，擬合的直線是y=15620 x+6606.1，R2的值為0.9994。 因為R2 0.99，因此這是一個線性特征特不明顯的實驗模型，即講明擬合直線能夠以大于99.99%地解釋、涵蓋了實測數據，具有專門好的一般性，能夠作為標準工作曲線用于其他未知濃度溶液的測量。 為了進一步使用更多的指標來描述這一個模型，我們使用數據分析中的“回歸”工具來詳細分析這組數據。 在選項卡中顯然詳細多了，注意選擇X、Y對應的數據列?！俺禐榱恪贝_實是指明該模型是嚴格的正比例模型，本例確實是如此，因為在濃度為零時相應峰面積確信為

20、零。先前得出的回歸方程盡管擬合程度相當高，然而在x=0時，仍然有對應的數值，這顯然是一個可笑的結論。因此我們選擇“常數為零”。 “回歸”工具為我們提供了三張圖，分不是殘差圖、線性擬合圖和正態概率圖。重點來看殘差圖和線性擬合圖。 HYPERLINK /excel/index.htm t _blank 點那個地點查看更多Excel教程在線性擬合圖中能夠看到，不但有依照要求生成的數據點，而且還有通過擬和處理的預測數據點，擬合直線的參數會在數據表格中詳細顯示。本實例旨在提供更多信息以起到拋磚引玉的作用，由于涉及到過多的專業術語，請各位讀者依照實際，在具體使用中另行參考各項參數，此不再對更多細節作進一步

21、解釋。 殘差圖是有關于世紀之與預測值之間差距的圖表，假如殘差圖中的散點在中州上下兩側零亂分布，那么擬合直線確實是合理的，否則就需要重新處理。 更多的信息在生成的表格中，詳細的參數項目完全能夠滿足回歸分析的各項要求。下圖提供的是擬合直線的得回歸分析中方差、標準差等各項信息。 EXCEL的話，剛看了一下，是要先加載宏：在工具菜單下點加載宏，選擇分析工具庫（在該復選框打鉤，點確定）。再點工具菜單發覺多了“數據分析”這一項，點那個，自行選擇分析工具就好了，有方差分析、回歸等等。還能夠直接作圖。用EXCEL函數進行多元線性回歸分析示例 計算柯布-道格拉斯生產 HYPERLINK javascript:;

22、 t _self 函數（Cobb-Douglas ）： lnQ = lnA + a lnL + b lnK 式中Q為產出，L和K分不表示勞動和資本投入量，A表示平均生產技術水平， a和b分不是Q相關于L和K的彈性。使用下表的統計數據， HYPERLINK javascript:; t _self 線性回歸參數A, a, b。ABCD1年份產出投入萬人投入億元21984733.691531.2351.331985985.11701.4376.4419861330.81800.6459.3519871603.611852.5501.6619881959.421899.4565.3719892169.481773.4675.28819901947.581716.7717.5919912284.781783.3792.31019923298.71961.2792.631119935498.352156.7865.521219947684.362448.8906.4813199595052511.91152.3414