Excel中的浮点数 [英] Floating-point number in Excel

问题描述

我的excel表单中有一个double值的问题。
double值是宏计算的结果。
它以单元形式显示为 1.0554 。
单元格格式是显示4位小数的数字。如果我选择更多的小数，它将显示尾随0（EG 1.05540000 ）。

我的问题是我用jxls（使用Apache POI）阅读这个文件，我得到的值不是 1.0554 但是 1.0554000000000001
我已经将我的xls转换为xlsx，直接在xlsx（这是一个包含zip文件xml文件，ascii文件），保存的值是 1.0554000000000001 ;但即使我格式化我的单元格作为一个32位十进制的数字，Excel仍然显示 1.0554000 ... 000 没有任何结尾1

所以我想在xls中，实数也保存为1.0554000000000001而不是1.0554。

那么如何在excel中显示真实的原始值呢？或强制excel保存1.0554而不是1.05540000 ... 01？或与宏检查，显示值不是原始值？...

解决方案

浮点舍入错误进场只要你转换一个不能完全代表的数字。它会随着计算而增加。

1.0554最接近的可表示值是1.0553999999999998937738610038650222122669219970703125。与1.0554000000000001最接近的可表示值是1.055400000000000115818465928896330296993255615234375 - 这可能是您的宏计算出来的。坏消息是你没有得到你想要的价值。好消息是，在现实生活中，这种差异经常与实际情况相比太小而不重要，甚至无法衡量。一般来说，你不会想要打印的确切值。 Java中的默认 toString 行为是产生将转换为内部值的最短十进制扩展。这通常也不是您想要的最终用户显示。

在大多数情况下，正确的做法正是您在Excel中所做的 - 只显示你认为的数字对你正在做的事情很重要。在Java中，可以使用 DecimalFormat 将 double 转换为 String 控制显示的数字。

I've a little problem with a double value in my excel sheet. The double value is the result of a macro computation. It is displayed into the cell as 1.0554. The cell format is Number with 4 decimal points displayed. If I choose more decimal, it will be displayed with trailing 0s (E.G. 1.05540000).

My problem is I'm reading this file with jxls (which is using Apache POI), and the value I'm getting is not 1.0554 but 1.0554000000000001 (the last 1 is at the 16th digit).

I've convert my xls to xlsx, check directly inside the xlsx (which is a zip file containing xml files, so ascii file) and the saved value is 1.0554000000000001; but even if I format my cell as a number with 32 decimal, excel still displays 1.0554000...000 without any trailing 1.

So I suppose that in xls, the real number is also saved as 1.0554000000000001 and not 1.0554.

So how to display in excel the real raw value? or to force excel to save 1.0554 instead of 1.05540000...01? Or to check with macro that the display value is not the raw value?...

解决方案

Floating point rounding error comes into play as soon as you convert a number that cannot be represented exactly. It tends to increase as you do calculations.

The closest representable value to 1.0554 is 1.0553999999999998937738610038650222122669219970703125. The closest representable value to 1.0554000000000001 is 1.055400000000000115818465928896330296993255615234375 - that is probably what came out of your macro calculation. The bad news is that you don't get exactly the value you meant. The good news is that the difference is often, as in this case, far too small to matter, or even be measured, in real life.

Generally, you do not want the exact value printed. The default toString behavior in Java is to produce the shortest decimal expansion that would convert to the internal value. That is also not usually what you want for end-user displays.

The right thing to do, in most cases, is exactly what you are doing in Excel - display only the digits you think matter for what you are doing. In Java, you can use DecimalFormat to convert a double to a String with control over the displayed digits.

这篇关于Excel中的浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！