如何将字符串按分隔符拆分为横向数组以适配SUMPRODUCT函数？

嘿，我完全懂你碰到的麻烦——那个无VBA的拆分公式返回的是纵向数组，和SUMPRODUCT搭配时只能算出第一个元素的结果，根本没法处理后面的内容对吧？别慌，咱们只要把纵向数组转成横向的就行，下面给你具体的解决办法：

核心思路 #

SUMPRODUCT函数默认是按横向数组来遍历计算的，当你传入纵向数组时，它只会读取数组的第一行（也就是第一个拆分出来的元素）。所以关键步骤就是把拆分得到的纵向数组，用TRANSPOSE函数转置成横向数组，这样SUMPRODUCT就能识别所有元素了。

具体公式实现 #

假设你的目标字符串在A1单元格（比如"aa#b#ccc#1#2"），我们基于你找到的拆分公式做修改：

通用转置拆分公式（适配所有Excel版本） #

TRANSPOSE(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",LEN(A1))), (ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*LEN(A1)+1, LEN(A1))))

把这个转置后的数组和SUMPRODUCT结合，比如你要对拆分后的数值求和（假设最后两个是数字），可以写成：

SUMPRODUCT(--TRANSPOSE(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",LEN(A1))), (ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*LEN(A1)+1, LEN(A1)))))

这里的--是把文本格式的数字转换成数值格式，确保SUMPRODUCT能正常计算。

简化版（Excel 365/2021及以上） #

如果你用的是新版本Excel，直接用TEXTSPLIT函数就能一步生成横向数组，公式更简洁：

SUMPRODUCT(--TEXTSPLIT(A1,"#"))

公式各部分说明 #

• SUBSTITUTE(A1,"#",REPT(" ",LEN(A1)))：把字符串里的#替换成和原字符串一样长的空格，保证每个拆分段都能被完整提取
• MID(..., ..., LEN(A1))：按固定长度提取每一段内容
• TRIM()：去掉提取后多余的空格
• ROW(INDIRECT(...))：生成对应拆分元素数量的序列，用来定位每个拆分段的起始位置
• TRANSPOSE()：把纵向数组转成横向数组，适配SUMPRODUCT的计算逻辑
• --：将文本型数值转为纯数值，避免SUMPRODUCT忽略文本格式的数字

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