常用函数与操作技巧.ppt

2020 2 25 1 李东2011 12 EXCEL常用公式函数实战技巧与应用入门 2020 2 25 2 目录 常用操作技巧引用切换公式与结果切换手动重算和F9设置计算精度设置底纹隐藏与保护序列数据填充数据有效性 常用函数实践数学函数IF与奖金核算多条件求和与业绩统计舍入取整统计单元格数量查找和排名日期函数与在库天数 员工工龄计算查找重复值 2020 2 25 3 学习Excel的必要性 心态及方法 普及性效率性通用性 积极心态循序渐进善用资源学以致用 愿你从一个新手成长成为一个高手 2020 2 25 4 EXCEL技能的层次 新手 刚刚开始接触初级 基本掌握常用功能 录入 排序 筛选等中级 熟练使用常用功能 20个以上函数 数据透视表 简单的宏等高级 数组公式 VBA编程专家 EXCEL技能 专业知识 行业经验 从新手到专家 2020 2 25 5 公式中的算术运算符 2020 2 25 6 公式中的比较运算符 2020 2 25 7 公式中的文本运算符 连字符 CONCATENATE 参数1 参数2 2020 2 25 8 公式中运算符优先级顺序 2020 2 25 9 常用操作技巧 单元格引用相对引用 默认状态 绝对引用混合引用切换技巧 F4 2020 2 25 10 常用操作技巧 单元格公式与结果切换 菜单方式 工具 选项 视图 公式 勾选 快捷键 Ctrl 2020 2 25 11 常用操作技巧 自动重算和手动重算默认自动 但数据量大时运行速度慢切换方式 工具 选项 重新计算 勾选 手动计算重算活动工作表 Shift F9编辑栏内选中公式切换结果 F9所有工作薄 Shift Ctrl F9 2020 2 25 12 常用操作技巧 设置计算精度 菜单方式 工具 选项 重新计算 勾选 以显示精度为准函数方式 RONUD 数据 位数 2020 2 25 13 常用操作技巧 设置不同底纹格式 条件格式 公式 进行设置 奇偶行不同底纹 MOD ROW 2 1奇偶列不同底纹 MOD COLUMN 2 1根据部门名称设置不同间隔底纹 MOD SUM D 2 D2 D 1 D1 2 1 MOD SUM D 2 D2 D 1 D1 2 0 MOD COUNTA A 2 A2 2 1 2020 2 25 14 常用操作技巧 隐藏数据 1 单元格格式 数字 分类 自定义 类型 输入三个英文半角状态下的分号2 切换到保护 勾选隐藏和锁定 退出3 工具 保护 保护工具表 设定密码 2020 2 25 15 常用操作技巧 隐藏公式 1 单元格格式 保护 清除 锁定 返回2 编辑 定位 定位条件 选择 公式 返回3 单元格格式 保护 勾选 隐藏和锁定4 工具 保护工作表 设置密码 2020 2 25 16 常用操作技巧 数据保护权限 查看和编辑 部分和全部 工作表和簿是否可见 可改 单元格和工作表保护 2020 2 25 17 常用操作技巧 数据填充左键 范本 智能标记 右键 菜单 录入范本 左键拖动填充录入基准 1 左键复制 Ctrl 放开左键录入基准1 左键填充 显示智能标记选中区域 编辑 填充 序列设定基准 1 右键 自动填充选项 序列 2020 2 25 18 常用操作技巧 复写纸功能 同时在多张工作表上编辑数据Ctrl Shift编辑 填充 至同组工作表 2020 2 25 19 常用操作技巧 隔一行插一空行 插入辅助列 先用内容加行号123 之后1 1 2 1 3 1 排序升序 删除辅助列 2020 2 25 20 常用操作技巧 数据有效性与二级下拉菜单 1 数据有效性与下拉菜单2 序列引用名称实现跨工作表3 建立名称区域 序列 indirect 单元格 实现二级下拉菜单单元格 上级菜单中的一个 相对引用 2020 2 25 21 常用操作技巧 常用快捷键 选中活动单元格周围的当前区域 Ctrl SHIFT 8选定整个工作表 CTRL A单元格内换行 Alt 回车键每次回车在原单元格 Ctrl 回车键快速录入相同文本 Ctrl DCtrl R同时多选单元格或工作表 Ctrl Shift 2020 2 25 22 常用操作技巧 常用快捷键 显示 单元格格式 对话框 Ctrl 1隐藏行 Ctrl 9取消隐藏行 Ctrl Shift 9隐藏列 Ctrl 0 零 取消隐藏列 Ctrl Shift 0插入新工作表 Shift F11 2020 2 25 23 常用操作技巧 常用快捷键 插入新工作表 Shift F11定义名称 Ctrl F3插入时间ctrl shift 输入日期ctrl 2020 2 25 24 单元格函数错误提示 列不够宽或包含一个无效的时间 VALUE 参数类型错误 REF 单元格引用无效 NAME 不能识别公式中的文本 名称拼写错误 NUM 公式中使用了无效数值 N A 数值对公式或函数不可用 2020 2 25 25 什么是函数 Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 以常用的求和函数SUM为例 它的语法是 SUM number1 number2 其中 SUM 称为函数名称 一个函数只有唯一的一个名称 它决定了函数的功能和用途 函数名称后紧跟左括号 接着是用逗号分隔的称为参数的内容 最后用一个右括号表示函数结束 2020 2 25 26 函数的参数 常量逻辑值数组错误值单元格引用嵌套函数名称和标记 2020 2 25 27 函数的参数 常量是直接输入到单元格或公式中的数字或文本 或由名称所代表的数字或文本值 例如数字 2890 56 日期 2003 8 19 和文本 黎明 都是常量 但是公式或由公式计算出的结果都不是常量 因为只要公式的参数发生了变化 它自身或计算出来的结果就会发生变化 常量 2020 2 25 28 函数的参数 逻辑值是比较特殊的一类参数 它只有TRUE 真 或FALSE 假 两种类型 例如在公式 IF A3 0 A2 A3 中 A3 0 就是一个可以返回TRUE 真 或FALSE 假 两种结果的参数 当 A3 0 为TRUE 真 时在公式所在单元格中填入 0 否则在单元格中填入 A2 A3 的计算结果 逻辑值 2020 2 25 29 函数的参数 数组用于可产生多个结果 或可以对存放在行和列中的一组参数进行计算的公式 Excel中有常量和区域两类数组 前者放在 按下Ctrl Shift Enter组合键自动生成 内部 而且内部各列的数值要用逗号 隔开 各行的数值要用分号 隔开 假如你要表示第1行中的56 78 89和第2行中的90 76 80 就应该建立一个2行3列的常量数组 56 78 89 90 76 80 数组 2020 2 25 30 函数的参数 区域数组是一个矩形的单元格区域 该区域中的单元格共用一个公式 例如公式 TREND B1 B3 A1 A3 作为数组公式使用时 它所引用的矩形单元格区域 B1 B3 A1 A3 就是一个区域数组 区域数组 2020 2 25 31 函数的参数 使用错误值作为参数的主要是信息函数 例如 ERROR TYPE 函数就是以错误值作为参数 它的语法为 ERROR TYPE error val 如果其中的参数是 NUM 则返回数值 6 错误值 2020 2 25 32 函数的参数 单元格引用是函数中最常见的参数 引用的目的在于标识工作表单元格或单元格区域 并指明公式或函数所使用的数据的位置 便于它们使用工作表各处的数据 或者在多个函数中使用同一个单元格的数据 还可以引用同一工作簿不同工作表的单元格 甚至引用其他工作簿中的数据 单元格引用 2020 2 25 33 函数的参数 函数也可以是嵌套的 即一个函数是另一个函数的参数 例如 IF OR RIGHTB E2 1 1 RIGHTB E2 1 3 RIGHTB E2 1 5 RIGHTB E2 1 7 RIGHTB E2 1 9 男 女 其中公式中的IF函数使用了嵌套的RIGHTB函数 并将后者返回的结果作为IF的逻辑判断依据 嵌套函数 2020 2 25 34 函数的参数 为了更加直观地标识单元格或单元格区域 我们可以给它们赋予一个名称 从而在公式或函数中直接引用 例如 B2 B46 区域存放着学生的物理成绩 求解平均分的公式一般是 AVERAGE B2 B46 在给B2 B46区域命名为 物理分数 以后 该公式就可以变为 AVERAGE 物理分数 从而使公式变得更加直观 名称和标记 2020 2 25 35 常用函数解读与应用 数学函数 求和 差 SUM 参数1 参数30 返回某一单元格区域中数字 逻辑值及数字的文本表达式之和 2020 2 25 36 常用函数解读与应用 数学函数 求积 PRODUCT 参数1 参数30 2020 2 25 37 常用函数解读与应用 数学函数 求商 返回商的整数部分 QUOTIENT 被除数 除数 返回余数 MOD 被除数 除数 2020 2 25 38 常用函数解读与应用 数学函数 舍入与取整函数四舍五入 ROUND按位舍入 ROUNDUP ROUNDDOWN按倍舍入 CEILING FLOOR截断舍入 TRUNC截断取整 INT奇偶取整 ODD EVEN 2020 2 25 39 常用函数解读与应用 数学函数 舍入与取整函数按指定位数四舍五入某个数字 ROUND 数据 保留的位数 按绝对值减小的方向舍入某一数字 ROUNDDOWN 数据 位数 ROUNDDOWN 3 14159 3 返回3 141按绝对值增大的方向舍入一个数字 ROUNDUP 3 14159 1 返回 3 2 2020 2 25 40 常用函数解读与应用 数学函数 舍入与取整函数将数字的小数部分按指定小数截去 返回整数 TRUNC 数据 保留的位数 数据舍入取整 INT 数据 两函数在处理负数时有所不同 TRUNC 4 3 返回 4 而INT 4 3 返回 5 2020 2 25 41 常用函数解读与应用 数学函数 舍入与取整函数按照货币格式将小数四舍五入到指定的位数并转换成文字 DOLLAR number decimals RMB 数据 位数 省略参数decimals 则以2计算 指定位数四舍五入后文本形式显示FIXED 数据 位数 逻辑值 2020 2 25 42 常用函数解读与应用 数学函数 2020 2 25 43 常用函数解读与应用 统计函数 求平均值 计算所有参数的算术平均值 AVERAGE 参数1 参数30 计算参数清单中数值的平均值 包含文本等 AVERAGEA 参数1 参数30 掐头去尾求平均值 TRIMMEAN array percent TRIMMEAN 区域 去掉的数据百分比 TRIMMEAN B3 I3 2 8 2020 2 25 44 常用函数解读与应用 统计函数 求最大值 MAX 参数1 参数30 MAXA 参数1 参数30 求中位值 MEDIAN number1 number30 求最小值 MIN 参数1 参数30 MINA 参数1 参数30 2020 2 25 45 常用函数解读与应用 统计函数 MODE求众数用途 返回在某一数组或数据区域中的众数 语法 MODE number1 number2 33参数 Number1 number2 是用于众数计算的1到30个参数 实例 如果A1 71 A2 83 A3 71 A4 49 A5 92 A6 88 则公式 MODE A1 A6 返回71 2020 2 25 46 常用函数解读与应用 统计函数 计算单元格个数统计单元格区域中含有数字的单元格个数 COUNT 参数1 参数30 返回参数组中非空值的数目 COUNTA 参数1 参数30 计算某个单元格区域中空白单元格的数目 COUNTBLANK 区域 2020 2 25 47 常用函数解读与应用 统计函数 计算单元格个数计算区域中满足给定条件的单元格的个数 COUNTIF range criteria COUNTIF 区域 条件 2020 2 25 48 常用函数解读与应用 统计函数 返回某一数据集中的某个最大值 LARGE array k LARGE 区域 名次 返回数据集中第k个最小值 SMALL array k SMALL 数据集 名次 2020 2 25 49 常用函数解读与应用 统计函数 返回一个数值在一组数值中的排位 RANK 数值 区域 0或省略 降序 RANK 数值 区域 1或大于1 升序RANK 要判断的单元格 查找的区域 1为最小数排第一0为最大数排第一 COUNTIF A 3 A 12 A3 1 SUM A 3 A 12 A3 1 业绩排名 2020 2 25 50 常用函数解读与应用 文本函数 从一个文本字符串的指定位置开始 截取指定数目的字符 MID 文本字符串 起始位置 截取数目 从一个文本字符串的最后一个字符开始 截取指定数目的字符RIGHT 文本字符串 截取数目 提取文本的一部分 2020 2 25 51 常用函数解读与应用 文本函数 从一个文本字符串的第一个字符开始 截取指定数目的字符 LEFTB 目标单元格 从内容的左边开始截取指定的字的个数 LEFT 目标单元格 截取数目 提取文本的一部分 2020 2 25 52 常用函数解读与应用 文本函数 统计文本字符串中字符数目 LEN 目标单元格 检索字符位置 区分或不区分大小写 FIND 要查找的内容加引号或单元格 查找区域 单元格 从几个字开始查找 SEARCH 要查找的内容加引号或单元格 查找区域 单元格 从几个字开始查找 计算文本的长度 2020 2 25 53 常用函数解读与应用 文本函数 EXACT 检查两文本是否完全相同 EXACT 目标单元格或文本1 目标单元格或文本2 REPT 根据指定次数重复文本REPT 要显示的内容或单元格 要求重复的次数 2020 2 25 54 常用函数解读与应用 文本函数 TEXT 要改变数值或单元格 要显示的方式代码 神奇的TEXT函数 2020 2 25 55 常用函数解读与应用 实战案例 性别提取 IF A2 IF MOD RIGHT LEFT A2 17 2 男 女 年龄计算 IF A2 DATEDIF TEXT LEN A2 15 19 MID A2 7 6 LEN A2 18 2 00 00 TODAY y 身份证解析要用到的函数 2020 2 25 56 常用函数解读与应用 实战案例 出生日期提取 IF A2 TEXT LEN A2 15 19 MID A2 7 6 LEN A2 18 2 00 00 0 有效性验证 OR LEN A2 15 IF LEN A2 18 MID 10X98765432 MOD SUM MID A2 ROW INDIRECT 1 17 1 2 18 ROW INDIRECT 1 17 11 1 1 RIGHT A2 身份证解析要用到的函数 2020 2 25 57 常用函数解读与应用 实战案例 提取所属省市IF A5 VLOOKUP LEFT A5 2 data 2 VLOOKUP LEFT A5 4 data 2 15位升级18位 IF LEN A3 15 REPLACE A3 7 19 MID 10X98765432 MOD SUM MID REPLACE A3 7 19 ROW INDIRECT 1 17 1 2 18 ROW INDIRECT 1 17 11 1 1 A3 身份证解析要用到的函数 2020 2 25 58 常用函数解读与应用 逻辑函数 IF 根据条件满足与否返回不同的值AND 检测所有的条件是否为真OR 检测任意一项条件是否为真NOT 对示条件的参数的逻辑值求反逻辑值 TRUE 表示总是为真FALSE 表示总是为假 逻辑函数 2020 2 25 59 常用函数解读与应用 逻辑函数 IF逻辑函数的运用 如果 就 否则 IF 条件表达式 TRUE结果 FALSE的结果 IF J 3 L 3 1 2 500 IF J 3 L 3 1 15 400 IF J 3 L 3 1 1 300 IF J 3 L 3 1 05 200 0 CHOOSE IF J 3 L 3 1 2 4 IF J 3 L 3 1 15 3 IF J 3 L 3 1 1 2 IF J 3 L 3 1 05 1 5 200 300 400 500 0 2020 2 25 60 常用函数解读与应用 数学函数 条件求和根据指定条件对若干单元格 区域或引用求和 SUMIF 条件区域 条件 求和区域 SUMIF B1 B1000 中级 F1 F1000 2020 2 25 61 常用函数解读与应用 数学函数 SUBTOTAL 计算方式 要计算的区域 2020 2 25 62 常用函数解读与应用 实战案例 多条件求和 数组公式 SUM IF Q 91 Q 120 店销 G 91 G 120 C8 1 0 J 91 J 120 SUM IF 条件区域 条件 条件区域2 条件2 1 0 求和区域 2020 2 25 63 常用函数解读与应用 实战案例 多条件求和 数组公式 SUM R 91 R 120 分期 G 91 G 120 C 5 J 91 J 120 SUM 条件1 条件2 求和区域 2020 2 25 64 常用函数解读与应用 实战案例 多条件求和 函数 SUMPRODUCT R 91 R 120 分期 G 91 G 120 C 5 J 91 J 120 SUMPROCUCT 条件区域 条件 条件区域2 条件2 求和区域 2020 2 25 65 常用函数解读与应用 实战案例 多条件求和 函数 2007 SUMIFS 求和区域 条件区域1 条件1 条件区域2 条件2 2020 2 25 66 常用函数解读与应用 实战案例 多条件计数 SUM 条件1 条件2 条件n SUMPROCUCT 条件区域1 条件1 条件区域2 条件2 条件区域n 条件n 2020 2 25 67 常用函数解读与应用 日期与时间函数 今天和现在 TODAY NOW 如何快捷输入当天日期 2020 2 25 68 常用函数解读与应用 日期与时间函数 从日期中提取出年或月或日或星期几 YEAR 日期 MONTH 日期 DAY 日期 WEEKDAY 要查的单元格 2 提取时分秒与此雷同 2020 2 25 69 常用函数解读与应用 日期与时间函数 将文本方式转换成标准日期 DATE 年 月 日 从指定的年 月 日来计算日期序列号值 DATEVALUE 要计算的单元格 从表示日期的文本来计算序列号值 TIME hour minute secod 从时 分 秒来计算出时间的序列号值 TIMEVALUE 时间 从表示时间的文本来计算序列号值 2020 2 25 70 常用函数解读与应用 日期与时间函数 计算年龄 库龄 工龄 IF K 2 DATE YEAR K 2 MONTH D5 DAY D5 YEAR K 2 YEAR D5 YEAR K 2 YEAR D5 1 DATEDIF D5 C5 y IF K 2 D6 365 1 INT K 2 D6 365 0 2020 2 25 71 常用函数解读与应用 日期与时间函数 隐秘函数DATEDIF很多版本里都没有 不能从 插入函数 对话框中输入 用来计算两个日期的年 月 天数的差 DATEDIF 起始日期 结束日期 y DATEDIF 起始日期 结束日期 m DATEDIF 起始日期 结束日期 d 2020 2 25 72 常用函数解读与应用 查找与引用函数 VLOOKUP 查找条件 总区域 结果列数 0 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 VLOOKUP 要查找的内容 搜索的区域 从查找区域首列开始到要找的内容的列数 指定是近似匹配还是精确匹配查找方式 精确查找是vlookup最基本也是最常用的功能 对于数据量大的查找 其速度比菜单中的查找还快 设置vlookup第四个参数为false或0 即为精确查找 2020 2 25 73 常用函数解读与应用 查找与引用函数 VLOOKUP 查找条件 总区域 结果列数 0 VLOOKUP 查找值 所在区域 结果所在列数 0 VLOOKUP 查找条件 搜索区域 列序号 查找方式 VLOOKUP 指定条件 查找区域 右移几行 0 指定搜查条件 设定搜查区域 向右设定到结果所在的列 设定搜查方式是否精确查找 2020 2 25 74 常用函数解读与应用 查找与引用函数 MATCH 查找条件 查找区域 0 返回在指定方式下与指定数值匹配的数组中元素的相应位置 2020 2 25 75 常用函数解读与应用 查找与引用函数 CHOOSE 指定区域的第几个 指定区域1 指定区域2 第一个参数是用来指明待选参数序号的值 它必须是1到29之间的数字 或者是包含数字1到29的公式或单元格引用 其他29个参数可以是数字 单元格 已定义的名称 公式 函数或文本 2020 2 25 76 常用函数解读与应用 查找与引用函数 COLUMN 返回列号ROW 返回行号ADDRESS 第几行 第几列 引用类型 工作表名称 2020 2 25 77 常用函数解读与应用 查找重复值1 防止录入重复数据数据有效性 COUNTIF A A A1 12 查找区域内重复值 条件格式 COUNTIF A A A1 1 IF COUNTIF A A A1 2 重复 2020 2 25 78 常用函数解读与应用 查找重复值3 不同列中找重复数据 ISNUMBER MATCH B2 A 2 A 18 0 IF ISNUMBER MATCH B8 A 2 A 18 0 重复 2020 2 25 79 常用函数解读与应用 查找重复值4 VLOOKUP函数是否可以查找重复值 请大家自己学习 2020 2 25 80 常用函数解读与应用 5 高级筛选解决重复值A 提取单个表中的不重复值全选 数据 高级筛选 列表区域 选择不重复记录B 提取两个表中的相同数据一个条件区域 一个列表区域 2020 2 25 81 常用函数解读与应用 查找不重复值 SUM N MATCH 数据区域 数据区域 0 ROW 数据行区间 1 SUM N MATCH B 2 B 10 B 2 B 10 0 ROW 2 10 1 2020 2 25 82 常用函数解读与应用 VLOOKUP函数向左查询 VLOOKUP G3 CHOOSE 1 2 D 3 D 12 C 3 C 12 2 VLOOKUP G3 IF 1 0 D 3 D 12 C 3 D 12 2 2020 2 25 83 常用函数解读与应用 计算不重复单元格的个数 SUM 1 COUNTIF A 1 A 6 A 1 A 6 数组公式 SUM 1 COUNTIF 区域 区域 2020 2 25 84 常用函数解读与应用 注意事项 名称的规范不能数字和 开头或以纯数字命名不能以字母R C r c命名不能用空格 可以下划线和点号字母不区分大小写 2020 2 25 85 常用函数解读与应用 注意事项 通配符 表示任何字符 表示任何单个字符 用于解释字符的通配性 2020 2 25 86 常用函数解读与应用 文本型数字转换为数值的六个公式 A1 1 A1 1 A1 0 A1 0 A1 VALUE A1 2020 2 25 87 常用函数解读与应用 实战案例 IF ABS A2 0 005 IF A2 0 负 IF INT A