☰

Excel 无法将文本中的非常小的浮点数解析为值

tag-icon • tag-icon microsoft-excel floating-point
Excel 无法将文本中的非常小的浮点数解析为值

我从计算中得到文本形式的结果，我想将其粘贴到 Excel 中。有些数字非常小，小到最小 IEEE 754 双精度值大于零，2^-1074，例如大约 10^-324。

当我将其粘贴到 Excel 中时，Excel 仅解析小于 2^-1022 的数字，例如大约 10^-308，就好像它忽略了小尾数一样。

这些数字并不重要，可以直接解析为零，但 Excel 会将它们视为文本，极其不方便。

我有两个问题：

  • 为什么 Excel 不将值解析为数字或零？
  • 除了在导出过程中预先过滤小数字之外，还有其他方法可以缓解这个问题吗？

例子：
仅对线以下的部分进行正确解析。

4696.57419374627    1.64825190702534E-317
4610.79539316943    4.5992506248762E-316
4526.58326700779    1.27364779881474E-314
4443.90920132986    3.50034092033703E-313
4362.74510481244    9.54706896850641E-312
4283.0633991957     2.58421411361223E-310
4204.83700991247    6.94201659277496E-309
-----------------------------------------
4128.03935688878    1.85072341656942E-307
4052.64434551232    4.89661672712669E-306
3978.62635776597    1.2857289504059E-304
3905.96024352317    3.35043697803301E-303
3834.62131200233    8.66467350863874E-302
3764.58532337728    2.22383044536139E-300

答案1

为什么 Excel 不将值解析为数字或零？

Excel 不支持这么小的浮点数。

对于Excel，可以存储的最大数字是1.79769313486232E+308，可以存储的最小正数是2.2250738585072E-308

来源：https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

答案2

Excel 不会如您所愿，因为它在确定要考虑的离散项之后（即在确定哪些字符属于哪些“实体”之后）遵循以下步骤。

  1. 离散项可能是十进制数（“基数为 10”）吗？其所有字符是否都符合十进制数字模式？例如，如果字符串中有“A”，则不符合，但字符串中有“E”可能没问题。“，”就没问题。一旦确定了这一点，它就会继续。如果它决定“否”，则将字符串输出为文本。如果“是”，它将尝试确定数字的类型。例如日期。4 到 8 个数字字符，且“/”在正确的位置，通常可以确保假定日期，并且 Excel 将尝试引入日期等项目。等等。如果它尝试了所有编程模式，但仍然无法生成它认为是有效数字的内容，因此无法输出结果，它将默认将该项视为文本字符串并将其输出。全部完成。

因此，它会将您的输入视为数字，并将其视为数字。当它显示结果值并发现它不能解释为数字时，它会将其转换为您获取的文本。因此，它会得到一个像 1E-400 这样的值，并将其视为“数字”，并尝试将其呈现为数字。但它无法在程序的用户端创建这样的数字。（它可以在内部创建，因为它在内部使用 27-28 位小数，但不能超过Ramhound计算输出显示的限制。）然后它会转到默认设置“您是文本”。

Excel 中没有解决方案。正如您所提到的，您可以预先过滤材料。但是，作为内部函数呈现的相关程序 Power Query 可以为您完成此操作。Power Query（“PQ”）不受几乎所有传统 Excel 限制的限制，并且可能比为您解决这个问题更能引起您的兴趣。

首先，如果您能够将要导入的每个文件重命名为一个您将始终使用的单一标准名称，那么请这样做。创建一个 PQ 查询来读取所谓的文件。告诉它加载和转换。您的数据（如图所示）将作为单列出现。告诉它拆分列并使用空格作为分隔符。进一步告诉它使用最左边的空格（因为您的样本中的空格数量会有所不同）。拆分列后，突出显示两列并告诉它将类型更改为“十进制数”。

此时您已完成。您只需执行一次。由于您使用了一个特殊的文件名（每次重命名文件时都会使用该文件名），因此您无需编辑它即可为其指定下一个文件的名称。（还有其他方法可以实现这一点，但这很简单，所以我在这里介绍它。）您现在可以告诉它关闭并加载。

您的数据以表格形式进入 Excel。您会注意到，尽管您可能看到 PQ 在设置时将有问题的数据显示为超出 Excel 限制的实际数字，但这些超出 Excel 限制的数字实际上会以 0 的形式导入表格，而您表示这是可以接受的。

所以，小菜一碟。

但是，根据越界数据对您的价值，您可以考虑在 PQ 内部完成所有工作。然后您可以使用有问题的值。它可以做一些令人惊奇的事情，甚至更平凡的事情。它是 SQL 的前端。因此，它可以做任何非深奥的事情，您可以使用 SQL 程序来获取您的数据。

但不管怎样，能解决今天的难处就行了。

传统的 Excel 解决方案是插入一个辅助列，输入一个简单的公式：

=IF(ISNUMBER(A1),A1,"")

并将其填充到辅助列中，复制辅助列，并Paste|Special|Values覆盖源列（在此公式中为 A 列）。然后删除辅助列。

它并不优雅，并且涉及许多人讨厌的可怕的“辅助列”，但它有效，运行简单，并且运行良好。

另一种方法是使用 VBA 在导入数据后清理每一列。它可以测试每个导入单元格的内容，如果内容是文本，则删除该内容，只留下数字条目。

一切都取决于你的时间、兴趣和经验。或者就我而言，PQ 似乎非常方便，所以如果我有时间，我会尝试看看它能给我带来什么新东西。但无论对你有用与否！

相关内容