excel常用工作表格20个函数应用介绍

1 IF 函数条件判断函数条件判断 IF 函数是最常用的判断类函数之一 能完成非此即彼的判断 如下图 考核得分的标准为 9 分 要判断 B 列的考核成绩是否合格 IF B4 9 合格 不合格 IF 相当于普通话的 如果 常规用法是 IF 判断的条件 符合条件时的结果 不符合条件时的结果 2 多条件判断 多条件判断 如下图所示 如果部门为生产 岗位为主操 有高温补助 在 D 列使用公式 IF AND B2 生产 C2 主操 有 无 2 AND 函数对两个条件判断 如果同时符合 IF 函数返回 有 否则为无 3 条件求和 条件求和 如下图所示 使用 SUMIF 函数计算一班的总成绩 SUMIF D2 D5 F2 C2 C5 SUMIF 用法是 SUMIF 条件区域 指定的求和条件 求和的区域 用通俗的话描述就是 如果 D2 D5 区域的班级等于 F2 单元格的 一班 就对 C2 C5 单元格对应的区域求和 4 多条件求和 多条件求和 3 如下图所示 要统计部门为生产 并且岗位为主操的补助总额 公式为 SUMIFS D2 D9 B2 B9 F2 C2 C9 G2 SUMIFS 用法是 SUMIFS 求和的区域 条件区域 1 指定的求和条件 1 条件区域 2 指定的求和条件 2 5 条件计数 条件计数 如下图 要统计指定店铺的业务笔数 也就是统计 B 列中有多少个指定的店铺名称 COUNTIF B2 B12 E3 4 COUNTIF 函数统计条件区域中 符合指定条件的单元格个数 常规用法为 COUNTIF 条件区域 指定条件 6 多条件计数 多条件计数 要求 统计统计部门为生产 并且岗位为主操的人数 公式为 COUNTIFS B2 B9 F2 C2 C9 G2 5 COUNTIFS 函数统计条件区域中 符合多个指定条件的单元格个数 常规用法为 COUNTIFS 条件区域 1 指定条件 1 条件区域 2 指定条件 2 7 条件查找 条件查找 VLOOKUP 函数一直是大众情人般的存在 函数的语法为 VLOOKUP 要找谁 在哪儿找 返回第几列的内容 精确找还是近似找 如下图 要查询 F5 单元格中的员工姓名是什么职务 VLOOKUP F 5 B 1 D 10 2 0 使用该函数时 需要注意以下几点 1 第 4 参数一般用 0 或 FASLE 以精确匹配方式进行查找 2 第 3 参数中的列号 不能理解为工作表中实际的列号 而是指定返回值在查找范围中 的第几列 3 如果查找值与数据区域关键字的数据类型不一致 会返回错误值 N A 4 查找值必须位于查询区域中的第一列 8 多条件查找 多条件查找 如下图所示 要求查询部门为生产 并且岗位为部长的姓名 公式为 LOOKUP 1 0 B2 B9 F2 C2 C9 G2 A2 A9 6 LOOKUP 函数多条件查询写法为 LOOKUP 1 0 条件区域 1 条件 1 条件区域 2 条件 2 查询区域 9 计算文本算式 计算文本算式 如下图 要计算单元格中的文本算式 先单击第一个要输入公式的单元格 定义名称 计算 EVALUATE C2 然后在单元格中输入公式 计算 7 10 合并多个单元格内容 合并多个单元格内容 要连接合并多个单元格中的内容 可以使用 符号完成 如下图 要合并 A 列的姓名和 B 列的电话号码 可以使用公式 A2 B 1 B2 11 合并带格式的单元格内容 合并带格式的单元格内容 合并带有格式的内容时 Excel 默认按常规格式进行合并 但是如果是日期 时间或是其 他有格式的数值 结果就会让人大失所望了 8 如何才能正确连接出需要的字符串呢 其实很简单 C2 公式为 A2 TEXT B2 y 年 m 月 d 日 首先使用 TEXT 函数 把 B 列的日期变成具有特定样式的字符串 然后再与 A 列的姓名连 接 就变成了最终需要的样式 12 比较大小写的单词是否相同 比较大小写的单词是否相同 如果在 A1 和 A2 单元格中分别输入大小写的单词 使用以下公式判断时 Excel 会默认二 者是相同的 A2 B2 9 如需区别大小写 可以使用公式 EXACT A2 B2 EXACT 函数 区分大小写 但忽略格式上的差异 13 提取混合内容中的姓名 提取混合内容中的姓名 如下图 要从 A 列姓名电话中提取出姓名 除了使用高版本的自动填充功能 还可以使用 公式完成 LEFT A2 LENB A2 LEN A2 LENB 函数将每个汉字 双字节字符 的字符数按 2 计数 LEN 函数则对所有的字符都按 1 计数 因此 LENB A2 LEN A2 返回的结果就是文本字符串中的汉字个数 LEFT 函数从文本字符串的第一个字符开始 返回指定个数的字符 最终提取出员工姓名 14 根据身份证号码提取出生年月 根据身份证号码提取出生年月 计算公式为 1 TEXT MID B2 7 8 0 00 00 10 首先使用 MID 函数从 B2 单元格的第 7 位开始 提取出表示出生年月的 8 个字符 结果为 19780215 再使用 TEXT 函数将字符串转换为日期样式 1978 02 15 然后通过 1 计算 将其转换为真正的日期 最后设置为日期格式即可 15 替换部分电话号码 替换部分电话号码 如下图所示 要将手机号码的中间四位换成星号 公式为 SUBSTITUTE B2 MID B2 4 4 1 SUBSTITUTE 函数的用法是 SUBSTITUTE 要替换的文本 旧文本 新文本 替换第几个 先使用 MID 函数取得 B 列号码中的中间 4 位 再用 替换掉这部分内容 最后一个参数使用 1 表示只替换第一次出现的内容 比如第九行的电话号码是最后四位和中间四位相同 如果不指定 1 就会全部替换掉了 16 屏蔽函数公式返回的错误值 屏蔽函数公式返回的错误值 在使用函数公式过程中 经常会返回一些诸如 N A NAME 之类的错误值 要屏蔽这 11 些错误值其实很简单 只需在原公式外侧加上一个 IFERROR 函数就好 IFERROR 函数的用法为 IFERROR 原公式 出现错误时要返回的内容 如果公式正确 就返回原有计算结果 如果公式返回的是错误值 就返回用户指定的显示 内容 17 四舍五入函数 四舍五入函数 ROUND 函数这个想必大家经常用到吧 就是对数值按指定的位数四舍五入 比如 ROUND 8 9 3 就是将 8 9 的计算结果四舍五入到三位小数 结果为 0 889 18 取整的间隔小时数 取整的间隔小时数 计算两个时间的间隔小时数 不足一小时部分舍去 计算加班时经常会用到 说多了都是 泪 TEXT B2 B1 h 19 提取日期时间中的日期值 提取日期时间中的日期值 要从日期时间数据中提取出日期 可以使用以下公式 INT A2 要继续提取时间 只需要做个减法 就欧了 12