"BOKU"のITな日常

62歳・文系システムエンジニアの”BOKU”は日々勉強を楽しんでます

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

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

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とSUMPRODUCTの組み合わせです。

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

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

f:id:arakan_no_boku:20190225142831j:plain

 

計算式の説明です

 

2段階の処理をしています。

つまり。

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

です。

上記の例でいくと。

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

とやっているわけです。

この基準日を開始日と終了日の間に含む行の「開始日」を取得する部分の

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

 という計算式が肝になります。

support.office.com

 

 

例題の計算式の解説

 

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になります。

そういう動きをするので、最後の「A6:A11」(開始日の列)の部分で合計されるのは、「前の条件が1になったもの=両方ともTrueだったもの」だけなのですね。

だから、履歴の中で条件を満たす行が1つしかなければ、その行の開始日だけが取得される。

まあ。

そういう仕掛けです。

 

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

 

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

例えば、こんな例。

f:id:arakan_no_boku:20190225150956j:plain

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

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

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

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

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

これは、超便利な関数です。

だけど、ちょっと癖がありますけど、覚えて損はないと思います。

 

ちなみに

 

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

support.office.com

ではでは。