如何在Google表格中创建所有可能的对组合而没有重复项? [英] 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".
在Google表格中可以吗?
Is this possible in google sheets?
推荐答案
我必须同意@Max的观点，即使用本机函数比较困难，或者至少需要很长一段时间，但在Google表格中还是可以的
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)添加一列，以使用0到9对字符串进行编号
(1) Add a column to number the strings from 0 to 9 using
{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)在有条件的情况下将不需要的对设置为"
(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)使用查询"删除空白行.
(5) Use Query to remove blank rows.
注释2-行数限制
因为生成了冗余对然后将其删除，所以此方法要求工作表中有N ^ 2行，其中N是字符串数，而不是N *(N-1)/2，这是N的不同对数对象.相反，对于具有N行的工作表，可以用这种方式处理的最大字符串数s是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)，行标题和列标题指示相应的值，这些值用作查找以获取诸如(字符串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(N-1)/2个非冗余对，而无需先生成所有N ^ 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行末尾的数字(例如T)是通过将r的上限代入上面的第二个方程式得出的
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的列由
现在定义一个命名范围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表格中创建所有可能的对组合而没有重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
