如何用单公式统计Google Sheet中关键词列在字符串列的出现次数

一、单个公式直接统计总次数 #

假设A列关键词范围是A2:A，B列混合词汇范围是B2:B（可根据实际数据范围调整），直接用以下公式就能算出总出现次数：

=SUMPRODUCT(LEN(B2:B)-LEN(SUBSTITUTE(B2:B, A2:A, "")))/LEN(A2:A)

公式拆解： #

• SUBSTITUTE(B2:B, A2:A, "")：将B列每个单元格里的A列关键词替换为空串
• LEN(B2:B)-LEN(SUBSTITUTE(...))：算出每个B单元格中被替换掉的字符总长度，除以关键词本身的长度（LEN(A2:A)），得到该关键词在对应B单元格的出现次数
• SUMPRODUCT：把所有关键词的出现次数累加，最终得到总次数

二、生成B列中实际出现的关键词数组 #

如果需要先提取出B列里确实出现过的A列关键词，用下面的公式：

=UNIQUE(FILTER(A2:A, BYROW(A2:A, LAMBDA(x, SUMPRODUCT(--(ISNUMBER(SEARCH(x, B2:B))))>0))))

公式拆解： #

• SEARCH(x, B2:B)：检查A列的关键词x是否在B列的任意单元格中存在
• SUMPRODUCT(--(ISNUMBER(...)))>0：判断该关键词是否至少出现过一次
• FILTER：筛选出符合条件的关键词
• UNIQUE：去除A列中重复的关键词（如果有的话）

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