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個查詢中的合適數據源，使用數據透視表設置框，將字段拖動至篩選器、行、列或值位置，很容易得出突出重點關注信息的報表。本文使用的統計分析方法有效地提升了統計服務能力，一定程度地滿足了新制度對廣播電視統計工作提出的新要求。