ピボットテーブルは、任意のフィールド(項目)を「グループ化」することができます
「4月~3月」の会計年度の資料を作る
ピボットテーブルを使って集計する場合、「年」は1月~12月で集計され、4月~3月の会計年度で集計することができません。
このような場合「グループ化」の機能を使うことで4月~3月の「年度」で表示することができます。
「4月~3月」で集計する方法は何種類かありますが、今回紹介するのは数式(関数)を使う方法です。
「グループ化」の機能についてはこちらをご覧ください
ピボットテーブルの操作方法はこちらを参照してください
- ピボットテーブルは、任意のフィールド(項目)を「グループ化」することができます
「年」ではなく、「年度」で分類
ピボットテーブルで集計方法を「年」にすると、「1月~12月」で分類されてしまいます。
集計方法を「年」と「月」の複合集計にする
「年」と「月」の複合集計をすると、「月」は「1月~12月」でグループ化されてしまいます。
2018年度が「2018年1月~2018年12月」
「2018年1月~3月」が2018年度になっています。
2018年度が「2018年4月~2019年3月」
下のグラフのように「4月~3月」で分類しなければ、年度単位での分析になりません。
上のグラフのように「時系列」で月毎の変化を横並びに表示する場合は、それほど「年度」にこだわる必要はありません。
「年」と「商品名」でのクロス集計のグラフ
下のように「年度で比較」する場合には、年度でのグループ化は必須となります。
数式を使って「4月~3月」でグループ化
ピボットテーブルの「グループ化」の機能を使う方法もありますが、今回は数式を使って「年度」のフィールドを作成します。
売上を集計した「リスト」をピボットテーブルを使って分析
ピボットテーブルを作成します
標準では「年」でグループ化されます
ピボットテーブルは自動で「日付」を「年」、「月」、「日」、「四半期」などに分類(グループ化)します。
その際「月」は「1月~12月」でグループ化されています。
関数を使って「年度」のフィールド(項目)を作る
「月」のフィールドを「4月~3月」でグループ化した「年度」の項目(フィールド)を数式を使って作成します。
「日付」の項目からIF関数、MONTH関数、YEAR関数を使って「年度」の項目を作成
MONTH関数で「日付」から取り出した数字が「3より大きい(4以上)」か、「3以下」かで真・偽を求め、IF関数で条件を分ける。
数式の内容は
- MONTH関数で「日付」から取り出した「月」が3より大きい場合は「真」でYEAR関数で日付から取り出した「年」の値をそのまま表示
- MONTH関数で取り出した「月」が「偽(3以下)」であれば、YEAR関数で日付から取り出した「年」の値から1を除した値 を表示
「年度」のフィールド(項目)ができました
「4月~3月」の年度の項目がつくられました。
ピボットテーブルで「年度」を分類に追加する
「行」のエリアに作成した「年度」のフィールド(項目)をドラッグします。
必ず「日付」の上にドラッグしてください。
「日付」が上に来ると、「前年度同月」の複合集計になります。
「年度」と「日付(月)」で複合集計する
「月」と「年度(4月~3月)」の年度で複合集計されたグラフ
数式を使って作成した「年度」を条件にすることで、「2018年4月~2019年3月」が「2018年度」、「2019年4月~2020年3月」が「2019年度」になりました。
「月」と「年度」を入れ替えた複合集計
行のエリアに「年度」を「日付」の下に配置すると「各月を年度別に比較する」複合集計のグラフになります。
このように、ピボットテーブルは色々な集計方法があらかじめ用意されています。
今回のように独自の「ルール」で集計する場合は、「グループ化」の機能を使うことにより任意の方法で集計することができます。
- ピボットテーブルは、任意のフィールド(項目)を「グループ化」することができます