日付と共に記録されたデータを集計したいことがあります。
例えば、下図のようにある商品の売上データがあったとき、これを月ごとに集計したいなら、ピボットテーブルを使うこともできますね。
でも、今回は他の方法で集計してみます。
(1) SUMIFS関数を使う
SUMIFS関数は、複数の検索条件に一致するすべての引数を合計します。
その構文は、次のようになります：
SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
詳細は、Microsoftのサポートページ「SUMIFS関数」をご参照ください。
上の表データについて、月で集計してみます。
この表に、A列「売上日」データを参照して「月」を表示する「月」列を挿入します。
そこには、例えば「=MONTH(A2)」のように MONTH関数を使います。
月ごとの「売上額」の合計は下図のようになります。
H列に「月」、I列に「売上額」の合計を求めました。
例えば、セルI2には「=SUMIFS($F$2:$F$89,$B$2:$B$89,H2)」と入力し、以下にコピーします。
B列に MONTH関数で「月」だけを取り出していますので、もしデータがこの年だけでなく複数年のものであれば、年に関係なく月ごとの売上額を合計することになります。
季節要因とか月ごとの売上傾向などを調べるにはこのようにします。
「年月」で集計するなら、「年」列を設けて、年データを条件に加えればよろしいです。
あるいは、以下の方法もあります。
下図のように、「集計年月」「開始日」「終了日」列を設け、「集計年月」には例えば「2020/1/1」と入力し、表示形式で「yyyy/m」と設定します。
「開始日」にはそのまま「=G2」のように集計年月を参照し、表示形式で「yyyy/m/d」と設定します。
「終了日」には「=EOMONTH(H2,0)」のように「開始日」の月末日を求め、こちらも表示形式で「yyyy/m/d」と設定します。
「売上額」列には例えばセルJ2には次のように入力します：
=SUMIFS($E$2:$E$89,$A$2:$A$89,">="&H2,$A$2:$A$89,"<="&I2)
つまり、A列「売上日」が「開始日」以降で「終了日」以前の E列「売上額」の合計を求めています。
この例では年月ごとに集計していますが、他の日付単位での集計とすることもできます。
例えば、「終了日」を 3ヶ月後の日付とすれば 3ヶ月ごとの集計とすることができます。
業態によっては週単位での集計が求められることもあります。
WEEKNUM関数は、日付が1月1日の週から数えて何週目にあたるかを求める関数です。
これを使って週単位での集計が容易に求められます。
下図では、B列に「週」列を設け、WEEKNUM関数を使って週数を入力してあります。
I列に「売上額」の集計データを示しました。
例えば、セルI2には「=SUMIFS($F$2:$F$89,$B$2:$B$89,H2)」と入力されています。
同様に、曜日単位で集計するなら、WEEKDAY関数を使って行います。
下図において、B列に「曜日」列を設け、WEEKDAY関数を使って曜日を数値で入力してあります。
WEEKDAY関数の第2引数を省略したので、日曜日が「1」、月曜日が「2」‥‥土曜日が「7」となっています。
これに対し、例えば セルI2に「=SUMIFS($F$2:$F$89,$B$2:$B$89,1)」と入力して日曜日の売上額を集計しています。
なお、これらの例のうち、検索条件が 1つであるものは SUMIF関数を使ってもできます。
(2) SUMPRODUCT関数を使う
SUMPRODUCT関数は、対応する範囲または配列の積の合計を返し、その構文は次のようになります：
SUMPRODUCT(array1, [array2], [array3], ...)
上と同じ表データを使って、月ごとの集計を行います。
上記 SUMIFS関数では数式中に使えなかった関数が SUMPRODUCT関数では使えます。
G列「月」に集計しようとする月を列記し、H列「売上額」の例えばセルH2に「=SUMPRODUCT((MONTH($A$2:$A$89)=G2)*1,$E$2:$E$89)」と入力します。（下図）
第1引数の中の「(MONTH($A$2:$A$89)=G2」は A列「売上日」について MONTH関数で月を求め、それがセルG2「1」と等しいときは「TRUE」、そうでないときは「FALSE」となる配列を返します。
それに「1」を乗ずることで「1」と「0」からなる配列に変換しています。
あとは、この配列と E列「売上額」の配列とを要素ごとに掛け算し、最終的にその和を求めています。
これで、年に関係なく月ごとの売上額を集計できます。
今度は、曜日ごとの集計を行います。
上記「月」ごとの集計と同じ要領でできます。
下図において、セルH2には「=SUMPRODUCT((WEEKDAY($A$2:$A$89)=1)*1,$E$2:$E$89)」と入力します。
WEEKDAY関数も配列を戻すことが出来ます。
でも、WEEKNUM関数は 1つの値だけを返すので、この方法では週単位での集計はできません
