利用Excel及VBA实现卧式金属罐实标数据自动化处理*

万 勇 刘雅杰 罗福生 潘云飞 卢嘉敏

(广州能源检测研究院，广州 510170)



利用Excel及VBA实现卧式金属罐实标数据自动化处理*

万 勇 刘雅杰 罗福生 潘云飞 卢嘉敏

(广州能源检测研究院，广州 510170)

探讨卧式金属罐容量比较法标定数据的处理问题，重点论述利用Excel及VBA编制处理软件实现自动化处理过程，此外还提供了关键VBA代码以方便读者查询参考。

卧式金属罐；容量比较法；数据查验；数据剔除；三次差值；罐容表

0 引言

容量比较法是卧式金属罐(以下简称卧式罐)容量的常用标定方法(也称实标法)，分为量入法和量出法，因其具有良好的适应性和可靠性，近年来获得了广泛应用。然而，容量比较法并不是标准方法，在JJG 266—1996《卧式金属罐容积检定规程》中没有进行详细规定，操作起来存在一定困难。尤其是数据处理部分，极易造成数据失误，存在较高的计量风险。本文利用Excel公式以及VBA强大的编程能力，设计出卧式罐实标数据处理软件，实现罐容数据直观、准确、高效的自动化处理过程。

1 数据处理前准备

1)一组由容量比较法标定过程产生的原始数据，其中液位高度与容量的对应情况如表1所示。

2)一份由Excel(带有VBA模块)编制而成的卧式罐实标处理软件(建议Excel版本2003以上)。

表1 液位高度与容量的对应情况

注：表1中的液位高度、容量等数据均已修正到20℃的状态。

2 软件主要功能

1)利用有限的标定数据，进行差值处理，按一定数据间隔自动形成罐容数据。

2)数据查验功能，自动识别异常数据并予以剔除。

3)直观图形演示功能，可实现即时更新。

4)“反悔”功能，方便“撤消”与“重复”操作。

3 软件实现流程

一般按“方法选择(量入法、量出法)→数据查验→数据剔除→参数汇总→罐容表”的过程来进行。

4 软件编制方法

4.1 量入法与量出法模块

建立两个工作表，名称分别为“量入法”、“量出法”，里面带有一些修正计算，还与具体操作方法、操作时机有关。为了保证数据处理的一致性，量入法与量出法最终都要形成数据表形式(表1)。

4.2 数据查验模块

卧式罐标定过程中可能由于人为操作失误、仪器故障或其它因素造成个别数据失真，必须进行数据查验和数据剔除，因此建立一个名为“数据查验”的工作表，在A、B两列放置待查验的原始数据；D、E两列放置查验后的数据；G、H、I三列放置标定高度、分度容积、高差数据，右侧插入一幅数据图形，类型为XY平滑线散点图，数据源为G、H、I三列，这个波形图可直观反映罐容表分度容积变化情况，有助于发现异常数据。正常的波形曲线应如图1所示，呈大致的抛物线形，标定点之间的曲线可能不够平滑，但基本不会产生突变，这也是数据剔除工作的依据；插入三个VBA按钮，名称分别为“导入原始数据”、“恢复数据”、“刷新波形”。功能分别是“把量入法或量出法将对应数据导入到A、B两列”、“剔除数据失误需反悔时，重新调入原数据”、“删除异常数据后观察最新的波形显示”。异常数据的具体剔除方法可参考笔者另一篇论文[1]。

4.3 数据差值模块

此模块选择拉格朗日三次数据差值方法，将有限个标定数据按一定间隔形成差值数据。

4.4 参数汇总表模块

包括查验前、查验后原始数据的比较及图形对照，标定方法、证书类型、高度间隔、剔除数据统计、罐容表起始与结束高度、量出法专用数据等等，这些信息决定了罐容表的生成型式与风格(可以自行设计)。

4.5 罐容表生成模块

建立“生成罐容表数据”按钮，根据差值数据结果，点击后自动生成符合条件的罐容表，可以直接打印或上传到证书系统中。

5 软件界面

数据查验/数据剔除模块界面如图1所示。参数汇总模块界面如图2所示。其它界面略。

6 关键VBA代码参考

6.1 “导入原始数据”按钮(代码略)6.2 “恢复数据”按钮代码

Sub huifu()

Dim i As Long

Dim j As Long

i=ThisWorkbook.Worksheets(“数据查验”).Range

(ActiveCell.Address).Row

j=ThisWorkbook.Worksheets(“数据查验”).Range

(ActiveCell.Address).Column

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

IfThisWorkbook.Worksheets(“参数汇总表”)

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

ThisWorkbook.Worksheets(“数据查验”).Cells(i, 1) = ThisWorkbook.Worksheets(“量入法”).Cells(i, 5)

ThisWorkbook.Worksheets(“数据查验”).Cells(i, 2) = ThisWorkbook.Worksheets(“量入法”).Cells(i, 6)

Else

ThisWorkbook.Worksheets(“数据查验”).Cells(i, 1) =ThisWorkbook.Worksheets(“量出法”).Cells(i, 18)

ThisWorkbook.Worksheets(“数据查验”).Cells(i, 2) =ThisWorkbook.Worksheets(“量出法”).Cells(i, 19)

End If

Else

MsgBox “不能恢复数据，请先选中有效单元格!”

End If

End Sub

6.3 “刷新波形”按钮(代码略)6.4 “生成罐容表数据”按钮代码

Sub shengcheng()

If ThisWorkbook.Worksheets(“参数汇总表”)

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

′ 在“结论”单元格中加下边框

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

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

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

Else

′ 去除“结论”单元格中的下边框

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

End If

′ 将工作表“参数汇总表”筛选后的数据转置拷入到工作表“三次差值”的相关行中

ThisWorkbook.Worksheets(“参数汇总表”).Range

("D3:E100").Copy

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

图1 数据查验/数据剔除模块界面

图2 参数汇总模块界面

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(“参数汇总表”).Range

(“K17”) + 2 & “,C3:C” & Worksheets(“参数汇总表”).Range(“K17”) + 2

ThisWorkbook.Worksheets(“三次差值”)

.ChartObjects(“图表2”).Activate

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

:=xlColumns

Dim b As String

b = “A3:A” & Worksheets(“参数汇总表”).Range

(“K12”) + 2 & “,Q3:Q” & Worksheets(“参数汇总表”).Range(“K12”) + 2

ThisWorkbook.Worksheets(“参数汇总表”)

.ChartObjects(“图表10”).Activate

ActiveChart.SetSourceData Source:=Worksheets(“参数汇总表”).Range(b), PlotBy _

:=xlColumns

Dim c As String

c =“D3:D” & Worksheets(“参数汇总表”).Range

(“K13”) + 2 &“,R3:R” & Worksheets(“参数汇总表”).Range("K13") + 2

ThisWorkbook.Worksheets(“参数汇总表”)

.ChartObjects(“图表11”).Activate

ActiveChart.SetSourceData Source:=Worksheets(“参数汇总表”).Range(c), PlotBy _

:=xlColumns

Dim d As String

d = “G3:G” & Worksheets(“参数汇总表”).Range

(“K17”) + 2 &“,S3:S” & Worksheets(“参数汇总表”).Range("K17") + 2

ThisWorkbook.Worksheets(“参数汇总表”)

.ChartObjects(“图表12”).Activate

ActiveChart.SetSourceData Source:=Worksheets

(“参数汇总表”).Range(d), PlotBy _

:=xlColumns

Application.CutCopyMode = False

End Sub

7 结束语

卧式罐实标数据的处理是行业技术难点，把握不好会影响罐容表的准确性，甚至由此产生计量纠纷。本文设计专用的处理软件，具有简单直观、处理速度快、准确可靠等优点，具有很高的推广使用价值。

[1] 万勇,潘云飞,杨茹,等.卧式金属罐容量比较法标定及异常罐容曲线的分析与改进.计量技术，2014(8)

[2] 董波,等.JJG 266—1996卧式金属罐容积检定规程

[3] 孔庆彦,等.JJG 133—2005汽车油罐车容量检定规程

[4] 刘子勇,等.JJG 259—2005标准金属量器检定规程

[5] 郭立功,刘子勇,佟林,王金涛.自动容积比较法卧式金属罐容量现场检测研究.计量学报，2010,31(6)

[6] 夏惠芳,汤景峰, 张维智.卧式罐的标定及数据处理方法.石油库与加油站，2007,16(4)

*广州市质量技术监督局科技项目 (编号：2014kj09)

10.3969/j.issn.1000-0771.2015.07.04