EXCEL--函数应用2014剖析.ppt

1 EXCEL 函数运用 神龙培训中心 彭家胜Email pengjiasheng Tel 842991912016 06 目录 函数简介输入法运算符号运算顺序出错提示函数分类及常用函数功能介绍逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 2 什么是函数 函数是指一些预定义的公式 用户可以通过引入函数来进行计算 函数强化了公式的功能 使得公式的使用更加简化 函数一般有一个或者多个参数 并有返回值 这与一般意义上的函数是一致的 函数的典型结构为 函数名 参数1 参数2 3 函数的输入法 一种是直接输入以等号 开始输入函数名称输入括号和参数 每个参数之间用逗号 半角状态的逗号 隔开一种是使用 插入函数 对话框输入 4 公式运算符 算术运算符 5 6 公式运算符 算术运算符 公式运算符 连结运算符 7 使用concatenate或 符号来连结或关联一个或多个文字符串 以产生一个文字 例如 五三零 五三四零 五三零五三四零 公式运算符 引用运算符 8 运算符的优先顺序 9 若单一个公式中结合多个运算子 Excel会按照下表中显示的顺序来执行运算 如果公式里面的运算子有相同的前导引用 例如 一个公式里面同时有乘法和除法运算子 Excel会由左至右评估运算子 公式中的错误 10 目录 函数简介输入法运算符号运算顺序出错提示函数分类及常用函数功能介绍逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 11 函数的分类1 逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 12 逻辑函数 逻辑函数虽然并不太多 担应用广泛 AND OR ON分别代表的是常见的逻辑运算 求交集 并集和取反 IF函数则是EXCEL中使用最广泛的函数之一 13 FALSE和TRUE函数 功能 返回逻辑值FALSETRUE表达式 FALSE TRUE 参数 没有 14 AND函数 功能 对多个逻辑值进行交集运算 返回值是逻辑值 当所有的参数的逻辑值全为真时 返回TRUE 有任何一个参数为假时 则返回 FALSE表达式 AND logical1 logical2 参数不超过255个 15 OR函数 功能 对多个逻辑值进行并集运算 返回值是逻辑值 当所有的参数的逻辑值全为假时 返回FALSE 有任何一个参数为真时 则返回 TRUE表达式 OR logical1 logical2 参数不超过255个 16 NOT函数 功能 对参数值求反表达式 NOT logical 参数logical表示一个可以计算出TRUE或FALSE的逻辑值或逻辑表达式 17 条件函数IF功能 对逻辑测试值LOGICAL TEXT执行真假判断 条件成立时返回第2项 不成立时返回第3项 最多可以使用64个IF进行嵌套格式 IF LOGICAL TEXT VALUE IF TRUE VALUE IF FALSE 18 练习 判断闰年 闰年定义 年份被4整除而不能被100整除 或者能被400整除的年份 19 函数的分类2 逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 20 数学 三角和数组函数 用户在运用EXCEL的时候 经常要进行各种数学运算 为了提高运算的效率 EXCEL提供了数学和三角函数 用户如果能灵活地运用这些函数 将会解决许多复杂的问题 一般情况下 用户在解决具体问题时需要使用多个数学函数 21 CEILING函数 功能 对某个数值按照条件进行向上舍入计算 表达式 CEILING NUMBER SIGANIFICANCE NUMBER 需要进行舍入的数值SIGANIFICANCE 需要进行舍入的乘数 舍入的基准如 电讯收费 按6秒一个计时单位 不足6秒按一个计时单位计算 每个计时单位0 2元 CEILING MINITE A1 60 SECOND A1 6 1 22 FLOOR函数 功能 对某个数值按照条件进行向下舍入计算 表达式 FLOOR NUMBER SIGANIFICANCE NUMBER 需要进行舍入的数值SIGANIFICANCE 需要进行舍入的乘数 舍入的基准 23 取整函数INT功能 对参数NUMBER作舍后返回不超过本身的整数格式 INT NUMBER 24 截尾涵数TRUNC功能 NUM DIGITS为0 截去参数NUMBER的小数部分 返回整数部分NUM DIGITS不为0时 按指定的精度截取 格式 TRUNC NUMBER NUM DIGITS NUM DIGITS 小数位数 25 四舍五入函数ROUNDROUNDDOWNROUNDUP功能 ROUND将参数NUMBER四舍五入到由NUM DIGITS指定的小数位数ROUNDDOWN ROUNDUP分别指靠近 远离 0值 向 下 上舍入数字格式 ROUND NUMBER NUM DIGITS ROUNDDOWN NUMBER NUM DIGITS ROUNDUP NUMBER NUM DIGITS NUM DIGITS 小数位数 26 条件求和函数SUMIFSUMIFS功能 根据指定条件对若干单元格求和格式 SUMIF RANGE CRITERIA SUM RANGE 27 SUMPRODUCT函数计算数组求和功能 给定的几组数中 将数组间对应的元素相乘 并返回乘积之和格式 PRODUCT array1 array2 28 MOD函数 功能 计算两个数字相除的余数表达式 MOD NUMBER DIVISOR 29 Product函数 功能 所有以参数形式给出的数字相乘 并返回乘积表达式 product number1 number2 30 Rand函数 功能 返回大于0小于1的均匀分布随机数表达式 rand 31 练习 某次抽奖中 随机返回4位数1000 9999 32 函数的分类3 逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 33 AVERAGEAVERAGEA函数 功能 AVERAGE计算所有包含数字单元各的平均值 AVERAGEA计算所有非空单元各的平均值格式 AVERAGE VALUE1 VALUE2 AVERAGEA NUMBER1 NUMBER2 34 计数函数 COUNT和COUNTA函数功能 COUNT函数返回的区域数据单元格个数COUNTA函数返回的区域非空单元格个数表达式 COUNT VALUE1 VALUE2 COUNTA NUMBER1 NUMBER2 参数个数在1 30之间 35 COUNTIF函数 按条件统计功能 COUNTIF函数是统计指定单元格区域中满足条件的单元格个数格式 COUNTIF RANGE CRITERIA 36 LARGESMALL函数功能 LARGE和SMALL函数是MAX和MIN函数的具体化 可以进一步细分被考察区域的数值中第K个最大或最小的点 反过来说MAX与MIN函数恰好是这两个函数的极端形式格式 LARGE ARRAY K 37 返回排位函数RANK功能 返回某个数字在数字列表中的排位格式 RANK NUMBER REF ORDER 38 练习 出勤统计 39 函数的分类4 逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 40 ADDRESS函数返回引用地址功能 按照给定的行号和列标 建立文本类型的单元格地址格式 ADDRESS ROW NUM COLUMN NUM ABS NUM A1 SHEET TEXT ABS NUM 表示指定引用的类型1或省略 表示绝对引用2表示绝对行号 相对列标3表示相对行号 绝对列标4表示相对引用 41 CHOOSE函数从列值中选择数值功能 返回数值参数列表中的数值格式 CHOOSE index num value1 value2 index num 表示用以指明待选参数序号的参数值 必须是1 29之间的数值 或者包含1 29的公式或单元格引用 42 COLUMN ROW函数返回列标和行号COLUMNS ROWS函数返回引用的列行数功能 COLUMN ROW返回给定引用的列标 行号格式 COLUMN reference ROW reference 功能 COLUMNS ROWS返回给定引用的列数 行数格式 COLUMNS array ROWS array 43 查找引用函数垂直查找函数 VLOOKUP 功能 在数据区域的首列查找指定的值 并返回查找值所在行指定列处的数据格式 VLOOKUP lookup value table array col index num range lookup 参数的含义lookup value 需要在数据表首列进行搜索的值table array搜索数据指定的数据表或数组col index num满足条件的range lookup 44 在数组中查询函数 MATCH 功能 返回在指定方式下与指定数值匹配的数组中元素的相应位置 格式 MATCH lookup value lookup array match type 参数的含义Lookup value为需要在数据表中查找的数值 Lookup value为需要在Look array中查找的数值 Lookup value可以为数值 数字 文本或逻辑值 或对数字 文本或逻辑值的单元格引用 Lookup array可能包含所要查找的数值的连续单元格区域 Lookup array可以为数组或数组引用 45 参数的含义 Match type为数字 1 0或1 1 函数MATCH查找小于或等于lookup value的最大数值 Lookup array必须按升序排列 0 函数MATCH查找等于lookup value的第一个数值 Lookup array可以按任何顺序排列 1 函数MATCH查找大于或等于lookup value的最小数值 Lookup array必须按降序排列 如果省略match type 则假设为1查找文本值时 函数MATCH不区分大小写字母 如果函数MATCH查找不成功 则返回错误值 N A 如果match type为0且lookup value为文本 lookup value可以包含通配符 星号 和问号 星号可以匹配任何字符序列 问号可以匹配单个字符 46 功能 返回数据清单或数组中的元素值 此元素由行序号和列序号的索引值给定 格式 INDEX array row num column num Array为单元格区域或数组常数 Row num数组中某行的行序号 函数从该行返回数值 如果省略row num 则必须有column num Column num数组中某列的列序号 函数从该列返回数值 如果省略column num 则必须有row num 47 INDEX 返回指定内容 INDIRECT返回指定的引用 功能 返回由文本字符串指定的引用 此函数立即对引用进行计算 并显示起内容 格式 INDIRECT ref text a1 参数的含义ref text为对单元格的引用 A1引用式样 48 功能以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以为一个单元格或单元格区域 并可以指定返回的行数或列数 格式 OFFSET reference rows cols height width Reference作为偏移量参照系的引用区域 Reference必须为对单元格或相连单元格区域的引用 否则 函数OFFSET返回错误值 VALUE Rows相对于偏移量参照系的左上角单元格 上 下 偏移的行数 如果使用5作为参数Rows 则说明目标引用区域的左上角单元格比reference低5行 行数可为正数 代表在起始引用的下方 或负数 代表在起始引用的上方 49 OFFSET 调整引用函数 Cols相对于偏移量参照系的左上角单元格 左 右 偏移的列数 如果使用5作为参数Cols 则说明目标引用区域的左上角的单元格比reference靠右5列 列数可为正数 代表在起始引用的右边 或负数 代表在起始引用的左边 如果行数和列数偏移量超出工作表边缘 函数OFFSET返回错误值 REF Height高度 即所要返回的引用区域的行数 Height必须为正数 Width宽度 即所要返回的引用区域的列数 Width必须为正数 如果省略height或width 则假设其高度或宽度与reference相同 50 OFFSET 调整引用函数 说明 函数OFFSET实际上并不移动任何单元格或更改选定区域 它只是返回一个引用 函数OFFSET可用于任何需要将引用作为参数的函数 例如 公式SUM OFFSET C2 1 2 3 1 将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值 示例OFFSET C3 2 3 1 1 等于F5 如果在工作表中输入此公式 MicrosoftExcel显示单元格F5中的数值 OFFSET C3 E5 1 0 3 3 等于C2 E4OFFSET C3 E5 0 3 3 3 等于 REF 51 OFFSET 调整引用函数 练习 某公司财务部门统计了公司的债权情况 由于每个人的还款时间都是不一样的 需要根据借款清单 将没有还请贷款的名单自动顺序列出 要求不留空行 结清后有自动删除 重新排序 而且不需要进行手工处理 52 函数的分类5 逻辑函数数学 三角和数组函数统计函数查询和引用函数数据库函数文本函数日期和时间函数信息函数 53 数据库函数 数据库函数主要是对存放在数据清单或数据库中的数据进行分析 判断是否符合满足特定条件 并返回满足条件的值 具有以下特点 每个函数均有三个参数 DATABASE FIELD CRITERIA参数DATABASE为构成数据清单或数据库的单元格区域参数FIELD为指定数据所使用的数据列参数CRITERIA为一给定条件的单元格区域 包含列标题和条件函数名称以D开头 如去掉D 则函数已学过 54 55 DAVERAGE函数返回条目的平均值 功能 返回清单或数据库中满足指定条件的列中数值的平均值格式 DAVERAGE DATABASE FIELD CRITERIA 56 DCOUNT函数返回单元格个数 功能 返回清单或数据库列中满足指定条件且包含数字单元格个数格式 DAVERAGE DATABA