如何用COUNTIF+VLOOKUP按国家统计Excel问卷中关键词出现次数？

云上老周

2026-7-26

解决方案

通用Excel版本公式（兼容所有版本）

假设你的数据结构为：

A列：填写国家名称
B列：调查问卷的多行文本内容
统计表格：D列列出所有目标国家，E1、F1、G1等单元格分别填写关键词（price、quality、delivery）

1. 统计某国家某关键词的总出现次数（单单元格内多次提及也计入）

在对应统计单元格（比如E2，对应D2的国家和E1的关键词）输入公式：

=SUMPRODUCT(--($A:$A=$D2), (LEN($B:$B)-LEN(SUBSTITUTE($B:$B, E$1, "")))/LEN(E$1))

公式说明：

--($A:$A=$D2)：将A列中匹配目标国家的行转为数值1，不匹配转为0
(LEN($B:$B)-LEN(SUBSTITUTE($B:$B, E$1, "")))/LEN(E$1)：计算每个B列单元格内关键词的出现次数（用原文本长度减去替换掉关键词后的长度，再除以关键词本身长度）
SUMPRODUCT：将两个数组相乘后求和，得到该国家所有文本中关键词的总次数

2. 仅统计包含关键词的行数（单单元格内多次提及只算1次）

如果不需要统计单单元格内的重复提及，只需要知道有多少行文本包含该关键词，使用公式：

=SUMPRODUCT(--($A:$A=$D2), --(ISNUMBER(SEARCH(E$1, $B:$B))))

公式说明：

--(ISNUMBER(SEARCH(E$1, $B:$B)))：判断B列单元格是否包含关键词，是则转为1，否则转为0

Excel 365/2021动态数组简化方案

如果使用Excel 365或2021版本，可通过动态数组公式自动生成整个统计表格，无需手动下拉填充：
假设国家列表在D2:D21，关键词列表在E1:G1，在E2单元格输入公式：

=BYROW(D2:D21, LAMBDA(country, BYCOL(E1:G1, LAMBDA(keyword, SUMPRODUCT(--($A:$A=country), (LEN($B:$B)-LEN(SUBSTITUTE($B:$B, keyword, "")))/LEN(keyword))))))

输入后公式会自动扩展填充所有国家和关键词的统计结果。