PowerQuery在卫星广播电视播出数据统计分析的应用

国家广播电视总局无线电台管理局 郭小鹏

广播电视统计工作是推进广播电视和网络视听行业治理体系和治理能力现代化的一项基础性综合性工作。国家广播电视总局2020年重新修订发布了《广播电视和网络视听统计调查制度》，增加了广播电视卫星地球站传输系统等指标，对卫星广播电视播出数据统计工作提出了更高的要求。

1 卫星广播电视播出数据统计方法

无线电台管理局主要有甲地球站、乙地球站、丙地球站等三座地球站上行中央和省级广播电视卫星节目信号。三座地球站互为实时备份上行，上行系统均接入地球站自动调度系统，可实现上行节目的无缝代播，克服系统检修、雨雪衰等原因造成的节目停传，保证24h安全可靠传输。

目前，无线电台管理局卫星广播电视播出数据的统计方法是，三座地球站分别按月统计本站上行节目统计表和节目传输时间统计表，综合三座地球站上报的节目统计表和传输时间统计表，使用Excel表格公式得出全局卫星广播电视播出节目情况和传输时间统计情况。在实际使用过程中存在以下问题：第一、信息存在大量冗余。例如某节目流1在甲地球站主播、乙地球站备播，同时统计在甲、乙地球站的上行节目统计表和传输时间统计表中，都需要进行填写、维护。第二、信息易出现冲突。由于同一信息同时存在多个无关联的表中，容易出现相互矛盾的情况。例如某节目流1在甲地球站主播，由甲地球站填报了被代播时间，同时此节目流在乙地球站备播，由乙地球站填报了代播时间，如果甲乙地球站填报时间不一致，则会造成统计错误。第三、统计公式严重依赖表结构，调整不便。全局卫星广播电视上行节目统计表只是简单合并三座地球站上行节目统计表信息，全局卫星广播电视传输时间统计表只是简单引用三座地球站传输时间统计表中单元格数据，当节目流出现增减等需要变动表结构时，容易导致全局统计表公式不可用，需重新调整公式。第四、报表格式不够灵活。由于统计表格中使用固定公式，导致报表格式固定，如果需要不同维度的统计报表，则需要重新编写新公式，制作新报表。

2 基于Power Query的卫星广播电视播出数据统计

使用基于Excel软件Power Query插件和数据透视表的统计方法，能有效解决工作中卫星广播电视播出数据现用统计方法的上述问题，极大改善播出数据统计分析的质量和效率。

2.1 Power Query简介

Power Query是微软提供的一款工具，在Excel2013版中作为插件使用，从2016版开始，Power Query的功能集成到Excel里，置于模块“获取和转换”中。Power Query主要作用是连接不同种类的数据源，进行数据的转换，可用于发现、连接、合并和优化数据源以满足分析需要。Power Query相比于VBA编程及SQL数据库编程简单易学，比起Excel公式功能更强大，是数据处理的理想工具之一。

2.2 基于Power Query的卫星广播电视播出数据统计方法

经过对无线电台管理局三座地球站卫星广播电视播出数据的梳理，以及现有统计方法及报表的研究，可以使用如图1所示框架对播出数据进行统计。

图1 基于Power Query的卫星广播电视播出数据统计框图

图2 使用数据透视表生成卫星广播电视播出数据分析报表示例

图1中数据存储方式使用Excel中“表格”形式，各地球站播出数据和卫星广播电视播出节目数据集播出时间数据都是通过Power Query新建查询的方式生成的，最后各类播出报表则通过数据透视表生成。

使用基于Power Query的方法进行统计分析，克服了原方法分别维护多个节目播出数据易形成数据冲突的缺点，只需维护一个卫星广播电视播出数据总表，同时细化字段定义，将主播站、备播站、主播系统、备播系统分别填写。使用Power Query新建查询，设置主播站、备播站查询条件可以将播出总表拆分成各地球站播出数据分表。

各地球站卫星广播电视播出数据表中设置了“载波计应播时长”、“载波计实播时长”、“节目计实播合计”、“节目计应播合计”、“节目计代播合计”、“节目计停传合计”等计算列，通过Power Query查询编辑器菜单或M语言使用无线局地球站卫星广播电视播出数据表中相应字段进行计算得出。各地球站卫星广播电视播出数据表和卫星广播电视播出节目数据表及卫星广播电视播出时间数据表是作为5个查询储存在Excel文件中，用于后续数据分析处理，也可以将5个查询连接至Excel表格中直观展现出来，便于查阅。

2.3 卫星广播电视播出数据分析

基于Power Query的卫星广播电视播出数据统计分析，是在维护图1中最左侧的卫星广播电视播出数据基础上，由Power Query生成5个查询，然后选择此5个查询作为数据源，利用数据透视表制作出各类重点突出的报表。当出现节目调整、节目流新增或删除时仅更改卫星广播电视播出数据，无需调整查询程序，刷新Excel文件可得更新后的报表，充分体现Power Query的方便易用及强大功能。数据透视表能够快速方便地汇总计算卫星广播电视播出数据，实现数据的筛选、排序、分组及条件格式设置等，可以重点关注所需信息，提供简明、灵活、有吸引力的报表。例如图2所示为使用卫星广播电视播出节目数据为数据源，由图中所示的设置得出的报表。

结束语：本文使用基于MicrosoftExcel软件Power Query插件和数据透视表的统计分析方法，有效解决了工作中卫星广播电视播出数据现用统计方法的信息冗余、信息冲突、报表单调、调整不便等问题，增强了卫星广播电视播出数据统计分析的质量和效率。工作中使用新统计分析方法后，只需将节目流数据、每月代播数据等填入卫星广播电视播出数据总表中，点击工具栏刷新按钮，所有报表都进行刷新，各中间表格及查询均不用关心，直接进行报表打印输出即可，极大地简化了操作。如需新的报表，只要选择好5个查询中的合适数据源，使用数据透视表设置框，将字段拖动至筛选器、行、列或值位置，很容易得出突出重点关注信息的报表。本文使用的统计分析方法有效地提升了统计服务能力，一定程度地满足了新制度对广播电视统计工作提出的新要求。