"BOKU"のITな日常

BOKUが勉強したり、考えたことを頭の整理を兼ねてまとめてます。

基準日が開始日と終了日の間にあるデータを取得する計算式の組み方/EXCELの小ネタ

f:id:arakan_no_boku:20190201221826j:plain

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

目次

履歴から基準日が有効期間に含まれるものを取得する

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

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つしかなければ、その行の開始日だけが取得される・・まあ・・そういう仕掛けです。

 計算式サンプルの応用例とか

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

例えば、こんな例。

f:id:arakan_no_boku:20190225150956j:plain

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

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

集計対象がC列の会費になっているので、条件に一致する行がのみ合計されることになります。

一見、ややこしく見えますけど、じっくり見てみるとわかりますし、かなり、応用の聞く関数なので、覚えておいて損はないのではないかと思います。 

ではでは。

#EXCEL