主页 / computer / 问题 / 1640152
Accepted
ExcelEverything
Asked: 2021-04-08 22:42:56 +0800 CST

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

  • 772

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

3.1
5
2.9
1

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

有没有更好的方法，也许是排序或统计功能？特别是如果您有超过 4 个数据点。

microsoft-excel

1 个回答

  1. Best Answer

    如果您有 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)))
    • 对范围进行排序
    • 从 row2 - row1 开始减去每一对
      • 由于范围已排序，结果将始终为正
    • 确定最小的差异
    • 返回匹配的对

    如果您没有具有这些功能的 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，在不开发极其冗长且笨拙的公式的情况下，一种方法是使用Name 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)

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

    此外，在旧版本的 Excel 中可能会出现这种情况，要输入/确认此数组公式，您可能必须在按下时按住ctrl+ 。如果您正确执行此操作，Excel 将在公式栏中看到的公式周围放置大括号。shiftenter{...}

    • 4

相关问题