![从 Excel 中的两列中提取匹配的值](https://linux22.com/image/1531772/%E4%BB%8E%20Excel%20%E4%B8%AD%E7%9A%84%E4%B8%A4%E5%88%97%E4%B8%AD%E6%8F%90%E5%8F%96%E5%8C%B9%E9%85%8D%E7%9A%84%E5%80%BC.png)
我有两份参加某项活动的人员名单。这两份名单由两个不同的用户生成。每个访客都有一个唯一的访客 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 列,就不需要数组公式来获得答案。
创建一个列,通过一个常量指标来显示是否存在匹配。在此示例中,您可以填写 F 列或插入一个新列 G,使每个 E 与 B 完全匹配。按 F5,然后向下填充:
=IF(IFERROR(match(E5,B:B,0)),0,1)
新建一个列,在您的匹配列中搜索您的指标。您的开始前的第一个数字是 1,例如 0。该公式使用 INDIRECT 根据上一个匹配停止的位置构建匹配,并且此公式可重复。上面的例子,填写了 J。J4=4,J5 并向下填充:
=MATCH(1,INDIRECT("Sheet1!F"&F4+1&":$F$30000"),0)+F4
如果需要,使用 INDEX 提取实际数据。以 K5 为例,填写:
=INDEX(E:E,J5)
如果需要,使用宏或手动清理索引行。如果您使用“1”作为匹配指示符,则宏或更复杂的公式可以使用 SUM 自动从多余的索引行中删除 #N/A。