如何使用查询或Vlookup联接表 [英] How to join tables using Query or 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时，如何生成报告并仅在它们匹配时才显示记录(不是所有销售单)

How do I make the report to generate when people.email = sales.EMAIL ADDRESS and display the records only if they match (Not all of sales sheet)

报告字段为

full_name
EMAIL ADDRESS
PRODUCT
commission

推荐答案

样本文件

请尝试使用基本公式来连接工作表+查询以跳过一些行.

Sample File

Please try to use basic formula for joining sheets + query to skip some rows.

基本公式:

= 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，)，不显示")，Sales！F14:F}，Sales！A14:A")，选择*，其中Col4<'显示'"")

它使用 iferror(表达式，不显示")用文本不显示"替换#N/A 错误.

It uses iferror(expression, "do not show") to replace #N/A error with the text "do not show".

然后，它使用查询来隐藏值不显示"的行:

Then it uses query to hide rows with value "do not show":

"选择*，其中Col4<>不显示""

基本公式和问题在此处

使用注意事项:

1. Sales！B14:B，Sales！C14:C，Sales！E14:E 是一些需要首先选择的列
2. IFERROR(vlookup(Sales！E14:E，{People！B2:B，People！D2:D}，2，)将从"People"返回列，它是列 People！D2:D .请注意，示例中的 Sales！E14:E 和 People！B2:B 列均包含密钥.这是电子邮件列表.
3. Sales！F14:F 是要返回下一个列的列.
4. 过滤条件 Sales！A14:A 是要跳过原始工作表中的空行.您也可以将其他条件添加到过滤器中.
5. query 部分将缩短报告并跳过键(电子邮件)不匹配的值.您可以在查询中添加其他条件.

1. Sales!B14:B,Sales!C14:C,Sales!E14:E are some columns you need to select first
2. IFERROR(vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,) will return column from "People", it is column People!D2:D. Please note that columns Sales!E14:E and People!B2:B are both containing a key, in the sample it is e-mail list.
3. Sales!F14:F is a column to return next.
4. filter condition Sales!A14:A<>"" is to skip empty rows from original sheet. You may also add other conditions into a filter.
5. query part will shorten the report and skip values where keys (emails) do not match. You may add other conditions into a query.

注意.Google表格中的查询语言没有 joins ，并且提供的解决方案是一种解决方法.它显示了如何在Sheets中的 vlookup 函数的帮助下进行联接.

Note. Query language in Google Sheets does not have joins, and provided solution is a workaround. It shows how to make a join with a help of vlookup function in Sheets.

• 所提供的公式从表"Sales"中收集行.并使 vlookup 覆盖人物".您也可能需要做出相反的报告:从人"网站收集数据和来自销售"的"vlookup"数据.

• The provided formula gathers rows from sheet "Sales" and makes vlookup to sheet "People". You also may want to make vice-versa report: collect data from "People" and 'vlookup` data from "Sales".

反之亦然"；报告:

= 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<>")，选择*，其中Col5<> 100500")

注释！在此公式中，使用数字值100500跳过值，因为查询仅适用于单一数据类型(数字或文本).

note! In this formula used numeric value 100500 to skip values because query works with the single data type (number or text).

如果对它的工作方式有任何疑问，请在这里评论.

Please comment here if you have any questions about how it works.

这篇关于如何使用查询或Vlookup联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！