|
哈姆雷特說：“生存還是死亡,，這是一個問題”,。套用一下,，“數(shù)據(jù)是正態(tài)還是非正態(tài)，這是一個問題”,。而且對于統(tǒng)計(jì)學(xué)來說,，這是一個非常重要的問題！很多統(tǒng)計(jì)工具的運(yùn)用,，都是基于數(shù)據(jù)的正態(tài)性,。判斷數(shù)據(jù)的正態(tài)性，乃是萬種統(tǒng)計(jì)技術(shù)的基石,，沒有基石,，何來高樓！ 正態(tài)性檢驗(yàn)Minitab中一般采用Anderson-Darling檢驗(yàn)（以下簡稱AD檢驗(yàn)）,，6SQ統(tǒng)計(jì)V3.1（以下簡稱6SQ統(tǒng)計(jì)）軟件同樣如此,。 那么Excel是否能簡單地進(jìn)行AD檢驗(yàn)?zāi)兀看鸢甘强隙ǖ?，一切在于您的Excel技能,。 當(dāng)然，重中之重,，先得搞清楚AD檢驗(yàn)的具體計(jì)算方法,，以下用實(shí)例進(jìn)行說明。 先看Minitab的幫助文件：
其原理大致上就是,，用樣本數(shù)據(jù)的實(shí)際累積分布概率,，與已知均值和標(biāo)準(zhǔn)差的正態(tài)分布的累積分布概率，兩相比較,，如果其差異小于一個閥值,，就是正態(tài)分布，反之則非,。 具體的算法： Minitab幫助文件中的“方法和公式”,，是這樣描述的：
讓我們來解析一下統(tǒng)計(jì)量A2的計(jì)算公式： 式中，N是數(shù)據(jù)個數(shù),，i是數(shù)據(jù)序號,。F函數(shù)，這里指的是正態(tài)分布的累積分布函數(shù)(CDF),，Excel中的正態(tài)分布累積分布函數(shù)是NormDist函數(shù),。查看Excel幫助文件中關(guān)于NormDist函數(shù)的說明：
NormDist函數(shù)有四個參數(shù)，第1個參數(shù)為數(shù)據(jù)Yi,，第2個參數(shù)為均值(Mean),，第3個參數(shù)為標(biāo)準(zhǔn)偏差(StDev)，第4個參數(shù)為累積還是非累積,，這里是CDF,，取邏輯型常數(shù)True,。 應(yīng)用這個函數(shù)，首先要計(jì)算出均值和標(biāo)準(zhǔn)偏差,，這在Excel中很簡單，略過,。 先將數(shù)據(jù)從小到大排列,，構(gòu)建序號i，F(xiàn)(Yi)就是第i個數(shù)據(jù)的累積分布函數(shù),，Excel公式為： =NormDist(第i個數(shù)據(jù)Yi,Mean,StDev,True) F(YN+1-i),，就是倒數(shù)第i個數(shù)據(jù)的累積分布函數(shù)，Excel公式為： =NormDist(第N+1-i個數(shù)據(jù)YN+1-i,Mean,StDev,True) 當(dāng)i=1時,，F(Yi)就是第1個數(shù)據(jù)的累積分布概率,，1- F(YN+1-i)，就是總概率1,，減去第N個數(shù)據(jù)的累積分布概率,，也就是最后一個數(shù)據(jù)在正態(tài)分布概率圖形上的右尾累積概率。 如果完全符合正態(tài)分布,，而正態(tài)分布的概率密度圖形呈倒鐘形,，左右對稱，分位數(shù)為0.05處的左側(cè)累積概率,，與分位數(shù)1-0.05=0.95處的右側(cè)累積概率是相等的,。如果不相等，說明與正態(tài)分布有所偏離,，偏離的程度用一個統(tǒng)計(jì)量來表述,，這就是AD檢驗(yàn)的統(tǒng)計(jì)量A2的大致原理。 AD檢驗(yàn)的計(jì)算過程： 1. 根據(jù)A2的計(jì)算公式：
計(jì)算第i行數(shù)據(jù)的 (2i-1) × [ Ln(F(Yi)) + Ln(1- F(YN+1-i)) ],，然后累加,，再乘以-1/N，再減去N,，即可得到A2統(tǒng)計(jì)量,。 這里，F(xiàn)(Yi)的Excel計(jì)算公式上文已述： =NormDist(Yi,Mean,StDev,True) 2. A2統(tǒng)計(jì)量進(jìn)行調(diào)整,，得到調(diào)整了的統(tǒng)計(jì)量A2',，公式為： A2' = A2×[ 1 + 0.75/A2 + 2.25/(A2×A2) ] 3. 根據(jù)A2'計(jì)算p值： A2'≥0.6時： EXP(1.2937 - 5.709 × A2' + 0.0186 × A2' × A2') 這里計(jì)算結(jié)果如果小于0.001，就記為“<0.001”,，不必顯示很小的數(shù)值,。 A2'≥0.34時： EXP(0.9177 - 4.279 × A2' - 1.38 × A2' × A2') A2'≥0.2時： 1 - EXP(-8.318 + 42.796 × A2' - 59.938 × A2' × A2') A2'＜0.2時： 1 - EXP(-13.436 + 101.14 × A2' - 223.73 × A2' × A2') 注：EXP函數(shù)，表示自然常數(shù)e的幾次方,，例如EXP(2)就是e的平方,。 4. 如果p≥0.05(這個臨界值一般認(rèn)為是0.05,，也有采用0.10的)，即可認(rèn)為數(shù)據(jù)符合正態(tài)分布,，否則不是正態(tài)分布,。 6SQ統(tǒng)計(jì)和Minitab的AD檢驗(yàn)： 實(shí)例數(shù)據(jù)如下： 一組數(shù)據(jù)，排序,，構(gòu)建序號,，并計(jì)算F函數(shù)如下表：
其中，表中F函數(shù)的計(jì)算公式：
說明：Small返回?cái)?shù)據(jù)區(qū)域的第n小的數(shù)據(jù),，Large返回?cái)?shù)據(jù)區(qū)域的第n大的數(shù)據(jù),，單元格I7和I8分別是數(shù)據(jù)的均值和標(biāo)準(zhǔn)偏差。采用Small和Large函數(shù)在源數(shù)據(jù)沒有排序時也能得到正確的結(jié)果,。 得到的結(jié)果：
具體計(jì)算過程請參見附上的Excel文檔,。 以下是6SQ統(tǒng)計(jì)輸出的圖表
Minitab的輸出： 兩個軟件的檢驗(yàn)結(jié)果完全相同，但輸出的詳略有很大區(qū)別,，6SQ包含直方圖和概率圖,，及詳細(xì)具體的計(jì)算過程，研究一下6SQ統(tǒng)計(jì)輸出的Excel文檔,，就可以看出AD檢驗(yàn)的步驟,。而Minitab只輸出一張圖，幫助文件中只有A2統(tǒng)計(jì)量的算法,，并未涉及統(tǒng)計(jì)量的調(diào)整算法和p值的計(jì)算方法,。 挑戰(zhàn)高難度：三個單元格實(shí)現(xiàn)AD檢驗(yàn)： 有時候AD檢驗(yàn)并不需要輸出那么多內(nèi)容，盡量簡單的檢驗(yàn)結(jié)果也非常有必要,。例如SPC控制圖工作表的設(shè)計(jì),，只要有AD檢驗(yàn)的結(jié)果就可以了。 6SQ統(tǒng)計(jì)軟件可以只用三個單元格就搞定AD檢驗(yàn),，在需要進(jìn)行AD檢驗(yàn)的其它統(tǒng)計(jì)功能中,，就是這么做的。(這里所說的三個單元格,，并未包括用于標(biāo)識的單元格,。) 同樣，您如果對Excel操作夠熟悉,，您也可以,！ 三個單元格，對,，就三個,！Come on。 Step 1：添加工作表名稱定義 添加兩個工作表的名稱定義： SQ_AD，其Refers to為源數(shù)據(jù)區(qū)域,。 SQ_AdCnt,，數(shù)據(jù)源為: =COUNT(SQ_AD) “Sorry，添加名稱定義,，偶不會操作:)”,，好吧，這里有個截圖說明,，我的Exce是英文的2013版本,，沒辦法，將就著看,。 還是不會？納尼,，那您還是問度娘吧……
Step 2：輸入公式： 第1個單元格D2：用于計(jì)算A2統(tǒng)計(jì)量 在D2單元格中輸入公式： =-SQ_ADCnt-(1/SQ_ADCnt)*SUM((2*ROW(OFFSET($C$1,,,SQ_ADCnt))-1)*(LN(NORMDIST(SMALL(SQ_AD,ROW(OFFSET($C$1,,,SQ_ADCnt))),AVERAGE(SQ_AD),STDEV(SQ_AD),1))+LN(1-NORMDIST(LARGE(SQ_AD,ROW(OFFSET($C$1,,,SQ_ADCnt))),AVERAGE(SQ_AD),STDEV(SQ_AD),1)))) 復(fù)制這個公式文本到單元格,，按F2進(jìn)入公式編輯狀態(tài)，再按下Ctrl + Shift + Enter組合鍵,，輸入數(shù)組公式,。不輸入數(shù)組公式是無法得到正確結(jié)果滴。 這個公式看上去很復(fù)雜,，其實(shí)還是根據(jù)Minitab幫助文件中的AD統(tǒng)計(jì)量的計(jì)算方法而來,，只是將眾多單元格的公式濃縮在一個單元格內(nèi)。 后面兩個單元格就比較簡單了,，無需輸入數(shù)組公式,。 第2個單元格D3：用于計(jì)算調(diào)整了的A2統(tǒng)計(jì)量A2' 在D3單元格中輸入以下公式： =D2*(1+0.75/COUNT(SQ_AD)+2.25/COUNT(SQ_AD)^2) 或者： =D2*(1+0.75/SQ_ADCnt +2.25/SQ_ADCnt^2) 第3個單元格D4：用于計(jì)算p值 在D4單元格中輸入以下公式： =IF(ISERROR($D$3),'',IF($D$3='','',IF($D$3>=0.6,IF(EXP(1.2937- 5.709 * $D$3 + 0.0186 * $D$3 *$D$3)<0.001,'<0.001',EXP(1.2937 - 5.709 * $D$3 + 0.0186 * $D$3* $D$3)),IF($D$3>=0.34,EXP(0.9177 - 4.279 * $D$3 - 1.38 * $D$3 *$D$3),IF($D$3>=0.2,1 - EXP(-8.318 + 42.796 * $D$3 - 59.938 * $D$3 * $D$3),1- EXP(-13.436 + 101.14 * $D$3 - 223.73 * $D$3 * $D$3)))))) Step 3：得到的結(jié)果
公式如下：
當(dāng)然，也可以不定義SQ_ADCnt名稱定義,，直接用COUNT(SQ_AD)代替即可,。 可以發(fā)現(xiàn)，三個單元格得到的計(jì)算結(jié)果,，和6SQ統(tǒng)計(jì)和Minitab的輸出完全相同,。 步驟還是太多，您如果會VBA編程,，就可以自己設(shè)計(jì)一個按鈕,，一鍵實(shí)現(xiàn)AD檢驗(yàn)，原理在這里放著,，就等您來運(yùn)用了,。
|
|
|
