Excel公式技巧30： 查找单元格数据中有多少个列表中的字符/单词

excelperfect

SUMPRODUCT函数功能强大，与其他函数配合，往往能解决很多问题。本文介绍的技巧将是SUMPRODUCT函数与COUNTIF函数组合，统计单元格中的单词或句子中出现的指定的字符列表中的字符的个数。

如下图1所示，在单元格B3中，共出现了字符/单词列表中的“a”、“b”、“f”共3个字符；在单元格B4中，字符/单词列表中的所有4个字符都出现了；其他单元格依此类推。

图1

在单元格C3中，使用的公式是：

=SUMPRODUCT(COUNTIF(B3,'*'&$E$3:$E$6&'*'))

下拉至单元格C9。

下面，我们来看看这个公式的运行原理。

公式中的主要部分是：

COUNTIF(B3,'*'&$E$3:$E$6&'*')

我们知道，COUNTIF函数的语法：COUNTIF(Range, Criteria)，将遍历参数Range指定的区域/数组，统计其中满足参数Criteria指定的条件的数据的数量。在示例中，参数Range的值为：

B3

而参数Criteria指定的值为：

'*'&$E$3:$E$6&'*'

是*与单元格区域E3:E6中单元格值首尾连接。这里插入了通配符“*”，可以匹配含有单元格区域E3:E6中的值的数据。

SUMPRODUCT函数在这里的一个重要作用就是，强制将参数Criteria指定的值转换成数组。然后，COUNTIF函数就在单元格B3中查找前面或者后面与E3:E6中的字符相连的字符出现的次数。

COUNTIF(B3,'*'&$E$3:$E$6&'*')

可解析为：

COUNTIF(B3, {'*a*';'*b*';'*f*';'*d*'})

可解析为：

COUNTIF('abcefgh',{'*a*';'*b*';'*f*';'*d*'})

得到：

{1;1;1;0}

将其传递给SUMPRODUCT函数：

=SUMPRODUCT(COUNTIF(B3,'*'&$E$3:$E$6&'*'))

即：

=SUMPRODUCT({1;1;1;0})

在仅提供给SUMPRODUCT函数一个数组时，将只是简单地将数组中的元素相加，即得到结果：

3