doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

エクセルで指定日に一番近い日付を抽出したい

スポンサーリンク

最も近い一致を見つける

f:id:doodle-on-web:20191003172323p:plain

ジェネリック

{=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日の日付)の値を返します。

注:同点の場合、この式は最初の一致を返します。