逻辑判断函数只能返回TRUE或FALSE这样的逻辑值,因此为了返回更加直观的结果,通常要根据真假值再为其指定返回不同的值。IF函数即可实现先进行逻辑判断,再根据判断结果返回指定的值。IF函数是日常工作中使用最频繁的函数之一。
函数功能: IF函数用于根据指定的条件判断其 “真” (TRUE)或 “假” (FALSE),从而返回其相对应的内容。
函数语法: IF(logical_test,value_if_true,value_if_false)
参数解析: √logical_test:表示逻辑判决表达式。
√value_if_true:表示当判断条件为逻辑 “真” (TURE)时,显示该处给定的内容。如果忽略,返回TRUE。
√value_if_false:表示当判断的条件为逻辑 “假” (FALSE)时,显示该处给定的内容。IF函数可以嵌套7层关系式,这样可以构造复杂的判断条件,从而进行综合测评。
例1:判断员工本月业绩是否优秀
市场部在月末需要对各销售员的业绩进行评定,评定标准为当业绩大于等于20000元时,评为 “优秀” 。可以使用IF函数进行条件判断。
① 将光标定位在单元格C2中,输入公式: =IF(B2>=20000,"优秀","") ,如图6-19所示。
图 6-19
② 按Enter键,判断B2值,然后返回结果,如图6-20所示。
③ 选中C2单元格,向下填充公式至C10单元格,即可一次性实现对其他销售员本月业绩的评定结果,如图6-21所示。
图 6-20
图 6-21
① 首先判断B2>=20000是否为真。
② 如果第 ① 步结果是真,返回 “优秀” ;否则返回空。
例2:分区间判断业绩并返回不同结果
沿用上一实例,市场部在月末需要对各销售员的业绩进行评定,评定标准为:当业绩大于等于20000元时,评为 “优秀” ,业绩在10000~20000元时,评为 “合格” ,业绩小于10000元时,评为 “不达标” 。可以使用IF函数的嵌套来进行多条件的判断。
① 将光标定位在单元格C2中,输入公式: =IF(B2>=20000,"优秀",IF(B2>=10000,"合格","不达标"))) ,如图6-22所示。
图 6-22
② 按Enter键,判断B2值,然后返回结果,如图6-23所示。
图 6-23
③ 选中C2单元格,向下填充公式至C10单元格,即可一次性实现对其他销售员本月业绩的评定结果,如图6-24所示。
图 6-24
① 判断B2>=20000是否为真,如果是,返回“ 优秀” ;如果不是,则进入第 ② 步的判断。
② 判断B2>=10000是否为真,如果是,返回 “合格” ;否则返回“不达标”。
例3:判断能够获得公司年终福利的员工
某公司规定,当业绩大于10000,并且工龄在1年及以上的员工,具有参加年终旅游的福利。要想知道有哪些员工可以参加年终旅游,可以使用IF函数进行批量判断并得出最终结果。
① 将光标定位在单元格D2中,输入公式: =IF(AND(B2>10000,C2>=1),"是","否") ,如图6-25所示。
② 按Enter键,即可得出第一位员工的参游结果,如图6-26所示。
图 6-25
③ 选中D2单元格,向下填充公式至D8单元格,即可得出其他员工的参游结果,如图6-27所示。
图 6-26
图 6-27
① AND函数分别判断B2单元格中的值是否大于10000,C2单元格中的数值是否大于或等于1,当二者同时满足条件时,返回TRUE;否则返回FALSE。
② 第 ① 步返回TRUE的,返回 “是” 文字;第 ① 步返回FALSE的,返回 “否” 文字。
例4:根据双条件筛选出符合条件的员工
某医院安排医生轮流下乡问诊活动,要求参与者的条件为:男性在60岁以下可参与,女性在50岁以下可参与。可以使用IF函数配合OR函数、AND函数来设置公式进行判断。
① 将光标定位在单元格D2中,输入公式: =IF(OR(AND(C2="女",B2<50),AND(C2="男", B2<60)),"是","否") ,如图6-28所示。
图 6-28
② 按Enter键,即可判断出第一位员工是否符合条件,如图6-29所示。
③ 选中D2单元格,向下填充公式至D10单元格,即可批量判断其他员工是否符合条件,如图6-30所示。
图 6-29
图 6-30
① AND函数判断C2是否为 “女” ,并且B2是否小于50,两条件要求同时满足。
② AND函数判断C2是否为 “男” ,并且B2是否小于60,两条件要求同时满足。
③ 最后使用IF函数判断如果第 ① 步或第 ② 步的任一个满足时,则返回TRUE;否则返回FALSE。返回TRUE的,最终返回 “是” ;第 ① 步返回FALSE的,最终返回 “否” 。
例5:根据员工的职位和工龄调整工资
本例表格统计了员工的职位、工龄以及基本工资。为了鼓励员工创新,不断推出优质的新产品,公司决定上调研发员薪资,其他职位工资暂时不变。加薪规则:工龄大于5年的研发员工资上调1000元,其他的研发员上调500元。
① 将光标定位在单元格E2中,输入公式: =IF(NOT(B2="研发员"),"不变",IF(AND(B2="研发员",C2>5),D2+1000,D2+500)) ,如图6-31所示。
② 按Enter键,即可依据C2和D2中的职位和工龄判断第一位员工是否符合加薪条件以及加薪金额,如果符合加薪条件,再用D2中的基本工资加上加薪金额,即为加薪后的薪资水平,如图6-32所示。
图 6-31
图 6-32
③ 选中E2单元格,向下填充公式至E10单元格,即可批量判断其他员工是否给予调薪,如图6-33所示。
图 6-33
① NOT函数首先判断B2单元是否不是研发员,如果是非研发员,则返回 “不变” ;否则进入下一个IF的判断,即 “IF(AND(B2="研发员",C2>5),D2+1000,D2+500)” 。
② AND函数判断B2单元格中的职位是否为 “研发员” 并且D2单元格中工龄是否大于5,若同时满足返回TRUE;否则返回FALSE。
③ 若第 ② 步返回TRUE,则IF返回 “D2+1000” 的值;若第 ② 步返回FALSE,则IF返回 “D2+500” 的值。
例6:当销量达到平均销量时给予合格
销售部在月末对销售员的销售数量进行统计,要求是只要单人的销售量达到月平均销售量即为达标。
① 将光标定位在单元格C2中,输入公式: =IF(B2>=AVERAGE($B$2:$B$10),"达标","不达标") ,如图6-34所示。
图 6-34
② 按Enter键,即可判断第一位员工销售量是否达到平均水平,如图6-35所示。
③ 选中C2单元格,向下填充公式至C10单元格,即可批量判断其他员工的销量是否达标,如图6-36所示。
图 6-35
图 6-36
① AVERAGE函数对$B$2:$B$10中的数值求平均值。
② 判断B2单元格的值是否大于第 ① 步中得到的平均值。
③ 如果第 ② 步结果为真,返回 “达标” ;否则返回 “不达标” 。
函数功能: IFERROR函数用于当公式的计算结果错误时,则返回指定的值;否则将返回公式的结果。使用IFERROR函数可以捕获和处理公式中的错误。
函数语法: IFERROR(value,vsalue_if_error)
参数解析: √value:表示检查是否存在错误的参数。
√value_if_error:表示公式的计算结果错误时要返回的值。计算得到的错误类型有#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?和#NULL!。
例:解决被除数为空值(或0值)时返回错误值问题
在计算各个产品上旬销量占月销量的百分值时会应用到除法,当除数为0值会返回错误值,而为了避免错误值出现,可以使用IFERROR函数。
① 如图6-37所示在使用公式=C2/B2时,当C列中出现0值或空值时会出现错误值。
图 6-37
② 将光标定位在D2单元格中,输入公式: =IFERROR(C2/B2,"") ,如图6-38所示。
③ 按Enter键,即可返回计算结果。此时可以看到返回正确的结果,如图6-39所示。
④ 选中D2单元格,向下填充公式至D7单元格,即可批量得出其他计算结果(当除数为0值,返回结果为空),如图6-40所示。
图 6-38
图 6-39
图 6-40
=IFERROR(C2/B2,"")
当C2/B2的计算结果为错误值时返回空值;否则返回公式的正确结果。