☰

从 Excel 中的两列中提取匹配的值

tag-icon • tag-icon microsoft-excel worksheet-function
从 Excel 中的两列中提取匹配的值

我有两份参加某项活动的人员名单。这两份名单由两个不同的用户生成。每个访客都有一个唯一的访客 ID，因此我从每个名单中提取了访客 ID 列，并将它们并排粘贴在新工作表中。我需要做的是使用某种公式，将出现在两列中的任何访客 ID 显示在第三列中。简而言之，我需要知道这两个用户记录了多少相同的访客。

其中一个列表也比另一个列表长得多，以防万一。每个列表中的 ID 也没有任何数字顺序。

基本上，我想看到类似这样的内容：

List from User 1        List from User 2        Matching IDs
    1129                      1781                   1129
    1200                      1743                   1776
    1525                      1129                   1200 
    1695                      1350                   1525
    1928                      1776
    1972                      1644
    1776                      1200
    1297                      1525
    1980                      1153

我查阅了几个教程，但没有一个符合我的要求。执行此功能的公式的正确语法是什么？

答案1

在 A 列中列出来自 User1 的列表，在 B 列中列出来自 User2 的列表，在 C 列中，在 C2 开始写入以下公式：

=IFERROR(INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($B$2:$B$10)-1,9999999999),ROW()-1)),"")

数组公式同时按下Ctrl+ Shift+ 将公式向下拖动，如果匹配则返回 ID（匹配将跟在后面，然后为空）或为空 A2：A10 是没有标题的 A 列中的数据，（列表用户 1）将其更改为您的数据引用 B2：B10 是没有标题的 B 列中的数据，（列表用户 2）将其更改为与您的数据引用相对应 保留 $ 表示固定引用 Countif 将找到匹配的 ID 如果匹配，将返回行号 拖动公式时，Index 将返回对应于最小行（）的 ID Enter






根据您的参考进行更新

=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")

因为你的数据从第 5 行开始，所以我假设你的公式也将从第 5 行开始，
我更改了公式以对应此

=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")

在此处输入图片描述

答案2

Vlookup（在匹配的ID列中）应该可以完成这项工作。

公式如下 =Vlookup(短列表单元格,长列表,1,FALSE)

如果为 False，则表示没有匹配，返回 NA，只需过滤掉即可获得最终列表。

答案3

仅当值存在于两列中时，带 IFERROR 的 VLOOKUP 才会在新列中返回值。然后，您将能够使用数据过滤器仅显示匹配项。根据您的数据，假设 A 列中的“用户 1”和 B 列中的“用户 2”：

=IFERROR(VLOOKUP(B2,A:A,1,FALSE),"")

为了保证结果，表格需要按升序排列，但由于只有两个数据点，我仅将表格范围命名为由 A 列组成，将查找列命名为 1。由于查找的数据点只有一个，因此它可以处理无序的数字。

答案4

如果有人查找类似问题并需要公式，则只要您可以接受额外的 2 列，就不需要数组公式来获得答案。

  1. 创建一个列，通过一个常量指标来显示是否存在匹配。在此示例中，您可以填写 F 列或插入一个新列 G，使每个 E 与 B 完全匹配。按 F5，然后向下填充：=IF(IFERROR(match(E5,B:B,0)),0,1)

  2. 新建一个列，在您的匹配列中搜索您的指标。您的开始前的第一个数字是 1，例如 0。该公式使用 INDIRECT 根据上一个匹配停止的位置构建匹配，并且此公式可重复。上面的例子，填写了 J。J4=4，J5 并向下填充：=MATCH(1,INDIRECT("Sheet1!F"&F4+1&":$F$30000"),0)+F4

  3. 如果需要，使用 INDEX 提取实际数据。以 K5 为例，填写：=INDEX(E:E,J5)

如果需要，使用宏或手动清理索引行。如果您使用“1”作为匹配指示符，则宏或更复杂的公式可以使用 SUM 自动从多余的索引行中删除 #N/A。

相关内容