将表格拆分为工作表

Microsoft Excel 有许多工具可用于从多个表（来自不同的工作表或来自不同的文件）收集数据：直接链接、函数 间接 （间接）、Power Query 和 Power Pivot 附加组件等。从路障的这一侧看，一切看起来都不错。

但是如果你遇到一个逆问题——将数据从一张表传播到不同的工作表——那么一切都会变得更加糟糕。 遗憾的是，目前 Excel 库中还没有用于这种数据分离的文明内置工具。 因此，您将不得不在 Visual Basic 中使用宏，或者使用宏记录器 + Power Query 组合并稍加“文件优化”。

让我们仔细看看这是如何实现的。

问题的形成

我们有一个超过 5000 行的销售表作为初始数据：

将表格拆分为工作表

任务：将表格中的数据按城市分布在本书的不同页面上。 那些。 在输出中，您只需要在每张纸上获取销售在相应城市的表中的那些行：

将表格拆分为工作表

Prepare

为了不使宏代码复杂化并使其尽可能易于理解，让我们执行几个准备步骤。

首先， 创建一个单独的查找表，其中一列将列出您要为其创建单独工作表的所有城市。 当然，这个目录可能不包含源数据中存在的所有城市，而只包含我们需要报告的城市。 创建此类表的最简单方法是使用命令 数据 - 删除重复 （数据 - 删除重复项） 用于列复制 城市 或功能 K （独特的） – 如果您拥有最新版本的 Excel 365。

由于 Excel 中的新工作表默认在当前（上一个）工作表之前（左侧）创建，因此按降序（从 Z 到 A）对该目录中的城市进行排序也是有意义的 - 然后在创建后，城市工作表将按字母顺序排列。

第二，该 п将两个表都转换为动态 （“智能”）以更轻松地与他们合作。 我们使用命令 主页 – 格式为表格 （首页——表格格式） 或键盘快捷键 按Ctrl+T. 在出现的选项卡上 构造函数 （设计） 让我们打电话给他们 表普罗达吉 и 桌城， 分别：

将表格拆分为工作表

方法 1. 按表划分的宏

在高级选项卡上 开发人员 （开发商） 点击按钮 Visual Basic中 或使用键盘快捷键 其他+F11. 在打开的宏编辑器窗口中，通过菜单插入一个新的空模块 插入 - 模块 并在那里复制以下代码：

Sub Splitter() For Each cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub

这里有一个循环 对于每个……下一个 实现了通过目录单元格的通道 桌城, 对每个城市进行过滤（方法 自动筛选) 在原始销售表中，然后将结果复制到新创建的工作表中。 一路上，创建的工作表被重命名为与城市相同的名称，并在其上打开自动调整列的宽度以进行美化。

您可以在选项卡上的 Excel 中运行创建的宏 开发人员 按键 宏 （开发者——宏） 或键盘快捷键 其他+F8.

方法2.在Power Query中创建多个查询

以前的方法虽然紧凑和简单，但有一个明显的缺点——在原始销售表中进行更改时，宏创建的工作表不会更新。 如果需要即时更新，那么您将不得不使用 VBA + Power Query 捆绑包，或者更确切地说，使用宏创建不仅包含静态数据的工作表，而且还使用更新的 Power Query 查询。

本例中的宏与前一个部分相似（它也有一个循环 对于每个……下一个 遍历目录中的城市），但在循环内部将不再进行过滤和复制，而是创建 Power Query 查询并将其结果上传到新工作表：

Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""更改类型"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""应用过滤器的行"" = Table.Se " & _ "lectRows(#""改变类型"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""应用过滤器的行""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;提供者 =Microsoft.Mashup.OleDb.1;数据源 =$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1"))。 QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False 。 SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value 下一个单元格 End Sub

启动后，我们将按城市看到相同的工作表，但已经创建的 Power Query 查询将形成它们：

将表格拆分为工作表

随着源数据的任何变化，用鼠标右键更新相应的表就足够了——命令 更新并保存 （刷新） 或使用按钮一次批量更新所有城市 全部更新 标签 时间 （数据——全部刷新）.

  • 什么是宏，如何创建和使用它们
  • 将工作簿工作表另存为单独的文件
  • 将书中所有工作表中的数据收集到一个表中

发表评论