馬海軍 祁淑梅

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

關(guān)鍵詞：Excel;PowerQuery;VBA;數(shù)據(jù)清洗;模型

中圖分類號：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? 引? 言

教育數(shù)據(jù)是教育管理和科學(xué)決策的重要依據(jù)，作為高職院校每年都要填報大量的基礎(chǔ)數(shù)據(jù)和統(tǒng)計數(shù)據(jù)，比如高職院校人才培養(yǎng)工作狀態(tài)數(shù)據(jù)屬于基礎(chǔ)數(shù)據(jù)，高等學(xué)?；厩闆r報表（簡稱“高基報表”）以及教育質(zhì)量年度報告屬于統(tǒng)計數(shù)據(jù)，基礎(chǔ)數(shù)據(jù)的采集雖數(shù)據(jù)量特別大，因采用網(wǎng)絡(luò)填報，全員參與，任務(wù)量相對較小;“高基報表”等統(tǒng)計數(shù)據(jù)的填報，往往由少數(shù)人負責(zé)，填報的難度更大[1]。

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

1? 解決問題的設(shè)想

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

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

2? 實施過程及舉例

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

2.1? 數(shù)據(jù)抓取途徑

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

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

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

2.2? 數(shù)據(jù)清洗

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

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

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

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

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

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

2.3? 數(shù)據(jù)整合

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

（4）添加自定義列：“當(dāng)前日期”

公式=DateTime.LocalNow（）;

（5）更改自定義列“當(dāng)前日期”數(shù)據(jù)類型為日期類型;

（6）添加自定義列“當(dāng)前月日”：

公式=Date.ToText（[當(dāng)前日期]，”MMdd”）;

添加自定義列“年齡”

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

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

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

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

then [年齡]

else if[年齡]<18

then”17歲及以下”

else”31歲及以上”

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

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

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

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

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

（8）刪除列（“當(dāng)前日期”“當(dāng)前月日”“年齡”）;

（9）合并查詢：將班級基本信息和學(xué)生基本信息進行合并，以擴充學(xué)生基本信息的信息量（合并查詢操作前應(yīng)將班級基本信息導(dǎo)入PowerQuery）;

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

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

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

至此數(shù)據(jù)整合環(huán)節(jié)結(jié)束，點開始功能選項卡，然后關(guān)閉并上載。

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

2.4? 數(shù)據(jù)透視

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

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

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

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

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

Private Sub Workbook_Open（）

ThisWorkbook.RefreshAll

End Sub

對于本例的幾點說明：

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

（2）由于篇幅等原因，本次只展示了高基報表321統(tǒng)計數(shù)據(jù)的生成過程，在基礎(chǔ)數(shù)據(jù)完備的情況下其他報表也是能夠生成的，比如可以從人才培養(yǎng)狀態(tài)數(shù)據(jù)數(shù)據(jù)平臺中可以獲取教師基礎(chǔ)信息，生成教師相關(guān)情況的高基報表等;

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

3? 結(jié)? 論

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

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

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

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

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

參考文獻：

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

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

[3] 朱仕平.Power Query：用Excel玩轉(zhuǎn)商業(yè)智能數(shù)據(jù)處理 [M].北京：電子工業(yè)出版社，2017.

[4] Excel Home.Excel 2016數(shù)據(jù)透視表應(yīng)用大全 [M].北京：北京大學(xué)出版社，2018.

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

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