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屋!