|
哈姆雷特说：“生存还是死亡，这是一个问题”。套用一下，“数据是正态还是非正态，这是一个问题”。而且对于统计学来说，这是一个非常重要的问题！很多统计工具的运用，都是基于数据的正态性。判断数据的正态性，乃是万种统计技术的基石，没有基石，何来高楼！ 正态性检验Minitab中一般采用Anderson-Darling检验（以下简称AD检验），6SQ统计V3.1（以下简称6SQ统计）软件同样如此。 那么Excel是否能简单地进行AD检验呢？答案是肯定的，一切在于您的Excel技能。 当然，重中之重，先得搞清楚AD检验的具体计算方法，以下用实例进行说明。 先看Minitab的帮助文件：
其原理大致上就是，用样本数据的实际累积分布概率，与已知均值和标准差的正态分布的累积分布概率，两相比较，如果其差异小于一个阀值，就是正态分布，反之则非。 具体的算法： Minitab帮助文件中的“方法和公式”，是这样描述的：
让我们来解析一下统计量A2的计算公式： 式中，N是数据个数，i是数据序号。F函数，这里指的是正态分布的累积分布函数(CDF)，Excel中的正态分布累积分布函数是NormDist函数。查看Excel帮助文件中关于NormDist函数的说明：
NormDist函数有四个参数，第1个参数为数据Yi，第2个参数为均值(Mean)，第3个参数为标准偏差(StDev)，第4个参数为累积还是非累积，这里是CDF，取逻辑型常数True。 应用这个函数，首先要计算出均值和标准偏差，这在Excel中很简单，略过。 先将数据从小到大排列，构建序号i，F(Yi)就是第i个数据的累积分布函数，Excel公式为： =NormDist(第i个数据Yi,Mean,StDev,True) F(YN+1-i)，就是倒数第i个数据的累积分布函数，Excel公式为： =NormDist(第N+1-i个数据YN+1-i,Mean,StDev,True) 当i=1时，F(Yi)就是第1个数据的累积分布概率，1- F(YN+1-i)，就是总概率1，减去第N个数据的累积分布概率，也就是最后一个数据在正态分布概率图形上的右尾累积概率。 如果完全符合正态分布，而正态分布的概率密度图形呈倒钟形，左右对称，分位数为0.05处的左侧累积概率，与分位数1-0.05=0.95处的右侧累积概率是相等的。如果不相等，说明与正态分布有所偏离，偏离的程度用一个统计量来表述，这就是AD检验的统计量A2的大致原理。 AD检验的计算过程： 1. 根据A2的计算公式：
计算第i行数据的 (2i-1) × [ Ln(F(Yi)) + Ln(1- F(YN+1-i)) ]，然后累加，再乘以-1/N，再减去N，即可得到A2统计量。 这里，F(Yi)的Excel计算公式上文已述： =NormDist(Yi,Mean,StDev,True) 2. A2统计量进行调整，得到调整了的统计量A2'，公式为： A2' = A2×[ 1 + 0.75/A2 + 2.25/(A2×A2) ] 3. 根据A2'计算p值： A2'≥0.6时： EXP(1.2937 - 5.709 × A2' + 0.0186 × A2' × A2') 这里计算结果如果小于0.001，就记为“<0.001”，不必显示很小的数值。 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函数，表示自然常数e的几次方，例如EXP(2)就是e的平方。 4. 如果p≥0.05(这个临界值一般认为是0.05，也有采用0.10的)，即可认为数据符合正态分布，否则不是正态分布。 6SQ统计和Minitab的AD检验： 实例数据如下： 一组数据，排序，构建序号，并计算F函数如下表：
其中，表中F函数的计算公式：
说明：Small返回数据区域的第n小的数据，Large返回数据区域的第n大的数据，单元格I7和I8分别是数据的均值和标准偏差。采用Small和Large函数在源数据没有排序时也能得到正确的结果。 得到的结果：
具体计算过程请参见附上的Excel文档。 以下是6SQ统计输出的图表
Minitab的输出： 两个软件的检验结果完全相同，但输出的详略有很大区别，6SQ包含直方图和概率图，及详细具体的计算过程，研究一下6SQ统计输出的Excel文档，就可以看出AD检验的步骤。而Minitab只输出一张图，帮助文件中只有A2统计量的算法，并未涉及统计量的调整算法和p值的计算方法。 挑战高难度：三个单元格实现AD检验： 有时候AD检验并不需要输出那么多内容，尽量简单的检验结果也非常有必要。例如SPC控制图工作表的设计，只要有AD检验的结果就可以了。 6SQ统计软件可以只用三个单元格就搞定AD检验，在需要进行AD检验的其它统计功能中，就是这么做的。(这里所说的三个单元格，并未包括用于标识的单元格。) 同样，您如果对Excel操作够熟悉，您也可以！ 三个单元格，对，就三个！Come on。 Step 1：添加工作表名称定义 添加两个工作表的名称定义： SQ_AD，其Refers to为源数据区域。 SQ_AdCnt，数据源为: =COUNT(SQ_AD) “Sorry，添加名称定义，偶不会操作:)”，好吧，这里有个截图说明，我的Exce是英文的2013版本，没办法，将就着看。 还是不会？纳尼，那您还是问度娘吧……
Step 2：输入公式： 第1个单元格D2：用于计算A2统计量 在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)))) 复制这个公式文本到单元格，按F2进入公式编辑状态，再按下Ctrl + Shift + Enter组合键，输入数组公式。不输入数组公式是无法得到正确结果滴。 这个公式看上去很复杂，其实还是根据Minitab帮助文件中的AD统计量的计算方法而来，只是将众多单元格的公式浓缩在一个单元格内。 后面两个单元格就比较简单了，无需输入数组公式。 第2个单元格D3：用于计算调整了的A2统计量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：用于计算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：得到的结果
公式如下：
当然，也可以不定义SQ_ADCnt名称定义，直接用COUNT(SQ_AD)代替即可。 可以发现，三个单元格得到的计算结果，和6SQ统计和Minitab的输出完全相同。 步骤还是太多，您如果会VBA编程，就可以自己设计一个按钮，一键实现AD检验，原理在这里放着，就等您来运用了。
|
|
|
