Excel IF函数非精确匹配问题：如何通过取整实现近似匹配

核心思路 #

通过对输入值进行取整处理，将近似值转化为需要的精确匹配值，再进行条件判断，无需依赖完全精确的输入。

方法1：修改原IF公式，加入取整逻辑 #

把原公式中所有的C2替换为ROUND(C2, 2)（四舍五入到两位小数），就能让近似值匹配到对应的阈值。同时修正原公式中明显的笔误（C2=95应为C2=0.95，否则数值逻辑矛盾）：

=IF(ROUND(C2,2)>=1,"0",IF(ROUND(C2,2)=0.85,"56",IF(ROUND(C2,2)=0.86,"52",IF(ROUND(C2,2)=0.87,"48",IF(ROUND(C2,2)=0.88,"44",IF(ROUND(C2,2)=0.89,"40",IF(ROUND(C2,2)=0.9,"36",IF(ROUND(C2,2)=0.92,"28",IF(ROUND(C2,2)=0.93,"24",IF(ROUND(C2,2)=0.95,"16",IF(ROUND(C2,2)=0.96,"12",IF(ROUND(C2,2)=0.97,"8",IF(ROUND(C2,2)<0.85,"0"))))))))))))))

• 效果：输入0.8495849294会被四舍五入为0.85，自动输出56；
• 针对整数场景（如输入89.48282949要识别为89输出7），需补充对应IF条件，比如在公式开头插入：

  IF(ROUND(C2,0)=89,"7",...)
  

方法2：用LOOKUP函数替代多层IF（更简洁高效） #

多层IF嵌套可读性差，用LOOKUP可以直接通过数组映射实现近似匹配，同时整合取整逻辑：

=LOOKUP(ROUND(C2,2),{0,0.85,0.86,0.87,0.88,0.89,0.9,0.92,0.93,0.95,0.96,0.97,1},{"0","56","52","48","44","40","36","28","24","16","12","8","0"})

• 逻辑：第一个参数是四舍五入后的输入值，第二个数组是所有匹配阈值，第三个数组是对应的输出结果，LOOKUP会自动匹配最接近且符合条件的阈值；
• 若需单独处理整数（如89.48），可以拆分逻辑：

  =IF(C2>=10,LOOKUP(ROUND(C2,0),{89,95,96,97},{"7","16","12","8"}),LOOKUP(ROUND(C2,2),{0,0.85,0.86,0.87,0.88,0.89,0.9,0.92,0.93,1},{"0","56","52","48","44","40","36","28","24","0"}))
  

可选取整方式 #

根据实际需求替换ROUND函数：

• ROUNDUP(C2,2)：向上取整到两位小数（如0.841会变成0.85）；
• ROUNDDOWN(C2,2)：向下取整到两位小数（如0.859会变成0.85）。

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