■文/王 強

VBA在對內(nèi)廣播效果質(zhì)量報表中的應用

■文/王 強

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

VBA使用；報表自動化；數(shù)據(jù)中心

引言

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

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

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

1.報表制作流程

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

1.1 復制工作薄的使用內(nèi)容到本工作簿

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

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

1.2 整列復制內(nèi)容到需要的位置

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

1.3 刷新透視表

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

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

1.4 多列去重復

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

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

A、B和C三列聯(lián)合去重復。

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

A、B、C和D四列聯(lián)合去重復。

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

1.5 檢查可聽率對比表中的數(shù)據(jù)劇烈變化

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

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

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

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

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

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

1.7 發(fā)射臺頻率匯總

表2發(fā)射臺頻率匯總表分別記錄每個發(fā)射臺（發(fā)射臺存在發(fā)射臺X型和發(fā)射臺Y型兩種類型）每月播出中短波和調(diào)頻的頻率數(shù)目。表格要求中央直屬發(fā)射臺記錄于上半?yún)^(qū)，地方發(fā)射臺記錄于下半?yún)^(qū)。

表2 發(fā)射臺頻率匯總表

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

1.8 按地區(qū)劃分可聽率統(tǒng)計表

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

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

表3 可聽率統(tǒng)計表

2.結(jié)束語

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

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

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

（作者單位：國家新聞出版廣電總局監(jiān)測數(shù)據(jù)處理中心）

TP311.1

A

1671-0134（2017）12-109-03

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