简单的HLOOKUP失败与Excel 2010 [英] Simple HLOOKUP Failing with Excel 2010

问题描述

我有一个Excel电子表格（由电子表格设备生成，但它更是一个Excel问题），这是不正常的，当我使用 HLOOKUPS 。我使用一个简单的HLOOKUP来查找表中给定百分比的值。对于两个特定值，HLOOKUP失败。这些值为0.9993和0.999。此excel文档演示了此问题： http://dsokol.com/tokio/excelna.xlsx 。单元格C6和C7显示为N / A，当它们打算用B和C填充时。如果我重新键入值，HLOOKUP将再次开始工作。

解决方案

我同意Barry的意见。这个问题与它们是数字还是字符串无关（它们都是数字）。这似乎是以二进制存储浮点数的限制 - 如下所述： http：// support。 microsoft.com/kb/214118

Microsoft Excel中浮点数
的算术运算的许多组合而Microsoft Works可能会产生非常少的
错误的结果。例如，方程
= 1 *（。5-.4-.1）可以被评估为数量（-2.78E-17），或-0.0000000000000000278而不是0。

这个问题不是唯一的excel，而是以下结果：

IEEE 754指定数字以二进制格式存储以减少
存储要求，并允许所有微处理器上可用的内置二进制算术
指令在相对较小的处理
数据快速时尚。然而，一些
简单的数字，不重复的十进制数将被转换为重复的
二进制数，不能以完美的准确性存储。

这个现象在算术平等中可见：

再次，这不是一个独特的excel问题：

此行为不是Excel或Works的问题或限制;
这种行为发生是因为电气研究所和
电子工程师（IEEE）754浮点数标准要求
数字以二进制格式存储。

< blockquote>

祝你好运。

I have an Excel spreadsheet (generated by spreadsheet gear, but it's more an Excel problem) that isn't functioning as i would expect when I use HLOOKUPS. I use a simple HLOOKUP to look up the value at a given percent in a table. For two certain values, the HLOOKUP fails. These values are 0.9993 and 0.999. This excel document demonstrates the problem: http://dsokol.com/tokio/excelna.xlsx. Cells C6 and C7 are showing as N/A, when they are intended to be populated with B and C. If I re-key over the values, the HLOOKUPs start working again.

Can anyone explain why this is occurring?

解决方案

I agree with Barry. This problem is not related to whether or not they are numbers or strings(they are all numbers). This appears to be a limitation of storing floating point numbers in binary - as described here: http://support.microsoft.com/kb/214118

Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation =1*(.5-.4-.1) may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.

This problem is not unique to excel either but rather a result of:

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

This phenomenon is visible here in "Arithmetic Equality":

Again this is not a problem that is unique to excel:

This behavior is not a problem in or a limitation of Excel or Works; this behavior occurs because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format.

Good Luck.

这篇关于简单的HLOOKUP失败与Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！