1、基于 Excel 实现 JJG4432015 记录证书全自动生成摘 要检定燃油加油机的计算工作具有参与计算原始数据多、计算工作量大、计算过程重复繁琐的特点， 应用本 Excel 模板录入原始数据就可以实现自动计算、自动判断、自动生成检定证书或不合格通知书，从而大大提高了工作效率。 关键词燃油加油机；原始记录；检定证书；Excel 中图分类号：TP274 文献标识码：A 文章编号：1009-914X（2016）01-0303-02 JJG443-2015燃油加油机检定规程已于 2015 年 4 月 10 日由国家质检总局发布实施。JJG443-2015 相较于 JJG443-2006 的主要变化
2、是重复性的计算方法和判断要求，以及外观的判断等，具体见 JJG443-2015燃油加油机的引言部分已经作了详细说明。本人根据 JJG443 -2015燃油加油机检定规程和质检总局文件关于印发新版检定证书和检定结果通知书封面格式式样的通知（国质检量函2005 861 号） 的要求， 利用 Excel 软件制作了燃油加油机检定原始记录和证书自动生成模板。本模板可以依据实际情况选择首次或后续检定、使用的不同检定介质、标准量器的不同材质等条件自动选择相应的判断条件，对计算结果进行判定并自动生成检定证书或不合格通知书，从而达到提高工作效率、减少出错率的目的，谨希望本模板能给同行带来便利。 1 建立单位信
3、息表 按如图 1 所示格式建立单位信息表，并填入本单位的相关信息及标准器的信息。 2 建立检定原始记录模板 按如图 2 所示格式建立原始记录模板（此工作表命名为“原始记录” ）。 编辑计算公式， 自动生成原始检定记录 首次检定与后续检定选择的公式录入： 点中 I1 单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“，” 在 K1 单元格中，输入“=IF（I1=“，“，“） ” 单位基本信息调用的公式录入： 在 N1 单元格中，输入“=单位信息！B10”此含义为 N1 单元格引用单位信息表 B10 单元格的内容。 按此格式分别在C5、E5、G5、I5、N5、C6、E6、G6
4、、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
5、单元格中，输入“=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 单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输
6、入“合格，不合格” 。同样在对 H10 单元格、N10 单元格、C11 单元格、H11 单元格、N11 单元格进行同样的操作。 自锁功能合格性判定的公式录入： 在 B14 单元格中，输入“=IF（OR（C15=“，F15=“，J15“无“） ，“不合格“，“合格“） ” 点中 J15 单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“无” 。点出错警告，将输入无效数据时显示出错警告前的去掉。点确定。 示值误差合格性判定的公式录入： 在 B16 单元格中，输入“=IF（AND（I1=“，ABS（M18）0.3，ABS（M21）0.3，N180.1，N210.1） ，“合格“
7、，IF（AND（I1=“，ABS（M18）0.3，ABS（M21）0.3，ABS（M24）0.3，N180.1，N210.1，N240.1） ，“合格“，“不合格“） ） ” 标准量器示值自动计算的公式录入： 在 F18 单元格中，输入“=IF（E18=“，“，ROUND（100+（E18-单位信息！$H$15）*单位信息！$I$15/1000，3） ） ” 点中 F18 单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到 F20 单元格，完成对 F19 单元格、F20 单元格输入公式。 在 F21 单元格中，输入“=IF（E21=“，“，ROUND（20+（E21-单位信
8、息！$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 单元格输入公式。
9、 在 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
10、 单元格，完成对 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 单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到
11、 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，
12、“） ” 点中 D30 单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到 D32 单元格，完成对 D31 单元格、D32 单元格输入公式。 在 H30 单元格中，输入“=IF（D30=“，“，D30*E30） ” 点中 H30 单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到 D32 单元格，完成对 H31 单元格、H32 单元格输入公式。 在 J30 单元格中，输入“=IF（D30=“，“，F30-H30） ” 点中 J30 单元格，将光标移到该单元格的右下角，光标变成黑十字架，按住鼠标向下拖到 D32 单元格，完成对 J31 单元格、J32 单元
13、格输入公式。 在 M30 单元格中，输入“=IF（D30=“，“，AVERAGE（J30：L32） ） ”检定结论合格性判定的公式录入： 在 B36 单元格中，输入“=IF（OR（B9=“不合格“，B14=“不合格“，B16=“不合格“，C28=“不合格“） ，“不合格“，“合格“） ”。有个别检定机构存在调修行为，会有经调修合格的情况。此时需要人工判断合格性，则按如下程序操作：点中 B36 单元格，选择工具栏的数据，有效性，设置，在允许中选择序列，在来源中输入“合格，经调修合格，不合格” ，确定。 检定有效期的公式录入： 在 M36 单元格中，输入“=IF（B36=“合格“，DATE（H37
14、，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 单元格中，输
15、入“=YEAR（原始记录！M36） ”，在 O43 单元格中，输入“=MONTH（原始记录！M36） ”，在 R43 单元格中，输入“=DAY（原始记录！M36） ” 选中 A43：AD43 单元格，点菜单栏的格式，条件格式，条件选中公式，输入“=$K$26=“不合格“，字体，颜色选中白色，确定。从而达到检定不合格时不显示有效期。 证书内页模板的生成 证书名称的调用，在 A55 单元格中，输入“=IF（原始记录！B36=“合格“，单位信息！B2&“检定证书“，单位信息！B2&“检定结果通知书“） ”证书编号的调用，在 Z55 单元格中，输入“=P10” 标准装置名称相关信息的调用： 在 F62
16、 单元格中，输入“=单位信息！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 单元格中，输入
17、“=原始记录！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
18、（原始记录！M30，2）&“元“，“/“） ” 在 K89 单元格中，输入“=原始记录！G8” 在 U89 单元格中，输入“=原始记录！M15&原始记录！N15” 4 模板格式的保护 单位信息表因为不常用，只有标准器经检定后要录入标准器证书信息才用到，所以可以将该表格式设置为隐藏。点到该工作表，点菜单栏，格式，工作表，隐藏。 检定证书表因为是全自动生成，无改动或再录入信息，所以可以将该表保护起来。按如下步骤进行：点到该工作表，点菜单栏，工具，保护，保护工作表，输入保护密码，确定。 原始记录的保护，在需要录入信息或改变信息的单元格，点右键，点设置单元格格式，点保护，将锁定前的去掉，点确定。然后再按前面介绍的步骤将该工作表保护起来。 本文详细介绍了燃油加油机的检定原始记录和证书的自动生成方法。本模板经使用以来， 显著提高了工作效率、减少了出错率。因本人能力有限，本文如有不妥之处，万望批评指正。本文的模板，提供交流。 参考文献 1 JJG443-2015 燃油加油机检定规程。 2 关于印发新版检定证书和检定结果通知书封面格式式样的通知（国质检量函2005 861 号） 。 3 Excel 函数、图表与透视表从入门到精通（修订版。
