☰

列内の次に大きい値を見つける

tag-icon • tag-icon microsoft-excel worksheet-function lookup
列内の次に大きい値を見つける

A2 つの列とがありますB。 列 には、B列 で検索する必要がある値がありますA。 ただし、正確に一致する値を見つける必要はなく、次に高い値が必要です。

例えば：

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

5したがって、列 の値については、列 からB返したいと思います。7A

おそらく何らかの形式のルックアップ/インデックス一致関数が必要だと思いますが、自分で数式を書くことができませんでした。

答え1

ソート済み

A最も単純な式は、列が昇順で並べ替えられている場合です。

ワークシートのスクリーンショット

次の数式を入力しC1、表の残りの列に Ctrl キーを押しながら Enter キーを押すか、コピーして貼り付けるか、下方向にフィルするか、自動でフィルします。

=INDEX(A:A,1+MATCH(B1,A:A,1))

説明：

1の 3 番目の引数は、最初MATCH()の引数以下の最大値を検索することを意味します。1そのインデックスに追加すると、次に大きい数値のインデックスが生成されます。INDEX()その後、関数はその数値を抽出します。

列の末尾に追加の値を追加したことに注意してくださいA。これは、次に高い値がない場合の特別なケースのためです。

未分類

列がソートされていない場合A（ソートされている場合でも機能します）、数式は少し複雑になります。

配列に次の数式を ( Ctrl+ Shift+ ) 入力し、コピーして、テーブルの列の残りの部分に貼り付けます ( と を削除することを忘れないでください) 。EnterC1{}

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

説明：

この関数は配列のn番目に小さい値を返します。SMALL(array,n)ブール値を無視するIF()関数の 3 番目の引数のデフォルトはなのでFALSE、列の値よりも大きい値のみがBチェックされ、次に大きい値になります。

列 に列の値より大きい値がない場合、エラーが発生するため、列 に特別な終了値はA必要ありません。#NUM!AB

最後に、 aventurin が指摘したように、ソートに関係なく機能する代替の同様の式があります (ただし、重要な注意点があります)。

Excel 2016以降の場合:

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

これが機能するのはMINIFS()、関数が最小値を抽出する前に基準に一致しない値を除外するためです。

以前のバージョンの Excel の場合:

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

これは関数と同じ理由で機能しますSMALL()。つまり、関数によって生成されたブール値を無視しますIF()。

警告：

ゼロが次に高い値として正しい場合、=MINIFS()および式は両方とも正しく動作しません。ゼロが返されるのは、{=MIN(IF())}は次に高い値はありません。(これは、最初の数式の列の末尾に追加の値を追加するのと同じ理由ですA。その数式も、それより高い値がない場合にはゼロを返します。)

答え2

たとえば、配列関数{=MIN(IF(A1:A6 > B1; A1:A6))}または{=MIN(IF(A1:A6 > B1; A1:A6; 1000))}(フォールバック値として 1000 を使用) を使用できます。

列 B の現在のセルの値 (ここでは ) より大きい列 A のすべての値の最小値を取得しますB1。したがって、どちらの列も並べ替える必要はありません。

Excel >= 2016 では、この関数も使用できますMINIFS。

配列関数は を押して挿入する必要があることに注意してくださいCtrl+Shift+Enter。

関連情報