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

スポンサードリンク

問題　　　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	2017/4/10	60,888		4月
  4	2017/4/17	77,526		5月
  5	2017/4/24	59,137		6月
  6	2017/5/1	65,948		7月
  7	2017/5/8	94,066		8月
  8	2017/5/15	97,416
  9	2017/5/22	71,710
  10	2017/5/29	65,348
  11	2017/6/5	89,012
  12	2017/6/12	61,881
  13	2017/6/19	83,615
  14	2017/6/26	95,066
  15	2017/7/3	70,941
  16	2017/7/10	92,775
  17	2017/7/17	85,501
  18	2017/7/24	51,587
  19	2017/7/31	99,850
  20	2017/8/7	83,203
  21	2017/8/14	87,186

問題1の解答例　　　　topへ

• 解答例
  下表の計算結果になります。
  

  	B	C	D	E	F
  2	日付	販売金額			合計金額
  3	2017/4/10	60,888		4月	197,551
  4	2017/4/17	77,526		5月	394,488
  5	2017/4/24	59,137		6月	329,574
  6	2017/5/1	65,948		7月	400,654
  7	2017/5/8	94,066		8月	170,389
  8	2017/5/15	97,416
  9	2017/5/22	71,710
  10	2017/5/29	65,348
  11	2017/6/5	89,012
  12	2017/6/12	61,881
  13	2017/6/19	83,615
  14	2017/6/26	95,066
  15	2017/7/3	70,941
  16	2017/7/10	92,775
  17	2017/7/17	85,501
  18	2017/7/24	51,587
  19	2017/7/31	99,850
  20	2017/8/7	83,203
  21	2017/8/14	87,186

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

FILTER関数を使って求める
SUMIFS関数で合計を求める	SUMIF関数で合計を求める
SUMPRODUCT関数を使って求める	配列数式を使って合計する
小計(集計)機能を使って集計する	ピボットテーブルで計算する(集計する)

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

• Microsoft365ではFILTER関数が使えます。
  FILTER関数の詳細な使い方は　FILTER関数でデータを抽出する をご覧ください。

1. FILTER関数で各月の販売額を配列に取り出して、SUM関数で合計するといった手順になります。
   F3セルに　=SUM(FILTER($C$3:$C$21,MONTH($B$3:$B$21)&"月"=E3))　と入力します。
   
2. 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関数　をご覧ください。

1. SUMIFS関数はExcel2007以降で使用可能です。
   SUMIFS関数で、開始日以上、終了日未満の合計を求めます。
   具体的には
   4月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2017/4/1",$B$3:$B$21,"<2017/5/1")
   5月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2017/5/1",$B$3:$B$21,"<2017/6/1")
   6月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2017/6/1",$B$3:$B$21,"<2017/7/1")
   7月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2017/7/1",$B$3:$B$21,"<2017/8/1")
   8月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2017/8/1",$B$3:$B$21,"<2017/9/1")
   となります。
   
   • 5つも数式を書くのが面倒・・・といった場合は
     F3セル(4月)の数式を　=SUMIFS($C$3:$C$21,$B$3:$B$21,">="&DATE(2017,ROW(A4),1),$B$3:$B$21,"<"&DATE(2017,ROW(A5),1))　として、下方向へコピーすることも可能です。
   

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

  F3セルに　=SUMIFS($C$3:$C$21,$B$3:$B$21,">="&DATE(2017,E3,1),$B$3:$B$21,"<"&DATE(2017,E3+1,1))　と入力します。
  F3セルの数式を　F4:F7セルにコピーします。
  
• Microsoft365ではSpill機能が使えますので、数式を下のように書くことができます。
  =SUMIFS($C$3:$C$21,$B$3:$B$21,">="&DATE(2017,E3:E7,1),$B$3:$B$21,"<"&DATE(2017,E3:E7+1,1))
  

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

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

1. SUMIFS関数はExcel2007以降で使用可能です。もしそれより前のバージョンをお使いの場合はSUMIF関数で代用します。
   この場合は、4月は　2017/4/1以上の合計金額から、2017/5/1以上の合計金額を差し引いて求めます。
   =SUMIF($B$3:$B$21,">=2017/4/1",$C$3:$C$21)-SUMIF($B$3:$B$21,">=2017/5/1",$C$3:$C$21)
   

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

  F3セルに　=SUMIF($B$3:$B$21,">="&DATE(2017,E3,1),$C$3:$C$21)-SUMIF($B$3:$B$21,">="&DATE(2017,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月の集計ができないケースがあります。
  下図では　=SUMPRODUCT((MONTH(B2:B9)=1)*C2:C9)　として計算しています。
  
  日付の欄が空白になっていたら・・・
  空欄が「1月」と判断され計算結果が変わってしまいました。
  
  原因はMONTH関数で空欄のセルを参照すると 1 が返されるためです。
  
• 対策としては、空欄でないという条件を付ける必要があります。
  =SUMPRODUCT(((B2:B19)<>"")*(MONTH(B2:B19)=1)*C2:C19)
  =SUMPRODUCT((LEN(B2:B19)<>0)*(MONTH(B2:B19)=1)*C2:C19)
  とすることが考えられます。
  

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

  F3セルに　=SUMPRODUCT((MONTH($B$3:$B$21)=E3)*($C$3:$C$21))　と入力します。
  F3セルの数式を　F4:F7セルにコピーします。
  

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

1. 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))}　とかっこ { } でくくられます。
   • コピーして使用するには　=SUM(IF(MONTH($B$3:$B$21)=ROW(A4),$C$3:$C$21))　といった具合に入力して、Shift + Ctrl + Enter　で入力を確定します。そのあと、下方向へコピーします。
   

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

  F3セルに　=SUM(IF(MONTH($B$3:$B$21)=E3,$C$3:$C$21))　と入力して、Shift + Ctrl + Enter　で入力を確定します。
  F3セルの数式を　F4:F7セルにコピーします。
  

小計(集計)機能を使って集計する　　　　topへ

1. 集計するといえば、集計機能を使う方法もありますが・・・データをそのままではちょっと無理があります。
   作業列を作成します。
   D3セルには　=MONTH(B3)&"月" と数式を入力し、フィルハンドルをダブルクリックして数式を下方向へコピーします。
   ここのデータは月順に並んでいますのでそのままでOKですが、月順でない場合は　月の列をキーにしてデータを並べ替える必要があります。
   
2. データ範囲　B2:D21を選択して、[データ]タブの[小計]を実行します。
   
3. グループの基準で「月」、集計の方法で「合計」、集計するフィールドで「販売金額」を選択します。
   [OK]ボタンをクリックします。
   
4. 月ごとに集計ができました。
   
5. アウトラインの「2」をクリックして集計行のみを表示することができます。(データを折りたたみます)