☰

使用 Excel Unique 函数查找所有工作表相同列中的值

tag-icon • tag-icon microsoft-excel
使用 Excel Unique 函数查找所有工作表相同列中的值

我有一个 excel 文件用于自动处理项目中的名称，并且它的工作表数量取决于项目。我需要查找并列出所有工作表的特定列中的所有名称，且不重复。例如这里：

工作表 A 列 C 工作表 B 列 C 结果
马里奥 罗伯特 马里奥
大卫 伯纳德 罗伯特
凯文 大卫 伯纳德
马里奥 凯文
大卫

由于 Excel 2021 中有一个新的 UNIQUE 函数，我尝试使用它，但我不知道如何组织函数内的数组引用以考虑所有工作表中的不同列。

答案1

看起来我们不能在 UNIQUE 函数中使用 3D 引用，因此我们可以先将一个文本数组附加到另​​一个文本数组中：

=LET(
     a,Sheet1!C1:C3,
     b,Sheet2!C1:C4,
     tr,ROWS(a)+ROWS(b),
     MAKEARRAY(
               tr,
               1,
               LAMBDA(
                     r,
                     c,
                     IF(
                        r<=ROWS(a),
                        INDEX(a,r),
                        INDEX(b,r-ROWS(a))
                       )
                     )
              )
    )

因此，设a为 中的数据Sheet1!C1:C3，设b为 中的数据Sheet2!C1:C4。设tr为 的行数a和 的行数之和b（即总行数）。然后，创建一个tr高为 行、宽为 列的数组。按如下方式填充数组：对于索引小于或等于 长度的1行，使用 中相同索引的值。对于索引大于 长度的行，使用索引为 的索引减去 的长度的值。raarabra

我创建了一个名为 ARRAYUNION 的 LAMBDA，以便将来可以重复使用它。以下是公式：

=LAMBDA(a,b,LET(r,ROWS(a)+ROWS(b),MAKEARRAY(r,1,LAMBDA(r,c,IF(r<=ROWS(a),INDEX(a,r),INDEX(b,r-ROWS(a)))))))

您可以在这里看到其用法。

在此处输入图片描述

灵感来自excel-ticker.de，mrexcel.com一些经验教训sumproduct.com。第一个参考文献的实现更全面，可以处理多列数组并提供默认值。我认为对于这个问题来说它有点太复杂了，所以我创建了一些更简单的东西。

答案2

我采取了不同的方法（不成为一个特殊的人，LAMBDA()并且各种（大约八个左右？）功能MAKEARRAY()将在某一年随之而来）。

假设有三张工作表和一个公共列（Sheet1、Sheet2 和 Sheet3，此处使用 F 列）。公式如下：

=LET(AllSheets, CHOOSE({1,2,3},  Sheet2!F1:F10,  Sheet3!F1:F10,  Sheet4!F1:F10  ),

      UNIQUE(FILTERXML("<Group><Element>"& TEXTJOIN("</Element><Element>",TRUE,AllSheets)& "</Element></Group>","/*/*"))
 )

其中有一些非常简单的东西：

  1. 使用LET()可以将需要编辑的所有元素放在公式的开头，这样就无需像翻垃圾桶一样来编辑范围等。请注意名称的一系列范围周围的括号AllSheets：这样您就可以轻松地使用任意数量的范围。

  2. AllSheets然后是工作公式中的起始输入。它是的第二个参数，要选择的CHOOSE()项目。CHOOSE()

  3. CHOOSE()实际上会选择您在此处使用的所有选项。这就是 的作用{1,2,3}。不过，这只是一个公式编写创建。对于最终公式，您可能希望使用它SEQUENCE()来创建列表，因为您可能有许多工作表，并且不想手动输入这样的列表。

  4. 选择所有选项将构建一个二维表，其中包含每个工作表的一列和任意多行。您指定的范围内的任何单元格AllSheets都将在此内部表中显示为 0。由于下一层的存在，这些“额外”和不需要的材料不是问题。之所以CHOOSE()使用，是因为它不介意在同一次使用中从多个工作表中提取数据。INDEX()这是我通常这样做的方式，但我从未在多个工作表上绘图，并且发现它不会。

  5. TEXTKOIN()然后会接管，首先，转储 收集的所有空单元格CHOOSE()。其次，它会从所有内容中创建一个字符串，但使用分隔符字符串来设置FILTERXML()。所以现在你只有实际数据。此步骤意味着您可以使用当时的实际范围、您巧妙构建的动态范围，或者只是带有一百行额外行的范围（只是为了确保万无一失）。

  6. 下一层用于&将制作真实 HTML 字符串所需的起始和结束文本粘贴到当前字符串的开始和结束位置。这就是FILTERXML()我一直提到的实际函数的输入。最后使用的“XPath”就是像这样的简单“单个组，仅一种元素”字符串所需的全部内容。这会将数据从单个字符串重新更改为 Excel 可以在单独单元格中显示的真实数据元素。此时，所有空白都已删除。数据可能包含重复项。

  7. UNIQUE()现在接管并删除重复项。SORT()如果愿意的话可以这样做。

一旦你了解了各个部分，就没有什么复杂或难以理解的了。一年后当你去更新它时就可以理解了。

还有一些后续的想法。首先，您提到了不同的文件、不同的数据集合。对于任何方法，您都需要在某个地方、以某种方式“输入”它们的范围，因此您必须编辑公式或其他东西来实现这一点。这种方法将该条目放在开头，而不是在整个公式中加盐。您可以使用INDIRECT)with CHOOSE()，这样就可以在公式之外的数据区域中进行编辑，该数据区域填充了工作表名称。这些可以是动态的。或者填充几行宏，收集并转储工作表名称以供您的公式参考。这些主题有很多变化。您可以将辅助区域用于所有事物，而不仅仅是传统的“辅助列”，而是整个“辅助表”……或两个。

换个角度，CHOOSE()按顺序返回您在公式中构建的虚拟表中其自己的虚拟列中的每一列。如果您只将该部分放入单元格中，则数组将如下所示。TEXTJOIN()然后横向读取，然后向下读取，因此它使用所有列中的所有第一个条目，然后是所有第二个条目等来构建其字符串。如果您出于某种原因需要将每个工作表的列保持在一起，则需要在用 换行之前用 换CHOOSE()行，以便当它横向读取时，它会先“向下”读取一列，然后再转到包含第二列数据的下一行，依此类推。TRANSPOSE()TEXTJOIN()

我不知道可以指定多少个范围的限制。每张表都有一个，所以我猜没问题，因为它可能是 127 或 255，所以在出现问题之前你必须有那么多张表。人们可以将范围“堆叠”在范围中，这是一种隐喻性的迭代，无论如何都会得到更多，所以...

有一个很棒的新函数，ARRAYTOTEXT()，我选择不在本例中使用，因为它不会像 那样消除空格TEXTJOIN()。当我为 构建多元素 HTML 字符串时FILTERXML()，它可以是一个很棒的文本连接符，其严格的结果比所有元素的逗号空格分隔符更适合使用，因为它让我能够让 Excel 看到在何处应用不同类型的元素标记。但在这里，TEXTJOIN()是无与伦比的。

相关内容