Excel的使用第5章.ppt

第5章 函数,本章学习目标,1、了解函数极其调用方法 2、掌握常用工作表函数的用法 3、掌握常见统计函数的用法 4、掌握条件函数的用法，特别是： Ifsumifcountif等 5、掌握解查找引用类函数的用法 6、掌握常用财务函数的用法,5.1 函数简介,1、函数的概念 函数是能够完成特定功能的程序。在Excel中，它是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算,然后把计算的结果存放在某个单元格中。 在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组或工作表的信息,5.1 函数简介,2、Excel函数分类,5.1 函数简介,3、函数调用 函数的语法 函数名(参数1, 参数2, 参数3,) 在公式中调用函数,5.1 函数简介,4、函数输入 在单元格的公式中直接输入函数调用 函数调用向导 5、Excel的帮助系统,5.2 常用工作表函数,1、案例 某班学生成绩表如图所示，统计出其中的平均分、总分、参考人数、等级及缺考人数等数据。,该工作表要用到：if,average, sum,count, countif，max,min 等函数,5.2 常用工作表函数,2、条件函数IF 用法 IF(条件, 表达式1, 表达式2)。 功能 当条件成立时，计算出表达式1的值；当条件不成立时，计算出表达式2的值 事例 If(A160,“及格了”,“不及格”)，若A1单元格的值是76，则该函数的结果是：及格了；若A1单元格的值是50，则该函数的结果是:不及格。 IF函数的嵌套调用,5.2 常用工作表函数,3、汇总求和函数 自动求和按钮,图示工作表中的所有汇总数据可用自动和按钮计算出来！,5.2 常用工作表函数,4、SUM函数 用法 SUM(x1, x2,x30) x1，x2，x30是需要求和的参数 ，可以是数据或单元格区域 功能 计算各参数的数值之和 说明： 参数表中的数字、逻辑值及数字的文本表达式将被计算。例如，SUM(3,2)=5，SUM(“9“,20,true)=30。因为文本值被转换成数字，而逻辑值“true”被转换成数字1。,5.2 常用工作表函数, 如果参数为数组或引用，那么只有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 例如，设A1的值为“9”，A2为true，则公式SUM(A1,A2,20)的计算结果为20，而不是30。因为本公式中包括两个引用A1、A2，而A1的值为文本，A2的值为逻辑值，它们在计算时被忽略，最终就只有一个数值20参与运算。 SUM函数的参数最多可达30个，不同类型的参数可以同时出现。 例如，A2:E2 包含 5、15、30、40、50、a3的值为10，则SUM(A2:C2,A3) =60，SUM(B2:E2,15) =150，SUM(A2:D2,1,2,3,4,A3,10)=110。,5.2 常用工作表函数,5、条件求和函数SUMIF 用法 SUMIF(range,criteria,sum_range) 其中，range是用于条件判断的单元格区域，criteria条件，其形式可以为数字、表达式或文本； sum_range是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略sum_range，则直接对range中的单元格求和。 功能 对range单元格区域中的数据进行Criteria条件检查，然后对满足条件行的sum_range同行进行求和。,5.2 常用工作表函数,案例 某商场的销售记录如图所示。如果统计各类商品的总销售额。 彩电的销售总额：,=SUMIF(C$3:C$13,“彩电“,F$3:F$13),5.2 常用工作表函数,6、平均值函数AVERAGE，AVERAGEA 用法 AVERAGE(n1，n2，n30) AVERAGEA(n1，n2，n30) 其中，n1，n2，n30是要计算平均值的参数，该函数最多允许有30个参数。参数可以是数字，或者是涉及数字的名称、数组或引用 功能 两函数都是求参数的平均值。AverAge不对文本和逻辑值类数据进行计算； AverageA要对文本和逻辑函数进行平均值计算：文本被视为0，逻辑值true被视为1，false被视为0，空文本（“）也作为0计算,5.2 常用工作表函数,案例 下图说明AVERAGE函数与AVERAGEA函数的区别,5.2 常用工作表函数,7、计数函数COUNT、COUNTA、COUNTBLANK、COUNTIF 用法 COUNT(v1, v2, ) COUNTA(v1, v2, ) COUNTBLANK (range) COUNTIF (range, criteria) 功能 Count 统计数字的个数 CountA统计数字和文本的个数 CountBlank统计空白单元格的个数 Countif统计满足条件Criteria的单元格个数,5.2 常用工作表函数,8、求最大值、最小值函数MAX，MIN 用法 MAX(number1,number2,.) MIN(number1,number2,.) 其中 number1, number2, . 是要从中找出最大值或最小值的 1 到 30 个数字参数，也可以是单元格或单元格区域的引用。 功能 MAX函数返回一组值中的最大值，MIN函数返回一组值中的最小值,5.2 常用工作表函数,本节案例问题的解决,5.3 逻辑函数,1比较运算 比较运算就是人们常说的比较式，又称关系运算。比较运算只有两种不同的结果，要么“正确”，要么“错误”，不可能有第三种结果。,5.3 逻辑函数,2逻辑运算 AND(l1, l2, ) OR(l1, l2, ) NOT(logical) TRUE( ) FALSE( ) 其中， l1, l2是关系式或逻辑值。,5.3 逻辑函数,案例 某单位有3000名职工，要按其工资缴纳个人收入所得税，税率与工资的关系如表所示。 该单位的职工表如下所示，用IF函数求出各职工的税率！,5.3 逻辑函数,在E4中输入编辑栏中的公式！,5.4 数学和三角函数,Excel提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。 常见的数学函数 MOD 、TRUNC 、ABS 、SQRT 、SIN 、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x, n)、LN(n)、FACT(n)、LOG(n, base)、MINVERSE(array)、MMULT(array1, array2) ,5.5日期及时间函数,1、Excel处理日期的方式 Excel将日期存储为一系列连续的序列数，将时间存储为小数。 例如，37260.73表示2002-1-4 5:36 PM，在这个数中小数点前面的数字37260表示日期2002-1-4，小数点后的数字73表示时间，即下午5点36分。 日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。,5.5日期及时间函数,1DATE函数 用法 DATE(year, month, day) 功能 DATE函数利用所给的参数，构造一个日期序列数 例如 DATE(2005,3,21)的结果是2005-3-21,5.5日期及时间函数,2YEAR，MONTH，DAY函数 用法 YEAR(serial_number) MONTH(serial_number) DAY(serial_number) 其中的serial_number是一个日期或数字。 功能 YEAR函数返回某日期的年份。 MONTH函数返回以系列数表示的日期中的月份。 DAY函数返回以系列数表示的某日期的天数，用整数131表示。,5.5日期及时间函数,3TODAY、NOW函数 用法 TODAY( ) NOW( ) 功能 TODAY函数返回系统的当前日期。 NOW函数计算当前日期和时间。,5.5日期及时间函数,5WEEKDAY 用法 WEEKDAY(serial_number, return_type) 其中： serial_number代表要查找的日期，或日期的系列数，以了解该日期为星期几；return_type确定返回值类型的数字，。 功能 WEEKDAY计算给定的日期是星期几,5.5日期及时间函数,NETWORKDAYS函数 用法 NETWORKDAYS(start_date, end_date, holidays) 其中 start_date表示代表开始日期，end_date为终止日期， holidays表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。,5.5日期及时间函数,案例 某建筑公司按天数计算每个民工的报酬，民工每个星期工作5天，工作时间和请假时间如图的AH列所示，I列是每天的报酬，用日期函数计算每个民工的工作天数和总工资。,5.6 查找及引用函数,概述 查找引用函数能通过地址、行、列对工作表的单元格进行访问，也可通过这些函数从单元格的地址中求出其所在的行或列，进而查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。,5.6 查找及引用函数,1ADDRESS 函数 用法 ADDRESS(row, col, abs_num, a1, sheet_text) 其中 row_num是引用中使用的行号；column_num是引用中使用的列标；abs_num指明返回的单元格引用类型 sheet_text为一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。 功能 用指定的行号和列标，建立文本类型的单元格地址 例如： ADDRESS(6,3) = “$C$6” ADDRESS(6,1,2) =A$6 DDRESS(2,3,1,false,“book1 sheet1“)=book1sheet1!r2c3,5.6 查找及引用函数,2choose函数 用法 CHOOSE(N, V1, V2, .) 功能 利用索引N从参数清单V1, V2, .中选择需要的数值 。 例如： CHOOSE(5,“一月”,“二月”,“三月”,“四月”,“五月”,“六月”)=“五月”； SUM(A1:CHOOSE(3,A10,A20,A30)=SUM(A1:A30) CHOOSE(B5, “nails“,“screws“,“nuts“,“bolts“)=“nuts“,5.6 查找及引用函数,4LOOKUP函数 用法 LOOKUP(value, r1, r2) 其中 value是要查找的数值，可以为数字、文本、逻辑值或包含数值的名称或引用。 r1为只包含一行或一列的区域，其值可以为文本、数字或逻辑值。 r2为只包含一行或一列的区域，其大小必须与r1相同 功能 该函数在r1所在的行或列中查找值为value的单元格，然后返回r2中与r1同行或同列的单元格中的值。,5.6 查找及引用函数,5、HLOOKUP函数 用法 HLOOKUP(value, table, n, range_lookup) 说明 value为需要在数据表第一行中进行的查找的数值，它可以是数值、引用或文字串。 table是需要在其中查找数据的数据表、可以为对区域或区域名称的引用。它的第一行的数值可以为文本、数字或逻辑值。 如果range_lookup为1，则进行近似值查找，且table第一行的数值必须按升序排列；如果 range_lookup为0，则进行精确查找，且table不必进行排序。 功能 在数据清单（或区域）table的第一行中查找值为value的单元格，如果找到，则返回该列第n行单元格的值。,5.6 查找及引用函数,案例,5.6 查找及引用函数,6MATCH函数 用法 MATCH(value, array, type) 其中 value是在array中查找的数值。 array是包含要查找数据的区域，数组 type为数字-1、0或1 功能 在指定方式下与指定数值匹配的数组中元素的相应位置,5.6 查找及引用函数,6、INDEX函数 用法 INDEX(array,row_num,column_num) 其中 array 是单元格区域或数组常量。row_num 是array中某行的行号，column_num是array中某列的列号。 功能 index函数返回array中row_num和column_num交叉处的单元格引用。,5.6 查找及引用函数,小结 数据查找是Excel办公应用中最常见、最重要的问题，方法不当，本来几分种就能解决的问题，可能要弄几小时，甚至几天。 总的说来，在工作中应用最广、作用最大的查找函数是VLOOKUP。 在查找数据的案例集合.xls工作薄中包括了我几年以来从实际办公应用中收集到的数据查找案例。,5.7 财务函数,Excel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。,5.7 财务函数,1、折旧函数 Excel折旧函数有DB、DDB、SLN、YDB及VDB。运用这5个折旧函数可以确定指定时期内资产的折旧值。 这5个折旧函数有4个参数是共有的，如表所示。,5.7 财务函数,DB函数 用法 DB(cost, salvage, lIFe, period, month) 其中：lIFe和period须使用相同的时间单位；month为第一年的月份数，如果省略，则默认为12。 功能 DB函数使用固定余额递减法，计算一笔资产在给定期限的折旧值 例如 某学校购买了一批计算机，价值为500 000元使用期限为3年，报废价值为100 000元。每年的折旧公式及结果值如下所示： DB(500000,100000,3,1,6) =￥103 750.00 DB(500000,100000,3,2,6) =￥164 443.75 DB(500000,100000,3,3,6) =￥96 199.59,5.7 财务函数,DDB函数 用法：DDB(cost, salvage, lIFe, period, factor) 其中，factor为余额递减速率。如factor省略，则默认为2（双倍余额递减法）。5个参数都必须为正数 功能 DDB函数使用双倍余额递减法或其他指定方法，计算一笔资产在给定期限内的折旧值 例如： 学校花10万元购买了一台新设备，使用期限为10年，报废价值为1万。下面的例子给出几个期限内的折旧值（结果保留两位小数）。 DDB(100000,10000,3650,1) =￥54.79，第一天的折旧值。factor被Excel默认设置设为2。 DDB(100000,10000,120,1) =￥1 666.67，第一个月折旧值。 DDB(100000,10000,10,1) =￥20 000.00，第一年的折旧值。 DDB(100000,10000,10,3,1.5) =￥10 837.50，第三年的折旧。 这里没有使用双倍余额递减法，factor=1.5。,5.7 财务函数,SYD、SLN、VDB函数 用法 SYD(cost, salvage,life, per) SLN(cost, salvage, life) VDB(cost, salvage, lIFe, start_period, end_period, factor, no_switch) 功能 SYD函数计算某项资产按年限总和折旧法计算的某期限内的折旧值 SLN函数计算一项资产每期的直线折旧值 VDB函数代表可变余额递减法，可使用双倍递减余额法或其他指定的方法，计算指定期间内或某一时间段内的资产折旧额。,5.7 财务函数,2、 投资函数 投资函数的参数 Excel投资分析方面的函数，这些函数使用的参数大致相同，意义相近 ，如下表所示。,5.7 财务函数,PMT函数 用法 PMT(rate, nper, pv, fv, type) 说明，PMT返回的支付款项包括本金和利息，但不包括税款。rate和nper单位要一致 。 功能 PMT基于固定利率及等额分期付款方式，计算投资或贷款的每期付款额 。 例如：某人买房了，贷款200 000元，利率为7%，分10个月付清，则他的月支付额为： PMT(7%/12, 10, 200000) =￥-20 647.26,5.7 财务函数,PV函数 用法 PV(rate, nper, PMT, fv, type) 功能 PV函数可以计算投资的现值。现值是一系列未来付款当前值的累积和，如借入方的借入款即为贷出方贷款的现值。 例如： 某人想买一笔养老保险，该保险可以在今后25年内于每月末回报500元。该保险的购买成本为75 000，假定投资回报率为6.7%。该保险是否合算？ 现在可以通过函数PV计算一下这笔投资是否值得。该项投资的年金现值为： PV(0.067/12,12*25,500,0)=-72 700.05,5.7 财务函数,FV函数 用法 FV(rate, nper, PMT, pv, type) 功能 FV函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。 例如 某人为保证退休后的生活，打算每年年初存入3 000元，在整个投资期间，平均投资回报率为7%。此人今年30岁，到他60岁时，有多少存款？计算方法如下。 FV(7%, 30,-2000,1)=￥202 146.08,5.7 财务函数,XNPV函数 用法 XNPV(rate, values, dates) 功能 XNPV函数计算一组现金流的净现值，这些现金流不一定定期发生 例如 假定某项投资需要在2002年3月2日支付现金30 000元，并于下述时间获取以下金额的返回资金：2002年4月21日返回8 750元；2002年6月10日返回7 250元；2002年7月30日返回16 250元；2002年9月18日返回9 750元。假设资金流转折扣为7%，则净现值为。 XNPV(0.07,-30000,8750,7250,16250,9750, 37317,37667,38017,38367,38717) =￥5 465.45,5.7 财务函数,其它投资函数 NPV(rate, value1, value2, .) IPMT(rate, per, nper, pv, fv, type) PPMT(rate, per, nper, pv, fv, type) NPER(rate, PMT, pv, fv, type),5.7 财务函数,3、计算偿还率的函数 RATE函数 用法 RATE(nper, PMT, pv, fv, type, guess) 功能 RATE函数用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率 例如 某人修房贷款100 000元的6年期贷款，月支付额为2 200元，该笔贷款的利率为。 =RATE(12*6, -2200, 100000)=1.38%,5.7 财务函数,IRR函数 用法 IRR(values, guess) 功能 IRR函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率 例如 某人投资70 000元开设一家录像带租借店，并预期今后5年的净收益为：12 000元、15 000元、18 000元、21 000元和26 000元。试计