☰

使用 INDEX MATCH 或脚本自动生成列表？

tag-icon • tag-icon microsoft-excel
使用 INDEX MATCH 或脚本自动生成列表？

我希望获得一些帮助来自动化我的 COVID-19 拭子测试电子表格。

以下是示例电子表格： https://drive.google.com/file/d/1Vi6Dd0UV_E6tnisC3Na_zCxeLC_3DrLr/view?usp=sharing

仅需关注前 4 个 TAB。

“员工名单”选项卡大约每周更新一次，显示员工总数。

当这种情况发生时，我希望每个部门选项卡都能自动填充其部门的所有工作人员，但我似乎无法让 INDEX MATCH 正常工作。

我还希望有一个明确的命令来清除所有部门选项卡中的测试结果（G：N），因为这将是一个实时文档，其他电子表格将从中获取数据并将其替换，即使使用相同的文件名也会产生错误。

如果您在电子表格中看到我的工作并且有其他建议，请告诉我！

非常感谢！ Larson

编辑：我尝试使用此网站上的方法：exceldemy.com/excel-generate-list-based-on-criteria 这个：=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") 但显然改变了单元格引用，而我只让它返回一个结果，并且返回了错误的引用单元格（例如，在应该显示名字的地方返回了姓氏）

EDIT2：将此公式放入“Rest.Serv”选项卡中，用于返回第一位员工的正确 ID：

=IFERROR(INDEX('Crew List'!$B$4:$B$19, MATCH(0, IF($B$1='Crew List'!$E$4:$E$19, COUNTIF($B$1:$B$1, 'Crew List'!$B$2:$B19), ""), 0)),"")

解决方案：非常感谢您的帮助，我已成功使用您的公式将其粘贴到每个部门选项卡的单元格 B3 中，但我稍微修改了代码以消除没有任何员工的部门的任何错误，并且因为每个部门选项卡在单元格 B1 中都有部门名称，所以我使用了这个公式：

=IFERROR(FILTER('Crew List'!B4:F1000,'Crew List'!E4:E1000=B1),"")

至于用于清除测试结果的 VBA 脚本，我发现这样做是成功的（不完整）：

Sub ClearValueOnly()

Worksheets("Audio Visual Media").Range("G3:N1000").ClearContents
Worksheets("Aux Serv").Range("G3:N1000").ClearContents
Worksheets("Rest. Service").Range("G3:N1000").ClearContents

End Sub

答案1

嗯，这里有几件事。

FILTER()首先，您需要用于提取（可能的）多行数据的函数。 VLOOKUP()，，XLOOKUP()和INDEX/MATCH都在其正常使用方法中提供单行返回。

FILTER()如果需要，将为每个匹配条目提供整行。因此，对于显示的数据，您可以使用如下公式，并根据实际工作表和范围进行调整：

=FILTER('Crew List'!B4:N19,'Crew List'!E4:E19="REST. SERVICE")

这将返回两个条目，Stanley Sands 和 Ican Designer。这解决了主要问题。

它的使用还有更精细的点。例如，可以使用另一个FILTER()包裹它来仅返回 B:N 列中的选定列。对于该条件，可以使用一个由 1（TRUE）和 0（FALSE）组成的数组常量，每个列一个，以显示是保留还是放弃。因此，要保留 B、C、D、I 和 J 列，您可以使用{1,1,1,0,0,0,0,1,1,0,0,0,0}。或者可以使用INDEX()并在类似的数组常量（如）中按数字指定列（不是工作表上的列号，如 B 列 = 2，而是按给定范围内的位置，如 B 列 = 1）{1,2,3,8,9}。

然后，在引入新数据之前，希望清除旧数据。如何做到这一点取决于个人的技能和倾向，以及需求。例如，需求。例如，如果您希望从 Crew List 表中提取的数据保留在提取它的子表中，而只在 Crew List 表上消失，那么您需要 VBA 或大量手动工作，因为 Excel 的公式不会对输入的数据采取行动。他们会查找数据并在您喜欢的任意地方显示或以其他方式使用它，但他们从不做任何事情。因此，要保留旧的提取数据，您需要自己做，不管怎样。该文件是 .XLSM，所以显然有人在其中使用了 VBA，这对某些人来说相对容易编写。

如果您希望数据无处不在，并且只有基于更新列表的新数据，FILTER()只需清除数据，而不是删除正在查找新数据的行、列和工作表。因此，选择船员名单数据范围（可能选择过多，以便其可变的行数不会影响您一周的时间）并按删除键或等效键清除它即可。子工作表将看到数据消失，数据也将从子工作表中消失。当新数据被粘贴时，无论使用哪种方法，但同样，不是通过移动行等方法，子工作表都会从中正确提取数据，而不会错过任何节拍。

因此，最终还是要选择并清除当前数据。手动操作？有一个巧妙的技巧可以让它变得快速而简单。VBA？很多方法，都很好。但请记住（我再次强调），不要通过删除或插入行来影响事物的结构。以某种方式粘贴数据。

所以，这是一个巧妙的技巧。创建一个命名范围，并在其框中指定足够的范围Refers to。假设您最多希望有 20-30 个条目。然后使用像 B4:N103 这样的范围来确保您始终获得所有内容。创建命名范围后，如果您查看公式编辑器左侧列字母上方的框，您可以在那里找到它。只需单击它并选择命名范围。您的数据区域将被选中，只需按 即可Delete擦除它。就是这样。非常简单。

如果你能写一个宏，并且不介意的话，你也可以这么做。但是上面的方法真的很容易。

如果我提到每周清除源数据也会清除子工作表上的数据（因为当数据消失且尚未替换时，公式将找不到任何内容可显示）时没有说清楚，那根本就不是问题。清除源数据也会清除子工作表上的数据。

接下来，您要清除的内容示例是 G:N 列，而不是 B:N 列。就好像您想保留 B:F 列的旧材料一样。这种方法不适用于这种情况。不能使用单个公式，也不需要大量手动工作或 VBA。

例如，每个子表可以有一个FILTER()公式，用于 B:F 列中的数据和 G:N 列中的数据。您必须将当前公式结果转换为这些子表上的数据，并清除它们的第二组。但这也意味着您想用新数据填充 G:N 区域，即使新数据显然是全新的，添加和删除船员，因此没有任何内容匹配或有效。

因此这些陈述会导致矛盾。我猜你只是在说这些事情，因为你正在研究可以做事情的方法，并且并不真正知道如何FILTER()从新数据集中重新填充所有列，因此不需要保留 B:F 材料，因为公式也会提取它。所以一切都会顺利进行。但猜测可能会完全错误，所以......

回顾一下，这个想法是使用给定公式将拉取到每个工作表的数据数组。每当有一组新数据准备导入时，人们就会使用命名范围选择技术快速清除旧数据。然后，人们将导入新数据，公式将根据需要重新填充。所有这些都很容易做到，无需 VBA 或任何认真的努力。但如果有人愿意并且有时间编写它，当然可以将 VBA 用于所有这些。

如果在导入新数据替换之前必须存档子表的当前数据，那么 VBA 是您的最佳选择，因为需要检查和处理的表非常多。特别是如果它是我上面提到的更复杂的路径之一。

相关内容