Excel制表技巧 數據分析

用Excel做數據分析移動平均

某化工反應過程，每隔2分鐘對系統測取一次壓力數據。由于反應的特殊性，需要考察每8分鐘的壓力平均值，如果該壓力平均值高于15MPa，則認為自屬于該平均值計算范圍內的第一個壓力數據出現時進入反應階段，請使用Excel給出反應階段時間的區間。

移動平均就是對一系列變化的數據按照指定的數據數量依次求取平均，并以此作為數據變化的趨勢供分析人員參考。移動平均在生活中也不乏見，氣象意義上的四季界定就是移動平均最好的應用。

注:本功能需要使用Excel擴展功能，如果您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤支持下加載“分析數據庫”。加載成功后，可以在“工具”下拉菜單中看到“數據分析”選項。

操作步驟

1.打開原始數據表格，制作本實例的原始數據要求單列，請確認數據的類型。本實例為壓力隨時間變化成對數據，在數據分析時僅采用壓力數據列.

需要注意的是，因為平均值的求取需要一定的數據量，那么就要求原始數據量不少于求取平均值的個數，在Excel中規定數據量不少于4。

2.選擇“工具”-“數據分析”-“直方圖”后，出現屬性設置框，依次選擇:

輸入區域:原始數據區域;如果有數據標簽可以選擇“標志位于第一行”;

輸出區域:移動平均數值顯示區域;

間隔:指定使用幾組數據來得出平均值;

圖表輸出;原始數據和移動平均數值會以圖表的形式來顯示，以供比較;

標準誤差:實際數據與預測數據(移動平均數據)的標準差，用以顯示預測與實際值的差距。數字越小則表明預測情況越好。

3.輸入完畢后，則可立即生成相應的數據和圖表。

從生成的圖表上可以看出很多信息。

根據要求，生成的移動平均數值在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對應數據的差的平方的平均值再取平方根，也就是數組的標準差。

用Excel做數據分析抽樣分析工具

省教育廳派專家組進行某校檢查學生考試試卷，專家組擬對總體進行抽樣調查，對學校某班的全體同學隨機抽取25名作為調查樣本。為了保證結果的非人為性，采用Excel幫助專家組做出抽查的結果。

抽樣分析工具以數據源區域為總體，從而為其創建一個樣本。當總體太大而不能進行處理或繪制時，可以選用具有代表性的樣本。如果確認數據源區域中的數據是周期性的，還可以對一個周期中特定時間段中的數值進行采樣。也可以采用隨機抽樣，滿足用戶保證抽樣的代表性的要求。

注：本功能需要使用Excel擴展功能，如果您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤的支持下加載“數據分析庫”。加載成功后，可以在工具的下拉菜單中看到“數據分析”選項。

操作步驟：

1.打開原始數據表格，制作本實例的原始數據無特殊要求，只要滿足行或列中為同一屬性數值即可。實例中顯示的是學生學號。

2.選擇“工具”“數據分析”“抽樣”后，出現對話框，依次選擇：

輸入區域：把原始總體數據放在此區域中，數據類型不限，數值型或者文本型均可;

抽樣方法：有間隔和隨機兩種。間隔抽樣需要輸入周期間隔，輸入區域中位于間隔點處的數值以及此后每一個間隔點處的數值將被復制到輸出列中。當到達輸入區域的末尾時，抽樣將停止。(在本例題中沒有采用);隨機抽樣是指直接輸入樣本數，電腦自行進行抽樣，不用受間隔的規律限制;

樣本數：在此輸入需要在輸出列中顯示需要抽取總體中數據的個數。每個數值是從輸入區域中的隨機位置上抽取出來的，請注意：任何數值都可以被多次抽取!所以抽樣所得數據實際上會有可能小于所需數量。本文末尾給出了一種處理方法;

輸出區域：在此輸入對輸出表左上角單元格的引用。所有數據均將寫在該單元格下方的單列里。如果選擇的是“周期”，則輸出表中數值的個數等于輸入區域中數值的個數除以“間隔”。如果選擇的是“隨機”，則輸出表中數值的個數等于“樣本數”;

3.然后單擊確定就可以顯示結果了(這是電腦自行隨機抽樣的結果)。

需要說明的情況：

由于隨機抽樣時總體中的每個數據都可以被多次抽取，所以在樣本中的數據一般都會有重復現象，解決此問題有待于程序的完善。可以使用“篩選”功能對所得數據進行篩選。

選中樣本數據列，依次執行“數據”-“篩選”-“高級篩選”，如下圖所示。

最后的樣本結果如下圖所示，請您根據經驗適當調整在數據樣本選取時的數量設置，以使最終所得樣本數量不少于所需數量。

用Excel做數據分析相關系數與協方差化學合成實驗中經常需要考察壓力隨溫度的變化情況。某次實驗在兩個不同的反應器中進行同一條件下實驗得到兩組溫度與壓力相關數據，試分析它們與溫度的關聯關系，并對在不同反應器內進行同一條件下反應的可靠性給出依據。相關系數是描述兩個測量值變量之間的離散程度的指標。用于判斷兩個測量值變量的變化是否相關，即，一個變量的較大值是否與另一個變量的較大值相關聯(正相關);或者一個變量的較小值是否與另一個變量的較大值相關聯(負相關);還是兩個變量中的值互不關聯(相關系數近似于零)。設(X,Y)為二元隨機變量，那么:

為隨機變量X與Y的相關系數。p是度量隨機變量X與Y之間線性相關密切程度的數字特征。

注:本功能需要使用Excel擴展功能，如果您的Excel尚未安裝數據分析，請依次選擇“工具”-“加載宏”，在安裝光盤中加載“分析數據庫”。加載成功后，可以在“工具”下拉菜單中看到“數據分析”選項。

操作步驟

1.打開原始數據表格，制作本實例的原始數據需要滿足兩組或兩組以上的數據，結果將給出其中任意兩項的相關系數。

2.選擇“工具”-“數據分析”-“描述統計”后，出現屬性設置框，依次選擇:

輸入區域:選擇數據區域，注意需要滿足至少兩組數據。如果有數據標志，注意同時勾選下方“標志位于第一行”;

分組方式:指示輸入區域中的數據是按行還是按列考慮，請根據原數據格式選擇;

輸出區域可以選擇本表、新工作表組或是新工作簿;

3.點擊“確定”即可看到生成的報表。可以看到，在相應區域生成了一個3×3的矩陣，數據項目的交叉處就是其相關系數。顯然，數據與本身是完全相關的，相關系數在對角線上顯示為1;兩組數據間在矩陣上有兩個位置，它們是相同的，故右上側重復部分不顯示數據。左下側相應位置分別是溫度與壓力A、B和兩組壓力數據間的相關系數。

從數據統計結論可以看出，溫度與壓力A、B的相關性分別達到了0.95和0.94，這說明它們呈現良好的正相關性，而兩組壓力數據間的相關性達到了0.998，這說明在不同反應器內的相同條件下反應一致性很好，可以忽略因為更換反應器造成的系統誤差。

協方差的統計與相關系數的活的方法相似，統計結果同樣返回一個輸出表和一個矩陣，分別表示每對測量值變量之間的相關系數和協方差。不同之處在于相關系數的取值在-1和+1

【Excel制表技巧 數據分析】相關文章：

高手玩遺漏數據分析的技巧08-01

數據分析報告07-28

大數據分析07-20

銷售數據的分析方法07-25

大數據分析07-25

多維數據分析方法04-07

數據分析工作職責07-28

數據分析常用方法07-26

數據分析主管的職責01-13

數據分析報告【推薦】03-07