開始日・終了日をもとに年月毎の件数を別ファイルへカウント
ここでは、開始日・終了日をもとに年月毎の件数を別ファイル（ブック）へ関数でカウントする方法を解説します。
別ブックでのカウントのため、COUNTIFS関数はエラーになるので使えません。
代わりの関数「SUMPRODUCT関数」を使った方法の説明です。
１．まずはやりたいことを確認
始めに、やりたいことを例を含めて確認します。
１－１．開始日と終了日から年月毎の件数を別ファイルに表示させたい
やりたいことは、「開始日と終了日から年月毎の件数を別ファイルに表示させる」です。
前のページで解説した数式は、別ファイルに結果を表示させようとすると、エラーになることがあります。
そこでここでは、別ファイルへの表示でもエラーにならない数式を説明します。
１－２．例を確認
一つ例を見てみましょう。
上の表は、あるセミナーの受講開始日と終了日をまとめた表です。受講者ごとに開始日が、人によっては終了日も入力されています。
先ほどの表をもとに、別のエクセルファイルで月ごとの実施中の件数をカウントしたい、というのが「開始日・終了日をもとに月毎の件数をカウントする」です。
始まっていてまだ終わっていない人を月ごとに何人いるか数える、ということです。
２．SUMPRODUCT関数とは
次に、数式に使うSUMPRODUCT関数の使い方を確認します。
SUMPRODUCT関数は様々なことができる関数ですが、COUNTIF関数と同じく「特定の条件に一致するデータの件数をカウントする」ことができます。
ただ一つCOUNTIFS関数と違うのは「他のファイルを引数で参照してもエラーにならない」ことです。そのため、他のファイルにある表をもとにカウントする場合には、SUMPRODUCT関数を使います。
SUMPRODUCT関数の数式の構文は、次の通りです。
=SUMPRODUCT（①配列1,②配列2…)）
そして「特定の条件に一致するデータの件数をカウントする」場合には、配列①のみ使用し、ここに範囲と条件をまとめて入れます。
| 項目名 | 指定する内容 | 指定の例 |
|
① 配列1 |
条件を指定（1つでも複数でも全てここで指定） |
（D2:D13="売上"）*1 |
言い換えると数式は、
=SUMPRODUCT（条件式）
の形になるということです。
とてもシンプルですが、条件式の入れ方には以下のポイントがあります。
① 条件式はかっこで囲む
※ 例）（D2:D13="売上"）
② 条件式が一つの場合には「*1」を付ける
※ 例）（D2:D13="売上"）*1
② 条件式が一つの場合には「③ 条件式が複数の場合には「*」でつなぐ
※ 例）（D2:D13="売上"）*（E2:E13="A店"）
これら３つのポイントを踏まえて条件式を書きましょう。
３．数式の作り方
では続いて、「開始日・終了日をもとに実施中の件数を月ごとにカウント・集計」できる数式の作り方を解説します。
使う表は先ほどの例のものと同じ「セミナーの受講開始日・終了日一覧表」です。
ではさっそく作ってみましょう。
３－１．カウント結果を表示する表を作成する
まずは、カウント結果を表示するための表を作成します。
イメージは上のような形で、必要な項目は「月初の日付」です。
これには関数を使った簡単な方法がありますが、長くなってしまうのと別の話になってしまうのでここではカットします。
ちょっと面倒ですが、「年月」に毎月1日付の日付（例：2018/12/1）を手入力し、表示形式で「年月」の形にしましょう。
ちなみに、求めたい年月の次の月まで入れてください。
３－２．カウントのための数式を作る
次に、別ファイルへカウントするための数式を作ります。ここでは、Book1のSheet1にある表をもとに、セルB2に数式を作ってみます。
数式は、次のイメージで作成します。
= ① AND（ ② OR ③ ）
① 開始日が当月以前の人
② 終了日が当月1日以降の人
③ （開始日が入っていて）終了日が入っていない人
※ 開始日は必ず入っていることが前提です
※「当月以前」は当月も含みます
以下手順です。
（１）数式バーに「=SUMPRODUCT((」と入力する
（２）元の表の「開始日」の範囲を絶対参照「$」付きで指定する
（３）「<」を入力後、「次の月の日付が入ったセル番号」を指定する
※ 例）「<A3」
（４）「)*((」と入力する
（５）元の表の「終了日」の範囲を絶対参照「$」付きで指定する
（６）「>=」を入力後、「次の月の日付が入ったセル番号」を指定する
※ 例）「>=A2」
（７）「)+()」と入力する
（８）元の表の「終了日」の範囲を絶対参照「$」付きで指定する
（９）「="")))」と入力する
（１０）入力した数式を下のセルにコピーすれば完了
例の場合、セルB2に入力した数式は次のようになります。
=SUMPRODUCT(([Book1.xlsx]Sheet1!$C$4:$C$18<A3)*(([Book1.xlsx]Sheet1!$D$4:$D$18>=A2)+([Book1.xlsx]Sheet1!$D$4:$D$18="")))
ちょっと長いですが、これで「開始日・終了日をもとに実施中の件数を月ごとにカウント・集計」することができます。
４．数式の根拠を解説
ではなぜ「①開始日が当月以前の人」AND（「②終了日が当月1日以降の人」OR「③（開始日が入っていて）終了日が入っていない人」）で月ごとのカウントができるのでしょうか？
まず①の「開始日が当月以前の人」では、「カウントする月より前に開始した人の数」を数えます。
次に②の「終了日が当月1日以降の人」では、「カウントする月の1日より後に終了した人」を数えます。
続いて③の「（開始日が入っていて）終了日が入っていない人」では、「まだ終了していない人の数」を数えます。
そして「カウントする月より前に開始した人」で（AND）「カウントする月の1日より後に終了した人」か（OR）「まだ終了していない人」を数えているのです。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】