学过函数的人应该都知道,Excel里面有一个经常会碰到的条件判断函数,那就是IF函数。
有了IF函数,Excel才拥有了逻辑,拥有了大脑。假如Excel只能留下一个函数,那我选择IF。
今天的内容我们将于大白话的形式,从最基础的语法开始,一步步地深入讲解 IF 函数的用法。
01.
基础语法
IF函数有3个参数,它的基础语法为:
IF(logical_test, [value_if_true], [value_if_false])IF
用中文翻译过来就是判断是否为对,若判断结果为“对”则执行此命令,若判断结果为“错”则不执行此命令。
02.
TRUE和FALSE的应用
IF函数对于逻辑的判断源于TRUE和FALSE。
❶ 最基础的结果
我们先简单写两个公式:
① =IF(TRUE,'a','b'),它的结果返回a
② =IF(FALSE,'a','b'),它的结果返回b
先来说说IF公式中的第一个参数TRUE或FALSE。上面这两个公式是IF函数的根本,所有的条件判断都要返回结果TRUE或FALSE,判断对与错。我们以前见过的条件判断可能是“900>500”这种,它返回结果就是TRUE。
有了第一个参数判断,于是根据IF的语法,当结果为TRUE时返回第2个参数“a”,若为FALSE则返回第3个参数“b”。
我们再来看两个公式:
① =IF(1,'a','b'),它的结果返回 a
② =IF(0,'a','b'),它的结果返回 b
第一个参数明明不是TRUE和FALSE,怎么也可以得到计算结果呢?
这里要讲一个知识点:在逻辑判断时,所有“非0数字”都代表TRUE,而“数字0”代表FALSE,在做数字计算时,TRUE相当于数字1,FALSE相当于数字0。
那么,IF(1,'a','b') 里面的1就相当于TRUE,而且不仅可以写成1,还可以写成其他任何非0数字。
例如,IF(728,'a','b')、IF(3.1415926,'a','b')、IF(-365,'a','b')3个函数公式的结果都返回 'a'。
❷ TRUE与FALSE的结果生成
我们的每一个判断,都会生成一个逻辑结果,如下图所示。
TRUE与FALSE的结果生成
对于数字大小的判断:
(1)公式“=1500>900”,返回结果为TRUE
(2)公式“=500>900”,返回结果为FALSE
对于文本字符的判断:
(1)公式“=LEFT('abc')='a'”,判断abc字符串的第一个字符是否为a,返回结果为TRUE。
(2)公式“=LEFT('abc')='b'”,判断abc字符串的第一个字符是否为b,返回结果为FALSE。
其他还有很多种判断,就像我们平常说话,问1500是否大于 900,那么回答一定是“是”或 “否”。
03.
通过逻辑判断是否超出预算
在工作中可能判断是否超出预算,以下将通过分步输入的方式,来演示具体怎样写一个IF函数的公式。
❶ 案例:超预算判断
前面讲的都是基础,现在用IF函数来实战。下图中,C列是实际费用,D列为公司的预算900元,那么花费的金额是否超过预算呢?我们先做分析。
超预算判断
首先,写下“=IF(”,然后开始判断,如果实际费用大于预算(=IF(C19>D19,),就标注超出预算(=IF(C19>D19,' 超出预算 ')。
出现其他情况说明是正常的(=IF(C19>D19,' 超出预算 ','正常 ')。
整个公式的逻辑就是我们判断的逻辑,口中说着就能写出来,最后加上反括号。
完整公式如下:
=IF(C19>D19,' 超出预算 ',' 正常 ')
将公式向下复制到E20单元格,如上图所示,第19行的1500超预算了,所以返回“超出预算”,第20行的500在预算范围内,所以显示“正常”。
❷ 分数判断
对于考试分数我们做一个简单判断,大于80分记为“优”,剩下的记为“其他”。如下图所示,按照逻辑我们在D23单元格中输入公式“=IF(C23>80,' 优 ',' 其他 ')”。
分数判断1
然后将公式向下复制到D26单元格,很明显90分是优,70、80都是其他,但75分得到的结果也是优!明显不正确。
仔细观察格式,75所在单元格的左上角有一个“小绿帽子”。“小绿帽子”在这里表示75并不是一个纯数字,而是“文本型数字”。
在比较大小时,所有的文本都大于数字,所以这个文本型的75是大于一切纯数字的。
那么,现在该怎么办?
75既然“化了妆”,我们就让它把“妆”卸掉,回归到数值型。
将文本型数字转化成数值型,怎么转?用减负(--)法,如下图所示,于是公式就变成:
=IF(--C23>80,' 优 ',' 其他 ')
分数判断2
修改后,表格中对75的判断就正确了,虽然解决了问题,但还是要提醒一下:我们在作基础数据表时,尽量让数据格式保持一致,简化后面的操作。
❸ 案例 :身份证号信息判断
判断每个人是否是80后,出生年份在1980年及以后的判定为80后,剩下的判定为80前。而身份证号码中,第7~10位是出生年份,也就是“MID( 身份证号 ,7,4)”可以提取出生年份了。
E29单元格中输入公式
=IF(MID(C29,7,4)>=1980,'80 后 ','80 前 ')
我们来看看结果,29、30行的两位同志分别是80年和85年生人,明显是80后,而31、32行的同志,都是79年生人,应该是80前,为什么也显示80后?如下图所示。
80后判断1
观察表格可以发现,出现问题的两个单元格的数据为文本型数字。
使用MID函数提取的数字是文本型,将文本型变成数值型怎么操作?
如下图所示,公式可变为:
=IF(--MID(C29,7,4)>=1980,'80 后 ','80 前 ')
80后判断2
我们进一步判断性别,身份证号的倒数第2位,也就是正数第17位是表示性别的。
其中奇数1、3、5、7、9表示男性,偶数2、4、6、8、0表示女性。使用IF函数怎么判断?
再次观察数字的规律,我们可以充分使用MOD函数。用数字除以2取余数,是不是就能得到1、0两个数字的循环?
如果余数为1,说明数字是奇数,也就是1、3、5、7、9,表示男性,其他都是女性。
顺着这个思路将公式写下来。
第一步取数字:MID(C29,17,1)。
第二步取余数:MOD(MID(C29,17,1),2)。
第三步判断奇偶性:MOD(MID(C29,17,1),2)=1。
第四步IF函数返回结果。
如下图所示,F29单元格的最终公式为:
=IF(MOD(MID(C29,17,1),2)=1,' 男 ',' 女 ')
判断性别
将F29单元格的公式向下复制到F32单元格,可以看到结果完全正确。
到这里还没完,我们还可以给它“瘦身”:
=IF(MOD(MID(C29,17,1),2),' 男 ',' 女 ')
把“=1”删除,结果仍然完全一致,为什么?
本文开始时已经铺垫过了,所有的非0数字代表TRUE,数字0代表FALSE。而MOD部分的结果恰好就是1和0,我们可以将其看成TRUE和FALSE,不必再判断是否等于1,直接就可以返回男和女。
“会用”与“高手”之间,有时就差这两个字符。
本文内容摘录于
北京大学出版社新书
《白话聊Excel函数应用100例》
▼
千锤百炼:案例精选自ExcelHome热门课程的实战案例,源自4年的实战教学和2年的反复打磨 。
触类旁通:精选70余个常用函数,以深入浅出的代码讲解剖析,从单个函数用法到组合用法,使“浅者不觉深,深者不觉浅”。
首屈一指:由微软全球有价值专家潜心打造,多年职场经验和教学经验无保留分享。
融会贯通:轻松诙谐的教学方式和层层递进的讲解让读者看得懂、学得会、用得巧!
ExcelHome打造的Excel函数实战指南。以降低学习曲线为宗旨,真正能看得懂、学得会、用得巧的函数实战书。
联系客服