基于可变条件的Excel动态重复序列TOCOL函数实现需求

需求说明 #

现有数据表格如下：

需要生成一维数组，将Criteria为TRUE的每行Sales值，重复对应Number of stores列的次数，最终得到：

xxx
xxx
xxx
yyy
yyy
zzz
zzz
zzz
zzz

现有公式问题 #

你当前使用的公式：
=IFERROR(TOCOL(CHOOSE(SEQUENCE(1,COUNTIF(_sloc_repl[Repl type],XLOOKUP(TEXTAFTER(CELL("filename",$A$1),"]"),_tool[Material type],_tool[Repl type])),1,0),FILTER(_tool[Criteria],_tool[Criteria]=TEXTAFTER(CELL("filename",$A$1),"]"))),,0),"")

问题在于用了统一的重复次数（首个匹配的3），没有关联每行对应的Number of stores数值，导致所有符合条件的Sales都重复相同次数。

修正后的公式 #

直接针对需求，使用FILTER筛选符合条件的行，再用BYROW结合REPT生成对应次数的重复值，最后用TOCOL展平：

=TOCOL(BYROW(FILTER(A2:D5,D2:D5=TRUE),LAMBDA(r,REPT(INDEX(r,1)&"|",INDEX(r,3)))),TRUE)

公式解释 #

1. FILTER(A2:D5,D2:D5=TRUE)：筛选出Criteria为TRUE的所有行，得到包含Sales、Region、Number of stores、Criteria的数组。
2. BYROW(..., LAMBDA(r, ...))：遍历筛选后的每一行r：
   • INDEX(r,1)：取当前行的Sales值
   • INDEX(r,3)：取当前行的Number of stores数值
   • REPT(INDEX(r,1)&"|",INDEX(r,3))：给Sales值加分隔符后重复对应次数（加分隔符是为了避免重复值连在一起，比如xxx重复3次变成"xxx|xxx|xxx"）
3. TOCOL(..., TRUE)：将生成的带分隔符的字符串按分隔符拆分并展平成一维数组，同时忽略空值。

如果你的数据是Excel结构化表格，可改用结构化引用：

=TOCOL(BYROW(FILTER(Table1,Table1[Criteria]=TRUE),LAMBDA(r,REPT(r[Sales]&"|",r[Number of stores]))),TRUE)

如果需要兼容原有动态匹配逻辑（结合文件名称判断），可替换条件部分：

=TOCOL(BYROW(FILTER(Table1,Table1[Criteria]=TEXTAFTER(CELL("filename",$A$1),"]")),LAMBDA(r,REPT(r[Sales]&"|",r[Number of stores]))),TRUE)

验证结果 #

使用修正后的公式，会生成你期望的结果：

xxx
xxx
xxx
yyy
yyy
zzz
zzz
zzz
zzz

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