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

问题描述

我有一个包含四列的Google Spreadsheets.

I have a Google Spreadsheets with four columns.

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

I need to populate the Column D with all the possible combinations of the values in Columns A and C. Please take a look a the capture to see what I mean.

我发现以下公式，但它用于2列.但是我有3列.

I found this following formula, but it use for 2 column.but i have 3 column.

我正在使用以下代码:

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

推荐答案

答案:

您可以将此公式传递回自身，以获得三列相同的结果.

=ArrayFormula(transpose(split(concatenate(rept(A1:A&char(9),counta(ArrayFormula(transpose(split(concatenate(rept(B1:B&char(9),counta(C1:C))),char(9)))&" "&transpose(split(rept(concatenate(C1:C&char(9)),counta(B1:B)),char(9))))))),char(9)))&" "&transpose(split(rept(concatenate(ArrayFormula(transpose(split(concatenate(rept(B1:B&char(9),counta(C1:C))),char(9)))&" "&transpose(split(rept(concatenate(C1:C&char(9)),counta(B1:B)),char(9))))&char(9)),counta(A1:A)),char(9))))

此公式将对B1:B的引用替换为公式，该公式将转置B和C列的所有组合，并将所有那些组合与在A列中的组合进行转置.

This formula replaces the references to B1:B to a formula which transposes all combinations of column B and column C, and transposes all those combinations with those in column A.

希望对您有帮助！

这篇关于生成Google表格中3列的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！