一个MAX函数就能解决的事,你居然用IF写了这么长一串!
作者:苗旭来源:部落窝教育发布时间:2019-09-23 10:36:29点击:4541
编按:
哈喽,大家好!说到IF函数,那可真是无人不知无人不晓,在函数界甚至有条件函数之王的美誉。不过江湖上流传着这样一句话“真正的强者,往往深藏不露”。今天我们就带大家认识下这些隐藏极深的强者!
一、MIN函数的运用
“苗老师,我又来烦你啦!”
“怎么啦,小丽,你们人资部不是刚刚下发了新的考勤制度吗?”
“对呀,就是考勤的事。上个月刚发出去的加强考勤的文件,结果有的人迟到太多,工资都被扣光了。后来算算,一次迟到扣100块,最多扣1000块。”
“那如果我这个月迟到了10次,不是后面天天都能迟到了?”
“那也不行,会影响其他评级的。你先别岔开话题,急着算工资呢,快帮我把这个问题解决了吧,不然全公司的工资都发不了了,我记得IF函数可以解决,又等于又大于的,我一着急写不出来。”
“这么大的帽子我可戴不起,我马上就教你一个最最简单的函数,不用IF也能搞定,就是MIN函数。”
“这个不是求最小值的函数吗?”
“不错,这个函数的作用就是找出给定参数中的最小值。函数参数可以是数字、单元格、区域(以某一区域作为参数时,空单元格、文本和逻辑值将被忽略。),用法和SUM没什么区别。”如图1所示。
图1
那怎么将它用到我们的工资表里呢?下图是一张简化的工资表。
图2
里面的扣款金额有0、500、1000,超过1000的,那么超过1000的,就不符合我们的要求了,所以我们需要在公式上进行修改,用上MIN函数。如图3所示。
图3
“这下扣款金额全都在1000以内了,那我可以拿去交差了。”
“先别急,听我跟你说说原理。MIN函数用在这,就是判断这两个数谁大谁小,将得到的数与1000比较,大于1000返回较小值1000;小于1000则返回较小的源数据。”
在计算上限时我们往往可以使用MIN函数代替IF函数,同理计算下限时也可以使用MAX函数代替IF函数,公式会更加简洁。
二、MAX函数的运用
“苗老师,我又双叒叕来了。”
“怎么,上午的问题没解决吗?”
“解决是解决了,可是在做工资的时候,又出了一个问题,就是虽然只扣1000,但是有些人本来工资就低,一扣就扣到了最低保障线以下了,这是违反劳动法的呀。”
“我明白了,你希望在扣工资的同时,还能保证实发工资要大于最低保障。那就用MAX函数吧。”
“早上用完最小,下午用最大。”
“MAX函数的用法和MIN一模一样,这里我就不再赘述了。我们最低工资是1500元,现在要保证扣除罚款后剩余的工资不低于1500元。”如图4所示
图4
那我们就用MAX函数来优化它,如图5所示。
图5
“苗老师你太棒啦,我又可以交差了,工资不会延误啦!”
MAX在这里的原理和MIN一样,也是在运算后比大小,将得到的数与1500进行比较,大于1500就返回较大的源数据,小于1500则返回较大值1500。
MIN和MAX的这种用法很广泛,在人资,财务,以及公司业绩考评等方面都可以使用到它们。
另外还有一种常见的情况:上有封顶,下有保底。
我们可以结合MIN和MAX来完成。如图6所示,我们希望表里的最终得分下限为0,上限为5。
图6
其实MAX还有很多其他的用法,甚至有时候能秒杀VLOOKUP,详情可以参考往期文章《查询界黑马——MAX,竟让查询之王VLOOKUP也甘拜下风?》
在上面的例子中我们使用MIN和MAX,分别取出最大值和最小值。其实还有一种更方便的办法,那就是中位数。
三、中位数的运用
中位数是一种有别于平均数的数学名词,在Excel的函数为MEDIAN。它用于返回一组数值中居于中间的数值,即一半数的值比中位数大,另一半数的值比中位数小。若参数集合中的数字个数是偶数,函数MEDIAN将返回位于中间两个数的平均值。如图7所示。
图7
下面我们用MEDIAN函数来解决图6的问题,直接在B2单元格输入公式:=MEDIAN(A2,0,5),如图8所示。
图8
这里以B2单元格为例,在MEDIAN的参数集合中分别有“-3,0,5”三个数值,这组数值的中位数是0,所以返回0。
总结:
MIN、MAX和MEDIAN和很多Excel的函数一样,用好了对平时工作会有非常大的帮助。如果不使用这三个函数,大部分的朋友都是要用IF进行多层套嵌,不仅效率低,而且极容易出错。
本文配套的练习课件请加入QQ群:264539405下载。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
比VLOOKUP还厉害的MAX《查询界黑马——MAX,竟让查询之王VLOOKUP也甘拜下风?》
IF函数的妙用《IF函数:剥洋葱》
MIN函数的另类用法《三类最常见的统计问题,三个套路全搞定!》