目次
履歴から基準日時点データを取得する計算式
SQLでは簡単なのに、EXCELでやると意外に難しい「履歴から基準日が有効期間に含まれるものを取得する」処理を、SUMPRODUCT関数を使って実現します。
説明用サンプルケース
例えば、こんなやつです。
履歴があって、基準日（上記例だと 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セルに仕込むと以下のような結果が返ってきます。
正しいイベント名をとってきてます。
計算式サンプルの仕掛けの説明
この計算式は、2段階の処理をしています。
つまり。
- 条件を処理して、VLOOKUPのキーにできる値を取得する
- 上記で取得した値をキーにしてVLOOKUPをかける
です。
上記の例でいくと。
- 基準日を開始日と終了日の間に含む行の「開始日」を取得する。
- 上記で取得した開始日をキーにしてVLOOKUPをかける。
とやっているわけです。
この基準日を開始日と終了日の間に含む行の「開始日」を取得する部分の
SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),A6:A11
という計算式が肝になります。
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だったもの」だけです。
だから、履歴の中で条件を満たす行が１つしかなければ、その行の開始日だけが取得される・・まあ・・そういう仕掛けです。
計算式サンプルの応用例とか
その応用で、会員資格の有効期間があって、集金基準日時点の在籍者のみ会費を集めるとしたら、2019/1/16時点で集金すべき金額はいくらでしょうか？みたいな例題もSUMPRODUCT関数で対応することができます。
例えば、こんな例。
この場合も、計算式自体は上記のSUMPRODUCT部分とほぼ一緒。
=SUMPRODUCT((A6:A11<=B1)*(B6:B11>=B1),C6:C11)
集計対象がC列の会費になっているので、条件に一致する行がのみ合計されることになります。
一見、ややこしく見えますけど、じっくり見てみるとわかりますし、かなり、応用の聞く関数なので、覚えておいて損はないのではないかと思います。
ではでは。
