建立基于常函数的抗结核药物敏感性试验熟练度测试结果自动比对方法

宋媛媛 赵雁林



·技术交流·

建立基于常函数的抗结核药物敏感性试验熟练度测试结果自动比对方法

宋媛媛 赵雁林

为提高全国结核病实验室抗结核药物敏感性试验(简称“药敏试验”)熟练度测试结果比对的工作效率，杜绝人工比对错误的发生，作者应用Excel 2003软件的数据库函数、逻辑函数、查询和引用函数、统计函数、文本函数及自定义函数，建立自动结果比对方法。本方法可快速得出各实验室药敏试验熟练度测试结果的耐药检出率(%)、敏感检出率(%)、一致率(%)、可重复率(%)、缺失数、缺失对子数等数据，并能在被测结果中以特定颜色显示出错误结果，最终自动汇总各实验室比对结果。

函数/软件； 自动数据处理； 自动分析

中国是全球第二大结核病高负担国家，耐多药结核病患者的估计例数居全球第一位[1]。耐多药结核病的出现和流行已成为结核病防治领域的难点和关注焦点。抗结核药物敏感性试验(简称“药敏试验”)的结果是结核病治疗及控制的重要依据之一，因此保证药敏试验的准确性和可靠性尤为重要。国家结核病参比实验室负责全国结核病网络实验室的质量控制工作，每年会对全国结核病网络实验室下发30株结核分枝杆菌测试菌株，以进行药敏试验熟练度测试工作，测试结果将作为对结核病实验室能力年度考核的依据之一。熟练度测试工作是药敏试验外部质量控制的一个重要途径，对我国药敏试验质量控制工作具有重要意义。

根据《全国结核病防治规划(2011—2015年)》中要求到2015年100%的地市级结核病实验室开展药敏试验工作，我国开展药敏试验的实验室数量越来越多，特别是随着国家对结核病实验室药敏试验质量控制工作的加强，由2008年仅要求32个省级实验室参加药敏试验熟练度测试工作直至2014年已经有41个省级实验室、296个地市级实验室参加，这为测试结果的比对工作增加了很大工作量。原来仅仅通过人工肉眼比对测试结果已经不能满足对大量结果的比对分析。而且人工肉眼比对结果工作量大、费时费力、容易发生错误。亟需有一个熟练度测试结果自动比对方法来实现数据快速比对、汇总、评价。笔者通过前期查阅相关文献和阅读专业书籍，利用Excel 2003软件便可实现这个需求。现以2012年全国各实验室药敏试验熟练度测试结果的数据为资料，以Excel 2003软件为工具，对测试结果进行比对分析。

材料和方法

一、材料

1.计算机配置及配套设备基本要求：1台计算机，要求Intel奔腾4以上处理器，20 GB以上硬盘，256 MB 以上内存，并安装有Windows XP 操作系统、Microsoft Office 2003中文版软件。1台激光打印机[2]。

2.数据资料：2012年全国结核病网络实验室参加抗结核药敏试验熟练度测试结果。

二、方法

(一)录入抗结核药敏试验熟练度测试结果

打开Microsoft Office Excel 2003 程序，将30株结核分枝杆菌菌株的正确药敏试验结果(8种抗结核药物)按顺序录入 Excel表，选取其中1个省级实验室上报的结果按照相同顺序粘贴到同一个Excel表中，用格式刷把字体格式调整相同(图1)。

(二)计算被测实验室结果与标准结果比对后的耐药检出率、敏感检出率、一致率、缺失数

1.建立比对结果表格：在同一个sheet里(sheet1)的W到AE列建立一个新的表格“比对结果”，如图2。

××省疾病预防控制中心测试结果区域空白为测试结果缺失；比对结果区域空白为后续进行函数设置用图2 药敏试验熟练度测试比对结果

2.用数字标记出判定结果与标准结果比对后“相同”或“不同”结果：相同结果指的是被判结果与标准结果比较后相同，记为“0”。不同结果指的是被判结果与标准结果比较后不同，分为3种情况：判定结果为缺失，记为“空格”；判定结果为“S”而标准结果为“R”,记为“1”；判定结果为“R”而标准结果为“S”,记为“2”。

在X3格输入如下计算函数：=IF(OR(N3="",D3=""),"",IF(N3=D3,0,IF(AND(N3="S",D3="R"),1,2)))[3-4],按回车键，鼠标左键点击X3，当变成十字时向右拖拉到AE列，复制单元格，即可显示出Y3：AE3的“相同”或“不同”结果；鼠标左键点击X3：AE3，当变成十字时向下拖拉到第32行，复制单元格，即可显示全部“相同”或“不同”结果。

3.计算“缺失数”：(1)计算“缺失数”方法：由于结核分枝杆菌菌株通过航空或陆路方式运输到各省后再次传代的原因，有可能个别菌株被污染，造成整株菌株的药敏试验结果缺失，或者在进行药敏试验时某一株菌的某一种药物的培养基污染，造成某种药物的药敏试验结果缺失，因此在计算一致率时要减去缺失结果数，也即要先计算判定结果缺失数。在W33格中输入“缺失数”，在X33格输入=countblank(X3:X32), 鼠标左键点击X33，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“缺失数”；(2)计算“缺失数平均值”方法：在AF40格输入“缺失数平均值”，在AF41输入=AVERAGE(X33:AE33)，按回车键，即可得出“缺失数平均值”。

4.计算“耐药错误数”和“敏感错误数”：(1)计算耐药错误数方法：正确结果为耐药(R)，错误判为敏感(S)的个数，即计算标记为“1”的个数。在W34格输入“耐药错误数”，在X34格输入=COUNTIF(X3:X32,1)[5], 按回车键，鼠标左键点击X34格，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“耐药错误数”。(2)计算敏感错误数方法：正确结果为敏感(S)，错误判为耐药(R)的个数，即计算标记为“2”的个数。在W35格输入“敏感错误数”，在X35格输入=COUNTIF(X3:X32,2)[5], 按回车键，鼠标左键点击X35格，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“敏感错误数”。

5.用不同颜色标出“耐药错误”和“敏感错误”结果：耐药错误结果：正确结果为耐药(R)，错误判定为敏感(S)的结果，用橙色标出。敏感错误结果：正确结果为敏感(S)，错误判定为耐药(R)的结果，用浅绿色标出。鼠标左键点击N3格→菜单→格式→条件格式→条件1→公式→=AND(N3<>D3,N3=”S”) →格式→图案→橙色→确定[6]；添加条件2→公式→=AND(N3<>D3,N3=”R”) →格式→图案→浅绿色→确定(图3)。鼠标左键点击N3格，当变成十字时向右拖拉到U列，在右下方自动填充选项那里选择“仅填充格式”； 鼠标左键点击N3：U3，当变成十字时向下拖拉到第32行，在右下方自动填充选项那里选择“仅填充格式”。通过条件格式的设置，便可清晰看出被测试实验室的结果“耐药错误”或“敏感错误”。

图3 显示“耐药错误”和“敏感错误”结果的条件格式设置

6.计算“错误总数”：错误总数=耐药错误数+敏感错误数。在W36格中输入“错误总数”，在X36格输入=X34+X35，按回车键，鼠标左键点击X36格，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“错误总数”。

7.计算“实际报告结果总数”：实际报告结果总数=测试菌株数(30)-缺失数。在W37格输入“实际报告结果总数”，在X37格输入=30-X33,鼠标左键点击X37，按回车键，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“实际报告结果总数”。

8.计算“正确数”：正确数=实际报告结果总数-错误总数。在W38格输入“正确数”，在X38格输入=X37-X36, 按回车键，鼠标左键点击X38，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“正确数”。

粉红色和蓝色背景为后续汇总引用的关键数据；黄色背景为判断重复性的数值图4 药敏试验熟练度测试可重复率

9.计算“耐药检出率”、“敏感检出率”：(1)计算“真耐药数”、“真敏感数”方法：在C33格输入“真耐药数”，在D33格输入=countif(D2:D32,“R”)，按回车键，鼠标左键点击D33，当变成十字时向右拖拉到K列，复制单元格，即可计算E列至K列的“真耐药数”；C34格输入“真敏感数”，在D34格输入=countif(D2:D32,“S”)，按回车键，鼠标左键点击D34，当变成十字时向右拖拉到K列，复制单元格，即可计算E列至K列的“真敏感数”。(2)计算“耐药检出率”、“敏感检出率”方法：耐药检出率=耐药检出数/真耐药数×100%；敏感检出率=敏感检出数/真敏感数×100%。在W39格输入“耐药检出率(%)”, 在X39格输入=(D33-X34)/D33×100%×100(为在单元格内只显示数值，不加百分号，因此乘以100),按回车键，鼠标左键点击X39，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“耐药检出率”；在W40格输入“敏感检出率(%)”, 在X40格输入=(D34-X35)/D34×100%×100,按回车键，鼠标左键点击X40，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“敏感检出率”。

10.计算“一致率(%)”：一致率=(正确数/实际报告结果数)×100%。在W41格输入“一致率(%)”，在X41格输入=X38/X37×100%×100，按回车键，鼠标左键点击X41，当变成十字时向右拖拉到AE列，复制单元格，即可计算Y列至AE列的“一致率(%)”。

(三)计算被测实验室结果的“可重复率(%)”

1.建立计算可重复率表格：在同一个sheet里的AG到AO列建立一个新的表格“可重复率”(图4)。

2.设置重复菌株对子，在表格中标记重复性各种结果：因为在这30株菌株中有10株(对)重复株，所以根据A列和B列编号可知重复菌株编号。为了简便，此步骤可依据“比对结果”表来进行分析，通过相同菌株比对结果相加的形式体现出重复性的好坏(正确、错误或缺失)。如：第一个对子是12和19号菌株，12号所在的行为14和21行。因此在AH3格输入=X14+X21，按回车键。AH3格子中有可能出现表1中的数值。鼠标左键点击AH3格向右拖拉至AO列，即可得出AI3：AO3的重复性好坏。图4中黄色区域数值含义及由来见表1。同上述方法依次设置其他9对菌株的可重复性计算公式，展现重复性好坏。

表1 可重复率表格中黄色区域值含义

3.计算“缺失对子数”：(1)计算“缺失对子数”方法：缺失对子数：成对菌株中缺失1株或2株菌株药敏结果的对子个数，也即计算格中值为“#value!”的个数。在AG13格输入“缺失对子数”，在AH13输入=COUNTIF(AH3:AH12,"#value!")，按回车键，鼠标左键点击AH13向右拖拉到AO列，复制单元格，即可计算AI列至AO列的“缺失对子数”。(2)计算“缺失对子数平均值”方法：在AP15格中输入“缺失对子数平均值”，在AP16格输入=AVERAGE(AH13:AO13)，按回车键，得出“缺失对子数平均值”。

①：INH； ②：Sm； ③：EMB； ④：RFP； ⑤：Km； ⑥：Am； ⑦：Cm； ⑧：Ofx图5 汇总表格截图

4.计算“总对子数”：总对子数：除去缺失对子数，被判结果中的总对子数，总对子数=标准总对子数(10)-缺失对子数。在AG14格输入“总对子数”，在AH14格输入=10-AH13,按回车键，鼠标左键点击AH14向右拖拉到AO列，复制单元格，即可计算AI列至AO列的“总对子数”。

5.计算“正确对子数”：正确对子数：两株相同菌株得出相同结果的个数，也即计算格中值为“0”的个数。在AG15格输入正确对子数，在AH15格输入=COUNTIF(AH3:AH12,"0")，按回车键，鼠标左键点击AH15向右拖拉到AO列，复制单元格，即可计算AI列至AO列的“正确对子数”。

6.计算“可重复率(%)”：可重复率=正确对子数/总对子数×100%。在AG16格输入可重复率(%)，在AH16格输入=(AH15/AH14)×100%×100,按回车键，鼠标左键点击AH16向右拖拉到AO列，复制单元格，即可计算AI列至AO列的“可重复率(%)”。

(四)建立副本表格

在此Excel表中鼠标左键点击sheet1右键→移动或复制工作表→建立副本，这样需要比对多少个实验室结果就建立多少个副本，然后把副本重命名为sheet2、sheet3、sheet4(假设此省有3个地市级实验室参加熟练度测试工作)，在此项工作中为了反馈结果方便，每个省建立一个Excel表，省级实验室结果为sheet1 市级实验室结果依次为sheet2、sheet3、sheet4，当进行下一个实验室结果比对时，直接把下一个实验室结果按照顺序和格式粘贴过来，依照sheet1中模版进行比对就可以方便进行。

(五)汇总表

1.建立汇总表：在进行结果汇总时要把各个实验室的6个指标整合到一个表中，在此先对每个省进行汇总，插入一个新的Excel表sheet5，如图5进行制作表格。

2.引用数据：从sheet1到sheet4中引用数据，在sheet5中进行汇总。(1)引用单位名称方法：在D3格输入=INDIRECT("sheet"&ROW(A1)&"!m1")[7]，按回车键，即出现sheet1中M1格中的“××省疾病预防控制中心测试结果”，鼠标左键点击D3，向下拖拉到D6行，复制单元格，即可出现sheet2到sheet4中M1格的单位名称。(2)引用“一致率”、“缺失数平均值”方法：在E3格中输入=sheet1!X41，按回车键；E4中输入=sheet2!X41，按回车键；在E5中输入=sheet3!X41，按回车键；在E6中输入=sheet4!X41，按回车键。鼠标左键点击E3：E6，当变成十字时向右拖拉到M列，复制单元格，即可出现sheet2到sheet4中的“一致率”和“缺失数平均值”信息。(3)其他判定指标：同(2)引用一致率、“缺失数平均值”中方法依次进行设置。

结 果

一、清楚显示“耐药错误”和“敏感错误”结果

通过“方法(二)中的5”利用条件格式对被判结果的设置，可明显看出被判结果的“耐药错误”或“敏感错误”(图6)。

二、被判结果各指标的获得

通过“方法(二)、(三)”中设置各种计算函数，只需输入被判结果，Excel可立即自动计算出各指标数值(图7，8)。

三、准确汇总数据

通过对方法(五)的应用，可以准确无误的引用sheet1到sheet4表格中的数据(图9)。

讨 论

一、标记“相同”或“不同”结果

文中提到用数字标记出判定结果与标准结果比对后“相同”或“不同”，也可用任意数字或字母、文字进行标记，当用字母或文字标记时，需要加双引号。如用“a”、“b”标记不同结果，则计算函数为：=IF(OR(N3="",D3=""),"",IF(N3=D3,0,IF(AND(N3="S",D3="R"),”a”,”b”)))。但是为了后续计算重复性需要利用此数据进行加和计算，因此建议用数字标记。

二、比对结果出现完全错误问题

在比对过程中可能会遇到比对结果完全错误现象，即比对结果表中显示都为“1”或“2”，但被判结果格式字体都与标准结果相同，原因为被判结果输入时有空格，解决办法是直接在菜单中点击查找和替换，在“查找内容”里输入一个“空格” ，点击全部替换，确定即可。

三、关于计算耐药错误数和敏感错误数的方法

在计算耐药错误数和敏感错误数的过程中，本打算通过计算条件格式中标出的不同颜色来得出个数，但经过查阅大量文献及网络搜索，目前还没有计算条件格式设置成背景色的单元格个数的函数或方法。后来又想把条件格式设置成颜色背景的数据改成另一数据，然后利用countif函数计算，但也没有找到如何更改条件格式数据的方法。因此最后选用了嵌套IF函数的方法在另一表格中实现比对。

四、关于计算被判结果的可重复率(%)

在这个过程中，可以利用if函数嵌套设置来实现，但是由于更复杂，而且用简单的加和方法就能满足要求，最终选择了用加和方法来实现计算。

五、汇总表的数据引用

在汇总表中进行数据引用时，选用了INDIRECT函数和=sheet1!X41这样的设置，通过利用INDIRECT函数，数据可以从上向下的方向拖拉进行复制，通过对表格进行=sheet1!X41的设置，数据可以从左向右的方向拖拉进行复制，其中=sheet1!X41 较简单，但是笔者还没有想出如何设置函数来实现可以同时从上向下和从左向右的方向拖拉进行复制的方法，有待进一步探究。

六、后续数据的整合和报告

粉红色和蓝色背景为后续汇总引用的关键数据图8 被判结果比对后的可重复率(%)表格截图

图9 被判结果比对后的部分汇总表格截图

本研究实现了数据的比对和汇总，但是对于全国32个省的比对数据再汇总成为一个Excel表也是个很大的工作量，笔者目前想到的是利用简单的复制粘贴方法，因为每个省参加的实验室数量不同，每个省Excel表中的sheet表就不同，其他更快捷的方法还有待探究。

后续数据还需要根据本研究中的6个指标给出最终结果：优秀、合格或不合格。采用的方法是进行数据筛选来实现，笔者认为还应该会有更简捷的方法，有待进一步查阅资料来实现。

最终在撰写数据报告的过程还需要制作相应图表，这也需要进一步探索。

七、Excel 2003软件的应用可提高工作效率、杜绝人工比对错误

通过此次对Excel 2003软件很多功能的初步尝试，首次建立了我国药敏试验熟练度测试结果自动比对方法，使得所需各指标简单易得、一目了然，非常快捷、准确、可靠。大大提高了工作效率，并杜绝了人工比对过程中的错误发生。建立一个科学、合理的实验室内部比对数据快速分析方法，能帮助大家及时通过实验室各项数据比对报告中探求实验室检测中存在的问题，最终达到持续提高检测水平、持续提高检测质量的目的[8-9]。因此在实验室工作中，各种需要比对的数据都可参考文中提到的方法进行设置，使工作更便捷。

[1] World Health Organization. Global tuberculosis report 2015. Geneva: World Health Organization,2015.

[2] 周新明，黎绵绵.Excel 2003在实验室内部检验项目比对中的应用.检验医学与临床,2012，22(11)：2885-2886.

[3] 黄永庆，曹芳红.用Execl计算实验室间比对结果的ZB 和ZW 值.海峡预防医学杂志,2009，15(4)：76-77.

[4] 刘羽平,黄大喜.巧用Execl函数自动进行成绩量化处理.电脑学习,2010，3(6)：143-144.

[5] 付宏芳，陈莉.Excel 中常用函数的使用.电脑知识与技术，2010,6(10)：8523-8524.

[6] 谢恒，曾素根，谢元宏，等. 应用Excel条件格式功能自动判断血液分析仪校准和比对结果.现代检验医学杂志，2010，25(1):161.

[7] 李伟, 郭彩虹. Excel函数的妙用.福建电脑,2006,22(2):168.

[8] 肖士海，陈霞，田建梅，等．实验室间室内质量控制数据比对研究．口岸卫生控制，2011，12(2):28-30.

[9] 毛炳寰.用Excel和SPSS学习统计学.北京：中央财经大学，2005.

(本文编辑：范永德)

Establishing constant function based proficiency test results comparison system of anti-TB drug-sensitivity test

SONGYuan-yuan,ZHAOYan-lin.

NationalTuberculosisReferenceLaboratory,NationalCenterforTuberculosisControlandPrevention,ChineseCenterforDiseaseControlandPrevention，Beijing102206,China

ZHAOYan-lin,Email:zhaoyanlin@chinatb.org

To improve the result comparison analysis efficiency of proficiency test for tuberculosis drug sensitivity test (DST) within national laboratory network, and to prevent errors from manual result comparison. Database function, logic functions, lookup and reference functions, statistic functions, text functions and custom functions were used in Excel 2003 software to set up automatic system. Through appropriate Excel 2003 software function setting, we could obtain the resistance detection rate (%), sensitive detection rate (%), concordance rate (%), repeatability rate (%), missing value and so on, and the wrong results can be highlighted with the special colors. And eventually to summarize the laboratory comparison results.

Functions/software； Automatic data processing； Autoanalysis

10.3969/j.issn.1000-6621.2016.09.016

102206 北京，中国疾病预防控制中心结核病预防控制中心 国家结核病参比实验室

赵雁林，Email：zhaoyanlin@chinatb.org

2016-07-14)