如何在 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".
这可能在谷歌表格中吗?
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) 添加一列以使用 0 到 9 对字符串进行编号
(1) Add a column to number the strings from 0 to 9 using
{行(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((行(A:A)<=counta(A:A)^2)*(int((行(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))，例如对于 1000 行的工作表 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 ?
我希望通过一些数学来证明这是可能的.
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对应的列由下式给出
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 表格中创建所有可能的配对组合而不重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
