英文:
Google Sheet: Creating combination of all names from a column
问题
我已经将您提供的内容翻译成中文，代码部分保持不变。以下是翻译好的部分：
我有一个具有以下结构的表格
| 名 | 姓 | 角色 |
|---|---|---|
| 尼克 | 弗瑞 | 经理 |
| 托尼 | 斯塔克 | 经理 |
| 布鲁斯 | 万瑟 | 员工 |
| 史蒂夫 | 罗杰斯 | 员工 |
| 克林特 | 巴顿 | 员工 |
我想创建另一个表格，其中包含所有员工的完整姓名的组合
所以，我希望输出如下：
| 姓名 |
|---|
| 布鲁斯 万瑟 - 史蒂夫 罗杰斯 |
| 布鲁斯 万瑟 - 克林特 巴顿 |
| 史蒂夫 罗杰斯 - 克林特 巴顿 |
我已经实现了筛选和连接部分的功能
=ARRAYFORMULA(FILTER(Roster!C2:C&" " & Roster!D2:D, Roster!K2:K = "Employee"))
但是，我不确定如何创建这些组合。
英文:
I have a sheet with following structure
| fname | lname | role |
|---|---|---|
| Nick | Fury | Manager |
| Tony | Stark | Manager |
| Bruce | Banner | Employee |
| Steve | Rogers | Employee |
| Clint | Barton | Employee |
I want to create another sheet with combinations of all complete names of employees
So, I would like the output to be
| names |
|---|
| Bruce Banner - Steve Rogers |
| Bruce Banner - Clint Barton |
| Steve Rogers - Clint Barton |
I got the filter and concatenate part working with
=ARRAYFORMULA(FILTER(Roster!C2:C&" " & Roster!D2:D, Roster!K2:K = "Employee"))
But, I am not sure how to name create the combinations
答案1
得分: 1
以下是代码的翻译部分：
You may try:
=index(split(query(unique(map(lambda(z,flatten(z& "🐠" &transpose(z)))(filter(A:A&" "&B:B,C:C="Employee")),lambda(c,join("🐠",sort(unique(transpose(split(c,"🐠")))))))),"select Col1 where Col1 contains '🐠'"),"🐠"))
[![enter image description here][1]][1]
Incase the [new functions][2] are operational for you; here's another approach:
=let(a,filter(A:A&" "&B:B,C:C="Employee"),b,byrow(a,lambda(z,wraprows(z,counta(a),z))),query(map(tocol(b),tocol(b,,1),lambda(c,d,if(lt(c,d),{c,d},))),"where Col1<>''"))
[![enter image description here][3]][3]
[1]: https://i.stack.imgur.com/8Eim0.png
[2]: https://support.google.com/docs/thread/200022527?hl=en
[3]: https://i.stack.imgur.com/GaTSr.png
请注意，这些是代码部分的翻译，不包括代码本身。如果您需要进一步的翻译或解释，请提出具体的问题。
英文:
You may try:
=index(split(query(unique(map(lambda(z,flatten(z& "🐠" &transpose(z)))(filter(A:A&" "&B:B,C:C="Employee")),lambda(c,join("🐠",sort(unique(transpose(split(c,"🐠")))))))),"select Col1 where Col1 contains '🐠'"),"🐠"))
Incase the new functions are operational for you; here's another approach:
=let(a,filter(A:A&" "&B:B,C:C="Employee"),b,byrow(a,lambda(z,wraprows(z,counta(a),z))),query(map(tocol(b),tocol(b,,1),lambda(c,d,if(lt(c,d),{c,d},))),"where Col1<>''"))
答案2
得分: 0
要获取不使用split()的唯一组合，请使用reduce()：
=LET(
names, FILTER(A2:A & " " & B2:B, C2:C = "Employee"),
REDUCE(
{ "name 1", "name 2" }, names,
LAMBDA(
result, name,
REDUCE(
result, FILTER(names, names <> name),
LAMBDA(
result, otherName,
IF(
name < otherName,
{
result;
{ name, otherName }
},
result
)
)
)
)
)
文本字符串操作和split()可能会误解特定格式的日期、布尔值和数字，以及看起来像这些类型的文本字符串。对于像Bruce Banner这样的名称，这不会有影响，但split()会将文本字符串如1 2、1 2 3和1111-2-3强制转换为日期，而上面的公式将忠实地复制原始值。
英文:
To get unique combinations without using split(), use reduce():
=let(
names, filter(A2:A & " " & B2:B, C2:C = "Employee"),
reduce(
{ "name 1", "name 2" }, names,
lambda(
result, name,
reduce(
result, filter(names, names <> name),
lambda(
result, otherName,
if(
name < otherName,
{
result;
{ name, otherName }
},
result
)
)
)
)
)
)
Text string manipulation and split() can mistreat dates, Booleans and numbers in certain formats, together with text strings that look like those types. That won't matter with names like Bruce Banner but split() will coerce text strings like 1 2, 1 2 3 and 1111-2-3 to dates, while the formula above will faithfully reproduce the original values.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论