从不同的工作表创建数据透视表
使用数据透视表,您可以在几秒钟内汇总数据。您可以轻松创建报告、分析数据并与他人共享。
这就是数据透视表的美妙之处。
但这里有一个转折点:
通常,当您创建数据透视表时,您可以从工作表中的单个表中选择源数据,因为 Excel 不允许您引用不同的工作表。
但有时我们需要使用多个工作表中的源数据来创建数据透视表。
因此,今天我想与您分享在数据透视表中使用多个工作表的简单步骤。
问题!
假设您想要分析公司的销售数据并提取过去 4 年的年度数据。
这就是在 Excel 中获取数据转储的方法。
请注意,4 张表中的数据不同,但结构完全相同,即列数和标题数相同。
现在,要分析这些数据,您需要从这些多个工作表创建一个数据透视表报告。
一些常见的方法是:
- 手动复制并粘贴每个工作表中的数据,并在新工作表上创建单个数据集。
- 使用 VBA 代码自动合并多个工作表中的数据。
- 或者,您可以使用 Excel 的合并选项将多个工作表合并为一个工作表。
但事实是这些方法需要编码、复制和粘贴或者是重复的。
解决方案
在这里我将讨论一种使用 Microsoft 查询的新方法,它是动态的、健壮的且简单的。
相信我,你一定会喜欢它!
使用 Microsoft Query,您可以从多个工作表创建数据透视表。
下载此数据文件以进行后续操作。
从多个工作表创建数据透视表的步骤
这里我们有一些简单的步骤可供您遵循,在此之前,请从此处下载此文件以进行操作。
- 首先,选择每张纸上的所有数据并命名。
- 2005 年数据命名为 – Year 2005
- 2006 年数据命名为 – 2006 年
- 2007 年数据命名为 – 2007 年
- 2007 年数据命名为 – 2007 年
- 之后,将数据表应用于四个工作表中的所有数据。
- 选择数据范围中的任意单元格。
- 使用 Ctrl + T 将每个工作表上的数据转换为表格。
- 确保每次都选中“我的表格有标题”。
- 重复此操作 4 年(叶子)。
- 首先,我们将这些数据合并到一个新工作表中(添加新工作表的快捷键:Shift + F11)。
- 在“数据”选项卡中,单击“来自其他来源”-> 选择“来自 Microsoft Query”。
- 在选择数据源框中:
- 单击“Excel 文件”,然后按“确定”。
- 选择 Excel 文件的路径,然后选择文件,单击“确定”。
- 命名范围将出现在“查询向导 – 选择列”框中。
- 在查询向导中:
- 将每个命名范围(使用箭头按钮)拖到“查询框中的列”中。
- 单击下一步 – 忘记错误。
- 然后单击“确定”
- 现在我们的查询编辑器中已拥有所有数据,剩下的就是合并所有命名范围中的数据。为此,请单击 SQL 小按钮。
- 在 SQL 框中,删除所有文本并编写新查询,然后单击“确定”。
从2005年中选择*Union all从2006年中选择*Union all从2007年中选择*Union all从2008年中选择*
- 现在屏幕上显示的表格包含所有 4 个工作表中的数据。
- 我们所要做的就是转到“文件”选项卡并将该表导入到 Excel 中。
- 最后,将数据作为数据透视表导入回 Excel。
- 在“文件”菜单上 -> 单击“将数据返回到 Microsoft Excel”。
- 您可以看到我们总共(在 4 张表中)有 592 条记录。
- 此外,如果您向 4 个工作表中的任何一个添加更多数据,数据透视表将在您刷新后立即更新。