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

问题描述

我需要提供公式化的解决方案，以将在其自身顶部堆叠的列范围复制给定的次数.我发现了一个丑陋的解决方案，方法是将序列函数(以获得1,2,3 ... n)合并到一个用于文本操作(左)的arrayformula中.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屋！