excel抓取多页网页数据(用Excel进行数据汇总，再平常不过的需求单表汇总)

　　excel抓取多页网页数据(用Excel进行数据汇总，再平常不过的需求单表汇总)

　　使用Excel进行数据汇总，最常见的需求

　　单表汇总：最常用的操作应该是小计、数据透视表等。这是最理想的方式，所有数据都在一个工作表中，便于汇总。但理想很丰富，现实却很骨感。这种方法在工作中很少见，多表汇总是我们不得不面对的需要。

　　多表汇总：无论是在本工作簿内还是跨工作簿，也可以解决多个合并计算数据区。以这种方式创建的数据透视表的一个明显缺点是只有一个行字段，并且只能是数据源左侧的第一列。如果仅针对一个角度进行聚合，这也是一种解决方案。不过N张工作表的数据全部整合为一张，只能统计一个视角，有些遗憾。

　　当然可以在数据源中设置一个辅助列，将一个单元格中的多列信息连接起来作为一个行字段，但归根结底只有一个行字段，起不到改变行字段的作用统计的角度，能治标不治本。

　　

　　图：没有多少信息只是一个行字段

　　SQL join 语句：这当然是多表汇总的杀手锏。无论工作表是否跨工作簿，以这种方式创建的数据透视表都与单表数据透视一样简单。随意切换统计视角并生成各种报告似乎很完美。

　　让我们举个例子。五个销售部门分为五个工作簿，每个工作簿按月填写销售明细，需要多角度汇总。这个要求不应过分。无论是OLEDB还是Microsoft Query，写一条SQL连接语句都能完美解决。

　　不过写了这么多句子↓

　　

　　图：SQL连接语句

　　已经可以看到 5 个工作簿中只有 15 个工作表，如果是一年的数据呢？也就是60个工作表，也就是说有60条SQL连接语句。

　　如果用电量查询来完成呢？一个公式就够了

　　事实上，power query 是一个数据采集工具，微软已经有一两年没有将它内置到 Excel 中了。在刚才的例子中，别说5个工作簿和15个工作表，十倍的数据相加就是一个公式。将强大的查询称为 SQL 连接语句的终结者并不为过。

　　我用的版本是office365订阅版，Excel内置电源查询，名字有点长，姑且称之为PQ。如果你的 Excel 没有 PQ 怎么办？

　　我们先来体会一下PQ的威力。文章 最后附上PQ的安装方法。当然，要不要安装PQ，你说了算。

　　文末有本章演示数据和结果获取方法

　　免责声明：本章提供的数据为虚构数据，并非真实数据

　　

　　图：5个工作簿20个子表汇总

　　首先介绍一下要聚合的数据

　　这是5个销售部门的销售数据，位于5个工作簿中，每个工作簿的工作表按月记录。销售部成立时间不同，有的记录是3个月，有的记录是4个月，有的记录是6个月。现在需要从月份、各销售部门、销售区域、产品名称等不同角度对5个销售部门进行汇总。

　　

　　图：演示数据

　　面对这样的数据和这样的要求，你怎么看？是的，数据透视表，它不是快速转换统计观点的强项吗！

　　快速将所有子表整合到一张工作表中，并与数据同步更新子表。我们已经到了这一点。是否使用小计或数据透视表取决于您？

　　想想就兴奋！

　　让我们开始吧！

　　创建摘要工作簿

　　创建一个新的 Excel 文档并将其命名为“摘要”。

　　

　　图：新建 Excel 文档

　　打开“摘要”工作簿并添加一个文件夹。

　　操作路径：数据→获取数据→从文件→从文件夹→浏览到“E:\销售数据记录”→确定。

　　

　　图：添加文件夹

　　在弹出的对话框中确认文件夹中的所有工作簿都已加载完毕后，点击“转换数据”。

　　

　　图：加载的工作簿

　　这是自动打开的电源查询编辑器，并在“名称”列中进行过滤，只勾选少数销售部门。

　　

　　图：取消勾选不需要汇总的工作簿

　　可以看到数据有 N 列。事实上，我们真正需要的只是前两列。其他列显示文件创建时间、加载时间和后缀名等信息，删除它们。

　　操作路径：Ctrl键选中前两列→删除列→删除其他列。

　　

　　图：删除不需要的列

　　添加自定义列，输入公式，整个操作只需要输入这个公式。

　　操作路径：添加列→自定义列→输入公式：=Excel.Workbook([Content])→确定

　　

　　图：添加自定义列

　　展开“数据”，因为段名称自动显示为“Custom.Data”。

　　操作路径：点击自定义旁边的按钮→只选择数据→确保单选为“展开”→确定

　　

　　图：展开Data后，字段名称为“Custom.Data”

　　展开 Custom.Data

　　操作路径：点击自定义.Data旁边的按钮展开→单选展开→全选列→确定

　　

　　图：展开 Custom.Data

　　数据合并成功，但是中间一行有N个子表字段。这些无用的行会影响我们的摘要。有多少子表就有多少无用的行。下一步就是解决这个问题。

　　图：中间一行有多余的字段

　　隐藏多余的行

　　操作路径：修改自定义列的字段名→过滤任意列→取消勾选不需要的内容

　　

　　图：隐藏多余的行

　　好的，数据干净了，上传到Excel。

　　操作路径：首页→关闭上传

　　

　　图：关闭并上传

　　我们来看看Excel中的情况。5个销售部门的数据全部整合，日期截止到6月份。估计这张表叫“总表”。

　　

　　图：所有数据整合

　　刚才说了，分表的更新是同步到总表的，我们试试加个工作表吧。刚才最大的一个月是六月。通过筛选，我们发现这个数据属于第二销售部。现在，例如，第二销售部门添加了 7 月份的数据。

　　

　　图：销售部2新增工作表

　　关闭销售的第二个工作簿，然后到总表刷新。通过过滤，7月份的数据已经同步到汇总表中。如果你使用 SQL 连接，你必须添加另一行语句来更新，但现在，我们要做的就是刷新它。

　　

　　图：刷新后同步到主表

　　如果你使用这个整合的数据制作多个透视图，并在子表更新的时候全部刷新，所有的图表都会显示最新的结果。

　　

　　图：刷新所有数据

　　好吧，我刚才说了，我们已经到了这个阶段，怎么操作就看我们的心情了，我就用数据透视表。整个过程只写了一个公式，你说PQ要上天了。

　　

　　图：摘要演示

　　如何安装 PQ

　　好的，你决定安装 PQ 了吗？如下图，在今日头条搜索“powerquery安装”。

　　安装过程不用多说，在互联网时代，一切都是那么简单。

　　

　　图：今日头条搜索安装方法

　　如果您对数据透视图感兴趣，请不要害怕查看我的专栏，它只讨论数据透视图。

　　与列一样，必须为每个实例提供演示数据。私信“jbt3”获取本章demo数据及成果。