将多列数据混合为一列 [英] Mix data of multiple columns into one column
本文介绍了将多列数据混合为一列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将三列的内容合并为一列,如下例所示:
I want to combine the content of three columns into one column like in this example:
Col1 Col2 Col3
A 1 x
B 2 Y
所以结果是一个有 8 行的列,如下所示:
So the result is a column with 8 lines like this:
Result
A 1 X
A 1 Y
A 2 X
A 2 Y
B 1 X
B 1 Y
B 2 X
B 2 Y
我需要一个 Google 表格命令来执行此操作,而不是电子表格解决方案,因为在使用表格时结果值必须自动更改.
I need a Google Sheets command to do it, not a Spreadsheet solution because the result values must change automatically when using the sheet.
我已经尝试过 COMBINE
和 JOIN
,但都没有成功.或者,如果您向我展示如何仅组合三列中的两列,那么我可以在第二步中组合第三列.
I have already tried with COMBINE
and JOIN
without much success.
If, alternatively, you show me how to combine only two of the three columns, I then could combine the third one in a second step.
推荐答案
2 列:
=ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))
&" "&TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9))))
=ARRAYFORMULA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(
TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))&" "&
TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9)))&CHAR(9)),
COUNTA(C1:C)),CHAR(9)))&" "&
TRANSPOSE(SPLIT(CONCATENATE(REPT(C1:C&CHAR(9), COUNTA(
TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(B1:B)), CHAR(9)))&" "&
TRANSPOSE(SPLIT(CONCATENATE(REPT(B1:B&CHAR(9), COUNTA(A1:A))), CHAR(9)))))), CHAR(9))))
=ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(CONCATENATE(D1:D&CHAR(9)), COUNTA(C1:C)), CHAR(9)))
&" "&TRANSPOSE(SPLIT(CONCATENATE(REPT(C1:C&CHAR(9), COUNTA(D1:D))), CHAR(9))))
这篇关于将多列数据混合为一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文