Excel中不可见浮点数误差的来源探究

INVESTIGATION OF THE SOURCE OF INVISIBLE FLOATING-POINTNUMBER ERROR IN EXCEL

为了获取Excel中存在不可见的浮点数误差的直接证据,采用不同构造方式在Excel中生成了“相同”的小数数列,分别使用“=”和MATCH函数比较了各数列中的数据在修约前后的一致性。通过拆解Excel工作簿文件,获取其内部存储的高精度数据,证明了Excel中的存储精度高于显示精度,这一精度差异在某些情况下会导致不可见的浮点数误差。

In order to obtain direct evidence of the existence of invisible floating-point number errors in Excel,different generation methods were applied to build“identical”decimal number sequences.“=”and MATCH function were used to compare the data in each sequence before and after rounding.High-precision data were obtained by disassembling the Excel workbook file.It is proved that Excel has a higher storage accuracy than display accuracy,which sometimes lead to invisible floating-point number errors.