Excel制表技巧數(shù)據(jù)分析

1、Excel 制表技巧（ 36 ）數(shù)據(jù)分析用 Excel 做數(shù)據(jù)分析移動(dòng)平均 某化工反應(yīng) 過(guò)程，每隔 2 分鐘對(duì)系統(tǒng)測(cè)取一次壓力數(shù)據(jù)。由于反應(yīng)的特 殊性，需要考察每 8 分鐘的壓力平均值，如果該壓力平均值 高于 15MPa ，則認(rèn)為自屬于該平均值計(jì)算范圍內(nèi)的第一個(gè)壓 力數(shù)據(jù)出現(xiàn)時(shí)進(jìn)入反應(yīng)階段，請(qǐng)使用 Excel 給出反應(yīng)階段時(shí) 間的區(qū)間。 移動(dòng)平均就是對(duì)一系列變化的數(shù)據(jù)按 照指定的數(shù)據(jù)數(shù)量依次求取平均，并以此作為數(shù)據(jù)變化的趨 勢(shì)供分析人員參考。移動(dòng)平均在生活中也不乏見，氣象意義 上的四季界定就是移動(dòng)平均最好的應(yīng)用。 注 :本功能需要使用 Excel 擴(kuò)展功能，如果您的 Excel 尚未安裝數(shù)據(jù)分

2、析，請(qǐng)依 次選擇“工具”-“加載宏”在，安裝光盤支持下加載“分析數(shù)據(jù)庫(kù)” 加載成功后，可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項(xiàng)。 操作步驟 1.打開原始數(shù)據(jù)表格， 制作本實(shí)例的原始數(shù)據(jù)要求 單列，請(qǐng)確認(rèn)數(shù)據(jù)的類型。本實(shí)例為壓力隨時(shí)間變化成對(duì)數(shù) 據(jù)，在數(shù)據(jù)分析時(shí)僅采用壓力數(shù)據(jù)列 .需要注意的是， 因?yàn)槠?均值的求取需要一定的數(shù)據(jù)量，那么就要求原始數(shù)據(jù)量不少 于求取平均值的個(gè)數(shù)，在 Excel 中規(guī)定數(shù)據(jù)量不少于 4 。2. 選擇“工具”-“數(shù)據(jù)分析”-“直方圖”后，出現(xiàn)屬性設(shè)置框，依次 選擇 :輸入?yún)^(qū)域 :原始數(shù)據(jù)區(qū)域 ;如果有數(shù)據(jù)標(biāo)簽可以選擇“標(biāo) 志位于第一行” ; 輸出區(qū)域:移動(dòng)平均數(shù)值

3、顯示區(qū)域 ;間隔:指定使用幾組數(shù)據(jù)來(lái)得出平均值 ;圖表輸出 ;原始數(shù)據(jù)和移動(dòng)平均 數(shù)值會(huì)以圖表的形式來(lái)顯示，以供比較;標(biāo)準(zhǔn)誤差 :實(shí)際數(shù)據(jù)與預(yù)測(cè)數(shù)據(jù) (移動(dòng)平均數(shù)據(jù) )的標(biāo)準(zhǔn)差，用以顯示預(yù)測(cè)與實(shí)際 值的差距。數(shù)字越小則表明預(yù)測(cè)情況越好。3. 輸入完畢后，則可立即生成相應(yīng)的數(shù)據(jù)和圖表。 從生成的圖表上 可以看出很多信息。根據(jù)要求，生成的移動(dòng)平均數(shù)值在 9:02 時(shí)已經(jīng)達(dá)到了 15.55MPa ，也就是說(shuō)，包含本次數(shù)據(jù)在內(nèi)的 四個(gè)數(shù)據(jù)前就已經(jīng)達(dá)到了 15MPa ，那么說(shuō)明在 8 分鐘前， 也就是 8:56 時(shí)，系統(tǒng)進(jìn)入反應(yīng)階段 ;采用同樣的分析方法可 以知道，反映階段結(jié)束于 9:10 ，反應(yīng)階段時(shí)

4、間區(qū)間為 8:56-9:10，共持續(xù)14分鐘。單擊其中一個(gè)單元格“D6,可以看出它是“ BB6”的平均值，而單元格“E11 ”則是“SQRT(SUMXMY2(B6:B9,D6:D9)/4) ”，它的意思是 B6-B9 ， D6-D9 對(duì)應(yīng)數(shù)據(jù)的差的平方的平均值再取平方根， 也就是數(shù) 組的標(biāo)準(zhǔn)差。用 Excel 做數(shù)據(jù)分析抽樣分析工具 省教育廳派專家組進(jìn)行某校檢查學(xué)生考試試卷，專家組擬對(duì) 總體進(jìn)行抽樣調(diào)查，對(duì)學(xué)校某班的全體同學(xué)隨機(jī)抽取 25 名 作為調(diào)查樣本。為了保證結(jié)果的非人為性，采用 Excel 幫助 專家組做出抽查的結(jié)果。 抽樣分析工具以數(shù)據(jù)源區(qū)域?yàn)榭?體，從而為其創(chuàng)建一個(gè)樣本。當(dāng)總體太大

5、而不能進(jìn)行處理或 繪制時(shí)，可以選用具有代表性的樣本。如果確認(rèn)數(shù)據(jù)源區(qū)域 中的數(shù)據(jù)是周期性的，還可以對(duì)一個(gè)周期中特定時(shí)間段中的數(shù)值進(jìn)行采樣。也可以采用隨機(jī)抽樣，滿足用戶保證抽樣的 代表性的要求。注：本功能需要使用 Excel 擴(kuò)展功能，如果 您的 Excel 尚未安裝數(shù)據(jù)分析， 請(qǐng)依次選擇“工具”-“加載宏”， 在安裝光盤的支持下加載“數(shù)據(jù)分析庫(kù)”。加載成功后，可以 在工具的下拉菜單中看到“數(shù)據(jù)分析”選項(xiàng)。操作步驟： 1. 打 開原始數(shù)據(jù)表格，制作本實(shí)例的原始數(shù)據(jù)無(wú)特殊要求，只要 滿足行或列中為同一屬性數(shù)值即可。實(shí)例中顯示的是學(xué)生學(xué) 號(hào)。 2. 選擇“工具”“數(shù)據(jù)分析”“抽樣”后，出現(xiàn)對(duì) 話框，

6、依次選擇： 輸入?yún)^(qū)域：把原始總體數(shù)據(jù)放在 此區(qū)域中，數(shù)據(jù)類型不限，數(shù)值型或者文本型均可；抽樣方 法：有間隔和隨機(jī)兩種。間隔抽樣需要輸入周期間隔，輸入 區(qū)域中位于間隔點(diǎn)處的數(shù)值以及此后每一個(gè)間隔點(diǎn)處的數(shù) 值將被復(fù)制到輸出列中。當(dāng)?shù)竭_(dá)輸入?yún)^(qū)域的末尾時(shí)，抽樣將 停止。 （在本例題中沒(méi)有采用 ）；隨機(jī)抽樣是指直接輸入樣本 數(shù)，電腦自行進(jìn)行抽樣，不用受間隔的規(guī)律限制；樣本數(shù)： 在此輸入需要在輸出列中顯示需要抽取總體中數(shù)據(jù)的個(gè)數(shù)。 每個(gè)數(shù)值是從輸入?yún)^(qū)域中的隨機(jī)位置上抽取出來(lái)的，請(qǐng)注意： 任何數(shù)值都可以被多次抽取 !所以抽樣所得數(shù)據(jù)實(shí)際上會(huì)有 可能小于所需數(shù)量。本文末尾給出了一種處理方法；輸出區(qū) 域：在此輸

7、入對(duì)輸出表左上角單元格的引用。所有數(shù)據(jù)均將 寫在該單元格下方的單列里。如果選擇的是“周期”，則輸出 表中數(shù)值的個(gè)數(shù)等于輸入?yún)^(qū)域中數(shù)值的個(gè)數(shù)除以“間隔”。如果選擇的是“隨機(jī)”，則輸出表中數(shù)值的個(gè)數(shù)等于“樣本數(shù)”；3. 然后單擊確定就可以顯示結(jié)果了 （這是電腦自行隨機(jī)抽樣的 結(jié)果 ）。需要說(shuō)明的情況：由于隨機(jī)抽樣時(shí)總體中的每個(gè)數(shù)據(jù)都可以被多次抽取，所以在樣本中的數(shù)據(jù)一般都會(huì) 有重復(fù)現(xiàn)象， 解決此問(wèn)題有待于程序的完善。 可以使用“篩選” 功能對(duì)所得數(shù)據(jù)進(jìn)行篩選。選中樣本數(shù)據(jù)列，依次執(zhí)行“數(shù) 據(jù)”-“篩選-”“高級(jí)篩選”，如下圖所示。最后的樣本結(jié)果如下圖所示，請(qǐng)您根據(jù)經(jīng)驗(yàn)適當(dāng)調(diào)整在數(shù)據(jù)樣本選取時(shí)的 數(shù)

8、量設(shè)置，以使最終所得樣本數(shù)量不少于所需數(shù)量。用 Excel 做數(shù)據(jù)分析相關(guān)系數(shù)與協(xié)方差 化學(xué)合 成實(shí)驗(yàn)中經(jīng)常需要考察壓力隨溫度的變化情況。某次實(shí)驗(yàn)在 兩個(gè)不同的反應(yīng)器中進(jìn)行同一條件下實(shí)驗(yàn)得到兩組溫度與 壓力相關(guān)數(shù)據(jù)，試分析它們與溫度的關(guān)聯(lián)關(guān)系，并對(duì)在不同 反應(yīng)器內(nèi)進(jìn)行同一條件下反應(yīng)的可靠性給出依據(jù)。 相關(guān)系數(shù)是描述兩個(gè)測(cè)量值變量之間的離散程度的指標(biāo)。用 于判斷兩個(gè)測(cè)量值變量的變化是否相關(guān)，即，一個(gè)變量的較 大值是否與另一個(gè)變量的較大值相關(guān)聯(lián)（正相關(guān) ）；或者一個(gè)變量的較小值是否與另一個(gè)變量的較大值相關(guān)聯(lián)（ 負(fù)相關(guān) ）；還是兩個(gè)變量中的值互不關(guān)聯(lián) （相關(guān)系數(shù)近似于零 ）。設(shè) （X,Y） 為二元隨

9、機(jī)變量， 那么 : 為隨機(jī)變量 X 與 Y 的相關(guān) 系數(shù)。 p 是度量隨機(jī)變量 X 與 Y 之間線性相關(guān)密切程度的數(shù) 字特征。注 :本功能需要使用 Excel 擴(kuò)展功能，如果您的 Excel尚未安裝數(shù)據(jù)分析，請(qǐng)依次選擇“工具” -“加載宏”，在安裝光 盤中加載“分析數(shù)據(jù)庫(kù)”。加載成功后， 可以在“工具”下拉菜單 中看到“數(shù)據(jù)分析”選項(xiàng)。操作步驟 1. 打開原始數(shù)據(jù)表格， 制 作本實(shí)例的原始數(shù)據(jù)需要滿足兩組或兩組以上的數(shù)據(jù)，結(jié)果 將給出其中任意兩項(xiàng)的相關(guān)系數(shù)。 2. 選擇“工具”-“數(shù)據(jù)分 析”-“描述統(tǒng)計(jì)”后，出現(xiàn)屬性設(shè)置框，依次選擇 :輸入?yún)^(qū)域 :選 擇數(shù)據(jù)區(qū)域， 注意需要滿足至少兩組數(shù)據(jù)。

10、 如果有數(shù)據(jù)標(biāo)志， 注意同時(shí)勾選下方“標(biāo)志位于第一行”；分組方式 :指示輸入?yún)^(qū) 域中的數(shù)據(jù)是按行還是按列考慮，請(qǐng)根據(jù)原數(shù)據(jù)格式選擇； 輸出區(qū)域可以選擇本表、新工作表組或是新工作簿； 3. 點(diǎn)擊“確定”即可看到生成的報(bào)表?？梢钥吹?，在相應(yīng)區(qū)域生成了一個(gè) 3X3的矩陣，數(shù)據(jù)項(xiàng)目的交叉處就是其相 關(guān)系數(shù)。顯然，數(shù)據(jù)與本身是完全相關(guān)的，相關(guān)系數(shù)在對(duì)角 線上顯示為 1；兩組數(shù)據(jù)間在矩陣上有兩個(gè)位置，它們是相 同的，故右上側(cè)重復(fù)部分不顯示數(shù)據(jù)。左下側(cè)相應(yīng)位置分別 是溫度與壓力 A、B 和兩組壓力數(shù)據(jù)間的相關(guān)系數(shù)。 從數(shù)據(jù)統(tǒng)計(jì)結(jié)論可以看出，溫度與壓力A、B 的相關(guān)性分別達(dá)到了 0.95 和 0.94 ，這說(shuō)

11、明它們呈現(xiàn)良好的正相關(guān)性，而 兩組壓力數(shù)據(jù)間的相關(guān)性達(dá)到了 0.998 ，這說(shuō)明在不同反應(yīng) 器內(nèi)的相同條件下反應(yīng)一致性很好，可以忽略因?yàn)楦鼡Q反應(yīng) 器造成的系統(tǒng)誤差。協(xié)方差的統(tǒng)計(jì)與相關(guān)系數(shù)的活的方法相 似，統(tǒng)計(jì)結(jié)果同樣返回一個(gè)輸出表和一個(gè)矩陣，分別表示每 對(duì)測(cè)量值變量之間的相關(guān)系數(shù)和協(xié)方差。不同之處在于相關(guān) 系數(shù)的取值在 -1 和 +1 之間，而協(xié)方差沒(méi)有限定的取值范 圍。相關(guān)系數(shù)和協(xié)方差都是描述兩個(gè)變量離散程度的指標(biāo)。 用 Excel 做數(shù)據(jù)分析描述統(tǒng)計(jì)某班級(jí)期中考試進(jìn)行后，需要統(tǒng)計(jì)成績(jī)的平均值、 區(qū)間，以及給出班級(jí)內(nèi)部學(xué)生成績(jī)差異的量化標(biāo)準(zhǔn)，借此來(lái) 作為解決班與班之間學(xué)生成績(jī)的參差不齊的依據(jù)

12、。要求得到 標(biāo)準(zhǔn)差等統(tǒng)計(jì)數(shù)值。樣本數(shù)據(jù)分布區(qū)間、標(biāo)準(zhǔn)差等都是描述 樣本數(shù)據(jù)范圍及波動(dòng)大小的統(tǒng)計(jì)量，統(tǒng)計(jì)標(biāo)準(zhǔn)差需要得到樣 本均值， 計(jì)算較為繁瑣。 這些都是描述樣本數(shù)據(jù)的常用變量， 使用 Excel 數(shù)據(jù)分析中的“描述統(tǒng)計(jì)”即可一次完成。注：本 功能需要使用 Excel 擴(kuò)展功能，如果您的 Excel 尚未安裝數(shù) 據(jù)分析，請(qǐng)依次選擇“工具”-“加載宏”，在安裝光盤中加載“分 析數(shù)據(jù)庫(kù)”。加載成功后， 可以在“工具”下拉菜單中看到“數(shù)據(jù) 分析”選項(xiàng)。操作步驟 1.打開原始數(shù)據(jù)表格， 制作本實(shí)例的原 始數(shù)據(jù)無(wú)特殊要求，只要滿足行或列中為同一屬性數(shù)值即可。 2. 選擇“工具”-“數(shù)據(jù)分析”-“描述統(tǒng)計(jì)

13、”后，出現(xiàn)屬性設(shè)置框， 依次選擇：輸入?yún)^(qū)域：原始數(shù)據(jù)區(qū)域， 可以選中多個(gè)行或列， 注意選擇相應(yīng)的分組方式； 如果數(shù)據(jù)有標(biāo)志， 注意勾選“標(biāo)志 位于第一行”；如果輸入?yún)^(qū)域沒(méi)有標(biāo)志項(xiàng)， 該復(fù)選框?qū)⒈磺宄?Excel 將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志；輸出區(qū)域可以選 擇本表、新工作表或是新工作簿； 匯總統(tǒng)計(jì)： 包括有平均值、 標(biāo)準(zhǔn)誤差 (相對(duì)于平均值 ) 、中值、眾數(shù)、標(biāo)準(zhǔn)偏差、方差、 峰值、偏斜度、極差、最小值、最大值、總和、總個(gè)數(shù)、最 大值、最小值和置信度等相關(guān)項(xiàng)目。其中：中值：排序后位 于中間的數(shù)據(jù)的值；眾數(shù)：出現(xiàn)次數(shù)最多的值；峰值：衡量 數(shù)據(jù)分布起伏變化的指標(biāo)，以正態(tài)分布為基準(zhǔn)，比其平緩時(shí)

14、值為正，反之則為負(fù)；偏斜度：衡量數(shù)據(jù)峰值偏移的指數(shù)， 根據(jù)峰值在均值左側(cè)或者右側(cè)分別為正值或負(fù)值；極差：最 大值與最小值的差。第 K 大(小 )值：輸出表的某一行中包含 每個(gè)數(shù)據(jù)區(qū)域中的第 k 個(gè)最大 (小 )值。平均數(shù)置信度：數(shù)值 95% 可用來(lái)計(jì)算在顯著性水平為 5% 時(shí)的平均值置信度。 結(jié)果示例如下 (本實(shí)例演示了雙列數(shù)據(jù)的描述統(tǒng)計(jì)結(jié)果 )： 成績(jī) 學(xué)習(xí)時(shí)間 平均 78.64285714 平均 62.91428571 標(biāo)準(zhǔn)誤差 2.408241878 標(biāo)準(zhǔn)誤差 1.926593502 中位數(shù) 85 中位數(shù) 68 眾數(shù) 98 眾數(shù) 78.4 標(biāo)準(zhǔn)差 18.02163202 標(biāo)準(zhǔn)差 14.41730562 方差 324.7792208 方差 207.8587013