喻靖 朱峰 夏瑞杰

摘要： VBA全稱Visual Basic for Applications，可以實現(xiàn)EXCELL表格與WORD文檔等數(shù)據(jù)編寫、篩選、處理等一系列功能。在實際應用中可以提高文檔制作效率，降低數(shù)據(jù)處理過程中的錯誤率。該設計利用其便捷的功能，針對某油田復雜的報表進行自動化改良，其中實現(xiàn)了數(shù)據(jù)的自動計算、自能篩選，報表的一鍵生成等功能，最終實現(xiàn)報表的自動化制作。

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;數(shù)據(jù)處理;自動化

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

中圖分類號：TP311.5? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 文獻標識碼：A? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 文章編號：1006-4311（2020）12-0193-02

1? 概況

某油田系遼東區(qū)塊油氣集輸中心，因而所涉及數(shù)據(jù)多且雜，日常涉及到的報表有20余項，有近千項的數(shù)據(jù)需要人為錄入（報表大致框架結構及流程如圖1所示），為響應集團公司提出的降本增效方針，故此，該項目應用Office文檔中的宏功能對報日常表進行升級，實現(xiàn)一鍵式的報表輸出，省時省力。

2? 應用

2.1 復制粘貼功能

本項目通過VBA中的復制粘貼代碼實現(xiàn)基本數(shù)據(jù)的粘貼，因報表內所涉及到的工作表較多，選擇使用的代碼示例如下：

Windows（"A.xlsm"）.Activate

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

Windows（"A.xlsm"）.Activate

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

并通過xlPasteValues函數(shù)指定為文本復制，以避免將模板中的公式進行粘貼影響報表的正確性。

2.2 數(shù)據(jù)整合功能

因報表中大部分數(shù)據(jù)由人為整理并進行分配，在進行一系列的公式計算后會存在數(shù)據(jù)為負的情況，但根據(jù)實際和常理該數(shù)值應為正值，固通過If函數(shù)來對這些數(shù)據(jù)進行整合并從新分配。示例代碼如下：

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 數(shù)據(jù)智能篩選

在報表中存在許多每日數(shù)據(jù)不同，并需要人為進行更改等數(shù)據(jù)，而這部分數(shù)據(jù)的錄入在報表的制作中工作量較大，并占用了大量的制作時間。

2.3.1單表數(shù)據(jù)篩選? 以每日油井計量數(shù)據(jù)為例，如表1所示，該表為日計量報表，每日至少需要填報30口，在填報的過程中需要找到當日的計量井并對該行的日期，標記，產(chǎn)量數(shù)據(jù)，顏色進行更改。要實現(xiàn)這項工作的自動化，可以通過創(chuàng)建字典對象以及For循環(huán)函數(shù)來進行數(shù)據(jù)對比和篩選。根據(jù)篩選結果來對數(shù)據(jù)進行所需要的更改。

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多報數(shù)據(jù)篩選? 在報表的制作中，常需要整合多個報表的數(shù)據(jù)，針對多項報表的數(shù)據(jù)錄入，可通過時間函數(shù)與“&”符號來自定義文本，從而準確查找到當日報表。

如表2所示，該表內容為人員信息，需從其他報表中查找數(shù)據(jù)并錄入，同時只留下人數(shù)大于0的行。

該功能可通過簡單循環(huán)函數(shù)實現(xiàn)，但由于正循環(huán)會導致計算量過大，這里使用逆循環(huán)，所使用的示例代碼如下：

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將單元格定義為數(shù)值并進行計算匯總。

①報表自動生成。在制作Excel表格的同時，每日還需制作Word文檔，在制作文檔的同時，還要計算每日各項產(chǎn)量的增減，該功能可以在Excel表中新建一個工作簿來匯總Word中所需要的數(shù)據(jù)與文字并進行計算匯總，然后通過VBA來輸出Word文檔，使用Set WordApp = CreateObject（"Word.Application"） 代碼來生成WORD對象，使用fn$函數(shù)來指定文件名與文件生成路徑，使用If函數(shù)來進行數(shù)據(jù)計算與匯總。示例代碼如下：

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中的數(shù)據(jù)是由Excel表中多次復制過來的，所以格式較亂，并且報表對于格式有嚴格眼球，需重新進行調整，因為每日的數(shù)據(jù)格式與文字長度都是一定的，所以可以通過Word中錄制宏來實現(xiàn)。如圖2所示。

②其他。在Excel表的制作中，因數(shù)據(jù)的重疊，要通過單元格刪除代碼對部分單元格進行刪除，并將該代碼。代碼如下： Sheets（"A"）.Cells.Clear

Sheets（"B"）.Cells.Clear

參考文獻：

[1]李桂春.計算思維在Excel教學中的應用[J].電腦知識與技術，2019（33）.

[2]苗智雯.基于C語言的Excel文件操作研究[J].電腦編程技巧與維護，2017（07）.

[3]喬治強.基于Excel實現(xiàn)動態(tài)報表[J].電腦知識與技術， 2016（29）.

[4]丁紅利.Excel數(shù)據(jù)透視表在高校數(shù)據(jù)處理中的應用[J].電腦知識與技術，2017（03）.

作者簡介：喻靖（1990-），男，重慶人，工程師。