如何计算仅包含前几个字母匹配的单元格的行数

如何计算仅包含前几个字母匹配的单元格的行数

我需要计算两列单元格之间的匹配数。单元格中的数据是姓名。不幸的是,有些姓名末尾带有星号,因此它们不是完全匹配。有没有办法计算两个单元格之间仅包含前 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<>""))

相关内容