基于Excel VBA實(shí)現(xiàn)油田報(bào)表自動(dòng)化設(shè)計(jì)

喻靖 朱峰 夏瑞杰

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

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.

關(guān)鍵詞：VBA;Excel;數(shù)據(jù)處理;自動(dòng)化

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

中圖分類號(hào)：TP311.5? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 文章編號(hào)：1006-4311（2020）12-0193-02

1? 概況

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

2? 應(yīng)用

2.1 復(fù)制粘貼功能

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

Windows（"A.xlsm"）.Activate

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

Windows（"A.xlsm"）.Activate

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

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

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

因報(bào)表中大部分?jǐn)?shù)據(jù)由人為整理并進(jìn)行分配，在進(jìn)行一系列的公式計(jì)算后會(huì)存在數(shù)據(jù)為負(fù)的情況，但根據(jù)實(shí)際和常理該數(shù)值應(yīng)為正值，固通過(guò)If函數(shù)來(lái)對(duì)這些數(shù)據(jù)進(jìn)行整合并從新分配。示例代碼如下：

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ù)智能篩選

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

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

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多報(bào)數(shù)據(jù)篩選? 在報(bào)表的制作中，常需要整合多個(gè)報(bào)表的數(shù)據(jù)，針對(duì)多項(xiàng)報(bào)表的數(shù)據(jù)錄入，可通過(guò)時(shí)間函數(shù)與“&”符號(hào)來(lái)自定義文本，從而準(zhǔn)確查找到當(dāng)日?qǐng)?bào)表。

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

該功能可通過(guò)簡(jiǎn)單循環(huán)函數(shù)實(shí)現(xiàn)，但由于正循環(huán)會(huì)導(dǎo)致計(jì)算量過(guò)大，這里使用逆循環(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所示，在多個(gè)Excel表中，可通過(guò)ActiveSheet.Range（""）.Value將單元格定義為數(shù)值并進(jìn)行計(jì)算匯總。

①報(bào)表自動(dòng)生成。在制作Excel表格的同時(shí)，每日還需制作Word文檔，在制作文檔的同時(shí)，還要計(jì)算每日各項(xiàng)產(chǎn)量的增減，該功能可以在Excel表中新建一個(gè)工作簿來(lái)匯總Word中所需要的數(shù)據(jù)與文字并進(jìn)行計(jì)算匯總，然后通過(guò)VBA來(lái)輸出Word文檔，使用Set WordApp = CreateObject（"Word.Application"） 代碼來(lái)生成WORD對(duì)象，使用fn$函數(shù)來(lái)指定文件名與文件生成路徑，使用If函數(shù)來(lái)進(jìn)行數(shù)據(jù)計(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表中多次復(fù)制過(guò)來(lái)的，所以格式較亂，并且報(bào)表對(duì)于格式有嚴(yán)格眼球，需重新進(jìn)行調(diào)整，因?yàn)槊咳盏臄?shù)據(jù)格式與文字長(zhǎng)度都是一定的，所以可以通過(guò)Word中錄制宏來(lái)實(shí)現(xiàn)。如圖2所示。

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

Sheets（"B"）.Cells.Clear

參考文獻(xiàn)：

[1]李桂春.計(jì)算思維在Excel教學(xué)中的應(yīng)用[J].電腦知識(shí)與技術(shù)，2019（33）.

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

[3]喬治強(qiáng).基于Excel實(shí)現(xiàn)動(dòng)態(tài)報(bào)表[J].電腦知識(shí)與技術(shù)， 2016（29）.

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

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