Excel在高锰酸钾消耗量的测定电子原始记录中的应用

捍卫生活品质 推动产业升级探讨
Excel在高锰酸钾消耗量的测定电子原始记录中的
应用
石鎏杰 施均 韦存茜
（上海市质量监督检验技术研究院，上海 201114）
摘要：针对目前高锰酸钾消耗量测定过程中检测原始记录采用手工书写，导致工作效率低、出错率较高、调阅不便等问题，提出电子原始记录的设计思路和实现方法。

在深入分析数据修约规则的基础上，采用Excel中的IF、
ROUND、TRUNC、MOD函数，组合嵌套实现自动计算及计算结果的标准化修约。

通过设计电子原始记录模板，
建立逻辑关联，实现原始记录的自动生成。

该方法完全满足GB/T 8170-2008对数值修约的特殊要求，可将测量数
据录入后直接完成计算、修约，并生成电子原始记录，可以大大减轻检测人员的工作量，提高工作效率，有效提升
实验室原始记录的自动化和信息化水平。

关键词：Excel软件；高锰酸钾消耗量测定；电子原始记录
中图分类号： TS206.4；TS761.7 文献标识码：A DOI：10.19541/ki.issn 1004-4108.2021.03.032
高锰酸钾消耗量是指从试样中迁移到浸泡液中的可被高锰酸钾氧化的物质的总量, 可以反映试样中可迁移出并能被氧化的水溶性有机物的总和，是食品包装安全评估的重要质量参数[1,2]。

在目前的检测工作中，原始记录基本采用手工书写模式。

一方面，原始记录的书写、整理、校核需要耗费大量的时间，计算繁琐，数据的正确性和规范性难以保证，影响工作效率。

另一方面，纸质原始记录在保管、调阅、查询等方面都极为不便。

在当前广泛推广信息化管理的形势下，原始记录实现电子化已经成为一种必然[3,4]。

Excel软件是一款通用的、容易掌握的数据处理与分析软件，在环境监测、水文监测以及其他行业的数据处理中得到广泛应用[5]。

Excel是按照“四舍五入”这个最基本的规则来保留数据计算位数的，而在高锰酸钾消耗量的测定中，要求按照GB/T 8170-2008《数值修约规则与极限数值的表示和判定》对结果进行 “四舍六入五考虑”的处理，所以常常会出现人工计算与计算机计算结果不符的现象。

因此，利用Excel软件实现数据的标准化修约，是高锰酸钾消耗量的测定原始记录电子化过程中的关键环节。

本文在深入分析数据修约规则的基础上，提出了一种利用Excel自带函数组合嵌套的方法，实现检测结果的全自动标准化修约，并同步生成原始记录，以期降低出错率、减轻检测人员的工作量、提高工作效率。

1 数据修约规则
图1 数据修约的逻辑关系
高锰酸钾消耗量的结果修约过程遵循GB/T 8170-2008数值修约规则[6]，如图1所示，具体要求为：（1）拟舍弃数字的最
下转第121页
左一位数字小于5，则舍去，保留其余各位数字不变；（2）拟舍弃数字的最左一位数字大于5，则进一，即保留数字的末尾数字加1；（3）拟舍弃数字的最左一位数字是5，且其后有非零数字时进一，即保留数字的末位数字加1；（4）拟舍弃数字的最左一位数字为5，且其后无非零数字时，若所保留的末位数字为奇数（1、3、5、7、9），则进一，即保留数字的末位数字加1；
若所保留的末位数字为偶数（0、2、4、6、8），则舍去[7]。

此外，根据GB 31604.2-2016《食品安全国家标准食品接
触材料及制品高锰酸钾消耗量的测定》的要求，高锰酸钾消耗量的结果需保留两位有效数字[8]。

因此，高锰酸钾消耗量的结
果修约需综合考虑有效数字位数和标准化修约规则。

2 需调用的函数及其功能
利用Excel 软件实现数据标准化修约主要有两种方法：（1）
应用VBA 宏；（2）利用Excel 函数组合嵌套。

由于VBA 需要分析人员具备较高的程序编写能力，其推广应用受到限制；
而通过Excel 函数的组合嵌套，实现数据的标准化修约，对分析人员的编程能力要求较低，更具推广潜力。

本文利用Excel 软件自带的函数组合嵌套实现标准化修约，
过程中用到的函数有：IF（）、ROUND（）、TRUNC（）、MOD（），其功能及使用规则如表1所示。

3 高锰酸钾消耗量的结果标准化修约的嵌套函数设计
高锰酸钾消耗量的计算分为两个步骤：
（1）两个平行样的实测结果计算，保留三位有效数字，此时第四位有效数字开始均为“舍”。

此步骤用Excel 中的函数表示为：=TRUNC(A,n)，其中A 为被修约的数据，n 表示拟保留的小数点后位数。

高锰酸钾消耗量两个平行样的实测结果不仅需要保留三位
有效数字，还涉及方法检出限的问题，当结果小于检出限时，原始记录中最终应当显示“<X”（X 表示检出限值），因此将高锰酸钾消耗量两个平行样的实测结果的修约过程用Excel 中
的嵌套函数表示为：
=IF(A<X,"<X",IF(A<1,TRUNC(A,3),IF(A<10,TRUNC(A,
2),IF(A<100,TRUNC(A,1),IF(A<1000,TRUNC(A,0),(TRUNC (A,-1)))))))，其中A 为被修约的数据，X 表示检出限值。

（2）高锰酸钾消耗量的平均值计算，即计算两个平行样
实测结果的平均值，保留两位有效数字，此时按照“四舍六入五考虑”的标准化修约规则进行处理。

根据GB/T 8170-2008的修约规则，可将数值修约程序用
Excel 中的嵌套函数进行表示为：
=IF(MOD(B*10^m,2)=0.5,TRUNC(B,m),ROUND(B,m))，其中B 为被修约的数据，m 表示拟保留的小数点后位数。

因此将高锰酸钾消耗量的结果的修约过程用Excel 中的嵌套函数表示为：
=IF(B="<X","<X",IF(B<1,IF(MOD(B*10^2,2)=0.5,TRU
NC(B,2),ROUND(B,2)),IF(B<10,IF(MOD(B*10^1,2)=0.5,TR
UNC(B,1),ROUND(B,1)),IF(B<100,IF(MOD(B*10^0,2)=0.5, TRUNC(B,0),ROUND(B,0)),IF(B<1000,IF(MOD(B*10^-1,2)=0.5,TRUNC(B,-1),ROUND(B,-1)),B)))))，其中B 为被修约的数据，X 表示检出限值。

4 高锰酸钾消耗量的测定电子原始记录设计
根据GB 31604.2-2016的规定，高锰酸钾消耗量的计算对
于不同属性的产品需采用不同的计算公式、不同的单位和判定值。

因此，实现样品属性与计算公式、单位和判定值的逻辑关联，
并在模板中相应位置呈现样品信息、试验条件计算结果等内容是本设计的核心环节。

整个软件包含6个工作表[11]：
（1）主页：开始检测前，录入样品信息，包括报告编号、
样品名称、样品属性、检验依据、迁移试验条件、计算公式等。

表1 涉及函数的用法及含义
[9]
54+57.
[4] 杨启航. 实物资产标准化管理的问题及对策[J]. 中国标准化, 2021(04): 55-57.
[5] 丛日升. 一种新的客制化空调箱机组参数化设计方法的应用[J]. 智能制造, 2020(12): 40-43.
[6] 翁雷. T 地产公司产品标准化体系实施效果及优化策略[D]. 杭州: 浙江工商大学, 2020.
[7] 王赵琳.客制化“特权”助力提升客户忠诚度[J]. 中国信用卡, 2019(11): 30-33.
[8] 李鸿基, 徐新悦, 陈沛丰. 基于工业互联网的制造业客制化[J]. 汽车零部件,
2019(08): 104-106.
[9] 薛小刚. 产品标准化在定制家具项目管理中的应用研究——以A 公司定制产品开发为例[J]. 现代商贸工业, 2019, 40(18): 180-181.
[10] 王苗. 客制化理念生产预混料1对1模式服务养殖户——访河南普丹美饲料有限公司研发总监兼人力资源总监陶位云[J]. 猪业科学, 2018, 35(10): 72-73. [11] 引领客制化新模式 完美呈现中丹技术——访湖南普爱生物饲料有限公司总经理罗海辉[J]. 湖南饲料, 2017(05): 3+12.
[12] 闫建华. 产品标准化在企业生产中的作用[J]. 中国标准化, 2017(14): 80-81.
[13] 王德生. 浅谈新时期商品客制化生产的若干问题[J]. 科技展望, 2016, 26(32): 275.
（2）面积体积页：检测过程中，录入样品的容量、面积、浸泡液的体积等数据。

（3）称重页：检测过程中，天平联机录入称量数据。

（4）映射页：此页为数据过渡页，很多情况下，上述数
据不方便直接形成打印页需要的样式，需要在此页过渡后再传送到“原始记录页”。

通过将“主页”、“面积体积页”、“称
重页”采集到的数据进行处理，完成自动计算、修约、判定等。

最后将采集到的原始数据以及此页处理得到的数据传送到“原始记录页”对应内容上。

（5）原始记录页：此页为电子原始记录模板，与“主页”、
“面积体积页”、“称重页”、“映射页”逻辑关联的数据自动填入，生成电子原始记录。

（6）历史记录页：历史记录是质量体系对原始记录的基本
要求，为实现这一功能，运用了Excel 的审阅功能，以查询操作时间、操作人、操作区域、操作内容等信息。

5 结束语
本文针对目前食品接触材料高锰酸钾消耗量测定工作中人
工数值修约和手工誊写原始记录出错率高、工作量大等种种弊端，按照GB/T 8170-2008数值修约国家标准的要求，设计了一款基于Excel 的自动计算修约并同步生成原始记录的软件。

检测人员在进行检测工作时，通过自动或手动的方式录入数据，实现测定结果自动计算、修约并同步生成电子原始记录。

本设计不仅大大提高了数据记录和计算的准确性，还将原始记录同
步生成，大大减轻了检测人员的工作量，提高了工作效率，具有极大的推广价值。

参考文献
[1] 石鎏杰, 韦存茜, 施均. 涂漆筷子中高锰酸钾消耗量的风险调查[J]. 食品安全质量检测学报, 2020, 11(04): 1086-1089.
[2] 杨建平, 李文慧, 张丽媛, 等. 食品接触材料中高锰酸钾消耗量的影响因素研究[J]. 食品安全质量检测学报, 2019, 10(21): 7158-7162.
[3] 李宏州, 莫国福.计量工作原始记录无纸化的实现[J]. 轻工标准与质量, 2019(03): 60-61+63.
[4] 李貌, 苏红雨, 冉庆, 等. 基于计算机语言编程平台的自动数值修约生成原始记录方法研究[J]. 中国测试, 2016, 42(S2): 73-75.
[5] 张然, 吕学研, 刘雷, 等. 环境监测数据标准化修约的Excel 函数实现[J]. 四川环境, 2019, 38(01): 85-91.
[6] GB/T 8170-2008, 数值修约规则与极限数值的表示和判定[S].
[7] 蒋帆, 刘一, 李业. 一种利用Excel 实现硬度计量数据修约的方法[J]. 上海计量测试, 2019, 46(04): 60-61+65.
[8] GB 31604.2-2016, 食品安全国家标准食品接触材料及制品高锰酸钾消耗量的测定[S].
[9] 张莉莉, 朱俊, 陆浩. 基于LIMS 和EXCEL 砝码电子原始记录的设计[J]. 衡器, 2020, 49(02): 21-24.
[10] 毛森, 刘莹, 李佳, 等. 检定/校准电子化数据修约技巧[J]. 工业计量, 2016, 26(03): 83-85.
[11] 吴湘. 计量检测电子原始记录系统的研制[J]. 计量与测试技术, 2019, 46(09): 11-12.
基金项目：由上海市科学技术委员会研发公共服务平台建设项目《上海市日用消费品质量安全检测专业技术服务平台》（项目编号：14DZ2293000）资助；由国家市场监督管理总局科技计划项目《基于智能辨色技术制备全自动高锰酸钾消耗量测定仪》（项目编号：2020MK029）资助。

通讯作者：石鎏杰，E-mail:*************.cn。

上接第117页。