Bizsoft360

软件，管理，实践

合并2个谷歌表并根据共同记录更新数据

今天的博文介绍了合并2个Google表单的所有方法。你将使用VLOOKUP、INDEX/MATCH、QUERY和合并表插件，根据共同列的匹配情况，从另一个表中的记录更新一个表中的单元格。

使用VLOOKUP功能合并谷歌表

当你需要匹配和合并两个谷歌表时，你可能首先求助于VLOOKUP功能。

语法& 使用方法

这个函数在你指定的列中搜索某个键值，并从同一行中拉出一条相关记录到另一个表或工作表中。

虽然谷歌表的VLOOKUP通常被认为是困难的函数之一，但它实际上是相当直接的，甚至在你了解它之后也很简单。

让我们快速了解一下它的组成部分。

=VLOOKUP(search_key, range, index, [is_sorted])

  • search_key 是你要找的键值。它可以是任何文本字符串、数字或一个单元格引用。
  • 范围 是一组单元格（或一个表格），你将在其中寻找 search_key 以及你将从哪里提取相关记录。注意。 谷歌表格中的VLOOKUP总是在扫描第一列的 范围内 为 search_key.
  • 索引 是该列中的编号 范围内 你想从哪里提取数据。例如，如果你要搜索的范围是A2:E20，你需要从E列获取数据，就输入5。但如果你的范围是D2:E20，你就需要输入2来获取E列的记录。
  • [is_sorted] 是唯一一个你可以省略的参数。它用于说明带有键值的列是否被排序（TRUE）或不排序（FALSE）。如果是TRUE，该函数将使用最接近的匹配，如果是FALSE，则使用完整的匹配。当省略时，默认使用TRUE。

考虑到这些参数，让我们使用VLOOKUP来合并两个Google表单。

假设我在Sheet2中有一个包含浆果及其ID的小表。不过，库存量是未知的。
我的主表要填补C列中数据缺失的地方。
让我们把这个表称为主表，因为我的目标是把它填进去。

在Sheet1中还有一个表，所有数据都已到位，包括库存量。
我的带有数据的查找表。
我把它叫做查找表，因为我将查找它以获得数据。

我将使用谷歌表的VLOOKUP功能来合并这2张表。该函数将匹配两个表中的浆果，并将相应的 “股票 “信息从查找表中拉到主表中。

=VLOOKUP(B2,Sheet1!$B$2:$C$10,2,FALSE)
在谷歌表格中使用VLOOKUP匹配和合并数据。
下面是这个公式如何准确地合并两个谷歌表。

  1. 它在Sheet1（查找表）的B列中查找B2（主表）的值。注意。 记住，VLOOKUP扫描的是指定范围的第1列 – Sheet1！$B$2:$C$10.
    注意。 我使用 绝对引用 因为我把公式复制到下一列，因此我需要这个范围在每一行都保持不变，这样结果就不会中断。
  2. 最后的FALSE表示B列中的数据（在查找表中）没有被排序，所以只有完全匹配的数据才会被考虑。
  3. 一旦有了匹配，Google Sheets VLOOKUP就会从该范围的第2列（C列）拉出相关记录。

隐藏谷歌表格中VLOOKUP返回的错误 – IFERROR

但那些#N/A的错误怎么办？

你会在那些行中看到它们，在这些行中，浆果在另一张表中没有匹配，也没有什么可以返回。幸运的是，有一种方法可以使这种单元格保持空的状态。

只要把你的谷歌表VLOOKUP包在IFERROR里就可以了。

=IFERROR(VLOOKUP(B2,Sheet1!$B$2:$C$10,2,FALSE),"")
使用IFERROR函数隐藏错误。

提示。 捕获并修复您的谷歌表VLOOKUP可能返回的其他错误，使用以下解决方案 本指南.

匹配& 一次性更新整列的记录 – ArrayFormula

还有一件事我想提一下，就是如何一次性匹配和合并整个列的谷歌表数据。

这里没有什么花哨的，只是多了一个函数–ArrayFormula。

只需将你在谷歌表VLOOKUP中的单格关键记录替换成整列，并将这整个公式放在ArrayFormula里面。

=ArrayFormula(IFERROR(VLOOKUP(B2:B10,Sheet1!$B$2:$C$10,2,FALSE),""))
在ArrayFormula中的VLOOKUP，以获得该列中所有单元格的结果。
这样一来，你就不需要将公式复制到列中。ArrayFormula将立即向每个单元格返回正确的结果。

虽然谷歌表格中的VLOOKUP非常适合这种简单的任务，但它有一些局限性。这里有一个缺点：它不能看它的左边。无论你指出什么范围，它总是扫描其第一列。

因此，如果你需要合并2个谷歌表，并根据浆果（第二列）提取ID（第一列数据），VLOOKUP不会有帮助。你只是无法建立一个正确的公式。

在这样的情况下，谷歌表的INDEX MATCH进入了游戏。

匹配& 使用INDEX MATCH duo合并谷歌表

INDEX MATCH，或者说INDEX & MATCH，实际上是两个不同的谷歌表函数。但当它们一起使用时，就像一个次一级的VLOOKUP。

是的，它们也可以合并谷歌表：根据共同的关键记录，用另一个表中的记录来更新一个表中的单元格。

但它们做得更好，因为它们忽略了VLOOKUP的所有那些限制。

我今天不会介绍所有的基础知识，因为我已经在 这篇博文.但我将给你几个INDEX MATCH公式的例子，这样你就可以看到它们如何直接在Google电子表格中工作。我将使用上面的相同样本表。

谷歌表格中的INDEX MATCH操作

首先，让我们合并这些谷歌表，并更新所有匹配浆果的库存可用性。

=INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0))
在谷歌表中使用INDEX MATCH更新数据。
INDEX & MATCH这样一起使用时如何工作？

  1. MATCH 查看 B2，并在 Sheet1 的 B 列中搜索完全相同的记录。一旦找到，它将返回包含该值的行的编号–在我的例子中是10。
  2. INDEX也会进入Sheet1上的第10行，只是它从另一列–C中取值。

现在，让我们尝试测试一下 INDEX MATCH 与 Google Sheets VLOOKUP 无法做到的事情之间的关系–合并表并使用所需的 ID 更新最左边的列。

=INDEX(Sheet1!$A$1:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0))
INDEX MATCH更新最左边一列。
简单-易行 🙂

在谷歌表格中处理由INDEX MATCH返回的错误

让我们更进一步，摆脱那些没有匹配的单元格中的错误。IFERROR将再次提供帮助。只要把你的谷歌表INDEX MATCH作为它的第一个参数。

例1.

=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
纳入IFERROR，让空单元格代替错误。

例2.

=IFERROR(INDEX(Sheet1!$A$1:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0)),"")
最左边一列的IFERROR。
现在，你如何使用INDEX MATCH合并这些Google表，并一次性更新整个列中的所有单元格？

嗯……你不能这样做。有一个小问题：ArrayFormula在这两个表中不起作用。

你需要将INDEX MATCH公式复制到列下，或者使用谷歌表的QUERY函数作为替代。

合并谷歌表& 使用QUERY更新单元格

谷歌表的QUERY是电子表格中最强大的功能。考虑到这一点，它提供一种合并表格的方法也就不足为奇了–匹配& 合并不同表格的值。

=QUERY(data, query, [headers])

小费。 如果你以前从未使用过谷歌表QUERY。 本教程 将让你了解其奇特的语言。

更新 “我 “的QUERY公式应该是什么样子的？ 股票 栏目与实际数据？

=QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Sheet4!$B2:$B$10&"'")
QUERY用另一个工作表的信息更新一列。

  • 谷歌表的QUERY查看我的查找表（Sheet1，有我需要拉到主表的记录）。
  • 并返回所有来自C列的单元格，其中B列与我的主表中的浆果相符

让我失去那些没有匹配的单元格的错误。

=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Sheet4!$B2:$B$10&"'"),"")
用IFERROR包住QUERY来处理错误。
好了，这样就好了 🙂

合并来自不同谷歌电子表格的表格 – IMPORTRANGE功能

还有一个我想提及的功能。它很重要，因为它可以让你合并驻留在不同谷歌电子表格（文件）中的表。

这个函数叫做IMPORTRANGE。

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

  • 前者是那个电子表格的链接，你从那里提取数据
  • 后者是工作表&；你想从该电子表格中获取的范围。

想象一下，你的查找工作表（有参考数据）在电子表格2（又称查找工作表）。你的主工作表在电子表格1（主电子表格）中。

注意。 为了使IMPORTRANGE发挥作用，你必须连接两个文件。虽然Google Sheet建议在你在单元格中输入公式后有一个按钮，并点击 输入, 对于下面的公式，你可能需要事先做。 这个分步骤的指南 将帮助你。

下面是使用IMPORTRANGE从不同的文件中合并Google表单的例子，其中有你今天早些时候学过的每个函数。

例子1.importrange + vlookup

使用IMPORTRAGE作为VLOOKUP的范围，合并2个独立的Google电子表格。

=ArrayFormula(IFERROR(VLOOKUP(B2:B10,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$B$2:$C$10"),2,FALSE),""))
在VLOOKUP中使用IMPORTRANGE从另一个Google文件中提取数据。

例2.importrange + 索引匹配

至于INDEX MATCH & IMPORTRANGE，公式变得更加庞大，因为你需要两次引用另一个电子表格：作为INDEX的范围和作为MATCH的范围。

=IFERROR(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$A$1:$A$10"),MATCH(B2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$B$2:$B$10"),0)),"")
IMPORTRANGE + INDEX MATCH在一个公式中。

例3.变化范围+查询

这个串联的公式是我个人的最爱。它们一起使用时，几乎可以处理电子表格中的任何问题。从独立的电子表格中合并谷歌表也不例外。

=IFERROR(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$A$2:$C$10"),"select Col3 where Col2='"&QUERY!$B2:$B$10&"'"),"")
使用IMPORTRANGE和QUERY从另一个电子表格导入数据。
呜呼!

这就是函数&公式的全部内容。

你可以自由选择任何函数& 通过上面的例子建立自己的公式…

或…

…试试一个特殊的工具，它可以为你合并谷歌表！;)

无公式匹配& 合并数据的方法 – 合并表的Google表插件

如果你没有时间建立甚至学习公式，或者你只是在寻找基于共同记录的最简单的方法来连接数据。 合并表 将是完美的。

你所需要做的就是在5个用户友好的步骤中勾选复选框。

  1. 选择你的主表
  2. 选择你的查询表
  3. 用复选框标记关键列（那些包含要匹配的记录）。
  4. 选择要更新的列。
    选择要更新的列。
  5. 调整其他选项，例如，用颜色或在状态栏中标记更新的记录，等等。

甚至还有可能将所有选择的选项保存到 一个场景 并在需要时重复使用。
将选项保存到场景中，以便以后使用。
观看这个3分钟的演示视频，看看它是如何工作的。

带公式的电子表格示例

合并谷歌表&更新数据–公式示例 (复制一份文件)

Source link