在 Excel 中存储和执行长小数的计算而不进行四舍五入 [英] Storing and performing calculations on long decimals in Excel without being rounded

问题描述

我正在尝试在 Excel 中对包含长小数的数字进行一些计算.

I am attempting to do some calculations in Excel on numbers that include long decimals.

但是，Excel 似乎不允许我用我想使用的数字填充单元格.

However, Excel doesn't seem to let me populate the cells with the numbers I would like to use.

示例:

• 如果我在新的标准单元格中输入 600000.00000000030000000000电子表格，它被转换为 600000.这是有道理的，因为单元格格式设置为常规.
• 如果我将单元格格式设置为数字并设置小数位到 20，我希望能够正确输入数字.

相反，600000.00000000030000000000 被转换为 600000.000000000000000000000.

有人知道为什么会这样吗?

推荐答案

Excel 只存储 15 位有效数字.除此以外的任何数字都会自动四舍五入，无论数字格式如何.

Excel only stores 15 significant figures for numbers. Any figures beyond that automatically get rounded, regardless of number format.

首先将单元格设置为文本格式会将数字存储为包含任意位数的字符串，但 Excel 无法对其执行任何计算.

Setting the cell to Text format first will store the number as a string with as many digits as you want, but Excel can't perform any calculations on it.

但是，VBA 可以使用 Decimal 类型的变量进行计算，这些变量最多可以存储 29 个有效数字.

However, VBA can do calculations with Decimal type variables which can store up to 29 significant figures.

如果您首先将值作为文本存储在 Excel 中(在输入值之前将单元格编号格式设置为文本)，您可以在 VBA 中创建一个用户定义函数来读取字符串值，将它们转换为十进制值，执行您的计算，然后返回一个具有完整计算精度的字符串.

If you first store the values as text in Excel (setting the cell number format to Text before entering the values), you can create a User Defined Function in VBA to read the string values, convert them to Decimal values, perform your calculations and then return a string with the full precision calculated.

例如:

Function PrecisionSum(ra As Range) As String

    'Declare variables holding high precision Decimal values as Variants
    Dim decSum As Variant 

    'This loop will sum values from all cells in input range
    For Each raCell In ra
        'Read values from input cells, converting the strings to Decimals using CDec function
        decSum = decSum + CDec(raCell.Value)
    Next raCell

    'Return calculated result as a String
    PrecisionSum = Format(decSum, "0.00000000000000000000")

End Function

您需要编写函数来执行您想要的操作.

You'll need to write functions to do the operations that you desire.

请注意，您在 VBA 中使用的任何函数的准确性仍然会受到限制.例如，返回数字平方根的 SQR 函数仅返回具有双精度的数字，而与输入的精度无关.

Note that you'll still be limited by the accuracy of any functions you use in VBA. For example, the SQR function to return the square root of a number only returns a number with Double precision regardless of the precision of the input.

这篇关于在 Excel 中存储和执行长小数的计算而不进行四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！