二维码 购物车
部落窝在线教育欢迎您!

处理条件判断的函数除了IF函数还有CHOOSE、TEXT 、IFS等六个函数

 

作者:E图表述来源:部落窝教育发布时间:2021-04-07 10:47:03点击:194

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

作为EXCEL函数大家庭中的绝对大佬之一,IF函数在众多场合中都有出色的表现。每次遇到需要进行条件判断的情况时,它总能一马当先,手到擒来!但是,也有小弟不服!比如CHOOSE、TEXT、IFS等,都在挑战它在“条件判断”领域中的地位……

 

前言:

如果问任何一位EXCELER,在EXCEL函数的日常使用中,哪些是你认为的常用函数呢?笔者相信,无论说出哪些函数名称,想必一定会包含IF函数。别不承认,IF函数绝对是使用率最高的一个函数了,甚至超过SUM函数和VLOOKUP函数。

但是今天,大家就来看一组实际的案例,本来应该是IF出场的,居然被一堆函数抢了风头。

 

正文:

今天的案例是笔者E图表述的一个做物料控制部门工作的朋友提供的素材,真实性和实用性还是比较高的,案例如下:

 

 

问题:根据计划数量求实际数量,以便采购原材料。

计算规则为:

1.如果“计划数量”小于50时,“实际数量”按最小采购量50记入D列;

2.如果“计划数量”大于500时,“实际数量”按最大采购量500记入D列;

3.如果“计划数量”在50500之间,则“实际数量”按照“计划数量”实际值记入D列。

 

【解法一:常规解法】

看到这样的问题,大家的第一反应应该就是使用IF函数做“多级判断”处理。没有任何问题,这就是一个典型的IF函数案例,答案如下:

 

 

D2单元格输入函数:=IF(C2>=500,500,IF(C2<=50,50,C2))

这段函数就不再赘述了,如果大家在部落窝大家庭中,还没有学会两级IF函数的话,那就报一个系统学习的课程吧。

 

接下来,开始真正的表演吧!!

【解法二:最简解法】

如果说“让一个数字和另一个数字比较后取最大值或者最小值”,对于EXCEL函数高手来说,往往会采用MIN函数或者MAX函数,来替代多层IF的写法,答案如下:

 

 

D2单元格输入函数:=MAX(50,MIN(C2,500))

 

函数解析:

MIN(C2,500),在C2单元格值和500之间取最小值,满足了实际数量不超过500的取值规则;再用=MAX(50,X),在50MIN函数的返回值之间取最大值,得到不小于50的数值,及此完成需求操作。

 

【解法三:另类解法】

 

 

大家有没有看“懵”,有没有内心狂吼一句“What is this?”

 

D2单元格输入函数:=SMALL(CHOOSE({1,2,3},50,500,C2),2)

 

函数解析:

CHOOSE函数是在一组数据中,指定选择第几个的函数,语法如下:

=CHOOSE(index_num,value1,[value2],…)

其中index_num是在value组中指定的序号。

如果大家将index_num的值,换做数列{1,2,3},那么就意味着,大家要CHOOSE提取value组中的第123个值,形成一个新的数组。

 

D2单元格,就形成了{50,500,408}这样的一个组列;再使用SMALL函数,提取第2小的值,就取得了≥50 and 500的值。又如D4单元格,CHOOSE函数返回的数列是{50,500,754},那么SMAll(数列,2)就得到了第2小的值500。大家明白了吗?

 

同理,大家也可以使用LARGE函数处理,公式如下:=LARGE(CHOOSE({1,2,3},50,500,C2),2),原理都是一样的,大家可以自己推敲一下。

 

【解法四:创意解法】

 

 

这个公式中虽然也有CHOOSE,但是和上例不一样,CHOOSE函数只是常规用法。重点是利用了LOOKUP函数的区间取值的方法。

D2单元格输入函数:=CHOOSE(LOOKUP(C2,{0,50,500},{1,2,3}),50,C2,500)

(关于区间取值的LOOKUP函数的用法,大家可以查看《老是加班还没加班费?谁让你不会excel区间查询的三大套路!

 

通过C列单元格的值,在区间{0,50,500}中的落点,得到{1,2,3}中的某一个值,然后利用CHOOSE函数根据这个值在{50C列的值,500}这三个数中提取一个满足规则的值并记入。

 

【解法五:究极解法】

 

有了解法三和解法四做铺垫,大家可以总结出这道题的一个规律,其实就是在{50C列的值,500}中找到一个“中间数”。那么有没有什么方法可以不用这么绕来绕去,直接一些的吗?大家再看一组解法:

 

 

哇哦,原来这才是最简单的方法啊!

D2单元格输入函数:=MEDIAN(50,500,C2)

原来工作表函数中是有一个专门的函数是做“中值”的,就是大家看到的MEDIAN函数,有了前面函数的讲解,相信大家也就明白了这个函数解题的原理了,取三个数中的“中间数”。

 

【解法六:变态解法】

还来?!有了“究极解法”,难道还有更好的解法吗?

 

为了让IF函数退休,笔者也是拼了,不辣、微辣、正常辣、PLUS辣、变态辣,口味一定要全才能开饭馆!

 

 

EXCEL中,TEXT函数一直就有可以替代IF的用法,本例亦如此。

D2单元格输入函数:=--TEXT(C2,"[>500]5!0!0;[<50]5!0;0")

 

函数解析:

TEXT函数判断格式的语法为:[条件1]显示值1;[条件2]显示值3;都不满足返回本身

使用TEXT函数,最重要的一点是要将格式部分写好,这个格式中就有很多需要大家学习的地方:

1.TEXT函数的格式一定要用英文的双引号括起来;

2.TEXT函数中的“[]”,中括号里面是写条件的部分;

3.0”在TEXT函数的格式中是占位符,如果直接使用,则代表一个有效数值,如本例,如果要在[>500]的时候显示500,那么大家不能直接写500,因为此时的00是占位符,而不是实际的0值。所以如上图中,大家需要使用“!”,英文状态下的感叹号是“强制符”,强制显示0值。

及此完成用TEXT函数解题的过程,再用两个负号(减负函数),将文本转换成数值即可。

 

编后语:

今天的题目写完了,其实IF函数是不会退休的,只是大家使用的函数越多越灵活,就会有更多的积累和沉淀,各种运用思路对大家更快速地完成工作是很有帮助的,有时需要迂回,有时需要直接,例如今天的题,还可以用IF的升级版来解决:

=ifs(C3>500,500,C3<50,50,C3,C3)

 

PS:以上函数在EXCEL2016及以上的版本中均可使用。函数和软件一直都在更新换代,大家也要与时俱进啊!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

同事一个函数都没用,仅靠小学算数,分分钟搞定excel多条件判断

Excel大擂台:CHOOSE函数巅峰对决IF函数,你投谁的票?

请也为Excel中的《无名之辈》choose献上一份理解!

IF函数:剥洋葱

版权申明:

本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。