一、IF
基础用法:IF(logical_test, [value_if_true], [value_if_false])
IF(判断条件, 判断条件成立返回的值, 判断条件不成立返回的值)
实际费用 | 预算 | 公式输入 | 结果 |
1500 | 900 | =IF(A2>B2, "超出预算", "正常") | 超出预算 |
500 | 900 | =IF(A3>B3, "超出预算", "正常") | 正常 |
IF嵌套 (案例:纸条摸奖 要求:抽到紫色奖励自行车、蓝色奖励微波炉、红色不中奖)
姓名 | 纸条颜色 | 公式输入 | 结果 |
姓名1 | 紫色 | =IF(B2="紫色", "自行车", IF(B2="蓝色", "微波炉", "不中奖")) | 自行车 |
姓名2 | 蓝色 | =IF(B3="紫色", "自行车", IF(B3="蓝色", "微波炉", "不中奖")) | 微波炉 |
姓名3 | 红色 | =IF(B4="紫色", "自行车", IF(B4="蓝色", "微波炉", "不中奖")) | 不中奖 |
注意:当一个公式输入完成后双击单元格右下角或往下拖,如果没有锁定单元格(F4)那么上面B2会自动变为B3、B4,往右拖那么自动变为C2、D2…… 如果锁定列,即$B2,那么往下拖拽$B2将变为$B3、$B4,往右拖拽始终为$B2,B$2则B发生改变,但是2不发生改变,全锁,即$B$2,那么不管往哪拖拽都不会使引用的单元格发生变化。
二、其他逻辑函数
(1) AND (*(乘号) (条件1) * (条件2) * (条件3) ……)
用法:
=AND(判断条件1, 判断条件2, ...)
特性:
并且:一假则假;所有的条件都成立,返回逻辑值TRUE,否则返回FALSE
作用:
扩大用于执行逻辑检验的其他函数的效用
举例:
= AND(TRUE, TRUE, TRUE) 返回 TRUE
= AND(TRUE, TRUE, FALSE) 返回 FALSE
= AND(1, 0, 1, 0) 返回 FALSE
注意:参数的计算结果必须是逻辑值(如 TRUE 或 FALSE)
如果指定的单元格区域未包含逻辑值,则 AND 函数将返回错误值 #VALUE!
(2) OR (+(加号) (条件1) + (条件2) + (条件3) ……)
用法:
= OR(判断条件1, 判断条件2, ...)
特性:
或者:一真则真;任一条件成立,返回逻辑值TRUE,否则返回FALSE
举例:
= OR(TRUE, TRUE, TRUE) 返回 TRUE
= OR(FALSE, FALSE, TRUE) 返回TRUE
= OR(FALSE, FALSE, FALSE) 返回FALSE
(3) NOT
用法:
= NOT(判断条件)
特性:取反:真则假,假则真
举例:
= NOT(TRUE) 返回 FALSE
= NOT(FALSE) 返回 TRUE
(4) XOR
用法:
= XOR(判断条件1, 判断条件2, ...)
特性:仅且只有一个条件为真则真
(5) TRUE、FALSE
用法:
=TRUE() =FALSE()
可以直接在单元格或公式中键入值 TRUE/FALSE,而可以不使用函数。
三、判断公式
(1) IFERROR (常用于规避错误)
用法:
= IFERROR(值或表达式,公式的计算结果为错误时要返回的值)
作用:如果公式的计算结果为错误,则返回您指定的值;否则将返回公式的结果。
注意:如果 value 或 value_if_error 是空单元格,则 IFERROR 将其视为空字符串值 ("")。
(2) IFNA
用法:
= IFNA(值或表达式,值或表达式返回#N/A错误时想要输出的信息)
作用:判断公式是否出现#N/A错误。
(3) IFS (2019和365版本)
用法:
= IFS(判断条件1,真值1,判断条件2,真值2……,判断条件N,真值N)
作用:避免IF函数嵌套。
注意:IFS函数检查是否满足一个或多个条件,且是否返回与第一个TRUE条件对应的值
IFS 函数允许测试最多 127 个不同的条件。
三、SWITCH (2016及以上版本)
基础语法:
= SWITCH(expression,value1,result1,[defult_or_value2,result2],..,[defult_or_value126,result126]
含义:
SWITCH(表达式, 值1, 结果1, [默认值 或 值2, 结果2],…[默认值 或 值3, 结果3])
参数定义:
Expression:必须。 表达式的值将于value1(值1)至valueN(值N)比较
ValueN:valueN(值N)的值与表达式比较
ResultN:resultN(结果N)是valueN(值N)与表达式的结果匹配时返回的值。此参数必须为每一个valueN(值N)设定
defult:默认值。当表达式的值与所有提供的值都不匹配时,函数返回默认值。默认值没有与之对应的结果参数,并且默认值总是函数的最后一个参数
注意:跟据值列表计算一个值(称为表达式),并返回与第一个匹配值对应的结果。如果不匹配,则可能返回可选默认值。不支持范围匹配如:>=,<=数的范围
区别于IFS:IFS可以按区间进行判断,而SWITCH只能按固定值进行判断
还是一样案例,抽到紫色奖励自行车、蓝色奖励微波炉、红色不中奖
姓名 | 颜色 | 公式输入 | 结果 |
姓名1 | 紫色 | =SWITCH(B2, "紫色", "自行车", "蓝色", "微波炉","红色", "不中奖") | 自行车 |
姓名2 | 蓝色 | =SWITCH(B3, "紫色", "自行车", "蓝色", "微波炉","红色", "不中奖") | 微波炉 |
姓名3 | 红色 | =SWITCH(B4, "紫色", "自行车", "蓝色", "微波炉","红色", "不中奖") | 不中奖 |
四、信息类函数
注意:IS 类函数的参数 value 是不可转换的
实际意义:IS类函数在公式检验计算结果时十分有用.当它与函数 IF 结合在一起使用时,可以提供一种方法用来在公式中查出错误值