思路:假设A1为原数据,A2是需要保留的有效数字的个数,A3是根据需要保留的有效位数,计算出该数值应保留的小数位数。最后用ROUND(A1,A3)计算得到结果。
方式一:
计算A3的公式:常数+A2*2-LEN(INT(ABS(A1)*(10^(常数+A2))))
公式参数介绍:常数即为用户自己设置的一个大于0的整数。
公式优点:简短,不用复杂的判定语句。
公式缺点:常数取值过大时(例如取值15),因为要计算10的15次方,容易溢出EXCEL的计算位数上限,从而导致公式出错;而常数取值过小,当整数位为0时,有可能造成A1乘以10的次方扩大后整数部分依然没有出现需保留的数字而导致公式出错,不适用于原始数据非常大(整数位数过多)或非常小(无整数位,小数点后的0个数过多)的情况计算。
方式二:
计算A3的公式:IF(INT(A1)=0,A2-2+LEN(ABS(A1))-LEN(ABS(A1)*10^(LEN(ABS(A1))-2)),A2-LEN(INT(ABS(A1))))
公式优点:没有数据长度的上下限制。
公式缺点:语句较长,需要引入判断整数位是否有数值来进行不同的公式计算。
特别说明:如果A1是用户自己输入的值,则公式无需改变;如果A1是由其他单元格计算得到的值,则很有可能出现计算浮点,那么每次在用LEN函数进行长度计算前,都需要用ROUND函数去掉浮点,否则公式会有概率出错。这个是Excel的硬伤,没办法改变的。