利用Excel電子表格統(tǒng)計(jì)廣播電視停播率

朱家強(qiáng)

（貴州省廣播電影電視技術(shù)管理中心，貴州 貴陽(yáng) 550002）

0 概述

本文主要闡述利用Microsoft Excel電子表格軟件的公式、函數(shù)、宏調(diào)用、交叉引用等功能對(duì)停播率等錄入數(shù)據(jù)進(jìn)行計(jì)算、分類、統(tǒng)計(jì)。對(duì)省廣播中心、省電視中心、16個(gè)省局直屬臺(tái)、省網(wǎng)絡(luò)公司前端及干線、全省50余個(gè)CMMB單頻網(wǎng)站點(diǎn)、9個(gè)市（州）1KW以上40個(gè)發(fā)射臺(tái)及部分播控中心近120個(gè)播出單位的停播率等數(shù)據(jù)進(jìn)行年度統(tǒng)計(jì)，對(duì)各播出單位的播出情況進(jìn)行評(píng)分和排名，為年度考核提供播出情況基本數(shù)據(jù)，生成播出情況報(bào)告。

1 初始化

1.1 Excel的初始化

對(duì)Excel進(jìn)行必要的設(shè)置。包括啟用宏、自動(dòng)計(jì)算、時(shí)間格式等。

1.2 參數(shù)保護(hù)和數(shù)據(jù)容錯(cuò)

對(duì)關(guān)鍵的不讓操作員隨意改動(dòng)的文件、工作簿、工作表單、單元格等進(jìn)行密碼保護(hù)，對(duì)錄入數(shù)據(jù)單元的數(shù)值、字符類型進(jìn)行容錯(cuò)提示處理。

2 停播率數(shù)據(jù)目錄分類

停播率數(shù)據(jù)目錄結(jié)構(gòu)如圖表1所示。包括年度（2004年至2013年），播出系統(tǒng)類型，年度統(tǒng)計(jì)、評(píng)分?jǐn)?shù)據(jù)、排序評(píng)比結(jié)果和年度播出情況報(bào)告。綜合評(píng)分包括：停播率、運(yùn)行指標(biāo)、綜合管理等。

圖1 停播率數(shù)據(jù)目錄結(jié)構(gòu)

3 統(tǒng)計(jì)方法規(guī)范

3.1 停播率計(jì)算公式

根據(jù)《廣播電視停播統(tǒng)計(jì)方法規(guī)范》（GYT264-2012），節(jié)目播出總時(shí)長(zhǎng)為某一統(tǒng)計(jì)時(shí)段內(nèi)，每套節(jié)目按既定編排的應(yīng)播時(shí)間。對(duì)于多套節(jié)目按照各套節(jié)目播出時(shí)長(zhǎng)累計(jì)計(jì)算。停播時(shí)長(zhǎng)為某一統(tǒng)計(jì)時(shí)段內(nèi)的節(jié)目停播累計(jì)時(shí)長(zhǎng)。對(duì)多套節(jié)目按照各套節(jié)目停播時(shí)長(zhǎng)累計(jì)計(jì)算。停播率為某一統(tǒng)計(jì)時(shí)段內(nèi)所有播出節(jié)目停播時(shí)長(zhǎng)與播出總時(shí)長(zhǎng)的比值，單位為秒/每百小時(shí)[1]，見(jiàn)式(1)。

4 統(tǒng)計(jì)匯總

（1）參照國(guó)家和省的有關(guān)廣播電視技術(shù)維護(hù)管理辦法和工作實(shí)際要求進(jìn)行綜合評(píng)定。

（2）按不同屬性、類別進(jìn)行劃分。①傳輸和播出的單位（部門），②地區(qū)，③中（短）波、調(diào)頻、電視、CMMB、廣播（電視）中心、有線光纜（前端）、衛(wèi)星上行及微波。

（3）月停播事故登記表內(nèi)容包括：?jiǎn)挝弧㈩l率/頻道、本月應(yīng)播時(shí)間（小時(shí)）、臺(tái)內(nèi)停播（性質(zhì)、分、秒）、臺(tái)外停播（性質(zhì)、分、秒）、上報(bào)日期、備注。其中，備注內(nèi)容包括停播事故詳細(xì)原因、計(jì)劃停播原因等。（技術(shù)指標(biāo)改為按月單獨(dú)統(tǒng)計(jì)）。季度匯總表主要對(duì)本季3個(gè)月的數(shù)據(jù)及備注內(nèi)容進(jìn)行匯總，同樣，年度匯總表對(duì)12個(gè)月的數(shù)據(jù)及備注內(nèi)容進(jìn)行匯總。

（4）季度匯總對(duì)數(shù)據(jù)表單中的對(duì)應(yīng)單元求和，如：

L31=四月!I31+五月!I31+六月!I31[2]；

對(duì)備注欄加入編號(hào)進(jìn)行合并，如：

Q31="①"ffamp;四月!N31ffamp;"②"ffamp;五月!N31ffamp;"③"ffamp;六月!N31”；

而年度統(tǒng)計(jì)要求跨文件或跨目錄進(jìn)行求和，如：

J5='D:2011直屬臺(tái)[一季.xls]一季度'!$M5+……+'D:2011直屬臺(tái)[四季.xls]四季度'!$M5。

（5）停播時(shí)間統(tǒng)計(jì)。按秒、分鐘進(jìn)行求和，再統(tǒng)一轉(zhuǎn)化為秒進(jìn)行求和進(jìn)行停播率計(jì)算。如一季度小關(guān)發(fā)射臺(tái)的臺(tái)外停播的分鐘數(shù)為：

D34=IF(C34＞0,(F34*60+G34)/C34*100,"/")

式中C34為總播出時(shí)間（小時(shí)），F(xiàn)34為總停播（分），G34為總停播（秒）。在特殊情況下，當(dāng)總停播時(shí)間為0時(shí)不進(jìn)行計(jì)算，則用“/”取代。

（6）綜合評(píng)分表

綜合評(píng)分表季度統(tǒng)計(jì)工作簿文件包含有月（季度）數(shù)據(jù)表單，年度統(tǒng)計(jì)工作簿文件包含年統(tǒng)、臺(tái)（單位）匯總、分類匯總、總評(píng)分和停播率排序等表單[3]。例如，全省CMMB各站點(diǎn)綜合評(píng)分表如表1所示。

表1 全省CMMB各站點(diǎn)綜合評(píng)分表

（7）在統(tǒng)計(jì)過(guò)程中，會(huì)經(jīng)常碰到時(shí)間單位“時(shí)、分、秒”之間的換算，特別是用當(dāng)年數(shù)據(jù)與上年數(shù)據(jù)進(jìn)行比較時(shí)，由于采用不同公式會(huì)產(chǎn)生小細(xì)節(jié)的誤差。對(duì)于大單位化小單位比較簡(jiǎn)單，可直接乘以進(jìn)制系數(shù)60得到結(jié)果，但小單位換算成大單位就要用到除法和取整INT（）函數(shù)等。例如：

當(dāng)年停播率比上年下降多少分鐘的計(jì)算：

=IF(((C46*60+F46)-(C45*60+F45))＞=0,INT(((C46*60+F46)-(C45*60+F45))/60),-INT(-((C46*60+F46)-(C45*60+F45))/60))

當(dāng)年停播率比上年下降多少秒鐘數(shù)值的計(jì)算：

=IF(((C46*60+F46)-(C45*60+F45))＞=0,((C46*60+F46)-(C45*60+F45))-INT(((C46*60+F46)-(C45*60+F45))/60)*60,INT(-((C46*60+F46)-(C45*60+F45))/60)*60+((C46*60+F46)-(C45*60+F45)))

在該年度與上年度的比較計(jì)算中，加入了對(duì)其結(jié)果正值和負(fù)值的判斷而采取不同的計(jì)算方法，并對(duì)負(fù)值用紅色顯示?？紤]到后面自動(dòng)生成word文檔的需要，加入了“下降”、“相同”和“上升”的判斷語(yǔ)句，并將其放在表的后面便于生成的word文檔所引用。例如：本年度臺(tái)外停播時(shí)間與上年比較的單元區(qū)如表2所示，其語(yǔ)句為：

=IF(F47+C47＞0,“下降”,IF(F47+C47=0,“相同”,IF(F47+C47＜0,“上升”)))

表2 本年度臺(tái)外停播時(shí)間與上年比較的單元區(qū)

5 Word文檔（年度播出情況）的生成

在Word中調(diào)用Excel數(shù)據(jù)有兩種方式，一種是OLE(對(duì)象的鏈接和嵌入),另一種是DDE(動(dòng)態(tài)數(shù)據(jù)交換)。OLE用于將Excel數(shù)據(jù)直接嵌入到Word文檔中,可采取可視化交互進(jìn)行操作，而DDE是一個(gè)協(xié)議，可通過(guò)應(yīng)用程序的VBA編程讓一個(gè)DDE"通道"連續(xù)地進(jìn)行數(shù)據(jù)自動(dòng)交換，不具備可視化交互性，適用于整套或部分報(bào)表的引用。在這里我們選擇第一種方式，比較直觀，可視化編輯效果好。

表3

操作方法：首先將完整的word文檔模板內(nèi)容按行復(fù)制到一個(gè)Excel工作簿的同一列上，然后對(duì)這一列的每個(gè)單元格進(jìn)行相關(guān)數(shù)據(jù)引用，即用TEXT(Value,Format_test)函數(shù)對(duì)引用的數(shù)值進(jìn)行字符轉(zhuǎn)換，用Textffamp;Text方法對(duì)文字進(jìn)行拼接，分別對(duì)各單元進(jìn)行語(yǔ)句編程，完成文檔所有行的文字編輯。再將Excel中已編輯好的每行表格單元內(nèi)容復(fù)制后，在word文檔中的對(duì)應(yīng)位置逐行采用選擇性粘貼，就可完成一篇word文檔報(bào)告(因Excel的版本不同，上述操作會(huì)有所不同，具體操作參見(jiàn)百度搜索“word引用excel數(shù)據(jù)”)。例如對(duì)一個(gè)單元格的文檔內(nèi)容進(jìn)行拼接如下：

Text（2011，C21）ffamp;年中短波發(fā)射系統(tǒng)共播出ffamp;text（89691.81，D24）ffamp;小時(shí)，與ffamp;text（2010，Q100）ffamp;"年相比多播出ffamp;text（3468.65，R24）ffamp;小時(shí)，

在word中顯示為：

“2011年中短波發(fā)射系統(tǒng)共播出89691.81小時(shí)，與2010年相比多播出3468.6小時(shí)?！?/p>

6 評(píng)分辦法

對(duì)于不同種類、不同條件單位的評(píng)分辦法共有10余項(xiàng)標(biāo)準(zhǔn)。例如：對(duì)大功率發(fā)射設(shè)備且有檢測(cè),有備機(jī)的情況下，臺(tái)內(nèi)停播率的評(píng)分標(biāo)準(zhǔn)就是其中之一，如表3所示。

根據(jù)引用相應(yīng)的評(píng)分"標(biāo)準(zhǔn)"表格文件的評(píng)分?jǐn)?shù)據(jù)進(jìn)行評(píng)分。例如停播率評(píng)分：

C6=IF(B6＜='E:標(biāo)準(zhǔn)[評(píng)分.xls]Sheet1'!$C$109,'E:標(biāo)準(zhǔn)[評(píng)分.xls]Sheet1'!$D$109,…… ,IF(B6＞'E:標(biāo)準(zhǔn)[評(píng)分.xls]Sheet1'!$C$116,'E:標(biāo)準(zhǔn)[評(píng)分.xls]Sheet1'!$D$116)))))+100。

其 中 100為 基 本 分 。 IF（logical_test,value_if_true,value,value_if_false）語(yǔ)句的功能為：判斷是否滿足某個(gè)條件，如果滿足返回一個(gè)值，如果不滿足就返回另一個(gè)值。該條語(yǔ)句引用了跨文件的宏調(diào)用和多層嵌套調(diào)用，對(duì)評(píng)分表中的每個(gè)值進(jìn)行比較得到對(duì)應(yīng)評(píng)分值。用同樣的方法計(jì)算出各季度的總秒值。

備注欄拼接匯總。例如，一季度某臺(tái)某頻率備注欄匯總（自動(dòng)識(shí)別為字符型）方法是：Q52="①"ffamp;一月!N52ffamp;"②"ffamp;二月!N52ffamp;"③"ffamp;三月!N52

年度統(tǒng)計(jì)、評(píng)分、排序圖表及說(shuō)明略。

7 統(tǒng)計(jì)報(bào)表編制技巧

（1）將常數(shù)、參數(shù)、標(biāo)準(zhǔn)等放在根目錄上，可盡量縮短調(diào)用、引用語(yǔ)句長(zhǎng)度，同時(shí)也便于語(yǔ)句的修改、調(diào)試和閱讀。

（2）在停播率事件錄入的表格中加入不同類型屬性的索引關(guān)鍵詞，便于分類和統(tǒng)計(jì)。

（3）在修改（增、減）每一個(gè)表單數(shù)據(jù)時(shí)，為保證表單數(shù)據(jù)的關(guān)聯(lián)性，需要同時(shí)打開所有關(guān)聯(lián)的表格文件。

8 擴(kuò)展與升級(jí)

在原有系統(tǒng)基礎(chǔ)上，可不斷增加符合當(dāng)?shù)貙?shí)際的考核標(biāo)準(zhǔn)和管理規(guī)則進(jìn)行補(bǔ)充和完善。全方位的可視化體驗(yàn)的編程和維護(hù)體驗(yàn)，超過(guò)任何高級(jí)語(yǔ)言之對(duì)應(yīng)開發(fā)環(huán)境。

9 先進(jìn)性與實(shí)效性

根據(jù)以后的工作實(shí)際需要可利用EXCEL成熟的數(shù)據(jù)庫(kù)接口和外圍軟件進(jìn)行功能擴(kuò)充，界面美化，實(shí)現(xiàn)網(wǎng)絡(luò)、異地、遠(yuǎn)程管理和維護(hù)。

雖然這里Excel的應(yīng)用未采取網(wǎng)絡(luò)、B/S模式、云應(yīng)用等技術(shù)，但從另一方面來(lái)講更體現(xiàn)出該統(tǒng)計(jì)表格的安全性，維護(hù)和升級(jí)的便捷性。無(wú)論是當(dāng)今后的管理標(biāo)準(zhǔn)修改還是臺(tái)站增減變化，都可非常方便地對(duì)該統(tǒng)計(jì)表格進(jìn)行維護(hù)和升級(jí)。

世界上第一張電子表格軟件（Lotus123）誕生于1979年，而Microsoft Excel忠實(shí)地繼承了Lotus123基本功能和操作習(xí)慣。直到Excel 2010，微軟始終沒(méi)有忘記做到與Lotus1-2-3的兼容。直觀的界面、出色的計(jì)算功能和圖表工具，再加上成功的市場(chǎng)營(yíng)銷，使Excel成為最流行的數(shù)據(jù)處理軟件。

11 結(jié)束語(yǔ)

利用Excel電子表格進(jìn)行匯總統(tǒng)計(jì)，都是預(yù)先將計(jì)算公式、調(diào)用、引用、宏等設(shè)定在相對(duì)應(yīng)的單元格，在進(jìn)行播出停播故障情況原始數(shù)據(jù)輸入工作表單的同時(shí)，所需的統(tǒng)計(jì)數(shù)據(jù)即可自動(dòng)生成，既快速，又準(zhǔn)確。這套電子表格提升了我們的日常工作效率，簡(jiǎn)化了數(shù)據(jù)處理流程，自動(dòng)化辦公已給我們工作帶來(lái)實(shí)效。

該統(tǒng)計(jì)軟件自從2003使用至今，經(jīng)過(guò)不斷更新完善，使用情況良好，大大減輕工作強(qiáng)度。

［1］GYT264-2012廣播電視停播統(tǒng)計(jì)方法規(guī)范[S].

［2］陳靜珊.利用Excel電子表格制作《廣播電視檢測(cè)專報(bào)》統(tǒng)計(jì)表[J].有線電視技術(shù)，2012，02（266）：42-44.

［3］韋桂光，李曉楓.廣播電視停播率及運(yùn)行指標(biāo)管理系統(tǒng)[J].廣播與電視技術(shù)，1996.12：57-58.