查找和引用函数应用技巧 – 第2页

Excel 应用INDEX函数计算表或区域中的值或值的引用

INDEX函数用于返回表或区域中的值或值的引用，它两种形式：数组形式和引用形式。

数组形式

返回表格或数组中的元素值，此元素由行序号和列序号的索引值给定。当函数INDEX的第一个参数为数组常量时，使用数组形式。其语法如下。

INDEX(array,row_num,column_num)

其中参数array为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数row_num或column_num为可选参数。如果数组有多行和多列，但只使用row_num或column_num，函数INDEX返回数组中的整行或整列，且返回值也为数组。row_num为数组中某行的行号，函数从该行返回数值；如果省略row_num，则必须有column_num。column_num为数组中某列的列标，函数从该列返回数值；如果省略column_num，则必须有row_num。

【典型案例】返回表格或数组中的元素值。本例的原始数据如图15-13所示。

步骤1：在A5单元格中输入公式“=INDEX（A2：B3，2，2）”，用于返回位于区域中第二行和第二列交叉处的数值（沙和尚）。

图15-13　原始数据

步骤2：在A6单元格中输入公式“=INDEX（A2：B3，2，1）”，用于返回位于区域中第二行和第一列交叉处的数值（唐三藏）。计算结果如图15-14所示。

引用形式

返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。其语法如下。

INDEX(reference,row_num,column_num,area_num)

图15-14　计算结果

其中参数reference为对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来；如果引用中的每个区域只包含一行或一列，则相应的参数row_num或column_num分别为可选项。例如，对于单行的引用，可以使用函数INDEX（reference，column_num）。

参数row_num为引用中某行的行号，函数从该行返回一个引用。参数column_num为引用中某列的列标，函数从该列返回一个引用。area_num选择引用中的一个区域，返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，依此类推。如果省略area_num，则函数INDEX使用区域1。

例如，如果引用描述的单元格为（A1：C4，D1：E4，F1：H4），则area_num 1为区域“A1：C4”，area_num 2为区域“D1：E4”，而area_num 3为区域“F1：H4”。

【典型案例】返回指定的行与列交叉处的单元格引用。本例的原始数据如图15-15所示。

图15-15　原始数据

步骤1：在A12单元格中输入公式“=INDEX（A2：C6，2，3）”，用于返回区域“A2：C6”中第二行和第三列的交叉处，即C3单元格的内容。

步骤2：在A13单元格中输入公式“=INDEX（（A1：C6，A7：C10），2，2，2）”，用于返回第二个区域“A7：C10”中第二行和第二列的交叉处，即B8单元格的内容。

步骤3：在A14单元格中输入公式“=SUM（INDEX（A1：C10，0，3，1））”，用于对第一个区域“A1：C10”中的第三列求和，即对“C1：C10”求和。

步骤4：在A15单元格中输入公式“=SUM（B2：INDEX（A2：C6，5，2））”，用于返回以B2单元格开始到“A2：C6”单元格区域中第五行和第二列交叉处结束的单元格区域的和，即“B2：B6”单元格区域的和。计算结果如图15-16所示。

图15-16　计算结果

Excel 应用HYPERLINK函数创建快捷方式（跳转）

HYPERLINK函数用于创建一个快捷方式（跳转），以便打开存储在网络服务器、Intranet或Internet中的文件。当单击函数HYPERLINK所在的单元格时，Excel将打开存储在参数link_location中的文件。其语法如下：

HYPERLINK(link_location,friendly_name)

其中参数link_location为文档的路径和文件名，此文档可以作为文本打开。friendly_name为单元格中显示的跳转文本值或数字值。

说明：如果要选定一个包含超链接的单元格并且不跳往超链接的目标文件，则须单击单元格区域并按住鼠标按钮直到光标变成一个十字，然后释放鼠标按钮。

【典型案例】创建用于打开Internet上某工作表或其他工作表的超链接。

步骤1：以下公式打开存储在Internet的“example.microsoft.com/report”地址上的工作表“Budget Report.xls”，并显示文本“单击打开报告”：

=HYPERLINK("http://example.microsoft.com/report/budget report.xls","单击打开报告")

步骤2：以下公式创建对工作簿“Budget Report.xls”的“Annual”工作表中F10单元格的超链接，该工作簿存储在Internet的“example.microsoft.com/report”地址上。工作表中包含超链接的单元格将以D1单元格的内容作为其显示的跳转文本：

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]Annual!F10",D1)

步骤3：以下公式创建对工作簿“Budget Report.xls”的“第1季度”工作表中DeptTotal区域的超链接，该工作簿存储在Internet的“example.microsoft.com/report”地址上。工作表中包含超链接的单元格将显示跳转文本“单击此处查看第一季度部门汇总”。

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]First Quarter! DeptTotal", "单击此处查看第一季度部门汇总")

步骤4：以下公式将创建指向另一个外部工作簿“Mybook.xls”中名为Totals的区域的超链接。

=HYPERLINK("[C:\My Documents\Mybook.xls]Totals")

步骤5：可以在工作表内创建超链接，以便从一个单元格跳转到另一个单元格。例如，如果“Budget”工作簿中的“七月”工作表为活动工作表，则下面的公式将创建跳转到E56单元格的超链接，链接的文本本身为E56单元格中的数值。

=HYPERLINK("[Budget]七月!E56",E56)

说明：如果要跳转到同一工作簿中的其他工作表，则可以更改超链接中的工作表名称。在上例中，如果要创建指向“十一月”工作表中E56单元格的链接，则将公式中的“七月”更改为“十一月”。

Excel 应用HLOOKUP函数实现水平查找

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

HLOOKUP函数的语法如下。

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

各参数的作用介绍如下。

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

【典型案例】练习水平查找指定的数值。本例的原始数据如图15-11所示。

步骤1：在A2单元格中输入公式“=HLOOKUP（”CocaCola”，A1：C4，2，TRUE）”，用于在首行查找CocaCola，并返回同列中第2行的值。

步骤2：在A3单元格中输入公式“=HLOOKUP（”Fenda”，A1：C4，3，FALSE）”，用于在首行查找Fenda，并返回同列中第3行的值。

步骤3：在A4单元格A4中输入公式“=HLOOKUP（”F”，A1：C4，3，TRUE）”，用于在首行查找F，并返回同列中第3行的值。由于F不是精确匹配，因此将使用小于F的最大值CocaCola。

步骤4：在A5单元格A5中输入公式“=HLOOKUP（”Future Cola”，A1：C4，4）”，用于在首行查找Future Cola，并返回同列中第4行的值。

图15-11　本例的原始数据

步骤5：在A6单元格中输入公式“=HLOOKUP（2，{1，2，3；”a”，”b”，”c”；”d”，”e”，”f”}，2，TRUE）”，用于在数组常量的第一行中查找3，并返回同列中第2行的值。计算结果如图15-12所示。

图15-12　计算结果

Excel 应用COLUMNS函数计算数组或引用的列数

COLUMNS函数用于返回数组或引用的列数。其语法如下。

COLUMNS(array)

其中参数array为需要得到其列数的数组或数组公式，或对单元格区域的引用。

【典型案例】返回数组或引用的列数。本例的原始数据如图15-9所示。

步骤1：在A2单元格中输入公式“=COLUMNS（A1：D5）”，用于返回第四个参数A5的值（完成）。

步骤2：在A3单元格中输入公式“=COLUMNS（{1，2，3，4；4，5，6，7；5，6，7，8}）”，用于返回第二个参数B3的值（李四）。计算结果如图15-10所示。

图15-9　原始数据

图15-10　计算结果

Excel 应用COLUMN函数计算给定引用的列标

COLUMN函数用于返回给定引用的列标。其语法如下。

COLUMN(reference)

其中参数reference为需要得到其列标的单元格或单元格区域。如果省略reference，则假定为是对函数COLUMN所在单元格的引用；如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference中的列标以水平数组的形式返回。reference不能引用多个区域。

【典型案例】返回给定引用的列标。本例的原始数据如图15-7所示。

步骤1：在A2单元格中输入公式“=COLUMN（）”，用于返回公式所在列的列标。

图15-7　原始数据

步骤2：在A3单元格中输入公式“=COLUMN（B15）”，用于返回B15的列标。计算结果如图15-8所示。

图15-8　计算结果

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，从中选择一个数值或一项要执行的操作。参数可以为数字、单元格引用、定义名称、公式、函数或文本。

【典型案例】返回数值参数列表中的数值。本例的原始数据如图15-5所示。

步骤1：在A7单元格中输入公式“=CHOOSE（4，A2，A3，A4，A5）”，用于返回第四个参数A5的值（完成）。

步骤2：在A8单元格中输入公式“=CHOOSE（2，B2，B3，B4，B5）”，用于返回第二个参数B3的值（李四）。计算结果如图15-6所示。

图15-5　原始数据

图15-6　计算结果

【使用指南】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 应用AREAS函数计算引用中的区域个数

AREAS函数用于返回引用中包含的区域个数。区域表示连续的单元格区域或某个单元格。其语法如下。

AREAS(reference)

其中参数reference表示对某个单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来，以免Excel将逗号作为参数间的分隔符。

【典型案例】返回引用中包含的区域个数。本例的原始数据如图15-3所示。

图15-3　原始数据

步骤1：在A2单元格中输入公式“=AREAS（B1：D5）”。

步骤2：在A3单元格中输入公式“=AREAS（（B1：D5，E5，F6：I9，G6））”。

步骤3：在A4单元格中输入公式“=AREAS（B1：D5 B2）”。计算结果如图15-4所示。

图15-4　计算结果

Excel 应用ADDRESS函数以文本形式返回引用值

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

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

各参数的作用介绍如下。

row_num：表示在单元格引用中使用的行号。
column_num：表示在单元格引用中使用的列标。
abs_num：用于指定返回的引用类型，abs_num函数返回的引用类型如表15-1所示。
a1：用于指定A1或R1C1引用样式的逻辑值。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用；如果A1为FALSE，函数ADDRESS返回R1C1样式的引用。
sheet_text：为一文本，用于指定作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。

表15-1　abs_num函数返回的引用类型

【典型案例】以文本形式返回单元格地址引用值。本例的原始数据如图15-1所示。

步骤1：在A2单元格中输入公式“=ADDRESS（6，8）”，用于返回绝对引用（$H$6）。

步骤2：在A3单元格中输入公式“=ADDRESS（6，8，2）”，用于返回绝对行号，相对列标（H$6）。

步骤3：在A4单元格中输入公式“=ADDRESS（6，8，2，FALSE）”，用于返回在R1C1引用样式中的绝对行号，相对列标（R6C[8]）。

步骤4：在A5单元格中输入公式“=ADDRESS（6，8，1，FALSE，”[Book1]Sheet1″）”，用于返回对其他工作簿或工作表的绝对引用（[Book1]Sheet1！R6C8）。

步骤5：在A6单元格中输入公式“=ADDRESS（6，8，1，FALSE，”销售报表”）”，用于返回对其他工作表的绝对引用（’销售报表’！R6C8）。计算结果如图15-2所示。

图15-1　原始数据

图15-2　计算结果