从“基础数据”得到“高基报表”的方法研究

马海军 祁淑梅

摘  要：根据国家政策，各高职院校每年都要报送高基报表，填报工作费时费力，该高基报表统计数据的获取方法是基于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—），男，汉族，宁夏银川人，讲师，本科，研究方向：计算机基础应用。