從“基礎數據”得到“高基報表”的方法研究

馬海軍 祁淑梅

摘? 要：根據國家政策，各高職院校每年都要報送高基報表，填報工作費時費力，該高基報表統計數據的獲取方法是基于Excel2016環境，用Power Query+VBA以及數據透視表來實現，通過PowerQuery和VBA動態獲取數據平臺基礎數據，然后對基礎數據進行清洗，分析處理得到所想要的統計數據，該方法是一種全新的嘗試，拓寬了數據獲取的途徑，提高了統計數據采集填報的效率。

關鍵詞：Excel;PowerQuery;VBA;數據清洗;模型

中圖分類號：TP311? ? ? ? ? ? ? ? ? ?文獻標識碼：A文章編號：2096-4706（2021）19-0101-04

Research on the Method of Getting“High-base Report” from “Basic Data”

MA Haijun1， QI Shumei2

（1.Ningxia Technical College of Wine and Desertification Prevention， Yinchuan? 750199， China; 2.Gulou Branch of Yinchuan 21st Primary School， Yinchuan? 750001， China）

Abstract： According to the national policy， each higher vocational college should submit the high-base reports every year， which is time-consuming and laborious. The obtaining method of the statistical data of the high-base reports is implemented based on Excel 2016 environment， using Power Query+VBA and PivotTable. The basic data of data platform is dynamically obtained through PowerQuery and VBA， and then the basic data is cleaned， analyzed and processed to obtain the desired statistical data. This method is a new attempt， which widens the way of data acquisition and improves the efficiency of statistical data collection and reporting.

Keywords：Excel; PowerQuery; VBA; data cleaning; model

0? 引? 言

教育數據是教育管理和科學決策的重要依據，作為高職院校每年都要填報大量的基礎數據和統計數據，比如高職院校人才培養工作狀態數據屬于基礎數據，高等學校基本情況報表（簡稱“高基報表”）以及教育質量年度報告屬于統計數據，基礎數據的采集雖數據量特別大，因采用網絡填報，全員參與，任務量相對較小;“高基報表”等統計數據的填報，往往由少數人負責，填報的難度更大[1]。

當然，這些數據的獲取對于信息化建設程度較高的學校來講，是比較簡單的。但是對于大多數學校，填報還是比較費時費力。筆者所在的學校，教務處有教務管理系統和人才培養狀態數據數據平臺，學生處有學工管理系統，財務處有專門的財務系統，黨政辦公室有OA系統，人事處和其他部門雖沒有自己的數據庫服務器，但他們卻有自己部門所管轄的數據本地報表;學校各部門之間數據不互通，不共享，是典型的數據孤島，這是共性問題，隨著數據量的增大，如何利用現有數據資源，快速準確高效地完成統計數據的獲取，是數據填報人員迫切要解決的問題[2]。

1? 解決問題的設想

通過各渠道獲取基礎數據，比如有數據庫的直接從數據庫獲取，沒有數據庫的，從本地電腦獲取，然后將這些數據進行格式轉換、數據清洗，建立源數據中心。

整合好源數據后，可以對源數據表進行拓展操作，比如根據身份證號獲取出生日期，或者根據出生日期計算年齡等等，擴展數據是根據統計需要確定。數據完善之后，可以通過數據透視表對源數據進行透視，獲得所需要的統計數據，這種設想從理論層面來講是可行的，從技術層面來講，PowerQuery強大的統計分析功能也是能實現的，另外實時更新數據，也是要考慮的問題[3]。

2? 實施過程及舉例

本例以獲取學工系統服務器數據庫中的在校生基本信息，結合本地班級輔助信息，最后得到高基321在校生年齡情況表為例說明操作方法。

2.1? 數據抓取途徑

通過Excel“數據”功能選項卡，新建查詢，獲取外部數據;可以是自Access數據庫、自網站、自文本、導入數據，具體途徑要根據數據來源確定。建議盡可能從數據庫服務器獲取的數據，這種方式獲取的數據更新方便。

本例選自其他來源下的來自SQLServer數據庫，輸入“學工系統”服務器的地址、用戶名和密碼，進入后根據提示選擇在校生基本信息，即可完成信息的獲取。（從“數據狀態平臺”獲取教師信息同理）。

注意：數據獲取前必須知道數據庫服務器的IP地址、用戶名，密碼，必須明確你要獲取的數據是哪一個，獲取數據后根據內容重新對工作表進行命名。

2.2? 數據清洗

從數據庫獲取的數據往往有很多冗余奇異數據，比如重復、空行、沒用的列，從本地導入的或者手工輸入的數據單元格前后偶爾會出現空格，這些都不利于數據的應用，所以必須對源數據進行清洗。

獲取原始數據后，將在Excel工作簿里面生成一個原始數據的工作表，打開數據功能選項卡，選從表格，選擇數據范圍，即可將原數據導入PowerQuery，再進行各種清洗操作。往往通過數據庫獲得的數據相對整齊，但是清洗的步驟不能少，順序為先刪除沒用的、冗余的，然后再更改數據類型、并數據整理。

舉例：將原始數據表重命名為“0在校生基本信息”，將該表導入PowerQuery，導入范圍為A-AJ列的所有內容，如圖1所示。

（1）刪除“審核人”“審核時間”“姓名拼音”“曾用名”“照片”“身份證件類型”“乘車區間”“預計畢業日期”等無須統計的列;

（2）更改類型：將出生日期更改為日期類型，其他列數據更改為文本類型;

（3）刪除重復行、刪除空行、取除前后空格（選擇所有文本列，右鍵-轉換-修整/清除）。

2.3? 數據整合

數據整合是整個過程中最復雜的一個環節，數據整合仍然在PowerQuery下進行，可以通過追加查詢把多個表的數據依據一定的規則整合到一起，同時把一些需要計算的項目計算出來，整合數據一定要有目標性。具體示例為：

（4）添加自定義列：“當前日期”

公式=DateTime.LocalNow（）;

（5）更改自定義列“當前日期”數據類型為日期類型;

（6）添加自定義列“當前月日”：

公式=Date.ToText（[當前日期]，”MMdd”）;

添加自定義列“年齡”

公式=Date.Year（DateTime.LocalNow（））-Date.Year（[出生日期]）-Number.From（Date.ToText（[出生日期]，”MMdd”）>[當前月日]）

注釋：年齡的大小為當前年-出生年-1/0（如果出生月日大于當前月日，返回為1，反之返回為0。

添加自定義列“年齡特征”，具體公式如下：

=if[年齡]>=18 and [年齡]<=30

then [年齡]

else if[年齡]<18

then”17歲及以下”

else”31歲及以上”

注釋：通過選擇語句，將年齡分為三段：

Ⅰ段為小于18歲這個年齡段的年齡特征為17歲及以下。

Ⅱ段為大于等于18歲、小于等于30歲，這個年齡段為中間年齡段年齡特征為具體年齡值。

Ⅲ段為大于等于31歲這個年齡段的年齡特征為31歲及以上。

（7）更改“年齡特征”為文本類型;

（8）刪除列（“當前日期”“當前月日”“年齡”）;

（9）合并查詢：將班級基本信息和學生基本信息進行合并，以擴充學生基本信息的信息量（合并查詢操作前應將班級基本信息導入PowerQuery）;

由于原學生基本信息表中沒有學生的學歷層次信息，因此將各班級的信息引入，目的是為獲取每個學生的學歷層次信息，合并查詢時要注意選擇匹配的列和聯結種類，本例以班級名稱進行匹配，聯結以學生基本信息為準，如圖2所示。

（10）將合并查詢展開（注意只選擇班級信息中需要的信息，本例只選了類別，取消勾選使用原始列名作為前綴），如圖3所示;

（11）更改“年齡特征”數據類型為文本，然后升序排序。

至此數據整合環節結束，點開始功能選項卡，然后關閉并上載。

注意，選擇導入原數據時不要用CTRL+A，這樣只選了有數據的部分，建議從操作區上狀態欄上選，列只選有數據的部分，行全選，比如“ =$B：$P”為B列到P列的所有數據，后續再往后面添加數據，刷新即可自動進入清洗范圍。另外PowerQuery的每一步操作都保留有記錄，和Photoshop操作相似，如圖4所示。

2.4? 數據透視

將關閉并上載的數據表重新命名為“1在校生信息”，然后對該表進行透視，所有操作還是在Excel下進行，具體方法：

（1）打開插入功能選項卡，選數據透視表，選擇數據范圍為“1在校生信息”下的所有記錄;

（2）在數透視表字段下勾選要透視的內容，依次為類別-性別-年齡特征-學號，將年齡特征字段拖拽到列（類別、性別等信息默認在行），學號字段拖拽到值，默認為計數。

至此統計數據的獲取結束，可以對數據透視表進行設計報表布局為表格形式，默認為壓縮形式，對數據透視表選項下布局勾選合并且居中[4]，如圖5所示。

往后每年的高基報表，無論是學生出現何種變化，只要把每個表刷新一下，高基報表內容自動更新，但是如果數據源表特別多的情況下，備注又不是很明確，極容易出現因某一個表沒有更新導致統計數據出錯，為此我們可以通過VBA，在當工作簿重新打開時都做一次全面更新，在數據庫源頭數據或者源數據發生改變后，當工作簿重新打開，Excel、PowerQuery都會和源數據庫或數據源再聯結一次，這樣就保證了整個工作簿所有信息都是最新的，達到了數動態更新的目的。VBA代碼為[5]：

Private Sub Workbook_Open（）

ThisWorkbook.RefreshAll

End Sub

對于本例的幾點說明：

（1）本例中原始信息只用到學生類別、性別、年齡特征、學號四項，其他信息在高基報表321統計信息中沒有用到，如果只獲取高基報表321的統計數據，其他數據項可以在數據清洗時刪除;在本例中沒有刪除是因為該表數據還能統計獲得高基報表的其他數據，比如依據類別、學號、政治面貌等列，獲得高基報表341的統計數據（在校生其他情況）;依據類別、學號、年級、專業等列結合一些輔助信息，獲得高基報表311的統計數據（普通專科分專業學生數）等等;

（2）由于篇幅等原因，本次只展示了高基報表321統計數據的生成過程，在基礎數據完備的情況下其他報表也是能夠生成的，比如可以從人才培養狀態數據數據平臺中可以獲取教師基礎信息，生成教師相關情況的高基報表等;

（3）每次自動更新數據的VBA代碼建議在所有統計報表完成后再加，因為報表再設計時需要測試，每次重新打開如果全部刷新會浪費時間和電腦運行資源。

3? 結? 論

使用Excel PowerQuery和VBA解決高基報表獲取數據的問題，這是筆者作為學校數據統計人員，做的一次嘗試，目前我院高基報表321的數據獲取已經通過本方式完成，雖然原始設計比較費時，但是設計完成后次年獲取該數據表只要打開表就自動生成了。在大數據時代的今天，這種操作方法或者途徑可以作為大數據獲取的有益補充，供奮戰在數據統計一線的同仁們借鑒。需要說明的是：

（1）這種方法需要由一定的計算機基礎知識，要對Excel PowerQuery有深入的了解，平時多練，多積累多總結，靈活運用。

（2）目前筆者的探索還停留在單個高基表的自動生成階段，如果能把高基報表每個表的數據都通過這種方法獲取，能夠實現打開工作簿即實現統計數據的生成，必將有很大的推廣價值，苦于筆者時間、精力等原因雖進展緩慢，但按照理論是完全可以實現的。

（3）用Excel PowerQuery獲取高基報表的數據，這是一種獲取手段和方法。這種方法和人工相比，具有速度快，準確性高等特點，當然還有其他更簡單的方法，本文不做研究。

（4）用Excel PowerQuery獲取并清洗數據這種方法能否在解決數據孤島，打通數據壁壘方面發揮作用，有待進一步驗證，目前看來，它可以做到從各數據源獲取數據，如何讓它的作用更大發揮，需要更進一步的探索和學習。

參考文獻：

[1] 李林，錢丹丹，黃婷婷，等.高校信息化數據治理探討 [J].中國教育信息化，2017（9）：66-68.

[2] 王錢靜，趙慶櫻，晏杉.高職院校教育統計工作的思考 [J].教育教學論壇，2016（40）：246-248.

[3] 朱仕平.Power Query：用Excel玩轉商業智能數據處理 [M].北京：電子工業出版社，2017.

[4] Excel Home.Excel 2016數據透視表應用大全 [M].北京：北京大學出版社，2018.

[5] Excel Home.別怕，ExcelVBA其實很簡單 [M].北京：人民郵電出版社，2012.

作者簡介：馬海軍（1980.12—），男，漢族，寧夏銀川人，講師，本科，研究方向：計算機基礎應用。