如何在Excel/Google Sheets中统计两个单元格的匹配单词数量？

需求说明 #

示例：单元格A2内容为 This is an apple，单元格B2内容为 This was a rotten apple，需要返回两个单元格中匹配单词的数量（此例中匹配的是"This"和"apple"，返回值为2）。

Excel 解法 #

方法1：使用TEXTSPLIT（适用于Excel 365/2021及以上版本） #

直接拆分文本为单词列表，统计匹配项数量：

=SUMPRODUCT(--ISNUMBER(MATCH(TEXTSPLIT(A2," ",,TRUE),TEXTSPLIT(B2," ",,TRUE),0)))

• TEXTSPLIT(A2," ",,TRUE)：将A2按空格拆分，忽略空值，得到单词数组
• MATCH(...)：检查A2的每个单词是否存在于B2的单词数组中，存在返回位置，否则返回错误值
• ISNUMBER(...)：将匹配结果转为布尔值（找到为TRUE，否则FALSE）
• --：将布尔值转为1/0，SUMPRODUCT求和得到总匹配数

方法2：使用FILTERXML（适用于旧版Excel） #

如果没有TEXTSPLIT函数，可借助FILTERXML拆分文本：

=SUMPRODUCT(--ISNUMBER(MATCH(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),FILTERXML("<t><s>"&SUBSTITUTE(B2," ","</s><s>")&"</s></t>","//s"),0)))

• SUBSTITUTE+FILTERXML：通过构造XML格式文本，实现按空格拆分单词的效果

忽略大小写匹配 #

如果需要忽略大小写差异（比如"This"和"this"视为匹配），可统一转换为大写/小写后再匹配：

=SUMPRODUCT(--ISNUMBER(MATCH(UPPER(TEXTSPLIT(A2," ",,TRUE)),UPPER(TEXTSPLIT(B2," ",,TRUE)),0)))

Google Sheets 解法 #

Google Sheets支持TEXTSPLIT函数，直接使用类似公式即可：

=SUMPRODUCT(--ISNUMBER(MATCH(TEXTSPLIT(A2," ",,TRUE),TEXTSPLIT(B2," ",,TRUE),0)))

忽略大小写的版本同样适用：

=SUMPRODUCT(--ISNUMBER(MATCH(UPPER(TEXTSPLIT(A2," ",,TRUE)),UPPER(TEXTSPLIT(B2," ",,TRUE)),0)))

内容的提问来源于stack exchange，提问作者Danny Richman