☰

Excel MATCH 函数似乎已损坏。问题描述和调查详情。对此有任何解释或解决方案吗？

tag-icon • tag-icon microsoft-excel worksheet-function formatting
Excel MATCH 函数似乎已损坏。问题描述和调查详情。对此有任何解释或解决方案吗？

我是一名经验丰富的 Excel 用户，习惯使用 INDEX 和 MATCH 函数以及字符串，因此我也熟悉由于不同的单元格格式、未注意到的空格等导致的 MATCH 函数错误问题。我还熟悉解决此类问题的多种方法，例如使用 TEXT() 或 VALUE() 函数将文本转换为数字，反之亦然，或者使用更简单的方法，例如添加 --、+0、*1、"" & 或其他类似操作来在格式之间进行转换。这次我发现的是一种完全不同类型的错误，它依赖于值，而不会更改格式。

在我的电子表格中，我只是想找出“cm”值列表中的哪一行对应于在其他地方获得的“mm”值。因此，我只需要匹配输入值除以 10。这最初是有效的，直到我意识到它对某些值不起作用。在下面的第一张图片中，可以看到 match 无法找到值“55.025”，但可以毫无问题地找到“64.025”，即使列表中的后续单元格仅包含 B4 中的值减去 A 列中它们旁边的值。

错误示例图像

在无法追踪错误后，我创建了一个电子表格来测试多种不同的组合，并尝试找出 MATCH 函数行为的模式。但如果有什么不同的话，那就是它让我更加困惑，它不仅在不同的值下表现不同，而且将文本转换为数字的不同方式也表现不一致。根据实际输入的值，我实际上已经确定了 6 种不同的可能情况。

很难总结结果，所以你应该检查电子表格并在其中进行自己的测试。我只会解释下图所示的结构。

错误调查电子表格图像

参考值是单元格 D3（灰色）的值，我只是在那里添加了小数，以简化在 B3（蓝色）中输入的不同值的输入。D 列上的橙色和黄色单元格是格式化 D3（灰色）值的不同方式。即：

D7   ="" & D3/10
D11  =--D7
D15  =D3/10

分别对应文本格式、文本转换为数字以及普通数字。

D 列底部的绿色单元格只是 MATCH 函数搜索的值列表，通过用 C 列中的值减去 D18 中的值获得。

再往右（G:Q 列），第 3 行包含行 MATCH，应在 D 列的绿色数组中找到搜索的值。第 5 行列出了对 D 列的值实施的修改类型。在它们上方（第 6、10、14 行），检查这些值是否等于 D 列绿色数组中的相应单元格。下方（第 8、12、16 行）是这些值的实际 MATCH 公式。

所有这些之下有一个显示每种行为的不同值的列表，范围从无论修改如何都在数组中找到（只要它们是数字）到根本找不到，其间有几种不同的情况。

“Testing_B”选项卡与“Testing”选项卡完全相同，但单元格 D18 的值不是从我的初始电子表格中粘贴的，而是手动输入的。这导致它出现了一组不同的奇怪行为，即使最初粘贴的值应该与前一个选项卡的值相同。

您可以下载下面的电子表格，并验证在单元格 B3（蓝色）中输入不同列出的值时的行为。有效范围目前为 44 至 64。

电子表格 MATCH_ERRORS.xlsx

我希望有人能从这一切中找到某种解释。

*编辑（2020-05-07）：

可能的舍入问题

关于 Justin Doward 指出的舍入问题可能是根本原因，我注意到 Excel 在检查两个单元格是否相等时使用的精度级别似乎与检查两个单元格是否匹配（MATCH 函数）时使用的精度级别不同。我刚刚做了一个简单的测试，根据比较的值，差异是一个或两个数量级（下图）。它仍然无法解释为什么 Excel 会以某种近似值更改单元格的实际值，然后该近似值与该单元格的假定值不匹配。原始问题中使用的值甚至没有接近可能面临舍入误差的精度级别。这似乎只是表明可能存在更严重的潜在问题。

舍入误差测试。Equal 与 MATCH

答案1

由于 Sheets 和 Excel 均基于 IEEE 标准中固有的舍入问题，因此它们都做出了某些假设。

因此，有时 Sheets 似乎可以在 Excel 无法工作的情况下工作，但有时 Sheets 也会返回“错误”的答案。

例如：

Excel  A1:  =3*(4/3-1)-1
       B1:  =A1=0  -->TRUE

Sheets:  A1:  =3*(4/3-1)-1
         B1:  =A1=0 --> FALSE

在您的特定示例中，如果B4: 6.4025您输入的不是输入，而是公式=B2/10+A8，那么您的MATCH函数就会起作用。

这些限制以某种方式适用于所有使用 IEEE 标准存储双精度数字的电子表格程序。

答案2

这似乎是一个舍入错误，如果您首先对两个公式（我的工作表上的 B4:B8 和 E1）中的数字进行舍入，则匹配效果很好。

=MATCH(ROUND(B2/10,4),$B$4:$B$8,0)

=ROUND($B$4-A8,4)

在此处输入图片描述

答案3

到目前为止，我发现针对此问题的唯一有效解决方案是将 MATCH 函数中包含的所有涉及小数的值转换为文本。例如，下图中单元格 G8 的公式将变为：

G8:   =MATCH("" & G7,"" & $D$18:$D$38,0)

如果验证单元格是否相等的测试也进行相应的改变，它将变成：

G6:   ="" & G7="" &@INDEX($D$18:$D$38,G$3)

但这只是避免仍然存在的问题的一种解决方法。 MATCH 函数应该会自动执行类似的操作。

建议解决方法的图片]

相关内容