VBA在對內廣播效果質量報表中的應用

■文/王 強

VBA在對內廣播效果質量報表中的應用

■文/王 強

本文介紹了用VBA實現報表自動化的關鍵語句和業務邏輯。使用VBA報表的制作速度是人工制作速度的兩倍以上，提高了報表制作效率。

VBA使用；報表自動化；數據中心

引言

我所在的部門是數據中心。數據中心是所有數據的匯集地點，也是眾多報表的制作地點。使用VBA能大大提高制作報表的效率，將給數據中心帶來很大幫助。同時，各直屬臺也要制作報表。若他們使用VBA，也會給他們帶來效率上的提高。

VBA（Visual Basic for Applications）是Visual Basic的一種宏語言，是微軟開發出來在其桌面應用程序中執行通用的自動化（OLE）任務的編程語言。主要能用來擴展Windows的應用程序功能，特別是Microsoft Office軟件。也可說是一種應用程式視覺化的Basic腳本。

對內效果及質量月報描述了對內效果及質量的情況，是重要的廣播報表，包含多個附件。由于該報表要抄送給多個單位，從側面說明此報表很受重視。在制作過程中，為了便于制作，我又添加了5個中間附件。過去沒有加入VBA程序時，要花費3天制作月報?，F在，順利的話，不到1天半的時間可以完成。這樣大大提高了制作報表的效率。而且用VBA時，報表不容易出錯。

1.報表制作流程

下面介紹比較有特點的VBA使用。復制和刷新透視表是比較多的操作過程。多列去重復是比較特殊的應用，是我用VBA錄制宏之后，反向得到的。后幾個介紹都是業務流程性的，操作起來比較復雜。目的是為了跟讀者分享一下VBA究竟能解決什么復雜問題。

1.1 復制工作薄的使用內容到本工作簿

workbookA.Sheets(x).UsedRange.Copy ThisWorkbook. Sheets(x).Range(“a1”)

把工作簿A中sheet x使用的內容復制到本工作簿sheetx的左上角位置。

1.2 整列復制內容到需要的位置

Sheets(x).Range(“H:H”).Copy Sheets(x1).Range(“A1”)復制sheet x的H列到sheetx1的A1單元格內容。

1.3 刷新透視表

Sheets(x).PivotTables(“ 數 據 透 視 表 x”).PivotCache. Refresh

刷新本工作簿下的透視表x。當報表中存在大量透視表，該語句可以大大提高效率和操作準確度。

1.4 多列去重復

乳酸鏈球菌素(Nisin)是由乳酸鏈球菌合成的一種多肽抗菌素，能有效抑制革蘭氏陽性菌，如金黃色葡糖球菌、溶血性鏈球菌、單增李斯特菌、肉毒桿菌，是國際上公認的天然防腐劑。研究表明[11]，在不影響色澤和防腐的情況下，添加Nisin可使亞硝酸鹽的含量由原來的150 mg/kg降到40 mg/kg。若添加0.2 g/kg Nisin, 則亞硝酸鹽的添加量減少到 0.04 g/kg，香腸中的菌落總數可降低到3200 cfu/g，抑菌效果明顯。

Sheets(“x).Range(“$A$1:$C$65535”).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

A、B和C三列聯合去重復。

Sheets(X).Range(“$A$1:$D$65535”).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes

A、B、C和D四列聯合去重復。

去重復也是使用比較多的功能。這個語句我是通過錄制宏，得到代碼，再調試得到的。這種方法值得學習。

1.5 檢查可聽率對比表中的數據劇烈變化

程序的一個重要內容是檢查可聽率對比表中的數據是否存在劇烈變化。所謂劇烈變化檢查就是，檢查本月的可聽率與上月相比是否下降了10%以上（含10%），還有該前端設備本月的可聽率是否為0（前端設備未收測）。檢查的內容如表1所示。檢查的表格還有按地區劃分的節目可聽率對比表和按地區劃分的語言可聽率對比表。

表1 按地區分的前端設備可聽率對比表

從第一條記錄開始檢查表1。先判斷地區中是否存在前端設備，如果不存在前端設備則跳轉到下一個地區。如果存在前端設備，則判斷該前端設備的本月可聽率是否為0，為0則記錄下來“該前端設備本月未收測”，不為0則檢查本月的可聽率是否下降了10%。若可聽率下降了10%，則記錄下來“該前端設備的可聽率下降達到10%”。檢查本表格中是否所有的記錄都完成了。沒有完成，則檢查下一條記錄。所有的情況都記錄在一個名為《出現的問題及回答》的文件中。記錄完成后，還要通過查看直屬臺月報數據和詢問直屬臺的方法了解可聽率出現劇烈變化的原因，并把原因記錄在該文件中，以備月報之用。具體流程如圖1所示。

圖1 可聽率劇烈變化的檢查方法

在多報表導入前，判斷其報表存在性是必要條件。若報表不存在，則程序自動會提出錯誤信息。但這種錯誤信息一般不能直接指出出現的問題。所以，需要自己寫代碼，代碼中編寫提示信息。這樣可以直接指出問題，如圖2所示。首先要檢查要導入的文件是否存在，若存在，則查找下一個文件。否則，記錄不存在的文件名，給出提示信息，跳出程序。待重新準備好相應的文件再次運行程序。該程序是遇到問題直接中斷，而沒有把所有問題都找出來。這種設計的考慮是一般準備好文件后，頂多有一兩個文件漏準備。中斷之后，回來快速準備文件，可以快速運行。如果真是多個文件漏準備，熟練的報表制作者運行一次中斷后就能知道自己的問題。

圖2 多個報表導入前的準備程序

1.7 發射臺頻率匯總

表2發射臺頻率匯總表分別記錄每個發射臺（發射臺存在發射臺X型和發射臺Y型兩種類型）每月播出中短波和調頻的頻率數目。表格要求中央直屬發射臺記錄于上半區，地方發射臺記錄于下半區。

表2 發射臺頻率匯總表

從已獲得的列表中比對表2的發射臺名稱，若發射臺頻率匯總表中存在該發射臺名稱，則在對應位置填寫頻率數。若不存在該發射臺名稱，當獲得的發射臺屬于是發射臺X型時，在最上面一行插入相應信息；當獲得的是發射臺Y型時，在最后一行插入相應信息。

1.8 按地區劃分可聽率統計表

表3為可聽率統計表。程序要按照行政區，把相應的記錄放置到對應行政區的工作薄中。具體的做法是，先按照行政區對可聽率統計表進行排序。再一次性把記錄放置到相應工作簿。

可聽率是指可聽度大于等于3分的次數與總收聽次數之比，用百分數表示。在這里，可聽率等于3分以上頻次和總收測頻次之比。每個頻率收測一次，叫做一頻次。

表3 可聽率統計表

2.結束語

使用VBA制作Excel報表將會給我們帶來效率上的大幅提高，而且減少了出錯的可能性。希望有更多的報表制作者都嘗試使用VBA，體驗VBA帶來的改變。

[1]李曉玫等.Excel中的VBA程序設計[J].四川師范大學學報，2004，27（4）：423-424.

[2]任小龍等.VBA程序接口在儀表設計中的應用[J].化工自動化及儀表，2016，43（2）：145-146.

（作者單位：國家新聞出版廣電總局監測數據處理中心）

TP311.1

A

1671-0134（2017）12-109-03

10.19483/j.cnki.11-4653/n.2017.03.026