有没有更好的方法在 Google 表格中复制列数组 n 次 [英] Is there a better way to replicate a column array n times in Google Sheets

问题描述

我需要公式化的解决方案来复制一个列范围，该列范围在其自身顶部堆叠给定次数.我通过将一个序列函数(得到 1,2,3...n)合并到一个用于文本操作的数组公式(左)中，找到了一个丑陋的解决方案.Left 操作除了返回原始值之外什么都不做，但给了我包含序列数组的机会.

I need to formulaic solution to copy a column range stacking on top of itself a given number of times. I found one ugly solution by incorporating a sequence function (to get 1,2,3...n) into an arrayformula for a text operation (Left). The Left operation does nothing but return the original value, but gives me the opportunity to include the sequence array.

必须有更好的方法来做到这一点.

There must be a better way to do this.

问题:编写一个公式来创建一个列，其中命名的列范围在彼此的顶部堆叠任意次数.必须是单个公式，因为其他用户需要它来自动调整到新的长度.

Problem: Write a formula that creates a column where a named column range is stacked on top of each other an arbitrary number of times. Must be a single formula as other users will need this to self adjust to a new length automatically.

=flatten( transpose( arrayformula( left( Column_Range,len( Column_Range ) + 0 * 
 sign( sequence( 1,Number_of_Times_To_Repeat ) ) ) ) ) )

推荐答案

可以写成:

=ARRAYFORMULA(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*SIGN(SEQUENCE(1, C1))))))

或:

=INDEX(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*SIGN(SEQUENCE(1, C1))))))

或:

=INDEX(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*TRANSPOSE(ROW(INDIRECT("A1:A"&C1))^0)))))

或:

=INDEX(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*TRANSPOSE(SIGN(ROW(INDIRECT("A1:A"&C1))))))))

或:

=INDEX(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*SPLIT(REPT(1&"♀", C1), "♀")))))

或:

=INDEX(FLATTEN(TRANSPOSE(LEFT(A1:A5, LEN(A1:A5)*COLUMN(INDIRECT("A1:"&ADDRESS(1, C1)))^0))))

或:

=QUERY(FLATTEN(SPLIT(REPT("♀"&JOIN("♀", A1:A5), C1), "♀",,)), "offset 1")

或:

=FLATTEN(SPLIT(REPT(QUERY(A1:A5,,9^9)&" ", C1), " ",,))

这篇关于有没有更好的方法在 Google 表格中复制列数组 n 次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！