スプレッドシートで月別集計時の注意点

日次で取得しているデータを月別に集計するケースはよくあります。そのような場合に注意する点がありますので集計のやり方と注意点について説明します。

月別集計の仕方

集計対象となる商品名一覧を作成するにはUNIQUE関数を使って作成しています。
もちろん、商品マスターにアクセスできるなら商品マスターから商品名を抽出して作成するのがパーフェクトです。

売上月については該当月の月初1日を指定します。その上で書式設定で「yyyy年mm月」と表示するようにします。文字列で「2023年01月」と入力するのはNGです。

集計には「SUMIFS」関数を使用します。SUMIFS関数の構文は以下の通りです。

SUMIFS

SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, …])
合計範囲：集計対象の範囲
条件範囲1：条件1で設定されたものを判定する範囲
条件1：
条件範囲2, 条件2, ... – [省略可]

以下の式で月別の集計を行うことができます。

=SUMIFS($C:$C,$A:$A,">="&F$1,$A:$A,"<="&EOMONTH(F$1,0),$B:$B,$E2)

以下の図で、SUMIFS関数の使い方を順番に説明していきます。

①合計範囲：集計する対象の列を指定しています。今回の場合はC列の「販売金額」です。コピペしてもずれないように絶対参照にしています。

②条件範囲1、条件1：A列の「売上日」を条件範囲として、F1列にある日付と等しいか大きい値を条件にしています。「売上日」のA列はコピペしてもずれないように絶対参照にしています。「日付」のF1列は行のみずれないように絶対参照にしています。

③条件範囲2、条件2：A列の「売上日」を条件範囲として、F1列にある日付の月末と等しいか大きい値を条件にしています。けづまつは「EOMONTH」関数で求めています。「売上日」のA列はコピペしてもずれないように絶対参照にしています。「日付」のF1列は行のみずれないように絶対参照にしています。

④条件範囲3、条件3：B列の「商品名」を条件範囲として、E列にある商品名と等しいことを条件にしています。「商品名」のB列はコピペしてもずれないように絶対参照にしています。「商品名」のE2列は列のみずれないように絶対参照にしています。

ここで売上日に時間が含まれている場合について考えてみます。

これだけだと集計表には変化がなく問題ないように思えます。ここで1/31のデータの「売上日」を変更してみます。

売上日を『2023/01/31 0:00:00』→『2023/01/31 0:00:01』へ変更すると2023年1月の売上額が300円から200円へ減少してしまいました。これは『2023/01/31 0:00:01』にしたデータが集計対象から外れたためです。

ところが2023年2月の売上額は200円のままです。当然、2023年2月の売上額は下記の条件となっているため『2023/01/31 0:00:01』にしたデータは集計対象外のためです。

2023/02/01>=売上日>=2023/02/28

この原因は

2023/01/01>=売上日>=2023/01/31
2023/02/01>=売上日>=2023/02/28

とありますが時間まで考慮すると実質は以下の様な条件になっているためと考えられます。

2023/01/01 0:00:00>=売上日>=2023/01/31 0:00:00
2023/02/01 0:00:00>=売上日>=2023/02/28 0:00:00

このことを考慮して正しい結果にするには以下の様に数式を変更します。

=SUMIFS($C:$C,$A:$A,">="&F$1,$A:$A,"<"&EOMONTH(F$1,0)+1,$B:$B,$E2)

『EOMONTH(F$1,0)+1』とすることで月末ではなく翌月の月初にして、『<=』を『<』とすることで問題を回避することができます。

日付データで条件判定する場合は注意が必要です。今回の例で示したように時間が含まれる場合、たとえばフォームで受け取ったタイムスタンプが該当します。

また、見かけ日付のように見えていても単なる文字列の場合もあります。日付データを入力する場合はデータ入力規則などを使って日付を入れるのが安全でしょう。また、yyyy年mm月やyymmなどとしたい場合は書式で変更して対応すべきです。
文字列にしてしまうと数式で使えないデータとなってしまいます。