一种基于Power Query和透视表的数据处理方法
来源:用户上传
作者:沈丽民 李健
摘要：“大稻荨币殉晌当今炙手可热的术语，“大数据”的应用服务着人们生活工作的方方面面。如何进行大数据处理、大数据分析提高办公效率是当前学生任职后必备的一项基本素质，该文以战略支援部队信息工程大学洛阳校区在资产统计过程中遇到的数据处理问题为牵引，提出一种基于Power Query和数据透视表的数据处理方法，分析解决问题的思路、讨论解决问题的办法，培养学生解决复杂数据问题的思维方式，进而实现高效处理数据。
关键词：数据处理;Power Query;数据透视表;案例驱动
中图分类号：G642.4 文献标识码：A
文章编号：1009-3044（2021）36-0025-03
开放科学（资源服务）标识码（OSID）：
1 背景
当前“大数据”“数字化”“人工智能”已成为信息社会的热门话题，人们无时无刻不在享受着信息、数据、数字化给生活方式带来的便利。付款方式、购物方式、营销方式和出行方式等发生变化的背后是信息技术的巨大发展和大数据处理分析的结果。从海量数据中挖掘有用的数据，利用工具对数据进行处理和分析进而实现高效办公是当前学生应具备的素质[1-3]。一种基于Power Query和透视表的数据处理方法是以战略支援部队信息工程大学洛阳校区资产统计为案例，进行分析、设计并进行实践检验，利用该方法进行数据处理可以大大节省时间、人力和物力提高工作效率。
2 项目背景及需求
资产统计对于一个单位而言是一项非常重要而又超级烦琐复杂的工作，牵扯得人多、周期长、频率高、数据量大，在我校每年都会进行资产统计核对。一直以来战略支援部队信息工程大学洛阳校区的做法如图1所示，按照行政分工进行逐级下发大数据量的Excel表，具体做法是学校资产负责人通过筛选复制粘贴把学校资产按系分成若干个Excel工作表分别发放给各系资产负责人，各系的资产负责人按照继续按照上述方法把该系的资产按照教研室分成若干各Excel工作表发给教研室资产负责人，教研室负责人直接发放给资产保管人，保管人自行根据实际添加删除修改核对然后提交，每个保管人都在大表中进行修改自己保管的资产，然后上交给教研室负责人，教研室负责人根据保管人提交的内容一条一条地修改，然后提交系负责人，系负责人在复制粘贴汇总各教研室的资产提交给学校负责人，学校负责人按照同样的方法汇总各系的资产。整个资产统计的过程用时多工作量极大效率极低，而且是大量重复性的工作。
3 一种基于Power Query和数据透视表的数据处理的新方法
如何高效完成学校大数据量的资产统计，确保不漏一人不漏一项进行增删核对，提出了基于Power Query和数据透视表相结合的新方法，实践结果表明该方法准确高效，可以完美解决复杂的数据处理问题。整个过程校资产负责人可以直接和保管人对接，由校资产负责人把庞大的资产总表按照保管人进行数据拆分，拆分成以保管人命名的若干工作簿，保管人进行修改核对然后提交自己的工作簿，校资产负责人收集到所有资产保管人提交的工作簿进行汇总即可。因为整个数据处理过程只有校资产管理人和保管人直接对接，少了很多中间环节，节省了大量人力、物力，技术实现方面数据拆分巧妙使用Excel的数据透视表和VBA编程实现，数据汇总则应用Excel的Power Query功能。
3.1 数据拆分
数据拆分就是把如图2（左）所示的总表，包含资产编号、资产名称、规格、单价、保管人、部门和位置7个标题项，按照保管人分成以保管人命名的若干个工作簿如图2（右）所示，然后发放给每个保管人进行修改核对等工作。
1）利用数据透视表巧妙拆分工作表
具体操作过程是首先添加辅助列，暂定列名称为辅助列，该列内容等于保管人姓名如图3（a）所示，然后插入数据透视表，把资产总表中的前7项放入行标签，把辅助列作为筛选项，如图3所示，最后进行参数设置，包括不显示分类汇总、对行列禁用如图3（b）所示，这样即可把总表拆分成以保管人名命的若干工作表，如图4所示，但是并没有达到拆分成若干工作簿的目标，每个保管人一个工作簿才能确保数据独立，方便增删、修改核对和收集。
2）利用VBA代码把多个工作表拆分成工作簿
选中利用数据透视表拆分完成的若干工作表，右键单击查看代码打开VBA对话框，完成如图5（左）代码编写，单击运行选项卡选择运行子过程，即可实现把工作表拆分成工作簿，至此完成了按照保管人把资产总表拆分成若干个以保管人命名的工作簿，此时可以把工作簿分发给保管人完成增删核对等工作了。
3.2 Power Query数据汇总
校资产管理人把保管人完善后的工作簿收集好放在一个文件夹中，就可以利用Power Query进行一键完成汇总[4-5]。Power Query是微软提供的一款实现数据获取和数据查询工具，针对Excel 2010和2013版本需要去官网下载，自行安装使用，2016版开始， 微软把Power Query内置为Excel的一个功能模块，位于【数据】选项卡下的【获取和转】功能集中。Power Query支持的数据类型XLSX、CSV、XML、文本等，常用功能是多表（工作簿）汇总、合并查询等。针对本文涉及的资产统计问题，按照数据-获取和转换-新建查询-从文件夹-转换数据如图6所示，打开Power Query数据编辑删除不必要的列，添加自定义列，通过Excel.Workbook把Content 里的内容提取出来，如图7（a）所示，取消“使用原始列名作为前缀”，单击确定即可完成数据表内容的汇总如图7（b），选择关闭上载即可完成资产统计汇总工作，如果有新的工作簿提交，只需放在该文件夹，刷新即可完成数据汇总。
4 结束语
本文以战略支援部队信息工程大学洛阳校区每年资产统计过程中遇到的数据处理问题为牵引，提出了一种基于Power Query和透视表的数据处理方法，利用Excel数据透视表进行把资产总表按照保管人拆分成若干个以保管人名命的sheet，然后利用简单的VBA代码把数据透视表拆分的sheet分离成以保管人名命的工作簿，最后利用Power Query进行多工作簿汇总，实现高效处理数据的目的。经实践检验该方法不仅实现了高效拆分数据，一键数据汇总，还可以自动更新，如果有新的工作簿提交，只需刷新总表即可把新的内容更新至总表，大大节省了人力、物力和时间，真可谓一劳永逸。
参考文献：
[1] 郭小鹏.PowerQuery在卫星广播电视播出数据统计分析的应用[J].电子世界，2021（9）：186-187.
[2] 李蓬实，黎伟.大数据背景下面向经管类专业的PowerQuery数据处理课程探索[J].教育现代化，2019，6（91）：162-163.
[3] 冯注龙.Excel之光[M].北京：电子工业出版社.2020：199-223.
[4] 秋叶，黄群金，章慧敏.和秋叶一起学Excel[M].2版.北京：人民邮电出版社，2020：77-86.
[5] 初道忠，陈瑞鑫.Excel规划求解在数据分析与处理中的应用[J].福建电脑，2021，37（3）：104-106.
【通联编辑：谢媛媛】
转载注明来源:https://www.xzbu.com/8/view-15425245.htm
