如何用数组公式统计Google Sheets中值截至当前位置的出现次数

问题回顾 #

你通过公式 =iferror(split(regexreplace(C2, "(.)", "$1_"), "_"),"") 将单词拆分为单个大写字母的数组，需要统计每个位置的字母从数组开头到当前位置的出现次数。例如输入ZOOTOPIA时，对应位置应返回[1,1,2,1,3,1,1,1]。之前尝试的两个公式均无法满足需求：

• =ARRAYFORMULA(if(len(D2:2), COUNTIF(D$2:2, D2), ""))：返回的是每个字母在整行的总出现次数，而非截至当前位置的次数
• =ARRAYFORMULA(if(len(D2:2), COUNTIF(D$2:D2, D2), ""))：仅统计首个字母的出现次数，所有位置返回1

解决方案 #

方法1：使用MAP+LAMBDA（新版Google Sheets支持）

假设拆分后的字母数组从D2开始横向排列，可使用以下公式：

=ARRAYFORMULA(IF(LEN(D2:2), MAP(D2:2, COLUMN(D2:2), LAMBDA(val, col, COUNTIF(D$2:INDEX(D2:2, col), val))), ""))

• 逻辑：MAP遍历每个字母和其对应的列号，INDEX(D2:2, col)截取从开头到当前列的范围，再用COUNTIF统计该范围内当前字母的出现次数。

方法2：使用MMULT（兼容旧版Sheets）

如果无法使用LAMBDA函数，可借助矩阵乘法实现：

=ARRAYFORMULA(IF(LEN(D2:2), MMULT(--(TRANSPOSE(D$2:D2)=D2:2), SEQUENCE(COLUMNS(D2:2),1,1,0)), ""))

• 逻辑：通过TRANSPOSE(D$2:D2)=D2:2生成布尔矩阵标记元素匹配情况，--将布尔值转为0/1，MMULT计算每行累加和，即截至当前位置的出现次数。

验证示例 #

输入ZOOTOPIA拆分后，两个公式均会返回预期结果：

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