分类：函数篇

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　计算结果

Excel 数据库函数的使用实战

使用数据库函数可以处理和分析数据清单中的数据，以得到用户想要的资料。本节将通过一个综合实例，来说明数据库函数的使用。

打开工资表文件。可以看到，该数据清单的数据字段包括了员工姓名、性别、年龄、职务以及员工的工资额。基础数据如图14-27所示。

图14-27　基础数据清单

根据上面的基础数据清单，公司需要了解的信息有：

·销售员中工资额的最高值。

·工资额在1200到1500间的员工个数。

·女员工的平均年龄。

·采购员的工资总和。

·经理苏小北的工资额。

·采购员中工资额的最小值。

下面的步骤将详细讲解怎样使用数据库函数，对上面的信息进行统计。

步骤1：设置计算表格。根据公司的要了解的信息，设置的计算表格如图14-28所示。

图14-28　计算表格

步骤2：统计销售员中工资额的最高值。在G11：G12单元格区域设置数据库查询的数据字段“职务”和条件值“销售员”，然后在J2单元格中输入公式“=DMAX（A1：E15，E1，G11：G12）”，按Enter键，返回销售员中工资额的最高值，计算结果如图14-29所示。

步骤3：统计工资额在1200到1500间的员工个数。在H11：I12单元格区域设置数据库查询的数据字段“工资额”和条件值“>1200”和“<1500”，然后在J3单元格中输入公式“=DCOUNT（A1：E15，E1，H11：I12）”，按Enter键，返回工资额在1200~1500元之间的员工个数，计算结果如图14-30所示。

步骤4：统计女员工的平均年龄。在J11：J12单元格区域，设置数据库查询的数据字段“性别”和条件值“女”，然后在J4单元格中输入公式“=DAVERAGE（A1：E15，C1，J11：J12）”，按Enter键，返回女员工的平均年龄，计算结果如图14-31所示。

图14-29　统计销售员中工资额的最高值

图14-30　统计工资额在1200到1500间的员工个数

图14-31　统计女员工的平均年龄

步骤5：统计采购员的工资总和。在G13：G14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后在J5单元格中输入公式“=DSUM（A1：E15，E1，G13：G14）”，按Enter键，返回采购员的工资总和，计算结果如图14-32所示。

步骤6：统计经理苏小北的工资额。在H13：I14单元格区域设置数据库查询的数据字段“职务”和“姓名”，设置条件值“经理”和“苏小北”，然后在J6单元格中输入公式“=DGET（A1：E15，E1，H13：I14）”，按Enter键，返回经理苏小北的工资额，计算结果如图14-33所示。

图14-32　统计采购员的工资总和

图14-33　统计经理苏小北的工资额

步骤7：统计采购员中工资额的最小值。在J13：J14单元格区域设置数据库查询的数据字段“职务”和条件值“采购员”，然后在J7单元格中输入公式“=DMIN（A1：E15，E1，J13：J14）”，按Enter键，返回采购员中工资额的最小值，计算结果如图14-34所示。

图14-34　统计采购员中工资额的最小值

Excel 应用DVARP函数计算总体方差

DVARP函数用于返回将列表或数据库中满足指定条件的列中数值作为样本总体，计算出样本的总体方差。其语法如下：

DVARP(database,field,criteria)

下面通过实例来说明DVARP函数的应用。

根据图14-1的基础数据清单，班主任想要了解：

  • 性别为女生的英语成绩的样本总体方差。
  • 总分大于等于240分的男生成绩的样本总体方差。

步骤1：根据上面提出的查询条件，设置的计算表格和条件区域如图14-25所示。

步骤2：分别在单元格E22~E23中，输入下面的公式，然后按Enter键，返回结果如图14-26所示。

=DVARP(A1:G16,E1,A25:A26)
=DVARP(A1:G16,F1,B25:C26)

图14-25　计算表格和条件区域

图14-26　计算结果

Excel 应用DVAR函数计算样本方差

DVAR函数用于返回将列表或数据库中满足指定条件的列中数值作为一个样本，估算样本的总体方差。其语法如下：

DVAR(database,field,criteria)

下面通过实例来说明DVAR函数的应用。

根据图14-1的基础数据清单，班主任想要了解：

  • 性别为女生的英语成绩的样本方差。
  • 总分大于等于240分的男生成绩的样本方差。

步骤1：根据上面提出的查询条件，设置的计算表格和条件区域如图14-23所示。

步骤2：分别在单元格E22~E23中，输入下面的公式，然后按Enter键，返回结果如图14-24所示。

=DVAR(A1:G16,E1,A25:A26)
=DVAR(A1:G16,F1,B25:C26)

图14-23　计算表格和条件区域

图14-24　计算结果