计算机等级考试excel_考点函数.doc

第四章 Excel考点函数 （ 补充 ）最新考点函数列表：（共19个）1、常用函数SUM 求和SUMIF 对满足条件的单元格求和AVERAGE 求平均值MAX 求最大值MIN 求最小值COUNT 统计区域内的定义的数值的单元格个数COUNTIF 统计区域内满足条件的单元格个数COUNTA统计区域内非空的单元格个数RANK 排定名次的函数2、日期函数TODAY 返回日期格式的当前日期YEAR 返回某日期的年份NOW() 返回当前日期和时间3、财务函数PMT固定利率下，投资或贷款等额的分期偿还额FV 求按每期固定利率及期满的本息总和PV每期固定利率及期满的投资或贷款当前值的累积和4、其他函数IF 条件判断函数AND 逻辑“与”函数OR 逻辑“或”函数VLOOKUP 查找函数LEFT 取字符串左边若干个字符 RIGHT 取字符串右边若干个字符 考点函数语法详解： 一、 常用函数 ( 9个) 求和函数： SUM语法：SUM ( X1 , X2 , )参数 : 可以为 数值、单元格地址或函数。功能：返回所有参数数值之和。例1 ： SUM( 2，3，4 ) 结果为9 。例2 ：如果 A1：A5的数值分别为 10、7、9、27 和 2，那么：SUM ( A1:A5 ) 等于 55SUM ( A1:A5 , 5 ) 等于 60 条件求和函数： SUMIF格式：SUMIF（应用条件区域，条件，需要求和区域）功能：在求和区域中累加满足条件的单元格的数值。 例：如下工资表 A B C D E F 部门号姓名性别基本工资补贴扣除01李华文女500150020001林宋权男400105035002高玉成男450140015002陈 青女600265045002李 忠男4001800550 = SUMIF ( C2 : C6 , “男” , D2 : D6 ) = SUMIF ( A2 : A6 , “ 01 ” , F2 : F6 ) 求平均值函数： AVERAGE语法：AVERAGE( X1 , X2 , )参数 可以为 数值 或 单元格区域地址。 功能：返回所有参数的平均值 即（算术平均）。说明 如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 示例 如果 A1：A5的数值分别为 10、7、9、27 和 2，那么：AVERAGE(A1:A5) 等于 11AVERAGE(A1:A5, 5) 等于 10AVERAGE(A1:A5) 等于 SUM(A1:A5) / COUNT(A1:A5) 最大值函数： MAX语法：MAX( X1 , X2 , ) 功能：返回所有参数中的最大值。 示例如果 A1:A5 包含数字 10、7、9、27 和 2，则：= MAX(A1:A5) 等于 27A1销售额212/8/903419522.246true7#DIV/0!= MAX(A1:A5,30) 等于 30 最小值函数： MIN语法：MIN( X1 , X2 , )功能：返回所有参数的最小值。 统计数值型数据个数函数： COUNT语法：COUNT ( value1 , value2 , ) 功能：返回参数中数值型数据的个数; 参数为区域地址时，返回值是含有数值型数据的单元格个数。例如：在上面的工作表中， COUNT (A1:A5) 等于 3 COUNT (A1:A7) 还是等于 3， 为什么？ 条件计数函数： COUNT IF 格式：COUNTIF（计数区域，条件）功能：计算指定区域中满足给定的单元格个数。可以为数字、表达式或文本，还可为通配符？*。例如，可以表示为 32、32、32、app。 示例:1、 假设 A3:A6 中的内容分别为： app、orpnges、peaches、app 。 则：COUNTIF(A3:A6,app) 等于 22、 假设 A3:A6 中的内容分别为： app、orpnges、peaches、app 。 则：COUNTIF(A3:A6, ?p* ) 等于 33、假设 B3:B6 中的内容分别为 32、54、75、86 。 则：COUNTIF(B3:B6,55) 等于 2 统计非空白单元格个数函数： COUNTA 语法：COUNTA ( value1 , value2 , ) 功能： 返回参数中非空白单元格个数。A1销售额212/8/903419522.246true7#DIV/0!说明：参数值可以是任何类型，它们可以包括空字符 ( )，但不包括空白单元格。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。例如：在右面的工作表中，COUNTA(A1:A7) 等于 6COUNTA(A4:A7) 等于 4COUNTA(A1:A7, 2) 等于 7COUNTA(A1:A7, Two) 等于 7 排位函数： RANK语法： RANK ( , , ) 功能： 返回要排位数所排的位数。 为需要找到排位的数字（或单元格）。 为数字列表数组或对数字列表的引用。 中的非数值型参数将被忽略。 为一数字，指明排位的方式。 如果 为 0（零）或省略，则按降序排列。 如果 不为零，则按升序排列。 说明 函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。示例 123456A数据73.53.512公式说明（结果）=RANK( A3 , A2:A6 , 1 )3.5 在上表中的排位 (3)=RANK( A2 , A2:A6 , 1 )7 在上表中的排位 (5)二、 日期和时间函数 ( 3个) 当前日期函数： TODAY（）格式：TODAY（）功能：返回计算机系统的当前日期。 年函数： YEAR格式：YEAR（“日期”）功能：求出给定“日期”的年份。示例： YEAR(2006/04/24) 等于 2006 YEAR(1998/05/11) 等于 1998 YEAR(11-may-1998) 等于 1998 当前日期和时间函数： NOW（）格式：NOW（）功能：返回计算机系统的当前日期和当前时间。注意：设置单元格显示格式方法：选定单元格，按右键弹出快捷菜单，选“设置单元格格式”选项，按提示进行选择。三、 财务函数 偿还（支付）函数： PMT格式: PMT( 利率 , 期数 , 总额 )功能：返回按固定利率及等额分期支付方式和总额，返回投资或贷款的每期付款额。( PMT为PayMenT的缩写 )说明：应确认所指定的 利率 和 总期数 单位的一致性。例如，同样是四年期年利率为 12% 的贷款。如果按月支付， 利率 应为 12%12， 期数应为 412；如果按年支付， 利率 应为 12%， 期数为 4 。 示例 下面的公式将返回需要 10 个月付清的年利率为 8% 的 10,000 贷款的月支付额： PMT( 8 %12, 10, 10000) 等于 1,037.03如果希望对方在 5 个月内还清，下列公式将返回每月所得款数： PMT(8%12, 5, 10000) 等于 2,504.56 求本息总和的函数： FV功能：基于固定利率及等额分期付款方式，返回某项投资的本息总和。 格式: FV( rate , nper , pmt , pv , type )Rate 为各期利率。Nper 为总投资期，即该项投资的付款期总数。Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。Pv 为现值，即从该项投资开始计算时已经入帐的款项，也称为本金。如果省略 PV，则假设其值为零。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。Type 值支付时间0期末1期初说明 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。 示例 1如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法1. 创建空白工作簿或工作表。 2. 请在“帮助”主题中选取示例。不要选取行或列标题。 从帮助中选取示例。3. 按 Ctrl+C。 4. 在工作表中，选中单元格 A1，再按 Ctrl+V。 5. 若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。 123456AB数据说明6%年利率10付款期总数-200各期应付金额-500现值1各期的支付时间在期初（请参见上面的信息）公式说明（结果）=FV(A2/12, A3, A4, A5, A6)在上述条件下投资的未来值 (2581.40)注释 年利率应除以 12，因为它是按月计复利而得的。示例 2如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法1. 创建空白工作簿或工作表。 2. 请在“帮助”主题中选取示例。不要选取行或列标题。 从帮助中选取示例。3. 按 Ctrl+C。 4. 在工作表中，选中单元格 A1，再按 Ctrl+V。 5. 若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。 1234AB数据说明12%年利率12付款期总数-1000各期应付金额公式说明（结果）=FV(A2/12, A3, A4)在上述条件下投资的未来值 (12,682.50)注释 年利率应除以 12，因为它是按月计复利而得的。示例 3如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法1. 创建空白工作簿或工作表。 2. 请在“帮助”主题中选取示例。不要选取行或列标题。 从帮助中选取示例。3. 按 Ctrl+C。 4. 在工作表中，选中单元格 A1，再按 Ctrl+V。 5. 若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。 12345AB数据说明11%年利率35付款期总数-2000各期应付金额1各期的支付时间在期初（请参见上面的信息）公式说明（结果）=FV(A2/12, A3, A4, A5)在上述条件下投资的未来值 (82,846.25)注释 年利率应除以 12，因为它是按月计复利而得的。示例 4如果您将示例复制到空白工作表中，可能会更易于理解该示例。操作方法1. 创建空白工作簿或工作表。 2. 请在“帮助”主题中选取示例。不要选取行或列标题。 从帮助中选取示例。3. 按 Ctrl+C。 4. 在工作表中，选中单元格 A1，再按 Ctrl+V。 5. 若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。 123456AB数据说明6%年利率12付款期总数-100各期应付金额-1000现值1各期的支付时间在期初（请参见上面的信息）公式说明（结果）=FV(A2/12, A3, A4, A5, A6)在上述条件下投资的未来值 (2301.40)注释 年利率应除以 12，因为它是按月计复利而得的。 可贷款（现值）函数： PV格式: PV ( 利率 , 期数 , 每期偿还数 ) 功能：返回按固定利率及支付期数和每期偿还数，返回投资或贷款总额。 (PV为Present Value的缩写 ) 各期利率，例如，如果按 10% 的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为 10%12（即 0.83%）。可以在公式中输入 10%12、0.83% 或 0.0083 作为利率的值。 期数：即该项投资（或贷款）的付款期总数。例如，对于一笔 4 年期按月偿还的汽车贷款，共有 4*12（即 48）个偿款期次。可以在公式中输入 48 作为 期数 的值。说明： 注意：利率和期数单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，利率 应为 12%12，期数 应为 4*12；如果按年支付，利率 应为 12%，期数 为 4。 在年金函数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。例如，对于储户来说， 1000元银行存款可表示为参数 1,000，而对于银行来说该参数为 1,000。示例 假设要购买一项保险年金，该保险可以在今后二十年内于每月回报 500。此项年金的购买成本为 60,000，假定投资回报率为 8 %。现在可以通过函数 PV 计算一下这笔投资是否值得。该项年金的现值为：PV(0.08/12, 12*20, 500) 等于 - 59,777.15结果为负值，因为这是一笔付款，亦即支出现金流。年金（ 59777.15）的现值小于实际支付的（ 60000）。因此，这不是一项合算的投资。四、其他函数 ( 6个) IF 条件函数 格式：IF（ 条件X ，值1 ，值2 ）功能：当 条件X成立时，函数取 值1，否则取 值2 。例5.31： = IF（E3=1 ，“男” ，“女”） 当E3的值为1时，函数取值“男”，否则函数取值“女”。例5.32： =IF（D3=80 ，“优良” ， “其他” ） =IF（D3=80 ，“优良” ， IF（D3=60，“中”，“不及格” ） 逻辑“与”函数： AND 格式：AND（ 条件式X1 ，条件式X2 ，）功能：当所有条件式都取TRUE，函数取值TRUE，否则取FALSE。例1： AND ( 2+2=4 , 2+3=5 ) 结果为 TRUE例2： 成绩大于等于80 同时 成绩小于90 条件： 80 = C2 = 80 ，C2 1 , B4=101 ) 结果为 FALSE 逻辑“或”函数： OR 格式：OR（条件式X1 ，条件式X2，）功能：只有条件式全取FALSE时，函数取值FALSE，否则取 TRUE。例： 成绩大于等于80 或者 成绩小于90表示成 OR（ C2 = 80 ，C2 0,数字将被四舍五入到所指定的小数位数; 当n =0,数字将被四舍五入成整数; 当n 0,数字将被四舍五入到小数点左边指定的位数 如: ROUND(2.15,1)=2.2; ROUND(2.149,1)=2.1; ROUND(2.475,1)= 2.5;ROUND(21.5, 1)=20;ROUND(21.5,0) = 22 ROUND(215.8, 2) = 200 二、统计函数 频率分布统计函数： FREQUENCY 语法： FREQUENCY ( 统计数据区域 , 分段点区间 ) 功能：给定一组数据（在统计数据区域中）和一组分段值（在统计分段点区间中），FREQUENCY 函数会 给出统计数据在每个分段区间中出现的个数。频率分布统计函数使用操作步骤：1）在一空区域中（一般在一空列上），输入统计的间隔值；2）选定统计结果输出的区域； （注意输出区域中单元格个数与分段区间中单元格数是否相同）。 3）在编辑框中 输入 FREQUENCY 函数及参数； 4）按Ctrl + Shift + Enter 组合键。三、文本函数 中间截取函数： MID（X ，m ，n）功能：从字符串X的第m个字符开始截取n个字符，n不能省略。例： = MID (“嘉应学院化学系05级”, 3 , 5 ) 结果为 “学院化学系”例： = MID ( A2 , 7 , 1 ) 例： = MID ( Fluid Flow , 1 , 5 ) 等于 Fluid例： = MID ( Fluid Flow , 7 , 4 ) 等于 Flow例： = MID ( 1234 , 5 , 5 ) 等于 （空文本） 求字串长度函数： LEN（X）功能：求字符串X中包含字符的个数（也是称字符串的长度）。例： = LEN ( “abcdefg”) 结果为7 。= LEN ( “学院教育科学与技术系”) 结果为10 。( 注意：EXCEL中一个汉字占1个字符位置 )四、 数据库函数 ( 6个) 1 数据库函数的格式函数名（ 数据表区域 ，字段顺序号 ，条件区域）(1)数据表区域指整个数据表所占的区域，即字段名行和所有记录行所占的区域。(2)字段顺序号指被统计字段在数据库中的列序号，第一字段的列序号为1，第二字段的列序号为2，,以此类推。字段顺序号也可以用被统计字段的字段名所在的单元坐标（用相对坐标）或用英文双引号括住的字段名表示。(3)条件区域指字段名行和条件行所占的区域，具体规定与高级筛选的完全相同。2 常用数据库函数的功能(1) DAverage ( 数据库区域 ,字段顺序号, 条件区域 ) 返回数据库或数据清单中满足给定条件的字段中数值的平均值。 例5.26：在成绩表中求出11班学生平均分。 -（ P195 ） 解：（1） 先在一个空区域（如F1F2）中建立条件区域 ； （2） 任选一个存放结果的单元格（如F5），输入求平均分函数： = DAVERAGE（ A1D9 ，3 ，F1F2 ） (2) DSum ( 数据库区域 ,字段顺序号, 条件区域