ピボットテーブルで年・月・日ごとに集計《日付のグループ化》
- Excel 2019, 2016
- Office 365
また、Excel 2013以下の操作方法は、「Excel 2010・2013のピボットテーブルで年・月ごとに集計《日付のグループ化》」をご覧ください。
（pivottable-group.xlsx 221KB）
「ピボットテーブルの作り方」の続きでご覧いただいている方は、そこでの練習用データを、そのまま引き続きお使いいただけます。
スポンサーリンク
ピボットテーブルでは、日付データを、
年ごとや四半期ごと、月ごとにまとめて集計することができます。
もちろん、日ごとの集計もできます。
但し、
ピボットテーブルの日付データの集計では、Excelが日付をまとめる単位を勝手に決めてしまう
ということを念頭に入れておかないとハマるんです！
そして、Excelが勝手にまとめた単位が気に入らなければ、手動で調整していきます。
これから、ピボットテーブルでの日付の集計について見ていくわけですが、このページでは、「ピボットテーブルの作り方」をお読みいただいていることを前提に解説していきますので、まだの方は、先に「ピボットテーブルの作り方」をご覧ください。
スポンサーリンク
日付を集計する
図の例では、「Sheet1」のデータをもとに、
「Sheet2」にピボットテーブルで集計を作成しています。
そもそも、こうなるまでの方法自体が分からない！という方は、先を読み進めてもちんぷんかんぷんになってしまうので、まずは焦らず、「ピボットテーブルの作り方」をご覧ください。
このピボットテーブルは、商品名ごとに集計されていますが、
それは、［ピボットテーブルのフィールド］作業ウィンドウで、
商品名ごとに集計されるよう設定してあるから。
もし、この作業ウィンドウが表示されていない場合には、「［ピボットテーブルのフィールド］作業ウィンドウが消えた」をご覧ください。
今回のお題は日付での集計なので、この商品名ごとの集計を、申込日ごとの集計に変更していきましょう。
［ピボットテーブルのフィールド］作業ウィンドウ上部で、「商品名」のチェックを外し、「申込日」にチェックを付けます。
この操作で、作業ウィンドウ下部の［行］欄から「商品名」が消え、代わりに「申込日」が入りました。
そして、更に！
作業ウィンドウ上部には、新たに「年」「四半期」という項目ができ（見つからない方は作業ウィンドウ上部の欄をスクロールしてください）、自動的にチェックが付いた状態になるので、作業ウィンドウ下部の［行］欄に、「年」「四半期」も自動的に入りました！
日付の集計と元データの関係
実はこの自動的にできる項目、ピボットテーブルの元となっているデータに、どれだけの期間のデータが収められているかによって変わってきます。
今回の例では、ピボットテーブルの元となっているデータには、複数年におよぶデータが収められていたので、
日付の集計を行うことで、自動的に「年」「四半期」が作られましたが、
でも、ピボットテーブルの元となっているデータが、2ヶ月分程度のデータだと、
四半期にまたがっているわけでも、年にまたがっているわけでもないので、自動的にできるのは「月」という項目になります。
スポンサーリンク
折りたたみと展開
ピボットテーブル内の、「2017年」の脇にある［＋］をクリックすると、
折りたたまれた中身が展開され、「四半期」ごとの集計が表示されます。
作業ウィンドウに自動的にできた、「年」「四半期」の項目のおかげで、
年ごと、四半期ごとの集計になっているわけです。
展開されると、［＋］だったマークは［−］に変わります。
［−］をクリックすると、
展開していたものが折りたたまれます。
［＋］と［−］で、展開と折りたたみを、クリックの度に切り替えていくことができるわけです。
今度は「2018年」の脇にある［＋］をクリックして、
四半期ごとの集計が見えるように展開してみます。
更に「第1四半期」の脇にある［＋］をクリックすると、
折りたたまれた中身が展開され、月ごとの集計が表示されます。
Excelのピボットテーブルでは、「第1四半期」は1月〜3月が既定なので、うちの会社は違うよという方も、このまま読み進めてください。
スポンサーリンク
日付の集計単位に注目！
さて、四半期ごとに集計される必要はないんだけど、という場合もあるでしょう。
不要な集計項目は作業ウィンドウ上部でチェックを外せばいいだけですから、
作業ウィンドウ上部で「四半期」のチェックを外すと、作業ウィンドウ下部から「四半期」の項目が消え、
連動してピボットテーブルでも、四半期ごとの集計が消えました！
今度は作業ウィンドウ上部で「申込日」のチェックを外してみます。
すると、作業ウィンドウ下部から「申込日」の項目が消え、
ピボットテーブルでは、月ごとの集計が消えました！
このことから、
作業ウィンドウの「申込日」は、月ごとの単位だったことが分かります。
このピボットテーブルの元となっているデータでは、「申込日」は1日単位で入力されているので、
ピボットテーブルの作業ウィンドウにある「申込日」にチェックを付ければ、1日単位で集計されると思ってしまいます。
ですが、今回の例では、
ピボットテーブル側の「申込日」は、「月」ごとの集計単位として、自動的に変わっていました。
このページの最初で初めて「申込日」で集計した際に、「年」「四半期」という項目は自動的にできたのに、「月」という項目ができなかったのは、「申込日」が自動的に「月」の単位に変わっていたからというわけ。
このようにExcelは、ピボットテーブルで日付を集計すると、自動的にまとめた単位で集計してしまいます。
ですが、このような単位でまとめられたのは、今回の例のピボットテーブルの元データが、複数年におよぶデータだったから。
元データが2ヶ月分程度のデータであれば、四半期にまたがっているわけでも、年にまたがっているわけでもないので、自動的にできる単位は「月」、「申込日」の単位は「日」となります。
スポンサーリンク
年・四半期・月・日の項目が無い場合には［グループ化］
このように、日付データの集計は、自動的にまとめた単位で集計されてしまうため、自分の思い通りに集計できていない、ということが出てきます。
今回の例では、Excelが勝手に「申込日」＝「月」にしてしまったので、
月ごとの集計はできるものの、1日の合計がどれ位なのか、「日」単位の集計ができていません。
自分の思い通りの単位で集計するには、グループ化を行います。
ピボットテーブル内にある、集計単位を変えたい、日付のセルだったらどれでもいいので、どれか1つのセルを選択します。
うまく1つのセルのみを選択できない場合には、セルの文字にマウスポインタを合わせてクリックすると、うまく選択できます。
セルを選択したら右クリック、
［グループ化］をクリックするか、
［ピボットテーブルツール］の［分析］タブ、［グループ］にある［フィールドのグループ化］をクリックします。
この青くなっている項目は、現在、作業ウィンドウに存在する項目。
ここまでの操作で見ていただいたとおり、「月」という名前の項目は作業ウィンドウにはありませんが、現在の状態では作業ウィンドウの「申込日」が月ごとの集計になっているため、「月」も作業ウィンドウに存在すると考えます。
あくまでも、この青くなっている項目は、作業ウィンドウに存在する項目かどうかの話で、作業ウィンドウでチェックが付いているかどうかという意味ではありません。
選択して青くするか、選択を解除して青を外すかは、各単位をクリックするたびに切り替えられますので、試しに「四半期」をクリックして青を外し、［OK］ボタンをクリックすると、
作業ウィンドウ上部から、「四半期」という項目自体がなくなってしまいました！
つまり、［グループ化］ダイアログボックスは、作業ウィンドウに表示したい単位を決める場所、ということになります。
ということは、「日」単位で集計も行いたいのなら、現在は「申込日」＝「月」という単位になってしまっているので、集計単位を変えていけばいいことになります。
集計単位を変えたい、日付のセルだったらどれでもいいので、どれか1つのセルを選択します。
うまく1つのセルのみを選択できない場合には、セルの文字にマウスポインタを合わせてクリックすると、うまく選択できます。
セルを選択したら右クリック、
［グループ化］をクリックするか、
［ピボットテーブルツール］の［分析］タブ、［グループ］にある［フィールドのグループ化］をクリックします。
［日］をクリックで選択して青くし、［OK］ボタンをクリックすると、
先ほどまでは、Excelが自動的にまとめて「申込日」＝「月」でしたが、「申込日」＝「日」となったので、
新たに「月」の項目ができました！
そして、この作業ウィンドウ上部で、「申込日」「月」「年」にチェックが付いているため、
作業ウィンドウ下部にも、集計の対象として「年」「月」「申込日」が入り、
実際のピボットテーブルでも、「年」ごと、「月」ごと、「申込日」ごとに、階層構造で集計が表示されました！
ここで気を付けたいのは、あくまでも、グループ化は、集計する項目として作業ウィンドウに存在させるかどうかを決めるもの。
このダイアログボックスで選択していない単位は、作業ウィンドウに項目として存在できません。
例えば、このダイアログボックスでは「四半期」を選択していないので、
作業ウィンドウには「四半期」が存在しません。
作業ウィンドウに存在しないから、この状態では「四半期」ごとの集計はできないということになります。
そして、作業ウィンドウ上部に準備した項目で、実際に集計するかどうかは、その項目にチェックをつけるかどうかによって、切り替えられます。
一時的に日ごとの集計のチェックを外し、
「年」と「月」のチェックを残せば、
日ごとの集計だけ解除され、「年」ごと、「月」ごとのみの集計に変わります。
再び日ごとの集計が必要になったら、チェックをつければ、
日ごとの集計も加わります。
集計する項目として、作業ウィンドウに存在させるかどうかを決めるのが、［グループ化］ダイアログボックス。
その項目を使って、ピボットテーブルで集計するかどうかは、チェックを付けるかどうかで決める、というわけです。
スポンサーリンク
「年」ごとの集計、ある／なしに注意！
ピボットテーブルの元となっているデータが、複数年におよぶ場合、
「年」ごと、「月」ごとの両方で集計するよう設定されている場合は、一般的な月ごとの集計、つまり、2017年の1月、2月、3月･･･、2018年の1月、2月、3月･･･という風に集計されます。
ところが、「年」ごとを集計の対象にせず、「月」ごとのみで集計するように設定すると、年は関係なく、1月、2月、3月･･･と集計されます。
というわけで、ピボットテーブルの元となっているデータに、複数年に渡って日付データが入力されている場合は、「年」ごとの集計のある／なしに注意する必要があります。
逆を言えば、ピボットテーブルの元となっているデータに、1年以内の日付データしか入力されていない場合には、年をまたいでいないわけですから、月間の集計も「月」のみの指定で問題ありません。
スポンサーリンク
Officeの使いこなしテクニックをメールでお届け！