Google Sheets 从单元格区域取值按指定次数重复n次并横向遍历的方法

需求说明 #

从指定单元格区域中提取对应值，按照给定的重复次数进行重复，再横向遍历迭代直至行尾。

原有公式问题说明 #

你当前使用的公式如下：

={ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(A1&",",A2:A4 ), ,999^99), ","))));ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(C1&",",C2:C4 ), ,999^99), ","))));ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(E1&",",E1:E4 ), ,999^99), ","))))}

存在两个核心问题：

• 第三组重复的次数范围引用错误：误写为E1:E4，会把E1的表头文本当成数值计算重复次数，导致结果出错
• 数组拼接逻辑不符合输出要求：使用分号;拼接三个结果数组，分号在Google Sheets中代表纵向堆叠，且额外添加了TRANSPOSE转置函数，最终输出为纵向排列的多列结果，和预期的横向排列不符

调整后可用公式 #

直接在对应单元格输入以下公式即可实现预期的横向输出效果：

=ARRAYFORMULA(TRIM({SPLIT(QUERY(REPT(A1&",",A2:A4),,999^99),","),SPLIT(QUERY(REPT(C1&",",C2:C4),,999^99),","),SPLIT(QUERY(REPT(E1&",",E2:E4),,999^99),",")}))

调整点说明 #

• 修正了E列重复次数的引用范围为E2:E4，保证重复次数取值正确
• 移除了所有TRANSPOSE转置函数，保留拆分后默认的行输出格式
• 将数组拼接符从分号;改为逗号,, 实现三组重复结果横向依次拼接，直至行尾

简化版公式（适配支持新函数的Google Sheets版本） #

如果你的Google Sheets支持LAMBDA系列函数，可以使用更简洁易维护的写法，后续新增列只需调整引用范围即可：

=LET(
  header_range,A1:E1,
  count_rows,3,
  arr,BYCOL(header_range,LAMBDA(v,IF(ISODD(COLUMN(v)),REPT(v&"|",SUM(OFFSET(v,1,0,count_rows,1))),))),
  TOROW(SPLIT(TEXTJOIN("|",1,arr),"|"),1)
)

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