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

スポンサードリンク

問題　　　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へ

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

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

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,">=2024/4/1",$B$3:$B$21,"<2024/5/1")
  5月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2024/5/1",$B$3:$B$21,"<2024/6/1")
  6月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2024/6/1",$B$3:$B$21,"<2024/7/1")
  7月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2024/7/1",$B$3:$B$21,"<2024/8/1")
  8月は　=SUMIFS($C$3:$C$21,$B$3:$B$21,">=2024/8/1",$B$3:$B$21,"<2024/9/1")
  となります。
• 5つも数式を書くのが面倒・・・といった場合は
  F3セル(4月)の数式を
  =SUMIFS($C$3:$C$21,
  $B$3:$B$21,">="&DATE(2024,ROW(A4),1),
  $B$3:$B$21,"<"&DATE(2024,ROW(A5),1))
  として、下方向へコピーすることも可能です。
  • ">="&DATE(2024,ROW(A4),1) は　>=DATE(2024,4,1)　
    "<"&DATE(2024,ROW(A5),1) は　<DATE(2024,5,1)　となります。
  

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

• 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セルにコピーします。
  
• Excel for Microsoft365、Excel2024ではSpill機能が使えますので、数式を下のように書くことができます。
  =SUMIFS($C$3:$C$21,
  $B$3:$B$21,">="&DATE(2024,E3:E7,1),
  $B$3:$B$21,"<"&DATE(2024,E3:E7+1,1))
  

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

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

1. SUMIFS関数はExcel2007以降で使用可能です。もしそれより前のバージョンをお使いの場合はSUMIF関数で代用します。
   この場合は、4月は　2024/4/1以上の合計金額から、2024/5/1以上の合計金額を差し引いて求めます。
   =SUMIF($B$3:$B$21,">=2024/4/1",$C$3:$C$21)
   -SUMIF($B$3:$B$21,">=2024/5/1",$C$3:$C$21)
   5月は2024/5/1以上の合計金額から、2024/6/1以上の合計金額を差し引いて求めます。
   =SUMIF($B$3:$B$21,">=2024/5/1",$C$3:$C$21)
   -SUMIF($B$3:$B$21,">=2024/6/1",$C$3:$C$21)
   といった具合になります。
2. ひとつの数式にするなら、
   =SUMIF($B$3:$B$21,">="&DATE(2024,ROW(A4),1))
   -SUMIF($B$3:$B$21,">="&DATE(2024,ROW(A5),1)
   とすることができます。
   

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

• 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月の集計ができないケースがあります

• 日付の欄が空白だと、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)
  とすることが考えられます。
  

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

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

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

• 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」をクリックして集計行のみを表示することができます。(データを折りたたみます)
   
6. [Alt]キーを押しながら「;」(セミコロン)キーを押して、表示されている行のみを選択します。
   [Ctrl]+[C]でコピーする
   
7. [Ctl]+[V]で空いているセルに貼り付けます。
   
8. 集計したデータ範囲のセルを選択します。
   [データ]タブの[小計]を実行します。
   [すべて削除]をクリックします。
   
9. 貼り付けた集計データをF3セル以降に移動します。