EXCEL于异常数据剔除中的应用 smlai 数据是关于自然、社会现象和科学试验的定量或定性的记录，是科学研究最重要的基础。数据作为研究依赖的基础资源，其质量好坏直接关系到以此为据的正确性和科学性。所谓异常数据就是数据集中与其它数据明显不一致的数据。异常数据的产生原因有很多，可能是数据产生机制内在特性决定的，也可能是抽样调查技术问题；数据采集设备不完善；数据录入及传输错误；测量单位混乱；虚报、瞒报使统计数据失真；丢失数据等人力可控因素造成的。异常数据的出现会极大程度地降低数据的质量，导致统计分析，如参数估计、假设检验、方差分析、相关分析、回归分析、聚类分析等得到的分析结果发生显著变异，使得样本对总体的推断、控制与预测等工作可能会不准确或者出现错误，进一步甚至可能造成宏观决策上的失误，带来不可挽回的损失。因此，异常数据的识别和剔除具有重要意义。异常数据识别有物理识别法和统计识别法。物理识别法是根据人们对客观事物已有的认识，识别由于外界干扰、人为误差等原因造成实测数据偏离正常结果，在实验过程中识别，随时剔除的一种方法。统计识别法是给定一个置信概率，并确定一个置信限，凡超过此限的误差，就认为它不属于随机误差范围，将其视为异常数据剔除的一种方法。当物理识别异常数据不易做到时，一般采用统计识别法。 对于多次重复测定的数据，异常数据常用的统计识别与剔除法有拉依达准则（3σ准则)法、格拉布斯（Grubbs）准则法、狄克逊(Dixon)准则法、肖维勒（Chauvenet）准则法、罗马诺夫斯基（t检验）准则法。拉依达准则法简单，无需查表，用起来方便，测量次数较多或要求不高时用，当测量次数小于或等于10时，拉依达准则法失效。肖维勒准则是经典方法，改善了拉依达准则，过去应用较多，但它没有固定的概率意义，特别是测量数据n无穷大时，该准则失效。狄克逊准则法对数据中只存在一个异常数据时，效果良好，但当数据中异常数据的个数不止一个且出现在同侧时，该方法的检验效果不好，尤其同侧的异常数据很接近时效果更差，易遭受到屏蔽效应。罗马诺夫斯基准则法计算较为复杂。格拉布斯准则和狄克松准则给出了严格的结果，但同样存在狄克逊准则法的缺陷，朱宏等人采用数据的中位数取代平均数，改进得到了更为稳健的处理方法，有效地消除了同侧异常数据的屏蔽效应。国际上常推荐采用格拉布斯（Grubbs）准则法，对此采用改进型格拉布斯准则法。 改进型格拉布斯（Grubbs）准则法具体应用如下： 第 1 页 共 5 页
1、求出拟似异常值。设重复测定数据为（x，x，…，x），其数据个数为n、最大12n值为x、最小值为x、中位数M、标准差为s，则拟似最大异常值G和拟似最小异maxminemax常值G为： minx−MmaxeG= maxsM−xeminG= mins2、查出格拉布斯临界值G。设显著性水平为α（常设α为或（相当于置(α,n)信度为95%或99%）时，则通过表1查出数据个数为n时的格拉布斯临界值G。 (n,α)表1 格拉布斯（Grubbs）临界值G表 (n,α)n α= α= α= n α= α= α= 3 17 4 18 5 19 6 20 7 21 8 22 9 23 10 24 11 25 12 30 13 35 14 40 15 50 16 100 3、异常数据的识别与剔除。比较G和G与G，如果G＞G或G＞maxmin(n,α)max(n,α)minG，则对应的x或x为异常数据，应给与剔除。 (n,α)maxmin4、重复识别与剔除常数数据。对剔除异常数据后的数据重复1～3步骤，直至余下数据的最大值或最小值小于格拉布斯临界值为止。 【例】某啤酒厂，研究开发一种新型的淡色啤酒。经多次重复性测定，该新型淡色啤酒第 2 页 共 5 页
的浊度数据如表2中的A列。判断该重复测定的数据是否存在异常数据并给予剔除。 表2 异常数据EXCEL分析工作表 A B C 1 浊度（EBC） 参数描述 参数值 2 最大值x：｛键入公式：=MAX(A2:A14)｝ max3 最小值x：｛键入公式：=MIN(A2:A14)｝ min4 中位数M：｛键入公式：=MEDIAN (A2:A14)｝ e5 标准差s：｛键入公式：=STDEV (A2:A13)｝ 6 拟似最大异常值G：｛键入公式：=(C2-C4)/C5）｝ max7 拟似最小异常值G：｛键入公式：=(C4-C3)/C5）｝ min格拉布斯临界值G：｛查格拉布斯临界值G表获得｝ 8 (n,)(n,α)9 10 11 12 13 14 EXCEL应用步骤如下： 步骤1：打开EXCEL，按表2格式输入数据并建立工作表，其中数据个数n为13。 步骤2：通过EXCEL提供的MAX函数、MIN函数、MEDIAN函数、STDEV函数，求出数据的最大值、最小值、中位数、标准差。具体求法如下： 1、取单元格C2，键入公式：=MAX(A2:A14)，确认即求得最大值x为； max2、取单元格C3，键入公式：=MIN(A2:A14)，确认即求得最小值x为； min3、取单元格C4，键入公式：=MEDIAN (A2:A14)，确认即求得M中位数为； e4、取单元格C5，键入公式：=STDEV (A2:A13)，确认即求得标准差s为； 步骤3：求出拟似最大异常值G和最小异常值G。具体求求解如下： maxminx−Mmaxe1、取单元格C6，根据公式G=，键入公式：=(C2-C4)/C5，确认即求得maxs第 3 页 共 5 页
拟似最大异常值G为； maxM−xemin2、取单元格C7，根据公式G=，键入公式：=(C4-C3)/C5，确认即求得mins拟似最小异常值G为。 min步骤4：查出格拉布斯临界值G。设显著性水平为α为（显著性水平取值越大，(α,n)数据识别区间越窄，识别要求越严），因为数据的个数n为13，则通过表1查出，格拉布斯临界值G为。初步分析结果列于表5。 (13,)表3 异常数据EXCEL初步分析结果 A B C 1 浊度（EBC） 参数描述 参数值 2 最大值x： max3 最小值x： min4 中位数M： e5 标准差s： 6 拟似最大异常值G： max7 拟似最小异常值G： min格拉布斯临界值G：8 (n,)9 10 11 12 13 14 步骤5：异常数据的识别与剔除。因为G为大于临界值G的，而Gmax(13,)min为小于临界值G的，则当取显著性水平α为（置信度为95%）时，G(13,)max对应的最大值数据（位于表2中的A列第7行），从统计学观点来看应视为异常数据，应给予剔除。 步骤6：重复识别与剔除常数数据。剔除后，对剩余数据经上述步骤重新识别，第 4 页 共 5 页
发现剩余数据的最大值，其G为，还大于G值（这时数据个数n为max(12,)12），应视为异常数据，应给予剔除。剔除后，再剩余数据的最大值，其G为，小于G值的（这时数据个数n为11），数据中已不存在异常数据，结束异(11,)常数据的识别与剔除。 异常数据通常只有少数，如果多了也就不成为异常数据。是否为异常数据，在实际应用中要结合经验来判断。“概率统计理论只是为更细致的分析和更充分利用现有数据提供工具，并为人们准确地判断创造更好的条件，而决不是否定经验判断。”如果数据并不是任何错误所致，而是固有的数据变异性的结果的蕴知性数据，则应高度重视，谨慎对待，不可简单剔除，否则可能丢失一些重要的隐藏信息。 参考文献： [1] 何少华.试验设计与数据处理[M]. 2002:126 [2] 乐立利.观测数据的异常值统计检验方法研究[D].中南大学硕士论文，2008. 第 5 页 共 5 页
