Электронная таблица Google "= QUERY" join() эквивалентна?

Этот вопрос касается присоединения двух баз данных в электронной таблице Google, используя функцию = QUERY

У меня есть таблица вроде так в диапазоне A1: C3

a d g
b e h
c f i

У меня есть другая таблица

c j m
a k n
b l o

Я хочу, чтобы финальная таблица выглядела так:

a d g k n
b e h l o 
c f i j m

Я могу сделать это с помощью функции vlookup довольно легко в ячейке D1 и вставить ее вниз и поперек, но мой набор данных огромен. Мне понадобится целая страница vlookups, а Google Spreadsheet сообщит, что я нахожусь в моем лимите сложности.

Я смотрю ссылку на ссылку Google Query Language..., похоже, не упоминается тип упомянутых функций объединения. Вы бы подумали, что это будет простая операция типа "join on A".

Может ли кто-нибудь решить это без vlookup?

Ответ 1

Короткий ответ

Язык Google QUERY версии 0.7 (2016) не включает в себя оператор JOIN (LEFT JOIN), но этого можно достичь с помощью формулы массива, результат которой можно использовать в качестве входных данных для функции QUERY или для других целей.

объяснение

Формулы массивов и функции обработки массивов в Google Sheets позволяют объединить две простые таблицы. Чтобы было проще читать, в предложенной формуле вместо имен диапазонов используются именованные диапазоны.

Именованные Диапазоны

table1: Sheet1! A1: C3
таблица2: лист2! A1: C3
ID: Sheet1! A1: A3

формула

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

Примечания:

Использование открытых диапазонов возможно, но это может замедлить работу электронной таблицы.
Чтобы ускорить время пересчета:
1. Замените Indirect("R1C2:R1C"&COLUMNS(table2),0) на массив констант от 2 до числа столбцов таблицы2.
2. Удалить пустые строки из таблицы

пример

Смотрите этот лист для примера

Заметка

В 2017 году Google улучшил официальную справочную статью на английском языке о функции QUERY, QUERY. Он все еще не включает в себя такие темы, но может быть полезным, чтобы понять, как это работает.

Ответ 2

Вы можете использовать ARRAYFORMULA, или вы можете просто перетащить эту формулу: после импорта или QUERY - для первой таблицы; в столбце D:

=QUERY(Sheet2!A1:C3, "Select B,C WHERE A='" & A1 & "'", 0)

Ответ 3

Итак, это отвечает, как вы делаете это с помощью функции Vlookup, но только в одной ячейке.
В вашем примере, учитывая, что каждая таблица данных имеет следующие ссылки на ячейки:

Таблица1: Лист1! A1: C3

a d g
b e h
c f i

Таблица 2: Лист 2! А1: С3

c j m
a k n
b l o

Вот как формула должна быть построена.

Join-формула

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!A1:A, Sheet2!B1:C}, {2,3}, false)
   }
)

Ключ к получению этой формулы - понять, как использовать фигурные скобки в диапазоне Vlookup. Вы в основном определяете первую ссылку на ячейку Range как столбец, который должен соответствовать Vlookup Search_Key. Остальные ссылки на ячейки в диапазоне относятся к столбцам, к которым вы хотите присоединиться.

Индекс записывается как {2,3} для возврата второго и третьего столбца диапазона (диапазон состоит из 3 столбцов); фигурные скобки не имеют ничего общего с Arrayformula в индексе Vlookup, но необходимы для возврата нескольких столбцов из функции Vlookup. Причина не писать {1,2,3} в том, что вы не хотите включать столбец, который используется для присоединения.

Пример, в котором столбец в таблице2, используемый для объединения, расположен в другом столбце (справа от данных, которые нужно объединить)

Этот тип формулы соединения может использоваться, даже если столбец соединения во второй таблице расположен как третий столбец этой таблицы. Предположим, что необработанные данные в этом примере будут выглядеть так:

Таблица1 (Лист1):

a d g
b e h
c f i

Таблица 2 (Лист 2):

j m c
k n a
l o b

Если вы напишите формулу, подобную этой, вы все равно получите желаемый результат (как показано в таблице объединенных данных):

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!C1:C, Sheet2!A1:B}, {2,3}, false)
   }
)

Таблица объединенных данных:

a d g k n
b e h l o 
c f i j m

В формуле соединения обратите внимание, что третий столбец таблицы 2 расположен в качестве первой ссылки на ячейку в диапазоне Vlookup!
Причина, по которой это работает, заключается в том, что когда вы используете фигурные скобки в Range (вместе с Arrayformula), Vlookup Search_Key НЕ будет искать столбец как общий знаменатель в необработанных данных, вместо этого он будет использовать Array внутри фигурные скобки в качестве ссылки для поиска столбца в качестве общего знаменателя (по умолчанию это первый столбец диапазона).

Я написал подробное руководство по этой теме под названием:

"Освоение формул объединения в Google Sheets"

Ответ 4

Если вы можете сопоставить каждый "индекс" (a, b, c) с определенной строкой или столбцом, вы можете использовать INDEX.

В этом случае вы могли бы, вероятно, сопоставить 'a' с столбцом A (или с строкой 1), 'b' с столбцом B (или с строкой 2) и т.д.

Кроме того, Merge Tables, похоже, относится к этому конкретному варианту использования.

Ответ 5

С таблицей "Другие" в A5: C7, пожалуйста, попробуйте:

=query({A1:C3,query(sort(A5:C7,1,TRUE),"Select Col2,Col3")})