我正在制作一份销售表格,其中包含多个决定销售佣金的因素。
如果 C4=Implant,并且 K4 = >30 至 34%,则返回“Tier 1”
看起来很简单,但还有更多......
Implant 30-33% = Tier 1
Implant 34-38% = Tier 2
Implant 39+% = Tier 3
800Ship 30-34% = Tier 1
800Ship 35-40% = Tier 2
800Ship 41+% = Tier 3
Fulfillment 31-35% = Tier 1
等等
有人能帮我解决这个问题吗?在我看来,这似乎很简单,但我就是无法按照需要的方式制定它以返回层数据的值。
答案1
以下是可行的方法:
=IFS(C4="Implant",IFS(K4>=39,"Tier 3",K4>=34,"Tier 2",K4>=30,"Tier 1"
,TRUE,"Implant < 30"
), C4="800Ship",IFS(K4>=41,"Tier 3",K4>=35,"Tier 2",K4>=30,"Tier 1"
,TRUE,"800Shipt < 30"
), C4="Fulfillment",IFS(K4>=50,"Tier 3",K4>=36,"Tier 2",K4>=31,"Tier 1"
,TRUE,"Fulfillment < 31"
)
, TRIM(C4)="","Code is Blank",TRUE,C4&" not found"
)
公式(栏)中的类别可以通过按以下方式换行Alt-Enter
根据需要 删除加粗的代码,TRUE,"Implant < 30"
, ,TRUE,"800Shipt < 30"
, ,TRUE,"Fulfillment < 31"
, ,TRIM(C4)="","Code is Blank"
, 。,TRUE,C4&" not found"
我认为使用查找表不会太难懂,而且更容易维护,例如:
_____|Column A |Column B|Column C|Column D
Row 1|Code | Tier 1 | Tier 2 | Tier 3
Row 2|Implant | 30 | 34 | 39
Row 3|800Ship | 30 | 35 | 41
Row 4|Fulfillment | 31 | 36 | 44
使用公式:
=IFNA(INDEX(TierSht!$B$1:$D$1,MATCH(K4,INDEX(TierSht!$B$2:$D$4,MATCH(C4,TierSht!$A$2:$A$4,0),0),1)),"")
在末尾的空双引号中,插入未选择层级时显示的文本。如果C4
未找到或层级K4
太低,就会出现这种情况。您可以将其替换""
为
IFNA(IF(MATCH(C4,TierSht!$A$2:$A$4,0),"Too Low"),"No Code")
是否需要知道发生了哪种情况。