月ごとに集計する(合計する):エクセル練習問題

問題 topへ

• 問題1:B列に年月日が入力されています。C列に販売金額が入力されています。
  E列には月名が入力してあります。F列はE列の各月ごとに販売金額を合計しなさい。 問題1の解答
  E3:E7セルには 4月、5月、6月、7月、8月と入力されています。
  E3:E7セルに4,5,6,7,8 と数値が入力されている場合の解答例も付けています。(こちらの方が簡単です)
  
  B C D E F
  2 日付 販売金額 合計金額
  3 2025年4月10日 60,888 4月
  4 2025年4月17日 77,526 5月
  5 2025年4月24日 59,137 6月
  6 2025年5月1日 65,948 7月
  7 2025年5月8日 94,066 8月
  8 2025年5月15日 97,416
  9 2025年5月22日 71,710
  10 2025年5月29日 65,348
  11 2025年6月5日 89,012
  12 2025年6月12日 61,881
  13 2025年6月19日 83,615
  14 2025年6月26日 95,066
  15 2025年7月3日 70,941
  16 2025年7月10日 92,775
  17 2025年7月17日 85,501
  18 2025年7月24日 51,587
  19 2025年7月31日 99,850
  20 2025年8月7日 83,203
  21 2025年8月14日 87,186

問題1の解答例 topへ

• 解答例が複数ありますので、使い勝手の良いものを利用してください。

GROUPBY関数を使って求める topへ

• GROUPBY関数はExcel for Microsoft365で使用できます。
  この関数とTEXT関数を使うと一発で計算できます。
• E3セルに =GROUPBY(TEXT(B3:B21,"m")&"月",C3:C21,SUM) と入力します。
  

FILTER関数を使って求める topへ

• Excel for Microsoft365,Excel2021以降のバージョンではFILTER関数が使えます。
  FILTER関数の詳細な使い方は FILTER関数でデータを抽出する をご覧ください。
• FILTER関数で各月の販売額を配列に取り出して、SUM関数で合計するといった手順になります。
  F3セルに =SUM(FILTER($C3ドル:$C21,ドルMONTH($B3ドル:$B21ドル)&"月"=E3)) と入力します。
  F3:F7セルを選択して、[Ctrl]+[D]でF3セルのF4:F7セルに数式をコピーします。
  または、F3セルを選択して、フィルハンドルをダブルクリックして、オートフィルで数式をコピーします。
  

• E3:E7セルに月の数値が入力されている場合

  F3セルに =SUM(FILTER($C3ドル:$C21,ドルMONTH($B3ドル:$B21ドル)=E3)) と入力します。
  F3セルの数式を F4:F7セルにコピーします。
  
  • E3:E7は数値が入力されていますが、表示形式で 0"月" とすると、セルに 月 を表示することができます。
    セルの値は 数値のままですので、上記のように数式の見た目がスマートになります。
    

SUMIFS関数で合計を求める topへ

• SUMIFS関数の詳細な使い方は 複数の条件で合計する関数(SUMIFS関数)の使い方:Excel関数 をご覧ください。
• SUMIFS関数はExcel2007以降で使用可能です。
  SUMIFS関数で、開始日以上、終了日未満の合計を求めます。
  具体的には
  4月は =SUMIFS($C3ドル:$C21,ドル$B3ドル:$B21,ドル">=2025年4月1日",$B3ドル:$B21,ドル"<2024年5月1日")
  5月は =SUMIFS($C3ドル:$C21,ドル$B3ドル:$B21,ドル">=2025年5月1日",$B3ドル:$B21,ドル"<2025年6月1日")
  6月は =SUMIFS($C3ドル:$C21,ドル$B3ドル:$B21,ドル">=2025年6月1日",$B3ドル:$B21,ドル"<2025年7月1日")
  7月は =SUMIFS($C3ドル:$C21,ドル$B3ドル:$B21,ドル">=2025年7月1日",$B3ドル:$B21,ドル"<2025年8月1日")
  8月は =SUMIFS($C3ドル:$C21,ドル$B3ドル:$B21,ドル">=2025年8月1日",$B3ドル:$B21,ドル"<2025年9月1日")
  となります。
• 5つも数式を書くのが面倒・・・といった場合は
  Excel for Microsoft365、Excel2021ではSpill機能が使えますので、
  =SUMIFS(C3:C21,
  B3:B21,">="&DATE(2025,ROW(A4:A8),1),
  B3:B21,"<"&DATE(2025,ROW(A5:A9),1))
  と入力します。
• Excel2019以前のバージョンでは
  F3セル(4月)の数式を
  =SUMIFS($C3ドル:$C21,ドル
  $B3ドル:$B21,ドル">="&DATE(2025,ROW(A4),1),
  $B3ドル:$B21,ドル"<"&DATE(2025,ROW(A5),1))
  として、下方向へコピーすることも可能です。
  • ">="&DATE(2025,ROW(A4),1) は >=DATE(2025,4,1)
    "<"&DATE(2025,ROW(A5),1) は <DATE(2025,5,1) となります。

E3:E7セルに月の数値が入力されている場合

• Excel for Microsoft365、Excel2021ではSpill機能が使えますので、数式を下のように書くことができます。
  =SUMIFS(C3:C21,
  B3:B21,">="&DATE(2024,E3:E7,1),
  B3:B21,"<"&DATE(2024,E3:E7+1,1))
• Excel2019以前のバージョンでは
  F3セルに
  =SUMIFS($C3ドル:$C21,ドル
  $B3ドル:$B21,ドル">="&DATE(2024,E3,1),
  $B3ドル:$B21,ドル"<"&DATE(2024,E3+1,1))
  と入力します。
  F3セルの数式を F4:F7セルにオートフィルなどでコピーします。

SUMIF関数で合計を求める topへ

• SUMIF関数の詳細な使い方は 条件付きで合計する関数(SUMIF関数)の使い方) をご覧ください。

1. SUMIFS関数はExcel2007以降で使用可能です。もしそれより前のバージョンをお使いの場合はSUMIF関数で代用します。
   この場合は、4月は 2024年4月1日以上の合計金額から、2024年5月1日以上の合計金額を差し引いて求めます。
   =SUMIF($B3ドル:$B21,ドル">=2025年4月1日",$C3ドル:$C21ドル)
   -SUMIF($B3ドル:$B21,ドル">=2025年5月1日",$C3ドル:$C21ドル)
   5月は2024年5月1日以上の合計金額から、2024年6月1日以上の合計金額を差し引いて求めます。
   =SUMIF($B3ドル:$B21,ドル">=2025年5月1日",$C3ドル:$C21ドル)
   -SUMIF($B3ドル:$B21,ドル">=2025年6月1日",$C3ドル:$C21ドル)
   といった具合になります。
2. ひとつの数式にするなら、
   Excel for Microsoft365、Excel2021ではSpill機能が使えますので、数式を下のように書くことができます。
   =SUMIF(B3:B21,">="&DATE(2025,ROW(A4:A8),1),C3:C21)
   -SUMIF(B3:B21,">="&DATE(2025,ROW(A5:A9),1),C3:C21)
   
   Excel2019以前のバージョンでは
   =SUMIF($B3ドル:$B21,ドル">="&DATE(2025,ROW(A4),1),,$C3ドル:$C21ドル)
   -SUMIF($B3ドル:$B21,ドル">="&DATE(2025,ROW(A5),1,),$C3ドル:$C21ドル)
   として、下方向へ数式をオートフィルなどでコピーします。
   

E3:E7セルに月の数値が入力されている場合

• Excel for Microsoft365、Excel2021ではSpill機能が使えますので、数式を下のように書くことができます。
  =SUMIF(B3:B21,">="&DATE(2025,E3:E7,1),C3:C21)
  -SUMIF(B3:B21,">="&DATE(2025,E3:E7+1,1),C3:C21)
• Excel2019以前のバージョンでは
  F3セルに
  =SUMIF($B3ドル:$B21,ドル">="&DATE(2024,E3,1),$C3ドル:$C21ドル)
  -SUMIF($B3ドル:$B21,ドル">="&DATE(2024,E3+1,1),$C3ドル:$C21ドル)
  と入力します。
  F3セルの数式を F4:F7セルにコピーします。
  

SUMPRODUCT関数を使って求める topへ

• 4月の合計は =SUMPRODUCT((MONTH($B3ドル:$B21ドル)=4)*($C3ドル:$C21ドル)) として求めることができます。
  5月以降は =SUMPRODUCT((MONTH($B3ドル:$B21ドル)=5)*($C3ドル:$C21ドル)) と月の判定箇所を修正します。
• =SUMPRODUCT((MONTH($B3ドル:$B21ドル)=ROW(A4))*($C3ドル:$C21ドル)) としてコピーすれば、修正は必要なくなります。

日付の欄が空白だと、1月の集計ができないケースがあります

• ここではデータが2024年になっていますが、気にしないでください。
• 日付の欄が空白だと、1月の集計ができないケースがあります。
  下図では =SUMPRODUCT((MONTH(B2:B9)=1)*C2:C9) として計算しています。
  
  日付の欄が空白になっていたら・・・
  空欄が「1月」と判断され計算結果が変わってしまいました。
  
  原因はMONTH関数で空欄のセルを参照すると 1 が返されるためです。
  
• 対策としては、空欄でないという条件を付ける必要があります。
  =SUMPRODUCT(((B3:B11)<>"")*(MONTH(B3:B11)=1)*C3:C11)
  =SUMPRODUCT((LEN(B3:B11)<>0)*(MONTH(B3:B11)=1)*C3:C11)
  とすることが考えられます。
  

配列数式を使って合計する topへ

• Excel for Microsoft365、Excel2021では動的配列数式が使えますので、数式を下のように書くことができます。
  =SUM(IF(MONTH($B3ドル:$B21ドル)=ROW(A4),$C3ドル:$C21ドル))
  と入力して、下方向へ数式をコピーします。
• Excel2019以前のバージョンでは
  F3セル(4月)には
  =SUM(IF(MONTH($B3ドル:$B21ドル)=4,$C3ドル:$C21ドル))
  と入力して、Shift + Ctrl + Enter で入力を確定します。
  数式は {=SUM(IF(MONTH($B3ドル:$B21ドル)=4,$C3ドル:$C21ドル))} とかっこ { } でくくられます。
  そのあと、下方向へコピーします。