Excel常用函数20例,我们一起嗨起来!

1、Excel 常用函数公式20 例，我们一起嗨起来！原创 2017-10-30 祝洪忠 Excel 之家 ExcelHome小 伙 伴 们 好 啊 ， 今 天 老 祝 要 和 大 家 分 享 一 期 常 用 Excel 函 数 公 式 的 用 法，学会这些套路，让工作效率再高一丢丢。1、 IF函 数 条 件 判 断IF 函数是 最常用 的判 断 类函 数 之一，能完成 非 此 即彼 的判 断 。如 下图， 考核得 分的 标 准为 9分，要判断 B列 的 考核 成绩 是 否 合 格 。=IF（B4=9,合 格 ,不 合 格 ）IF， 相当于普通话的“如果”，常 规用法是：IF（判 断的条件,符合

2、条件 时的结果,不符合条件时的结果）2、 多 条 件 判 断如 下 图所示，如果部门为生产、岗位为主操有高温补助 。 在 D列 使用公式：=IF（AND（B2=生 产 ,C2=主 操 ）,有 ,无 ）AND 函 数 对 两 个 条 件 判 断 ， 如 果 同 时 符 合 ， IF函数 返回 “有 ”否则为无。3、 条 件 求 和如 下 图 所 示 ， 使 用 SUM IF 函 数 计 算 一 班 的 总 成 绩 ：=SUMIF(D2:D5,F2,C2:C5)SUMIF 用法是：=SUMIF( 条件区域,指定的求和条件,求和的区域)用通俗的话描述就是：如果 D2:D5 区域的班级等于F2 单元格

3、的 “一班” ， 就对 C2:C5 单元格对应的区域求和。4、 多 条 件 求 和如 下 图 所 示 ， 要 统 计 部 门 为 生 产 ,并 且 岗 位 为 主 操 的 补 助 总 额 。公式为：=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)SUM IFS 用 法 是 ：=SUMIFS( 求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,)5、 条 件 计 数如 下 图 ， 要 统 计 指 定 店 铺 的 业 务 笔 数 。 也 就 是 统 计 B列 中 有 多 少 个 指 定的店铺名称。=COUNTIF(B2:B12,E3)COUNTIF 函数 统

4、计 条 件 区域中，符合指定 条 件 的 单 元 格 个 数 。 常 规用法为： =COUNTIF（条 件 区 域 ,指 定条 件）6、 多条件计数要 求 ： 统 计统 计 部 门 为 生产,并且岗位为主 操 的 人 数公式为：=COUNTIFS（B2:B9,F2,C2:C9,G2）COUNTIFS 函 数统 计 条 件 区域 中 ， 符 合 多个指 定 条件 的单元 格 个 数 。常规用法为：=COUNTIFS（条件 区 域 1 ,指定 条 件 1,条 件区域 2,指定 条件2 ）7、 条件查找VLOOKUP 函数一直是大 众情 人般的存在，函数的语法为 ：VLOOKUP（要找谁,在 哪 儿

5、 找,返回第几列的内容,精确找还 是 近 似 找 ）如 下 图 ， 要 查询F5单元 格中 的员工姓名是什么职务。=VLOOKUP（$F$5,$B$1:$D$10,2,0）使用该函数时，需要注意以下几点：1、 第4 参数一般用0(或FASLE)以精确匹配方式进行查找。2、 第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。3、 如果 查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。4、 查找 值必须位于查询区域中的第一列。8、 多 条 件 查 找如 下 图 所 示 ， 要 求 查 询 部 门 为 生 产 ,并 且 岗 位 为 部 长 的 姓 名

6、。公式为：=LOOKUP(1,0/(B2:B9=F2)*(C2:C9=G2),A2:A9)LOOKUP 函 数 多 条 件 查 询 写 法 为 ：=LOOKUP(1,0/(条 件 区 域 1=条 件 1)*(条 件 区 域 2=条 件 2),查 询 区 域)9、 计 算 文 本 算 式如下图，要计算单元格中的文本算式，先单击第一个要输入公式的单 元格，定义名称 ：计 算 = EVALUATE(C2)然后在单元格中输入公式：=计 算要连接合合 并 A列=A2&B$10、 合 并 多 个 单 元 格 内 容并 多 个 单 元 格 中 的 内 容 ， 可 以 使 用 &符 号 完 成 。 如 下 图

7、 ， 要的 姓 名 和 B列 的 电 话 号 码 ， 可 以 使 用 公 式 ：1&B2合并带有是日期、11、 合 并 带 格 式 的 单 元 格 内 容格 式 的 内 容 时 ， Excel 默 认 按 常 规 格 式 进 行 合 并 ， 但 是 如 果时间或是其他有格式的数值，结果就会让人大失所望了：如何才能正 确 连 接 出 需 要 的 字 符 串 呢 ？ 其 实 很 简 单 ， C2 公 式 为 ：=A2&TEXT（B2, y 年 m 月 d 日 ）首 先 使 用 TEXT 函 数 ， 把 B 列 的 日 期 变 成 具 有 特 定 样 式 的 字 符 串 ， 然 后 再 与 A列 的

8、 姓 名 连 接 ， 就 变 成 了 最 终 需 要 的 样 式 。12、 比较大小 写 的 单 词是否相 同如果在A1 和A2 单 元格中分别 输 入 大 小 写的单词 ， 使 用 以 下 公 式 判断时，Excel会 默 认 二者是相同 的 ：=A2=B2如需区别大小写，可以使用公式：= EXACT(A2,B2)EXACT函 数区分大 小写，但忽略格式上的 差 异 。13、 提取混合内容中的 姓 名如 下 图 ，要从A列姓 名电话中提取出姓名，除 了 使 用 高 版 本 的 自 动 填充功能，还可以使用公式完成：= LEFT(A2,LEN B(A2)-LEN(A2)LEN B 函 数 将

9、每 个 汉 字 ( 双 字 节 字 符 ) 的 字 符 数 按 2 计 数 ， LEN 函 数 则 对 所 有 的 字 符 都 按 1 计 数 。 因 此 “ LENB(A2)-LEN (A2)” 返 回 的 结 果就是文本字符串中的汉字个数。LEFT 函 数 从 文 本 字 符 串 的 第 一 个 字 符 开 始 ， 返 回 指 定 个 数 的 字 符 ， 最终提取出员工姓名。14、 根 据 身 份 证 号 码 提 取 出 生 年 月计算公式为：=1*TEXT(M ID(B2,7,8),0-00-00)首 先 使 用M I D 函 数 从 B2 单 元 格 的 第 7 位 开 始 ，提 取

10、出 表 示 出 生 年 月的 8个 字 符 ， 结 果 为 ： 19780215再使用TEXT 函数将字符 串转换为日期样式：1978-02-15然后通过 *1 计算 ，将其转 换为真正的日期。最后 设 置 为 日 期 格 式 即 可 。15、 替换部分电话号码如下图所示，要将手机号码的中间四位换成星号，公式为： =SUBSTITUTE(B2,M ID(B2,4,4),*,1 )SUBSTITUTE 函 数 的 用 法 是 ：SUBSTITUTE（要替 换 的 文本 ,旧 文 本 ,新文 本 ,替换 第 几 个 ）先 使 用 MID 函 数取 得 B列 号 码 中 的 中间 4 位 ，再 用

11、“ *”替 换 掉这部分内容。最后一个参数使用 1，表示只替换第一次出现的内容。比如第九行的电话号码最后 四位和中间四位相同，如果不指定1，就会全部替换掉了。16、 屏蔽函数公式 返回 的错误值在使用函数公式过 程 中，经常会返回一些诸如#N /A、# NA M E?之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个 IFERRO R 函 数就好。IFERRO R 函 数的用法为：=IFERROR（原公式,出现错 误 时 要 返 回 的 内 容 ）如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就 返回用户指定的显示内容。17、 四 舍 五

12、入 函 数ROUND 函 数 这 个 想 必 大 家 经 常 用 到 吧 ， 就 是 对 数 值 按 指 定 的 位 数 四 舍五入。比如：=ROUND（8/9,3）就 是 将 8/9 的 计 算 结 果 四 舍 五 入 到 三 位 小 数 ， 结 果 为 0. 889。18、 取 整 的 间 隔 小 时 数计算两个时间的间隔小时数，不足一小时部分舍去，计算加班时经常 会用到，说多了都是泪= TEXT(B2-B1 ,h)19、 提 取 日 期 时 间 中 的 日 期 值要从日期时间数据中提取出日期，可以使用以下公式：=INT(A2)要继续提取时间，只需要做个减法，就欧了：20、 生 成 随 机 数RAN D BETW EEN 能 够 在 指 定 范 围 内 生 成 一 组 随 机 数 据 ，对 于 广 大 质 检