利用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.