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


スポンサードリンク

問題   topへ

問題1の解答例    topへ

  1. ピボットテーブルで計算する(集計する)     topへ

    1. 問題からはちょっと外れるかもしれませんが、一番簡単な集計方法です。
      [挿入]タブの[ピボットテーブル]を実行します。
    2. ピボットテーブルの作成ダイアログボックスが表示されます。
      テーブル/範囲 が B2:C21 とデータリストの範囲に一致しているのを確認します。
      ピボットテーブル レポートを配置する場所を選択してください。 で「既存のワークシート」にチェックを入れ、場所を G3 としました。
      (これは説明上同じシートの方が都合がよいためです。新規ワークシートでも構いません。)
      [OK]ボタンをクリックします。
    3. ピボットテーブルのフィールドで、行に 「日付」、値に 「販売金額」をそれぞれドラッグして配置します。
      ピボットテーブルが日付別に作成されます。
    4. 月ごとに集計したいので、ピボットテーブルの 日付のセルを選択します。
      ピボットテーブル ツール リボンの[分析]タブの[フィールドのグループ化]をクリックします。
      グループ化ダイアログが表示されるので、単位で「月」を選択して、[OK]ボタンをクリックします。
    5. ピボットテーブルで月ごとの集計ができました。
    6. 表の形式にこだわるなら、ピボットテーブルの結果をコピーして、F3セル以降に張り付ければOKと考えます。
  2. SUMIFS関数で合計を求める     topへ

    1. 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)) として、下方向へコピーすることも可能です。
    2. SUMIFS関数はExcel2007以降で使用可能です。もしそれより前のヴァージョンをお使いの場合はSUMIF関数で代用します。
      この場合は 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)
  3. 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)

      とすることが考えられます。
  4. 配列数式を使って合計する    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 で入力を確定します。そのあと、下方向へコピーします。
  5. 小計(集計)機能を使って集計する    topへ

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

スポンサードリンク


よねさんのExcelとWordの使い方エクセル2010 練習問題:目次|月ごとに集計する