☰

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

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 列。 **=最小值(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

相關內容