PowerQuery 汇总系列 - 单个Excel工作薄文件、多工作表

文章目录

• 1. 写在前面，多工作表汇总也有坑
• 2. 删除自动生成的步骤，重新开始
• 3. 删除自动生成的步骤，开始改写
• 4. 指定工作表汇总
• • 4.1. 按单个关键词
  • 4.2. 按多个关键词

Authors

@ 樊笼星海

@ w180361

@ Email：[email protected]

1. 写在前面，多工作表汇总也有坑

  我见过一些同事，先获取到文件内的所有工作表，然后使用追加查询合并数据的操作，这种方式太繁琐，也不够灵活。

  多数已经在工作的小伙伴，并不清楚在 Excel 工作薄文件中，工作表Sheet 不等于 表格Table，我们也都习惯了叫它Excel表格。

  平常我们仅仅使用 Excel 表格时，可以不在乎这些东西有什么区别，甚至不需要知道。然而，在使用了微软的 ETL 工具之后，即 PowerQuery，不得不去在乎这些内容的区别，否则容易犯下大错。

  读过这篇文章的小伙伴，需要知道工作薄、工作表、表格、单元格、行、列相关概念有所区别，当我们不再以单元格的视角去看待表格时，这尤为重要。关于工作表与表格的概念，这里不详细解释区分了。如有兴趣，可搜索其他文章。

  下面从 PowerQuery 的视角，去看看工作表与表格的区别。如果不注意，将会汇总到重复的数据。

2. 删除自动生成的步骤，重新开始

  前面有说过 PowerQuery 从单工作薄文件、单工作表获取数据时，PQ 会自动生成 4 个步骤，其中第二个步骤【导航】是用来选择工作表。

  也就是说第一个步骤已经包含工作薄文件的所有工作表Sheet与表格Table，那我们在汇总多个工作表时，便可以把后面生成的三个步骤的删除掉。

  具体步骤如下：

1. 删除自动生成的查询步骤，只留下第一个查询步骤。
   每个查询步骤的前面会有一个Ⅹ，点击可删除此查询步骤。
   

2. 强调一下：Sheet 与 Table的区别。删除查询步骤之后，会发现Kind字段下，有Table文字显示，这表示这一行的数据来源于表格Table，又因为表格是存在于工作表里面，所以同一份数据被不同的方式获取了两次，如果不进行剔除，将会导致重复汇总。

示例文件由作者准备，刻意在4月份的工作表中，将区域转换成了表格Table，以示区别。若不剔除，4月份的数据将会重复汇总。

3. 获取所有工作表Sheet数据，先筛选Kind字段，剔除不需要重复的数据。
   = Table.SelectRows(源, each ([Kind] = "Sheet"))
   

4. 保留Data列，删除其他列。预览时发现标题在第一行，将第一个查询步骤中Excel.Workbook的第二参数，改为true即可。
   

5. 点击列的右上角，随后点击确定，把数据展开。无需使用原始列名作为前缀，取消勾选。
   

6. 最后的结果
   

3. 删除自动生成的步骤，开始改写

不要忘记了，前面改写过第一个查询步骤，Excel.Workbook的第二个参数true。

1. 保留筛选的行，即保留工作表Sheet的数据。
   = Table.SelectRows(源, each ([Kind] = "Sheet"))
   

2. 取Data列，在公式后面加上[Data]
= Table.SelectRows(源, each ([Kind] = "Sheet"))[Data]
   

3. 套上Table.Combine。
   = Table.Combine(Table.SelectRows(源, each ([Kind] = "Sheet"))[Data])
   

4. 指定工作表汇总

  在工作当中，很少有这样规范的数据，直接给到你进行整理，太多复杂离谱的需求暂且不说。来说说比较简单的指定工作表汇总，尤以汇总其他同事手工做表的情况，统一工作表名称是最基础的，但有时总会冒出一些新的表来，那些你明明不需要。

  本节只讨论单个工作薄文件的汇总，相对来说，变化较少，也许几个关键词就够。

4.1. 按单个关键词

上述案例中，要求工作表名称包含“月”字的工作表数据。

1. 点击列的右上角，选择文本筛选器，除了等于、不等于外，还有开头、结尾、包含的判断。这里我们选择包含“月”字。
   

2. 选择包含后，出现筛选行 的界面。随后点击确定，得到筛选结果后，按正常步骤进行汇总即可。
   = Table.SelectRows(筛选的行, each Text.Contains([Name], "月"))
   

4.2. 按多个关键词

上述案例中，要求汇总第二个季度的数据，即取 4月、5月、6月等 6 个工作表的数据。

1. 筛选行的界面，最多只能填两个参数。先自动生成一个，然后看看怎么改。
   

2. 自动生成的公式，判断条件是串起来的，那咱就继续加。
   

3. 咱假设关键词很多，怎么办？为以防万一，预先做一个关键词表，从 Excel 导入 PowerQuery，合并成文字即可使用。
   

除了合并成字符串，还有以下其他方法。前两种适合工作表名称与关键词一致的，第 3 种，适合工作表名称包含关键词的。

改法 1：

Table.SelectRows(筛选的行, each Text.Contains("4月5月6月", [Name]))

改法 2：

Table.SelectRows(筛选的行, each List.Contains({"4月","5月","6月"}, [Name]))

改法 3：

Table.SelectRows( //判断每一个工作表名称是否包含其中一个关键词，有其一即为 true。
	筛选的行,
	( row )=> List.AnyTrue( 
	  	List.Transform( 
		    {"4","5","6"},
		    ( key )=> Text.Contains( row[Name], key )
	    )
	)
)

文章难免有疏漏，若读者发现某处编辑有误，可评论留言。

相关问题也可找作者进行咨询，DIY定制皆可。

Authors

@ 樊笼星海

@ w180361

@ Email：[email protected]