我需要计算两列单元格之间的匹配数。单元格中的数据是姓名。不幸的是,有些姓名末尾带有星号,因此它们不是完全匹配。有没有办法计算两个单元格之间仅包含前 3 个字母左右的匹配项的行数?
答案1
在某些 Excel 公式中,通配符很棘手(甚至不要使用正则表达式!)。但是,对于简单的计数,使用该COUNTIF
函数就可以了。因此,如果您的数据如下所示:
+----+-----+
| | A |
+----+-----+
| 1 | AAA |
| 2 | AAB |
| 3 | AAC |
| 4 | ABA |
| 5 | ABB |
| 6 | ABC |
| 7 | ACA |
| 8 | ACB |
| 9 | ACC |
| 10 | ADA |
+----+-----+
然后,您可以使用公式计算以“AA”开头的范围内的单元格
=COUNTIF(A1:A10,"AA*")
通配符指示符*
用于匹配多个单元格并对其进行计数。
答案2
作为替代方案,您可以使用 sumproduct 进行计数。由于 sumproduct 执行类似数组的计算,因此您需要避免使用完整的列引用并将其限制在实际数据范围内。在单元格 D2 中,我使用了以下公式并复制下来:
=SUMPRODUCT(--(LEFT($A$2:$A$8,LEN(C2))=C2))
此公式基本上会查看您想要匹配的长度,然后从您想要查看的数据左侧仅提取那么多字符。然后,它会将该列表与您要查找的内容进行比较,并生成一个 TRUE/FALSE 列表。要将 TRUE/FALSE 转换为 1 或 0,它需要进行不改变其值的数学运算。在本例中使用的是 --,但也可以同样轻松地使用 +0, *1。
答案3
与之前使用 SUMPRODUCT 数组的答案类似,但计数比较每行单元格之间的前三个字符,即 A1 到 B1、A2 到 B2 等。
=SUMPRODUCT(--(LEFT(A1:A5,3)=LEFT(B1:B5,3)))
当匹配整个单词但替换星号时,可以考虑解决星号问题的另一种可能方法。(星号是字符 42)。
=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")))
此方法确实将空单元格计为有效匹配,除非您向 SUMPRODUCT 添加另一个数组,以便仅在单元格不为空(小于或大于空)时进行计数。
=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")),--(A1:A5<>""))
稍加调整即可进行整列计算
=SUMPRODUCT(--(SUBSTITUTE(A:A,CHAR(42),"")=SUBSTITUTE(B:B,CHAR(42),"")),--(A:A<>""))