☰

Excel - 工作项目需要公式帮助

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 工作项目需要公式帮助

我正在工作中开展一个项目，每周从我们的一个系统中提取一份报告并将其粘贴到工作簿的“数据”选项卡中。

然后有 6 个按地区指定的其他选项卡 - “中西部”、“东北部”等。我提取的报告中没有位于数据中的区域，因此我考虑创建另一个选项卡作为一种查找表，但我不确定如何连接所有这些。

基本上，当我将报告数据粘贴到“数据”选项卡时，我需要区域选项卡上的公式来确定该州是否是该地区的成员并填充相应选项卡上的所有行和列。

我正在发布“数据”选项卡的屏幕截图（已删除机密信息）以及“区域”选项卡的示例。任何帮助都将不胜感激！

数据选项卡示例：

数据选项卡示例

区域选项卡的示例：

区域选项卡应有的样子的示例

答案1

好奇心占了上风。我不喜欢我链接的示例没有实际的表格，而只有普通的单元格区域。根据您的需要，这没问题。但我希望区域工作表中的数据也是表格。因此，我使用 Power Query 并自引用现有工作簿来查询工作表DATA以填充区域工作表，从而创建了一个示例。这样，如果工作表InitialData中的表格DATA发生变化，您可以Refresh All更新，并且所有区域工作表都应该更新（原则上，如果您有权限，您也可以完全省略工作DATA表并只连接到实际数据源）。如果您想更改包含的列或对源数据进行其他计算并将其放入区域工作表中，您也可以这样做，所有这些都无需过多地使用查找或动态溢出数组函数。

基本上，每个区域工作表都有一个表格，通过 Power 查询连接到工作DATA表，并带有适当的过滤器，以便仅抓取指定区域的行。

https://www.dropbox.com/s/asvbq9rukwna544/superuser-split-data-to-sheets.xlsx?dl=0

编辑：我知道我忘了一些东西 -__- 自我引用文件很棘手。下载示例时，您需要进入其中一个 Power Query，单击“数据源设置”，单击“更改源”（您将在自己的计算机上看到工作簿的路径，，C:\Users\Preston Maness\etcetcetc这显然不会存在于您的计算机上），然后浏览到您自己的计算机上的下载位置。然后，您可以退出 Power Query 并在它要求您保存时进行保存。此时，在执行“全部刷新”后，对工作表InitialData中表格的更新DATA应该会更新区域选项卡。

下面是更详细的图片解释。

  1. 我们首先假设您有一个工作簿，其中有一个名为 的工作表DATA，该工作表中有一个名为 的正确表格InitialData。该InitialData表格包含数据分组，您希望将这些数据分组拆分到每个组单独的工作表中。在此示例中，我们希望根据列中的值将数据拆分到单独的工作表中State。

步骤1

  1. 我们将创建与此工作簿的连接。转到Data > Get Data > From File > From Excel Workbook功能区上的。

第2步

  1. 选择您当前所在的同一工作簿。在此示例中superuser-1714937-split-table-data-to-different-sheets。

步骤3

  1. 这将打开一个初始数据选择对话框，其中显示工作簿中可用的InitialData表和工作表。选择表。请注意，有一个选项和一个选项。我们需要与这两个选项所指向的区域进行交互，我们可以按任意顺序处理它们。就我个人而言，我首先从 的下拉列表中找到 开始。DATAInitialDataLoadTransform DataLoad ToLoad

步骤4

  1. 单击Load To将弹出一个Import Data对话框。您想将数据导入Table，并将其放在Existing worksheet或中New workshoot。在此示例中，我将其放在新工作表中。

步骤5

  1. 它为新工作表指定的默认名称是InitialData，表的默认名称是InitialData_2。还请注意，右侧的查询和连接抽屉已打开，并且有一个查询，也称为InitialData。

第 6 步

  1. 我喜欢让我的名字遵循一致的格式。因此，我将更新表格名称，将其改为我想要定位的第一个分组，South即南部各州。工作表将重命名为InitialData，SOUTH表格名称将InitialData_2重命名为South，查询将重命名为InitialData。South您可以在功能区中编辑表格名称，并且可以右键单击工作表和查询，然后会出现重命名选项。

步骤7

  1. 此时，是时候调整 Power Query 了，它从工作表InitialData上的表中提取数据DATA并将其推送到工作表South上的表SOUTH。您可以双击Queries & Connections抽屉中的查询，也可以右键单击它并选择选项Edit。这将打开 Power Query 编辑器对话框，如下所示。默认情况下，Applied Steps抽屉部分中Query Settings显示最新步骤（Changed Type）。此界面的各个部分都突出显示，我将在后续步骤中介绍它们。

步骤8

  1. 让我们对该界面进行一些观察。首先，Query Settings右侧的抽屉Applied Steps从上到下显示了当前的。Source是工作簿文件，指定Navigation要从工作簿中提取的内容（工作表InitialData中的表格DATA），表示Changed TypePower Query 对列类型的猜测。如果单击其中任何一个，主面板都会更新并提供有关该步骤的其他信息。
  1. 步骤 - 来源：窗口的主要部分显示了有关源的一些元数据。在本例中，源是一个文件。您可以在公式栏中看到，我们正在通过特定路径的文件打开 Excel 工作簿。此时，特定数据之内Excel 工作簿尚未被选中。这在导航步骤中。

（此步骤的完整公式内容如下）

= Excel.Workbook(File.Contents("C:\Users\Preston Maness\Documents\superuser-1714937-split-table-data-to-different-sheets.xlsx"), null, true)

第 10 步

  1. 步骤 - 导航：在这里，我们看到 Excel 工作簿的特定部分被定位。它是表格，其中InitialData有。KindTable

（此步骤的完整公式内容如下）

= Source{[Item="InitialData",Kind="Table"]}[Data]

步骤11

  1. 步骤 - 改变类型：在这里，我们可以看到 Power Query 推断每列的类型。这是最终会出现在目标表中的每列的类型，也可能是格式。单击列名称旁边的任意类型图标将允许您覆盖推断的类型。

（此步骤的完整公式内容如下）

= Table.TransformColumnTypes(InitialData_Table,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"State", type text}})

步骤12

  1. 具体来说，在这个Changed Type步骤中，你可以看到每一列都有下拉列表。我们将选择State下拉列表并选择“适当”的值。好吧，实际上，我们会犯一个错误，这样我以后才能指出这一点。但你会看到 :)

步骤13

  1. 步骤 - 筛选行Applied Steps：这在抽屉列表中创建了另一个步骤Query Settings：Filtered Rows步骤。您可以观察到，我们现在只有两行匹配，并且公式栏显示了过滤的语法（我们不需要自己创建）。

（此步骤的完整公式内容如下）

= Table.SelectRows(#"Changed Type", each ([State] = "CA" or [State] = "TX"))

步骤14

  1. 我们已准备好保存！点击Close & Load左上角的图标：

步骤15

  1. 您可能需要等待几秒钟，但刷新完成后，您现在将在工作表的表格中看到TX和行！CASouthSOUTH

步骤16

  1. 但请稍等一下！加利福尼亚不是南部州。哎呀。该South再次编辑查询了。如果右键单击并编辑Queries & Connections抽屉中的查询，您将再次获得 Power Query 编辑器对话框。确保所选的应用步骤是该Filtered Rows步骤，然后更新过滤器。

步骤17

  1. 再次按下Close & Load按钮，几秒钟后，我们将在表格中看到只有一行的预期South结果SOUTH。

步骤18

  1. 太棒了。现在，为了展示这种方法的威力，让我们向工作表的表格TX中添加另一条记录。InitialDataDATA完成此操作后请务必保存工作表。

步骤19

  1. 然后返回SOUTH工作表并查看... 稍等一下！新记录在哪里？它不在那里！不过请保持冷静，然后单击功能区选项卡Refresh All上的按钮。Data

步骤20

  1. 它就在那里！

步骤21

  1. 太棒了！我们现在有了区域工作表，其中包含适当的表格，这些表格将从工作表InitialData中的主表更新DATA。然后，我们可以在后续分析中使用这些适当的表格进行结构化引用，而不必担心必须手动调整普通单元格范围的选择范围！
  1. 关于分享此类工作簿的警告：如果您回忆一下步骤 10 中有关该Source步骤的内容，它需要对工作簿文件路径的硬编码引用：C:\Users\Preston Maness\Documents\superuser-1714937-split-table-data-to-different-sheets.xlsx。如果您希望其他用户能够操作此工作簿中的数据并且不会看到冻结的快照，则位置需要在用户之间保持不变（通过网络驱动器或其他共享 URL），或者接收用户需要在其中一个 Power Query 查询中更新文件源。可以通过单击Data source settingsPower Query 编辑器中的按钮、单击打开的对话框、单击Change Source打开的对话框、选择其他用户下载文件到的文件路径、单击、然后单击对话框、然后单击Power Query 编辑器来编辑文件源。Data source settingsBrowseExcel WorkbookOkCloseData source settingsClose & Load

步骤23

  1. 这种方法并不局限于 Excel 工作簿文件，事实上，第 23 步显示了该方法的麻烦/局限性。您可以直接连接到共享数据源（如数据库），并应用相同的逻辑来查询它。

鳍。

答案2

如果您有 Excel 365，您可以使用FILTER函数以非常简单的方式获取数据：

在此处输入图片描述

相关内容