Google电子表格“= QUERY” join（）等价函数？ [英] Google spreadsheet "=QUERY" join() equivalent function?

问题描述

这个问题涉及到使用= QUERY函数在Google电子表格中连接两个数据库

我有一个类似于A1：C3

  adg 
beh 
cfi 
  

我有另一张桌子

  cjm 
akn 
blo 
  
 
我希望最终表看起来像这样
  adgkn 
behlo 
cfijm 
  
我可以通过使用vlookup在D1单元格中非常容易使用，并将其粘贴并跨越，但是我的数据集非常庞大。我需要一整页的vlookups和谷歌电子表格告诉我在我的极限复杂。
 
 
 
我看谷歌的查询语言参考...那里没有' t似乎是提到的一种连接功能。你可能会认为这将是一个简单的加入A类型的操作。
 
 
 
任何人都可以在没有vlookup的情况下解决这个问题吗？
 
解决方案
 
简短回答
 
 
 
  Google QUERY Language版本0.7（2016）不包含JOIN（左连接）操作符，但可以通过使用数组公式来实现此操作，该结果可用作输入QUERY函数或用于其他用途。
 
 
解释
 
 
 
数组公式和 Google表格的数组处理功能可以在两个简单表格之间进行联接。为了便于阅读，建议的公式使用命名范围而不是范围引用。
 
 
 
命名范围
 
 
 
 
 
 table1：Sheet1！A1：C3 
 
 
 table2：Sheet2！A1：C3 
 
 
 A3 
 
 
 
 
 
公式
 
 
 
 
 = ArrayFormula 
 {
 table1，
 vlookup（ID，table2，COLUMN（Indirect（R1C2：R1C&COLUMNS（table2），0）），0）
} 
 ）
 
备注：
 
 
 
 
使用开放式范围是可能的，但这可能会使电子表格变慢。
 
 
重新计算时间：
 
 
 
 
 code>通过从2到2的列数的常量数组间接（R1C2：R1C& COLUMNS（table2），0） 
 
 
 
  
 $ b 删除空行中的空行 
 
 
示例
 
 
 
请参阅本表为例 
 
 
注意
 
 
 
 2017年Google改进有关QUERY的英文正式帮助文章， QUERY功能。它仍然不包括这样的主题，但可能有助于理解它是如何工作的。
 
This question is concerning joining two databases in Google spreadsheet using =QUERY function

I have a table like so in range A1:C3
a d g
b e h
c f i
I have another table
c j m
a k n
b l o
I want the final table to look like this
a d g k n
b e h l o 
c f i j m
I can do this by using a vlookup function pretty easily in cell D1 and paste it down and across, but my dataset is huge. I would need a whole page of vlookups and Google Spreadsheet tells I'm at my limit in complexities.

I look at the Google's Query Language reference... there doesn't seem to be an type of "join" functions mentioned. You would think it would be an easy "join on A" type operation.

Can anybody solves this without a vlookup?
解决方案
Short answer

Google QUERY Language version 0.7 (2016) doesn't include a JOIN (LEFT JOIN) operator but this could be achived by using an array formula which result could be used as input for the QUERY function or for other uses.

Explanation

Array formulas and the array handling features of Google Sheets make possible to make a JOIN between two simple tables. In order to make easier to read, the proposed formula use named ranges instead of range references.

Named Ranges


table1 : Sheet1!A1:C3
table2 : Sheet2!A1:C3
ID : Sheet1!A1:A3


Formula

=ArrayFormula(
   {
     table1,
     vlookup(ID,table2,COLUMN(Indirect("R1C2:R1C"&COLUMNS(table2),0)),0)
   }
)
Remarks: 


Using open ended ranges is possible but this could make the the spreadsheet slower.
To spead up the recalculation time :


Replace Indirect("R1C2:R1C"&COLUMNS(table2),0) by an array of constants from 2 to number of columns of table2. 
Remove the empty rows from the spreasheet



Example

See this sheet for an example 

Note

On 2017 Google improved the official help article in English about QUERY, QUERY function. It still doesn't include yet topics like this but could be helpful to understand how it works.

                        
这篇关于Google电子表格“= QUERY” join（）等价函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！