如何在 Google Sheets 中创建所有可能的配对组合而不重复? [英] How to create all possible pair combinations without duplicates in Google Sheets?
问题描述
如何对 excel/google 表格单元格执行迭代以获得成对组合?
How to perform iteration over excel/google sheets cells to get pairwise combinations?
"string1"
"string2"
"string3"
...
"string10"
我正在考虑编写一个可以迭代这些字符串以创建以下内容的函数:
I'm looking at writing a function that can iterate over these strings to create the following:
"string1, string2"
"string1, string 3"
...
"string 1, string 10"
"string 2, string 3"
...
"string 2, string 10"
"string3, string 4"
... ...
"string9 string10".
这可能在谷歌表格中吗?
Is this possible in google sheets?
推荐答案
我不得不同意@Max 的观点，原生函数很难，或者至少是冗长的，但在 Google Sheets 中是可能的
I have to agree with @Max that it is difficult with native functions, or at least long-winded, but it is possible in Google Sheets
=ArrayFormula(query({if((row(A:A)<=counta(A:A)^2)*(int((row(A:A)-1)/counta(A:A))<mod((row(A:A)-1),counta(A:A))),
vlookup(int((row(A:A)-1)/counta(A:A)),{row(A:A)-1,A:A},2)&vlookup(mod((row(A:A)-1),counta(A:A)),{row(A:A)-1,A:A},2),"")},"select Col1 where Col1<>''"))
注意 1 - 方法
以包含 10 个字符串的列表为例.
Using a list of 10 strings as an example.
(1) 使用
{row(A:A)-1,A:A}
{row(A:A)-1,A:A}
(2) 使用 VLOOKUP 中的行号获得对的第一个字符串
(2) Use the row number in a VLOOKUP to get the first string of the pair with
vlookup(int((row(A:A)-1)/counta(A:A)),{row(A:A)-1,A:A},2)
vlookup(int((row(A:A)-1)/counta(A:A)),{row(A:A)-1,A:A},2)
Row number-1 int((row(A:A)-1)/counta(A:A)) String
0 0 String1
1 0 String1
...
9 0 String1
10 1 String2
...
20 2 String3
...
99 9 String10
(3) 使用 VLOOKUP 中的行号获得对的第二个字符串
(3) Use the row number in a VLOOKUP to get the second string of the pair with
vlookup(mod((row(A:A)-1),counta(A:A)),{row(A:A)-1,A:A},2)
vlookup(mod((row(A:A)-1),counta(A:A)),{row(A:A)-1,A:A},2)
Row number-1 mod((row(A:A)-1),counta(A:A)) String
0 0 String1
1 1 String2
2 2 String3
...
9 9 String10
10 0 String1
11 1 String2
...
99 9 String10
请注意，该列表将包含不需要的对，例如 String1String1 和 String2String1.
Note that the list will include unwanted pairs like String1String1 and String2String1.
(4) 使用 if 条件将不需要的对设置为 ""
(4) Set unwanted pairs to "" with if condition
if((row(A:A)<=counta(A:A)^2)*(int((row(A:A)-1)/counta(A:A))
注意 1 使用过滤器去除@Max Makhrov 建议的不需要的对会更短.
if((row(A:A)<=counta(A:A)^2)*(int((row(A:A)-1)/counta(A:A))
Note 1 Using a filter to remove unwanted pairs as suggested by @Max Makhrov would be shorter.
(5) 使用 Query 删除空白行.
(5) Use Query to remove blank rows.
注意 2 - 行数限制
因为生成冗余对然后删除，此方法需要 N^2 行在工作表中，其中 N 是字符串的数量，而不是 N*(N-1)/2，这是 N 的不同对的数量对象.相反，对于具有 N 行的工作表，可以通过这种方式处理的最大字符串数是 floor(sqrt(N))，例如对于 1,000 行的工作表 s=floor(sqrt(1000))=31.
Because redundant pairs are generated then removed, this method requires N^2 rows to be in the sheet where N is the number of strings rather than N*(N-1)/2 which is the number of distinct pairs of N objects. Conversely, the maximum number of strings s which can be processed this way for a sheet with N rows is floor(sqrt(N)), e.g. for a sheet with 1,000 rows s=floor(sqrt(1000))=31.
注意 3 - 一种避免生成冗余对的可能方法
一种可视化我尝试做的事情的方法如下，其中数组元素表示输出行 (A:A)，行和列标题表示相应的值，这些值用作查找以获得像 (string 1, 字符串 1), (字符串 1 字符串 2) 等
One way of visualising what I have tried to do is as follows, where the array elements represent output rows (A:A) and the row and column headers indicate corresponding values which are used as lookups to get pairs like (string 1, string 1), (string 1 string 2) etc.
使用整数除法和上述 MOD 函数将输出行映射到查找值相当容易.
It is fairly easy to do the mapping from output rows to lookup values using integer division and the MOD function as above.
我们真正想做的是得到这样的非冗余对
What we would really like to do is to get non-redundant pairs like this
但是您如何将输出行 1-10 映射到查找值对 1-5 ?
but then how would you map from output rows 1-10 to pairs of lookup values 1-5 ?
我希望通过一些数学提供(至少在原则上)一种无需首先生成所有 N^2 对即可立即获得 N(N-1)/2 非冗余对的方法，以表明这是可能的.
I hope to show that this is possible with a bit of maths providing (at least in principle) a way to get the N(N-1)/2 non-redundant pairs straight away without first generating all N^2 pairs.
上三角部分第 1 行到第 r 行单元格的计数 S 是总计数 N(N-1)/2 减去其下方行的计数 (Nr)(Nr-1)/2
The count S of cells in rows 1 to r of the upper triangular part above is the total count N(N-1)/2 minus the count in the rows below it (N-r)(N-r-1)/2
这可以重新排列如下
这是 r 的二次方，所以我们可以使用正则公式来解决它
This is a quadratic in r so we can solve it using the regular formula
给予
因此该行由上述 r 公式的上限给出.
So the row is given by the ceiling of the above formula for r.
将 r 的上限代入上面的第二个等式，给出第 r 行末尾的数字(比如 T)
The number (say T) at the end of row r is given by substituting the ceiling of r back into the second equation above
最后对应于 S 的列由
and finally the column corresponding to S is given by
现在定义一个命名范围N，其值为
Now define a named range N whose value is
=counta(A:A)
和一个命名范围 M，其值为
and a named range M whose value is
=2*N-1
那么最后你需要选择stringA(矩阵的第r行)的公式是
Then finally the formula you need to select stringA (the row r of the matrix) is
=iferror(ArrayFormula(vlookup(ceiling((M-sqrt(M^2-8*row(A:A)))/2,1),{row(A:A),A:A},2)),"")
你需要选择stringB(矩阵的c列)的公式是
and the formula you need to select stringB (the column c of the matrix) is
=iferror(ArrayFormula(vlookup(N+row(A:A)-(M*CEILING((M-SQRT(M^2-8*row(A:A)))/2,1)-CEILING((M-SQRT(M^2-8*row(A:A)))/2,1)^2)/2,{row(A:A),A:A},2)),"")
其中 D 列和 E 列仅用于测试目的.
where columns D and E are just included for testing purposes.
如果需要，只需将两个公式合并为一列即可.
Then it only remains to combine the two formulas into one column if desired.
这篇关于如何在 Google Sheets 中创建所有可能的配对组合而不重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
