【Excel】ピボットテーブルのグループ化で日付を月・四半期単位に集計する方法

Excelで大量の日付データを扱う際、月別や四半期別に集計したい場面は多いでしょう。

しかし、手作業で日付を月や四半期に変換して集計するのは非常に手間がかかります。

この記事では、Excelのピボットテーブル機能を使って、日付データを自動的に月や四半期単位で集計する方法を解説します。

ピボットテーブルのグループ化機能を使いこなせば、煩雑なデータ集計作業を劇的に効率化できます。

ピボットテーブルのグループ化機能の概要

ピボットテーブルのグループ化機能は、日付だけでなく、数値やテキストデータも特定の単位でまとめることができる便利な機能です。

日付データの場合、日、月、四半期、年といった単位で自動的にグループ化して集計できます。これにより、日々の売上データを月次や四半期ごとに分析するといったことが容易になります。

この機能を使うためには、元データに日付を表す列が一つ以上存在し、その列がExcelに日付として正しく認識されている必要があります。

ピボットテーブルで日付を月単位に集計する手順

ここでは、具体的なデータ例を用いて、日付を月単位で集計する手順を解説します。

1. 元データの準備
   Excelシートに、日付、商品名、売上金額などのデータを用意します。日付列は「2023/10/26」のようにExcelが日付として認識できる形式で入力してください。
2. ピボットテーブルの作成
   1. 集計したいデータ範囲全体を選択します。
   2. Excelのリボンメニューから「挿入」タブをクリックします。
   3. 「ピボットテーブル」ボタンをクリックします。
   4. 「ピボットテーブルの作成」ダイアログボックスが表示されたら、「新規ワークシート」を選択し、「OK」をクリックします。
3. ピボットテーブルフィールドの設定
   1. 右側に表示される「ピボットテーブルフィールド」ウィンドウで、「日付」フィールドを「行」エリアにドラッグします。
   2. 「売上金額」フィールドを「値」エリアにドラッグします。これで、日ごとの売上金額が表示されたピボットテーブルが作成されます。
4. 日付フィールドのグループ化(月単位)
   1. ピボットテーブル内の「日付」フィールドが表示されているセル(例:「2023/10/26」など)を右クリックします。
   2. 表示されるメニューから「グループ化」を選択します。
   3. 「グループ化」ダイアログボックスが表示されます。「開始」と「終了」の日付は自動で設定されます。
   4. 「単位」のリストから「月」を選択します。必要であれば、「年」も同時に選択できます(例:年と月を同時に選択)。
   5. 「OK」をクリックします。
5. 集計結果の確認
   ピボットテーブルの日付フィールドが、年と月(例:「2023年10月」)ごとにグループ化され、月ごとの売上金額が集計されていることを確認します。

ピボットテーブルで日付を四半期単位に集計する手順

月単位の集計と同様に、四半期単位での集計も簡単に行えます。

1. ピボットテーブルの準備
   上記「ピボットテーブルで日付を月単位に集計する手順」のステップ1〜3までを実行し、日付フィールドを「行」エリアに配置した状態にします。
2. 日付フィールドのグループ化(四半期単位)
   1. ピボットテーブル内の「日付」フィールドが表示されているセルを右クリックします。
   2. 表示されるメニューから「グループ化」を選択します。
   3. 「グループ化」ダイアログボックスが表示されます。
   4. 「単位」のリストから「四半期」を選択します。必要であれば、「年」も同時に選択できます。
3. 集計結果の確認
   ピボットテーブルの日付フィールドが、年と四半期(例:「2023 第1四半期」)ごとにグループ化され、四半期ごとの売上金額が集計されていることを確認します。

グループ化の解除と単位の変更方法

集計単位を変更したい場合や、グループ化を解除したい場合も簡単に行えます。

グループ化の解除

1. ピボットテーブル内のグループ化された日付フィールド(例:「年」や「月」など)のいずれかのセルを右クリックします。

2. 表示されるメニューから「グループ解除」を選択します。

3. これにより、日付フィールドが元の(日ごとの)状態に戻ります。

集計単位の変更

1. グループ化された日付フィールドのいずれかのセルを右クリックします。

2. 表示されるメニューから「グループ化」を選択します。

3. 「グループ化」ダイアログボックスで、希望する新しい単位(例:「年」のみ、「日」と「月」など)を選択し、「OK」をクリックします。

グループ化機能の注意点とよくある質問

h3>日付データが正しく認識されない場合

ピボットテーブルで日付フィールドを右クリックしても「グループ化」が表示されない場合、元データの日付列がExcelに日付として正しく認識されていない可能性があります。

対処法:

1. 1. セルの書式設定の確認
   日付列のセルを選択し、「ホーム」タブの「表示形式」グループで「標準」や「日付」書式になっているか確認します。もし「文字列」などになっている場合は、「日付」書式に変更してください。
2. 2. データ形式の統一
   「2023/10/26」「2023-10-26」「Oct 26, 2023」のように、日付の入力形式が混在していると正しく認識されないことがあります。可能であれば、いずれかの形式に統一してください。
3. 3. 不要な文字の削除
   日付の前後にスペースや「’」などの不要な文字が含まれていると、日付として認識されないことがあります。これらの文字を削除してください。
4. 4. DATEVALUE関数やTEXT関数での変換
   上記の方法で改善しない場合、一度TEXT関数などで日付文字列に変換し、再度DATEVALUE関数などで日付型に変換し直す方法も有効です。例えば、元のデータがA1セルにあるとして、新しい列に「=DATEVALUE(A1)」のような数式を入力して変換します。

h3>グループ化の開始日・終了日がおかしい場合

「グループ化」ダイアログボックスの「開始」や「終了」の日付が、意図しない範囲になっていることがあります。これは、元データに本来存在しないはずの日付(例:1900年1月0日など)が含まれている場合に発生することがあります。

対処法:

1. 1. 元データの確認
   ピボットテーブルの元データ範囲で、日付列に異常な値がないか確認します。特に、日付として認識されているものの、明らかに不自然な日付(例:1900年1月1日より前の日付や、未来すぎる日付)がないかチェックします。
2. 2. フィルター機能の活用
   元データの日付列にフィルターを設定し、日付の昇順・降順で並べ替えて、異常な日付がないか確認すると見つけやすいです。
3. 3. 異常な日付の修正または削除
   異常な日付が見つかった場合は、正しい日付に修正するか、該当する行を削除してください。

h3>「日」単位でのグループ化ができない場合

「グループ化」ダイアログボックスで「月」や「四半期」は選択できるのに、「日」が選択できない、または「日」を選択しても意図した通りにならないことがあります。

これは、元データの日付が「2023/10/26 10:30:00」のように、日付だけでなく時刻情報まで含んでいる場合に発生しやすいです。

対処法:

1. 1. 日付のみにする
   元データの日付列の時刻情報を削除します。セルの書式設定で時刻部分を非表示にするだけでは不十分で、実際の値から時刻情報を取り除く必要があります。
   数式で対応する場合、新しい列に「=INT(A1)」のように入力して、日付部分のみを抽出します。INT関数は数値の整数部分を返すため、日付はシリアル値(数値)で保存されているため、時刻部分が切り捨てられます。
2. 2. ピボットテーブルの再作成
   日付列の時刻情報を削除した後、ピボットテーブルを更新するか、一度削除して再度作成し直してください。

h3>グループ化できるのは日付フィールドのみ

ピボットテーブルのグループ化機能は、日付データや数値データに対して有効ですが、テキストデータに対しては使用できません。例えば、「商品名」フィールドを「50音順」などでグループ化することは、この機能ではできません。

テキストデータをまとめる場合は、フィルター機能や、「ピボットテーブルフィールド」ウィンドウで「行」や「列」エリアに配置する際に、手動で並べ替えたり、重複する項目をまとめるなどの工夫が必要です。

ピボットテーブルのグループ化機能とVLOOKUP関数の比較

日付データを月単位などで集計する際に、ピボットテーブルのグループ化機能以外にも、VLOOKUP関数やXLOOKUP関数とIF関数などを組み合わせて実現する方法も考えられます。

しかし、ピボットテーブルのグループ化機能は、これらの関数を使う方法と比較して、以下のようなメリットがあります。

項目	ピボットテーブルのグループ化	VLOOKUP/XLOOKUP関数
集計の手軽さ	数クリックで月・四半期・年単位の集計が可能	数式作成、コピー、集計行の追加など、複数の手順が必要
柔軟性	集計単位の変更や解除が容易	集計単位を変更するには数式を修正する必要がある
データ更新への対応	元データ更新後にピボットテーブルを「更新」するだけで集計結果が反映される	元データ変更時に数式が正しく参照しているか確認が必要。集計範囲の自動拡張設定などが必要な場合も。
学習コスト	比較的直感的で学習しやすい	関数の知識が必要。複雑な条件分岐には高度な数式知識が求められる
集計結果の表示形式	自動で集計表として整形される	集計表を別途作成する必要がある

このように、日付データの集計という点においては、ピボットテーブルのグループ化機能が圧倒的に効率的で使いやすいと言えます。

関数を使った集計は、特定の条件でデータを抽出・加工したい場合や、集計結果を特定のレイアウトで出力したい場合に有効ですが、単純な期間別集計にはピボットテーブルが最適です。

まとめ

Excelのピボットテーブルを使えば、日付データを月や四半期といった単位で簡単に集計できます。

元データを準備し、ピボットテーブルを作成後、日付フィールドを右クリックして「グループ化」を選ぶだけで、煩雑な集計作業が劇的に効率化されます。

日付データが正しく認識されない場合は、セルの書式設定やデータ形式を確認し、必要に応じて修正してください。

このグループ化機能を活用することで、より詳細な期間別分析が可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】