为 Google 电子表格中的列生成所有可能的组合 [英] Generate all possible combinations for Columns in Google SpreadSheets

问题描述

我有一个包含 A、B 和 C 三个列的 Google 电子表格文档.

我需要用 A 列和 B 列中值的所有可能组合填充 C 列.请查看捕获以了解我的意思.

我发现这可以在 Excel 中完成，

解决方案

更新 201810

原始公式在大型数据集时崩溃.我描述了一种对任何大小的数据进行交叉连接的方法

公式在单元格C2和E2中，

C2 是:

=ArrayFormula(transpose(split(rept(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))"&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))) )

和 E2 是:

=ArrayFormula(transpose(split(rept(rept(concatenate(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?

解决方案

Update 201810

Original formula crashes for a big dataset. I described a way to make cross-join with any size of data here.

---

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 电子表格中的列生成所有可能的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！