EXCEL 里面 如果值为A,B,C,D,E 那么等于1；如果值为F,G,H,I,K那么等于2,怎么设置公式

Question

如题所述

Answer 1

可以先构造一个辅助区域，如图所示。然后输入公式=LOOKUP(A7,$C$7:$C$16,$D$7:$D$16)，向下填充即可。

Answer 2

=if(or(A1="A",A1="B",A1="C",A1="D",A1="E"),1,if(or(A1="F",A1="G",A1="H",A1="I",A1="K"),2,""))
或者
=lookup(A1,{"A","B","C","D","E","F","G","H","I","K"},{1,1,1,1,1,2,2,2,2,2}
再或者
=IF(MATCH(A1,{"A","B","C","D","E","F","G","H","I","K"},0)>5,2,1)
这都假设是没有其他情况的本回答被提问者采纳

Answer 3

=IF(单元格="A",1,IF(单元格="B",1,IF(单元格="C",1,IF(单元格="D",1,IF(单元格="E",1,IF(单元格="F",2,IF(单元格="G",2,IF(单元格="H",2,IF(单元格="I",2,2)))))))))

Answer 4

=IF(A1={"A";"B";"C";"D";"E"},1,IF(A1={"F";"G";"H";"I";"J"},2,""))追问

你这个公式只能反映出值为A与F 的情况，不能够反应到BCDE GHIJ的情况

追答

=IF(SUMPRODUCT(--(A1={"A";"B";"C";"D";"E"})),1,SUMPRODUCT((IF(--(A1={"F";"G";"H";"I";"J"}),2,""))))

Answer 5

用两次if即可函数！具体的很难表示！很遗憾！