查询不同表格, 如何从 Google 表格中的另一张表格进行 VLOOKUP, Google Sheets VLOOKUP HLOOKUP MATCH 范例解说

 

Google 试算表(Spreadsheets)是个好工具，内建不少跟Excel 相似的函数能够使用，所以会一点EXCEL 的人都能够轻松上手。如果用来取代Excel 应该是问题不大，因此我也常常会推荐使用Google Spreadsheets 统计数据、制作表单等等。

本篇介绍3 个实用的Google Spreadsheets 函数：

1. VLOOKUP
2. HLOOKUP
3. MATCH

 

VLOOKUP

VLOOKUP 的功用是在 特定范围 的 第1 栏垂直寻找 特定值并且回传 指定栏位的值。

这么说可能有点抽象，直接来看以下范例：

  A 欄    B 欄
1 學生    成績
2 小明    100
3 小王    95
3 水仙    88
4 小羊    60
5 火星    35
6 金星    33
7 老牛    10

 

假设我们有学生、成绩2 栏资料，如果想要轻松取的小羊的成绩，可以用VLOOKUP，只要在储存格输入已下公式，就能够得到小羊的成绩。

=VOLOOKUP("小羊", A1:B7, 2, FALSE)

上述公式的A1:B7就是一开始提到的 特定范围，所以Google Spreadsheets 会从特定范围的 第1 栏垂直寻找 小羊，然后把特定范围的第2 栏，也就是B 栏回传。

最后一个参数 FALSE 则是代表是否已将特定范围中的第1 栏进行排序，此处设定为 FALSE 因为我们希望精准搜寻，不希望Google Spreadsheets 回传相似的结果，而Google Spreadsheets u文件也提到建议设为FALSE：

建议将已排序设为FALSE。设为FALSE 后，系统会传回完全相符的值。如果有多个相符值，系统会传回与找到的第一个值对应的储存格内容；如果找不到相符值，则会传回#N/A。
如果已排序为TRUE 或省略，系统会传回最接近的相符值(小于或等于搜寻词)。如果进行搜寻的栏中所有的值均大于搜寻词，则会传回#N/A。

如果已排序设为TRUE 或省略，且范围中的第一栏并未排序，系统可能会传回不正确的值。如果VLOOKUP 似乎无法提供正确结果，请确认最后一个引数已设为FALSE。在大多数情况下，该引数应设为FALSE。不过，如果系统已将资料排序，您可以将该引数设为TRUE，让函式发挥最佳效能。

此外，HLOOKUP也有相同的参数，在此建议同样设定为 FALSE 即可。

 

HLOOKUP

VLOOKUP是垂直的寻找，那 HLOOKUP 的功用就是在 特定范围 的 第1 列水平寻找 特定值并且回传 指定列的值。

假设除了学生成绩资料外，还多一份学号与学生的对应资料：

  A 欄                                                  L 欄
1 學號  A001    A007    A003    A002    A005    A004    A006
2 學生  小明    小王    老牛    小羊    金星    火星    水仙

 

我们希望只要搜寻学号，就能够知道学生姓名的话，就可以用HLOOKUP，例如想知道A003 的姓名，只要输入以下公式，就能够得到学生姓名：

=HLOOKUP("A003", B1:L2, 2, FALSE)

上述公式的B1:L2就是一开始提到的 特定范围，所以Google Spreadsheets 会从特定范围的 第1 列水平寻找A003 ，然后把相对应特定范围的第2 列，也就是D2 储存格回传。

最后一个参数 FALSE 则是代表是否已将特定范围中的第1 列进行排序，此处设定为 FALSE 因为我们希望精准搜寻，不希望Google Spreadsheets 回传相似的结果

 

MATCH

而 MATCH 就更简单了，MATCH是传回指定值在特定范围中的相对位置。

以第1 个范例为例：

  A 欄    B 欄
1 學生    成績
2 小明    100
3 小王    95
3 水仙    88
4 小羊    60
5 火星    35
6 金星    33
7 老牛    10

 

假设想知道水仙在A 栏的位置，只要输入：

=MATCH("水仙", A1:A7, 0)

上述结果会得到4 ，代表水仙在第4 个。

 

此外，上述公式的最后1 个参数0代表的是使用精准搜寻，与VLOOKUP / HLOOKUP 类似，MATCH会假设资料已经排序过，所以我们用 0 代表未排序过，强迫Google Spreadsheets 使用精准搜寻：

0 indicates exact match, and is required in situations where 范围is not sorted.

 

Google 表格 VLOOKUP：什么是 Google 表格中的 VLOOKUP 功能？

VLOOKUP 代表垂直查找。它是一个 Google 表格功能，可让您执行垂直查找。换句话说，它允许您在一系列单元格的第一列中搜索特定的查找值。

一旦找到匹配值，它就会在与查找值相同的行中的另一个指定列中查找一个值 并检索它。

如果这个定义听起来令人困惑，请坚持下去。当我们用一个例子来解释它时，它会变得更清楚。

许多 Google 表格新手都认为 VLOOKUP 难以理解和应用。

然而，事实并非如此。

在本教程中，您将看到应用是多么容易。一旦你掌握了它的窍门，你也会意识到它是多么强大的工具！

 

何时在 Google 表格中使用 VLOOKUP

假设您有两张桌子。

一个包含有关员工的信息，另一个包含特定月份的总销售额。

两个表都有一个共同的列，即Employee ID。此外，员工 ID 可以被视为两个表中的键值或唯一标识符。

假设您需要知道销售表中列出的两名员工的每小时费率。这些费率已经存在于Employee Table中。

因此，您需要将与两个员工 ID 对应的小时费率从Employee Table拉到Sales Table。

现在，您只需在Employee Table中查找两个员工 ID，并将其相应的Hourly Rates复制到Sales Table中。但是，如果您正在处理非常大且复杂的数据集（实际上通常是这种情况），那将很难做到。

因此，解决此问题的最佳方法是使用 Google 表格的 VLOOKUP 功能。

在本教程中，我们将向您展示如何使用 VLOOKUP 来解决上述问题，以便您可以轻松地将功能应用于您自己的数据。

 

Google 表格 VLOOKUP 语法

Google 表格中 VLOOKUP 的语法如下：

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

这里，

• search_key表示您要查找的唯一标识符或键值。这可以是一个值，也可以是对包含该值的单元格的引用。
• range是 VLOOKUP 函数应搜索的​​单元格范围（在源表中）。始终确保此范围包含包含搜索键的列以及包含要检索的相应值的列。VLOOKUP 总是搜索范围的第一列以找到搜索键。
• index是应该从中检索相应值（与 search_key 在同一行中的值）的范围内的列号因此，范围内的第一列的索引为 1，第二列的索引为 2，依此类推。
• is_sorted是一个可选参数。这可以是 TRUE 或 FALSE。

 

is_sorted的 FALSE 值表示范围的第一列不需要按升序排序。因此， VLOOKUP 函数搜索search_key的完全匹配。

如果有多个值完全等于search_key，则 VLOOKUP 访问 search_key 的第一个匹配项。

另一方面，TRUE 值意味着第一列必须按升序排序。

因此，VLOOKUP 函数首先查找search_key的完全匹配。如果未找到完全匹配，则 VLOOKUP 会查找最接近的匹配。

默认情况下，参数 is_sorted 设置为 FALSE。

这听起来很复杂吗？

它不是真的。让我们将上述语法应用于我们的示例。

 

如何从 Google 表格中的同一张表格进行 VLOOKUP

让我们再次回顾一下这个问题：

我们要在员工表中查找员工 ID 的“E010”和“E014”（单元格 E3 和 E4），找到它们对应的小时费率（从 C 列），并将它们放入 F 列。

让我们看看我们需要在单元格 F3 的 VLOOKUP 公式中指定哪些参数：

• search_key：我们要查找值“E010”（位于单元格 E3 中）。因此，我们需要将 E3 指定为search_key参数。
• range：我们要查看Employee Table中 A3 到 C8 范围内的单元格。所以我们需要指定 A3:C8 作为范围参数。请注意，即使将 VLOOKUP 公式从单元格 F3 复制到 F4，我们也希望此范围保持不变。所以我们需要通过按键盘上的 F4 键来锁定这个范围。
• index：我们希望 VLOOKUP 检索与key_value中的员工 ID 对应的Hourly Rates。Hourly Rate是范围中的第三列。所以我们需要指定数字3作为索引参数。
• is_sorted：我们显然想要与员工 ID 完全匹配，因此我们可以将此参数设置为 FALSE 或直接将其省略（因为它是可选的，默认为 False）。

 

将所有这些放在一起，我们在单元格 F3 中设置以下公式：

=VLOOKUP(A3,$A$3:$C$8,3,false)

这样你就会得到这样的结果：

大多数情况下。人们使用 VLOOKUP 从同一张表中获取数据。但是您可以很容易地将它从另一张表中转到 VLOOKUP（通过将其与 IMPORTRANGE 功能相结合）

 

如何从同一工作簿中的另一个工作表 VLOOKUP

到目前为止，我们看到了当您希望 VLOOKUP 从Google 表格中的同一张表格中获取数据时该怎么做。但是，如果您要查找的数据位于同一工作簿的不同工作表中怎么办？

在这种情况下，我们使用相同的函数，但第二个参数略有不同。

以下是如何从 Google 表格中的另一个选项卡进行 VLOOKUP。

让我们假设员工表位于名为“员工”的工作表中

…并且销售表位于名为‘Sales’的单独工作表中。

我们要访问员工表，检索与员工 ID 的“E010”和“E014”对应的小时费率，并将它们显示在销售表的单元格 B3 和 B4 中。

 

以下是您需要遵循的步骤：

1. 单击目标列的第一个单元格（您希望 VLOOKUP 结果出现的位置）。对于我们的示例，单击销售表的单元格 B3。
2. 键入：=VLOOKUP，后跟左括号。您的公式栏现在应该显示：

   

3. 接下来，选择包含要查找的值的单元格。对于我们的示例，选择单元格 A3，后跟逗号。您的公式栏现在应该显示：

   

4. 对于第二个参数，选择员工选项卡（打开员工表）。选择您希望 VLOOKUP 搜索的单元格范围。在我们的示例中，选择 A3 到 C8 范围内的单元格。您的公式栏现在应该显示：

   

5. 按键盘上的 F4 键锁定单元格引用范围。这样做是为了在将公式复制到列中的其他行时不会更改这些引用。您的公式栏现在应该显示：

   

6. 放置一个逗号，后跟包含您要检索的值的列的索引。在我们的示例中，我们要检索Hourly Rates，它是 A3:C8 范围内的第三列。所以我们输入数字 3。

   

7. 最后，关闭括号。
8. 按下回车键，等待 VLOOKUP 完成处理。
9. 您现在应该能够在销售表的单元格 B3 中看到与员工 ID“E010”相对应的小时费率。

   

10. 向下拖动填充柄（位于单元格 B3 的右下角），将公式复制到列中的其他单元格。
11. 您现在应该看到与销售表A 列中提到其 ID 的每个员工对应的小时费率！

    

 

公式说明

VLOOKUP 公式的语法（引用不同工作表中的数据时）如下：

=VLOOKUP(search_key,{sheet name}!{cell range},index,is_sorted)

注意感叹号“！” 在工作表名称和单元格范围之间。

 

在我们的示例中，我们想从名为’Employees’的工作表中查找范围 A3:C8 中的数据。于是公式变成了：

=VLOOKUP(A2,'Employees'!$A$3:$C$8, 3)

另请注意，工作表名称周围有单引号。如果您使用 Google 表格提供的默认工作表名称，例如Sheet1、Sheet2等，则无需添加单引号。

在我们的例子中，我们为员工表提供了一个用户定义的名称。这就是为什么我们需要用单引号将其名称括起来。

此公式从同一工作簿中名为“员工”的工作表的第三列中获取值。

 

如何从不同的 Google 表格工作簿中的另一个表格进行 VLOOKUP？

现在让我们看看当我们希望 VLOOKUP 从不同工作簿中的工作表中获取数据时会发生什么。

同样，我们使用相同的函数，但第二个参数略有不同。

这一次，第二个参数将包括 IMPORTRANGE 函数。

 

Google 表格中的 IMPORTRANGE 功能

IMPORTRANGE 函数用于将另一个电子表格中的单元格中的值导入到当前电子表格中。

公式的语法如下：

IMPORTRANGE(spreadsheet_key, range_string)

它需要两个参数：

• spreadsheet_key：这是您要从中导入的电子表格的 URL。它应该用双引号指定。
• range_string：这是对要导入的单元格范围的引用。range_string参数应包含工作表名称以及所需的单元格范围。因此，如果您想从名为‘Employees’的工作表中导入单元格 A3:C8 ，则range_string将为“ Employees!A3:C8 ”。请记住在双引号内指定整个range_string 。

现在回到我们的例子。

 

让我们假设员工表位于名为‘Wb1’的工作簿中，位于名为‘Employees’的工作表中，而销售表位于名为‘Sales’的工作表中名为‘Wb2’ 的单独工作簿中。

我们想要访问员工表（来自工作簿Wb1），检索与员工 ID 的“E010”和“E014”相对应的小时费率，并将它们显示在销售表（即工作簿Wb2）的单元格 B3 和 B4 中。

 

以下是您需要遵循的步骤：

1. 单击目标列的第一个单元格（您希望 VLOOKUP 结果出现的位置）。对于我们的示例，单击工作簿“Wb1 ”的销售表的单元格 B3。
2. 键入：=VLOOKUP，后跟左括号。

   

3. 接下来，选择包含要查找的值的单元格。对于我们的示例，选择单元格 A3，后跟逗号。

   

4. 对于第二个参数，输入函数 IMPORTRANGE，后跟一个左括号。
5. 打开要查找的工作簿 ( ‘Wb1’ ) 并选择工作表选项卡（在我们的示例中为‘Employees’）。

   

6. 从浏览器的地址栏中复制此工作表的 URL。

   

7. 返回到您当前的工作簿 ( ‘Wb2’ ) 并将 URL 粘贴到公式栏中的公式末尾（请记住将 URL 括在双引号中）。您的公式栏现在应该如下所示：

   

8. 接下来，添加一个逗号，后跟源工作表的工作表名称（在我们的示例中为“Employees” ）
9. 添加感叹号 (!) 并键入要从源工作表中查找的单元格范围。请记住通过添加美元符号使这些单元格引用成为绝对引用。
10. 用双引号将整个参数（源工作表名称、感叹号和工作表名称）括起来。
11. 放一个逗号，后跟右括号（关闭 IMPORTRANGE 函数）。
12. 在包含您要检索的值的列的索引后放置一个逗号。在我们的示例中，我们要检索Hourly Rates，它是 A3:C8 范围内的第三列。所以我们输入数字 3。您的公式栏现在应该显示：
13. 最后，关闭 VLOOKUP 函数的括号。您的公式栏现在应该显示：
14. 按下回车键，等待 VLOOKUP 完成处理。
15. 您将被要求允许访问员工表以进行连接。按“允许访问”按钮。

    

16. 您现在应该能够在销售表的单元格 B3 中看到与员工 ID“E010”相对应的小时费率。

    

17. 向下拖动填充柄（位于单元格 B3 的右下角），将公式复制到列中的其他单元格。
18. 您现在应该看到与销售表A 列中提到其 ID 的每个员工对应的小时费率！

    

 

公式说明

VLOOKUP 公式的语法（引用不同工作簿中的数据时）如下：

=vlookup(search_key, Importrange(“{sheetsURL}”,“{sheet name}!{cell range}”),index,is_sorted)

注意工作表名称，感叹号“！” 和单元格范围都用双引号括起来。

 

在我们的示例中，我们想要查找位于工作簿 ‘Wb1’ 的名为 ‘Employees’ 的工作表中的 A3:C8 范围内的数据。于是公式变成了：

=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1nmaT8ggc7no8NVT9U4VXrAfvcBvvvzeJNraHx365MHc/edit#gid=0","员工！$A$3:$C$8"),3)

注意：出于安全考虑，Google 表格不允许访问您无权访问的表格。因此，如果您想从另一个工作簿访问一个工作簿，您需要成为两个工作簿的创建者，或者获得创建者的使用权限。

 

在本教程中，我们向您展示了如何从以下位置进行 VLOOKUP：

• 同一张纸
• 同一工作簿中的不同工作表
• 不同工作簿中的不同工作表。

随着您越来越习惯于使用 VLOOKUP 来参考来自不同来源的信息，您将了解 VLOOKUP 是一个多么强大的工具。

这特别有用，因为原始工作表中的任何更改都会在连接的单元格中自动更新，这使得在 Google 表格中快速引用另一个工作表变得非常容易。

这消除了很多麻烦，让您可以专注于使用 Google 表格 VLOOKUP 功能的最佳方式。

因此，这就是您可以从同一工作表或 Google 表格中的其他工作表进行 VLOOKUP 的方式。

我希望您发现此 Google 表格教程很有用！

 

 

 

本文：查询不同表格, 如何从 Google 表格中的另一张表格进行 VLOOKUP, Google Sheets VLOOKUP HLOOKUP MATCH 范例解说