☰

如何在不排序的情况下找到下一个最小/最大值？

tag-icon • tag-icon microsoft-excel-2010 worksheet-function microsoft-excel-2013
如何在不排序的情况下找到下一个最小/最大值？

我有一张表，需要从中提取下一个最大值和/或最小值（相对于另一张表中的值）。但是，我还需要允许第一个表按任何标准排序，而不会影响查找的准确性。

我搜索并阅读了使用 VLOOKUP、LOOKUP、HLOOKUP 和 MATCH 查找下一个最大/下一个最小值的各种方法，但我发现的所有方法似乎都要求根据您是否需要下一个最小/最大值对源列进行升序/降序排序。当我想同时使用这两种方法，或者我想在不破坏公式的情况下对表格进行排序时，这会破坏功能。

有没有办法在 Excel 中进行不区分排序的下一个最大值/下一个最小值搜索？

首选解决方案将仅使用原生 Excel 函数，因为我目前对 VBScript 不太熟悉，并且目前无法安装第三方工具。解决方案还必须与 Excel 2010 兼容和2013年。

答案1

预期结果

在此处输入图片描述

在 ARRAY 公式中使用 IF 和 SMALL

如果图片上所示的结果就是您想要的，那么查找下一个更大值的公式如下：
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

解释

  • Relative[Value]>[@Value]返回数组TRUE或FALSE
  • IF(Relative[Value]>[@Value];Relative[Value];99999999999)然后从相关表中返回较大的值，而对于那些较小的值，它会返回一些超出范围的巨大值。选择一个永远不会自然出现在您的数据中的值。或者，您可以使用0FALSES 的值，或者您可以使用错误值。
  • 然后我们只需使用SMALL带有参数的函数k=1从较大值中找到第一个最小值。
  • 这是一个数组公式，因此使用 CTRL+SHIFT+ENTER 输入公式。

链接：

答案2

如果你的号码是唯一的，那么这将有效：

下一个最小值：

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)+1)

下一个最大的：

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)-1)

如果不是，那么您可以使用数组公式或辅助列进行一些更复杂的操作。您还需要决定如何处理重复的数字（返回相同或不同的值），为此，您可以切换到使用LARGE并更改排序顺序RANK。

不过，这应该可以给你一个起点。

答案3

使用空白辅助（邪恶）列并将其一直复制并粘贴到下方。=IF(B3>NOW(),B3,"") 我将其称为 T 列。然后在“下一个”字段中输入。**=MIN(T1:T1000)

作为一个函数是这样的：

    Function Soonest(scolumn As String) As Date

'
'
Dim a, b
Dim test(20000) As Date
Dim Min As Date

b = 0

   For a = 1 To 20000
    If (IsEmpty(Range(scolumn & a))) Then
     GoTo SkipMe
    End If

     If (Range(scolumn & a).Value - Now() > 0) Then
     b = b + 1
     test(b) = Range(scolumn & a).Value
     End If
SkipMe:
  Next a

  If b = 0 Then
   Min = "None"
  GoTo NoneFound
  End If
  Min = test(1)
  For c = 1 To b
   If test(c) < Min Then Min = test(c)
  Next c
NoneFound:

Soonest = Min
End Function

相关内容