动态数组：如何按条件生成求和结果？

嘿，我来帮你搞定这个动态数组拆分累加的问题！你现在要处理的是带tally列的表格数据——就是把tally里那种用逗号隔开的「数量/长度」条目，拆成单独的行，同时保留其他列的所有信息，最后把这些拆分后的行统统堆到新工作表里，对吧？

先看看你的原始数据格式：

你希望把它处理成下面这种格式，每一个tally条目对应单独一行，其他列的信息同步保留：

解决方案（动态数组公式） #

不用VBA，直接用Excel动态数组函数组合就能搞定，而且数据更新后结果会自动同步。把下面的公式放到新工作表的A1单元格（假设原始数据在Sheet1!A:F区域）：

=LET(
    原始数据, Sheet1!A2:F,
    行数, ROWS(原始数据),
    Tally列, INDEX(原始数据,,5),
    拆分Tally, TEXTSPLIT(Tally列, ", ",,TRUE),
    拆分后总行数, SUM(LEN(Tally列)-LEN(SUBSTITUTE(Tally列,",",""))+1),
    重复行索引, ROUNDUP(SEQUENCE(拆分后总行数)/BYROW(拆分Tally, LAMBDA(x, COLUMNS(x))),0),
    结果表, HSTACK(
        INDEX(原始数据,重复行索引,{1,2,3,4}),
        TOCOL(拆分Tally),
        INDEX(原始数据,重复行索引,6)
    ),
    VSTACK(Sheet1!A1:F1,结果表)
)

公式简单拆解 #

• LET：用来定义变量，让公式逻辑更清晰，不用反复引用同一个区域
• TEXTSPLIT：把每个单元格里的tally内容按, 拆分，TRUE参数用来忽略空值
• SUM(LEN(...)-LEN(SUBSTITUTE(...)))：统计所有tally条目总数，算出拆分后要生成多少行
• ROUNDUP+SEQUENCE：生成重复的原始行索引，比如第一行拆分出2个条目，就生成1,1的索引，用来匹配其他列的重复数据
• HSTACK+VSTACK：把拆分后的tally列和其他列横向合并，再把表头和结果纵向拼接成完整表格

如果你的Excel版本不支持TEXTSPLIT（比如旧版365或2021之前的版本），可以用FILTERXML替代拆分步骤，调整后的公式如下：

=LET(
    原始数据, Sheet1!A2:F,
    行数, ROWS(原始数据),
    Tally列, INDEX(原始数据,,5),
    拆分Tally, FILTERXML("<t><s>"&SUBSTITUTE(Tally列,", ","</s><s>")&"</s></t>","//s"),
    拆分后总行数, SUM(LEN(Tally列)-LEN(SUBSTITUTE(Tally列,",",""))+1),
    重复行索引, ROUNDUP(SEQUENCE(拆分后总行数)/BYROW(拆分Tally, LAMBDA(x, COUNTA(x))),0),
    结果表, HSTACK(
        INDEX(原始数据,重复行索引,{1,2,3,4}),
        拆分Tally,
        INDEX(原始数据,重复行索引,6)
    ),
    VSTACK(Sheet1!A1:F1,结果表)
)

备注：内容来源于stack exchange，提问作者heartmender