利用Excel電子表格統計廣播電視停播率

朱家強

（貴州省廣播電影電視技術管理中心，貴州 貴陽 550002）

0 概述

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

1 初始化

1.1 Excel的初始化

對Excel進行必要的設置。包括啟用宏、自動計算、時間格式等。

1.2 參數保護和數據容錯

對關鍵的不讓操作員隨意改動的文件、工作簿、工作表單、單元格等進行密碼保護，對錄入數據單元的數值、字符類型進行容錯提示處理。

2 停播率數據目錄分類

停播率數據目錄結構如圖表1所示。包括年度（2004年至2013年），播出系統類型，年度統計、評分數據、排序評比結果和年度播出情況報告。綜合評分包括：停播率、運行指標、綜合管理等。

圖1 停播率數據目錄結構

3 統計方法規范

3.1 停播率計算公式

根據《廣播電視停播統計方法規范》（GYT264-2012），節目播出總時長為某一統計時段內，每套節目按既定編排的應播時間。對于多套節目按照各套節目播出時長累計計算。停播時長為某一統計時段內的節目停播累計時長。對多套節目按照各套節目停播時長累計計算。停播率為某一統計時段內所有播出節目停播時長與播出總時長的比值，單位為秒/每百小時[1]，見式(1)。

4 統計匯總

（1）參照國家和省的有關廣播電視技術維護管理辦法和工作實際要求進行綜合評定。

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

（3）月停播事故登記表內容包括：單位、頻率/頻道、本月應播時間（小時）、臺內停播（性質、分、秒）、臺外停播（性質、分、秒）、上報日期、備注。其中，備注內容包括停播事故詳細原因、計劃停播原因等。（技術指標改為按月單獨統計）。季度匯總表主要對本季3個月的數據及備注內容進行匯總，同樣，年度匯總表對12個月的數據及備注內容進行匯總。

（4）季度匯總對數據表單中的對應單元求和，如：

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

對備注欄加入編號進行合并，如：

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

而年度統計要求跨文件或跨目錄進行求和，如：

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

（5）停播時間統計。按秒、分鐘進行求和，再統一轉化為秒進行求和進行停播率計算。如一季度小關發射臺的臺外停播的分鐘數為：

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

式中C34為總播出時間（小時），F34為總停播（分），G34為總停播（秒）。在特殊情況下，當總停播時間為0時不進行計算，則用“/”取代。

（6）綜合評分表

綜合評分表季度統計工作簿文件包含有月（季度）數據表單，年度統計工作簿文件包含年統、臺（單位）匯總、分類匯總、總評分和停播率排序等表單[3]。例如，全省CMMB各站點綜合評分表如表1所示。

表1 全省CMMB各站點綜合評分表

（7）在統計過程中，會經常碰到時間單位“時、分、秒”之間的換算，特別是用當年數據與上年數據進行比較時，由于采用不同公式會產生小細節的誤差。對于大單位化小單位比較簡單，可直接乘以進制系數60得到結果，但小單位換算成大單位就要用到除法和取整INT（）函數等。例如：

當年停播率比上年下降多少分鐘的計算：

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

當年停播率比上年下降多少秒鐘數值的計算：

=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)))

在該年度與上年度的比較計算中，加入了對其結果正值和負值的判斷而采取不同的計算方法，并對負值用紅色顯示。考慮到后面自動生成word文檔的需要，加入了“下降”、“相同”和“上升”的判斷語句，并將其放在表的后面便于生成的word文檔所引用。例如：本年度臺外停播時間與上年比較的單元區如表2所示，其語句為：

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

表2 本年度臺外停播時間與上年比較的單元區

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

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

表3

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

Text（2011，C21）ffamp;年中短波發射系統共播出ffamp;text（89691.81，D24）ffamp;小時，與ffamp;text（2010，Q100）ffamp;"年相比多播出ffamp;text（3468.65，R24）ffamp;小時，

在word中顯示為：

“2011年中短波發射系統共播出89691.81小時，與2010年相比多播出3468.6小時。”

6 評分辦法

對于不同種類、不同條件單位的評分辦法共有10余項標準。例如：對大功率發射設備且有檢測,有備機的情況下，臺內停播率的評分標準就是其中之一，如表3所示。

根據引用相應的評分"標準"表格文件的評分數據進行評分。例如停播率評分：

C6=IF(B6＜='E:標準[評分.xls]Sheet1'!$C$109,'E:標準[評分.xls]Sheet1'!$D$109,…… ,IF(B6＞'E:標準[評分.xls]Sheet1'!$C$116,'E:標準[評分.xls]Sheet1'!$D$116)))))+100。

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

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

年度統計、評分、排序圖表及說明略。

7 統計報表編制技巧

（1）將常數、參數、標準等放在根目錄上，可盡量縮短調用、引用語句長度，同時也便于語句的修改、調試和閱讀。

（2）在停播率事件錄入的表格中加入不同類型屬性的索引關鍵詞，便于分類和統計。

（3）在修改（增、減）每一個表單數據時，為保證表單數據的關聯性，需要同時打開所有關聯的表格文件。

8 擴展與升級

在原有系統基礎上，可不斷增加符合當地實際的考核標準和管理規則進行補充和完善。全方位的可視化體驗的編程和維護體驗，超過任何高級語言之對應開發環境。

9 先進性與實效性

根據以后的工作實際需要可利用EXCEL成熟的數據庫接口和外圍軟件進行功能擴充，界面美化，實現網絡、異地、遠程管理和維護。

雖然這里Excel的應用未采取網絡、B/S模式、云應用等技術，但從另一方面來講更體現出該統計表格的安全性，維護和升級的便捷性。無論是當今后的管理標準修改還是臺站增減變化，都可非常方便地對該統計表格進行維護和升級。

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

11 結束語

利用Excel電子表格進行匯總統計，都是預先將計算公式、調用、引用、宏等設定在相對應的單元格，在進行播出停播故障情況原始數據輸入工作表單的同時，所需的統計數據即可自動生成，既快速，又準確。這套電子表格提升了我們的日常工作效率，簡化了數據處理流程，自動化辦公已給我們工作帶來實效。

該統計軟件自從2003使用至今，經過不斷更新完善，使用情況良好，大大減輕工作強度。

［1］GYT264-2012廣播電視停播統計方法規范[S].

［2］陳靜珊.利用Excel電子表格制作《廣播電視檢測專報》統計表[J].有線電視技術，2012，02（266）：42-44.

［3］韋桂光，李曉楓.廣播電視停播率及運行指標管理系統[J].廣播與電視技術，1996.12：57-58.