喻靖 朱峰 夏瑞杰

摘要： VBA全称Visual Basic for Applications，可以实现EXCELL表格与WORD文档等数据编写、筛选、处理等一系列功能。在实际应用中可以提高文档制作效率，降低数据处理过程中的错误率。该设计利用其便捷的功能，针对某油田复杂的报表进行自动化改良，其中实现了数据的自动计算、自能筛选，报表的一键生成等功能，最终实现报表的自动化制作。

Abstract： The full name of VBA is Visual Basic for Applications. It can achieve screening， processing when using excel tables and WORD documents or other data preparation. In practical application， the efficiency of document could be improved， the error rate could be reduced. This design makes use of its convenient functions to improve the complex statements of an oil field automatically， which realizes the functions of automatic calculation of data， self-energy screening， one-key generation of statements， and finally realizes the automatic production of statements.

关键词：VBA;Excel;数据处理;自动化

Key words： VBA;Excel;the data processing;automation

中图分类号：TP311.5                                    文獻标识码：A                                  文章编号：1006-4311（2020）12-0193-02

1  概况

某油田系辽东区块油气集输中心，因而所涉及数据多且杂，日常涉及到的报表有20余项，有近千项的数据需要人为录入（报表大致框架结构及流程如图1所示），为响应集团公司提出的降本增效方针，故此，该项目应用Office文档中的宏功能对报日常表进行升级，实现一键式的报表输出，省时省力。

2  应用

2.1 复制粘贴功能

本项目通过VBA中的复制粘贴代码实现基本数据的粘贴，因报表内所涉及到的工作表较多，选择使用的代码示例如下：

Windows（"A.xlsm"）.Activate

Sheets（"B"）.Range（""）.Copy

Windows（"A.xlsm"）.Activate

Sheets（"B"）.Range（""）.PasteSpecial Paste：=xlPasteValues

并通过xlPasteValues函数指定为文本复制，以避免将模板中的公式进行粘贴影响报表的正确性。

2.2 数据整合功能

因报表中大部分数据由人为整理并进行分配，在进行一系列的公式计算后会存在数据为负的情况，但根据实际和常理该数值应为正值，固通过If函数来对这些数据进行整合并从新分配。示例代码如下：

Dim i， j， m As Double

Windows（"A.xlsx"）.Activate

If Sheets（"B"）.Range（"X"）.Value < 0 Then

i = Range（"X"）.Value

j = Range（"Y"）.Value

m = i + j

Range（"X"）.Value = m

Range（"Y"）.Value = 0

End If

End Sub

2.3 数据智能筛选

在报表中存在许多每日数据不同，并需要人为进行更改等数据，而这部分数据的录入在报表的制作中工作量较大，并占用了大量的制作时间。

2.3.1单表数据筛选  以每日油井计量数据为例，如表1所示，该表为日计量报表，每日至少需要填报30口，在填报的过程中需要找到当日的计量井并对该行的日期，标记，产量数据，颜色进行更改。要实现这项工作的自动化，可以通过创建字典对象以及For循环函数来进行数据对比和筛选。根据筛选结果来对数据进行所需要的更改。

Dim arr， d， i&， cel As Range

Windows（"A.xlsx"）.Activate

Sheets（"C"）.Range（"A3：A" & [A3].End（4）.Row）.Interior.ColorIndex = xlNone

Windows（"B.xlsm"）.Activate

arr = Sheets（"D"）.Range（"Q8：Q15" & [Q8].End（4）.Row）

Set d = CreateObject（"scripting.dictionary"）

For i = 1 To UBound（arr）

d（Replace（arr（i， 1）， " "， ""）） = ""

Next

Erase arr： i = 1

Windows（"B.xlsx"）.Activate

With Sheets（"C"）.Range（"A3：A" & [A3].End（4）.Row）

For Each cel In Sheets（"C"）.Range（"A3：A" & [A3].End（4）.Row）

If d.exists（Replace（cel.Value， " "， ""）） Then

cel.EntireRow.Interior.ColorIndex = 6

Windows（"A.xlsm"）.Activate

cel.Offset（0， 1） = Sheets（"D"）.Range（"Q6"）

End If

Next

End With

Set d = Nothing

2.3.2多报数据筛选  在报表的制作中，常需要整合多个报表的数据，针对多项报表的数据录入，可通过时间函数与“&”符号来自定义文本，从而准确查找到当日报表。

如表2所示，该表内容为人员信息，需从其他报表中查找数据并录入，同时只留下人数大于0的行。

该功能可通过简单循环函数实现，但由于正循环会导致计算量过大，这里使用逆循环，所使用的示例代码如下：

Dim r As Integer

For r = 99 To 1 Step -1

If Sheets（"A"）.Cells（r， "F"） = 0 Then

Sheets（"B"）.Cells（r， "F"）.EntireRow.Delete

End If

Next

如表3所示，在多个Excel表中，可通过ActiveSheet.Range（""）.Value将单元格定义为数值并进行计算汇总。

①报表自动生成。在制作Excel表格的同時，每日还需制作Word文档，在制作文档的同时，还要计算每日各项产量的增减，该功能可以在Excel表中新建一个工作簿来汇总Word中所需要的数据与文字并进行计算汇总，然后通过VBA来输出Word文档，使用Set WordApp = CreateObject（"Word.Application"） 代码来生成WORD对象，使用fn$函数来指定文件名与文件生成路径，使用If函数来进行数据计算与汇总。示例代码如下：

Dim i， j As Integer

Dim WordApp As Word.Application

i = Sheets（"A"）.Range（"B8"）

j = Sheets（"A"）.Range（"B9"）

Set WordApp = CreateObject（"Word.Application"）

WordApp.Documents.Add

Sheets（"A"）.Range（"A3：D6"）.Copy

WordApp.Selection.Paste

If i > 0 Then

Sheets（"A"）.Range（"E4"）.Copy

WordApp.Selection.Paste

Else

Sheets（"A"）.Range（"E5"）.Copy

WordApp.Selection.Paste

End If

Sheets（"A"）.Range（"F4：K4"）.Copy

WordApp.Selection.Paste

If j > 0 Then

Sheets（"A"）.Range（"E4"）.Copy

WordApp.Selection.Paste

Else

Sheets（"A"）.Range（"E5"）.Copy

WordApp.Selection.Paste

End If

Sheets（"A"）.Range（"M4：N4"）.Copy

WordApp.Selection.Paste

fn$ = "C：＼Users＼jz25-1szk＼Desktop＼" & Format（Now（） - 1， "yyyy" & "年" & "mm" & "月" & "dd" & "日"） & Sheets（"A"）.Range（"c1"）WordApp.ActiveDocument.SaveAs fn$

WordApp.Quit

Set WordApp = Nothing

由于Word中的数据是由Excel表中多次复制过来的，所以格式较乱，并且报表对于格式有严格眼球，需重新进行调整，因为每日的数据格式与文字长度都是一定的，所以可以通过Word中录制宏来实现。如图2所示。

②其他。在Excel表的制作中，因数据的重叠，要通过单元格删除代码对部分单元格进行删除，并将该代码。代码如下： Sheets（"A"）.Cells.Clear

Sheets（"B"）.Cells.Clear

参考文献：

[1]李桂春.计算思维在Excel教学中的应用[J].电脑知识与技术，2019（33）.

[2]苗智雯.基于C语言的Excel文件操作研究[J].电脑编程技巧与维护，2017（07）.

[3]乔治强.基于Excel实现动态报表[J].电脑知识与技术， 2016（29）.

[4]丁红利.Excel数据透视表在高校数据处理中的应用[J].电脑知识与技术，2017（03）.

作者简介：喻靖（1990-），男，重庆人，工程师。