excel - 如何使用 Excel 公式从多个工作表创建唯一列表

Question

我正在处理工资单，我需要从工作簿的多个工作表中提取唯一员工 ID，并将它们放在另一个工作表的同一工作簿中。

虽然我可以创建一个公式来获得那些Unique List，但是我无法使其动态，因为每个月我都会导入一个新的工作簿中的工作表，应该考虑到这一点，这不适用于我的公式。

我曾尝试使用 INDIRECT 函数来动态引用所有工作表，但可能是我在这里做错了什么，但没有成功。我知道它可以用 Power Query 来完成，但我不想改变数据库的结构也可以用 VBA ，但我不愿意，特别是想要使用 Excel 公式 完成它。

我在 Master_List Cell A2 中使用的以下公式

="ID_"&SORT(SUBSTITUTE(UNIQUE(
FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",,Blad1:Blad3!A2:A1000)
,",","</b><b>")&"</b></a>","//b")),"ID_","")+0)

我尝试将其用作公式中的 SHEETS Defined Name，但它给出了 #REF 错误

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())

上面的公式，用于抓取其他工作表数据进行计算，尝试通过将其包装在 INDIRECT 函数中实现它，但不起作用，我知道为什么不行是否有可能使其动态化或是否有任何解决方法。我希望能够解释。感谢您付出的努力和时间。

注意:这是为查询创建的示例数据。

Answer 1

如果您坚持使用公式，以下是我为完成这项工作所做的工作:

• 我在名称管理器中创建了一个名称公式:SHEETNAME。它指的是:=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
• 假设您至少有两个工作表(一个“主”工作表和任何其他工作表的 ID 在 A 列中；

现在我在masterlist的A2中使用了:

=UNIQUE(FILTERXML("<t><s>"&REDUCE("",SEQUENCE(SHEETS()-1,,2),LAMBDA(a,b,TEXTJOIN("</s><s>",,a,INDIRECT(INDEX(SHEETNAME,b)&"!A2:A100"))))&"</s></t>","//s"))

注意 1:我假设您只有 A2:A100 范围内的 ID，以保持简单。

注意 2: TEXTJOIN() 的这种使用可以相当快地达到它的极限。

注意 3: 您可以尝试嵌套第二个 UNIQUE() 以确保每次迭代处理尽可能少的记录。这有望确保不会很快达到 TEXTJOIN() 的限制。