如何使用SEQUENCE或类似函数迭代该Excel动态公式？

我正在设置Excel动态公式，用VSTACK函数时卡壳了。需求是针对CATEGORIES里的每一行，用SEQUENCE实现迭代，替换现在重复写了25次的INDEX加FILTER组合。试过定义CATEGORIESNo（ROWS(CATEGORIES)）和CATEGORIESSEQ（SEQUENCE(CATEGORIESNo,1,1,1)）但没成功，目前在用的公式是：

=LET(CATEGORIES,FILTER(UNIQUE(Sheet2!D:D),(UNIQUE(Sheet2!D:D)<>"Type")*(UNIQUE(Sheet2!D:D)<>0)),
VSTACK(
INDEX(CATEGORIES,1,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,1,0)),
INDEX(CATEGORIES,2,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,2,0)),
INDEX(CATEGORIES,3,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,3,0)),
INDEX(CATEGORIES,4,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,4,0)),
INDEX(CATEGORIES,5,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,5,0)),
INDEX(CATEGORIES,6,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,6,0)),
INDEX(CATEGORIES,7,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,7,0)),
INDEX(CATEGORIES,8,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,8,0)),
INDEX(CATEGORIES,9,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,9,0)),
INDEX(CATEGORIES,10,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,10,0)),
INDEX(CATEGORIES,11,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,11,0)),
INDEX(CATEGORIES,12,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,12,0)),
INDEX(CATEGORIES,13,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,13,0)),
INDEX(CATEGORIES,14,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,14,0)),
INDEX(CATEGORIES,15,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,15,0)),
INDEX(CATEGORIES,16,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,16,0)),
INDEX(CATEGORIES,17,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,17,0)),
INDEX(CATEGORIES,18,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,18,0)),
INDEX(CATEGORIES,19,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,19,0)),
INDEX(CATEGORIES,20,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,20,0)),
INDEX(CATEGORIES,21,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,21,0)),
INDEX(CATEGORIES,22,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,22,0)),
INDEX(CATEGORIES,23,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,23,0)),
INDEX(CATEGORIES,24,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,24,0)),
INDEX(CATEGORIES,25,0),
FILTER(Sheet2!B:B,Sheet2!D:D=INDEX(CATEGORIES,25,0))
))

优化方案 #

不用手动重复写INDEX和FILTER，用MAP函数配合VSTACK就能实现自动迭代：

=LET(
    UniqueD, UNIQUE(Sheet2!D:D),
    CATEGORIES, FILTER(UniqueD, (UniqueD<>"Type")*(UniqueD<>0)),
    VSTACK(MAP(CATEGORIES, LAMBDA(cat, VSTACK(cat, FILTER(Sheet2!B:B, Sheet2!D:D=cat)))))
)

逻辑说明

1. 先提取Sheet2中D列的唯一值并过滤掉无效项（"Type"和0），得到CATEGORIES列表；
2. 用MAP遍历CATEGORIES的每个类别，对每个类别执行：先输出类别名称，再输出该类别对应的B列数据；
3. 最后用外层的VSTACK把所有MAP返回的垂直区域合并成一个完整的动态结果，不管CATEGORIES有多少行都能自动适配。

如果一定要用SEQUENCE，也可以结合REDUCE来实现迭代：

=LET(
    UniqueD, UNIQUE(Sheet2!D:D),
    CATEGORIES, FILTER(UniqueD, (UniqueD<>"Type")*(UniqueD<>0)),
    CatCount, ROWS(CATEGORIES),
    REDUCE("", SEQUENCE(CatCount), LAMBDA(acc, i, VSTACK(acc, INDEX(CATEGORIES,i), FILTER(Sheet2!B:B, Sheet2!D:D=INDEX(CATEGORIES,i)))))
)

这个公式用SEQUENCE生成从1到类别总数的序列，再通过REDUCE逐个迭代每个序号，把对应的类别和筛选结果追加到累积结果里。

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