问题的形成
数据透视表是 Excel 中最令人惊叹的工具之一。 但不幸的是，到目前为止，没有一个 Excel 版本可以即时完成这样简单而必要的事情，例如为位于不同工作表或不同表格中的多个初始数据范围构建摘要：
在开始之前，让我们澄清几点。 先验地，我认为我们的数据满足以下条件：
- 表格可以有任意数量的包含任何数据的行，但它们必须具有相同的标题。
- 带有源表的工作表上不应有额外的数据。 一张纸——一张桌子。 要控制，我建议您使用键盘快捷键 按Ctrl+结束，这会将您移动到工作表中最后使用的单元格。 理想情况下，这应该是数据表中的最后一个单元格。 如果当你点击 按Ctrl+结束 表格右侧或下方的任何空单元格都会突出显示 - 删除表格右侧或表格下方的这些空列或表格后的行并保存文件。
方法 1：使用 Power Query 为数据透视表构建表
从 Excel 2010 版本开始，有一个免费的 Power Query 加载项可以收集和转换任何数据，然后将其作为构建数据透视表的源。 在这个插件的帮助下解决我们的问题一点也不难。
首先，让我们在 Excel 中创建一个新的空文件——将在其中进行组装，然后在其中创建一个数据透视表。
然后在选项卡上 时间 （如果您有 Excel 2016 或更高版本）或在选项卡上 电源查询 （如果您有 Excel 2010-2013）选择命令 创建查询 - 从文件 - Excel （获取数据——从文件——Excel） 并指定要收集的表的源文件：
在出现的窗口中，选择任何工作表（不管是哪一个），然后按下面的按钮 更改 （编辑）:
Power Query 查询编辑器窗口应在 Excel 顶部打开。 在面板上窗口的右侧 请求参数 删除除第一个以外的所有自动创建的步骤 – 来源 （资源）:
现在我们看到所有工作表的一般列表。 如果文件中除了数据表之外还有其他一些边表，那么在此步骤中，我们的任务是仅选择需要从中加载信息的那些表，使用表头中的过滤器排除所有其他表：
删除除列之外的所有列 时间通过右键单击列标题并选择 删除其他列 （去掉 其他栏目）:
然后，您可以通过单击列顶部的双箭头（复选框 使用原始列名称作为前缀 你可以把它关掉）：
如果你做的一切都正确，那么此时你应该看到收集的所有表格的内容一个在另一个之下：
仍然使用按钮将第一行提升到表头 使用第一行作为标题 （使用第一行作为标题） 标签 主页 （首页） 并使用过滤器从数据中删除重复的表头：
保存使用命令完成的所有内容 关闭并加载 – 关闭并加载… （关闭并加载 - 关闭并加载到…） 标签 主页 （首页），然后在打开的窗口中，选择选项 仅连接 （仅限连接）:
一切。 它仍然只是建立一个摘要。 为此，请转到选项卡 插入 - 数据透视表 （插入 - 数据透视表）, 选择选项 使用外部数据源 （使用外部数据源）然后点击按钮 选择连接，我们的要求。 通过将我们需要的字段拖入行、列和值区域，以完全标准的方式进一步创建和配置数据透视表：
如果将来源数据发生更改或添加了更多存储表，那么使用命令更新查询和我们的摘要就足够了 全部刷新 标签 时间 （数据——全部刷新）.
方法二、我们在宏中用UNION SQL命令联合表
我们的问题的另一个解决方案是由这个宏表示，它使用命令为数据透视表创建一个数据集（缓存） UNITY SQL 查询语言。 此命令组合数组中指定的所有表 工作表名称 将书页合并到一个数据表中。 也就是说，我们不是从不同的工作表物理复制和粘贴范围，而是在计算机的 RAM 中执行相同的操作。 然后宏添加一个具有给定名称的新工作表（变量 结果表名称) 并根据收集的缓存在其上创建一个完整的 (!) 摘要。
要使用宏，请使用选项卡上的 Visual Basic 按钮 开发人员 （开发商） 或键盘快捷键 其他+F11. 然后我们通过菜单插入一个新的空模块 插入 - 模块 并在那里复制以下代码：
Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant '结果透视表将显示的工作表名称 ResultSheetName = "Pivot" '工作表数组带有源表的名称 SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") '我们为来自 SheetsNames 的工作表的表和 ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) 形成一个缓存) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$(arSQL, "UNION ALL"), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With '重新创建工作表以显示生成的数据透视表 On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo 吨。 Name = ResultSheetName '在此工作表上显示生成的缓存摘要 Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Select End With End Sub 然后可以使用键盘快捷键运行完成的宏 其他+F8 或选项卡上的宏按钮 开发人员 （开发者——宏）.
这种方法的缺点：
- 数据未更新，因为缓存与源表没有连接。 如果更改源数据，则必须再次运行宏并再次构建摘要。
- 更改张数时，需要编辑宏代码（数组 工作表名称).
但最终我们得到了一个真正成熟的数据透视表，它建立在来自不同工作表的多个范围之上：
瞧！
技术说明： 如果您在运行宏时收到类似“未注册提供程序”的错误，则很可能您安装了 64 位版本的 Excel 或安装了不完整的 Office 版本（无 Access）。 要解决此问题，请替换宏代码中的片段：
提供程序= Microsoft.Jet.OLEDB.4.0;
到：
提供者=Microsoft.ACE.OLEDB.12.0；
并从 Microsoft 网站的 Access 下载并安装免费的数据处理引擎 – Microsoft Access Database Engine 2010 Redistributable
方法 3：从旧版本的 Excel 合并数据透视表向导
这种方法有点过时，但仍然值得一提。 正式地说，在 2003 年之前（包括 XNUMX 年）的所有版本中，数据透视表向导中有一个选项可以“为多个合并范围构建数据透视”。 然而，不幸的是，以这种方式构建的报告只能是一个真正的完整摘要的可怜表象，并且不支持传统数据透视表的许多“筹码”：
在这样的数据透视表中，字段列表中没有列标题，没有灵活的结构设置，使用的函数集是有限的，总的来说，这一切都与数据透视表不太相似。 也许这就是为什么从 2007 年开始，Microsoft 在创建数据透视表报告时从标准对话框中删除了此功能。 现在此功能仅可通过自定义按钮使用 数据透视表向导（数据透视表向导），如果需要，可以通过以下方式将其添加到快速访问工具栏 文件——选项——自定义快速访问工具栏——所有命令 （文件——选项——自定义快速访问工具栏——所有命令）:
单击添加的按钮后，您需要在向导的第一步选择适当的选项：
然后在下一个窗口中，依次选择每个范围并将其添加到常规列表中：
但是，同样，这不是一个完整的总结，所以不要期望太多。 我只能在非常简单的情况下推荐这个选项。
- 使用数据透视表创建报表
- 在数据透视表中设置计算
- 什么是宏，如何使用它们，在哪里复制 VBA 代码等。
- 从多张纸到一张的数据收集（PLEX 插件）