Question

EXCEL函数：如果A列同时包含A,B,C,D,E,F,G,H,那么黄色位置上面显示正确，否则显示错误。

如果A列的A,B,C,D,E,F,G是公式形式的话，黄色位置需要仍然正常显示。麻烦了。

Answer 1

E2输入

=IF(SUMPRODUCT(N(COUNTIF(A2:A11,{"A","B","C","D","E","F","G","H"})>=1))=8,"正确","错误")

数组公式，先按住CTRL+SHIFT,最后回车

Answer 2

=IF(AND(COUNTIF(A:A,{"A","B","C","D","E","F","G","H"})),"正确","错误")
这个是普通公式。也可用下面的公式
=IF(AND(COUNTIF(A:A,CHAR(64+ROW(1:8)))),"正确","错误")
不过这个是数组公式，需要按Ctrl+Shift+Enter结束公式输入。

追答

{"A","B","C","D","E","F","G","H"}
这是一个常量数组，是一个一维数组，中间的字母是数组中的项，因为字母是文本，所以要使用双引号。

Answer 3

=IF(COUNT(0/MATCH(CHAR(ROW(65:72)),A:A,))=ROWS(65:72),"正确","错误")
同时按Ctrl+Shift+Enter三键输入数组公式

Answer 4

破案桨倬立式扛吨