最も近い一致を検索する

Excelで数値データセット内の検索値に最も近い一致を探すには、INDEX、MATCH、ABS、 およびMIN関数を組み合わせて使用できます。

Excelで最も近い一致を見つけるにはどうすればよいですか？

上記のように、どの営業担当者が2万ドルの目標に最も近い売上を達成したかを知るには、INDEX、MATCH、ABS、MIN関数を組み合わせた数式が役立ちます。ABS関数は、各営業担当者の売上と売上目標の差をすべて正の値に変換し、MIN関数はその中から最小の差を見つけます。これは最も近い一致を意味します。これで、MATCH関数を使用して最も近い一致の位置を特定し、INDEX関数で対応する位置の値を取得できます。

汎用構文

=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))

√ 注: これは配列数式であり、Ctrl + Shift + Enterで入力する必要があります。

どの営業担当者が2万ドルの目標に最も近い売上を達成したかを知るには、以下の数式をセルF5にコピーまたは入力し、Ctrl + Shift + Enterを押して結果を得てください：

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))

または、セル参照を使用して数式を動的にすることもできます：

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

数式の説明

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4): C5:C10-F4の部分は、範囲C5:C10内の各売上とセルF4の目標売上$20,000とのすべての差分値を取得します。結果は次のような配列になります：{4322;2451;6931;1113;6591;4782}。
• MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): MIN関数は配列{4322;2451;6931;1113;6591;4782}の中から最小の数値を見つけます。これは最小の差、つまり最も近い一致を意味します。したがって、この関数は1113を返します。
• MATCH(MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4)),,ABS(C5:C10-F4)ABS(C5:C10-F4),0),0) = MATCH(MATCH(1113,,{4322;2451;6931;1113;6591;4782},0),0): match_type 0により、MATCH関数は配列{4322;2451;6931;1113;6591;4782}内で正確な数値1113の位置を見つけます。この関数は、その数値が4番目の位置にあるため、4を返します。
• INDEX(B5:B10B5:B10,,,MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4): INDEX関数は、名前範囲B5:B10内の4番目の値を返します。この場合、それは「Bale」です。

関連する関数

関連する数式