☰

並べ替えずに次の最小値/最大値を見つけるにはどうすればよいですか?

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

関連情報