基于Excel實現(xiàn)JJG443—2015記錄證書全自動生成

周斌

[摘 要]檢定燃油加油機的計算工作具有參與計算原始數(shù)據(jù)多、計算工作量大、計算過程重復(fù)繁瑣的特點， 應(yīng)用本Excel模板錄入原始數(shù)據(jù)就可以實現(xiàn)自動計算、自動判斷、自動生成檢定證書或不合格通知書， 從而大大提高了工作效率。

[關(guān)鍵詞]燃油加油機；原始記錄；檢定證書；Excel

中圖分類號：TP274 文獻標識碼：A 文章編號：1009-914X（2016）01-0303-02

JJG443-2015《燃油加油機》檢定規(guī)程已于2015年4月10日由國家質(zhì)檢總局發(fā)布實施。JJG443-2015相較于JJG443-2006的主要變化是重復(fù)性的計算方法和判斷要求，以及外觀的判斷等，具體見JJG443-2015《燃油加油機》的引言部分已經(jīng)作了詳細說明。本人根據(jù)JJG443 -2015《燃油加油機》檢定規(guī)程和質(zhì)檢總局文件《關(guān)于印發(fā)新版〈檢定證書〉和〈檢定結(jié)果通知書〉封面格式式樣的通知（國質(zhì)檢量函[2005] 861 號）》的要求， 利用Excel軟件制作了燃油加油機檢定原始記錄和證書自動生成模板。本模板可以依據(jù)實際情況選擇首次或后續(xù)檢定、使用的不同檢定介質(zhì)、標準量器的不同材質(zhì)等條件自動選擇相應(yīng)的判斷條件，對計算結(jié)果進行判定并自動生成檢定證書或不合格通知書，從而達到提高工作效率、減少出錯率的目的，謹希望本模板能給同行帶來便利。

1 建立單位信息表

按如圖1所示格式建立單位信息表，并填入本單位的相關(guān)信息及標準器的信息。

2 建立檢定原始記錄模板

按如圖2所示格式建立原始記錄模板（此工作表命名為“原始記錄”）。

編輯計算公式， 自動生成原始檢定記錄

首次檢定與后續(xù)檢定選擇的公式錄入：

點中I1單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“□，√”

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

單位基本信息調(diào)用的公式錄入：

在N1單元格中，輸入“=單位信息！B10”此含義為N1單元格引用單位信息表B10單元格的內(nèi)容。

按此格式分別在C5、E5、G5、I5、N5、C6、E6、G6、I6、N6、B7、I7單元格中輸入對應(yīng)單位信息公式。

計量標準器信息選擇的公式錄入：

點中I1單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“不銹鋼，碳鋼，黃銅，青銅”

在D8單元格中，輸入“=IF（B8=""，""，IF（B8="不銹鋼"，0.00005，IF（B8="碳鋼"，0.000033，IF（B8="黃銅"，0.000053，IF（B8="青銅"，0.000053）））））”

檢定介質(zhì)選擇的公式錄入：

點中G8單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“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單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“合格，不合格”。同樣在對H10單元格、N10單元格、C11單元格、H11單元格、N11單元格進行同樣的操作。

自鎖功能合格性判定的公式錄入：

在B14單元格中，輸入“=IF（OR（C15=""，F(xiàn)15=""，J15<>"無"），"不合格"，"合格"）”

點中J15單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“無”。點出錯警告，將輸入無效數(shù)據(jù)時顯示出錯警告前的√去掉。點確定。

示值誤差合格性判定的公式錄入：

在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單元格輸入公式。

重復(fù)性自動計算的公式錄入：

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

點中N18單元格，將光標移到該單元格的右下角，光標變成黑十字架，按住鼠標向下拖到N26單元格，完成對N21單元格、N24單元格輸入公式。

應(yīng)付金額合格性判定的公式錄入：

在C28單元格中，輸入“=IF（I1="□"，"后續(xù)檢定不作要求"，IF（M30=0，"合格"，"不合格"））”

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

點中D30單元格，將光標移到該單元格的右下角，光標變成黑十字架，按住鼠標向下拖到D32單元格，完成對D31單元格、D32單元格輸入公式。

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

點中H30單元格，將光標移到該單元格的右下角，光標變成黑十字架，按住鼠標向下拖到D32單元格，完成對H31單元格、H32單元格輸入公式。

在J30單元格中，輸入“=IF（D30=""，""，F(xiàn)30-H30）”

點中J30單元格，將光標移到該單元格的右下角，光標變成黑十字架，按住鼠標向下拖到D32單元格，完成對J31單元格、J32單元格輸入公式。

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

檢定結(jié)論合格性判定的公式錄入：

在B36單元格中，輸入“=IF（OR（B9="不合格"，B14="不合格"，B16="不合格"，C28="不合格"），"不合格"，"合格"）”。有個別檢定機構(gòu)存在調(diào)修行為，會有經(jīng)調(diào)修合格的情況。此時需要人工判斷合格性，則按如下程序操作：點中B36單元格，選擇工具欄的數(shù)據(jù)，有效性，設(shè)置，在允許中選擇序列，在來源中輸入“合格，經(jīng)調(diào)修合格，不合格”，確定。

檢定有效期的公式錄入：

在M36單元格中，輸入“=IF（B36="合格"，DATE（H37，J37+6，M37-1），IF（B36="經(jīng)調(diào)修合格"，DATE（H37，J37+3，M37-1），""））”

3 建立檢定證書模板

按如圖3、圖4所示格式建立檢定證書模板（此工作表命名為“檢定證書”）。

證書名稱的自動判定與生成的公式錄入：在A6單元格中，輸入“=IF（原始記錄！B36="不合格"，"檢 定 結(jié) 果 通 知 書"，"檢 定 證 書"）”

送檢單位信息的調(diào)用，在K14單元格中，輸入“=原始記錄！M2”

按此格式分別在K18、K20、K22、K24、K26、K41、O41、R41單元格中輸入對應(yīng)單位信息公式。

有效期的調(diào)用，在K43單元格中，輸入“=YEAR（原始記錄！M36）”，在O43單元格中，輸入“=MONTH（原始記錄！M36）”，在R43單元格中，輸入“=DAY（原始記錄！M36）”

選中A43：AD43單元格，點菜單欄的格式，條件格式，條件選中公式，輸入“=$K$26="不合格"，字體，顏色選中白色，確定。從而達到檢定不合格時不顯示有效期。

證書內(nèi)頁模板的生成

證書名稱的調(diào)用，在A55單元格中，輸入“=IF（原始記錄！B36="合格"，單位信息！B2&"檢定證書"，單位信息！B2&"檢定結(jié)果通知書"）”

證書編號的調(diào)用，在Z55單元格中，輸入“=P10”

標準裝置名稱相關(guān)信息的調(diào)用：

在F62單元格中，輸入“=單位信息！B15”

按此格式分別在F63、J62、J63、AA60、AA61單元格中輸入對應(yīng)單位信息公式。

檢定地點和環(huán)境條件信息的調(diào)用：

在P69單元格中，輸入“=原始記錄！J4”

在Z69單元格中，輸入“=原始記錄！M4”

銘牌和外觀信息的調(diào)用：在H72單元格中，輸入“=原始記錄！B9”

按此格式分別在I76、I77、L76、P76、P77、S76、W76、W77、Z76單元格中輸入對應(yīng)銘牌和外觀信息公式。

自鎖功能檢查信息的調(diào)用：

在G78單元格中，輸入“=IF（原始記錄！B36="不合格"，原始記錄！B14，""）”

在B82單元格中，輸入“=原始記錄！C15”

按此格式分別在K82、T82單元格中輸入對應(yīng)自鎖功能檢查信息公式。

加油機示值檢定信息的調(diào)用：

在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 模板格式的保護

單位信息表因為不常用，只有標準器經(jīng)檢定后要錄入標準器證書信息才用到，所以可以將該表格式設(shè)置為隱藏。點到該工作表，點菜單欄，格式，工作表，隱藏。

檢定證書表因為是全自動生成，無改動或再錄入信息，所以可以將該表保護起來。按如下步驟進行：點到該工作表，點菜單欄，工具，保護，保護工作表，輸入保護密碼，確定。

原始記錄的保護，在需要錄入信息或改變信息的單元格，點右鍵，點設(shè)置單元格格式，點保護，將鎖定前的√去掉，點確定。然后再按前面介紹的步驟將該工作表保護起來。

本文詳細介紹了燃油加油機的檢定原始記錄和證書的自動生成方法。本模板經(jīng)使用以來， 顯著提高了工作效率、減少了出錯率。因本人能力有限，本文如有不妥之處，萬望批評指正。本文的模板，提供交流。

參考文獻

[1] JJG443-2015 燃油加油機檢定規(guī)程。

[2] 關(guān)于印發(fā)新版〈檢定證書〉和〈檢定結(jié)果通知書〉封面格式式樣的通知（國質(zhì)檢量函[2005] 861 號）。

[3] Excel函數(shù)、圖表與透視表從入門到精通（修訂版。