Google Sheets风险矩阵：计算值为0时显示指定提示文本的问题

原公式的错误点 #

• 条件判断错误：原IF函数中用了字符串"E7=0"作为条件，这是文本而非逻辑判断，Excel会直接将其视为TRUE，导致逻辑完全错误
• 公式结构错误：用分号将IF函数与风险值计算分开，属于语法错误，Excel无法识别这种拆分写法
• 返回值错误：原IF的返回值写为"E7"，这是文本字符串，而非单元格的计算结果

正确公式写法 #

基础版本（直接嵌套）

=IF(SWITCH(C7,"Very Likely",4,"Likely",3,"Unlikely",2,"Very Unlikely",1,"Unknown/Unsure",0,0)*SWITCH(D7,"Catastrophic",4,"Critical",3,"Moderate",2,"Negligible",1,"Unknown/Unsure",0,0)=0,"Seek Additional Resources",SWITCH(C7,"Very Likely",4,"Likely",3,"Unlikely",2,"Very Unlikely",1,"Unknown/Unsure",0,0)*SWITCH(D7,"Catastrophic",4,"Critical",3,"Moderate",2,"Negligible",1,"Unknown/Unsure",0,0))

简化版本（用LET函数避免重复计算）

如果你的Excel版本支持LET函数（Office 365/2021及以上），可以用以下更简洁的写法：

=LET(
    riskValue, SWITCH(C7,"Very Likely",4,"Likely",3,"Unlikely",2,"Very Unlikely",1,"Unknown/Unsure",0,0)*SWITCH(D7,"Catastrophic",4,"Critical",3,"Moderate",2,"Negligible",1,"Unknown/Unsure",0,0),
    IF(riskValue=0,"Seek Additional Resources",riskValue)
)

逻辑说明 #

1. 先通过两个SWITCH函数分别计算发生概率和影响程度的得分，相乘得到风险值
2. 判断风险值是否为0（即任意一方选择了"Unknown/Unsure"）
3. 若风险值为0，显示Seek Additional Resources；否则显示计算得到的风险数值

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