|
2学分
本帖最后由 SEh0906 于 2019-10-22 12:24 编辑
条件:
1.如果产品零售价不在定价范围内为疑问a(售出商品的商家为百货店则可超出定价范围的1.2)
2.如果产品的零售价/最低成交价>2.1(产品种类为a的产品,此比例为1.3,种类为d的产品,比例为1.8),为疑问b
3.如果产品的成交价>零售价,为疑问c
大致条件如上所述,目前我设置的公式只能单一显示疑问a,疑问b和疑问c,但是如果同时满足条件1和条件2,能否显示成疑问a/b?这种多重的情况,一共有四个(ab,ac,bc,abc),要怎么设置一个比较精简的公式能同时满足单个显示以及复合显示呢?
详细请看附件
目前设置的具体公式为:=IF(AND(IF(OR(C2="",C2=0,C2<(INDEX(数据源!D:D,MATCH(B2,数据源!B:B,0))),IF(ISNUMBER(FIND("百货",A2)),C2>(INDEX(数据源!E:E,MATCH(B2,数据源!B:B,0)))*1.2,C2>(INDEX(数据源!E:E,MATCH(B2,数据源!B:B,0))))),"疑问a","")="疑问a",IF((AND(IFERROR(C2/D2,"0")=1,IFERROR(D2/E2,"0")=1,IFERROR(E2/F2,"0")=1,C2<>0,C2<>""))=FALSE,"有疑问","")="有疑问"),"疑问a",IF(AND(IF(OR(AND(INDEX(数据源!C:C,MATCH(B2,数据源!B:B,0))="a",IFERROR(C2/E2,"")<>"",IFERROR(C2/E2,"")>2.1),AND(INDEX(数据源!C:C,MATCH(B2,数据源!B:B,0))="d",IFERROR(C2/E2,"")<>"",IFERROR(C2/E2,"")>1.8),AND(AND(INDEX(数据源!C:C,MATCH(B2,数据源!B:B,0))<>"a",INDEX(数据源!C:C,MATCH(B2,数据源!B:B,0))<>"d"),IFERROR(C2/E2,"")<>"",IFERROR(C2/E2,"")>1.3)),"疑问b","")="疑问b",IF((AND(IFERROR(C2/D2,"0")=1,IFERROR(D2/E2,"0")=1,IFERROR(E2/F2,"0")=1,C2<>0,C2<>""))=FALSE,"有疑问","")="有疑问"),"疑问b",IF(OR(AND((IFERROR(C2/E2,""))<>"",(IFERROR(C2/E2,""))<1),AND((IFERROR(C2/D2,""))<>"",(IFERROR(C2/D2,""))<1)),"疑问c","")))
|
最佳答案
查看完整内容
=IF(SUM(D2:F2)=0,"",IFERROR(LOOKUP($C2,OFFSET(数据源!D$1:E$1,MATCH($B2,数据源!$B:$B,0)-1,)*IF(ISERROR(FIND("百货",$A2)),1,{0.8,1.2})+{0,0.01},{"","疑问a"}),"疑问a")&IF($C2>E2*VLOOKUP(VLOOKUP(检查!$B2,数据源!$B:$C,2,0),{"a",2.1;"b",1.8;"c",1.3},2,0),"疑问b","")&IF(OR(C2
|