18岁白手起家创业,26岁身价3000万Excel舍入和取整函数,玩点高层次的行不行?Excel应用技巧:不规则合并单元格之批量填充序列

哪位大神能解释表格函数IF的使用方法?

2015年移动互联网创业,你该牢记这八大忠告!
2015年微信公众号媒体价值研究报告
SUMIF函数五个不同求和技巧,个个都很实用

一、语法

首先看看IF函数的语法

IF(logical_test,value_if_true,value_if_false)

logical_test,逻辑值或者表达式。

value_if_true ,logical_test 为 真 时返回的值。

value_if_false ,logical_test 为假 时返回的值。

逻辑值可以用TRUE和FALSE或者1和0表示。如果第一参数为任意数字,只有当数字为0时表示假,其他数字都表示真,例如=IF(-1,1,0)结果为1,=IF(2,1,0)结果为1,=IF(0,1,0)结果为0.

如果第一参数为表达式,要注意表达式的写法。很多初学者极容易犯的错误,写出数学表达式a=<x<=b。例如一个简单的IF判断题:如果0<=x<=1,y=0.5,否则y=0。新手容易写出这种公式=IF(0=<A2<=1,0.5,0)。这个公式没有语法错误,可以得到结果。我们测试一下看看结果,如图:

可以看到所有的y值都是0,为什么?

这个公式计算步骤是这样的:首先计算0<=0.3,结果为逻辑值TRUE。再计算

TRUE<=1,逻辑值TRUE和FALSE,不能和数字比较大小,所以这个式子结果为FALSE。条件为假,得到value_if_false的值,结果为0.

正确的写法之一=IF(A2<0,0,IF(A2<=1,0.5,0)),如图

实际运用中,很多小伙伴不喜欢这么写,他们喜欢用OR和AND来连接多个条件。

这个题目的另外一种写法=IF(AND(A2>=0,A2<=1),0.5,0)

实际上我们经常会用*和 来代替AND和OR。表示两个条件同时成立,=AND(A2>=0,A2<=1)等效为=(A2>=0)*(A2<=10);

表示两个条件只满足一个即可,=OR(A2>=0,A2<=1)等效为=(A2>=0) (A2<=10).

OR和AND这两个函数我个人一般不会大量使用,其一是表达式的逻辑值容易混乱理不清,其二就是增加了公式的长度。例如下面这个问题:如果A1和B1同时为空,C1为空;如果A1非空,B1为空,C1为1;如果A1为空,B1非空,C1为2;如果A1和B1都不为空,C1为3.如果大量使用AND,公式

=IF(AND(A1='',B1=''),'',IF(AND(A1<>'',B1=''),1,IF(AND(A1='',B1<>''),2,3)))

这条公式属于IF的多重嵌套。多重嵌套的特点之一,最后至少有和IF个数一般多的括号。这里三个IF,可以看到最后有三个括号,这个特点可以帮助我们判断多重嵌套的IF公式是否漏写了括号。

如果不使用AND,公式

=IF(A1='',IF(B1='','',2),IF(B1='',1,3))

这条公式也用了三个IF,第二个IF判断当A1为空时,B1是否为空。第三个IF判断当A1为非空时,B1是否为空。

二、关于IF函数的简写

简写一:IF(logical_test,value_if_true)

这里省略了第三参数,当logical_test为假时,结果为FALSE。这样写的目节省了字符,某些条件下写公式更加简洁。如图,某次考试的成绩如下表,缺考考生成绩不计算分数,求各班的最低分。

E2输入公式

=MIN(IF(($A$2:$A$16=D2)*($B$2:$B$16<>''),$B$2:$B$16))

然后按ctrl shift enter,下拉填充。

MIN函数忽略文本值和空值'',也忽略逻辑值TRUE和FALSE。空单元格会当做0处理,所以这里需要去掉空值。

如果只是求各班的最高分,公式改写为

=MAX(($A$2:$A$16=D2)*$B$2:$B$16),然后按ctrl shift enter,下拉填充。

同样MAX函数函数忽略文本值和空值'',也忽略逻辑值TRUE和FALSE。

简写二:IF(logical_test,value_if_true,)

这里第三参数为0,空起来不写只写一个逗号。这种写法出现在很多公式中,常用函数除了OFFSET函数和SUBSTITUTE函数外,一般都代表0。其中OFFSET第二、第三参数只写逗号代表0,第五参数只写逗号代表对应的引用区间宽度为1。SUBSTITUTE的第三参数只写逗号代表替换所有的旧文本为空值。

三、关于去IF的若干写法

IF函数虽然很简单,但有时候过多的IF嵌套显得公式很长,这个时候我们需要精简公式,下面通过例子说明。

例一,计算劳龄。规则:如果性别为男,年龄小于18岁,劳龄为0,大于等于18岁劳龄从1开始计算,最高劳龄为50。如果性别为女,年龄小于18岁,劳龄为0,大于等于18岁劳龄从1开始计算,最高劳龄为45。如图:

C2输入公式

=MEDIAN(0,B2-17,45 (A2='男')*5),下拉填充。

例二,计算评价结果。规则:分数小于60分为不及格,大于等于60分小于70分为及格,大于等于70分小于80分为良,大于等于80分小于90分为好,大于等于90分为优。如图:

这种题属于经典题,解法一,IF的多重嵌套:

=IF(A2<60,'不及格',IF(A2<70,'及格',IF(A2<80,'良',IF(A2<90,'好','优'))))

解法二,VLOOKUP的近似匹配:

=VLOOKUP(A2,{0,'不及格';60,'及格';70,'良';80,'好';90,'优'},2,1)

解法三,LOOKUP的基本用法:

=LOOKUP(A2,{0;60;70;80;90},{'不及格';'及格';'良';'好';'优'})

解法四,TEXT的多重嵌套:

=TEXT(TEXT(TEXT(TEXT(A2-60,'[>=0];不及格'),'[>=10];及格'),'[>=20];良'),'[>=30]优;好')

解法二和解法三需要注意条件,判断条件为左闭右开区间,形如a<=x<b。如果不是这个条件就要注意写法。再看一个例子,计算快递费。某快递规定,1kg内包含1kg的物品运费为3元,大于1kg小于等于2kg的物品运费为5元,大于2kg小于等于3kg的物品运费为8元。3kg以上的物品,每超过1kg(超重部分向上取整数),运费在8元的基础上再加2元。如图:

B2输入公式

=IF(A2<=3,LOOKUP(3-A2,{0,1,2},{8,5,3}),8 2*CEILING(A2-3,1))

解法四属于IF的一个远方亲戚,TEXT函数。TEXT函数属于百变之王,兼职IF的部分功能,使用这个函数某些情况下能减少公式的长度。如图,把字符X-Y拆成X,X 1,X 2,……,Y(X,Y均为正整数,且Y大于X)

A2公式

=TEXT(LEFT($A$1,FIND('-',$A$1)-1) ROW(A1)-1,'[<='&MID($A$1,FIND('-',$A$1) 1,99)&'];'),下拉填充。

这个公式我不作过多解析,有兴趣的童鞋去查看TEXT函数的高级用法。

某些情况下,CHOOSE函数也可以简化IF函数,如图计算评级

D2输入公式

=LOOKUP(C2,CHOOSE(MATCH(B2,{'重点班','普通班','艺术班','体育班'},),{0,70,80,90},{0,60,70,80},{0,50,60,70},{0,45,55,65}),{'D','C','B','A'})

以上是本人对IF函数的一些理解,当然IF函数还可以生成内存数组,常见于VLOOKUP函数的倒序查询和一对多查询,格式=IF({1,0},区域1,区域2)。由于本人水平有限,这里我就不展开。欢迎各位朋友批评指正,谢谢大家!

责任编辑:18岁白手起家创业,26岁身价3000万