式を使用してExcelで最も近い値を検索します

さまざまな数式を組み合わせるコツをつかめば、Excel関数は非常に強力になります。かつては不可能と思われていたことが、突然子供の遊びのようになる可能性があります。

そのような例のXNUMXつは、Excelデータセットで最も近い値を見つけることです。

Excelには、最も近いものを見つけることができるいくつかの便利なルックアップ関数（VLOOKUPやINDEX MATCHなど）があります。值（以下の例に示すように）。

ただし、最良の部分は、これらのルックアップ関数を他のExcel関数と組み合わせて、さらに多くのことを実行できることです（ソートされていないリストでルックアップ値に最も近いものを見つけることを含む）。

このチュートリアルでは、ルックアップ数式を使用してExcelのルックアップ値に最も近いものを見つける方法を示します。

最も近い一致（または最も近い一致値）を見つける必要があるさまざまなシナリオがあります。

この記事で取り上げる例を次に示します。

1. 売上高に基づいて手数料率を見つける
2. 最適な候補を見つける（最も近い経験に基づく）
3. 次のイベントの日付を探す

はじめましょう！

手数料率を見つける（最も近い販売額を探す）

以下のようなデータセットがあり、すべての営業担当者の手数料率を調べたいとします。

コミッションは、販売額に基づいて分配されます。これは、右の表を使用して計算されます。

たとえば、営業担当者の総売上高が5000の場合、手数料は0％であり、営業担当者の総売上高が15000の場合、手数料は5％です。

手数料率を取得するには、販売額のすぐ下にある最も近い販売範囲を見つける必要があります。たとえば、15000の売上の場合、手数料は10,000（または5％）であり、25000の売上の場合、手数料率は20,000（または7％）です。

最も近い販売価格を見つけて手数料率を取得するには、VLOOKUPで近似マッチングを使用できます。

次の式でこれを行うことができます。

= VLOOKUP（B2、$ E $ 2：$ F $ 6,2,1）

この数式では、最後のパラメーターは1であり、近似ルックアップを使用するように数式に指示していることに注意してください。これは、数式が列Eの売上値を調べて、ルックアップ値のすぐ下の値を見つけることを意味します。

VLOOKUP式は、その値の手数料率を示します。

注意：これを行うには、データを昇順で並べ替える必要があります。

最適な候補を見つける（最も近い経験に基づく）

上記の例では、データを昇順で並べ替える必要があります。ただし、データが並べ替えられない場合があります。

それでは、例を見て、数式の組み合わせを使用してExcelで最も近い一致を見つける方法を見てみましょう。

以下は、仕事の経験が目的の値に最も近い従業員の名前を見つける必要があるデータセットの例です。この場合の期待値は2.5。XNUMX年です。

データはソートされていないことに注意してください。さらに、最も近いエクスペリエンスは、特定のエクスペリエンスよりも少ない場合も多い場合もあります。たとえば、2年と3年は等しく近いです（0.5。XNUMX年の違い）。

結果を与える式は次のとおりです。

=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

この式の秘訣は、ルックアップ配列とルックアップ値を変更して、目的の値と実際の値の間の最小の経験的差異を見つけることです。

まず、手動で行う方法を理解しましょう（次に、この式がどのように機能するかを説明します）。

これを手動で行う場合は、列Bの各セルを調べて、必要なエクスペリエンスと経験の違いを見つけます。すべての違いがわかったら、最小のものを見つけて、その人の名前を取得します。

それはまさに私たちがこの式で行うことです。

説明させてください。

MATCH式のルックアップ値はMIN（ABS（D2-B2：B15））です。

このセクションでは、与えられた経験（つまり、2.5。XNUMX年）と他のすべての経験との最小の違いを示します。この場合、0.3を返します

私はABSを使用して、最も近いものを探していることを確認していることに注意してください（おそらく与えられた経験よりも多かれ少なかれ）。

これで、この最小値がルックアップ値になります。

MATCH関数のルックアップ配列はABS（D2- $ B $ 2：$ B $ 15）です。

これにより、2.5（必要な経験）を差し引いた数値の配列が得られます。

所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

MATCH関数は、この配列内の0.3の位置を検出します。これは、最も経験の豊富な人の名前の位置です。

次に、INDEX関数は、ジョブ番号を使用して個人の名前を返します。

注：同じ最低経験を持つ複数の候補者がいる場合、上記の式は最初に一致する従業員の名前を示します。

次のイベントの日付を探す

ルックアップ式を使用して、現在の日付に基づいてイベントの次の日付を見つけることができる別の例を次に示します。

以下は、イベント名とイベント日付があるデータセットです。

次のイベントの名前と、この次のイベントのイベントの日付が欲しいです。

今後のイベントの名前を示す式は次のとおりです。

=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)

次の式は、今後のイベントの日付を示します。

=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)

この式がどのように機能するかを説明しましょう。

イベントの日付を取得するために、MATCH関数は列Bで現在の日付を探します。この場合、完全一致ではなく、近似一致を探します。したがって、MATCH関数の最後のパラメーターは1です（ルックアップ値以下の最大値を検出します）。

したがって、MATCH関数は、日付が現在の日付以下であるセルの位置を返します。したがって、この場合、次のイベントは次のセルにあります（リストは昇順でソートされているため）。

したがって、次のイベントの日付を取得するには、MATCH関数によって返されるセルの位置にXNUMXを追加するだけで、次のイベントの日付のセルの位置がわかります。

この値は、INDEX関数によって指定されます。

イベント名を取得するには、同じ式を使用して、INDEX関数の範囲を列Bから列Aに変更します。

この例は、友人がリクエストしたときに思い浮かびました。彼はすべての友人/親戚の誕生日を列にリストし、次の誕生日（およびその人の名前）が近づいていることを知りたいと考えています。

上記のXNUMXつの例は、ルックアップ式を使用してExcelで最も近い値を見つける方法を示しています。