《典型题解及函数参考》由会员分享，可在线阅读，更多相关《典型题解及函数参考（24页珍藏版）》请在金锄头文库上搜索。
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为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）
