☰

挑战：从一组数字中选出两个最相似/最接近的数字的最佳方法是什么？

tag-icon • tag-icon microsoft-excel
挑战：从一组数字中选出两个最相似/最接近的数字的最佳方法是什么？

假设您有一组 4 个或更多无序数字，并且您只对彼此最接近或最接近的两个数字感兴趣。其他数字将被丢弃（假设它们是错误的测量值）。例如

3.1
5
2.9
1

只有 3.1 和 2.9 值得关注。我使用繁琐的公式，首先将每个元素从其他元素中减去（这只适用于 4 个数据集），然后选择最小的差异，然后将最小的差异与正确的差异对匹配。

有没有更好的方法，比如使用排序或统计功能？特别是当您有超过 4 个数据点时。

答案1

如果您有 Office 365，则可以使用以下内容：

请注意，如果有多个最接近的对，则将仅返回绝对值最低的对。如果这是一个问题，可以设计一个不同的公式

=LET(x,SORT(myRange),
          y,COUNT(x)-1,
          z,INDEX(x,SEQUENCE(y-1,,2)),
          a,INDEX(x,SEQUENCE(y-1)),
          b,z-a,
          c,MATCH(MIN(b),b,0),
          INDEX(x,SEQUENCE(2,,c)))
  • 对范围进行排序
  • 从第 2 行到第 1 行开始，每对相减
    • 由于范围已排序，结果将始终为正
  • 确定最小差异
  • 返回匹配对

如果您没有具有这些功能的 Office 365，您可以编写一个非常复杂的公式；使用多个辅助列来实现相同的结果；或者编写 VBA UDF。

在此处输入图片描述

如果有多对符合条件，则返回全部试试这个公式：

=LET(x,SORT(myRange),
          y,COUNT(x)-1,
          z,INDEX(x,SEQUENCE(y-1,,2)),
          a,INDEX(x,SEQUENCE(y-1)),
          b,z-a,
         arr1,FILTER(a,MIN(b)=b),
         arr2,FILTER(z,MIN(b)=b),
         xml,"<t><s>" &TEXTJOIN("</s><s>",TRUE, arr1 & "</s><s>" & arr2)&"</s></t>",
         FILTERXML(xml,"//s"))

在此处输入图片描述

对于早期版本的 Excel，有一种方式可以做到这一点，而不需要开发非常长且笨拙的公式，那就是使用姓名d 公式。

在下面的名称中，myRange指的是数据源，它应该在单个列中，其他名称都从该列派生而来

| myRange | Refers to: | =Sheet1!$A$1:$A$16                                                              |
|---------|------------|---------------------------------------------------------------------------------|
| x       | Refers to: | =SMALL(myRange,ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNT(myRange))))   |
| y       | Refers to: | =ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNT(x)-1))                      |
| b       | Refers to: | =INDEX(x,y+1)-INDEX(x,y)                                                        |
| minB    | Refers to: | =MIN(b)                                                                         |
| rws     | Refers to: | =ROW(INDEX(Sheet1!$A:$A,MATCH(minB,b,0)):INDEX(Sheet1!$A:$A,MATCH(minB,b,0)+1)) |

由于数据是数字，我们可以SORT使用该SMALL函数。各种ROW(INDEX:...构造都模仿该SEQUENCE函数。

鉴于上述命名的公式，为了返回与上述函数相同的结果LET，您现在可以使用：

=INDEX(x,rws)

=MIN(b)我不能 100% 确定它是否适用于旧版本。我遇到了一个奇怪的现象，当我在工作表中输入时，我无法获得正确的答案；但是当作为Named 公式输入时，它确实返回了正确的答案（其中minB指的是Min(b)）。所以不确定旧版 Excel 是否适用。

在旧版本的 Excel 中，输入/确认此数组公式，您可能需要按住ctrl+shift并点击enter。如果您正确执行此操作，Excel 将{...}在公式栏中显示的公式周围放置括号。

相关内容