excel if使用(Lookup能做的就是不让你加班)
excel if使用(Lookup能做的就是不让你加班)公式2 =IF(OR(B3="" C3<1888) "" IF(C3<3888 IF(B3="金卡" "随手泡" "夜间灯") IF(C3<8888 IF(B3="金卡" "电磁炉" "摄像头") IF(B3="金卡" "微波炉" "电饭煲"))))公式1 =IF(OR(B3="" C3<1888) "" IF(B3="金卡" IF(C3<3888 "随手泡" IF(C3<8888 "电磁炉" "微波炉")) IF(C3<3888 "夜间灯" IF(C3<8888 "摄像头" "电饭煲"))))使用IF函数分别根据持卡种类和消费金额进行嵌套判断,分别返回相应结果。→ 操作方法在D3单元格输入公式1或公式2,并向下复制:
如何给金卡和银卡客户按消费额派发赠品
某商场元旦促销活动的规则为:凡当月消费满1888、3888、8888元,金卡会员可获赠随手泡、电磁炉、微波炉,银卡会员可获赠夜间灯、摄像头、电饭煲。如图 71‑1所示,如何设置公式根据销售记录派发赠品?
图71‑1 根据客户持卡种类和消费额派发奖品
→ 解决方案1:
使用IF函数分别根据持卡种类和消费金额进行嵌套判断,分别返回相应结果。
→ 操作方法
在D3单元格输入公式1或公式2,并向下复制:
公式1 =IF(OR(B3="" C3<1888) "" IF(B3="金卡" IF(C3<3888 "随手泡" IF(C3<8888 "电磁炉" "微波炉")) IF(C3<3888 "夜间灯" IF(C3<8888 "摄像头" "电饭煲"))))
公式2 =IF(OR(B3="" C3<1888) "" IF(C3<3888 IF(B3="金卡" "随手泡" "夜间灯") IF(C3<8888 IF(B3="金卡" "电磁炉" "摄像头") IF(B3="金卡" "微波炉" "电饭煲"))))
→ 原理分析
不同分支多层逻辑判断
1.首先判断无卡或者消费金额不足1888元,则不派发赠品;
图71‑2 以持卡种类为主树干的逻辑关系图
3.公式2原理与公式1相似,以消费额级别为主树干,在每一级中设置设置金卡、银卡两个分支,将对应的赠品列出,其逻辑关系如图 71‑3所示:
图71‑3 以消费额为主树干的逻辑关系图
→ 解决方案2:
使用辅助区域和查找函数简化公式。
→ 操作方法
步骤1 如图 71‑4所示,在G2:I6单元格区域输入关系对应表:
图71‑4 使用辅助区域简化公式
步骤2 在E3单元格输入以下公式并向下复制:
=IF(B3="" "" LOOKUP(C3 IF(B3="金卡" $G$3:$H$6 $G$3:$I$6))&"")
→ 原理分析
1.使用LOOKUP函数,在升序排列的G3:H6单元格区域中查找C3的数值,并返回小于等于C3的最大值对应的H列或I列的数据。
2.在G3单元格输入0,当C3数值大于等于0且小于1888时,LOOKUP函数将返回对应的H3或I3单元格的数据,并使用空单元格与空文本合并&"",返回空文本。
→ 知识扩展
多层级对应关系逻辑判断问题变通处理
通过本例解决方案1、解决方案2的对比,当需要执行对某一个数据进行多层级别逻辑判断并返回相应值时,可以考虑建立各层级关系对应表结合使用查找引用函数的方法,代替逐级使用IF函数嵌套方案。使用升序排列建立的层级关系对应表,配合LOOKUP或VLOOKUP、INDEX与MATCH函数等进行升序查找,可以达到简化公式和提高运算效率的效果。
例1.判断成绩等级可以使用以下公式:
=LOOKUP(B3 {0 "差";60 "中";80 "良";90 "优"})
=LOOKUP(B3 {0 60 80 90} {"差" "中" "良" "优"})
例2.个人所得税税率查找对应9个应税所得层级判断,将层级和税率存放在A3:B11单元格,假设应税所得在E3单元格,则使用IF函数嵌套的公式为:
=IF(E3>=A11 B11 IF(E3>=A10 B10 IF(E3>=A9 B9 IF(E3>=A8 B8 IF(E3>=A7 B7 IF(E3>=A6 B6 IF(E3>=A5 B5 IF(E3>=A4 B4 B3))))))))
而使用LOOKUP函数的公式为:
=LOOKUP(E3 A3:B11)
End.
来源:Excel技巧网
运行人员:中国统计网小编(itongjilove)
微博ID:中国统计网
中国统计网,是国内最早的大数据学习网站,公众号:中国统计网
http://www.itongji.cn