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

スポンサードリンク

問題　　　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関数を使って求める	FILTER関数を使って求める
SUMIFS関数で合計を求める	SUMIF関数で合計を求める
SUMPRODUCT関数を使って求める	配列数式を使って合計する
小計(集計)機能を使って集計する	ピボットテーブルで計算する(集計する)

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($C$3:$C$21,MONTH($B$3:$B$21)&"月"=E3))　と入力します。
  F3:F7セルを選択して、[Ctrl]+[D]でF3セルのF4:F7セルに数式をコピーします。
  または、F3セルを選択して、フィルハンドルをダブルクリックして、オートフィルで数式をコピーします。
  

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

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

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

• SUMIFS関数の詳細な使い方は　複数の条件で合計する関数(SUMIFS関数)の使い方:Excel関数　をご覧ください。
• SUMIFS関数はExcel2007以降で使用可能です。
  SUMIFS関数で、開始日以上、終了日未満の合計を求めます。
  具体的には
  4月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2025/4/1",$B$3:$B$21,"<2024/5/1")
  5月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2025/5/1",$B$3:$B$21,"<2025/6/1")
  6月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2025/6/1",$B$3:$B$21,"<2025/7/1")
  7月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2025/7/1",$B$3:$B$21,"<2025/8/1")
  8月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2025/8/1",$B$3:$B$21,"<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($C$3:$C$21,
  $B$3:$B$21,">="&DATE(2025,ROW(A4),1),
  $B$3:$B$21,"<"&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($C$3:$C$21,
  $B$3:$B$21,">="&DATE(2024,E3,1),
  $B$3:$B$21,"<"&DATE(2024,E3+1,1))
  と入力します。
  F3セルの数式を　F4:F7セルにオートフィルなどでコピーします。
• 

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

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

1. SUMIFS関数はExcel2007以降で使用可能です。もしそれより前のバージョンをお使いの場合はSUMIF関数で代用します。
   この場合は、4月は　2024/4/1以上の合計金額から、2024/5/1以上の合計金額を差し引いて求めます。
   =SUMIF($B$3:$B$21,">=2025/4/1",$C$3:$C$21)
   -SUMIF($B$3:$B$21,">=2025/5/1",$C$3:$C$21)
   5月は2024/5/1以上の合計金額から、2024/6/1以上の合計金額を差し引いて求めます。
   =SUMIF($B$3:$B$21,">=2025/5/1",$C$3:$C$21)
   -SUMIF($B$3:$B$21,">=2025/6/1",$C$3:$C$21)
   といった具合になります。
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($B$3:$B$21,">="&DATE(2025,ROW(A4),1),,$C$3:$C$21)
   -SUMIF($B$3:$B$21,">="&DATE(2025,ROW(A5),1,),$C$3:$C$21)
   として、下方向へ数式をオートフィルなどでコピーします。
   

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($B$3:$B$21,">="&DATE(2024,E3,1),$C$3:$C$21)
  -SUMIF($B$3:$B$21,">="&DATE(2024,E3+1,1),$C$3:$C$21)
  と入力します。
  F3セルの数式を　F4:F7セルにコピーします。
  

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

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

日付の欄が空白だと、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($B$3:$B$21)=ROW(A4),$C$3:$C$21))
  と入力して、下方向へ数式をコピーします。
• Excel2019以前のバージョンでは
  F3セル(4月)には
  =SUM(IF(MONTH($B$3:$B$21)=4,$C$3:$C$21))
  と入力して、Shift + Ctrl + Enter　で入力を確定します。
  数式は　{=SUM(IF(MONTH($B$3:$B$21)=4,$C$3:$C$21))}　とかっこ { } でくくられます。
  そのあと、下方向へコピーします。
•