利用Excel快速统计CNAS项目信息

/上海市质量监督检验技术研究院

0 引言

根据国际通行的实验室认可管理方法，我国的检测和/或校准技术机构需要根据CNAS-CL01:2006《检测和校准实验室认可准则》（内容等同于ISO/IEC 17025:2005），接受中国合格评定国家认可委员会（CNAS）的实验室认可评审，证明该技术机构进行检测或校准的能力（包括抽样能力）。通过认可的技术机构方可在出具的检测报告或校准证书上使用CNAS标志以及ilac-MRA国际互认联合标志。此外，根据CNAS-RL01《实验室认可规则》的有关规定，CNAS还需要对通过认可的技术机构进行监督评审、复评审、扩大认可范围评审等。

1 问题的提出

检测或校准实验室在接受CNAS评审时，必须要填写附表2《推荐认可的实验室检测能力范围》（以下简称附表2）或附表3《推荐认可的实验室校准能力范围》（以下简称附表3），如表1和表2所示。附表2和附表3都是以项目为序，附表2中涵盖了项目的检测对象、项目及参数（序号和名称）、领域代码、检测标准（方法）名称及编号（含年号）、限制范围和说明等信息；附表3中涵盖了项目的测量仪器名称、校准参量、领域代码、规范代号（含年号）名称、测量范围、扩展不确定度、限制说明等信息。附表2和附表3是一个规范性的文件，是表明该技术机构能力范围的文件。

检测或校准实验室在CNAS评审后，往往需要统计各个专业的项目总数和标准总数。以往，要不是通过电子文档直接统计，要不就是全部打印出来书面统计。对于专业和项目较少的技术机构而言，可以直接在附表2或附表3中进行统计。但是对于专业数较多，项目数上百甚至上千个的技术机构而言，由附表2或附表3中直接统计或打印成稿统计就显得非常麻烦了，主要原因如表3所示。

从表3中可知，问题1到问题4导致一个项目的描述可能会跨页、甚至是跨多页，这样在一页纸或一个屏幕界面中很难看到一个项目的完整信息。问题5导致专业和项目是交错排列的，这样统计同一个专业信息时就需要分段计算后再累计求和。问题4还导致标准信息存在有重复计算的可能性，这样就需要剔除重复统计的标准数。

表1 CNAS附表2的表头

表2 CNAS附表3的表头

表3 利用附表2或附表3存在的问题

因此，无论是电子文档统计还是打印稿统计，都存在有费时、费力、不经济、不环保等问题，而且非常容易出现统计过程中的失误，需要一遍遍地重新统计和验证。笔者通过不断地摸索和验证，找到一种借助Excel函数功能快速统计CNAS专业数和标准数的方法，供同行参考。下文以附表3为例进行说明。

2 统计项目

在附表3中，选取需要统计的某个专业的项目信息，无差别地复制到新的工作表1和工作表2上。

在工作表1中，删除“序号”所在列B外的全部信息。此时，工作表1中的序号不再是连续的。选取序号所在的那一列B，点击菜单栏上的“编辑”，如图1所示。在下拉式菜单中选择“定位”，出现弹出式菜单，如图2所示。点击“定位条件”按钮，出现新的弹出式菜单，如图3所示。选择“空值”选项后确定，则序号所在列B内无内容的行全部都被筛选出来。点击菜单栏“编辑”，在下列式菜单中选择“删除”选项，如图4所示，出现弹出式菜单“删除”，如图5所示。选择“整行”选项后确认，则序号所在列B内无内容的行全部被删除掉。此时，列B内有信息的单元格呈现出连续排列状态。

图1 “编辑”菜单

图2 “定位”菜单

图3 “定位条件”菜单

图4 “编辑”菜单

图5 “删除”菜单

在序号所在列B的左侧新增加一个空白列A。在列B第1个有数字信息的单元格B1的左侧列A单元格A1内输入数字“1”，在列A下一个单元格A2内输入“A1+1”，如图6所示。选定单元格A2，将鼠标移动至A2的右下角，则鼠标指针会变成黑色十字形图标。此时，按住鼠标左键直接下拉至列B最后一个有数字信息的单元格平齐，则列A自动生成了连续的序列号。最后的一个数字就是该专业的项目总数。

另外，利用Excel函数ROW（Reference）返回引用行号的功能，也可以自动获取到项目总数信息。例如，在列B最后一个有数字信息的单元格的右侧列C单元格内输入“=ROW（）”，则在该单元格内直接获得到该专业的项目总数，如图7所示。

图6 用序号统计

图7 用返回引用行号统计

3 统计标准

在工作表2中，删除“规范代号（含年号）名称”所在列B外的全部信息。同样，此时工作表2中的规范代号不是连续的。根据统计项目信息的办法，利用Excel的“定位”和“删除”功能，可以使列B的标准信息行呈现出连续状态。由于一个项目对应的标准可能有多个，也就是说在一个单元格内可能有多个标准信息。根据一个单元格包含有多个标准信息的数量，在该单元格下方新增一个或多个空白行，将标准信息分格保存，保证每一个单元格内只有一条标准信息。

此时，在标准信息统计过程中，特别要注意标准信息的书写方式。在标准信息所在单元格内，最好删除标准代号前、标准代号和标准名称之间的空格。这样利用Excel排序的功能才能使相同的标准信息排列在一起，否则易产生错误。因此，可以先借助 Excel函数 SUBSTITUTE（text, old_text，new_text，instance_num）在文字串中替换指定文本的功能，删除标准所在单元格内的文本之间的所有空格，如图8所示。或借助Excel函数TRIM（text）功能，将标准代号与标准名称之间的连续的多个空格变成一个空格，并去掉文本首尾的空格，如图9所示。

图8 信息书写方式

图9 信息书写方式

选择“规范代号（含年号）名称”所在列B后，点击菜单栏“数据”，在下拉式菜单中点击“排序”，如图10所示，出现排序的弹出式菜单，如图11所示。确认排序规则后，列B的所有标准信息按设定的规则进行了重新排列。此时，标准相同的信息呈现出前后排列的状态。删除相同标准信息所在的行，保证标准信息的唯一性。此后，再利用“A1+1”的Excel自动排序功能，或ROW（Reference）函数，来自动获取到该专业的标准总数。

图10 “数据”菜单

图11 “排序”菜单

4 结语

统计CNAS授权的项目数和标准数，是每一个检测或校准技术机构都必须要面对的工作。本文推荐的利用Excel相关函数的快速统计方法，克服了书面统计和电子文档直接统计带来的诸多问题，不仅提高了统计的准确性，而且显著缩短了统计的时间，为检测或校准实验室以及同行更好地完成项目数和标准数的统计工作提供了一个快速而有效的解决方案。