为Google SpreadSheets中的列生成所有可能的组合 [英] Generate all possible combinations for Columns in Google SpreadSheets
问题描述
我有一个带有三列A,B和C的Google SpreadSheets文档。
我需要使用列中所有可能的值组合填充列C A和B.请看一下捕获来看看我的意思。
我发现这是在Excel中完成的,
c $ c> E2 , C2
是:
= ArrayFormula(transpose(split(rept(concatenate(A2:A& char(9)),counta(B2:B)),char ))&& transpose(split(concatenate(rept(B2:B& char(9),counta(A2:A))),char(9))))
和 E2
是:
= ARRAYFORMULA(transpos e(拆分(rept(concateate(C2:C& char(9)),counta(D2:D)),char(9)))&& transpose(split(concatenate(rept(D2:D& char(9),counta(C2:C))),char(9))))
I have a Google SpreadSheets doc with three columns A, B and C.
I need to populate the Column C with all the possible combinations of the values in Columns A and B. Please take a look a the capture to see what I mean.
I found this to be done in Excel, here, but it doesn't work in google spreadsheets.
The formula should be useful even for more columns (e.g.: four instead of two)
Can I do this?
Try formula:
=ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))
&" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))))
The result:
car red
train red
car yellow
train yellow
car blue
train blue
You may use it again to add another list:
The formula is in cells C2
and E2
,
C2
is:
=ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))&" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))) )
and E2
is:
=ArrayFormula(transpose(split(rept(concatenate(C2:C&char(9)),counta(D2:D)),char(9)))&" "&transpose(split(concatenate(rept(D2:D&char(9),counta(C2:C))),char(9))) )
这篇关于为Google SpreadSheets中的列生成所有可能的组合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!