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

萬(wàn) 勇 劉雅杰 羅福生 潘云飛 盧嘉敏

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

?

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

萬(wàn) 勇 劉雅杰 羅福生 潘云飛 盧嘉敏

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

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

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

0 引言

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

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

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

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

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

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

2 軟件主要功能

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

2)數(shù)據(jù)查驗(yàn)功能，自動(dòng)識(shí)別異常數(shù)據(jù)并予以剔除。

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

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

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

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

4 軟件編制方法

4.1 量入法與量出法模塊

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

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

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

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

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

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

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

4.5 罐容表生成模塊

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

5 軟件界面

數(shù)據(jù)查驗(yàn)/數(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ù)查驗(yàn)”).Range

(ActiveCell.Address).Row

j=ThisWorkbook.Worksheets(“數(shù)據(jù)查驗(yàn)”).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ù)查驗(yàn)”).Cells(i, 1) = ThisWorkbook.Worksheets(“量入法”).Cells(i, 5)

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

Else

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

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

End If

Else

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

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ù)查驗(yàn)/數(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é)束語(yǔ)

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

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

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

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

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

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

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

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

10.3969/j.issn.1000-0771.2015.07.04