《EXCEL函数教学》PPT课件.ppt

EXCEL函数应用 管理部人力资源组2008年9月25日 课程大纲 课堂讲解篇 函数功能说明与案例讲解 共20个 课后自学篇 基础函数功能说明 25个 EXCEL函数教学目的 通过功能说明与案例演示 了解函数的应用范围与功效 激发后续学习函数的兴趣掌握函数使用的方法与技巧充分发挥Excel的强大数据处理功能 提升工作效率 函数应用初步演示 利用函数在excel中制作工资条Go EXCEL函数的结构 Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 Excel函数结构 也有一些函数是没有参数的 如ROW 左右括号成对出现 单一结构 嵌套结构 参数与参数之间使用半角逗号进行分隔 函数参数常用符号或表示方法 函数公式中的文本必须用半角引号 如 东南汽车 而非直接输入东南汽车或 东南汽车 连接符 A2 B7 1 1 1 5 F F A N 函数列表 VALUE 用途 将表示数字的文字串转换成数字 语法 VALUE text 参数 Text为带引号的文本 或对需要进行文本转换的单元格的引用 它可以是Excel可以识别的任意常数 日期或时间格式 如果Text不属于上述格式 则VALUE函数返回错误值 VALUE Value演示Go 文本型态的数字不可计算 通过value函数转换就可以计算了 If 逻辑函数 功能 IF函数用于执行真假值判断后 根据逻辑测试的真假值返回不同的结果 它的应用很广泛 可以使用函数IF对数值和公式进行条件检测 语法格式 IF 条件 A B 其中 条件 表示计算结果为TRUE或FALSE的任意值或表达式 若为常量 视为True 条件参数可使用任何比较运算符 条件返回的结果为真的话 则执行A 否则执行B IF函数仅可以嵌套七层 超过将会出错 IF函数的简单案例一Go 性别为男者 称谓为XX先生 性别为女者 称谓为XX女士 案例二 依下述税率表 利用if函数计算个人所得税 课后练习 注意 因IF仅可嵌套七层 所以无法计算月薪超过102000者个人所得税 假如A3为存放税前工资的单元格 个人所得税计算公式如下 IF A3 82000 A3 2000 40 10375 IF A3 62000 A3 2000 35 6375 IF A3 42000 A3 2000 30 3375 IF A3 22000 A3 2000 25 1375 IF A3 7000 A3 2000 20 375 IF A3 4000 A3 2000 15 125 IF A3 2500 A3 2000 10 25 IF A3 2000 A3 2000 5 0 Go Sumif 条件求和函数 用途 根据指定条件对若干单元格 区域或引用求和 语法 SUMIF 条件区域 条件 需求和的区域 参数 条件是由数字 逻辑表达式等组成的判定条件 Sumif案例Go 请统计人资组同仁的工资总额 Sumif A A 人资组 C C Countif 条件计数函数 用途 计算区域中满足给定条件的单元格的个数 语法 COUNTIF 统计区域 条件 参数 统计区域 为需要计算其中满足条件的单元格数目的单元格区域 条件 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 Countif案例Go 以上述样表为据 完成以下案例 例1 依 师员 字段 统计师员级的人数countif C C 师 countif C C 员 例2 统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif I I 3 countif I I 3 countif I I 5 例3 检测到职编号是否有重复值countif A A A2 Counta 用途 返回参数组中非空值的数目 利用函数COUNTA可以计算数组或单元格区域中数据项的个数 语法 COUNTA 单元格区域1 单元格区域2 说明 参数的个数为1 30个 Counta的思考案例 例1 如果A1 6 28 A2 3 74 其余单元格为空 则公式 COUNTA A1 A7 的计算结果等于 例2 统计C列的非空白单元格的表示方法为 若整张EXCEL表的每个单元格都有数据 前述函数公式的结果为 Offset 用途 以指定的引用为参照系 通过给定偏移量得到新的引用 返回的引用可以是一个单元格或单元格区域 并不返回值 语法 OFFSET 作为偏移量参照系的引用位置 上下偏移的行数 左右偏移的列数 height width 参数 作为偏移量参照系的引用位置 它必须是单元格或相连单元格区域的引用 上 下 偏移的行数 正数代表在起始引用下边 负数代表在起始引用的上边 0代表没有上 下 偏移 左 右 偏移的列数 正数代表在起始引用右边 负数代表在起始引用的左边 0代表没有左 右 偏移 Height 是要返回的引用区域的总行数 必须为正数 Width 是要返回的引用区域的总列数 必须为正数 Offset的思考题 问题一 OFFSET B1 2 1 4 1 定位到哪几个单元格 问题二 以A1单元格为参照 要定位到红线区域如何写函数 OFFSET A1 6 1 2 2 Counta offset的综合应用案例Go 要求 当记录增加时 公式自动统计总销量 假设销量存放600数值的单元格为C3 SUM OFFSET C3 0 0 COUNTA C C 1 1 或 SUM OFFSET C2 1 0 COUNTA C C 1 1 注 以上公式只能正确计算不间断的连续数据 如果表格中销量的数据有空白单元格 那么动态名称的引用位置将发生错误 COLUMN 用途 返回给定引用的列标 语法 COLUMN 单元格引用 参数 单元格引用 为需要得到其列标的单元格 如果省略 则假定函数COLUMN是对所在单元格的引用 实例 公式 COLUMN A3 返回1 COLUMN D5 返回 Row 用途 返回给定引用的行号 语法 ROW 单元格引用 单元格引用 为需要得到其行号的单元格或单元格区域 实例 利用row建立序号 Go MOD 取余 用途 返回两数相除的余数 其结果的正负号与除数相同 语法 MOD 被除数 除数 参数 除数不能为零 Mod函数的案例 实例1 公式 MOD 14 4 返回 MOD 5 2 返回 实例2 如何利用MOD函数 将整张EXCEL表的偶数行都标上底色Go Vlookup 返回表格或数组当前行中指定列处的数值 语法 VLOOKUP 索引值 数据区域 列序号 查找方式 参数 索引值为需要在数据表第一列中查找的数值 它可以是数值 引用或文字串 数据区域为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 列序号指数据区域中待返回的匹配值的列序号 它等于1时 返回数据区域第一列中的数值 它等于2时 返回数据区域第二列中的数值 以此类推 查找方式为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为1或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于索引值的最大数值 如果为0 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A Vlookup案例 案例一 利用vlookup抓取另一张表中的资料 Go案例二 请参考链接之案例 课后自学 用vlookup计算所得税 解决IF只能嵌套七层的局限 Go match 用途 返回在指定方式下与指定数值匹配的数组中元素的相应位置 不是具体的单元格 语法 MATCH A B C 参数 A 为需要在数据表中查找的数值或单元格引用 B 是可能包含所要查找的数值的连续单元格区域 C 它说明Excel如何在B中查找A C的常用值为0 表示函数MATCH查找等于A的第一个数值 注意 MATCH函数返回B中目标值的位置 而不是数值本身 Match函数的案例Go MATCH 11 A1 A9 0 返回的值是 match 420 A3 E3 0 返回的值是 Vlookup与match函数的综合运用Go 利用VLOOKUP和MATCH函数查找出相应月份和产品的销售额 Left Right 截取字符串函数 用途 根据指定的字符数返回文本串中的第一个或前几个字符 此函数用于双字节字符 语法 LEFT 文本 返回的字符数 Right 文本 返回的字符数 参数 返回的字符数必须大于或等于0 实例 如果A1 电脑爱好者 则LEFT A1 2 返回 Right A1 3 返回 Left与right的综合运用Go 如何利用Left及Right从身份证号中提取出生年份 提示 假如A2存放身份证号 提取年份的做法 RIGHT LEFT A2 10 4 Mid 截取字符串函数 用途 MID返回文本串中从指定位置开始的特定数目的字符 该数目由用户指定 语法 MID 文本串 开始位置 返回字符的个数 参数 开始位置是文本中要提取的第一个字符的位置 文本中第一个字符的开始位置为1 以此类推 实例1 如果a1 东南 福建 汽车工业有限公司 则公式 MID A1 4 2 返回 福建 案例 利用MID从身份证号中提取出生年份 Go Replace 替换指定位置处的任意文本 用途 REPLACE使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本 语法 REPLACE 需替换其部分字符的文本 被替换字符的起始位置 替换的字符个数 用于替换到原文本中去的字符 思考 EXCEL本身就有替换功能 为什么要用replace函数 解决替换字符多变的问题 Replace的案例 案例一 请分别说出下列两个函数公式的返回值 Go LEN 计算字符长度 用途 LEN返回文本串的字符数 语法 LEN text 参数 Text待要查找其长度的文本 注意 此函数用于双字节字符 且空格也将作为字符进行统计 实例 如果A1 电脑爱好者 则公式 LEN A1 返回 If Len及mid函数综合运用Go 如果A列里CM后没有 00 的话 就把 00 加到CM后 但是如果CM后有 00 话 就不加了 产生的效果如B列所示 怎么做 公式提示 IF MID A1 3 2 00 CM00 MID A1 3 LEN A1 2 A1 SUBSTITUTE 替换指定文本 用途 在文字串中用新文本替代旧文本 如果需要在一个文字串中替换指定的文本 可以使用函数SUBSTITUTE 如果需要在某一文字串中替换指定位置处的任意文本 就应当使用函数REPLACE 语法 SUBSTITUTE 需要替换其中字符的文本 需被替换的旧文本 用於替换的文本 第几次出现的旧文本 参数 第几次出现的旧文本 为一数值 用来指定以新的文本替换第几次出现的旧文本 如果指定了该参数 则只有满足要求的旧文本被替换 否则将用新文本替换所有出现的旧文本 SUBSTITUTE MID LEN的综合运用案例Go 将左边的物料代码中无序空格 处理为右边的统一格式 Rept 补位函数 用途 按照给定的次数重复显示文本 可以通过REPT函数对单元格进行重复填充 语法 REPT 需重复显示的文本 重复显示的次数 参数 重复显示的次数需为正数 注意 REPT函数的结果不能多于255个字符 实例 公式 REPT 很好 2 返回 很好很好 Rept函数与len函数的综合运用Go Index 用途 返回表格或区域中的数值 语法 INDEX 单元格区域 行序号 列序号 返回数组中指定的单元格或单元格数组的数值 案例 题一 INDEX A1 C5 2 2 的返回值是 题二 INDEX A1 A5 5 1 的返回值是 Go MOD COLUMN ROW index函数综合运用 打印工资条Go 工资数据原表 存放在sheet1中 希望能在另一个工作表中将上述工资表转换为以下样式 提示 IF MOD ROW 2 0 Sheet1 A 1 INDEX Sheet1 A N ROW 2 1 COLUMN PERCENTRANK 用途 返回某个数值在一个数据集合中的百分比排位 可用于查看数据在数据集中所处的位置 语法 PERCENTRANK 数据集合 需要在数值集合中得到排位的值 PERCENTRANK的案例 案例一 计算出各汽车厂商相应销量的百分比排位Go 自学篇 函数基础知识及常用函数 函数的种类 扩展函数的启用 按照函数的来源 Excel函数可以分为 内置函数 只要启动了Excel 用户就可以使用它们 如 sum average 扩展函数 必须通过单击 工具 加载宏 菜单命令加载 然后才能像内置函数那样使用 当你发现你某些函数不可用时 请执行这一步骤 如 iseven函数的使用必须执行 工具 加载宏 分析工具库 方能使用 函数数组公式的输入方法 数组用于可产生多个结果 或可以对存放在行和列中的一组参数进行计算的公式 如 FREQUENCY G2 G10 H2 H4 数组公式放在 内部 按下Ctrl Shift Enter组合键自动生成 不可用输入大括号的方式来完成Go 函数参数中的单元格引用 根据公式所在单元格的位置发生变化时 单元格引用的变化情况 我们可以引用分为相对引用 如A2 绝对引用 A 2 和混合引用三种类型混合引用有 绝对列和相对行 如 A3 E3 或是 绝对行和相对列 如A 3 E 3 两种形式 前者不论公式怎么复制 列都不变 后者不论公式怎么复制 行号不变 绝对符号的添加 用F4放在公式中的单元格引用中进行切换几种单元格引用表示法 A2 B7 某一单元格区域 1 1 第一行 1 5 1至5行 F F F列 A N A到N列 Go 函数参数中的名称使用 为了更加直观地标识单元格或单元格区域 我们可以给它们赋予一个名称 从而在公式或函数中直接引用 如 average 销量 创建好的名称可以被所有工作表引用 而且引用时不需要在名称前面添加工作表名 因此名称引用实际上是一种绝对引用 在这里 销量 不是文本哟 函数参数中名称的命名方法 给一个单元格或区域命名的两个方法是 1 选中要命名的单元格或单元格区域 鼠标单击编辑栏顶端的 名称框 在其中输入名称后回车 2 选中要命名的单元格或单元格区域 单击 插入 名称 定义 菜单命令 在打开的 定义名称 对话框中输入名称后确定即可 如果你要删除已经命名的区域 可以按第2种方法打开 定义名称 对话框 选中你要删除的名称删除即可 如果要查看所有的名称 插入 名称 粘贴 菜单命令 名称使用演示 Go 函数参数中标志的使用方法 由于Excel工作表多数带有 列标志 例如一张成绩统计表的首行通常带有 序号 姓名 数学 物理 等 列标志 也可以称为字段 如果单击 工具 选项 菜单命令 在打开的对话框中单击 重新计算 选项卡 选中 工作簿选项 选项组中的 接受公式标志 选项 公式就可以直接引用 列标志 了 例如 B2 B46 区域存放着学生的物理成绩 而B1单元格已经输入了 物理 字样 则求物理平均分的公式可以写成 AVERAGE 物理 需要特别说明的是 公式引用 列标志 时的限制较多 它只能在当前数据列的下方引用 不能跨越工作表引用 从本质上讲 名称和标志都是单元格引用的一种方式 因为它们不是文本 使用时名称和标志都不能添加引号 Go Rank 用途 返回一个数值在一组数值中的排位 如果数据清单已经排过序了 则数值的排位就是它当前的位置 语法 RANK 需要计算其排位的数字 存放要比较的数字的区域 排序方式 参数 排序方式为一数字 指明排位的方式 如果排序方式为0或省略 则按降序排列的数据清单进行排位 如果排序方式不为零 当作按升序排列的数据清单进行排位 注意 函数RANK对重复数值的排位相同 但重复数的存在将影响后续数值的排位 如在一列整数中 若整数60出现两次 其排位为5 则61的排位为7 没有排位为6的数值 Rank的案例Go 对汽车厂商8月份销量进行排名 QUARTILE 用途 返回一组数据的四分位点 值 四分位数通常用于在考试成绩之类的数据集中对总体进行分组 如求出一组分数中前25 的分数 语法 QUARTILE array Quart 参数 Array为需要求得四分位数值的数组或数字引用区域 Quart决定返回哪一个四分位值 Quart取0 1 2 3 4 则函数QUARTILE分别返回最小值 第一个四分位数 第25个百分排位 中分位数 第50个百分排位 第三个四分位数 第75个百分排位 和最大数值 QUARTILE的案例Go 请用QUARTILE函数 统计左表中不同分位值 结果如右表 Sign 返回数字的符号 用途 返回数字的符号 正数返回1 零返回0 负数返回 1 语法 SIGN number 参数 Number是需要返回符号的任意实数 实例 如果A1 65 25 则公式 SIGN A1 返回1 SIGN 6 12 返回 1 SIGN 9 9 返回0 SUM 求和函数 用途 返回某一单元格区域中所有数字之和 语法 SUM number1 number2 语法格式 Number1 number2 为1到30个需要求和的数值 包括逻辑值及文本表达式 区域或引用 注意 参数表中的数字 逻辑值及数字的文本表达式可以参与计算 其中逻辑值被转换为1 文本被转换为数字 如果参数为数组或引用 只有其中的数字将被计算 数组或引用中的空白单元格 逻辑值 文本或错误值将被忽略 SUM函数中的参数 即被求和的单元格或单元格区域不能超过30个 换句话说 SUM函数括号中出现的分隔符 逗号 不能多于29个 否则Excel就会提示参数太多 参数太多的突破方法 巧用括号法 AVERAGE 求算术平均值 用途 计算所有参数的算术平均值 语法 AVERAGE number1 number2 参数 Number1 number2 是要计算平均值的1 30个参数 实例 如果A1 A5区域命名为分数 其中的数值分别为100 70 92 47和82 则公式 AVERAGE 分数 返回78 2 Subtotal 返回数据清单的分类汇总 用途 返回数据清单或数据库中的分类汇总 如果用户使用 数据 菜单中的 分类汇总 命令创建了分类汇总数据清单 即可编辑SUBTOTAL函数对其进行修改 语法 SUBTOTAL 函数名代表号 区域1 区域2 语法说明 函数名代表号为1到11之间的自然数 用来指定分类汇总计算使用的函数 常用的代表号为 1是AVERAGE 3是COUNTA 4是MAX 5是MIN 9是SUM 区域1 区域2 则是需要分类汇总的1到29个区域或引用 AND 用途 所有参数的逻辑值为真时返回TRUE 真 只要有一个参数的逻辑值为假 则返回FALSE 假 语法 AND 逻辑表达式1 逻辑表达式2 参数 逻辑表达式最多可达30个 它们的结论或为TRUE 1 或为FALSE 0 参数必须是逻辑值或者包含逻辑值的数组或引用 如果指定的单元格区域内包括非逻辑值 AND将返回错误值 VALUE 实例 公式 AND 3 2 0 返回FALSE 公式 AND 3 2 8 6 返回True OR 用途 所有参数中的任意一个逻辑值为真时即返回TRUE 真 语法 OR 逻辑表达式1 逻辑表达式2 参数 同and的参数 实例 如果A1 6 A2 8 则公式 OR A1 A2 A2 A1 A2 返回TRUE 而公式 OR A1 A2 A1 A2 返回FALSE ABS 求绝对值 用途 返回某一参数的绝对值 语法 ABS number 参数 number是需要计算其绝对值的一个实数 实例 如果A1 16 则公式 ABS A1 返回16 ROUND 对数值进取四舍五入 用途 按指定位数四舍五入某个数字 语法 ROUND 需四舍五入的数字 保留的小数位 注意 如果保留的小数位大于0 则四舍五入到指定的小数位 如果等于0 则四舍五入到最接近的整数 如果小于0 则在小数点左侧按指定位数四舍五入 实例 如果A1 65 25 则公式 ROUND A1 1 返回65 3 ROUND 82 149 2 返回82 15 ROUND 21 5 1 返回20 Round A1 0 返回65 Countblank 用途 计算某个单元格区域中空白单元格的数目 语法 COUNTBLANK range 参数 Range为需要计算其中空白单元格数目的区域 Countblank的思考案例 1 在一个空白的excel表中 Countblank 1 1 相当于EXCEL的列数 Countblank a a 相当于EXCEL的行数 2 在一个非空白的excel表中 Counta a a countblank a a INT 无条件取整 用途 将任意实数向下取整为最接近的整数 语法 INT 实数 参数 Number为需要处理的任意一个实数 实例 如果A1 16 24 A2 28 389 则公式 INT A1 返回16 INT A2 返回 29 Trunc 用途 将数字的小数部分依需截去 不作四舍五入 返回实数 语法 TRUNC 需要截去小数部分的数字 保留小数的位数 注意 TRUNC函数可以按需要截取数字的小数部分 而INT函数则将数字向下舍入到最接近的整数 INT和TRUNC函数在处理负数时有所不同 TRUNC 4 3 返回 4 而INT 4 3 返回 5 实例 如果A1 78 652 则公式 TRUNC A1 1 返回78 6 TRUNC A1 2 返回78 65 TRUNC 8 963 2 返回 8 96 EXACT 比较两个字符串是否相同 用途 测试两个字符串是否完全相同 如果它们完全相同 则返回TRUE 否则返回FALSE EXACT函数能区分大小写 但忽略格式上的差异 语法 EXACT text1 text2 参数 Text1是待比较的第一个字符串 Text2是待比较的第二个字符串 实例 如果A1 得利卡 A2 富利卡 A3 戈蓝 则公式 EXACT A1 A2 返回FALSE EXACT A1 A3 返回FALSE EXACT word word 返回TRUE ISEVEN 用途 测试参数的奇偶性 如果参数为偶数返回TRUE 否则返回FALSE 语法 ISEVEN number Number待测试的数值 如果参数值不是整数 则自动截去小数部分取整 注意 该函数必须加载 分析工具库 方能使用 如果参数number不是数值 ISEVEN函数返回错误值 VALUE 实例 公式 ISEVEN 11 返回FALSE ISEVEN 6 返回TRUE ISERROR 用途 它们可以检验是否发生错误值 发生错误返回TRUE 否则返回FALSE 语法 ISERROR value 参数 Value是需要进行检验的参数 实例 用VLOOKUP函数找不到相应的值时 显示空值 IF ISSEROR vlookup A2 A2 G5 2 0 vlookup A2 A2 G5 2 0 CONCATENATE 用途 将若干文字串合并到一个文字串中 其功能与 运算符相同 语法 CONCATENATE text1 text2 参数 Text1 text2 为1到30个将要合并成单个文本的文本项 这些文本项可以是文字串 数字或对单个单元格的引用 实例 如果A1 98 A2 千米 则公式 CONCATENATE A1 A2 返回 98千米 与公式 A1 A2 等价 YEAR MONTH DAY 用途 返回某日期的年份 月份 日期 语法 YEAR MONTH DAY 日期值 实例 假设A2单元格存放的日期为2008 8 20公式 YEAR A2 返回2008 公式 MONTH A2 返回8 公式 DAY A2 返回20 Date 用途 返回代表特定日期的序列号 语法 DATE year month day 参数 year为一到四位 代表年份 Month代表每年中月份的数字 Day代表在该月份中第几天的数字 实例 将身份证中提取出的年月日直接转化成出生年月日 假若A 350102197405253613 则date mid a 7 4 mid a 11 2 mid a 13 2 返回1974 5 25 Today 用途 返回系统当前日期的序列号 参数 无语法 TODAY 实例1 公式 TODAY 返回执行公式时的系统时间 实例2 依出生日期计算年龄的示例表示方法 today 出生日期 365 weekday 用途 返回某日期的星期数 在默认情况下 它的值为1 星期天 到7 星期六 之间的一个整数 语法 WEEKDAY 日期 返回类型 参数 返回类型 为确定返回值类型的数字 数字1或省略则1至7代表星期天到数星期六 数字2则1至7代表星期一到星期天 推荐使用 数字3则0至6代表星期一到星期天 实例 公式 WEEKDAY 2008 8 20 2 返回3 星期三 WEEKDAY 2008 8 20 3 返回2 星期三 EDATE 用途 返回指定日期之前或之后指定月份的日期序列号 语法 EDATE 开始日期 在开始日期之前或之后的月份数 参数 在开始日期之前或之后的月份数 未来日期用正数表示 过去日期用负数表示 实例 公式 EDATE 2008 8 20 2 返回39741即2008 10 20 FREQUENCY 用途 以一列垂直数组返回某个区域中数据的频率分布 它可以计算出在给定的值域和接收区间内 每个区间包含的数据个数 语法 FREQUENCY 数据源 分段点 注意该函数的特性 1 忽略文本或空白单元格 数据源区域出现文本或空格 只统计数据 其他没有影响 2 支持数据乱序 支持数据源及分段点的乱序 3 对于每一分段点 统计小于等于此分段点 大于上一分段点的频数 最后一格始终统计大于最大分段点的频数 4 要以数组形式输入公式 在公式的编辑栏同时按下ctrl alt enter FREQUENCY的案例Go 统计某科成绩不同分数段的人数 思考 用FREQUENCY与用EXCEL原有的数据透视表功能有何不同 以上