在不四舍五入的情况下在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.

示例:

Example:

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

• If I enter 600000.00000000030000000000 into a standard cell on a new spreadsheet, it gets converted to 600000. This makes sense as the cell format is set to General.
• If I set the cell format to Number and set the decimals places to 20, I would expect to be able to enter the number properly.

相反，将600000.00000000030000000000转换为600000.00000000000000000000.

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

推荐答案

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函数仅返回具有Double精度的数字.

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屋！