從“基礎(chǔ)數(shù)據(jù)”得到“高基報(bào)表”的方法研究

馬海軍 祁淑梅

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

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

中圖分類號(hào)：TP311? ? ? ? ? ? ? ? ? ?文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：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ù)，作為高職院校每年都要填報(bào)大量的基礎(chǔ)數(shù)據(jù)和統(tǒng)計(jì)數(shù)據(jù)，比如高職院校人才培養(yǎng)工作狀態(tài)數(shù)據(jù)屬于基礎(chǔ)數(shù)據(jù)，高等學(xué)?；厩闆r報(bào)表（簡(jiǎn)稱“高基報(bào)表”）以及教育質(zhì)量年度報(bào)告屬于統(tǒng)計(jì)數(shù)據(jù)，基礎(chǔ)數(shù)據(jù)的采集雖數(shù)據(jù)量特別大，因采用網(wǎng)絡(luò)填報(bào)，全員參與，任務(wù)量相對(duì)較小;“高基報(bào)表”等統(tǒng)計(jì)數(shù)據(jù)的填報(bào)，往往由少數(shù)人負(fù)責(zé)，填報(bào)的難度更大[1]。

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

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

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

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

2? 實(shí)施過(guò)程及舉例

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

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

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

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

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

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

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

獲取原始數(shù)據(jù)后，將在Excel工作簿里面生成一個(gè)原始數(shù)據(jù)的工作表，打開(kāi)數(shù)據(jù)功能選項(xiàng)卡，選從表格，選擇數(shù)據(jù)范圍，即可將原數(shù)據(jù)導(dǎo)入PowerQuery，再進(jìn)行各種清洗操作。往往通過(guò)數(shù)據(jù)庫(kù)獲得的數(shù)據(jù)相對(duì)整齊，但是清洗的步驟不能少，順序?yàn)橄葎h除沒(méi)用的、冗余的，然后再更改數(shù)據(jù)類型、并數(shù)據(jù)整理。

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

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

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

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

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

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

（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歲及以上”

注釋：通過(guò)選擇語(yǔ)句，將年齡分為三段：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Private Sub Workbook_Open（）

ThisWorkbook.RefreshAll

End Sub

對(duì)于本例的幾點(diǎn)說(shuō)明：

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

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

（3）每次自動(dòng)更新數(shù)據(jù)的VBA代碼建議在所有統(tǒng)計(jì)報(bào)表完成后再加，因?yàn)閳?bào)表再設(shè)計(jì)時(shí)需要測(cè)試，每次重新打開(kāi)如果全部刷新會(huì)浪費(fèi)時(shí)間和電腦運(yùn)行資源。

3? 結(jié)? 論

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

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

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

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

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

參考文獻(xiàn)：

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

[2] 王錢(qián)靜，趙慶櫻，晏杉.高職院校教育統(tǒng)計(jì)工作的思考 [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其實(shí)很簡(jiǎn)單 [M].北京：人民郵電出版社，2012.

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