関数で月ごと・年月ごと・年ごとに値を抽出し合計・集計する

ここではエクセルで月ごと、年月ごと、年ごとに合計・集計する方法を解説します。

使う関数は、SUMIFS関数とSUMPRODUCT関数の２パターンあるため、それぞれの使い方を説明します。

また、関数ごとの欠点と状況別にどちらを使ったほうがいいかも紹介します。

始めに月、年月、年ごとに抽出・合計できる関数を２つ紹介します。

１－１．SUMIFS関数

一つ目は「SUMIFS関数」です。

SUMIFS関数は「特定の条件に一致する数値を合計し返す」関数です。

そのため、条件に期間を任意の指定することで、月、年月、年ごとの集計をすることができます。

SUMIFS関数の引数は、

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2…))

です。

それぞれの引数には、次のことを指定します。

１－２．SUMPRODUCT関数

二つ目は「SUMPRODUCT関数」です。

SUMPRODUCT関数は書き方によって様々なことができますが、SUMIFS関数と同じように「特定の条件に一致する数値を合計し返す」ことができます。

そのためこちらも条件に期間を任意の指定することで、月、年月、年ごとの集計ができます。

SUMPRODUCT関数の引数は、

=SUMPRODUCT(配列1,配列2…))

です。

それぞれの引数には、次のことを指定します。

まずは、SUMIFS関数を使って「月・年月・年」ごとに集計する方法から見てみましょう。

２－１．年関係なく月で集計する場合

始めに、SUMIFS関数を使って「年関係なく月で集計する場合」からです。

まず、下のような売上日と売上額をまとめた表があるとします。

日付のセルでは内部的には年のデータを持っていますが、見た目上は表示されていません。

この時何年か関係なく、SUMIFS関数を使って月ごとに集計したい時の方法になります。

このケースで一番簡単なのは「月を求める計算列を表に追加する」ことです。

まずは、MONTH関数を使って日付データから「月」の値を取り出します。

MONTH関数とは「指定したセルにある日付の月を返す」関数です。

MONTH関数の引数は、

=MONTH(シリアル値)

となっていますが、要は月を知りたいセル番号を指定すればOKです。

まずはMONTH関数で各日付の月をC列に求め、これをもとにSUMIFS関数で集計をするのです。

例のセルG2に入力した、4月の売上のみを合計する数式は、

=SUMIFS($D$2:$D$14,$C$2:$C$14,F2)

としています。

SUMIFS関数の引数は、

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2…))

でした。

つまり、合計対象範囲が「合計したい範囲」で「D2:D14」、検索範囲1が「月の値が入っている範囲」で「C2:C14」、条件1が「検索値」で「F2（値は「4」）」ということです。

これで年関係なく月ごとの売上額を合計できます。

ちなみに、MONTH関数をSUMIFS関数の中に組み込むことはできません。 

組み込んだ後に配列数式にしても、エラーになってしまいます。

２－２．年月で集計する場合

次に、SUMIFS関数を使って「年月で集計する場合」です。

下のような、売上日と売上額をまとめた表があるとします。

この表では年月日が表示されています。

この時、SUMIFS関数を使って年月ごとに集計したい時の方法になります。

このケースで一番簡単なのは「集計範囲を表示した表を準備する」ことです。

まずは、上の図の赤枠のように「集計をする範囲を指定した表」を準備します。

開始日は集計したい月の1日、終了日はその月の末日です。

なお、末日は

=翌月1日の日付が入ったセル番号－1

とすれば簡単に求められます。

そして、この範囲を使ってSUMIFS関数で集計します。

例のセルG2に入力した、4月の売上のみを合計する数式は、

=SUMIFS($C$2:$C$14,$B$2:$B$14,">="&F2,$B$2:$B$14,"<="&G2)

としています。

SUMIFS関数の引数は、

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2…))

でした。

つまり、合計対象範囲が「合計したい範囲」で「C2:C14」、検索範囲1が「月の値が入っている範囲」で「B2:B14」、条件1が「検索値」で「F2（値は「2019/4/1」）」、検索範囲2も「月の値が入っている範囲」で「B2:B14」、条件2も「検索値」で「G2（値は「2019/4/30」）」となります。

なお、SUMIFS関数で以上や以下の条件にする場合には、以上の場合「">="&」を、以下の場合「"<="&」を、条件値が入ったセル番号に付ける必要があり、ここがポイントです。

これで、年月ごとの売上額を合計できます。

２－３．年ごとに集計する場合

続いて、SUMIFS関数を使って「年ごとに集計する場合」です。

下のような、売上日と売上額をまとめた表があるとします。

この表から、SUMIFS関数を使って年ごとに集計したい時の方法になります。

この場合一番簡単なのは「年を求める計算列を表に追加する」ことです。

まずは、YEAR関数を使って日付データから「年」の値を取り出します。

YEAR関数とは「指定したセルにある日付の年を返す」関数です。

YEAR関数の引数は、

=YEAR(シリアル値)

となっていますが、要は年を知りたいセル番号を指定すればOKです。

まずはYEAR関数で各日付の年をC列に求め、これをもとにSUMIFS関数で集計をするのです。

例のセルG2に入力した、4月の売上のみを合計する数式は、

=SUMIFS($D$2:$D$14,$C$2:$C$14,F2)

としています。

SUMIFS関数の引数は、

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2…))

でした。

つまり、合計対象範囲が「合計したい範囲」で「D2:D14」、検索範囲1が「月の値が入っている範囲」で「C2:C14」、条件1が「検索値」で「F2（値は「2019」）」となります。

これで年ごとの売上額を合計できます。

２－４．SUMIFS関数は「他ブック参照」に不向き

最後に、SUMIFS関数の欠点を解説します。

SUMIFS関数の一番の欠点は「引数の参照に他ブックを指定すると、そのブックが閉じている時は#VALUE!エラー（値がないという意味のエラー）になる」ところです。

例えば「参照元の表があるファイル」と「集計した結果を表示させるファイル」が別だと、エラーになる時がある（というかほとんどの場合）のです。

ですから、SUMIFS関数を使うのは「同じファイル内で集計する場合」となります。

次に、SUMPRODUCT関数を使って「月・年月・年ごと」に集計する方法です。

３－１．年関係なく月ごとに集計する場合

始めに、SUMPRODUCT関数を使って「年関係なく月で集計する場合」からです。

まず、下のような売上日と売上額をまとめた表があるとします。

日付のセルでは内部的には年のデータを持っていますが、見た目上は表示されていません。

この時何年か関係なく、SUMPRODUCT関数を使って月ごとに集計したい時の方法になります。

ちなみにSUMPRODUCT関数のメリットは、「SUMIFS関数ではエラーになる関数を組み込める」点です。

SUMPRODUCT関数で条件式を、

(MONTH(日付の範囲)＝条件値)*1

と入力すれば、表の各日付からMONTH関数で月を抽出し、行ごとに条件値に合う、合わないを判定して、最終的に合うものだけを合計してくれるのです。

SUMPRODUCT関数の引数は、

=SUMPRODUCT(配列1,配列2…))

でした。

例えばセルF2に数式を入力する場合、配列1が「条件を指定」するところなので「(MONTH($B$2:$B$14)=E2)*1」、配列2が「合計する範囲」することろなので「C2:C14」となり、結果数式は、

=SUMPRODUCT((MONTH($B$2:$B$14)=E2)*1,$C$2:$C$14)

となります。

ポイントは「配列1でMONTH関数を使って日付の月を抽出する」こと、そして「条件が一つの場合はカッコでくくった条件に『*1』を付ける」ことです。

これで、年関係なく月ごとの売上額を合計できます。

３－２．年月で集計する場合

次に、SUMPRODUCT関数を使って「年月で集計する場合」です。

下のような、売上日と売上額をまとめた表があるとします。

この表では年月日が表示されています。

この時、SUMPRODUCT関数を使って年月ごとに集計したい時の方法になります。

こちらも先ほどの応用です。

表の各日付からYEAR関数とMONTH関数で年と月を抽出し、条件値と照らし合わせて一致するものだけを合計します。

条件式は、

(YEAR(日付の範囲)=年の条件値)*(MONTH(日付の範囲)=月の条件値)

です。

SUMPRODUCT関数の引数は、

=SUMPRODUCT(配列1,②配列2…))

でした。

配列1が「条件を指定」するところなので「(YEAR($B$2:$B$14)=YEAR(E2))*(MONTH($B$2:$B$14)=MONTH(E2))」、配列2が「合計する範囲」するところなので「C2:C14」となり、結果数式は、

=SUMPRODUCT((YEAR($B$2:$B$14)=YEAR(E2))*(MONTH($B$2:$B$14)=MONTH(E2)),$C$2:$C$14)

となります。

ポイントは「配列1に年と月の条件式を入れる」こと、そして「条件が二つの場合はカッコでくくった条件同士を『*』でつなげる」ことの２つです。

これで、年月ごとの売上額を合計できます。

３－３．年ごとに集計する場合

続いて、SUMPRODUCT関数を使って「年ごとに集計する場合」です。

下のような、売上日と売上額をまとめた表があるとします。

この表から、SUMPRODUCT関数を使って年ごとに集計したい時の方法になります。

こちらも先ほどと同様です。

表の各日付からYEAR関数で年を抽出し、条件値と照らし合わせて一致するものだけを合計します。

条件式は、

(YEAR(日付の範囲)=条件値)*1

です。

SUMPRODUCT関数の引数は、

=SUMPRODUCT(配列1,②配列2…))

でした。

配列1が「条件を指定」するところなので「(YEAR($B$2:$B$14)=E2)*1」、配列2が「合計する範囲を指定」するところなので「C2:C14」となり、結果数式は、

=SUMPRODUCT((YEAR($B$2:$B$14)=E2)*1,$C$2:$C$14)

となります。

ポイントは「配列1でYEAR関数を使って日付の年を抽出する」こと、そして「条件が一つの場合はカッコでくくった条件に『*1』を付ける」ことです。

これで、年ごとの売上額を合計できます。

３－４．SUMPRODUCT関数は分かりづらい

最後に、SUMPRODUCT関数の欠点を解説します。

SUMPRODUCT関数の一番の欠点は「何を求めているのか分かりづらい」ことです。

知名度が低いことと汎用性の利く関数であることが要因なのですが、その代わりSUMIFS関数のような「計算列」の必要がないことですっきりとした数式にできます。

特に、参照元の表と結果の表示が別のファイルの場合でも、SUMPRODUCT関数はエラーになりません。

ですから「別のファイル上で集計したい場合、または計算用の列を用意したくない時にはSUMPRODUCT関数を使う」ようにしましょう。

以上、参考になれば幸いです。

関数の問題解決記事一覧へはこちら