Google表格-用于将一列中的单元格合并为具有所有可能组合的两列的功能吗？ [英] Google Sheets - Function to combine cells in a column into two columns with all possible combinations?

问题描述

有人可以帮我编写一个Google表格功能来做到这一点吗？

Can anyone help me write a Google Sheets function for doing this:

需要执行哪些操作的Google表格示例：

Google Sheets example of what needs to be done:

第一列可以用 X行填充，每个都有一个名称。

The first column could be filled with X lines with a name in each one.

在这些名称中，目标和源列将填充名称，不重复对（如果您已经有 Gustavo | Jacinto，则不需要 Jacinto | Gustavo）。

From that names, the columns "target" and "source" would be filled with all possible combinations between the names, without repeating a pair (if you already have "Gustavo | Jacinto", you don't need "Jacinto | Gustavo").

我知道我可以使用python和其他工具解决此问题，但是我想使用Google表格的功能来解决此问题，因为这样可以更快地获得所需的响应。

I know I can solve this problem using python and other stuff, but I wanted to do this using Google Sheets' functions because it would be faster to get the response I need.

推荐答案

=ARRAYFORMULA(SPLIT(SORT(
 TRANSPOSE(SPLIT(CONCATENATE(REPT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&","&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))), (
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))<=
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))))*
 REGEXMATCH(CONCATENATE(","&SUBSTITUTE(TEXTJOIN(",",1,A2:A),",",",,")&","&CHAR(9)),"(,"&
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&",[^\t]*,"&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),","))))&",)|(,"&
 TRANSPOSE(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),","))))&",[^\t]*,"&
 UNIQUE(TRANSPOSE(SPLIT(JOIN(",",TEXTJOIN(",",1,A2:A)),",")))&",)"))&CHAR(9)),CHAR(9)))),","))

这篇关于Google表格-用于将一列中的单元格合并为具有所有可能组合的两列的功能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！