Excel 条件函数 ifs 和 switch,告别层层嵌套烧脑 if 公式
时间:09-28来源:作者:点击数:
虽然 Excel 2016 已经问世了好几年,不能算新版本了,但是仍有很多同学对其中的一些新函数不甚了解。
其实 Excel 版本每次更新迭代,总会给大众带来不少惊喜,因为微软真正秉承了软件开发的第一要旨:KISS(keep it simple, stupid)。公式越来越简单、模块越来越智能,对用户的技能要求越来越低……
所以不要辜负了这些更新,今天就来讲讲 2016 中的两个新函数 IFS 和 SWITCH。
一、IFS 函数:
作用:
- 检查是否满足一个或多个条件,且返回符合第一个 TRUE 条件的值。
语法:
- IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
参数:
- logical_test1:必需,计算结果为 TRUE 或 FALSE 的条件。
- value_if_true1:必需,当 logical_test1 的计算结果为 TRUE 时要返回结果。可以为空。
- [logical_test2..logical_test127]:可选,计算结果为 TRUE 或 FALSE 的条件。
- [value_if_true2...value_if_true127]:可选,当 logical_testN 的计算结果为 TRUE 时要返回结果。每个 value_if_trueN 对应于一个条件 logical_testN。可以为空。
二、SWITCH 函数:
作用:
- 根据值列表计算一个值(称为表达式),并返回与第一个匹配值对应的结果。如果不匹配,则返回可选默认值。
语法:
- SWITCH(表达式, value1, result1, [default 或 value2, result2],…[default 或 value3, result3])
参数:
- 表达式:必需,表达式是将与 value1…value126 比较的值(如数字、日期或某些文本)。
- value1:必需,要与表达式比较的第一个值。
- result1:必需,当 value1 参数与表达式匹配时,返回的第一个结果。
- [default]:可选,在表达式中没有找到匹配值时返回的默认值。
- [value2...valueN]:可选,要与表达式比较的第 2 至第 126 个值。
- [result2...resultN]:可选,value 参数与表达式匹配时,返回的的第 2 至第 126 个结果。
案例 1:
某校按学生文理选科分成了 4 个班,请按照 E 列中的规则批量替换班级名称。效果如下图 2 所示。
解决方案 1:IFS 函数
1. 在 C2 单元格输入以下公式 --> 拖动下拉复制公式即可:
=IFS(A2="一班","历史班",A2="二班","政治班",A2="三班","物理班",TRUE,"化学班")
公式释义:
- A2="一班","历史班":为一组逻辑条件及其对应的值;依次类推
- TRUE,"化学班":这是最后一组逻辑条件和值;当班级为“四班”时,前面三个条件都不符合,因此逻辑值均为 FALSE,那么这里的 TRUE 就是参数中的第一个 TRUE 值,就会返回其对应的值“化学班”
解决方案 2:SWITCH 函数
1. 在 D2 单元格输入以下公式 --> 拖动下拉复制公式:
=SWITCH(A2,"一班","历史班","二班","政治班","三班","物理班","化学班")
公式释义:
- 较之 IFS 函数,SWITCH 函数更加精简之处在于“A2”只要写一次,后面的 value 和 result 配对只要直接写在 "" 内写值即可
- 而且“化学班”作为默认值,连配对的 value 都不需要写,只要前面都不符合的就是“化学班”
案例 2:
虽然上例中 IFS 函数比 SWITCH 略复杂,但 IFS 可以按区间查找,而 SWITCH 则只能查找固定值。
按下图中 E 列的规则将分数替换成对应的等级,效果如下图 2 所示。
解决方案:
关于区间查询,有多种解决方案,比如大家可以参考我之前写过的这些:
- Excel 高手们青睐的小众函数 frequency,究竟有多强大?
- Excel函数(16)–lookup函数按区间查找
如果对函数掌握不多的同学,可能会用多个 if 嵌套。虽然最终也能达到目的,但是极力不推荐,因为非常不优化。
如果实在不想学其他函数,非用 if 不可,那就用今天教的 ifs 来替代吧,用法和 if 类似,但是免去了大量烧脑嵌套,省时省力还不容易出错。
1. 在 C2 单元格中输入以下公式,下拉复制公式:
=IFS(B2<60,"不及格",B2<70,"差",B2<80,"中",B2<90,"良",TRUE,"优")