■文/王 強(qiáng)

VBA在對(duì)內(nèi)廣播效果質(zhì)量報(bào)表中的應(yīng)用

■文/王 強(qiáng)

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

VBA使用；報(bào)表自動(dòng)化；數(shù)據(jù)中心

引言

我所在的部門(mén)是數(shù)據(jù)中心。數(shù)據(jù)中心是所有數(shù)據(jù)的匯集地點(diǎn)，也是眾多報(bào)表的制作地點(diǎn)。使用VBA能大大提高制作報(bào)表的效率，將給數(shù)據(jù)中心帶來(lái)很大幫助。同時(shí)，各直屬臺(tái)也要制作報(bào)表。若他們使用VBA，也會(huì)給他們帶來(lái)效率上的提高。

VBA（Visual Basic for Applications）是Visual Basic的一種宏語(yǔ)言，是微軟開(kāi)發(fā)出來(lái)在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化（OLE）任務(wù)的編程語(yǔ)言。主要能用來(lái)擴(kuò)展Windows的應(yīng)用程序功能，特別是Microsoft Office軟件。也可說(shuō)是一種應(yīng)用程式視覺(jué)化的Basic腳本。

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

1.報(bào)表制作流程

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

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

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

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

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

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

1.3 刷新透視表

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

刷新本工作簿下的透視表x。當(dāng)報(bào)表中存在大量透視表，該語(yǔ)句可以大大提高效率和操作準(zhǔn)確度。

1.4 多列去重復(fù)

乳酸鏈球菌素(Nisin)是由乳酸鏈球菌合成的一種多肽抗菌素，能有效抑制革蘭氏陽(yáng)性菌，如金黃色葡糖球菌、溶血性鏈球菌、單增李斯特菌、肉毒桿菌，是國(guó)際上公認(rèn)的天然防腐劑。研究表明[11]，在不影響色澤和防腐的情況下，添加Nisin可使亞硝酸鹽的含量由原來(lái)的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)合去重復(fù)。

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

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

去重復(fù)也是使用比較多的功能。這個(gè)語(yǔ)句我是通過(guò)錄制宏，得到代碼，再調(diào)試得到的。這種方法值得學(xué)習(xí)。

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

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

表1 按地區(qū)分的前端設(shè)備可聽(tīng)率對(duì)比表

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

圖1 可聽(tīng)率劇烈變化的檢查方法

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

圖2 多個(gè)報(bào)表導(dǎo)入前的準(zhǔn)備程序

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

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

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

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

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

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

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

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

2.結(jié)束語(yǔ)

使用VBA制作Excel報(bào)表將會(huì)給我們帶來(lái)效率上的大幅提高，而且減少了出錯(cuò)的可能性。希望有更多的報(bào)表制作者都嘗試使用VBA，體驗(yàn)VBA帶來(lái)的改變。

[1]李曉玫等.Excel中的VBA程序設(shè)計(jì)[J].四川師范大學(xué)學(xué)報(bào)，2004，27（4）：423-424.

[2]任小龍等.VBA程序接口在儀表設(shè)計(jì)中的應(yīng)用[J].化工自動(dòng)化及儀表，2016，43（2）：145-146.

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

TP311.1

A

1671-0134（2017）12-109-03

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