Google表格-如何用一张桌子进行外部联接? [英] Google Sheet - How to FULL OUTER JOIN with one table?

问题描述

我想为我的锦标赛创建一张桌子.我创建了一个包含一些球员及其联赛的表格.

我想按联盟生成配对表

我对Google电子表格不满意.我知道我不能使用"JOIN"与"QUERY()"一起使用；功能.看来我需要使用"ArrayFormula()"和"VLOOKUP()"；功能，但经过数小时的尝试后，我失败了.

如果你们中的任何一个可以帮助我，那就太好了！

这是一个包含数据的googlesheet:

I would like to create a tables for my tournament. I create a table with some players and their leagues.

I would like to generate matchmaking table, by league, like that

I'm to weak with google spreadsheet to do that. I understood I can't use "JOIN" with "QUERY()" function. It seems I need to use "ArrayFormula()" and "VLOOKUP()" functions but after hours pasted to try, I failed.

If anyone of you can help me, it will be so great !

Here is a googlesheet with datas : https://docs.google.com/spreadsheets/d/19ThnwVme8f3Ee730w8lTAyEJE9YdxzLEfox8arl5Q4o/edit?usp=sharing

Thanks a lot, I hope my problem is clear :)

Edit : Seems we are limited by number of characters in REPT() function. If there is an other solution than the answer or a "workaround", feel free to share it :)

解决方案

try:

=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(IF(
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"); COUNTA(A3:A)); "♦"))<
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"; COUNTA(A3:A))); "♦")); 
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"); COUNTA(A3:A)); "♦"))&"♠"&
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"; COUNTA(A3:A))); "♦"));
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"; COUNTA(A3:A))); "♦"))&"♠"&
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"♠"&B3:B&"♠"&C3:C)&"♦"); COUNTA(A3:A)); "♦"))); "♠"); 
 "select Col1,Col2,Col4,Col5,Col6
  where Col1 <> Col4 
    and Col3 =  Col6")))

这篇关于Google表格-如何用一张桌子进行外部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！