Excel数据透视表在高校学生贷款数据分析中的应用

龙金昌 陈斌

摘要：本文阐述了Excel数据透视表在高校学生贷款数据分析中的具体应用方法，为教育和科研进行数据透视数据分析提供了参考思路。

关键词：数据透视表;数据分析;方法

中图分类号：TP317 文献标识码：A 文章编号：1007-9416（2020）01-0073-02

经济飞速发展、信息技术全面应用，给各行各业带来了海量的数据，大数据时代数据爆炸式增长。尽管如ERP等大型软件和行业系统管理平台提供了强大的分析功能和报表输出功能，但它们提供的都是通用的报表分析，而以Excel为代表的电子表格软件却能灵活地满足企业个性化的数据分析需求，所以很多行业的管理人员在日常工作中始终离不开Excel，与工作息息相关，使用Excel的熟练程度，直接决定了数据分析效率。

在生源地贷款数据分析中，如使用函数进行统计分析，一旦布局改变，就要重新设计函数，在需要快速呈现报表的情况下，函数的效率大打折扣，在这种函数不擅长的时候，正可以用数据透视表弥补函数的不足，通过数据透视表简单的拖拽，就能组织大量的数据统计分析，缕出并呈现大量复杂数据的本质联系。

1 数据透视表的概念

Excel数据透视表（Pivot Table）是一种交互式的表，是从Excel数据列表、关系数据库文件等数据集的字段中总结信息的分析工具。所谓“透视”，是可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置，从不同的视角分析并重新计算数据，也就是可以从复杂的数据背后找到数据的本质联系，从而将看似杂乱的数据转化为有价值的信息，快速的生成各种类型的报表。

2 数据透视表的功能与用途

Excel数据透视表因具有强大的交互性，可以通过通过简单的布局改变，全方位、多角度、动态地统计和分析数据，综合了数据排序、筛选、分类汇总等功能，以及计算平均数或标准差、建立列联表、计算环比同比、建立新的数据子集等等，从大量数据中快速提取有价值信息。因此，在分析相关汇总数据，尤其是大量的数据需要统计分析的时候，需要用到数据透视表，其功能和用途主要有：一是快速的查询、统计数据，从多种视角查看数据的不同汇总;二是对数值数据进行分类汇总聚合，创建自定义计算和公式;三是展开、折叠所关注的结果的数据级别，导出关注汇总数据的明细;四是提供简明的联机报表和打印报表等。

3 创建数据透视表的方法

3.1 准备数据源

数据源可以是本地Excel文件，也可以通过联机获取外部文件，如文本文件、Microsoft SQL Server数据库、Microsoft Access数据库、Dbase数据库等。数据源可以是多个独立的Excel数据列表，数据透视表在创建过程中可以将多个独立的Excel数据列表中的信息汇总到一起，也可以是其他的数据透视表作为数据源来创建另外一个数据透视表。

作为数据源的数据表格，要将缺失的数据补充完整，将错误的数据纠正或者删除，将重复、多余的数据筛选清除，整理成为标准的、干净的、连续的数据，以方便后期的数据处理。比如数据表格不能使用中国传统的斜线表头、不能有合并单元格;数据表格中列标题不能为空，不能有空行空列;数据表格中不要大范围的使用条件格式、数据有效性、数组公式等等。

3.2 数据透视表创建方法

创建数据透视表常用方法有两种：

方法1：在“插入”选项卡中“表”组中，单击数据透视表图标，或者单击“数据透视表”右下方箭头，在单击“数据透视表”。

方法2：使用快捷键，依次按Alt、D和P键，启动“数据透视表和数据透视图向导”，按向导提示，三步完成透视表的创建。

4 数据透视表在生源地贷款数据分析中的应用

准备好数据源，按照数据透视表创建方法，创建数据透视表。本例选取2012-2017年贷款学生8512人的贷款信息进行数据统计分析（如图1所示）。

4.1 统计同名同姓学生人数、判断身份证号码是否重复

将光标定位在任一数据透视表数据区域，弹出“数据透视表字段列表”窗口，将“学生姓名”拖入“行标签”区域，将“学生姓名”拖入“Σ数值”区域。光标定位B5单元格并按右键，在弹出的菜单中点击“排序”，后点击“降序”，同名同姓的人数即完成统计，并按照降序排列。

同样操作，将“身份证号码”拖入“行标签”区域，将“身份证号码”拖入“Σ数值”区域。光标定位B5单元格并按右键，在弹出的菜单中点擊“排序”，后点击“降序”，相同身份证号码的人数即完成统计，并按照降序排列。如身份证号码统计数均应为1，如为2或2以上的数字，说明数据源表中，学生的身份证号码有重复，录入有误，应复核后进行更正。

4.2 院系、专业学生人数以及各院系所属各专业人数统计

将光标定位在在任一数据透视表数据区域，在弹出的“数据透视表字段列表”窗口，将“院系名称”拖入“行标签”区域，将“学生姓名”拖入“Σ数值”区域。将光标定位在B5单元格并按右键，在弹出的菜单中点击“排序”，后点击“降序”，各院系的人数即完成统计，并按照降序排列。同样操作，将“专业名称”拖入“行标签”区域，将“学生姓名”拖入“Σ数值”区域。光标定位B5单元格并按右键，在弹出的菜单中点击“排序”，后点击“降序”，各专业的人数即完成统计，并按照降序排列（如图2所示）。

将光标定位在在任一数据透视表数据区域，在弹出的“数据透视表字段列表”窗口，将“院系名称”拖入“行标签”区域，将“专业名称”拖入“行标签”区域，并确保专业名称”在“院系名称”下方，将“学生姓名”拖入“Σ数值”区域。将光标定位在C5单元格并按右键，在弹出的菜单中点击“排序”，后点击“降序”，各院系、各专业的人数即完成统计，并按照降序排列。

4.3 各院系所属各专业对应人数及贷款金额统计

将光标定位在任一数据透视表数据区域，在弹出的“数据透视表字段列表”窗口，将“院系名称”拖入“行标签”区域，将“专业名称”拖入“行标签”区域，并确保专业名称”在“院系名称”下方，将“学生姓名”“高校转账金额”拖入“Σ数值”区域。将光标定位在C5单元格并按右键，在弹出的菜单中点击“排序”，后点击“降序”，各院系、各专业对应的人数及贷款金额即完成统计，并按照降序排列（如图3所示）。

4.4 通过报表筛选进行单独汇总或多项汇总，并可生成具体名单

在上面的数据透视表的基础上继续进行操作。如将“贷款年度”拖拽到“报表筛选”区域，可以对各年度的贷款学生的人数和金额进行单独汇总或选择多项汇总。如将“发放到高校日期”拖拽到“报表筛选”区域，可按到账日期统计对贷款学生的人数和金额进行单独汇总或选择多项汇总。在统计汇总的学生人数或金额数字上单击，即可快速获得学生的具体名单。

5 结语

数据透视表交互性强，计算和统计快速高效，修改数据的内容和布局操作简便，能从各种视角快速变更不同的数据分析维度，实时数据变更后通过刷新功能即反映到数据透视表中，提高了统计分析的效率。

参考文献

[1] 罗惠民，钱勇.“偷懒”的技术：打造财务Excel达人[M].北京：机械工业出版社，2017.

[2]安伟星，裴雷.竞争力：玩转职场Excel，从此不加班[M].北京：电子工业出版社，2017.