PowerQuery在衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)分析的應(yīng)用

國家廣播電視總局無線電臺管理局 郭小鵬

廣播電視統(tǒng)計(jì)工作是推進(jìn)廣播電視和網(wǎng)絡(luò)視聽行業(yè)治理體系和治理能力現(xiàn)代化的一項(xiàng)基礎(chǔ)性綜合性工作。國家廣播電視總局2020年重新修訂發(fā)布了《廣播電視和網(wǎng)絡(luò)視聽統(tǒng)計(jì)調(diào)查制度》，增加了廣播電視衛(wèi)星地球站傳輸系統(tǒng)等指標(biāo)，對衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)工作提出了更高的要求。

1 衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)方法

無線電臺管理局主要有甲地球站、乙地球站、丙地球站等三座地球站上行中央和省級廣播電視衛(wèi)星節(jié)目信號。三座地球站互為實(shí)時(shí)備份上行，上行系統(tǒng)均接入地球站自動調(diào)度系統(tǒng)，可實(shí)現(xiàn)上行節(jié)目的無縫代播，克服系統(tǒng)檢修、雨雪衰等原因造成的節(jié)目停傳，保證24h安全可靠傳輸。

目前，無線電臺管理局衛(wèi)星廣播電視播出數(shù)據(jù)的統(tǒng)計(jì)方法是，三座地球站分別按月統(tǒng)計(jì)本站上行節(jié)目統(tǒng)計(jì)表和節(jié)目傳輸時(shí)間統(tǒng)計(jì)表，綜合三座地球站上報(bào)的節(jié)目統(tǒng)計(jì)表和傳輸時(shí)間統(tǒng)計(jì)表，使用Excel表格公式得出全局衛(wèi)星廣播電視播出節(jié)目情況和傳輸時(shí)間統(tǒng)計(jì)情況。在實(shí)際使用過程中存在以下問題：第一、信息存在大量冗余。例如某節(jié)目流1在甲地球站主播、乙地球站備播，同時(shí)統(tǒng)計(jì)在甲、乙地球站的上行節(jié)目統(tǒng)計(jì)表和傳輸時(shí)間統(tǒng)計(jì)表中，都需要進(jìn)行填寫、維護(hù)。第二、信息易出現(xiàn)沖突。由于同一信息同時(shí)存在多個(gè)無關(guān)聯(lián)的表中，容易出現(xiàn)相互矛盾的情況。例如某節(jié)目流1在甲地球站主播，由甲地球站填報(bào)了被代播時(shí)間，同時(shí)此節(jié)目流在乙地球站備播，由乙地球站填報(bào)了代播時(shí)間，如果甲乙地球站填報(bào)時(shí)間不一致，則會造成統(tǒng)計(jì)錯(cuò)誤。第三、統(tǒng)計(jì)公式嚴(yán)重依賴表結(jié)構(gòu)，調(diào)整不便。全局衛(wèi)星廣播電視上行節(jié)目統(tǒng)計(jì)表只是簡單合并三座地球站上行節(jié)目統(tǒng)計(jì)表信息，全局衛(wèi)星廣播電視傳輸時(shí)間統(tǒng)計(jì)表只是簡單引用三座地球站傳輸時(shí)間統(tǒng)計(jì)表中單元格數(shù)據(jù)，當(dāng)節(jié)目流出現(xiàn)增減等需要變動表結(jié)構(gòu)時(shí)，容易導(dǎo)致全局統(tǒng)計(jì)表公式不可用，需重新調(diào)整公式。第四、報(bào)表格式不夠靈活。由于統(tǒng)計(jì)表格中使用固定公式，導(dǎo)致報(bào)表格式固定，如果需要不同維度的統(tǒng)計(jì)報(bào)表，則需要重新編寫新公式，制作新報(bào)表。

2 基于Power Query的衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)

使用基于Excel軟件Power Query插件和數(shù)據(jù)透視表的統(tǒng)計(jì)方法，能有效解決工作中衛(wèi)星廣播電視播出數(shù)據(jù)現(xiàn)用統(tǒng)計(jì)方法的上述問題，極大改善播出數(shù)據(jù)統(tǒng)計(jì)分析的質(zhì)量和效率。

2.1 Power Query簡介

Power Query是微軟提供的一款工具，在Excel2013版中作為插件使用，從2016版開始，Power Query的功能集成到Excel里，置于模塊“獲取和轉(zhuǎn)換”中。Power Query主要作用是連接不同種類的數(shù)據(jù)源，進(jìn)行數(shù)據(jù)的轉(zhuǎn)換，可用于發(fā)現(xiàn)、連接、合并和優(yōu)化數(shù)據(jù)源以滿足分析需要。Power Query相比于VBA編程及SQL數(shù)據(jù)庫編程簡單易學(xué)，比起Excel公式功能更強(qiáng)大，是數(shù)據(jù)處理的理想工具之一。

2.2 基于Power Query的衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)方法

經(jīng)過對無線電臺管理局三座地球站衛(wèi)星廣播電視播出數(shù)據(jù)的梳理，以及現(xiàn)有統(tǒng)計(jì)方法及報(bào)表的研究，可以使用如圖1所示框架對播出數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。

圖1 基于Power Query的衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)框圖

圖2 使用數(shù)據(jù)透視表生成衛(wèi)星廣播電視播出數(shù)據(jù)分析報(bào)表示例

圖1中數(shù)據(jù)存儲方式使用Excel中“表格”形式，各地球站播出數(shù)據(jù)和衛(wèi)星廣播電視播出節(jié)目數(shù)據(jù)集播出時(shí)間數(shù)據(jù)都是通過Power Query新建查詢的方式生成的，最后各類播出報(bào)表則通過數(shù)據(jù)透視表生成。

使用基于Power Query的方法進(jìn)行統(tǒng)計(jì)分析，克服了原方法分別維護(hù)多個(gè)節(jié)目播出數(shù)據(jù)易形成數(shù)據(jù)沖突的缺點(diǎn)，只需維護(hù)一個(gè)衛(wèi)星廣播電視播出數(shù)據(jù)總表，同時(shí)細(xì)化字段定義，將主播站、備播站、主播系統(tǒng)、備播系統(tǒng)分別填寫。使用Power Query新建查詢，設(shè)置主播站、備播站查詢條件可以將播出總表拆分成各地球站播出數(shù)據(jù)分表。

各地球站衛(wèi)星廣播電視播出數(shù)據(jù)表中設(shè)置了“載波計(jì)應(yīng)播時(shí)長”、“載波計(jì)實(shí)播時(shí)長”、“節(jié)目計(jì)實(shí)播合計(jì)”、“節(jié)目計(jì)應(yīng)播合計(jì)”、“節(jié)目計(jì)代播合計(jì)”、“節(jié)目計(jì)停傳合計(jì)”等計(jì)算列，通過Power Query查詢編輯器菜單或M語言使用無線局地球站衛(wèi)星廣播電視播出數(shù)據(jù)表中相應(yīng)字段進(jìn)行計(jì)算得出。各地球站衛(wèi)星廣播電視播出數(shù)據(jù)表和衛(wèi)星廣播電視播出節(jié)目數(shù)據(jù)表及衛(wèi)星廣播電視播出時(shí)間數(shù)據(jù)表是作為5個(gè)查詢儲存在Excel文件中，用于后續(xù)數(shù)據(jù)分析處理，也可以將5個(gè)查詢連接至Excel表格中直觀展現(xiàn)出來，便于查閱。

2.3 衛(wèi)星廣播電視播出數(shù)據(jù)分析

基于Power Query的衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)分析，是在維護(hù)圖1中最左側(cè)的衛(wèi)星廣播電視播出數(shù)據(jù)基礎(chǔ)上，由Power Query生成5個(gè)查詢，然后選擇此5個(gè)查詢作為數(shù)據(jù)源，利用數(shù)據(jù)透視表制作出各類重點(diǎn)突出的報(bào)表。當(dāng)出現(xiàn)節(jié)目調(diào)整、節(jié)目流新增或刪除時(shí)僅更改衛(wèi)星廣播電視播出數(shù)據(jù)，無需調(diào)整查詢程序，刷新Excel文件可得更新后的報(bào)表，充分體現(xiàn)Power Query的方便易用及強(qiáng)大功能。數(shù)據(jù)透視表能夠快速方便地匯總計(jì)算衛(wèi)星廣播電視播出數(shù)據(jù)，實(shí)現(xiàn)數(shù)據(jù)的篩選、排序、分組及條件格式設(shè)置等，可以重點(diǎn)關(guān)注所需信息，提供簡明、靈活、有吸引力的報(bào)表。例如圖2所示為使用衛(wèi)星廣播電視播出節(jié)目數(shù)據(jù)為數(shù)據(jù)源，由圖中所示的設(shè)置得出的報(bào)表。

結(jié)束語：本文使用基于MicrosoftExcel軟件Power Query插件和數(shù)據(jù)透視表的統(tǒng)計(jì)分析方法，有效解決了工作中衛(wèi)星廣播電視播出數(shù)據(jù)現(xiàn)用統(tǒng)計(jì)方法的信息冗余、信息沖突、報(bào)表單調(diào)、調(diào)整不便等問題，增強(qiáng)了衛(wèi)星廣播電視播出數(shù)據(jù)統(tǒng)計(jì)分析的質(zhì)量和效率。工作中使用新統(tǒng)計(jì)分析方法后，只需將節(jié)目流數(shù)據(jù)、每月代播數(shù)據(jù)等填入衛(wèi)星廣播電視播出數(shù)據(jù)總表中，點(diǎn)擊工具欄刷新按鈕，所有報(bào)表都進(jìn)行刷新，各中間表格及查詢均不用關(guān)心，直接進(jìn)行報(bào)表打印輸出即可，極大地簡化了操作。如需新的報(bào)表，只要選擇好5個(gè)查詢中的合適數(shù)據(jù)源，使用數(shù)據(jù)透視表設(shè)置框，將字段拖動至篩選器、行、列或值位置，很容易得出突出重點(diǎn)關(guān)注信息的報(bào)表。本文使用的統(tǒng)計(jì)分析方法有效地提升了統(tǒng)計(jì)服務(wù)能力，一定程度地滿足了新制度對廣播電視統(tǒng)計(jì)工作提出的新要求。