"BOKU"のITな日常

還暦越えの文系システムエンジニアの”BOKU”は新しいことが大好きです。

基準日が開始日と終了日の間にあるという条件で値をVLOOKUPする方法/EXCELの小ネタ

SQLでは簡単なのに、EXCELでやると意外に難しいのが「履歴から基準日が有効期間に含まれるものだけを取得する」って処理です。

今回は、これをやってみます。

f:id:arakan_no_boku:20190201221826j:plain

 

何故難しいのか?

 

例えば、こんなやつです。

f:id:arakan_no_boku:20190225140419j:plain

こういうイベントの利用予定(履歴)があって、そこから基準日(上記例だと 2019/2/22)時点で開催中のイベント名をもってくるみたいなケース。

SQLだと「開始日 <= 基準日 and 終了日 >= 基準日」を条件にして終わり。

でも。

EXCELでどうやってやるの?と質問されると、大抵の人は一瞬「ん・」と固まります。

自分も初めて、このケースにでくわした時は、そうでした(笑)

VLOOKUPやMATCH関数は一致するものは探せるのですけど、「開始日 <= 基準日 and 終了日 >= 基準日」みたいな条件式は書けませんからね?

意外と難しいのです。

 

取得する計算式と説明

 

答えの計算式から書くと、こんな感じになります。

=VLOOKUP(SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),A6:A11),A6:C11,3,0)

VLOOKUPなんですが、かなり複雑です。

まあ、パッと見て意味が理解できる人はなかなかいないんじゃないですかね?

でも、この式をB2セルに仕込むと以下のような結果が返ってきます。

正しいイベント名をとってきてますよね。

f:id:arakan_no_boku:20190225142831j:plain

 

計算式の説明です

 

VLOOKUPで上記みたいなケースを扱うには2段階の処理が必要なんです。

つまり。

  1. 条件を処理して、VLOOKUPのキーにできる値を取得する
  2. 上記で取得した値をキーにしてVLOOKUPをかける

です。

上記の例でいくと。

  1. 基準日を開始日と終了日の間に含む行の「開始日」を取得する。
  2. 上記で取得した開始日をキーにしてVLOOKUPをかける。

とやっているわけです。

だから、1の部分にあたる

SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),A6:A11

 という計算式をのぞけば、単なるVLOOKUPなんですね。

 

SUMPRODUCTとは

 

SUMPRODUCT()をOfficeサポートの説明で見てみます。

support.office.com

引用すると。

引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。

です。

なので、本来意図された使い方はそうではないのでしょうが、「条件式判定のTRUE・FALSEを数値の1・0で処理してくれる」という仕様になっていて、これが今回のようなケースでは実にうまく働くのです。

 

例題の計算式の解説

 

つまり、こうなっているので。

f:id:arakan_no_boku:20190225140419j:plain

SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),A6:A11)

この式の「(A6:A11<=B1)」の部分は「開始日<=基準日」になります。

同様に「(B6:B11>=B1)」の部分は「終了日<=基準日」になります。

そして、SUMPRODUCTではTRUE・FALSEを1・0の数値で処理するので、(A6:A11<=B1)*(B6:B11>=B1)は、どちらかがFALSEなら「*0」で結果は0になりますし、両方TRUEなら1になります。

つまり、AND条件なので「開始日 <= 基準日 and 終了日 >= 基準日」となるわけです。

そして、最後の「A6:A11」(開始日の列)の部分は前の条件が1になったものだけを合計するのですが、履歴の中で条件を満たす行が1つしかなければ、その行の開始日だけが取得される。

まあ。

そういう仕掛けです。

 

もちろん、本来の使い方でも相当便利です

 

今回の例は日付を取得してますが、もちろん金額の合計とかに使っても相当便利です。

例えば、こんな例。

f:id:arakan_no_boku:20190225150956j:plain

会員資格の有効期間があって、集金基準日時点の在籍者のみ会費を集めるとしたら、2019/1/16時点で集金すべき金額はいくらでしょうか?みたいな例題です。

この場合も、計算式自体は上記のSUMPRODUCT部分とほぼ一緒。

=SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),C6:C11)

違うのは、集計対象がC列の会費になっているところ。

今回は条件に一致する行が複数あるので、結果は合計される・・というわけです。

これは、VLOOKUPの拡張につかってもよし、単独で使っても良しの、超便利な関数だと思いませんかね。

自分は結構愛用してます。

 

ちなみに

 

VLOOKUPだけじゃなくて、INDEX+MATCHの組合せで検索する場合でも、同じ理屈で使えます。

support.office.com

ではでは。