典型题解及函数参考

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

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

3、数项为“应付金额”；l 将对应的数据透视表保存在sheet3中。操作步骤：1、 使用hlookup函数的操作如下：步骤1：单击“停车情况记录表”中的c9单元格，使其成为活动单元格。步骤2：在编辑栏单击插入函数按钮fx，弹出“插入函数”对话框。在“选择函数”列表框中选择hlookup函数，单击“确定”按钮后，弹击“函数参数”对话框，如图10-7-2所示。步骤3：在“函数参数”对话框中，依次输入如图10-7-2所示内容。即：l lookup_value值为：b9（确定在数组区域首行进行搜索时的搜索值）。l table_array内容为：$a$2：$c$3（可先选择a2：c3单元格区域后按f4键来绝

4、对引用），用于确定要搜索的数组或数据表所在的区域。l row_index_num值设为：2，用以确定在数组区域首行搜索到满足搜索值时，要取的值位于该列的第几行，本题是取第二行的值。l rang_lookup内容设为：false。指明是精确匹配查找。单击“确定”按钮。此时，单元格c9内容为“5”。步骤4：双击单元格c9的填充柄，完成停车单价的自动填充。图10-7-2hlookup函数参数设置2、 利用时间函数计算停放时间的操作如下：步骤1：将光标定位于“停车情况记录表”第一条记录的“停放时间”单元格f9。步骤2：在编辑栏输入内容：“e9d9”后按回车键。步骤3：双击f9单元格的填充柄完成“停放时

5、间”列的自动填充。3、 使用函数计算停车费用的操作如下：步骤1：将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格g9。步骤2：在编辑栏单击插入函数按钮fx，弹出“插入函数”对话框。在“选择函数”列表框中选择if函数，单击“确定”按钮后，弹击“函数参数”对话框，如图10-7-3所示。步骤3：在“函数参数”对话框中，依次输入如图10-7-3所示内容。即：图10-7-3hlookup函数参数设置l logical_test框中输入：“hour（f9）0”，即使用时间函数hour（）提取f9单元格内的小时数进行判断是否小于0。l value_if_true框中输入：1。即：hour（f9）

6、15，hour(f9)+1，hour(f9)，这是根据题意，当hour（f9）0不成立时，要对超过整点小时十五分钟进行判断，如超过则还要多累积一个小时。步骤4：单击“确定”按钮，在编辑栏中对刚才的if函数公式进行编辑修改，要在计算停车小时数的后面输入“*c9”得到应付金额。整个公式内容为：“=if(hour(f9)15，hour(f9)+1，hour(f9)*c9”（注：也可以是公式：=if(hour(f9)=15)*c9）步骤5：双击g9单元格的填充柄，完成停车应付金额的自动填充。4、 利用统计函数进行统计的操作如下：步骤1：单击单元格j9，在编辑栏单击插入函数按钮fx，弹出“插入函数”对话

7、框。在“选择函数”列表框中选择countif函数，单击“确定”按钮后，弹击“函数参数”对话框，如图10-7-4所示。步骤2：在“函数参数”对话框中，依次输入如图10-7-4所示内容。即：图10-7-4countif函数参数设置l range：框中选择或输入要进行条件计数统计的单元格区域，根据本题要求选择“g9：g39”。l criteria：框中输入要统计的单元格的条件，本题应该输入“=40”。步骤3：单击“确定”。步骤4：单击单元格j10，在编辑栏输入公式“max（g9：g39）”，单击“确定”即可。完成后的sheet1效果如图10-7-5所示。5、 对sheet2进行高级筛选的操作如下：步

8、骤1：在sheet2工作表的无内容区域按题目要求建立好条件区域。这里我们选取i1：j2区域建立。如图10-7-6所示。步骤2：单击sheet2要进行高级筛选数据列表中任一单元格，选择菜单“数据”中的“筛选”中的“高级筛选”命令，打开“高级筛选”对话框，如图10-7-7所示。图10-7-5完成操作后的sheet1工作表图10-7-6 sheet2工作表中建立条件区域图10-7-7 高级筛选对话框步骤3：在“列表区域”中自动填入数据列表所在区域，将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件区域“i1：j2”，则“条件区域”文本框内自动填入，单击“确定”按钮完成。6、 创建数据透视

9、图chart1的操作如下：步骤1：将光标定位于sheet1要建立数据透视图的数据区域内（停车情况记录表）的任意单元格，选择菜单“数据”中的“数据透视表和数据透视图”命令。打开如下图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，则会出现

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

12、为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。l 付款 (pmt)-对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。l 现值 (pv)-在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。l 利率 (rate)-投资或贷款的利率或贴现率。l 类型 (type)-付款期间内进行支付的间隔，如在月初或月末，用0或1表示。l 日计数基准类型（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函数。（一） 求某项投资的未来值fvfv函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。语法形式为：fv(rate,nper,pmt,pv

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

15、款额会是多少呢？公式写为：fv(2.25%/12, 24,-2000,0,1)各参数含义见下图9-4-1所示：图9-4-1fv函数及说明（二） 求贷款分期偿还额pmtpmt函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。pmt函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的分期付款。比如借购房贷款或其它贷款时，可以计算每期的偿还额。其语法形式为：pmt(rate,nper,pv,fv,type)其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的

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

17、ate为各期利率。nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。type用以指定各期的付款时间是在期初还是期末。例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那么该项年金的现值为：pv(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。负值表

18、示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。各参数说明见下图9-4-2所示：图9-4-2pv函数及说明二、 折旧计算函数折旧计算函数主要包括amordegrc、amorlinc、db、ddb、sln、syd、vdb。这些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再赘述，具体选用哪种折旧方法，则须视各单位情况而定。我们仅以sln函数为例举例介绍：l sln函数计算的是资料原值；语法格式如下：sln（cost，salvage，life）其中：cost表示的产资产原值；sal

19、vage表示的是资产在折旧期末的价值，即资产残值life表示的是折旧期限，即资产的使用寿命。例：现有固定资产情况表如下图9-4-3所示：现要分别计算“每天折旧值、每月折旧值和每年折旧值”，填入折旧值情况表中，具体操作步骤如下：图9-4-3sln函数的应用步骤1：选中单位格e2，输入公式：“sln（b2，b3，b4*365）”，按回车键。步骤2：选中单位格e2，输入公式：“=sln(b2，b3，b4*12)”，按回车键。步骤3：选中单位格e2，输入公式：“=sln(b2，b3，b4)”，按回车键。三、偿还率计算函数偿还率计算函数主要用以计算内部收益率，包括irr、mirr、rate和xirr几个

20、函数。四、债券及其他金融函数债券及其他金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。1、计算本金、利息的函数-cumprinc、accrint、accrintm、cumipmt、coupnum2、与利息支付时间有关的函数-coupdaybs、coupdays、coupdaysnc、coupncd、couppcd3、 与利率收益率有关的函数-intrate、oddfyield、oddlyield、tbilleq、tbillprice、tbillyield、yield、yielddis

21、c、yieldmat4、与修正期限有关的函数-duration、mduration5、与有价证券有关的函数-disc、oddfprice、oddlprice、price、pricedisc、pricemat、received6、与证券价格表示有关的函数-dollarde、dollarfr9.4.2日期与时间函数1、取出当前系统时间/日期信息的函数用于取出当前系统时间/日期信息的函数主要有now、today。语法形式均为：函数名（）例如：now（）结果返回现在的日期和时间today（）结果返回现在的日期2、取得日期/时间的部分字段值函数如果需要单独的年份、月份、日数或小时的数据时，可以使用hou

22、r、day、month、year函数直接从日期/时间中取出需要的数据。例如，当前工作表的e5单元格中内容为：2010-8-30 12:30 pm，现在返回e5的年份、月份、日数及小时数，可以分别采用相应函数实现。year(e5)结果为：2010month(e5)结果为：8day(e5)结果为：30hour(e5)结果为：12 minute（e5）结果为：30例：现有工作表内容如图9-4-4所示：要求分别求出其中的年龄和工龄字段的值。操作步骤如下：步骤1：单击c2单元格输入公式：“=year（today（）year（b2）”步骤2：双击c2单元格的填充柄。步骤1：单击e2单元格输入公式：“=ye

23、ar（today（）year（d2）”步骤2：双击e2单元格的填充柄。图9-4-4日期函数的应用9.4.3数学与三角函数1、sum函数返回某一单元格区域中所有数字之和。语法格式：sum(number1,number2, .)number1, number2, . 为 1 到 30 个需要求和的参数。即被求和的单元格或单元格区域不能超过30个。2、sumif函数sumif函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。其语法格式：sumif(range,criteria,sum_range)l range 为用于条件判断的单元格区域。l c

24、riteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。l sum_range 是需要求和的实际单元格区域例：现有工作表数据如下图9-4-5所示：现要统计各种商品的采购总量和采购总金额。具体操作步骤如下：步骤1：选中j12单元格，输入公式：“sumif（$a$11:$a$43,i12,$b$11:$b$43）”。（即求区域a11：a43中内容是i12的值即“衣服”的单元格对应b列内容数值的和。其中区域：b11：b43是实际求和的区域。）步骤2：双击j12单元格的填充柄。步骤3：选中k12单元格，输入公式：“s

25、umif（$a$11:$a$43,i12,$f$11:$f$43）”。步骤2：双击k12单元格的填充柄。图9-4-5sumif函数的应用3、舍入函数：在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。这在财务运算中是不允许的。那是否有简单可行的方法来进行真正的四舍五入呢？有，那就要借助于函数了。excel的舍入函数很多，详见表9-4-1所示。这里我们着重介绍两个常用的舍入函数：（1）round函数它的功能就是根据指定的位数，将数字四舍五入。其

26、语法格式：round(number,num_digits)其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。例如图9-4-6所示：单元格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（取整函数），但由于这个函数的

27、定义是返回实数舍入后的整数值。因此，用int函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。仍然以上图9-4-6为例，如果采用int函数，则c2公式应写成：“=int(b2*100+0.5)/100”。表9-4-1常用舍入函数说明ceiling 函数将参数 number 沿绝对值增大的方向，舍入为最接近的整数或基数even 函数返回沿绝对值增大方向取整后最接近的偶数floor 函数将参数 number 沿绝对值减小的方向去尾舍入，使其等于最接近的 significance 的倍数int 函数返回实数舍入后的整数值odd 函数返回对指定数值进行舍入后的奇数round

28、函数返回某个数字按指定位数舍入后的数字rounddown 函数靠近零值，向下（绝对值减小的方向）舍入数字roundup 函数远离零值，向上（绝对值增大的方向）舍入数字trunc 函数将数字的小数部分截去，返回整数9.4.4统计函数excel的统计工作表函数用于对数据区域进行统计分析。1、average函数求参数的算术平均值函数。average语法形式为：average (number1, number2, .)其中number1, number2, .为要计算平均值的 130 个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽

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

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

31、元格。步骤2：单击编辑栏中的“插入函数”按钮，打开“插入函数”对话框，类别中选“统计”，下边选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就是用来求解数据集的极值（即最大值、最小值）。函数的用法非常简单。语法形

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

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

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

35、数据。查询与引用函数可以用来在数据列表或表格中查找特定数值，或者需要查找某一单元格的引用。excel中一共提供了address、areas、choose、column、columns、hlookup、hyperlink、index、indirect、lookup、match、offset、row、rows、transpose和vlookup共16个查询与引用函数。此类函数的灵活应用对于减少重复数据的录入是大有裨益的。接下来，我们着重介绍其中的三个：vlookup函数、hlookup函数和lookup函数。1、vlookup函数与hlookup函数hlookup用于在表格或数值数组的首行查找指定

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

37、kup_value表示要查找的值，它必须位于自定义查找区域的最左列。lookup_value 可以为数值、引用或文字串。table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。 row_index_num为 table_array 中待返回的匹配值的行序号。row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.range_look

38、up为一逻辑值，指明函数查找时是精确匹配(值为“false”时)，还是近似匹配(值为“true”或省略时，并且此时table_array第一列的值必须以递增次序排列，这样才能找到正确的值)。下面以vlookup函数为例介绍这两个函数的应用。vlookup函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数vlookup的查找可以达到两种目的：一是精确的查找。二是近似的查找。下面分别说明。（1） 精确查找-根据区域最左列的值，对其它列的数据进行精确的查找。例：根据图9-4-12上半部分的工资表来创建下半部分所示的各个员工的工资条

39、。此工资条为应用vlookup函数建立。以员工sandy（编号a001）的工资条创建为例说明。步骤1：拷贝标题栏。步骤2：在单元格a21中输入“a001”。步骤3：在单元格b21中输入公式“=vlookup($a21,$a$3:$h$12,2,false)”（语法解释：在$a$3:$h$12范围内（即工资表中）精确找出与a21单元格相符的行，并将该行中第二列的内容计入单元格中。）步骤4：以此类推，在随后的单元格中写入相应的公式。（2） 近似的查找-根据定义区域最左列的值，对其它列数据进行不精确值的查找。例：按照项目总额不同提取相应比例的奖金。步骤1：建立一个项目总额与奖金比例的对照表，如图9-

40、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函数的应用近似查找2、lookup函数lookup用于返回向量（单行区域或单列区域）或数组中的数值。函数 lookup 有两种语法形式：向量和数组。（1）向量形式函数lookup的向量形式是在单行区域或单列区域（向量）中查找

41、数值，然后返回第二个单行区域或单列区域中相同位置的数值。其基本语法形式为：lookup (lookup_value，lookup_vector，result_vector)lookup_value为函数 lookup 在第一个向量中所要查找的数值。lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。lookup_vector为只包含一行或一列的区域。lookup_vector 的数值可以为文本、数字或逻辑值。需要注意的是lookup_vector 的数值必须按升序排序：.、-2、-1、0、1、2、.、a-z、false、true；否则，函数 lookup 不能返回正确的结

42、果。文本不区分大小写。result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。 如果函数 lookup 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。如果 lookup_value 小于 lookup_vector 中的最小值，函数 lookup 返回错误值 #n/a。 示例详见图9-4-14所示：图9-4-14 lookup函数的应用（2）数组形式函数lookup的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。通常情况下，最

43、好使用函数 hlookup 或函数 vlookup 来替代函数 lookup 的数组形式。函数 lookup 的这种形式主要用于与其他电子表格兼容。9.4.6数据库函数数据库函数（dfunctions）主要是用于对存储在数据列表或数据库中的数据进行分析。它们具有一些共同特点：（1）每个函数均有三个参数：database、field 和 criteria。这些参数指向函数所使用的工作表区域。database为构成数据列表或数据库的单元格区域。数据库是包含一组相关数据的数据列表，其中包含相关信息的行为记录，而包含数据的列为字段。数据列表的第一行包含着每一列的标志项。field为指定函数所使用的数据

44、列。数据列表中的数据列必须在第一行具有标志项。field 可以是文本，即两端带引号的标志项，如“产量”；此外，field 也可以是代表数据列表中数据列位置的数字：1 表示第一列，2 表示第二列，等等。criteria为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。（2）除了getpivotdata函数之外，其余十二个函数都以字母d开头。（3）如果将字母d去掉，可以发现其实大多数数据库函数已经在excel的其他类型函数中出现过了。比如，daverage将d去掉的话，就是求平均值的函数average。excel包

45、含的数据库函数及其功能可以参见下表9-4-2所示：表9-4-2 数据库函数及其功能例：现在如图9-4-15所示的工作表数据及其右边建好的条件区域。要求利用数据库函数计算：（1）商标为上海，瓦数小于100的白炽灯的平均单价。（2）产品为白炽灯，其瓦数大于等于80且小于等于100的盒数。图9-4-15 数据库函数的应用操作步骤如下：步骤1：选中单元格g23，输入公式“daverage（a1：h17，e1，j2：l3）”，然后按回车键。步骤2：选中单元格g24，输入公式“dsum（a1：h17，g1，j7：l8）”，然后按回车键。注意：l 可为参数criteria指定任意区域，只要它至少包含一个列标

46、志和列标志下方用于设定条件的单元格。l 虽然条件区域可以在工作表的任意位置，但不要将条件区域置于数据列表下方。因为如果使用“数据”菜单中的“记录单”命令在数据列表中添加信息，新的信息将被添加在数据列表下方的第一行上。如果数据列表下方的行非空，excel将无法添加新的信息。l 确定条件区域没有与数据列表相重叠。l 若要对数据库的整个列进行操作，需要在条件区域中的列标志下方输入一个空白行。9.4.7文本函数1、replace函数replace函数可以使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。其语法格式为：replace (old_text, start_num, num_

47、chars, new_text)其中old_text是原始的文本数据，start_num 可以设置从原始文本的第几个字符位置开始替换，num_chars 可以设置共有多少字符要被替换，new_text是用来替换的新字符串。例：要对下图9-4-16的电话号码进行升级，方法是区号“0571”后面加上“8”，并将结果保存在“升级后号码”字段中。操作方法如下：步骤1：在g2单元格中输入“replace（b2，4，1，18）”，然后按回车键。步骤2：双击g2单元格填充柄。图9-4-16replace函数应用2、exact函数exact函数用来测试两个字符串是否完全相同。如果它们完全相同，则返回 true

48、；否则，返回 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函数这三个函数的语法格式如下：and（logical1,logical2, .）or（logical1,logical2, .n）not（logical）其中logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可能为true，可能为 false。 参数必须是逻辑值，或者包含逻辑值的数组或引用。and函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），and函数返回true，否则，只要有一