ピボットテーブルの年度比較の作成方法について紹介します。
ピボットテーブルの基本的な使い方についてはこちら↓↓
今回は下図のような参照リストから2020年度と2021年度（会計年度：4月～3月）の「増減額」と「前年比」が記載されたピボットテーブルを作っていきます。
日付のグループ化
レイアウトセクションに作成したいピボットテーブルに応じてフィールドを設置していきます。今回は年度比較をしたいので、「行」または「列」には日付フィールドをもっていきます。今回は「列」にもっていきます。
そして、日付を年にグループ化します。
日付フィールド右クリック→「グループ化」→「年」を選択
しかし、2020年度と2021年度（会計年度：4月～3月）でグループ化したいのですが、ピボットテーブルのグループ化は年ごとの括りになってしまい、３年分になってしまいます。
本来、2021年1月～3月と2022年1月～3月はそれぞれ2020年度と2021年度にしたいところですが・・・。
年度列の追加
解決策として、参照元リスト（データソース）に予め年度列を追加します。（集計するための列を作っておくことがセオリーです）
=TEXT(EDATE(C3,-3),"yyyy年度")=EDATE(開始日、月）
EDATE関数で引数１：開始日から引数２：月で指定した月数の日付を返します。
よって、引数２を「-3」とすれば、仮に2021年3月の日付だとしても、2020年12月になります。
あとは、TEXT関数を使って、表示形式を「年度」すれば1～3月も希望の年内に収まります。
会計年度が７月～6月などの場合も、EDATE関数の第２引数を-7にすれば正しく表示されます。
その他の会計年度の作成
会計年度のその他のまとめ方として、VLOOKUP関数を使います。
=VLOOKUP(C3,$O$3:$P$5,2,1)=VLOOKUP(検索値、範囲、列番号、［検索方法]）
検索方法を１とすることで、近似値での検索になります。
近似値とは、範囲の中に一致する検索値が無い場合は、検索値未満の数値で最大値で検索します。
つまり、範囲の検索値を年度始めの日付を入力していことで、希望の年度を検索してくれます。
データソースの変更
参照リストに新たな列を追加したら、ピボットテーブルツールの「ピボットテーブル分析」→「データソースの変更」で「年度列」を含めて参照範囲設定します。（データソースをテーブルに設定していれば更新ボタンのみでOK）
Excelテーブル機能の詳細はこちら↓
売上フィールドの追加（増減額）
レイアウトセクションの「値」に「売上フィールド」を追加します。
売上金額２フィールドで右クリック→「計算の種類」→「基準値との差分」を選択します。
「計算の種類」ダイアログボックスで「基準フィールド」は「年度」を選択します。「基準アイテム」は「（前の値）」を選択します。
そうすることで、年度において前の値（前の年度）との差分を算出してくれます。
売上フィールドの追加（前年比）
レイアウトセクションの「値」に「売上フィールド」を追加します。
売上金額２フィールドで右クリック→「計算の種類」→「基準値に対する比率」を選択します。
「計算の種類」ダイアログボックスで「基準フィールド」は「年度」を選択します。「基準アイテム」は「（前の値）」を選択します。
そうすることで、年度において前の値（前の年度）との差分を算出してくれます。
フィールド名をそれぞれ「増減額」「前年度比率」に変更します。
表示に不必要な列は非表示にします。
ピボットテーブルのお悩みはこの一冊で解消します（筆者オススメ）↓
まとめ
今回は、ピボットテーブルで前年度比較を表示する方法をご紹介しました。是非参考にしてみてください。
コメント