Excel评级公式异常：非投资级转投资级判断始终返回N

问题根源 #

1. 大小写不匹配：公式中使用的是大写评级标识（如BB+），但单元格实际存储的是小写值（如bb+），直接文本匹配会失败。
2. AND函数不支持数组逻辑判断：Ratings!C30={...}这种写法会返回一个布尔数组，AND函数无法识别数组中的单个匹配项，仅会判断整个数组是否全为TRUE，导致逻辑失效。

修复方案 #

方案1：使用COUNTIF统一匹配（兼容性好）

将单元格值转换为大写后，用COUNTIF检查是否属于目标评级列表：

=IF(AND(COUNTIF({"BB+","BB","BB-","B+","CCC+","CCC","CCC-","CC","C","D"},UPPER(Ratings!C30))>0,COUNTIF({"AAA","AA+","AA","AA-","A+","A","A-","BBB+","BBB","BBB-"},UPPER(Ratings!BK30))>0),"Y","N")

• UPPER()：将单元格中的小写评级转换为大写，消除大小写差异。
• COUNTIF(...)>0：只要转换后的值在目标列表中存在，就返回TRUE。

方案2：OR嵌套逐个判断（逻辑直观）

通过OR函数逐个匹配评级，同时用UPPER统一大小写：

=IF(AND(OR(UPPER(Ratings!C30)="BB+",UPPER(Ratings!C30)="BB",UPPER(Ratings!C30)="BB-",UPPER(Ratings!C30)="B+",UPPER(Ratings!C30)="CCC+",UPPER(Ratings!C30)="CCC",UPPER(Ratings!C30)="CCC-",UPPER(Ratings!C30)="CC",UPPER(Ratings!C30)="C",UPPER(Ratings!C30)="D"),OR(UPPER(Ratings!BK30)="AAA",UPPER(Ratings!BK30)="AA+",UPPER(Ratings!BK30)="AA",UPPER(Ratings!BK30)="AA-",UPPER(Ratings!BK30)="A+",UPPER(Ratings!BK30)="A",UPPER(Ratings!BK30)="A-",UPPER(Ratings!BK30)="BBB+",UPPER(Ratings!BK30)="BBB",UPPER(Ratings!BK30)="BBB-")),"Y","N")

方案3：动态数组版本（适用于Excel 365/2021）

用MATCH+ISNUMBER判断值是否在列表中：

=IF(AND(ISNUMBER(MATCH(UPPER(Ratings!C30),{"BB+","BB","BB-","B+","CCC+","CCC","CCC-","CC","C","D"},0)),ISNUMBER(MATCH(UPPER(Ratings!BK30),{"AAA","AA+","AA","AA-","A+","A","A-","BBB+","BBB","BBB-"},0))),"Y","N")

内容的提问来源于stack exchange，提问作者StMatthias