求取Excel中计算V等级与带对应颜色R等级的两个公式

原有公式失效原因 #

• 查找值与匹配列逻辑错位，部分公式误将单元格区域C5:D5作为查找值，未计算两个数字的乘积
• 参考区域未加绝对引用，公式下拉/复制时会出现区域漂移
• MATCH匹配参数设置错误，不符合区间匹配的需求

公式1：计算V等级（返回1-5数值） #

假设你的规则参考表配置如下：$D$18:$D$22存储1-5的V等级值，$H$18:$H$22存储每个等级对应的乘积区间最小值（按升序排列，比如1、6、11、16、21，覆盖1-25的数值范围），公式如下：

=INDEX($D$18:$D$22,MATCH(C5*D5,$H$18:$H$22,1))

如果你的Excel区域设置使用分号作为参数分隔符，将公式内的逗号替换为分号即可

公式2：计算R等级（返回I/V罗马数字+自动匹配颜色） #

第一步：输出罗马数字等级 #

假设规则参考表$E$18:$E$22存储对应V等级的R等级罗马数字（1对应I、2对应II，以此类推），公式如下：

=INDEX($E$18:$E$22,MATCH(C5*D5,$H$18:$H$22,1))

第二步：配置自动匹配颜色 #

通过条件格式实现颜色自动关联：

• 选中所有存放R等级的单元格，点击「开始」选项卡→「条件格式」→「新建规则」
• 选择「使用公式确定要设置格式的单元格」，输入规则=【当前选中区域的第一个单元格地址】="I"，设置对应字体/填充颜色
• 重复上一步操作，依次给II、III、IV、V四个等级设置对应颜色规则即可

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