返回与大量数据中找到的某个值最接近的值

Question 1

这是解决您问题的另一种方法。不幸的是，它不适用于像您这种情况的二维数组。如果您可以将值转换为一维数组形式，例如 D1:Z575。下面的公式将返回您想要的值。

=INDEX(D1:Z575,MATCH(MIN(ABS(D1:Z575-A1)),ABS(D1:Z575-A1),0))

重要的：按Ctrl+ Shift+Enter组合键，而不是Enter只按。因为公式只有在数组公式。

逻辑类似于 fixer1234 的评论，找到最小差值并使用该位置返回值指数功能。

以下是该公式的详细解释：如何找到最接近的匹配

Answer

这是解决您问题的另一种方法。不幸的是，它不适用于像您这种情况的二维数组。如果您可以将值转换为一维数组形式，例如 D1:Z575。下面的公式将返回您想要的值。

=INDEX(D1:Z575,MATCH(MIN(ABS(D1:Z575-A1)),ABS(D1:Z575-A1),0))

重要的：按Ctrl+ Shift+Enter组合键，而不是Enter只按。因为公式只有在数组公式。

逻辑类似于 fixer1234 的评论，找到最小差值并使用该位置返回值指数功能。

以下是该公式的详细解释：如何找到最接近的匹配

Question 2

可能有更优雅的方法来实现这一点，但这至少是一种不优雅的解决方案。公式变得很长，所以我将分部分解释。

我想说明这是如何工作的，所以我保持范围易于管理。根据您的需要进行扩展。

我的数据数组是 D1:F10。我只是用连续的数字填充它，因为这样很容易，并且说明清楚。该公式不依赖于数字是整数或按特定顺序排列。我用一些匹配值的重复值替换了几个值，以验证该公式是否有效。

A 列中有几个目标值 - 其中一个是将最接近的匹配项向下舍入，一个是完全匹配，一个是将最接近的匹配项向上舍入，还有一个是目标值与两个最接近的匹配项之间的距离相等。我选择了目标值，以便每列都有一个匹配项。

B 列包含数组中每个目标值（解决方案）的最接近匹配。

该公式由组件构成，因此我将解释此网格中显示的组件：

目标值在 A 列中重复。B 列查找目标值与表中最接近值之间的最小差异。B16 中的公式：

{=MIN(ABS($D$1:$F$10-A1))}

这是一个数组公式。它是最终公式的组成部分，因此最终公式需要用输入Ctrl Shift Enter。

我们不知道差异在哪个方向，不知道是否需要添加或减去以匹配表格中最接近的值，因此我们尝试两者。要通过“向下舍入”找到匹配值，它使用 C 列中的公式。要通过“向上舍入”找到匹配值，它使用 D 列中的公式。一个减去差异，另一个加上差异：

=SUMPRODUCT(($D$1:$F$10=A1-B16)*$D$1:$F$10)
=SUMPRODUCT(($D$1:$F$10=A1+B16)*$D$1:$F$10)

如果第一个括号为真，则计算结果为1，否则为0。SUMPRODUCT 将其与相关单元格值相乘，然后将结果相加。

但请注意一些奇怪的结果。

多个目标返回零或不匹配。这些情况是通过向另一个方向舍入找到匹配的。
位于两个最接近值中间的目标将返回向下舍入的较低匹配值和向上舍入的较高匹配值。如果您关心选择哪一个，请对公式组件进行排序，以便选择按首选方向舍入。
有几个值是实际值的两倍。这些是我复制了最接近的值的情况；公式会将每个值都加起来。

为了处理这些情况，我们计算找到的匹配数。这在 E 列和 F 列中完成：

{=SUM($D$1:$F$10=A1-B16)}
{=SUM($D$1:$F$10=A1+B16)}

这些组件也是数组公式。E 列中的公式评估每个单元格值是否等于目标值减去差值，即 True 或 False（1或0），然后对这些值求和。F 列中的公式相同，只是它测试的是目标值加上差值。

我们可以用两种方式使用这些结果：测试无匹配（零结果），以选择使用哪个结果，以及作为匹配计数来纠正上一步中多个匹配的虚高值。

请注意，由于我们正在寻找最接近的匹配项，因此总会有一个“最接近的”，因此向上或向下舍入不能同时返回零。我们只需测试其中一个结果。如果它是零，我们将使用另一个。如果它不是零，我们将使用该值，因为另一个值将是零、相同的或可以通过测试顺序选择的另一个有效结果。

对于选定的有效结果，计数将用作除数。因此，结果的公式为：

=IF(E16=0,D16/F16,C16/E16)

这就是它的工作原理。将其转换为单个合并公式只需用引用单元格中的公式替换单元格引用即可。合并公式如下所示（我将添加空格和换行符以使其更具可读性；如果您想复制和粘贴公式，则需要删除它们）：

{=IF(SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))=0,  
   SUMPRODUCT(($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1+MIN(ABS($D$1:$F$10-A1))),  
   SUMPRODUCT(($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1)))*$D$1:$F$10)/SUM($D$1:$F$10=A1-MIN(ABS($D$1:$F$10-A1))))}

Answer