Google 电子表格“=QUERY"join() 等效函数? [英] Google spreadsheet "=QUERY" join() equivalent function?

问题描述

这个问题是关于使用 =QUERY 函数在 Google 电子表格中连接两个数据库

我在 A1:C3 范围内有一张这样的表格

a d g乙金融机构

我还有一张桌子

c j ma k n乙

我希望决赛桌看起来像这样

a d g k n眼不见心不烦c f i j m

我可以通过在单元格 D1 中使用 vlookup 函数很容易地做到这一点，并将其粘贴下来，但我的数据集很大.我需要一整页的 vlookups，而 Google 电子表格会告诉我在复杂性方面我已达到极限.

我查看了 Google 的查询语言参考...似乎没有提到一种连接"函数.你会认为这是一个简单的连接 A"类型的操作.

任何人都可以在没有 vlookup 的情况下解决这个问题吗?

解决方案

简短回答

Google QUERY Language 0.7 版 (2016) 不包含 JOIN(LEFT JOIN) 运算符，但这可以通过使用数组公式来实现，该公式的结果可用作 QUERY 函数的输入或用于其他用途.

说明

数组公式和 Google 表格的数组处理功能使在两个简单的表之间进行 JOIN.为了更容易阅读，建议的公式使用命名范围而不是范围引用.

命名范围

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

公式

<前>=数组公式({表格1，vlookup(ID,table2,COLUMN(间接("R1C2:R1C"&COLUMNS(table2),0)),0)})

备注:

• 可以使用开放式范围，但这会使电子表格变慢.
• 加快重新计算时间:

1. 将 Indirect("R1C2:R1C"&COLUMNS(table2),0) 替换为从 2 到 table2 的列数的常量数组.
2. 从电子表格中删除空行

示例

请参阅此表的示例>

注意

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 achieved 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 spreadsheet slower.
• To speed up the recalculation time :

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

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