如何在Excel 2016中使用查找和引用函数

已编辑 2 几周前 通过 ExtremeHow 编辑团队

Microsoft Office办公软件Excel表格查找参考功能数据电子表格计算公式视窗苹果电脑生产力

Microsoft Excel 2016是一款功能强大的数据分析和管理工具。它的一个关键功能是一组允许用户轻松查找和引用数据的函数。此指南提供了有关如何在Excel 2016中使用查找和引用函数的详细信息。这些函数帮助用户在电子表格中找到特定的值，管理多个工作表中的数据，甚至可以自动执行一些复杂的数据处理任务。

了解查找函数

Excel中的查找函数旨在帮助您快速找到存储在电子表格中的信息。这些函数在处理大量数据时非常有用，因为它们允许您在不手动搜索的情况下找到特定信息。最常用的查找函数是VLOOKUP、HLOOKUP、LOOKUP以及INDEX和MATCH。

VLOOKUP函数

VLOOKUP函数可能是Excel中最常用的函数之一。它代表“垂直查找”，允许用户在表的第一列中找到一个值，然后从指定列中返回同一行中的值。

VLOOKUP函数的语法如下：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：您要搜索的值。
• table_array：构成表数组的单元格范围。
• col_index_num：您要从中检索数据的表数组中的列编号。
• range_lookup：逻辑值，确定是查找近似匹配（true）还是精确匹配（false）。

考虑以下示例：

=VLOOKUP("Banana", A1:C10, 2, FALSE)

在此示例中，Excel在范围A1:C10的第一列中搜索单词“Banana”，并从找到行的第二列中返回值。如果没有找到“Banana”的精确匹配，Excel将返回一个错误值。

HLOOKUP函数

HLOOKUP函数的工作原理与VLOOKUP相同，但它是在行中而不是列中进行搜索，因此执行“水平查找”。它特别适用于水平显示的数据。

HLOOKUP函数的语法是：

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value：您要搜索的值。
• table_array：包含数据的单元格范围。
• row_index_num：要从其中返回值的表数组中的行编号。
• range_lookup：逻辑值，指定是查找精确匹配（FALSE）还是近似匹配（TRUE）。

示例：

=HLOOKUP(1020, A1:F2, 2, FALSE)

此示例将在范围A1:F2的第一行中搜索值1020，并返回在同一列的第二行中找到的值。

Lookup函数

Excel中的LOOKUP函数可以执行水平和垂直查找，但由于与VLOOKUP和HLOOKUP相比存在的限制，它并不常用。LOOKUP函数有两种语法形式：向量和数组。

向量形式：

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value：您要合并到向量中的值。
• lookup_vector：单行或单列查找向量。
• result_vector：可选。从中获取结果的单行或单列结果向量。

数组形式：

=LOOKUP(lookup_value, array)

• lookup_value：您要在表中匹配的值。
• 数组：由至少两列组成的值范围。

示例：

=LOOKUP("dog", A1:A3, B1:B3)

此示例在范围A1:A3中查找单词“dog”，并返回B1:B3中相应单元格的值。

理解引用功能

Excel中的引用函数可以帮助您有效地处理单元格引用和范围。这些函数在您引用多个数据源的复杂电子表格中特别有用。最重要的引用函数包括INDEX、MATCH、ADDRESS、INDIRECT、OFFSET等。

Index函数

INDEX函数返回在给定范围或数组的行和列交叉处的单元格的值或引用。该函数可以以多种方式使用，尤其是与MATCH函数结合使用以实现更复杂的查找。

INDEX函数的语法是：

=INDEX(array, row_num, [column_num])

• array：一个范围或数组常量。
• row_num：要从中检索值的表中的行号。
• column_num：可选。数组有多个列时要返回的列编号。

示例：

=INDEX(A1:C3, 2, 3)

此示例将返回指定范围A1:C3中第二行和第三列的值。

Match函数

MATCH函数在单元格范围内搜索指定项，然后返回该项在范围内的相对位置。它主要与INDEX一起用于高级查找操作。

MATCH函数的语法是：

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value：您要搜索的值。
• lookup_array：要在其中查找搜索值的单元格范围。
• match_type：可选。号码-1、0或1。这取决于您想要如何搜索。

• 如果match_type为1，MATCH查找小于或等于lookup_value的最大值。数据必须按升序排列。
• 如果match_type为0，MATCH查找第一个正好等于lookup_value的值。数据可以按任何顺序排列。
• 如果match_type为-1，MATCH查找大于或等于lookup_value的最小值。数据必须按降序排列。

示例：

=MATCH(39, A1:A5, 0)

此示例将返回值39在范围A1:A5中的位置。如果39位于第三个单元格中，函数返回数字3。

一起使用INDEX和MATCH

INDEX和MATCH函数的组合是一种功能强大的替代方案，优于VLOOKUP或HLOOKUP，因为它具有灵活性。与VLOOKUP只能在最左列进行搜索不同，INDEX和MATCH可以通过任何列或行进行搜索。

示例：

=INDEX(B1:B10, MATCH("Orange", A1:A10, 0))

在此示例中，Excel使用MATCH在范围A1:A10中搜索“orange”，找到其位置，然后使用INDEX从B1:B10中返回相应的值。

Address函数

ADDRESS函数返回特定行和列号的引用作为文本。当您需要字符串格式的引用时，这可能很有用。

ADDRESS函数的语法是：

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num：单元格引用中使用的行号。
• column_num：单元格引用中使用的列号。
• abs_num：可选。指定引用类型的数字（1到4）。
• a1：可选。指定引用样式的逻辑值。
• sheet_text：可选。要使用的工作表名称。

示例：

=ADDRESS(2, 3)

此示例将返回$C$2，这是对位于第二行和第三列的单元格的绝对引用。

间接函数

INDIRECT函数返回由文本字符串指定的引用。在工作簿中创建动态单元格引用时，这尤其有用。

INDIRECT函数的语法是：

=INDIRECT(ref_text, [a1])

• ref_text：对包含A1样式引用、R1C1样式引用、定义为引用的名称或以文本字符串形式引用单元格的引用。
• a1：可选。一个逻辑值，指定单元格引用参数中包含什么类型的引用。

示例：

=INDIRECT("A" & 1)

此示例使用INDIRECT函数返回位于单元格A1中的值。

偏移函数

OFFSET函数创建一个引用，该引用相对于单元格或单元格范围偏移指定行数和列数，基于起始点。

OFFSET函数的语法是：

=OFFSET(reference, rows, cols, [height], [width])

• 引用：要根据此引用进行偏移。必须引用一个单元格或一组相邻的单元格。
• 行：您想要引用到左上角的单元格之上或之下的行数。
• cols：您想要的左上角单元格的列数，以引用的左侧或右侧。
• 高度：可选。返回引用的行数的高度。
• 宽度：可选。返回引用的列数的宽度。

示例：

=OFFSET(A1, 3, 2)

此示例返回从单元格A1开始向下3行和向右2列偏移的单元格引用。

结论

VLOOKUP、HLOOKUP及其他查找和引用函数将Excel 2016变成了数据分析和处理的强大工具。通过利用这些函数的力量，您可以有效地管理和引用工作表或工作簿中的数据，执行高级数据搜索，并自动执行复杂的计算。

掌握这些函数需要了解其语法、它们如何相互作用以及适用的场景。练习以不同的组合使用这些函数，以了解它们如何协同工作以创建强大而动态的电子表格。

  分享本文

如果你发现文章内容有误, 您可以 请求更新