自学Excel之23:查找与引用函数(二)

八、参照旧引用获取新引用（OFFSET函数）：

OFFSET函数以指定的引用为参照，按指定偏移量得到新的引用。

此处的引用必须是单元格或相连的单元格区域。单元格区域以左上角单元格为参照系。

语法是：“=OFFSET(reference, rows, cols, height, width)”。

参数：reference是参照引用。

rows是要偏移的行数。正数向下、负数向上。

cols是偏移的列数。正数向右、负数向左。

height是要返回的新引用区域的行数。

width是要返回的新引用区域的列数。

如果省略 height 或 width 参数，则默认其高度和宽度与参照引用相同。

例：1、在成绩表中获取学号为2的学生的科目2的成绩（以“姓名”单元格为参照，向右偏移2行、向下偏移2列）。

在F2单元格插入函数：“=OFFSET(B1,2,2)”，按【Enter】键确认。

2、计算学号为2的学生的总成绩（以“姓名”单元格为参照，向右偏移2行、向下偏移1列，区域范围为1行3列）。

在F4单元格插入函数：“=SUM(OFFSET(B1,2,1,1,3))”，按【Enter】键确认。

九、获取指定行列交叉处单元格的值或引用（INDEX函数）：

INDEX函数用于返回表或区域中的值或值的引用，分为数组形式和引用形式。

1、数组形式：

获取指定行号和列标交叉处的单元格的值。

语法是：“=INDEX(array,row_num,column_num)”。

参数array为单元格区域或数组常量。

row_num为数组中某行的行号。

column_num为数组中某列的列标。

如果省略row_num，则必须有column_num；如果省略column_num，则必须有row_num。

例：将成绩表看作1个区域（A1:D8），从中获取学号为2的学生的姓名及其科目2的成绩。

1）在F2单元格插入函数：“=INDEX(A1:D8,3,2)”，按【Enter】键确认；

2）在F4单元格插入函数：“=INDEX(A1:D8,3,4)”，按【Enter】键确认。

2、引用形式：

返回某区域中指定的行与列交叉处的单元格引用。

语法是：“=INDEX(reference,row_num,column_num,area_num)”。

参数reference为对一个或多个单元格区域的引用。

row_num为引用中某行的行号。

column_num为引用中某列的列标。

area_num为指定行列交叉点所在区域的序号。

例：将成绩表分为学号姓名和科目成绩2个区域，分别从中获取学号为2的学生的姓名及其科目2的成绩。

1）在F2单元格插入函数：“=INDEX((A1:B8,C1:E8),3,2,1)”，按【Enter】键确认；

2）在F4单元格插入函数：“=INDEX((A1:B8,C1:E8),3,2,2)”，按【Enter】键确认。

十、按行（水平）和按列（垂直）查找：

1、HLOOKUP函数：

HLOOKUP函数用于在表格区域或数值数组的首行查找指定的数值，并返回当前行中指定列的值，HLOOKUP中的H表示垂直方向。

语法是：“=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])”。

参数：lookup_value是要在第一行中查找的值。可以为数值、引用或文本字符串。

table_array是在其中查找数据的信息表或区域。

row_index_num是要返回匹配值的行序号。

range_lookup是一个逻辑值，指定查找结果是精确匹配值还是近似匹配值。

如果为 TRUE 或省略，返回近似匹配值。如果找不到则返回小于lookup_value 的最大数值。如果为 FALSE，返回精确匹配值。如果找不到，则返回错误值#N/A。

例：从成绩表中查询某个人的各科目成绩和总成绩。

设置lookup_value为G2单元格，其中输入的内容必须是首行中的项目。

设置查询区域为B1:F8。

设置row_index_num为2，即所选区域中的第2行，令狐冲。

1）在G4单元格 插入函数：“=HLOOKUP(G2,B1:F8,2,FALSE)”，按【Enter】键确认。

2）在G2单元格中输入不同科目，在G4单元格中返回他的对应成绩。

2、VLOOKUP函数：

VLOOKUP函数用于在表格或数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值，VLOOKUP中的V表示垂直方向。

语法是：“=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])”。

参数：lookup_value是要在单元格区域的第一列中查找的值。

table_array是包含数据的单元格区域。

col_index_num是table_array 参数中要返回匹配值的列序号。

range_lookup是一个逻辑值，指定查找结果是精确匹配值还是近似匹配值。

例：从成绩表中按姓名查询其总成绩。

设置lookup_value为G2单元格，其中输入的内容必须是姓名列中的姓名。

设置查询区域为B1:F8。

设置row_index_num为5，即所选区域中的第5列，总成绩。

1）在G4单元格 插入函数：“=VLOOKUP(G2,B1:F8,5)”，按【Enter】键确认。

2）在G2单元格中输入不同姓名，在G4单元格中返回他的总成绩。

十一、兼容性值查找（LOOKUP函数）：

1、向量形式：

LOOKUP函数的向量形式，是在单行或单列区域（向量）中查找指定值，返回第二个单行或单列区域中相同位置的值。

其语法是：“=LOOKUP(lookup_value,lookup_vector,result_vector)”。

参数lookup_value是在向量中要查找的值。lookup_value可以是数字、文本、逻辑值、名称或对值的引用。

lookup_vector是要查找指定值的一行或一列区域，必须以升序顺序排列。

result_vector是要返回值的一行或一列区域。

例：按姓名查询总成绩。

设置lookup_value为F2单元格，其中输入的内容必须是姓名列中的姓名。

设置查询列lookup_vector为A列（A2:A8）。

设置返回列result_vector为E列（E2:E8）。

1）在F4单元格 插入函数：“=LOOKUP(F2,A2:A8,E2:E8)”，按【Enter】键确认。

2）在F2单元格中输入不同姓名，在F4单元格中返回他的总成绩。

2、数组形式：

LOOKUP的数组形式，是在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。

其语法是：“=LOOKUP(lookup_value,array)”。

参数lookup_value是在数组中要查找的值。

array是要查找和返回值的数组。

例：按姓名查询总成绩。

设置lookup_value为F2单元格，其中输入的内容必须是姓名列中的姓名。

设置查询列lookup_vector为A列（A2:A8）。

设置数组array为E整个成绩表（A1:E8）。

1）在F4单元格 插入函数：“=LOOKUP(F2,A1:E8)”，按【Enter】键确认。

2）在F2单元格中输入不同姓名，在F4单元格中返回他的总成绩。

十二、查找指定值的位置（MATCH函数）：

MATCH函数用于在数组中查找指定项，然后返回该项在数组中的相对位置。

语法是：“=MATCH(lookup_value, lookup_array, [match_type])”。

参数：lookup_value是查找的值。该参数可以为数字、文本或逻辑值，也可以是单元格引用。

lookup_array是要搜索的区域。

match_type是查找结果匹配方式。

例：给成绩表按姓名顺序添加序号。

1）在F2单元格插入函数：“=MATCH(A2,A2:A8,0)”，按【Enter】键确认；

2）在F3单元格插入函数：“=MATCH(A3,A2:A8,0)”，按【Enter】键确认；

3）在F4单元格插入函数：“=MATCH(A4,A2:A8,0)”，按【Enter】键确认。

十三、转置单元格区域（TRANSPOSE函数）：

TRANSPOSE函数用于转置单元格区域，即将单元格区的行转为列、列转为行。

在行列数分别与数组的行列数相同的区域中，必须将TRANSPOSE输入为数组公式。

语法是：“=TRANSPOSE(array)”。

参数array为需要进行转置的数组或工作表中的单元格区域。

例：将成绩表的行列互换。

选中区域A10:H14，插入函数：“=TRANSPOSE(A1:E8)”，按【Shift】+【Ctrl】+【Enter】键确认。

十四、创建快捷方式或链接（HYPERLINK函数）：

HYPERLINK函数用于创建一个快捷方式或链接，以便打开存储在硬盘、网络服务器或Internet上的文档。

语法是：“=HYPERLINK(link_location,friendly_name)”。

参数link_location是要打开的文档的完整路径。

friendly_name是单元格中要显示的文本或数字。如果忽略则显示link_location的文本。

例：创建一个打开“百度”网站的链接。

1）在A1单元格中插入函数：“=HYPERLINK("https://www.toutiao.com/","今日头条")”，按【Enter】键确认。

2）点击A1单元格中的“今日头条”，打开“今日头条”网站。