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


スポンサードリンク

問題   topへ

問題1の解答例    topへ


解答例が複数ありますので、使い勝手の良いものを利用してください。
FILTER関数を使って求める
SUMIFS関数で合計を求める SUMIF関数で合計を求める
SUMPRODUCT関数を使って求める 配列数式を使って合計する
小計(集計)機能を使って集計する ピボットテーブルで計算する(集計する)

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

  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セルを選択して、フィルハンドルをダブルクリックして、オートフィルで数式をコピーします。

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

  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)) として、下方向へコピーすることも可能です。

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

  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)

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

配列数式を使って合計する    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 で入力を確定します。そのあと、下方向へコピーします。

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

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

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

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

スポンサードリンク



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