Как присоединиться к таблицам с помощью Query или Vlookup

У меня 2 листа

лист людей имеет

[company    first_name  last_name   full_name   address_line1   address_city    address_state   address_zip address_country telephone   email   order_id    order_date  order_time  processor   order_type  order_currency  commission  campaign_id]

торговый лист имеет

[DATE   PROSPECT NAME   EMAIL ADDRESS   CONTACT METHOD  PRODUCT RESULT  NOTES ]

Как сделать так, чтобы отчет генерировался, когда people.email = sales.EMAIL ADDRESS, и отображать записи только в том случае, если они совпадают (не весь лист продаж)

Поля отчета

full_name
EMAIL ADDRESS
PRODUCT
commission

google-sheets google-query-language
person Code Guy    schedule 09.01.2018    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Образец файла

Пожалуйста, попробуйте использовать базовую формулу для соединения листов + запрос, чтобы пропустить некоторые строки.

Базовая формула:

=FILTER({Sales!B14:B,Sales!C14:C,Sales!E14:E, vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,), Sales!F14:F},Sales!A14:A<>"")

Формула, которая вам нужна:

=QUERY(FILTER({Sales!B14:B,Sales!C14:C,Sales!E14:E, IFERROR(vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,), "do not show"), Sales!F14:F},Sales!A14:A<>""),"select * where Col4 <> 'do not show'")

Как это работает

Он использует iferror(expression, "do not show") для замены #N/A ошибки текстом, который не отображается.

Затем он использует запрос, чтобы скрыть строки со значением, которое не отображается:

"select * where Col4 <> 'do not show'"

Основная формула и вопрос здесь

Примечания по использованию:

  1. Sales!B14:B,Sales!C14:C,Sales!E14:E - некоторые столбцы, которые нужно выбрать в первую очередь
  2. IFERROR(vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,) вернет столбец от людей, это столбец People!D2:D. Обратите внимание, что столбцы Sales!E14:E и People!B2:B содержат ключ, в примере это список адресов электронной почты.
  3. Sales!F14:F - следующий столбец.
  4. условие фильтрации Sales!A14:A<>"" - пропускать пустые строки исходного листа. Вы также можете добавить в фильтр другие условия.
  5. query часть сократит отчет и пропустит значения, в которых ключи (электронные письма) не совпадают. Вы можете добавить в запрос другие условия.

Примечание. Язык запросов в Google Таблицах не имеет joins, и предоставленное решение является обходным путем. Он показывает, как выполнить соединение с помощью функции vlookup в Таблицах.

  • Предоставленная формула собирает строки из таблицы "Продажи" и превращает vlookup в лист "Люди". Вы также можете создать обратный отчет: собирать данные от людей и данные vlookup от продаж.

Обратный отчет:

=QUERY(FILTER({vlookup(People!B2:B ,{Sales!E14:E, Sales!B14:B, Sales!C14:C, Sales!E14:E},{2, 3, 4},), People!B2:B, IFERROR(vlookup(People!B2:B ,{Sales!E14:E, Sales!F14:F},2,),100500)},People!A2:A<>""),"select * where Col5 <> 100500")

Примечание! В этой формуле используется числовое значение 100500, чтобы пропустить значения, поскольку запрос работает с одним типом данных (числом или текстом).

Прокомментируйте, пожалуйста, здесь, если у вас есть какие-либо вопросы о том, как это работает.

person Max Makhrov    schedule 09.01.2018
comment
Я добавил несколько примечаний к ответу. В языке запросов в Google Таблицах нет соединений, и предоставленное решение является обходным путем. Он показывает, как выполнить соединение с помощью функции vlookup в Таблицах. Надеюсь это поможет. - person Max Makhrov; 09.01.2018
comment
Добавлена ​​формула для создания отчета из листа Люди в качестве основного листа (где хранятся уникальные идентификаторы. - person Max Makhrov; 09.01.2018