excel计算 0值判断与类型转换

提前说明:该问题我也没能解决,只是作为记录。猜测与强制类型转换、减法精度有关。

问题概括:使用excel的公式进行计算,发现涉及0.07,0.14,0.28这几个数字,出现计算问题,表现形式为7-7不等于0,LOOKUP二分查找不能识别0.07。

文件类型:Microsoft Excel 97-2003 工作表 (.xls)

场景:使用EXCEL的公式进行计算。

公式:=IF(A2<=0.5,IF((A2*100-INT(A2*100))>0,LOOKUP(INT(A2*100)/100+0.01,C:D),LOOKUP(A2,C:D)),IF(A2<=2,IF((A2*10-INT(A2*10))>0,LOOKUP(INT(A2*10)/10+0.1,C:D),LOOKUP(A2,C:D)),IF((A2*2-INT(A2*2))>0,LOOKUP(INT(A2*2)/2+0.5,C:D),LOOKUP(A2,C:D))))

其中,加粗标红(简称为公式1)和加粗标蓝(简称为公式2)的地方,分别出现了两个问题。

————————————————————————————————————————————————-

公式1的作用是,判断A2*100-INT(A2*100)的值是否大于0,本质是判断数字能否被0.01整除。若大于0,不能被0.01整除,则触发公式2。

问题:当A2的取值是区间(0.06,0.07],0.14,0.28,数字0.07,0.14,0.28可以被0.01整除,该判断应为false,实际触发了“大于0”的判断,进而触发公式2。

当A2=0.07,用公式判断IF(A2*100=7),返回TRUE;用公式判断IF((INT)A2*100=7),返回TRUE。执行公式1IF((A2*100-INT(A2*100))>0,返回TRUE;执行公式1IF((A2*100-INT(A2*100))=0,返回FALSE。

逐步执行公式,可以看到中间过程,相减值非0。

《excel计算 0值判断与类型转换》

《excel计算 0值判断与类型转换》

—————————————————————————————————————————-

公式2的作用是,LOOKUP(INT(A2*100)/100+0.01,C:D)在列C中,用二分查找法寻找参数INT(A2*100)/100+0.01的相等值Cx,取D列Dx的值返回。其中,INT(A2*100)/100+0.01作用类似于向上取“整”,不同之处是以0.01为单位,比如,A2=0.061,获得0.07。

问题:当A2的取值是区间(0.06,0.07),通过计算获得的0.07,其实不是0.07,不能被LOOKUP(a,b,c)识别为0.07,最终返回了到0.06的对应值。

以下为A2=0.061的中间过程,可以见到,0.061-0.06不等于0.1。

《excel计算 0值判断与类型转换》

《excel计算 0值判断与类型转换》

《excel计算 0值判断与类型转换》

———————————————————————————————————————————————

补充:0.01,0.02,……,0.5,以0.01为间隔的50个数字作为输入,目前只发现0.07,0.14,0.28出现公式1 的问题,进而发现(0.06,0.07)区间出现公式2的问题。其他区间未逐一验证。

 

 

 

    原文作者:HandsomeChow
    原文地址: https://blog.csdn.net/HandsomeChow/article/details/115336156
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞