教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010-2016:日付から月ごとのデータを集計したい(SUMPRODUCT、ピボットテーブル、配列数式)

 

 

 

 

 

 


 

 

 

 


 

 

 

 

 

日付が入力されているデータベースから「 月ごとのデータを集計したい、○月のデータを合計したい」といったご質問をいただくことがありますのでご紹介します。

 

◆ 主な集計方法 ◆

  • SUMPRODUCT関数を使う(自動更新)  ※または配列数式 {=sum(if…  {=count(if…

  • ピボットテーブル(簡単、手動更新)


◎◎ 月ごとの商品個数を集計してみよう ◎◎◎

 

◆ 方法1:SUMPRODUCT関数を使う(自動更新OK)

1.  E列に月(1~12)を入力、1行目に商品名を入力(※数式で指定します)

2.  E2にSUMPRODUCT((MONTH($A$2:$A$31)=$E2)*($B$2:$B$31=F$1),($C$2:$C$31)) と入力してオートフィル

 

 ◎ 解説 ◎

 

SUMPRODUCT((配列1の条件)*(配列2の条件),計算式) ← のように配列に対して条件した数式を簡単に作れます。

 

SUMPRODUCT((MONTH($A$2:$A$31)=$E2)*($B$2:$B$31=F$1),($C$2:$C$31))

→ 条件 … 日付列が○月(E2~E13)商品列が△△(F1~I1)集計

 

 

※同様の配置(月を列、商品を行)でSumIfの配列数式でも出ます → {=SUM(IF((MONTH($A$2:$A$31)=$F2)*($B$2:$B$31=G$1),$C$2:$C$31))}

※ドロップダウンリストで月を選択して表示する場合もSUMPRODUCTか配列数式が使えます。(末尾に記載)


 

◆ 方法2:ピボットテーブルを使う(簡単・柔軟 ※手動更新)

  1. データを範囲選択し「挿入」−「ピボットテーブル」をクリック「データ」タブ−「フィルタ」をクリック

  2. 「日付」を【行ラベル】 に、集計したいフィールド「個数」を【値ラベル】、「商品名」を【列ラベル】に配置

  3. 行ラベルの適当な日付セルを1つ右クリック「グループ化」

  4. 「月」を選択し「OK」をクリック


    → 日付が○月でグループ化され、集計されます(更新はピボットテーブルを右クリックして「更新」)

※補足

フィルタは○月のみの一覧表示は簡単ですが、各月をグループ化して集計には向いていません。フィルタオプションも同様です。

 

◆ ドロップダウンリストで条件月を選択し、抽出する場合は

E2で月を選択すると、商品ごとに個数が表示されます。

E2に ↓を入力して横にオートフィルします。

 

SUMPRODUCT((MONTH($A$2:$A$31)=$E2)*($B$2:$B$31=G$1),$C$2:$C$31)

または

{=SUM(IF((MONTH($A$2:$A$31)=$E$2)*($B$2:$B$31=G$1),$C$2:$C$31))}

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows