excelperfect

SUMPRODUCT函數(shù)功能強(qiáng)大，與其他函數(shù)配合，往往能解決很多問題。本文介紹的技巧將是SUMPRODUCT函數(shù)與COUNTIF函數(shù)組合，統(tǒng)計(jì)單元格中的單詞或句子中出現(xiàn)的指定的字符列表中的字符的個(gè)數(shù)。

如下圖1所示，在單元格B3中，共出現(xiàn)了字符/單詞列表中的“a”、“b”、“f”共3個(gè)字符；在單元格B4中，字符/單詞列表中的所有4個(gè)字符都出現(xiàn)了；其他單元格依此類推。

圖1

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

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

下拉至單元格C9。

下面，我們來看看這個(gè)公式的運(yùn)行原理。

公式中的主要部分是：

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

我們知道，COUNTIF函數(shù)的語法：COUNTIF(Range, Criteria)，將遍歷參數(shù)Range指定的區(qū)域/數(shù)組，統(tǒng)計(jì)其中滿足參數(shù)Criteria指定的條件的數(shù)據(jù)的數(shù)量。在示例中，參數(shù)Range的值為：

B3

而參數(shù)Criteria指定的值為：

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

是*與單元格區(qū)域E3:E6中單元格值首尾連接。這里插入了通配符“*”，可以匹配含有單元格區(qū)域E3:E6中的值的數(shù)據(jù)。

SUMPRODUCT函數(shù)在這里的一個(gè)重要作用就是，強(qiáng)制將參數(shù)Criteria指定的值轉(zhuǎn)換成數(shù)組。然后，COUNTIF函數(shù)就在單元格B3中查找前面或者后面與E3:E6中的字符相連的字符出現(xiàn)的次數(shù)。

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

可解析為：

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

可解析為：

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

得到：

{1;1;1;0}

將其傳遞給SUMPRODUCT函數(shù)：

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

即：

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

在僅提供給SUMPRODUCT函數(shù)一個(gè)數(shù)組時(shí)，將只是簡單地將數(shù)組中的元素相加，即得到結(jié)果：

3