指定日からの直近の日付を抽出するには
状況
日程の調整をするのですが、候補日の中から特定の日付に一番近い日を抽出することなんてできるのでしょうか。
エクセルで指定日に一番近い日付を抽出するには、以下のようにINDEX、MATCH、ABS関数を利用すると可能になります。
関数式の例
{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}
説明
ルックアップ値および数値データと最も近い一致を見つけるには、INDEX、MATCH、ABS、およびMIN関数に基づいた配列数式を使用できます。示されている例では、E5の式は次のとおりです。
{=INDEX(data,MATCH(MIN(ABS(data-E4)),ABS(data-E4),0))}
ここで、「data」は名前付き範囲 B5：B14で、E4にはルックアップ値が含まれます。
注：これは配列数式であり、Ctrl + Shift + Enterで入力する必要があります。
この式の仕組み
コアでは、これはINDEXおよびMATCH式であり、MATCHは最も近い一致の位置を見つけ、その位置をINDEXにフィードします。INDEXは、その位置の値を返します。ハードワークはすべて、MATCH関数内で行われます。MATCH関数は次のように構成されています。
MATCH(MIN(ABS(data-E4)),ABS(data-E4),0)
MATCH内で、この式はE4のルックアップ値と名前付き範囲データの値の差を計算します。
data-E4
これは配列式であり、次のような配列結果を返します。
{-18;-6;-2;1;6;8;10;11;13;19}
次に、ABS関数を使用して、負の値を正に変換します。
{18;6;2;1;6;8;10;11;13;19}
これらの値は、ルックアップ値とデータの値の差を表します。最も近い一致を探しているので、MIN関数を使用して最小値を返します。この場合、最小値は1で、これがMATCH内のルックアップ値になります。
ルックアップ配列も同様の方法で計算されます。表現：
ABS(data-E4)
次の配列をルックアップ配列としてMATCHに返します。
{18;6;2;1;6;8;10;11;13;19}
MATCH内の最後の引数はmatch_typeで、完全一致を強制するためにゼロに設定されます。
最後に、これらの値を使用して、MATCH関数は配列内の1の位置（4）を返します。位置は、行引数としてINDEXに渡されます。
=INDEX(data,4)
INDEX関数は、その位置（2018年7月26日の日付）の値を返します。
注：同点の場合、この式は最初の一致を返します。
