基于Excel实现JJG443—2015记录证书全自动生成

周斌

[摘 要]检定燃油加油机的计算工作具有参与计算原始数据多、计算工作量大、计算过程重复繁琐的特点， 应用本Excel模板录入原始数据就可以实现自动计算、自动判断、自动生成检定证书或不合格通知书， 从而大大提高了工作效率。

[关键词]燃油加油机；原始记录；检定证书；Excel

中图分类号：TP274 文献标识码：A 文章编号：1009-914X（2016）01-0303-02

JJG443-2015《燃油加油机》检定规程已于2015年4月10日由国家质检总局发布实施。JJG443-2015相较于JJG443-2006的主要变化是重复性的计算方法和判断要求，以及外观的判断等，具体见JJG443-2015《燃油加油机》的引言部分已经作了详细说明。本人根据JJG443 -2015《燃油加油机》检定规程和质检总局文件《关于印发新版〈检定证书〉和〈检定结果通知书〉封面格式式样的通知（国质检量函[2005] 861 号）》的要求， 利用Excel软件制作了燃油加油机检定原始记录和证书自动生成模板。本模板可以依据实际情况选择首次或后续检定、使用的不同检定介质、标准量器的不同材质等条件自动选择相应的判断条件，对计算结果进行判定并自动生成检定证书或不合格通知书，从而达到提高工作效率、减少出错率的目的，谨希望本模板能给同行带来便利。

1 建立单位信息表

按如图1所示格式建立单位信息表，并填入本单位的相关信息及标准器的信息。

2 建立检定原始记录模板

按如图2所示格式建立原始记录模板（此工作表命名为“原始记录”）。

编辑计算公式， 自动生成原始检定记录

首次检定与后续检定选择的公式录入：

点中I1单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“□，√”

在K1单元格中，输入“=IF（I1="□"，"√"，"□"）”

单位基本信息调用的公式录入：

在N1单元格中，输入“=单位信息！B10”此含义为N1单元格引用单位信息表B10单元格的内容。

按此格式分别在C5、E5、G5、I5、N5、C6、E6、G6、I6、N6、B7、I7单元格中输入对应单位信息公式。

计量标准器信息选择的公式录入：

点中I1单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“不锈钢，碳钢，黄铜，青铜”

在D8单元格中，输入“=IF（B8=""，""，IF（B8="不锈钢"，0.00005，IF（B8="碳钢"，0.000033，IF（B8="黄铜"，0.000053，IF（B8="青铜"，0.000053）））））”

检定介质选择的公式录入：

点中G8单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“93号汽油，97号汽油，0号柴油，煤油”

在J8单元格中，输入“=IF（G8=""，""，IF（G8="93号汽油"，0.0012，IF（G8="97号汽油"，0.0012，IF（G8="0号柴油"，0.0009，IF（G8="煤油"，0.0009）））））”

外观合格性判定的公式录入：

在B9单元格中，输入“=IF（OR（C10="不合格"，C11="不合格"，H10="不合格"，H11="不合格"，N10="不合格"，N11="不合格"，E12=""，E13=""，H12=""，H13=""，M12=""，M13=""），"不合格"，"合格"）”

点中C10单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“合格，不合格”。同样在对H10单元格、N10单元格、C11单元格、H11单元格、N11单元格进行同样的操作。

自锁功能合格性判定的公式录入：

在B14单元格中，输入“=IF（OR（C15=""，F15=""，J15<>"无"），"不合格"，"合格"）”

点中J15单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“无”。点出错警告，将输入无效数据时显示出错警告前的√去掉。点确定。

示值误差合格性判定的公式录入：

在B16单元格中，输入“=IF（AND（I1="□"，ABS（M18）<0.3，ABS（M21）<0.3，N18<0.1，N21<0.1），"合格"，IF（AND（I1="√"，ABS（M18）<0.3，ABS（M21）<0.3，ABS（M24）<0.3，N18<0.1，N21<0.1，N24<0.1），"合格"，"不合格"））”

标准量器示值自动计算的公式录入：

在F18单元格中，输入“=IF（E18=""，""，ROUND（100+（E18-单位信息！$H$15）*单位信息！$I$15/1000，3））”

点中F18单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到F20单元格，完成对F19单元格、F20单元格输入公式。

在F21单元格中，输入“=IF（E21=""，""，ROUND（20+（E21-单位信息！$H$16）*单位信息！$I$16/1000，3））”

点中F21单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到F26单元格，完成对F22单元格、F23单元格、F24单元格、F25单元格、F26单元格输入公式。

实际体积自动计算的公式录入：

在J18单元格中，输入“=IF（E18=""，""，ROUND（F18*（1+$J$8*（G18-H18）+$D$8*（H18-20）），2））”

点中J18单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到J20单元格，完成对J19单元格、J20单元格输入公式。

在J21单元格中，输入“=IF（E21=""，""，ROUND（F21*（1+$J$8*（G21-H21）+$D$8*（H21-20）），2））”

点中J21单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到J26单元格，完成对J22单元格、J23单元格、J24单元格、J25单元格、J26单元格输入公式。

单次测量相对误差自动计算的公式录入：

在K18单元格中，输入“=IF（E18=""，""，ROUND（（D18-J18）*100/J18，2））”

点中K18单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到L20单元格，完成对K19单元格、K20单元格输入公式。

在K21单元格中，输入“=IF（E21=""，""，ROUND（（D21-J21）*100/J21，2））”

点中L21单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到L26单元格，完成对K22单元格、K23单元格、K24单元格、K25单元格、K26单元格输入公式。

示值误差自动计算的公式录入：

在M18单元格中，输入“=IF（E18=""，""，ROUND（AVERAGE（K18：K20），2））”

点中M18单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到M26单元格，完成对M21单元格、M24单元格输入公式。

重复性自动计算的公式录入：

在N18单元格中，输入“=IF（K18=""，""，ROUND（（MAX（K18：K20）-MIN（K18：K20））/1.69，2））”

点中N18单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到N26单元格，完成对N21单元格、N24单元格输入公式。

应付金额合格性判定的公式录入：

在C28单元格中，输入“=IF（I1="□"，"后续检定不作要求"，IF（M30=0，"合格"，"不合格"））”

在D30单元格中，输入“=IF（$I$1="√"，D18，""）”

点中D30单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到D32单元格，完成对D31单元格、D32单元格输入公式。

在H30单元格中，输入“=IF（D30=""，""，D30*E30）”

点中H30单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到D32单元格，完成对H31单元格、H32单元格输入公式。

在J30单元格中，输入“=IF（D30=""，""，F30-H30）”

点中J30单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到D32单元格，完成对J31单元格、J32单元格输入公式。

在M30单元格中，输入“=IF（D30=""，""，AVERAGE（J30：L32））”

检定结论合格性判定的公式录入：

在B36单元格中，输入“=IF（OR（B9="不合格"，B14="不合格"，B16="不合格"，C28="不合格"），"不合格"，"合格"）”。有个别检定机构存在调修行为，会有经调修合格的情况。此时需要人工判断合格性，则按如下程序操作：点中B36单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“合格，经调修合格，不合格”，确定。

检定有效期的公式录入：

在M36单元格中，输入“=IF（B36="合格"，DATE（H37，J37+6，M37-1），IF（B36="经调修合格"，DATE（H37，J37+3，M37-1），""））”

3 建立检定证书模板

按如图3、图4所示格式建立检定证书模板（此工作表命名为“检定证书”）。

证书名称的自动判定与生成的公式录入：在A6单元格中，输入“=IF（原始记录！B36="不合格"，"检 定 结 果 通 知 书"，"检 定 证 书"）”

送检单位信息的调用，在K14单元格中，输入“=原始记录！M2”

按此格式分别在K18、K20、K22、K24、K26、K41、O41、R41单元格中输入对应单位信息公式。

有效期的调用，在K43单元格中，输入“=YEAR（原始记录！M36）”，在O43单元格中，输入“=MONTH（原始记录！M36）”，在R43单元格中，输入“=DAY（原始记录！M36）”

选中A43：AD43单元格，点菜单栏的格式，条件格式，条件选中公式，输入“=$K$26="不合格"，字体，颜色选中白色，确定。从而达到检定不合格时不显示有效期。

证书内页模板的生成

证书名称的调用，在A55单元格中，输入“=IF（原始记录！B36="合格"，单位信息！B2&"检定证书"，单位信息！B2&"检定结果通知书"）”

证书编号的调用，在Z55单元格中，输入“=P10”

标准装置名称相关信息的调用：

在F62单元格中，输入“=单位信息！B15”

按此格式分别在F63、J62、J63、AA60、AA61单元格中输入对应单位信息公式。

检定地点和环境条件信息的调用：

在P69单元格中，输入“=原始记录！J4”

在Z69单元格中，输入“=原始记录！M4”

铭牌和外观信息的调用：在H72单元格中，输入“=原始记录！B9”

按此格式分别在I76、I77、L76、P76、P77、S76、W76、W77、Z76单元格中输入对应铭牌和外观信息公式。

自锁功能检查信息的调用：

在G78单元格中，输入“=IF（原始记录！B36="不合格"，原始记录！B14，""）”

在B82单元格中，输入“=原始记录！C15”

按此格式分别在K82、T82单元格中输入对应自锁功能检查信息公式。

加油机示值检定信息的调用：

在H84单元格中，输入“=IF（原始记录！B16="不合格"，"不合格"，IF（原始记录！C28="不合格"，"不合格"，"合格"））”

在F86单元格中，输入“=IF（（MIN（原始记录！M18：M26）+MAX（原始记录！M18：M26））<0，MIN（原始记录！M18：M26），MAX（原始记录！M18：M26））”

在F87单元格中，输入“=MAX（原始记录！N18：N26）”

在H88单元格中，输入“=IF（原始记录！I1="√"，ROUND（原始记录！M30，2）&"元"，"/"）”

在K89单元格中，输入“=原始记录！G8”

在U89单元格中，输入“=原始记录！M15&原始记录！N15”

4 模板格式的保护

单位信息表因为不常用，只有标准器经检定后要录入标准器证书信息才用到，所以可以将该表格式设置为隐藏。点到该工作表，点菜单栏，格式，工作表，隐藏。

检定证书表因为是全自动生成，无改动或再录入信息，所以可以将该表保护起来。按如下步骤进行：点到该工作表，点菜单栏，工具，保护，保护工作表，输入保护密码，确定。

原始记录的保护，在需要录入信息或改变信息的单元格，点右键，点设置单元格格式，点保护，将锁定前的√去掉，点确定。然后再按前面介绍的步骤将该工作表保护起来。

本文详细介绍了燃油加油机的检定原始记录和证书的自动生成方法。本模板经使用以来， 显著提高了工作效率、减少了出错率。因本人能力有限，本文如有不妥之处，万望批评指正。本文的模板，提供交流。

参考文献

[1] JJG443-2015 燃油加油机检定规程。

[2] 关于印发新版〈检定证书〉和〈检定结果通知书〉封面格式式样的通知（国质检量函[2005] 861 号）。

[3] Excel函数、图表与透视表从入门到精通（修订版。