查询与引用函数应用 – 第2页

Excel 调整引用：OFFSET函数实例图解

OFFSET函数的功能是以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。其语法如下：

OFFSET(reference,rows,cols,height,width)

其中，reference参数作为偏移量参照系的引用区域。rows参数为相对于偏移量参照系的左上角单元格，上（下）偏移的行数。cols参数为相对于偏移量参照系的左上角单元格，左（右）偏移的列数。height参数为高度，即所要返回的引用区域的行数，必须为正数。width参数为宽度，即所要返回的引用区域的列数，必须为正数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“OFFSET函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-32所示。要求根据工作表中的数据内容，以指定的引用为参照系，通过给定偏移量得到新的引用。具体操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=OFFSET(B2,2,3,1,1)”，用于显示E4单元格中的值，输入完成后按“Enter”键返回计算结果，如图16-33所示。

图16-32　原始数据

图16-33　A2单元格返回结果

STEP02：选中A3单元格，在编辑栏中输入公式“=SUM(OFFSET(C3:E5,-2,0,3,3))”，用于对C1:E3数据区域进行求和，输入完成后按“Enter”键返回计算结果，如图16-34所示。

STEP03：选中A4单元格，在编辑栏中输入公式“=OFFSET(C3:E5,0,-3,3,3)”，输入完成后按“Enter”键返回计算结果，因为引用区域不在工作表中，工作表中会显示计算结果为错误值“#REF!”，如图16-35所示。

图16-34　A3单元格返回结果

图16-35　A4单元格返回结果

Excel 返回单元格地址引用值：ADDRESS函数

ADDRESS函数用于按照给定的行号和列标，建立文本类型的单元格地址。其语法如下：

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

其中，row_num参数表示在单元格引用中使用的行号，column_num参数表示在单元格引用中使用的列标，abs_num参数用于指定返回的引用类型，其返回的引用类型如表16-1所示。

a1参数为用于指定A1或R1C1引用样式的逻辑值。如果a1为TRUE或省略，函数ADDRESS返回A1样式的引用；如果a1为FALSE，函数ADDRESS返回R1C1样式的引用。

表16-1　abs_num参数返回的引用类型

sheet_text参数为一文本，用于指定作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ADDRESS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-26所示。要求根据工作表中的数据内容，以文本形式返回单元格地址引用值。具体操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=ADDRESS(6,8)”，用于返回绝对引用（$H$6），输入完成后按“Enter”键返回计算结果，如图16-27所示。

图16-26　原始数据

图16-27　A2单元格返回结果

STEP02：选中A3单元格，在编辑栏中输入公式“=ADDRESS(6,8,2)”，用于返回绝对行号，相对列标（H$6），输入完成后按“Enter”键返回计算结果，如图16-28所示。

STEP03：选中A4单元格，在编辑栏中输入公式“=ADDRESS(6,8,2,FALSE)”，用于返回在R1C1引用样式中的绝对行号，相对列标（R6C[8]），输入完成后按“Enter”键返回计算结果，如图16-29所示。

图16-28　A3单元格返回结果

图16-29　A4单元格返回结果

STEP04：选中A5单元格，在编辑栏中输入公式“=ADDRESS(6,8,1,FALSE,”[Book1]Sheet1″)”，用于返回对其他工作簿或工作表的绝对引用（[Book1]Sheet1!R6C8），输入完成后按“Enter”键返回计算结果，如图16-30所示。

STEP05：选中A6单元格，在编辑栏中输入公式“=ADDRESS(6,8,1,FALSE,”销售报表”)”，用于返回对其他工作表的绝对引用（’销售报表’!R6C8)），输入完成后按“Enter”键返回计算结果，如图16-31所示。

图16-30　A5单元格返回结果

图16-31　A6单元格返回结果

Excel选择参数列表数值：CHOOSE函数

CHOOSE函数使用index_num返回数值参数列表中的数值。使用CHOOSE函数可以根据索引号从最多254个数值中选择一个。例如，如果value1到value7表示一周的7天，当将1到7之间的数字用作index_num时，则CHOOSE返回其中的某一天。其语法如下：

CHOOSE(index_num,value1,value2,...)

其中，index_num参数用于指定所选定的值参数。参数value1、value2……为1～254个数值参数，函数CHOOSE基于index_num，从中选择一个数值或一项要执行的操作。参数可以为数字、单元格引用、定义名称、公式、函数或文本。下面通过实例详细讲解该函数的使用方法与技巧。

图16-23　原始数据

打开“CHOOSE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-23所示。要求根据工作表中的数据内容，返回数值参数列表中的数值。具体操作步骤如下。

STEP01：选中A7单元格，在编辑栏中输入公式“=CHOOSE(4,A2,A3,A4,A5)”，用于返回第4个参数A5的值（完成），输入完成后按“Enter”键返回计算结果，如图16-24所示。

STEP02：选中A8单元格，在编辑栏中输入公式“=CHOOSE(2,B2,B3,B4,B5)”，用于返回第2个参数B3的值（李四），输入完成后按“Enter”键返回计算结果，如图16-25所示。

图16-24　A7单元格返回结果

图16-25　A8单元格返回结果

index_num参数必须为1～254的数字，或者是包含数字1～254的公式或单元格引用。如果index_num参数为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，依此类推。如果index_num参数小于1或大于列表中最后一个值的序号，函数CHOOSE返回错误值“#VALUE!”。如果index_num参数为小数，则在使用前将被截尾取整。如果index_num参数为一个数组，则在计算函数CHOOSE时计算每一个值。函数CHOOSE的数值参数不仅可以为单个数值，也可以为区域引用。例如，下面的公式：

=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))

相当于：

=SUM(B1:B10)

然后基于B1:B10单元格区域中的数值返回值。

函数CHOOSE先被计算，返回引用B1:B10单元格区域，然后函数SUM用B1:B10单元格区域进行求和计算。即函数CHOOSE的结果是函数SUM的参数。

Excel竖直查找：VLOOKUP函数实例图解

VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值，VLOOKUP中的V表示垂直方向。当比较值位于需要查找的数据左边的一列时，可以使用VLOOKUP而不是HLOOKUP。其语法如下：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，lookup_value参数为需要在表格数组第1列中查找的数值。lookup_value可以为数值或引用。table_array参数为两列或多列数据。col_index_num参数为table_array中待返回的匹配值的列序号。range_lookup参数为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“VLOOKUP.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-19所示。要求根据工作表中的数据内容，实现竖直查找。具体的操作步骤如下。

STEP01：选中B10单元格，在编辑栏中输入公式“=VLOOKUP(A10,$A$2:$C$6,2,FALSE)”，用于在A2:C6单元格区域中根据A10单元格中输入的姓名查找对应的职务，输入完成后按“Enter”键返回计算结果，如图16-20所示。

图16-19　原始数据

图16-20　查找职务

STEP02：选中C10单元格，在编辑栏中输入公式“=VLOOKUP(A10,$A$2:$C$6,3,FALSE)”，用于在A2:C6单元格区域中根据A10单元格中输入的姓名查找对应的出生日期，输入完成后按“Enter”键返回计算结果，如图16-21所示。

STEP03：在A10单元格中输入一个员工姓名，例如“李靖”，在B10和C10单元格中就会显示出相应的结果，如图16-22所示。

图16-21　查找出生日期

图16-22　输入员工姓名

Excel水平查找：HLOOKUP函数图解

HLOOKUP函数用于在表格或数值数组的首行查找指定的数值，并在表格或数组中指定行的同一列中返回一个数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，可以使用函数HLOOKUP（HLOOKUP中的H代表“行”）。当比较值位于要查找的数据左边的一列时，则须使用函数VLOOKUP。

HLOOKUP函数的语法如下：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

其中，lookup_value参数为需要在数据表第1行中进行查找的数值。lookup_value可以为数值、引用或文本字符串。table_array参数为需要在其中查找数据的数据表。使用对区域或区域名称的引用。row_index_num参数为table_array中待返回的匹配值的行序号。range_lookup参数为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

下面通过实例详细讲解该函数的使用方法与技巧。

打开“HLOOKUP.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-13所示。要求根据工作表中的数据内容，练习水平查找指定的数值。具体的操作步骤如下所示。

STEP01：选中A6单元格，在编辑栏中输入公式“=HLOOKUP(“CocaCola”,A1:C4,2,TRUE)”，用于在首行查找CocaCola，并返回同列中第2行的值，输入完成后按“Enter”键返回计算结果，如图16-14所示。

图16-13　原始数据

图16-14　A6单元格返回结果

STEP02：选中A7单元格，在编辑栏中输入公式“=HLOOKUP(“Fenda”,A1:C4,3,FALSE)”，用于在首行查找Fenda，并返回同列中第3行的值，输入完成后按“Enter”键返回计算结果，如图16-15所示。

STEP03：选中A8单元格，在编辑栏中输入公式“=HLOOKUP(“F”,A1:C4,3,TRUE)”，用于在首行查找F，并返回同列中第3行的值，由于F不是精确匹配，因此将使用小于F的最大值CocaCola。输入完成后按“Enter”键返回计算结果，如图16-16所示。

图16-15　A7单元格返回结果

图16-16　A8单元格返回结果

STEP04：选中A9单元格，在编辑栏中输入公式“=HLOOKUP(“Future Cola”,A1:C4,4)”，用于在首行查找Future Cola，并返回同列中第4行的值，输入完成后按“Enter”键返回计算结果，如图16-17所示。

STEP05：选中A10单元格，在编辑栏中输入公式“=HLOOKUP(2,{1,2,3;”a”,”b”,”c”;”d”,”e”,”f”},2,TRUE)”，用于在数组常量的第1行中查找3，并返回同列中第2行的值，输入完成后按“Enter”键返回计算结果，如图16-18所示。

图16-17　A9单元格返回结果

图16-18　A10单元格返回结果

Excel MATCH函数数组元素查找

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。其语法如下：

MATCH(lookup_value,lookup_array,match_type)

其中，lookup_value参数为需要在数据表中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。lookup_array参数为可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。match_type参数为数字-1、0或1，指明如何在lookup_array中查找lookup_value。

下面通过实例详细讲解该函数的使用方法与技巧。

打开“MATCH函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-9所示。要求根据工作表中的数据内容，查找在指定方式下与指定数值匹配的数组中元素的相应位置。具体的操作步骤如下。

STEP01：选中A7单元格，在编辑栏中输入公式“=MATCH(1.3,B2:B5,1)”，然后按“Enter”键返回计算结果。由于此处无正确的匹配，所以返回B2:B5数据区域中最接近的下一个值（1.2）的位置，结果如图16-10所示。

图16-9　原始数据

图16-10　A7单元格返回结果

STEP02：选中A8单元格，在编辑栏中输入公式“=MATCH(1.7,B2:B5,0)”，用于返回B2:B5数据区域中1.7的位置，输入完成后按“Enter”键返回计算结果，如图16-11所示。

STEP03：选中A9单元格，在编辑栏中输入公式“=MATCH(1.7,B2:B5,-1)”，然后按“Enter”键返回计算结果，由于B2:B5数据区域不是按降序排列，所以返回错误值“#N/A”，如图16-12所示。

图16-11　A8单元格返回结果

图16-12　A9单元格返回结果

Excel 应用LOOKUP函数查找数据的两种形式

LOOKUP函数用于从单行或单列区域或者从一个数组返回值。LOOKUP函数具有两种语法形式：向量形式和数组形式。

向量形式

向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第2个单行区域或单列区域中相同位置的值。其语法如下：

LOOKUP(lookup_value,lookup_vector,result_vector)

其中，lookup_value参数为LOOKUP函数在第1个向量中搜索的值。lookup_value可以是数字、文本、逻辑值、名称或对值的引用。lookup_vector参数为只包含一行或一列的区域。lookup_vector中的值可以是文本、数字或逻辑值。result_vector参数为只包含一行或一列的区域。它必须与lookup_vector大小相同。

注意：lookup_vector中的值必须以升序顺序放置：…,-2,-1,0,1,2,…；A-Z；FALSE,TRUE，否则，LOOKUP可能无法提供正确的值。大写文本和小写文本是等同的。下面通过实例详细讲解该函数的使用方法与技巧。

打开“LOOKUP函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-1所示。要求根据该工作表中的内容，从单行或单列区域或者从一个数组查找数据。具体的操作步骤如下。

STEP01：选中A8单元格，在编辑栏中输入公式“=LOOKUP(13,A2:A6,B2:B6)”，用于在A列中查找13，然后返回B列中同一行内的值（兰花厅），输入完成后按“Enter”键返回计算结果，如图16-2所示。

图16-1　原始数据

图16-2　返回结果（兰花厅）

STEP02：选中A9单元格，在编辑栏中输入公式“=LOOKUP(18,A2:A6,B2:B6)”，用于在A列中查找18，与接近它的最小值17匹配，然后返回B列中同一行内的值（翠竹轩），输入完成后按“Enter”键返回计算结果，如图16-3所示。

STEP03：选中A10单元格，在编辑栏中输入公式“=LOOKUP(22,A2:A6,B2:B6)”，用于在A列中查找22，与接近它的最小值21匹配，然后返回B列中同一行内的值（紫云斋），输入完成后按“Enter”键返回计算结果，如图16-4所示。

图16-3　返回结果（翠竹轩）

图16-4　返回结果（紫云斋）

STEP04：选中A11单元格，在编辑栏中输入公式“=LOOKUP(0,A2:A6,B2:B6)”，用于在A列中查找0，并返回错误值“#N/A”，0小于lookup_vector A2:A7中的最小值，所以工作表中最终得出的计算结果如图16-5所示。

数组形式

LOOKUP的数组形式在数组的第1行或第1列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。当要匹配的值位于数组的第1行或第1列中时，使用LOOKUP的这种形式。当要指定列或行的位置时，则须使用LOOKUP的另一种形式。

说明：一般情况下，最好使用HLOOKUP或VLOOKUP函数而不是LOOKUP的数组形式。因为LOOKUP的这种形式是为了与其他电子表格程序兼容而提供的。

LOOKUP的数组形式语法如下：

LOOKUP(lookup_value,array)

其中，lookup_value参数为LOOKUP在数组中搜索的值。下面通过实例详细讲解该函数的使用方法与技巧。

打开“LOOKUP函数.xlsx”工作簿，切换至“Sheet2”工作表，本例中的原始数据如图16-6所示。要求根据该工作表中的内容，在数组的第1行或第1列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。具体的操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=LOOKUP(“B”,{“A”,”B”,”C”,”D”;5,6,7,8})”，用于在数组的第1行中查找“B”，查找小于或等于它（“B”）的最大值，然后返回最后一行中同一列内的值，输入完成后按“Enter”键返回计算结果，如图16-7所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=LOOKUP(“apple”,{“A”,3;”B”,4;”C”,5})”，用于在数组的第1行中查找“apple”，查找小于或等于它（“A”）的最大值，然后返回最后一列中同一行内的值，输入完成后按“Enter”键返回计算结果，如图16-8所示。

图16-5　计算结果

图16-6　原始数据

图16-7　A2单元格计算结果

图16-8　A3单元格计算结果