典型题解及函数参考.doc

1、 典型题解例：现有停车情况记录表如下图 10-7-1 所示，按下列要求操作。图 10-7-1 停车情况记录表操作要求：1、 使用 HLOOKUP 函数，对 Sheet1 中的停车单价进行自动填充。要求： 根据 Sheet1 中的“停车价目表”价格，利用 HLOOKUP 函数对“停车情况记录表”中的“单价”列，根据不同的车型进行自动填充。2、 在 Sheet1 中，利用时间函数计算汽车在停车库中的停放时间，要求： 公式计算方法为“出库时间入库时间” ； 格式为：“小时：分钟：秒” （例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12” ） 。3、 使用函数公式，计算停车费用。要求：根

2、据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。注意： 停车按小时收费，对于不满 1 小时的按 1 小时计费。 对于超过整点小时 15 分钟的多累积 1 小时（例如：1 小时 23 分将以 2 小时计费） 。4、 使用统计函数，对 Sheet1 中的“停车情况记录表” ，根据下列条件进行统计并填入相应单元可知，要求： 统计停车费用大于等于 40 元的停车记录条数。 统计最高的停车费用。5、 对 Sheet2 进行高级筛选，要求： 筛选条件为：“车型”小汽车， “应付金额”=30； 将筛选结果保存在 Sheet2 中。6、 根据 Sheet1，创建一个数据透视图 Chart1，要

3、求： 显示各种车型所收费用的汇总； 行区域设置为“车型” ； 计数项为“应付金额” ； 将对应的数据透视表保存在 Sheet3 中。操作步骤：1、 使用 HLOOKUP 函数的操作如下：步骤 1：单击“停车情况记录表”中的 C9 单元格，使其成为活动单元格。步骤 2：在编辑栏单击插入函数按钮 fx，弹出“插入函数”对话框。在“选择函数”列表框中选择 HLOOKUP 函数，单击 “确定”按钮后，弹击“函数参数”对话框，如图10-7-2 所示。步骤 3：在“函数参数”对话框中，依次输入如图 10-7-2 所示内容。即： Lookup_value 值为：B9（确定在数组区域首行进行搜索时的搜索值）

4、。 Table_array 内容为：$A$2 ：$C$3（可先选择 A2：C3 单元格区域后按 F4 键来绝对引用） ，用于确定要搜索的数组或数据表所在的区域。 Row_index_num 值设为：2，用以确定在数组区域首行搜索到满足搜索值时，要取的值位于该列的第几行，本题是取第二行的值。 Rang_lookup 内容设为：FALSE 。指明是精确匹配查找。单击“确定”按钮。此时，单元格 C9 内容为“5” 。步骤 4：双击单元格 C9 的填充柄，完成停车单价的自动填充。图 10-7-2 HLOOKUP 函数参数设置2、 利用时间函数计算停放时间的操作如下：步骤 1：将光标定位于“停车情况记录

5、表”第一条记录的“停放时间”单元格 F9。步骤 2：在编辑栏输入内容：“E9D9 ”后按回车键。步骤 3：双击 F9 单元格的填充柄完成“停放时间”列的自动填充。3、 使用函数计算停车费用的操作如下：步骤 1：将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格 G9。步骤 2：在编辑栏单击插入函数按钮 fx，弹出“插入函数”对话框。在“选择函数”列表框中选择 IF 函数，单击“确定”按钮后，弹击“函数参数”对话框，如图 10-7-3 所示。步骤 3：在“函数参数”对话框中，依次输入如图 10-7-3 所示内容。即：图 10-7-3 HLOOKUP 函数参数设置 Logical_tes

6、t 框中输入：“HOUR（F9 ）15，HOUR(F9)+1，HOUR(F9)，这是根据题意，当 HOUR（F9）15，HOUR(F9)+1 ，HOUR(F9)*C9 ”（注：也可以是公式：=IF(HOUR(F9)=15)*C9）步骤 5：双击 G9 单元格的填充柄，完成停车应付金额的自动填充。4、 利用统计函数进行统计的操作如下：步骤 1：单击单元格 J9，在编辑栏单击插入函数按钮 fx，弹出“插入函数”对话框。在“选择函数”列表框中选择 COUNTIF 函数，单击“确定”按钮后，弹击“函数参数”对话框，如图 10-7-4 所示。步骤 2：在“函数参数”对话框中，依次输入如图 10-7-4

7、所示内容。即：图 10-7-4 COUNTIF 函数参数设置 Range：框中选择或输入要进行条件计数统计的单元格区域，根据本题要求选择“G9：G39” 。 Criteria：框中输入要统计的单元格的条件，本题应该输入“=40” 。步骤 3：单击“确定” 。步骤 4：单击单元格 J10，在编辑栏输入公式“MAX（G9 ：G39） ”，单击“确定”即可。完成后的 Sheet1 效果如图 10-7-5 所示。5、 对 Sheet2 进行高级筛选的操作如下：步骤 1：在 Sheet2 工作表的无内容区域按题目要求建立好条件区域。这里我们选取I1：J2 区域建立。如图 10-7-6 所示。步骤 2：单

8、击 Sheet2 要进行高级筛选数据列表中任一单元格，选择菜单“数据”中的“筛选”中的“高级筛选”命令，打开“高级筛选”对话框，如图 10-7-7 所示。图 10-7-5 完成操作后的 Sheet1 工作表图 10-7-6 Sheet2 工作表中建立条件区域图 10-7-7 高级筛选对话框步骤 3：在“列表区域”中自动填入数据列表所在区域，将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件区域“I1：J2” ，则“条件区域”文本框内自动填入，单击“确定”按钮完成。6、 创建数据透视图 Chart1 的操作如下：步骤 1：将光标定位于 Sheet1 要建立数据透视图的数据区域内（停车

9、情况记录表）的任意单元格，选择菜单“数据”中的“数据透视表和数据透视图”命令。打开如下图10-7-8 所示的“数据透视表和数据透视图向导3 步骤之 1”对话框。图 10-7-8 数据透视表和数据透视图向导3 步骤之 1步骤 2：在对话框中单击“数据透视图（及数据透视表） ”单选按钮，单击“下一步” ，打开“数据透视表和数据透视图向导3 步骤之 2”对话框，如图 10-7-9 所示。图 10-7-9 数据透视表和数据透视图向导3 步骤之 2步骤 3：单击“下一步” ，打开“数据透视表和数据透视图向导3 步骤之 3”对话框，如图 10-7-10 所示。单击 “现有工作表”选项，再在下面文本框中输入

10、 “Sheet3!$A$1”，也可用光标直接定位，单击“布局”按钮，打开如图 10-7-11 所示的对话框。图 10-7-10 数据透视表和数据透视图向导3 步骤之 3图 10-7-11 数据透视表和数据透视图向导布局步骤 4：在“数据透视表和数据透视图向导布局”对话框中，将右边的“车型”按钮拖动至左边的“行”区域内；将“应付金额”按钮拖至左边的 “数据”区域内，然后按“确定”按钮。回到图 10-7-10 所示的对话框，按“完成 ”按钮。步骤：这时在当前工作簿中会增加一张 Chart1 的工作表，其内容如图是 10-7-12 所示的数据透视图，同时打开 Sheet3，则会出现如图 10-7-1

11、3 所示的数据透视表。图 10-7-12 完成后的数据透视图 图 10-7-13 对应的数据透视表9.4 Excel 常用函数的使用在 9.2 函数概述这一节中我们简单介绍了函数的定义、使用方法以及 10 类函数的基本功能。在本节我们将结合实例对其中一些比较重要的函数加以较详细的介绍。9.4.1 财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。财务函数中常见的参数： 未来值 (fv)-在所有付款发生后的投资或贷款的价值。 期间数 (nper)-为

12、总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 付款 (pmt)-对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。 现值 (pv)-在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率 (rate)-投资或贷款的利率或贴现率。 类型 (type)-付款期间内进行支付的间隔，如在月初或月末，用 0 或 1 表示。 日计数基准类型（basis）-为日计数基准类型。Basis 为 0 或省略代表 US (NASD) 30/360 ，为 1 代表实际天数/实际天数 ，为 2 代表实际天数/360 ，为 3

13、 代表实际天数/365 ，为 4 代表欧洲 30/360。一、投资计算函数投资计算函数可分为与未来值 fv 有关，与付款 pmt 有关，与现值 pv 有关，与复利计算有关及与期间数有关几类函数。1、与未来值 fv 有关的函数-FV 、FVSCHEDULE2、与付款 pmt 有关的函数-IPMT、ISPMT 、PMT、PPMT3、与现值 pv 有关的函数-NPV、PV、XNPV4、与复利计算有关的函数-EFFECT、NOMINAL5、与期间数有关的函数-NPER这里我们重点介绍 FV、PMT 和 PV 函数。（一） 求某项投资的未来值 FV FV 函数是基于固定利率及等额分期付款方式，返回某项投

14、资的未来值。语法形式为：FV(rate,nper,pmt,pv,type)其中 rate 为各期利率，是一固定值，nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv 为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常 Pv 包括本金和利息，但不包括其它费用及税款，pv 为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略 pv，则假设其值为零，type 为数字 0 或 1，用以指定各期的付款时间是在期初还是期末，如果省略 t，则假设其值为零。例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000 元，如果按年利

15、 2.25%，按月计息（月利为 2.25%/12），那么两年以后该账户的存款额会是多少呢？公式写为：FV(2.25%/12, 24,-2000,0,1)各参数含义见下图 9-4-1 所示：图 9-4-1 FV 函数及说明（二） 求贷款分期偿还额 PMTPMT 函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的“分期付款 “。比如借购房贷款或其它贷款时，可以计算每期的偿还额。其语法形式为：PMT(rate,nper,pv,fv,type)其中，rate 为各期利率，是一固定值，np

16、er 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv 为现值，或一系列未来付款当前值的累积和，也称为本金，fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零），type 为 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。例如，需要 10 个月付清的年利率为 8%的￥10,000 贷款的月支额为：PMT（8%/12,10,10000） 计算结果为：-￥1,037.03。（三） 求某项投资的现值 PVPV 函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果

17、投资回收的当前价值大于投资的价值，则这项投资是有收益的。其语法形式为：PV(rate,nper,pmt,fv,type)其中 Rate 为各期利率。Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。Type 用以指定各期的付款时间是在期初还是期末。例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为 80,000

18、，假定投资回报率为 8%。那么该项年金的现值为：PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。各参数说明见下图 9-4-2 所示：图 9-4-2 PV 函数及说明二、 折旧计算函数折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD 、VDB 。这些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再赘述，具体选用哪种折旧方法，则须视各单位情况而定。我们仅

19、以 SLN 函数为例举例介绍： SLN 函数计算的是资料原值；语法格式如下：SLN（cost，salvage，life）其中：cost 表示的产资产原值；salvage 表示的是资产在折旧期末的价值，即资产残值 life表示的是折旧期限，即资产的使用寿命。例：现有固定资产情况表如下图 9-4-3 所示：现要分别计算 “每天折旧值、每月折旧值和每年折旧值” ，填入折旧值情况表中，具体操作步骤如下：图 9-4-3 SLN 函数的应用步骤 1：选中单位格 E2，输入公式：“SLN（B2，B3 ， B4*365） ”，按回车键。步骤 2：选中单位格 E2，输入公式：“=SLN(B2，B3，B4*12)

20、” ，按回车键。步骤 3：选中单位格 E2，输入公式：“=SLN(B2，B3，B4)” ，按回车键。三、偿还率计算函数偿还率计算函数主要用以计算内部收益率，包括 IRR、MIRR 、RATE 和 XIRR 几个函数。四、债券及其他金融函数债券及其他金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。1、计算本金、利息的函数-CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM2、与利息支付时间有关的函数-COUPDAYBS、COUPDAYS、COUPDAYS

21、NC、COUPNCD、COUPPCD3、 与利率收益率有关的函数-INTRATE、ODDFYIELD、ODDLYIELD 、TBILLEQ 、TBILLPRICE 、TBILLYIELD 、YIELD、YIELDDISC、YIELDMAT4、与修正期限有关的函数-DURATION、MDURATION5、与有价证券有关的函数-DISC、ODDFPRICE、ODDLPRICE 、PRICE 、PRICEDISC 、PRICEMAT 、RECEIVED6、与证券价格表示有关的函数-DOLLARDE、DOLLARFR9.4.2 日期与时间函数1、取出当前系统时间/日期信息的函数用于取出当前系统时间/日

22、期信息的函数主要有 NOW、TODAY。语法形式均为：函数名（）例如：NOW（） 结果返回现在的日期和时间TODAY （） 结果返回现在的日期2、取得日期/时间的部分字段值函数如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、 DAY、MONTH、YEAR 函数直接从日期/时间中取出需要的数据。例如，当前工作表的 E5 单元格中内容为：2010-8-30 12:30 PM，现在返回 E5 的年份、月份、日数及小时数，可以分别采用相应函数实现。YEAR(E5) 结果为： 2010 MONTH(E5) 结果为：8 DAY(E5) 结果为：30 HOUR(E5) 结果为：12 MINU

23、TE（E5 ） 结果为： 30例：现有工作表内容如图 9-4-4 所示：要求分别求出其中的年龄和工龄字段的值。操作步骤如下：步骤 1：单击 C2 单元格输入公式： “=YEAR（TODAY（） ）YEAR （B2 ） ”步骤 2：双击 C2 单元格的填充柄。步骤 1：单击 E2 单元格输入公式：“=YEAR（TODAY（） ）YEAR（D2） ”步骤 2：双击 E2 单元格的填充柄。图 9-4-4 日期函数的应用9.4.3 数学与三角函数1、SUM 函数返回某一单元格区域中所有数字之和。语法格式：SUM(number1,number2, .)Number1, number2, . 为 1 到

24、30 个需要求和的参数。即被求和的单元格或单元格区域不能超过 30 个。2、SUMIF 函数SUMIF 函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。其语法格式：SUMIF(range,criteria,sum_range) range 为用于条件判断的单元格区域。 criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32“、“32“ 或 “apples“。 sum_range 是需要求和的实际单元格区域例：现有工作表数据如下图 9-4-5 所示：现要统计各种商品的采购总量

25、和采购总金额。具体操作步骤如下：步骤 1：选中 J12 单元格，输入公式：“SUMIF（$A$11:$A$43,I12,$B$11:$B$43 ） ”。（即求区域 A11：A43 中内容是 I12 的值即“衣服”的单元格对应 B 列内容数值的和。其中区域：B11：B43 是实际求和的区域。 ）步骤 2：双击 J12 单元格的填充柄。步骤 3：选中 K12 单元格，输入公式： “SUMIF（$A$11:$A$43,I12,$F$11:$F$43） ”。步骤 2：双击 K12 单元格的填充柄。图 9-4-5 SUMIF 函数的应用3、舍入函数：在实际工作的数学运算中，特别是财务计算中常常遇到四舍五

26、入的问题。虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。这在财务运算中是不允许的。那是否有简单可行的方法来进行真正的四舍五入呢？有，那就要借助于函数了。Excel 的舍入函数很多，详见表 9-4-1 所示。这里我们着重介绍两个常用的舍入函数：（1）ROUND 函数它的功能就是根据指定的位数，将数字四舍五入。其语法格式：ROUND(number,num_digits)其中 number 就是将要进行四舍五入的数字；num_digits 则是希望得到的数字的小数点后的位数。例如图 9-4-6 所示：单元格

27、 B2 中为初始数据 0.123456，B3 的初始数据为 0.234567，将要对它们进行四舍五入。在单元格 C2 中输入“=ROUND(B2,2) ”，小数点后保留两位有效数字，得到 0.12、0.23。在单元格 D2 中输入“=ROUND(B2,4) ”，则小数点保留四位有效数字，得到 0.1235、0.2346。 图 9-4-6 对数字进行四舍五入（2）INT 函数对于数字进行四舍五入,还可以使用 INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用 INT 函数进行四舍五入还是需要一些技巧的，也就是要加上 0.5，才能达到取整的目的。仍然以上图 9-4-6 为例

28、，如果采用 INT 函数，则 C2 公式应写成：“=INT(B2*100+0.5)/100”。表 9-4-1 常用舍入函数说明CEILING 函数 将参数 Number 沿绝对值增大的方向，舍入为最接近的整数或基数EVEN 函数 返回沿绝对值增大方向取整后最接近的偶数FLOOR 函数 将参数 Number 沿绝对值减小的方向去尾舍入，使其等于最接近的 significance 的倍数INT 函数 返回实数舍入后的整数值ODD 函数 返回对指定数值进行舍入后的奇数ROUND 函数 返回某个数字按指定位数舍入后的数字ROUNDDOWN 函数 靠近零值，向下（绝对值减小的方向）舍入数字ROUNDUP

29、 函数 远离零值，向上（绝对值增大的方向）舍入数字TRUNC 函数 将数字的小数部分截去，返回整数9.4.4 统计函数Excel 的统计工作表函数用于对数据区域进行统计分析。1、AVERAGE 函数求参数的算术平均值函数。AVERAGE 语法形式为：AVERAGE (number1, number2, .)其中 Number1, number2, .为要计算平均值的 130 个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。2、COUNT 函数COUNT 函数用于返回数字参数的个数，

30、即统计数组或单元格区域中含有数值类型的单元格个数，语法格式为：COUNT(value1,value2, .)其中 Value1, value2, .为包含或引用各种类型数据的参数（130 个），但只有数字类型的数据才被计数。函数 COUNT 在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，应当使用函数 COUNTA。3、COUNTBLANK 函数统计某个单元格区域中空白单元格的数目，语法格式为

31、：COUNTBLANK（range）参数 range 表示的是需要计算其中空白单元格数目的区域。4、COUNTIF 函数计算区域中满足给定条件的单元格的个数，其语法格式为：COUNTIF（range，criteria）其中参数 range 表示的是需要计算其中满足条件的单元格数目的单元格区域，参数criteria 表示的是确定哪些单元格将被计算在内的条件，其形式可以是数字、表达式或文本。例：统计女职工人数(要求使用函数 )，并把结果放入字段 “人数”的第一个记录中。如图 9-4-7 所示。操作方法如下：步骤 1：单击 F3 单元格。步骤 2：单击编辑栏中的“插入函数”按钮，打开“插入函数”对话

32、框，类别中选“统计” ，下边选 COUNTIF 函数，按确定。如图 9-4-8 所示。图 9-4-7 利用自动求和按钮输入公式步骤 3：在对话框的“Range”项中单击，然后用鼠标去选区域 D2:D18，放开鼠标后，就在“Range”项中出现参数值“D2:D18 ”，下一步是在第二个参数项中输入“女” ，最后单击确定。如图 9-4-9 所示。图 9-4-8 粘贴函数对话框图 9-4-9 COUNTIF 函数参数的输入5、求数据集的最大值 MAX 函数与最小值 MIN 函数这两个函数 MAX、MIN 就是用来求解数据集的极值（即最大值、最小值） 。函数的用法非常简单。语法形式为：函数（numbe

33、r1,number2,.）其中 Number1,number2,. 为需要找出最大数值的 1 到 30 个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。因此如果逻辑值和文本不能忽略，请使用带 A 的函数 MAXA 或者 MINA 来代替。6、排位函数 RANKRANK 函数的功能是返回一个数值在一组数值中的排位，其完整的格式为：RANK (number，ref，order)其中 Number 为需要找到排位的数字；Ref 为包含一组数字的数组或引用。Order 为一数字用来指明排位的方式。如果 order 为 0 或省略，则 Excel 将 ref 当作按降序排列的数据清单进

34、行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。 需要说明的是，函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。就好像并列第几的概念，例如，在一列整数里，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值） 。例：现有工作表如图 9-4-10 所示，要求对每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。具体操作步骤如下：步骤 1：选中单元格 H2，单击插入函数工具 “fx”，在选择类别中选择 “全部” 。然后选中 RANK 函数，单击“确定”按钮。步骤

35、2：在弹出的“函数参数”设置对话框中分别输入 F2（毛莉同学的总分） 、F2：F39（所有同学的总分区域，可用鼠标选择，选择后按 F4 键会自动将其绝对引用） 、0（由高到低排位） ，如图 9-4-11 所示。按回车键结束编辑。步骤 3：双击 H2 单元格的填充柄快速填充到 H39。图 9-4-10 RANK 排位函数的应用图 9-4-11 RANK 排位函数的参数设置9.4.5 查询与引用函数在 Excel 中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作

36、簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。查询与引用函数可以用来在数据列表或表格中查找特定数值，或者需要查找某一单元格的引用。Excel 中一共提供了ADDRESS、AREAS 、CHOOSE 、COLUMN、COLUMNS 、HLOOKUP 、HYPERLINK、INDEX、 INDIRECT、LOOKUP、 MATCH、OFFSET、ROW 、ROWS、TRANSPOSE 和VLOOKUP 共 16 个查询与引用函数。此类函数的灵活应用对于减少重复数据的录入是大有裨益的。接下来，我们着重介绍其中的三个：VLOOKUP 函数、HLOOKUP 函数和LOOKUP 函数

37、。1、VLOOKUP 函数与 HLOOKUP 函数HLOOKUP 用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。VLOOKUP 用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。这两个函数语法格式基本一样：HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)VLOOKUP (lookup_va

38、lue, table_array, col_index_num, range_lookup)其中，Lookup_value 表示要查找的值，它必须位于自定义查找区域的最左列。lookup_value 可以为数值、引用或文字串。table_array 查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。 row_index_num 为 table_array 中待返回的匹配值的行序号。row_index_num 为 1 时，返回 table_array 第一行的数值， row_index_num 为 2 时，返回 table_array 第二行的

39、数值，以此类推。col_index_num 为相对列号。最左列为 1，其右边一列为 2，依此类推.range_lookup 为一逻辑值，指明函数查找时是精确匹配 (值为“FALSE”时)，还是近似匹配(值为“ TRUE”或省略时，并且此时 table_array 第一列的值必须以递增次序排列，这样才能找到正确的值)。下面以 VLOOKUP 函数为例介绍这两个函数的应用。 VLOOKUP 函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数 VLOOKUP 的查找可以达到两种目的：一是精确的查找。二是近似的查找。下面分别说明。（

40、1） 精确查找-根据区域最左列的值，对其它列的数据进行精确的查找。例：根据图 9-4-12 上半部分的工资表来创建下半部分所示的各个员工的工资条。此工资条为应用 Vlookup 函数建立。以员工 Sandy（编号 A001）的工资条创建为例说明。步骤 1：拷贝标题栏。步骤 2：在单元格 A21 中输入 “A001”。步骤 3：在单元格 B21 中输入公式 “=VLOOKUP($A21,$A$3:$H$12,2,FALSE)”（语法解释：在$A$3:$H$12 范围内（即工资表中）精确找出与 A21 单元格相符的行，并将该行中第二列的内容计入单元格中。 ）步骤 4：以此类推，在随后的单元格中写入

41、相应的公式。（2） 近似的查找-根据定义区域最左列的值，对其它列数据进行不精确值的查找。例：按照项目总额不同提取相应比例的奖金。步骤 1：建立一个项目总额与奖金比例的对照表，如图 9-4-13 上半部分所示。项目总额的数字均为大于情况。即项目总额在 05000 元时，奖金比例为 1%，以此类推。步骤 2：假定某项目的项目总额为 13000 元，在单元格 B11 中输入公式“=VLOOKUP(A11，$A$4:$B$8 ， 2，TRUE) ”，即可求得具体的奖金比例为 5%，如图 9-4-13 下半部分所示。图 9-4-12 VLOOKUP 函数的应用精确查找图 9-4-13 VLOOKUP 函

42、数的应用近似查找2、LOOKUP 函数LOOKUP 用于返回向量（单行区域或单列区域）或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。（1）向量形式函数LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。其基本语法形式为：LOOKUP (lookup_value，lookup_vector，result_vector)Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector 为只包含一行或一列的

43、区域。Lookup_vector 的数值可以为文本、数字或逻辑值。需要注意的是 Lookup_vector 的数值必须按升序排序：.、-2 、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。 Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOK

44、UP 返回错误值 #N/A。 示例详见图 9-4-14 所示：图 9-4-14 LOOKUP 函数的应用（2）数组形式函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。通常情况下，最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP 的数组形式。函数 LOOKUP 的这种形式主要用于与其他电子表格兼容。9.4.6 数据库函数数据库函数（Dfunctions）主要是用于对存储在数据列表或数据库中的数据进行分析。它们具有一些共同特点：（1）每个函数均有三个参数：database、field 和 crite

45、ria。这些参数指向函数所使用的工作表区域。database 为构成数据列表或数据库的单元格区域。数据库是包含一组相关数据的数据列表，其中包含相关信息的行为记录，而包含数据的列为字段。数据列表的第一行包含着每一列的标志项。field 为指定函数所使用的数据列。数据列表中的数据列必须在第一行具有标志项。field 可以是文本，即两端带引号的标志项，如“产量” ；此外，field 也可以是代表数据列表中数据列位置的数字： 1 表示第一列，2 表示第二列，等等。criteria 为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条

46、件的单元格。（2）除了 GETPIVOTDATA 函数之外，其余十二个函数都以字母 D 开头。（3）如果将字母 D 去掉，可以发现其实大多数数据库函数已经在 Excel 的其他类型函数中出现过了。比如，DAVERAGE 将 D 去掉的话，就是求平均值的函数 AVERAGE。Excel 包含的数据库函数及其功能可以参见下表 9-4-2 所示：表 9-4-2 数据库函数及其功能例：现在如图 9-4-15 所示的工作表数据及其右边建好的条件区域。要求利用数据库函数计算：（1）商标为上海，瓦数小于 100 的白炽灯的平均单价。（2）产品为白炽灯，其瓦数大于等于 80 且小于等于 100 的盒数。图 9

47、-4-15 数据库函数的应用操作步骤如下：步骤 1：选中单元格 G23，输入公式 “DAVERAGE （A1：H17 ，E1，J2：L3） ”，然后按回车键。步骤 2：选中单元格 G24，输入公式 “DSUM（A1 ：H17，G1，J7：L8） ”，然后按回车键。注意： 可为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。 虽然条件区域可以在工作表的任意位置，但不要将条件区域置于数据列表下方。因为如果使用“数据”菜单中的“记录单”命令在数据列表中添加信息，新的信息将被添加在数据列表下方的第一行上。如果数据列表下方的行非空，Excel 将无法添加新

48、的信息。 确定条件区域没有与数据列表相重叠。 若要对数据库的整个列进行操作，需要在条件区域中的列标志下方输入一个空白行。9.4.7 文本函数1、REPLACE 函数REPLACE 函数可以使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。其语法格式为：REPLACE (old_text, start_num, num_chars, new_text)其中 old_text 是原始的文本数据，start_num 可以设置从原始文本的第几个字符位置开始替换，num_chars 可以设置共有多少字符要被替换，new_text 是用来替换的新字符串。例：要对下图 9-4-16 的电话

49、号码进行升级，方法是区号“ 0571”后面加上“8” ，并将结果保存在“升级后号码”字段中。操作方法如下：步骤 1：在 G2 单元格中输入 “REPLACE（B2，4，1， 18） ”，然后按回车键。步骤 2：双击 G2 单元格填充柄。图 9-4-16 REPLACE 函数应用2、EXACT 函数EXACT 函数用来测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文字。语法格式为：EXACT (text1, text2)Text1 为待比较的第一个字符串。Text2 为待比较的第二个字符串。举例说明： EXACT(“China“,“china“) 其结果为：“False” 。 9.4.8 逻辑函数逻辑函数是用来判断真假值，或者进行复合检验的 Excel 函数。在 Excel 中提供了六种逻辑函数。即 AND、OR、NOT 、FALSE 、IF、TRUE 函数。1、AND 函数、OR 函数和 NOT 函数这三个函数的语法格式如