22万粉丝共同关注
更多Excel/Word/PPT一分钟动画教程
请点左下角的“阅读原文”
如何给金卡和银卡客户按消费额派发赠品
某商场元旦促销活动的规则为：凡当月消费满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元，则不派发赠品；
2．公式1以持卡种类为主树干，在金卡、银卡两个分支中分别将几个消费额级别的赠品列出，其逻辑关系如图 71‑2所示：
图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)
版权所有 转载须经Excel技巧网许可
22万粉丝共同关注
更多Excel/Word/PPT一分钟动画教程
请点左下角的“阅读原文”
