萬 勇 劉雅杰 羅福生 潘云飛 盧嘉敏

(廣州能源檢測研究院，廣州 510170)

?

利用Excel及VBA實現(xiàn)臥式金屬罐實標(biāo)數(shù)據(jù)自動化處理*

萬 勇 劉雅杰 羅福生 潘云飛 盧嘉敏

(廣州能源檢測研究院，廣州 510170)

探討臥式金屬罐容量比較法標(biāo)定數(shù)據(jù)的處理問題，重點論述利用Excel及VBA編制處理軟件實現(xiàn)自動化處理過程，此外還提供了關(guān)鍵VBA代碼以方便讀者查詢參考。

臥式金屬罐；容量比較法；數(shù)據(jù)查驗；數(shù)據(jù)剔除；三次差值；罐容表

0 引言

容量比較法是臥式金屬罐(以下簡稱臥式罐)容量的常用標(biāo)定方法(也稱實標(biāo)法)，分為量入法和量出法，因其具有良好的適應(yīng)性和可靠性，近年來獲得了廣泛應(yīng)用。然而，容量比較法并不是標(biāo)準(zhǔn)方法，在JJG 266—1996《臥式金屬罐容積檢定規(guī)程》中沒有進(jìn)行詳細(xì)規(guī)定，操作起來存在一定困難。尤其是數(shù)據(jù)處理部分，極易造成數(shù)據(jù)失誤，存在較高的計量風(fēng)險。本文利用Excel公式以及VBA強大的編程能力，設(shè)計出臥式罐實標(biāo)數(shù)據(jù)處理軟件，實現(xiàn)罐容數(shù)據(jù)直觀、準(zhǔn)確、高效的自動化處理過程。

1 數(shù)據(jù)處理前準(zhǔn)備

1)一組由容量比較法標(biāo)定過程產(chǎn)生的原始數(shù)據(jù)，其中液位高度與容量的對應(yīng)情況如表1所示。

2)一份由Excel(帶有VBA模塊)編制而成的臥式罐實標(biāo)處理軟件(建議Excel版本2003以上)。

表1 液位高度與容量的對應(yīng)情況

注：表1中的液位高度、容量等數(shù)據(jù)均已修正到20℃的狀態(tài)。

2 軟件主要功能

1)利用有限的標(biāo)定數(shù)據(jù)，進(jìn)行差值處理，按一定數(shù)據(jù)間隔自動形成罐容數(shù)據(jù)。

2)數(shù)據(jù)查驗功能，自動識別異常數(shù)據(jù)并予以剔除。

3)直觀圖形演示功能，可實現(xiàn)即時更新。

4)“反悔”功能，方便“撤消”與“重復(fù)”操作。

3 軟件實現(xiàn)流程

一般按“方法選擇(量入法、量出法)→數(shù)據(jù)查驗→數(shù)據(jù)剔除→參數(shù)匯總→罐容表”的過程來進(jìn)行。

4 軟件編制方法

4.1 量入法與量出法模塊

建立兩個工作表，名稱分別為“量入法”、“量出法”，里面帶有一些修正計算，還與具體操作方法、操作時機有關(guān)。為了保證數(shù)據(jù)處理的一致性，量入法與量出法最終都要形成數(shù)據(jù)表形式(表1)。

4.2 數(shù)據(jù)查驗?zāi)K

臥式罐標(biāo)定過程中可能由于人為操作失誤、儀器故障或其它因素造成個別數(shù)據(jù)失真，必須進(jìn)行數(shù)據(jù)查驗和數(shù)據(jù)剔除，因此建立一個名為“數(shù)據(jù)查驗”的工作表，在A、B兩列放置待查驗的原始數(shù)據(jù)；D、E兩列放置查驗后的數(shù)據(jù)；G、H、I三列放置標(biāo)定高度、分度容積、高差數(shù)據(jù)，右側(cè)插入一幅數(shù)據(jù)圖形，類型為XY平滑線散點圖，數(shù)據(jù)源為G、H、I三列，這個波形圖可直觀反映罐容表分度容積變化情況，有助于發(fā)現(xiàn)異常數(shù)據(jù)。正常的波形曲線應(yīng)如圖1所示，呈大致的拋物線形，標(biāo)定點之間的曲線可能不夠平滑，但基本不會產(chǎn)生突變，這也是數(shù)據(jù)剔除工作的依據(jù)；插入三個VBA按鈕，名稱分別為“導(dǎo)入原始數(shù)據(jù)”、“恢復(fù)數(shù)據(jù)”、“刷新波形”。功能分別是“把量入法或量出法將對應(yīng)數(shù)據(jù)導(dǎo)入到A、B兩列”、“剔除數(shù)據(jù)失誤需反悔時，重新調(diào)入原數(shù)據(jù)”、“刪除異常數(shù)據(jù)后觀察最新的波形顯示”。異常數(shù)據(jù)的具體剔除方法可參考筆者另一篇論文[1]。

4.3 數(shù)據(jù)差值模塊

此模塊選擇拉格朗日三次數(shù)據(jù)差值方法，將有限個標(biāo)定數(shù)據(jù)按一定間隔形成差值數(shù)據(jù)。

4.4 參數(shù)匯總表模塊

包括查驗前、查驗后原始數(shù)據(jù)的比較及圖形對照，標(biāo)定方法、證書類型、高度間隔、剔除數(shù)據(jù)統(tǒng)計、罐容表起始與結(jié)束高度、量出法專用數(shù)據(jù)等等，這些信息決定了罐容表的生成型式與風(fēng)格(可以自行設(shè)計)。

4.5 罐容表生成模塊

建立“生成罐容表數(shù)據(jù)”按鈕，根據(jù)差值數(shù)據(jù)結(jié)果，點擊后自動生成符合條件的罐容表，可以直接打印或上傳到證書系統(tǒng)中。

5 軟件界面

數(shù)據(jù)查驗/數(shù)據(jù)剔除模塊界面如圖1所示。參數(shù)匯總模塊界面如圖2所示。其它界面略。

6 關(guān)鍵VBA代碼參考

6.1 “導(dǎo)入原始數(shù)據(jù)”按鈕(代碼略)6.2 “恢復(fù)數(shù)據(jù)”按鈕代碼

Sub huifu()

Dim i As Long

Dim j As Long

i=ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Range

(ActiveCell.Address).Row

j=ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Range

(ActiveCell.Address).Column

If i >= 3 And i <= 100 And (j = 1 Or j = 2) Then

IfThisWorkbook.Worksheets(“參數(shù)匯總表”)

.Range(“K9”) =“量入法”Then

ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Cells(i, 1) = ThisWorkbook.Worksheets(“量入法”).Cells(i, 5)

ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Cells(i, 2) = ThisWorkbook.Worksheets(“量入法”).Cells(i, 6)

Else

ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Cells(i, 1) =ThisWorkbook.Worksheets(“量出法”).Cells(i, 18)

ThisWorkbook.Worksheets(“數(shù)據(jù)查驗”).Cells(i, 2) =ThisWorkbook.Worksheets(“量出法”).Cells(i, 19)

End If

Else

MsgBox “不能恢復(fù)數(shù)據(jù)，請先選中有效單元格!”

End If

End Sub

6.3 “刷新波形”按鈕(代碼略)6.4 “生成罐容表數(shù)據(jù)”按鈕代碼

Sub shengcheng()

If ThisWorkbook.Worksheets(“參數(shù)匯總表”)

.Range(“K10”) =“檢定”Then

′ 在“結(jié)論”單元格中加下邊框

With ThisWorkbook.Worksheets(“罐容表”).Range

(“C18:G18”).Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

Else

′ 去除“結(jié)論”單元格中的下邊框

ThisWorkbook.Worksheets(“罐容表”).Range(“C18:G18”).Borders(xlEdgeBottom).LineStyle = xlNone

End If

′ 將工作表“參數(shù)匯總表”篩選后的數(shù)據(jù)轉(zhuǎn)置拷入到工作表“三次差值”的相關(guān)行中

ThisWorkbook.Worksheets(“參數(shù)匯總表”).Range

("D3:E100").Copy

ThisWorkbook.Worksheets(“三次差值”).Activate

圖1 數(shù)據(jù)查驗/數(shù)據(jù)剔除模塊界面

圖2 參數(shù)匯總模塊界面

ThisWorkbook.Worksheets(“三次差值”).Range

(“A1”).Activate

ThisWorkbook.Worksheets(“三次差值”).Range

(“R1”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=True

Dim a As String

a = “A3:A” & Worksheets(“參數(shù)匯總表”).Range

(“K17”) + 2 & “,C3:C” & Worksheets(“參數(shù)匯總表”).Range(“K17”) + 2

ThisWorkbook.Worksheets(“三次差值”)

.ChartObjects(“圖表2”).Activate

ActiveChart.SetSourceData Source:=Worksheets(“三次差值”).Range(a), PlotBy _

:=xlColumns

Dim b As String

b = “A3:A” & Worksheets(“參數(shù)匯總表”).Range

(“K12”) + 2 & “,Q3:Q” & Worksheets(“參數(shù)匯總表”).Range(“K12”) + 2

ThisWorkbook.Worksheets(“參數(shù)匯總表”)

.ChartObjects(“圖表10”).Activate

ActiveChart.SetSourceData Source:=Worksheets(“參數(shù)匯總表”).Range(b), PlotBy _

:=xlColumns

Dim c As String

c =“D3:D” & Worksheets(“參數(shù)匯總表”).Range

(“K13”) + 2 &“,R3:R” & Worksheets(“參數(shù)匯總表”).Range("K13") + 2

ThisWorkbook.Worksheets(“參數(shù)匯總表”)

.ChartObjects(“圖表11”).Activate

ActiveChart.SetSourceData Source:=Worksheets(“參數(shù)匯總表”).Range(c), PlotBy _

:=xlColumns

Dim d As String

d = “G3:G” & Worksheets(“參數(shù)匯總表”).Range

(“K17”) + 2 &“,S3:S” & Worksheets(“參數(shù)匯總表”).Range("K17") + 2

ThisWorkbook.Worksheets(“參數(shù)匯總表”)

.ChartObjects(“圖表12”).Activate

ActiveChart.SetSourceData Source:=Worksheets

(“參數(shù)匯總表”).Range(d), PlotBy _

:=xlColumns

Application.CutCopyMode = False

End Sub

7 結(jié)束語

臥式罐實標(biāo)數(shù)據(jù)的處理是行業(yè)技術(shù)難點，把握不好會影響罐容表的準(zhǔn)確性，甚至由此產(chǎn)生計量糾紛。本文設(shè)計專用的處理軟件，具有簡單直觀、處理速度快、準(zhǔn)確可靠等優(yōu)點，具有很高的推廣使用價值。

[1] 萬勇,潘云飛,楊茹,等.臥式金屬罐容量比較法標(biāo)定及異常罐容曲線的分析與改進(jìn).計量技術(shù)，2014(8)

[2] 董波,等.JJG 266—1996臥式金屬罐容積檢定規(guī)程

[3] 孔慶彥,等.JJG 133—2005汽車油罐車容量檢定規(guī)程

[4] 劉子勇,等.JJG 259—2005標(biāo)準(zhǔn)金屬量器檢定規(guī)程

[5] 郭立功,劉子勇,佟林,王金濤.自動容積比較法臥式金屬罐容量現(xiàn)場檢測研究.計量學(xué)報，2010,31(6)

[6] 夏惠芳,湯景峰, 張維智.臥式罐的標(biāo)定及數(shù)據(jù)處理方法.石油庫與加油站，2007,16(4)

*廣州市質(zhì)量技術(shù)監(jiān)督局科技項目 (編號：2014kj09)

10.3969/j.issn.1000-0771.2015.07.04