Excelでの日付データは、通常、例えば「12/4」などの形で入力したものを、「12月4日」や「2021年12月4日」などの形で表示させていると思います。
でもこのままだと年単位や月単位などで集計することはできませんので、その集計のために、年・月・日などに列を分割して入力し直したこともあるのではないでしょうか?
Excel2016以降では、ピボットテーブルを作成すると自動的に日付データを年・四半期・月にグルーピングする機能が備わりました。
また、ピボットテーブルにはフィルターの一種であるタイムラインという機能があります。
ここでは、日付データを含むソースデータを基に作成したピボットテーブルを使って、日付の自動グルーピング機能とタイムライン機能を解説します。
「ピボットテーブルの作成」ページ以降ここまでは、解説の進め方の都合上、「月」と「日」に列を分けたソースデータを使っていました。
ここからは「日付」(Excel内ではシリアル値として保存されていて、表示の設定で年月日などの様々な日付形式で表示されるもの)として保持されているソースデータを使って解説します。
「ピボットテーブルの作成」で、「日付」フィールドを「行」ボックスに、「金額」を「値」ボックスに設定してみましょう。
【日付の自動グルーピング例1】
「月」フィールドが自動生成され、「日付」は自動的に「月」単位にグルーピングされました。
グルーピングされている印の「+」マークをクリックすると、日にちごとに展開され、マークは「-」に変わります。
【日付の自動グルーピング例2】
「日付」フィールドを「列」ボックスに、「摘要」フィールドを「行」ボックスに、「金額」を「値」ボックスに設定してみましょう。
同様に列の日付が「月」単位に自動的にグルーピングされました。
上記の例では、同じ年の4月~9月のデータしかないので、「月」単位でしか自動的なグルーピングがされませんでした。
【日付の自動グルーピング例3】
ソースデータが複数年にまたがっていると、自動グルーピングは「年」と「四半期」となり、最小の表示が「月」単位となり、「日」単位のデータは表示できなくなります。
但し、四半期は欧米式の1月~3月が第一四半期とするもので、国内向けの資料には合いません。
Excelが勝手にグルーピングの仕方を決めている自動グルーピングでは、四半期の考え方の違いや「日」単位のデータが見れないなど、実状にそぐわないこともあるでしょう。
「ピボットテーブルの例3」で、日付に関わるいずれかのセル(「年」「四半期」「月」「日」)などを選択して右クリックし、ショートカットメニューから「グループ化」を選択すると、「グループ化」のダイログボックスが表示され、「年」「四半期」「月」でグルーピングされていることがわかります。
【日付の自動グルーピング例4】
「四半期」をクリックしてグルーピングの対象から外すと、「四半期」でのグルーピングを解除することができます。
【日付の自動グルーピング例5】
「グループ化」のダイアログボックスで、「日」フィールドをグルーピングの対象に加えると、集計対象の最小単位が「日」になります。
【日付の自動グルーピング例6】
「四半期」のグルーピングが邪魔ならば、「グループ化」ダイアログボックスで、「四半期」を対象から外します。
日単位のデータが見れなくなった場合など、自動的にグルーピングされては困ると感じられる方も居られるかと思います。自動グルーピングを解除するには2通りの方法があります。
日付に関わるいずれかのセル(「年」「四半期」「月」「日」)などを選択して右クリックし、ショートカットメニューから「グループ解除」を選択すると、「年」「四半期」「月」のグルーピングが解けて、「日」単位の表示のみになります。
「ファイル」タブの「その他」から「オプション」を選択し、「Excel のオプション」の「データ」画面で、「ピボットテーブルで日付 / 時刻列の自動グループ化を無効にする」 をオンにすると日付の自動グルーピング機能を無効化できます。
タイムライン機能は、Excel2013で導入された比較的新しい機能で、ピボットテーブル(あるいはピボットグラフ)において、タイムラインと呼ばれる時間軸を示すバー(横棒)を使って、対象期間を動的に変えて集計表示させることができるというものです。
役割としてはスライサーの一種と言えますが、明示的に項目を選択するスライサーに対し、タイムライン上のバーの位置と長さをマウスを使って調整することで集計期間を指定するという違いがあります。
前提として、ソースデータには日付データが存在しなければなりません。
【便利知識】
「行」や「列」に日付フィールドが無いピボットテーブルでもタイムラインを挿入できます。
ピボットテーブル上のいずれかのセルを選択しておいて、
を選択すれば、「タイムラインの挿入」ダイアログボックスが出現します。
「タイムラインの挿入」ダイアログボックスで、「日付」にチェックマークを入れて、「OK」ボタンを押せば、タイムラインが挿入されます。
また、同時に「タイムライン」タブが出現します。
タイムライン上でクリックすると、クリックした箇所の単一期間が選択され、ピボットテーブルの集計データがその期間に絞りこまれます。
タイムラインの選択された期間にマウスポインタを近づけると、両端にハンドルが表示されます。このハンドルをドラッグして、期間を変更できます。ピボットテーブルの集計データもそれに伴い自動的に変更されます。
右図は「7月~9月」の期間に絞り込んだ例です。
タイムラインの右上にある「▼」ボタン(「時間レベル」ボタン)をクリックすると、年、四半期、月、日のリストが表示されます。
このリストで、期間表示の単位を切り替えることができます。
タイムライン表示のヘッダー部右上にある「フィルターのクリア」ボタンをクリックすれば、集計データの絞り込みが解除されます。
タイムラインを選択しておいて、「delete」キーを押すか、右クリックしてショートカットメニューで「タイムラインの削除」を選べば、タイムラインが消去されます。
「タイムライン」タブで、タイムライン表示のデザインスタイル、サイズ、表示の内容などを変更できます。
表示の内容として変更できるのは以下のとおりです。