例如,我有 3 个单列列表。每个列表仅包含唯一值。这些列表没有排序,我想识别 3 个列表中出现的值。
AAA AAA AAA
BBB BBBB CCC
CCC CCC BBB
答案1
这个答案针对的是那些不熟悉 Excel 公式所有功能的用户(比如我)。
一个简单的解决方案是计算 A 列中项目在所有列中出现的次数(任何一列中都不允许有重复项)。
在单元格 D1 中 =COUNTIF($A$1:$C$3,A1) 这将计算 3 列中“AAA”出现的次数 答案 3
在单元格 D2 中 =COUNTIF($A$1:$C$3,B1)
这将计算 3 列中“BBB”出现的次数 答案 2
在单元格 D3 中 =COUNTIF($A$1:$C$3,C1) =3 这将计算 3 列中“CCC”出现的次数 答案 3
如果您有一个很长的列表,您可以应用数据,过滤并选择总共 3 行。
答案2
这是一个讨论数组函数的好机会!
在 D1 中输入此公式,然后按 CTRL-Shift Enter 输入。如果输入正确,公式将括在花括号 {} 中:
=LARGE((A$1:A$20=B$1:B$20)*(B$1:B$20=C$1:C$20)*ROW(A$1:A$20),ROW())
然后点击 D1 并向下填充,直到看到零结果。这将给出 A、B 和 C 列相同的行号。
其工作原理如下:表达式 A$1:A$20=B$1:B$20 询问范围 A1:A20 是否等于范围 B1:B20,并返回大批TRUE/FALSE 值:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
数组在 A 列等于 B 列的每个位置都包含 TRUE。这里,也就是位置 10、17 和 20。
表达式 B$1:B$20=C$1:C$20 对 B 列和 C 列执行类似的操作。将这两个 TRUE/FALSE 值数组相乘执行相当于 AND() 操作,并且分别将 TRUE 和 FALSE 转换为 1 和 0。
因此,现在我们有一个数组,其中除了列 A、B 和 C 相等的位置之外,其他位置均为 0 {0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;1;0;0;0}
,乘以ROW(A$1:A$20)
(行号数组)可得出一个包含列 A、B 和 C 相等的行号的数组:{0;0;0;0;0;0;0;0;0;10;0;0;0;0;0;0;17;0;0;0}
现在剩下要做的就是提取行号。函数 LARGE(array, n) 返回数组中第 n 大的值。此处的公式使用 ROW() 作为 n,因此当向下填充时,它将返回第 1 行中第 1 大的值、第 2 行中第 2 大的值,等等。
希望你喜欢。祝你好运。