实验室内精密度中间度量数据的Excel快速分析

开篇：润墨网以专业的文秘视角，为您筛选了一篇实验室内精密度中间度量数据的Excel快速分析范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！

摘要：目的 建立用Excel对实验室内精密度中间度量数据快速进行统计分析的方法。方法 利用excel中数据的"联动"特点以及INDEX（）、VLOOKUP（）等函数，合理布置原始数据、计算数据于工作表的各单元格中，自动多次对原始数据进行格拉布斯/柯克伦检验以剔除离群值，直至无离群值。最终实现对实验室内精密度中间度量数据的快速分析。结果 建立好Excel工作表后，对实验室内精密度中间度量数据进行统计分析时，仅仅录入原始测量数据，不需要再录入任何统计公式和命令，Excel自动对原始数据进行格拉布斯/柯克伦检验。最终可得到实验室内中间精密度SI（）、测量数据范围、离群值个数、歧离值个数等统计分析结论，并对离群值、歧离值作出星号标记。结论 建立的Excel文件能快速进行实验室内精密度中间度量的估计值的统计分析。

关键词：中间精密度；Excel

中国合格评定国家认可委员会于2012年9月13日了一批医学实验室质量和能力认可准则在各专业检验领域的应用说明。其中有3个专业对中间精密度（intermediate precision）提出了要求[1-3]：基因扩增检验项目分析性能标准中的中间精密度

国家标准化管理委员会于2012年11月5日了《GB/T 6379.3-2012/ISO 5725-3：1994测量方法与结果的准确度（正确度与精密度） 第3部分：标准测量方法精密度的中间度量》[4]。为实验室内获得精密度中间度量的估计值提供了模型和方法。我们依此建立了对实验室内精密度中间度量数据进行统计分析的Excel工作表，录入原始数据后立即可得到统计结论，现报道如下。

1统计学原理和材料

GB/T 6379.3-2012提供了两种估计实验室内中间精密度标准差的模型和方法。

1.1最简单的方法 抽取一个样本，对其进行次数为n的重复测量，在不同次测量之间因素发生改变。采用GB/T 6379.2[5]中7.3.4给出的格拉布斯检验剔除离群值。按下式计算中间精密度标准差的估计值：

S■=■

例1：在某实验室内，每天对同一样本测量一次，同样的测量连续进行29 d，得到29个数据，见图1的B列。计算得到某一特定测试水平下"时间"不同的中间精密度标准差SI（T）。

图1 实验室内n次重复的方法中精密度分析

1.2可供选择的方法 在一个实验室内，t组测量，每组测量包括n个重复测试结果。例如每一组测量后改变中间精密度因素，对t组物料进行重复测量，直到每一种物料得到n个测试结果。剔除离群值后按下式计算中间精密度标准差的估计值：

S■=■

例2：在某实验室内，由两个分析员在前后连续的两天，每天由一人对样本进行测试，按此程序，在一个月内得到了29对数据，见图2的B、C两列。计算得到某一特定测试水平下"时间-操作员"不同的中间精密度标准差SI（TO）。

图2 实验室内t组n次重复的方法中间精密度分析

2对实验室内精密度中间度量数据快速分析的Excel实现方法

新建一个Excel文件，命名为"标准测量方法精密度的中间度量"。插入一个工作表，将四个工作表分别重命名为"实验室内1组n次重复的方法"、"实验室内t组n次重复的方法"、"格拉布斯检验的临界值"和"柯克伦检验的临界值"。将GB/T 6379.2中临界值录入对应的工作表中。其余两工作表按以下方法在单元格中录入计算公式。

2.1最简单的方法 打开工作表"实验室内1组n次重复的方法"。

双击E13格，录入=COUNT（B2：B41）。此过程用E13=COUNT（B2：B41）表示，其余格公式录入依此表示如下：

C2=IF（（COUNT（G2）+COUNT（L2）+COUNT（Q2）+COUNT（V2）+COUNT（AA2）+COUNT（AF2））>0，"**"，""）

D2=IF（AG2=1，"*"，""） E15=AVERAGE（B2：B41） E17=STDEV（B2：B41）

G1=VLOOKUP（E13，格拉布斯检验的临界值！$A3：$B40，2）

H1=VLOOKUP（E13，格拉布斯检验的临界值！$A3：$C40，3）

F2=IF（B2""，ABS（B2-E$15）/E$17，""） G2=IF（B2""，IF（F2>G$1，1，""），""） H2=IF（B2""，IF（F2>H$1，1，""），""）

I2=IF（B2""，IF（G2""，""，B2），""）

将C2格至D2格、F2格至I2格的公式向下复制至第41行，选取E1：I41区域，复制。单击J1，粘贴；单击O1，粘贴；单击T1，粘贴；单击Y1，粘贴；单击AD1，粘贴。

E3=IF（COUNTBLANK（AF2：AF41）=40，AD17，"离群值太多，试验失败，优化条件后重试."）

E5=MIN（AC2：AC41） E7=MAX（AC2：AC41） E9=E13-AD13

E11=40-COUNTBLANK（AG2：AG41）

2.2可供选择的方法 打开工作表"实验室内t组n次重复的方法"。

K1=INT（AVERAGE（K3：K42）+0.5） L1=40-COUNTBLANK（L3：L42）

M1=SQRT（SUM（L3：L42）/（K1-1）/L1）

O2=INDEX（柯克伦检验的临界值！$A$1：$K$41，L$1+1，2*K$1-2）

P2=INDEX（柯克伦检验的临界值！$A$1：$K$41，L$1+1，2*K$1-1）

H3 =IF（（COUNT（O3）+COUNT（AD3）+COUNT（AS3）+COUNT（BH3）+

COUNT（BW3）+COUNT（CL3））>0，"**"，""）

I3=IF（CM3=1，"*"，""） K3=IF（B3""，COUNT（B3：G3），""）

L3=IF（B3""，STDEV（B3：G3）^2*（K3-1），""）

M3=IF（B3""，STDEV（B3：G3）^2，""）

N3=IF（B3""，M3/SUM（M$3：M$42），""） O3=IF（N3""，IF（N3>O$2，1，""），""）

P3 =IF（N3""，IF（N3>P$2，1，""），""） Q3=IF（B3""，IF（O3""，""，B3），""）

将Q3格公式向右复复制至V3格；将K3格至V3格的公式向下复制至第42行。选取K1：V42区域，复制。单击Z1，粘贴；单击AO1，粘贴；单击BD1，粘贴；单击BS1，粘贴；单击CH1，粘贴。

J4=IF（COUNTBLANK（CL3：CL42）=40，CJ1，"离群值太多， 试验失败，优化条件后重试."）

J6=MIN（BY3：CD42） J8=MAX（BY3：CD42） J10=L1-CI1

J12=40-COUNTBLANK（CM3：CM42）

3结果

应用实例Excel快速分析如下。

例1：如图1所示，从B2格往下录入29个重复测量的数据，录入完成后立即在E列得到分析结论：试验条件下中间精密度sI（T）为0.00387，测量数据范围从-0.007～0.01，在分析中剔除了2个离群值（见C列的**标记），没有歧离值。

例2：如图2所示，在B列至G列的第三行起往下依次录入第1组、第2组…的数据，每组重复n次（最多可为6次）测量数据录入同一行。录入完成后立即在J列得到分析结论：试验条件下中间精密度sI（TO）为0.00287，测量数据范围从0.03至0.173，在分析中剔除了2个离群值（见H列的**标记），没有歧离值。

以上实例说明实验室内精密度中间度量数据能利用Excel直观快速地得到统计结论。

4讨论

Excel因其单元格内数据独特的"联动"特点而广泛应用于实时数据处理，如R H Taylor等[6]在Excel中编辑公式后计算屈光不正的误差、McCoy AT等[7]以Excel为基础建立了毒代动力学预测模型。徐建平[8]也用Excel对测量方法的重复性与再现性数据进行了分析。对于标准测量方法精密度的中间度量数据的统计分析也以使用Excel较为简捷。

检测数据中潜在的离群值有多种统计判别法，GB/T 6379.3-2012推荐采用格拉布斯检验最简单的方法得到的数据；推荐采用柯克伦检验可供选择的方法得到的数据。杜英 秋[9]和胡修伟等[10]在其应用Excel处理数据的研究报告中也采用这两种统计判别法剔除离群值，但出现离群值后要手动删除数据。我们建立的Excel文件"标准测量方法精密度的中间度量"，将原始数据与最终统计分析结论部署在同一界面，将中间计算数据的单元格隐藏。使用时只需在指定单元格录入原始数据后，立即可得到统计结论。Excel对原始数据进行格拉布斯/柯克伦检验，自动剔除离群值后重新分析，直至无离群值。最终可得到实验室内中间精密度SI（ ）、测量数据范围、离群值个数、歧离值个数等统计分析结论，并对离群值、歧离值作出星号标记，使统计分析变得直观、快速。

参考文献：

[1]CNAS-CL36：2012医学实验室质量和能力认可准则在基因扩增检验领域的应用说明[S].

[2]CNAS-CL38：2012医学实验室质量和能力认可准则在临床化学检验领域的应用说明[S].

[3]CNAS-CL39：2012医学实验室质量和能力认可准则在临床免疫学检验领域的应用说明[S].

[4]GB/T 6379.3-2012，测量方法与结果的准确度（正确度与精密度） 第3部分：标准测量方法精密度的中间度量[S].

[5]GB/T 6379.2-2004，测量方法与结果的准确度（正确度与精密度）第2部分：确定标准测量方法重复性与再现性的基本方法[S].

[6]Taylor R H，Ellingham R B，Subramaniam S，et al. Calculating the error in refractive error[J]. Eye （Lond），2011，25（10）：1333-1336.

[7]McCoy AT， Bartels MJ， Rick DL， et al.TK Modeler version 1.0， a Microsoft？ Excel？-based modeling software for the prediction of diurnal blood/plasma concentration for toxicokinetic use[J]. Regul Toxicol Pharmacol，2012，63（2）：333-343.

[8]徐建平.Excel在确定监测方法重复性与再现性中的运用[J].环境监测管理与技术.2010，22（1）：54-55.

[9]杜英秋.对田间试验数据的统计处理方法--格拉布斯准则和应用EXCEL进行方差分析、多重比较（LSD）[J].中国西部科技，2009，（4）：23-25.

[10]胡修伟，张翠敏，彭霞，等.Excel软件在标准样品定值统计运算中的应用[J].理化检验-化学分册，2010，46（7）：834-836.