会计信息化基础-Excel高级应用 第4章 函数.ppt
第四章函数Excel的功能是强大的，尤其是Excel的数据运算功能，可以帮助用户解决繁杂的问题。不过，Excel的函数功能不仅在求和、求平均值等简单的应用上，还可以利用各种函数进行综合的数据运算与管理。本章的主要内容有：财务函数日期及时间函数数学及三角函数统计函数查找与引用函数文本函数逻辑函数信息函数函数应用4.1函数概述4.2财务函数4.3日期及时间函数4.4数学与三角函数4.5统计函数4.6查找与引用函数4.7文本函数4.8逻辑函数4.9信息函数4.10函数应用案例4.1函数概述4.1.1概述4.1.2函数嵌套4.1.3函数分类4.1.1概述函数其实就是一些设定好的公式，只需将一些参数交给函数，函数就会根据设定好的公式返回结果。像数字、文本、形如TRUE或FALSE的逻辑值、数组、形如“#N/A”的错误值或单元格引用都可作为参数使用。4.1.2函数嵌套了解函数的基本构成后，有一个问题就是：如果单一函数解决不了问题，是否可以同时使用多个函数来解决问题呢？也就是说，一个函数是否可以是另一个函数的参数呢？当然可以，这就是函数的嵌套。嵌套函数，就是指在某些情况下，可能需要将某函数作为另一函数的参数使用。例如“IF(COUNT(D1:D7)5,SUM(E1:E7),”noenoughdata“)”就是一个嵌套函数，表示如果（D1:D7）区域中有5个以上的数值，则汇总（E1:E7），否则显示“noenoughdata”。4.1.3函数分类Excel包含多种类型的函数，任何一类函数都可用来解决特定的问题。函数一共有九类，分别是数据库函数、日期与时间函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数以及文本函数。如图4.1所示，在“插入函数”对话框中可以选择所需要的函数类型，在常用函数中会列出一些最常用的函数。如果函数并没有出现在常用函数列表中，只要经常使用某个函数，此函数也会被Excel自动加入常用函数的列表中。4.2财务函数在Excel中，函数还可以计算如贷款的支付额、投资的未来值和净现值，以及及债券或股票的价值。这类函数称为财务函数，这些函数为财务分析提供了极大的便利，而且使用这些函数不必理解高级财务知识，只要填写参数值就可以了。4.2.1投资计算函数FV4.2.2投资年金现值函数PV4.2.3投资净现值函数NPV4.2.4利率计算函数RATE4.2.5年金函数PMT4.2.6折旧计算函数DB4.2.1投资计算函数FVFV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值；nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数；pmt为各期所应付给（或得到）的金额，其数值在整个期间（或投资期内）保持不变；pv为现值，也称为本金，就是在进行每期投资之前已经投入的额度，如果省略pv，则假设其值为零；type为数字0或1，0表示付款时间是在期末，1表示付款时间是在期初，如果省略type，则假设其值为0。4.2.2投资年金现值函数PVPV函数是用来计算某项投资的年金现值。现值就是未来各期收益折算成现在的价值总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。语法形式为：PV(rate,nper,pmt,fv,type)，其中rate为各期利率；nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数；pmt为各期所应支付的金额，其数值在整个年金期间保持不变，通常pmt包括本金和利息，但不包括其他费用及税款；fv为未来值，或在最后一次支付后想要得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）；type可以指定各期的付款时间是在期初还是期末。4.2.3投资净现值函数NPVNPV函数基于一系列未来不固定的现金流和固定的各期贴现率，返回一项投资的现值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。语法形式为：NPV(rate,valuel,value2,)，其中，rate为各期贴现率，是一固定值；valuel,value2,代表1-29笔支出及收入的参数值，valuel,value2,所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。需要注意的是：NPV按次序使用valuel,value2,来代表未来现金流。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略；如果参数是一个数组或引用，只有其中的数值部分计算在内，而忽略数组或引用中的空白单元格、逻辑值、文字及错误值。4.2.4利率计算函数RATERATE函数就可以计算出一组现金流的实际投资回报率。函数RATE是通过迭代法计算得出结果的，则有可能无解或有多个解。如果在进行20次迭代计算后，函数RATE的两次相邻结果没有收敛于0.0000001，则函数RATE将返回错误值“#NUM！”。语法形式为RATE(nper,pmt,pv,fv,type,guess)，其中，nper为总投资期，即该项投资的付款期总数；pmt为各期付款额，其数值在整个年金期间保持不变，通常pmt包括本金和利息，但不包括其他费用或税金，如果忽略了pmt，则必须包含fv参数；pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金；fv为未来值，或在最后一次付款后想要得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）；type数字0或者1，用以指定各期的付款时间是在期末还是期初。guess为预期利率（估计值），如果省略预期利率，则假设该值为10%。如果函数RATE不收敛，请改变guess的值。通常当guess位于0和1之间时，函数RATE是收敛的。4.2.5年金函数PMTPMT函数基于固定利率及等额分期付款方式，返回贷款的每期付款额，也就是平时所说的“分期付款”。比如借购房贷款或其他贷款时，PMT可以计算每期的偿还额。其语法形式为：PMT(rate,nper,pv,fv,type)，其中rate为各期贴现率，是一固定值；nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数；pv为现值，或一系列未来付款当前值的累积和，也称为本金；fv为未来值，或在最后一次付款后想要得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）；type数字0或者1，用以指定各期的付款时间是在期末还是期初。如果省略type，则假设其值为零。4.2.6折旧计算函数DBDB（固定余额递减法）的语法形式为：DB（cost,salvage,life,period,month），其中，cost为资产原值，也就是购买资产所付出的价钱；salvage表示资产在折旧期末的价值（也称为资产残值）；life为折旧期限（有时也称作资产的使用寿命）；period为需要计算折旧值的期数，而且period必须使用与life相当的单位；month为第一年的月份数，如省略，则假设为12。4.3日期及时间函数取出当前系统时间取出当前系统时间/日期信息日期信息取出当前系统时间/日期信息的函数主要有NOW和TODAY。其语法形式均为：函数名()。NOW可以提取系统的当前日期以及时间，而TODAY仅仅提取当前系统的日期。取得日期取得日期/时间的部分字段值时间的部分字段值如图4.8所示，例如，如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接取出需要的日期/时间数据。4.4数学与三角函数4.4.1求和函数SUM4.4.2条件求和函数SUMIF4.4.3四舍五入函数ROUND4.4.4取整函数TRUNC4.4.1求和函数SUMSUM函数是Excel中使用最频繁的函数，利用该函数可以返回某一单元格区域中所有数字之和。其语法形式为：SUM(number1,number2,.)，其中，number1，number2，.为1到30个需要求和的参数。直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算；如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果参数为错误值或为不能转换成数字的文本，将会导致错误。对于其他的汇总函数，如SUBTOTAL、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2请参考帮助文档。4.4.2条件求和函数SUMIFSUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。其语法形式为：SUMIF（range,criteria,sum_range），其中，range用于条件判断的单元格区域；criteria确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32”、“32”；sum_range是需要求和的实际区域。4.4.3四舍五入函数ROUNDROUND函数的功能就是根据指定的位数，将数值四舍五入。其语法形式为：ROUND（number,num_digits），其中number就是将要进行四舍五入的数值；num_digits则是想要得到的数字的小数点位数。4.4.4取整函数TRUNCTRUNC的作用是将数字的小数部分截去，返回整数。其语法形式为：TRUNC(number,num_digits)，其中number表示需要截尾取整的数值；num_digits用于指定取整精度，如果省略num_digits，其默认值为0。4.5统计函数4.5.1平均值函数AVERAGE4.5.2统计个数的函数COUNT4.5.3条件统计函数COUNTIF4.5.4最大值函数MAX与最小值函数MIN4.5.5第k个最大值函数LARGE与最小值函数SMALL4.5.6众数函数MODE4.5.7排位函数RANK4.5.1平均值函数AVERAGEAVERAGE函数可以计算一组函数值的平均值。其语法形式为：AVERAGE(number1,number2,)，其中，number1，number2，为要计算平均值的130个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中文字、逻辑值或空单元格，则忽略其值，但是，如果单元格包含零值则计算在内。4.5.2统计个数的函数COUNT函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去，但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字，数组中或引用的空单元格、逻辑值、文字或错误值都将被忽略。那么如果需要统计逻辑值、文字或错误值怎么办呢？还记得AVERAGE和AVERAGEA的区别么？没错，如果在统计中需要将这类数值计算在内，就可以使用COUNTA函数。其语法形式为COUNT(value1,value2,)，其中，value1，value2，为包含或引用各种类型的130个参数，但只有数字类型的数据才被计数。COUNTA的语法形式与COUNT的基本相同，只是在进行统计时将文本也包含在内。4.5.3条件统计函数COUNTIFCOUNTIF的作用是计算区域中满足给定条件的单元格的个数。其语法格式为：COUNTIF（range,criteria），其中，range为需要计算其中满足条件的单元格数目的单元格区域；criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本，例如，条件可以表示为43、“43”、“43”或“oranges”。4.5.4最大值函数MAX与最小值函数MIN如果需要提取一组数值中的最大值和最小值，可以使用MAX和MIN函数。其语法形式为：函数（number1,number2,），其中number1，number2，为需要找出最大数值的130个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。如果不想逻辑值和文本被忽略，请使用MAXA或者MINA来代替。4.5.5第k个最大值函数LARGE与最小值函数SMALL使用LARGE和SMALL来提取数组中第k个最大或最小值了。语法形式为：函数（array,k），其中，array为需要找到第k个最小值的数组或数字型数据区域；k为返回的数据在数组或数据区域里的位置（如果是LARGE函数则从大到小排，若为SMALL函数则从小到大排）。如果k=1则等同与使用MAX和MIN。4.5.6众数函数MODE函数MODE可以实现提取某一数组或数据区域中出现频率最多的数值。其语法形式为：MODE(number1,number2,)，其中，number1，number2，是用于众数计算的130个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。4.5.7排位函数RANK如果需要知道某个数值在一组数值中的排位，可以使用函数RANK。数值的排位是根据数据清单中其他数值的相对大小而得出的。数据清单的排序可以使用Excel提供的排序函数完成。其语法形式为RANK(number,ref,order)，其中，number为需要找到排位的数字；ref为包含一组数字的数组或引用；order用来指明排位的方式，如果order为0或省略，则Excel将ref当作按降序排列的数据清单进行排位，如果order不为零，Excel将ref当作按升序排列的数据清单进行排位。RANK函数对重复数的排位相同，但重复数的存在将影响后续数值的排位。4.6查找与引用函数4.6.1概述4.6.2LOOKUP函数4.6.3列查找函数VLOOKUP4.6.4行查找函数HLOOKUP4.6.1概述LOOKUP、HLOOKUP和VLOOKUP这三个函数的作用都是在表格或数值数组中查找指定的数值，并由此返回表格或数组中指定行或列的数值。函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似。不同之处在于函数HLOOKUP在区域的第一行查找，函数VLOOKUP在第一列查找，而函数LOOKUP则按照数组的维数查找。4.6.2LOOKUP函数LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行的数值。其基本语法形式为：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不能返回正确的结果。文本不区分大小写；result_vector只包含一行或一列的区域，其大小必须与lookup_vector相同。如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值“#N/A”。4.6.3列查找函数VLOOKUP它可在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP中的“”代表列。其语法形式为：VLOOKUP(lookup_value,table_array,col_index_num,range-lookup)，其中，lookup_value为需要在数组第一列中查找的数值，lookup_value可以为数值、引用或文本字符串；table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，例如数据库或列表；col_index_num为table_array中待返回的匹配值的列序号，col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值“#VALUE!”；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值“#REF!”。range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值“#N/A”。4.6.4行查找函数HLOOKUPHLOOKUP函数主要用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。其基本语法与VLOOKUP非常相近，格式为：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)，其中，lookup_value为需要在数组第一行中查找的数值，lookup_value可以为数值、引用或文本字符串；table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，table_array的第一行的数值可以为文本、数字或逻辑值；row_index_num为table_array中待返回的匹配值的行序号，row_index_num为1时，返回table_array第一行中的数值；row_index_num为2，返回table_array第二行中的数值，以此类推。如果row_index_num小于1，函数HLOOKUP返回错误值“#VALUE!”；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值“#REF!”。range_lookup为一逻辑值，指明函数HLOOKUP返回时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值，4.7文本函数4.7.1大小写转换函数LOWER、UPPER和PROPER4.7.2字符串提取函数LEFT、RIGHT和MID4.7.3字符串比较函数EXACT4.7.4字符串合并函数CONCATENATE4.7.1大小写转换函数LOWER、UPPER和PROPERExcel中有三个函数，专门负责转换大小写。其中LOWER函数负责将一个文字串中的所有大写字母转换为小写字母；UPPER函数负责将文本转换成大写形式；而PROPER则将文字串的首字母及任何非字母字符之后的首字母转换成大写，将其他的字母转换成小写。这三种函数的基本语法形式均为：函数名(text)。4.7.2字符串提取函数LEFT、RIGHT和MID使用MID、LEFT、RIGHT等函数可从长字符串内获取一部分字符。它们具体的语法格式介绍如下。LEFT函数：LEFT(text,num_chars)，其中，text是包含要提取字符或汉字的文本串；num_chars指定要从左边开始提取的字符或汉字的个数。RIGHT函数：RIGHT(text,num_chars)，其中，text是包含要提取字符的文本串；num_chars指定要从右边开始提取的字符或汉字的个数。MID函数：MID(text,start_number,num_chars)，其中，text是包含要提取字符的文本串；start_num表示从文本的左边开始计算，要提取的第一个字符的位置；num_chars表示从开始位置要提取的字符或汉字的个数。4.7.3字符串比较函数EXACTEXACT函数用来比较两个字符串是否相同。如果它们完全相同，则返回TRUE；否则，返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。利用函数EXACT可以测试输入文档内的文字。还可以与逻辑函数连用，作为其中的条件。其语法形式为：EXACT(text1,text2)，其中，text1为待比较的第一个字符串；text2为待比较的第二个字符串。4.7.4字符串合并函数CONCATENATECONCATENATE函数的主要作用就是将几个文本字符串合并为一个文本字符串。其语法格式为：CONCATENATE(text1,text2,)，其中，text1，text2，为130个将要合成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。4.8逻辑函数逻辑函数的主要功能就是判断真假值，然后根据真假值返回相应的参数。这类进行复合检验，并根据检验结果返回数据的Excel函数，称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。其中以IF函数的应用最为广泛，AND和OR主要为IF提供支持，而NOT、FALSE、TRUE由于功能单一，用得较少。4.8.1逻辑判断函数IF4.8.2复合条件函数AND和OR4.8.3IF、AND和OR函数综合实例4.8.1逻辑判断函数IFIF函数主要用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此IF函数也称为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。其语法格式为：IF(logical_test,value_if_true,value_if_false)，其中，logical_test表示计算结果为TRUE或FALSE的任意值或表达式，本参数可使用任意比较运算符；value_if_true表示在logical_test为TRUE时返回的值，value_if_true也可以是其他公式；value_if_false表示在logical_test为FALSE时返回的值，value_if_false也可以是其他公式。4.8.2复合条件函数AND和OR这两个函数都用来返回参数逻辑值，也就是主要用于嵌套在IF函数的第一个参数中，进行多个条件的判断。而函数OR与AND函数的区别在于：AND函数要求所有函数逻辑值均为真，结果方为真；而OR函数仅需其中任何一个为真即可为真。也就是说当OR函数的参数中任何一个满足条件即可，参数逻辑值即返回为TRUE，只有当所有条件均不满足时，才会返回FALSE。这两个函数的语法为：函数(logical1,logical2,)，其中logical1,logical2,表示待检测的130个条件值，各条件值可能为TRUE，可能为FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用。4.8.3IF、AND和OR函数综合实例4.9信息函数在Excel函数中有一类函数，它们专门用来返回某些指定单元格或区域的信息，比如单元格的内容、格式、个数等，这类函数称之为信息函数。在实际应用中，通常是与逻辑函数IF等配合使用来达到对单元格信息的确定。4.9.1返回单元格信息函数CELL4.9.2错误类型返回函数ERROR.TYPE4.9.3返回当前环境信息的函数INFO4.9.4检验引用类型函数IS类函数4.9.5奇偶校验函数ISEVEN与ISODD4.9.6数值类型测定函数TYPE4.9.1返回单元格信息函数CELLCELL函数用于返回某一引用区域的左上角单元格的格式、位置或内容等信息。其语法形式为：CELL(info_type,reference)，其中，info_type为一个文本值，指定所需要的单元格信息的类型；reference则表示要获取其有关信息的单元格，如果忽略，则在info_type中所指定的信息将返回给最后更改的单元格。reference表示要获取其有关信息的单元格。如果忽略，则在info_type中所指定的信息将返回给最后更改的单元格。如果CELL公式中的info_type参数为format，而且以后又用自定义格式格式化单元格，则必须重新计算工作表，以更新CELL公式。4.9.2错误类型返回函数ERROR.TYPEERROR.TYPE返回对应于Excel中某一错误值的数字，如果没有错误则返回“#N/A”。语法形式为ERROR.TYPE(error.val)，其中error.val为需要得到其标号的一个错误值。尽管error.val可以为实际的错误值，但它通常为一个单元格引用，而此单元格中包含需要检测的公式。4.9.3返回当前环境信息的函数INFO4.9.4检验引用类型函数IS类函数4.9.5奇偶校验函数ISEVEN与ISODDISEVEN与ISODD为检验参数奇偶性的函数。其中ISEVEN是当参数number为偶数时返回TRUE，否则返回FALSE。而ISODD则恰恰相反，如果参数number为奇数，返回TRUE，否则返回FALSE。4.9.6数值类型测定函数TYPE函数TYPE可用为返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数TYPE。其语法形式为：TYPE(value)，其中value可以为任意Excel数值，如数字、文本以及逻辑值等等。根据value的类型不同，TYPE函数返回不同的数值。4.10函数应用案例4.10.1奖金计算表4.10.2简历4.10.3万年历4.10.1奖金计算表某公司规定：一个月奖金基数为300元，病假1天扣15元，事假一天扣30元，旷工一天扣60元，扣完为止。使用这个奖金计算表时，只要将员工的出勤情况记录在表中，该员工的奖金将自动计算出来，兼有考勤和计算奖金两种功能。自动统计表做好以后还可以保存成模板，以便以后使用。本案例所涉及到的Excel函数有：COUNTIF(Range,Criteria)MONTH(serial_number)TODAY()4.10.2简历朋友在做人事工作，常常要打印指定员工的简历表，由于“员工基本情况登记表”是用Excel制作的，可以用Excel来自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表，十分方便。本文涉及的Excel函数如下：IF(Logical,Value_if_true,Value_if_false)ISERROR(value)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)4.10.3万年历这个万年历可以显示当月的月历，还可以随意查阅任何日期所属的月历。还可让它在特殊的日子里显示不同的提醒文字。所涉及到的函数有：AND(logical1,logical2,.)DATE(year,month,day)DAY(serial_number)IF(Logical,Value_if_true,Value_if_false)INT(number)MONTH(serial_number)NOW()OR(logical1,logical2,.)
