嵌套IF函数失效求助：Excel公式在Sports区域返回FALSE

你的公式失效的核心原因是嵌套IF的逻辑顺序完全错误，导致sports及之后的所有分支根本没有机会被触发。

原公式的问题点 #

看你原公式里的这段逻辑：

IF(A2="colours", IF(B2="red","red", IF(B2="orange","orange", IF(A2="sports",...))))

只有当A2="colours"且B2既不是red也不是orange时，才会进入判断A2="sports"的分支——但此时A2明明是colours，不可能等于sports，所以这个分支永远不会被执行。后面的fighting、planets等所有判断也都被卡在这个错误的嵌套里，最终只能返回FALSE。

优化后的嵌套IF公式 #

我重新梳理了你的需求逻辑，把每个A列大类作为顶层判断，再在大类内处理B/C列的子条件，这样每个分支都能正确触发：

=IF(A2="fruit","fruit",
 IF(A2="instrument","instrument",
 IF(A2="colours",IF(B2="red","red",IF(B2="orange","orange","")),
 IF(A2="sports",IF(B2="soccer","soccer",IF(B2="basketball","basketball","")),
 IF(A2="fighting",IF(B2="taekwando","taekwando",IF(B2="boxing","boxing",IF(B2="blood",IF(C2="mma","mma",IF(C2="ufc","ufc","")),""))),
 IF(A2="planets",IF(B2="earth","earth",IF(C2="dwarf","pluto","")),
 IF(A2="cars",IF(B2="ford","ford",IF(B2="toyota","toyota","")),
 "")))))))

把这个公式输入D2后下拉，就能得到你预期的结果。

更简洁的替代方案：SWITCH函数（Excel 2019及以后版本可用） #

如果你的Excel版本支持SWITCH，用它会比嵌套IF更易读、更易维护：

=SWITCH(A2,
 "fruit","fruit",
 "instrument","instrument",
 "colours",SWITCH(B2,"red","red","orange","orange",""),
 "sports",SWITCH(B2,"soccer","soccer","basketball","basketball",""),
 "fighting",SWITCH(B2,
  "taekwando","taekwando",
  "boxing","boxing",
  "blood",SWITCH(C2,"mma","mma","ufc","ufc",""),
  ""),
 "planets",IF(B2="earth","earth",IF(C2="dwarf","pluto","")),
 "cars",SWITCH(B2,"ford","ford","toyota","toyota",""),
 "")

验证结果 #

用优化后的公式，你的表格D列会返回以下预期结果：

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