Question

EXCEL多重判断问题，请高手指点！！！

单元格A1,B1。
A1有14种可以选填的内容（A,AA,AAA,AAAA,B,BB,BBB,BBBB,C,CC,CCC,CCCC，D,DD）
如何定义B1,若A1中填写了上述14种选项之一时，返回值为空（""）,否则返回值为假（"错误"）。

Answer 1

一，笨办法
=IF(OR(A1="A",A1="AA",A1="AAA",A1="AAAA",A1="B",A1="BB",A1="BBB",A1="BBBB",A1="C",A1="CC",A1="CCC",A1="CCCC",A1="D",A1="DD",A1="DDD",A1="DDDD"),"","错误")

二，动脑筋的办法
=IF(AND(CODE(A1)<69,CODE(A1)>64,A1=REPT(LEFT(A1,1),LEN(A1)),LEN(A1)<5),"","错误")
代码短了不少，如果你还有E,EE,F,FF...用这个办法就有优势了。
不过这个办法，ABCD不能用小写。

Answer 2

=IF(ISNA(HLOOKUP(A1,{"A","AA","AAA","AAAA","B","BB","BBB","BBBB","C","CC","CCC","CCCC","D","DD"},1,FALSE)),"错误","")

Answer 3

可以这样，b1=IF(ISNA(VLOOKUP(A1,选填内容,1,FALSE)),"错"，"")
其中选填内容你得另一些单元格填入（A,AA,AAA,AAAA,B,BB,BBB,BBBB,C,CC,CCC,CCCC，D,DD）然后选中这些单元格
不明白m我
知道意思了加分 谢谢

Answer 4

新建1个表,第1列输入选填的内容,第二例为空.假定这个表取名为"表1",用下面这个公式:
vlookup(a1,表1,2,false),
若能找到,则返回值为空,若不能找到,则返回错误代码

Answer 5

=if(isna(lookup(a1,{"A","AA","AAA","AAAA","B","BB","BBB","BBBB","C","CC","CCC","CCCC"，"D","DD"})),"错误","")