导图社区 第九节逻辑函数
检查是否满足一个或多个条件并返回与第一个TRUE条件对应的值是任何可能被计算为TRUE或FALSE的数值或表达式。
编辑于2022-05-13 23:51:49第九节 逻辑函数
价值
具备特殊的性质:函数应用中的条件开关
让函数应用场景呈指数级增长
能够锻炼逻辑能力
条件判断函数-if函数
=IF(logical_test,[value_if_true],[value_if_false])
=if(逻辑判断条件,为True返回值,为false返回值)
判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值
Logical_test
是任何可能被计算为TRUE或FALSE的数值或表达式
逻辑判断条件
为非0数值/True,则等同于True
为0/False,等同于False
为文本显示错误
可为公式
Value_if_true
是Logical_test为TRUE时的返回值
如果忽略,则返回TRUE
office2019显示0
IF函数最多可嵌套七层
Value_if_false
是当Logical_test为FALSE时的返回值
如果忽略,则返回FALSE
office2019显示0
多层逻辑判断从最极端的条件开始
剥洋葱法嵌套公式
层层递进
嵌套1
if(条件,真值返回值,"洋葱1")
"洋葱1"-if(条件,真值返回值,"洋葱2")
"洋葱2"-if(条件,真值返回值,"洋葱3")
"洋葱3"-if(条件,真值返回值,"洋葱4")
……
例
=IF(单元格>100000,5%,(IF(单元格>80000,4%,IF(单元格>50000,3%,0%))))
嵌套2
if(条件,"洋葱A","洋葱B")
"洋葱A"-if(条件,真值返回值,假值返回值)
"洋葱B"-if(条件,真值返回值,假值返回值)
例
=IF(单元格1="S",IF(单元格2<5,"合格","不合格"),IF(单元格2<8,"合格","不合格"))
注意括号
多层条件判断函数-ifs函数
=IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],[logical_test3,value_if_true3]…)
=ifs(条件1,条件1为true返回值,条件2,条件2为true返回值,…)
检查是否满足一个或多个条件并返回与第一个TRUE条件对应的值
Logical_test
是任何可能被计算为TRUE或FALSE的数值或表达式
Value_if_true
如果Logical_test为TRUE,是否返回该值
最后一个条件兜底,可以输入1,或任意大于0数字,返回兜底值
例
=IFS(单元格>100000,5%,单元格>80000,4%,单元格>50000,3%,任意非0数字,0%)
=IFS(单元格1>100000,5%,单元格1>80000,4%,单元格1>50000,3%,任意非0数字,IF(单元格2="试用",2%,"0%"))
逻辑运算函数
and函数
=AND(logical1,[logical2],[logical3],…)
=and(判断条件1,判断条件2,判断条件3,…)
检查是否所有参数均为TRUE,如果所有参数值均为TRUE,则返回TRUE
logical
logical1,logical2,…是1到255个结果为TRUE或FALSE的检测条件
检测内容可以是逻辑值、数组或引用
例
AND(单元格1>=90,单元格2>=90,单元格3>=90)
=IF(AND(单元格1>=90,单元格2>=90,单元格3>=90),"能","否")
or函数
=OR(logical1,[logical2],[logical3],…)
=OR(判断条件1,判断条件2,判断条件3,…)
有任意条件成真,返回true,否则返回false
如果任一参数值为TRUE,即返回TRUE
只有当所有参数值均为FALSE时才返回FALSE
logical
logical1,logical2,…是1到255个结果为TRUE或FALSE的检测条件
检测内容可以是逻辑值、数组或引用
例
=OR(单元格1=100,单元格2=100,单元格3=100)
=IF(OR(单元格1=100,单元格2=100,单元格3=100),"直接晋级","")
屏蔽公式错误值
iferror
=IFERROR(value,value_if_error)
=IFERROR(值,错误值返回值)
如果表达式是一个错误,则返回value_if_error,否则返回表达式自身的值
value
是任意值、表达式或引用
value_if_error
是任意值、表达式或引用
逻辑函数巩固
组合文本函数
地址智能提取
left、find、if、iferror组合
提取省区信息
=LEFT(单元格,FIND(IFERROR(IF(FIND("省",单元格)>0,"省"),"区"),单元格))
组合条件格式
智能高亮显示
MAX、MIN和条件格式联用
判断最大值
=相对引用单元格=MAX(绝对引用区域)
判断最小值
=相对引用单元格=MIN(绝对引用区域)
利用公式亮显最大值所在行
条件格式-新建规则-使用公式确定要设置格式的单元格
条件格式区域是全选,但公式要根据当前选中的单元格确定
选中后,通过左上角观察目前选中哪个单元格
抽奖小程序
“抽奖人员信息投入抽奖箱”
=INDEX($A:$A,10*(ROW($A1)-1)+COLUMN(B$1))
$A:$A
固定从A列提取抽奖名单
绝对引用
10
抽奖箱列宽
ROW($A1)-1
数据从0逐行加1
相对引用
COLUMN(B$1)
数据从1增加至最后一列数值
根据第一个抽奖人员所在行数确定起始值
相对引用
从抽奖箱中随机抽取获奖人员
INDEX(C2:L16,RANDBETWEEN(1,ROUND(COUNTA(A:A)/10,0)),RANDBETWEEN(1,10))
C2:L16
抽奖区域
ROUND(COUNTA(A:A)/10,0)
行数
最后一行人员容易抽不到
也可用ROUNDUP函数但容易抽空
RANDBETWEEN(1,10)
列数随机取数
高亮定位抽奖箱中奖人员
=MATCH($N$3,C$2:C$16,0)<>""
定位到单元格所在列
=MATCH($N$3,$C2:$L2,0)<>""
定位到单元格所在行
match
=MATCH(lookup_value,lookup_array,[match_type]
返回符合特定值特定顺序的项在数组中的相对位置
lookup_value
在数组中所要查找匹配的值
可以是数值、文本或逻辑值,或者对上述类型的引用
lookup_array
含有要查找的值的连续单元格区域
一个数组或是对某数组的引用
match_type
数字-1、0或1
指定了Microsoft将lookup_value与lookup_array中数值进行匹配的方式
条件格式
涉及绝对相对引用、row、column、index、randbetween、round、counta函数
index
=INDEX(array,row_num,[column_num])
在给定的单元格区域中,返回特定行列交叉处单元格的值或引用
Array
单元格区域或数组常量
Row_num
数组或引用中要返回值的行序号
如果忽略,则必须有Column_num参数
column_num
数组或引用中要返回值的列序号
如果忽略,则必须有Row_num参数
=INDEX(reference,row_num,[column_num],[area_num])
在给定的单元格区域中,返回特定行列交叉处单元格的值或引用
reference
对一个或多个单元格区域的引用
row_num
目标单元格在引用区域中的行序号
如果忽略,则必须有Column_num参数
column_num
目标单元格在引用区域中的列序号
如果忽略,则必须有Row_num参数
area_num
指定所要返回的行(row_num)列(column_num)交叉点位于引用区域组中的第几个区域
第一个区域为1,第二个区域为2,依次类推
randbeetween
=RANDBETWEEN(bottom,top)
返回一个介于指定的数字之间的随机数
bottom
是RANDBETWEEN能返回的最小整数
top
是RANDBETWEEN能返回的最大整数
round
=ROUND(number,num_digits)
按指定的位数对数值进行四舍五入
Number
要四舍五入的数值
Num_digits
执行四舍五入时采用的位数
如果此参数为负数,则圆整到小数点的左边
如果此参数为零,则圆整到最接近的整数
counta
=counta(value1,value2)
计算区域中非空单元格的个数
value
value1,value2,…是1到255个参数
代表要进行计数的值和单元格
值可以是任意类型的信息
2022年5月13日
By DRZ