英文:
Split the rows and organize as multi columed row using Google Sheets formula
问题
以下是翻译好的内容：
我有一个包含n行数据的表格，需要将这些行放在右侧。
这是块大小，如图所示
在此制作一个表格的副本：https://docs.google.com/spreadsheets/d/1aitrEnBpL7VV_qiylF4d00tLcKq9efU99hdO-5fa1RM/copy
块大小是一个变量，当块大小改变时，公式需要自动调整结果。
我尝试过以下公式：
=transpose(flatten(query(ARRAYFORMULA(A2:F8),"select * limit 4 offset 0")))
但我的公式需要手动推理和在表格变化时更新公式，这是不可行的。非常感谢一个可以接受块大小的单一公式来完成这个任务。
英文:
I have a table with n rows with data, the rows are required to be placed to the right.
Which is the chunk size, as shown
Make a copy of sheet here: https://docs.google.com/spreadsheets/d/1aitrEnBpL7VV_qiylF4d00tLcKq9efU99hdO-5fa1RM/copy
The chink size is a variable and when chunk is changed the formula needs to auto adjust the result.
I have tried with
=transpose(flatten(query(ARRAYFORMULA(A2:F8),"select * limit 4 offset 0")))
but my formula needs manual reasoning and update of the formula when the table changes which is not feasible. A single formula that does the job accepting chunk size is greatly appreciated.
答案1
得分: 1
=让(a，B11*columns(A$2:F$8)，b，roundup((rows(A2:F8)*columns(A2:F8))/a)，makearray(b，a，lambda(r，c，iferror(index(flatten(A$2:F$8)，(r-1)*a+c)))))
=当新函数处于活动状态并运行时，您也可以使用这个简单的函数：
=wraprows(tocol(A2:F8)，B11*columns(A2:F8)，)
英文:
You may try:
=let(a,B11*columns(A$2:F$8),b,roundup((rows(A2:F8)*columns(A2:F8))/a),makearray(b,a,lambda(r,c,iferror(index(flatten(A$2:F$8),(r-1)*a+c)))))
- for the reference I had the
chunk numberentered in the cellsB11andB17
If and when the new functions are active & operational fo ya; you can also use this simple one:
=wraprows(tocol(A2:F8),B11*columns(A2:F8),)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论