最も近い一致値を検索する

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

Excel で最も近い一致値を見つける方法は？

上記のように、売上目標 $20,000 売上目標に対して最も近い実績を達成した営業担当者を特定するには、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を押して確定する必要があります。

売上目標$20,000 に対して最も近い売上を達成した営業担当者を特定するには、以下の数式をセル F5 にコピーまたは入力し、$20,000を押して結果を取得してください：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}。ABS 関数により、すべての負の値が正の値に変換され、次のようになります：{4322;2451;6931;1113;6591;4782}。.
• MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):MIN 関数は、配列 {4322;2451;6931;1113;6591;4782}から最小値（＝最小の差分、つまり最も近い一致値）を抽出します。そのため、この関数は1113を返します。
• MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)match_type 0により、MATCH 関数は配列 {4322;2451;6931;1113;6591;4782} 内で完全に一致する数値1113 の位置を検索します。この関数は、その数値が4 番目にあるため、4 を返します。
• INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)INDEX 関数は、名前範囲 B5:B10 内の4 番目の値を返します。これはBaleです。

関連関数

関連する数式