Google Sheets用ArrayFormula实现横向部分文本匹配多列计数
Google Sheets 自动横向统计单行答题选项次数的ArrayFormula实现
适用场景
你已经搭建好了测验答题统计的表格:A列存储用户姓名,从B列开始往后的列对应用户逐题的答题内容,包含单选项、多选项连写场景(比如单元格内容为AB、AC、CA这类字符串)。需要实现:
- 新用户提交答题后,无需手动复制下拉公式
- 自动在每行答题区域的末尾,分别统计该行所有答题内容中A、B、C、D四个选项的出现总次数
- 支持多选项连写的部分文本匹配,不会因为字符连写漏算单个选项
可直接套用的核心公式
把公式输入到你要放统计结果的第一个单元格(也就是第一行有用户数据的位置,A2单元格正下方的统计区域起点,比如F2位置):
=BYROW(A2:Z, LAMBDA(row, IF(INDEX(row, 1)="",, LET( 拼接文本, TEXTJOIN("", TRUE, INDEX(row, 2):INDEX(row, COLUMNS(row)-1)), 统计选项, {"A","B","C","D"}, LEN(拼接文本) - LEN(SUBSTITUTE(拼接文本, 统计选项, "")) ) ) ))
输入完成按下回车,公式会自动溢出计算所有有用户姓名的行,横向自动生成4列结果,从左到右依次对应A、B、C、D四个选项的出现次数。
公式运行逻辑
BYROW会逐行遍历从第2行开始的所有数据行,只要当前行A列(也就是第一个单元格)没有用户姓名,就自动留空,不计算无意义的0值- 对有用户姓名的行,用
TEXTJOIN把该行从B列开始到最后一个答题列的所有内容拼接成连续的长字符串,不管单元格里是单选项还是连写的多选项,都会被合并成统一文本 - 通过
SUBSTITUTE把拼接文本里的目标选项全部删掉,计算原文本和删改后文本的长度差:因为单个选项是1个字符,长度差直接对应该选项在整行答题内容中的出现次数 - 一次性传入A、B、C、D四个统计选项,自动横向生成四列的计数结果
自定义调整方法
- 如果你的答题区域覆盖范围超过Z列,把公式里的
A2:Z改成对应的实际范围(比如到AZ列就写成A2:AZ)即可 - 如果需要兼容答题中的小写选项(比如a、b),只需要在
TEXTJOIN那行外面套一层UPPER,也就是把拼接文本的部分改成UPPER(TEXTJOIN("", TRUE, ...)),不管大小写都能准确识别 - 如果需要新增统计的选项(比如E、F),只需要修改
统计选项对应的数组内容为{"A","B","C","D","E","F"},公式会自动对应生成新增选项的计数列
效果验证
按照示例场景:某用户的逐题答题内容为A、AB、A、C,公式运行结果为:
- A选项:拼接后文本为
AABAC,删除所有A后剩余BC,长度差为3,对应出现3次 - B选项:删除所有B后剩余
AAAC,长度差为1,对应出现1次 - C选项:删除所有C后剩余
AABA,长度差为1,对应出现1次
完全匹配预期的统计效果,支持多选项连写的计数场景。
内容的提问来源于stack exchange,提问作者Dale Nelson
相关产品推荐
相关产品推荐

