日付から年や月ごとの人数･金額を調べる［演習用Excelシート付］

日付から年や月を条件にして集計するには、年が条件の場合はYEAR関数を使用して日付から年を、月が条件の場合はMONTH関数を使用して日付から月を取り出します。そして、取り出した年や月を、SUMPRODUCT関数を使って集計します。

以下で詳しく説明します。

説明に使用している例題の入ったExcelシート［演習用Excelシート］

日付から年ごとの数を求める

日付から年ごとの人数を求める

日付から年ごとの人数や個数を求める場合には、まず、YEAR関数で、日付のセル範囲から「年」を取り出します。

今、以下のようにA列に入会日の日付が入っているとき、2022年に入会した人の数をF2セルに求めてみます。なお、ここでは、SUMPRODUCT関数を使います。

SUMPRODUCT関数は引数が「配列」なので、少々難しいですが、COUNTIF関数で求める場合のように、検索条件を指定するためのセルがなくても算出できるのがメリットです。

今、E2セルには、求めたい入会年である2022（年）が入力されているので、YEAR(A2:A10)=E2 とします。

これは、入会日のセル範囲から「年」を取り出し、E2セルに等しいかを判定するという意味です。

この部分の判定結果は論理値になりますので、1を掛けて数値化します。

したがって、SUMPRODUCT関数の第1引数は、(YEAR(A2:A10)=E2)*1 となり、F2セルには、=SUMPRODUCT((YEAR(A2:A10)=E2)*1) と入力します。

これで、2022年に入会した人の数を求めることができます。

ちなみに、E2セルを2023（年）に変えてみると、人数が3に変化します。

日付から年ごとの金額を求める

今度は、C列がコース名ではなく、会費になっています。このとき、2022（年）の会費の合計を求めてみます。

さきほどの第1引数を利用すれば簡単で、第2引数に、C2:C10 という配列を追加するだけです。

したがって、F2セルには、=SUMPRODUCT((YEAR(A2:A10)=E2)*1,C2:C10) と入力します。

これで、2022年に入会した人の会費を求めることができます。

日付から月ごとの数を求める

日付から月ごとの人数を求める

日付から月ごとの人数や個数を求める場合には、まず、MONTH関数で、日付のセル範囲から「月」を取り出します。

つまり、さきほど説明したYEAR関数をMONTH関数に置き換えるだけです。

日付から月ごとの金額を求める

さきほどと同じように、C列に会費が入っています。このとき、11（月）の会費の合計を求めてみます。

これも、YEAR関数をMONTH関数に置き換えるだけです。

日付から年月ごとの数を求める

日付から年月ごとの人数を求める

さて、次は、日付から年月ごとの人数を求めてみましょう。以下のような場合に、2022年11月の人数を求めます。

今回の場合は「2022年、かつ、11月」というAND条件です。AND条件での集計には、SUMIFS関数を使用するのが一般的ですが、SUMPRODUCT関数でもできます。使い方を知っておくと、いろいろな場面で応用ができますので紹介しておきます。

AND条件の場合は、AND条件を［*］でつなげます。

式で示すと、=SUMPRODUCT((セル範囲1=条件1)*(セル範囲2=条件2)*1) となります。

ここでは、条件1がE2セルに、条件2がF2セルに入っていますので、E5セルに人数を求めます。

E5セルは =SUMPRODUCT((YEAR(A2:A10)=E2)*(MONTH(A2:A10)=F2)*1) となります。

難しそうに見えますが、これまで出てきた、YEAR関数とMONTH関数を［*］でつないでいるだけなのがわかります。

日付から年月ごとの金額を求める

日付から年月ごとの金額を求めるには、さきほどと同じように、第2引数にC列を指定するだけです。

したがって、F5セルは、=SUMPRODUCT((YEAR(A2:A10)=E2)*(MONTH(A2:A10)=F2)*1,C2:C10) となります。

最後まで、読んでいただきありがとうございました。