仕事でピボットテーブルを使用していると、会社の会計年度に合わせた集計が必要になるケースがあると思います。
会計年度は必ずしも1月から始まる会社ばかりでは無いと思います。
その場合、ピボットテーブルで会計年度に続いて月ごとの集計をすると 月の順番がおかしくなります。
何故なら会計年度の先頭の月が1月では無いのに、月の数字でソートすると必ず1月が先頭に来てしまうからです。
どうすれば良いのか【結論】
解決策の結論をまずご説明しますと、月の表示を数値では無く、以下のように文字列に変換するという方法になります。
4〜9月 → スペース ＋ スペース ＋ 数字 （⇒ ’ 4′, ‘ 5’, ‘ 6’, ‘ 7’, ‘ 8’, ‘ 9’）
10〜12月 → スペース ＋ 数字 （⇒ ’ 10′, ‘ 11’, ‘ 12’）
1〜3月 → アンダーバー ＋ 数字 （⇒ ’_1′, ‘_2’, ‘_3’）
これは文字列でソートする場合、スペース ＜ アンダーバー ＜ 数字 の順になるという仕組みを活用しています。
これにより文字列でソートすると下の図のように4月から３月までソート表示ができます。
パワークエリで「年度月」の列を作成する
ではこれを実現する具体的な方法を紹介しましょう。
パワークエリで取得したテーブルデータの中に「日付」という日付型のデータ列がある前提で説明します。もし日付型の列名が「日付」ではないのであれば、下記コードの「日付」の部分をご使用されている列名に変えて活用ください。
以下の手順でパワークエリを作成して下さい。
操作手順
- メニューから「列の追加」→ 「カスタム列」
- 図のように「新い列名」には「年度月」と入力し、「カスタム列の式」には下記のコードを入力します
if Date.Month([日付]) <= 3
then "_" & Number.ToText(Date.Month([日付]))
else
if Date.Month([日付]) <= 9
then " " & Number.ToText(Date.Month([日付]))
else " " & Number.ToText(Date.Month([日付]))
- 作成した「年度月」の列のデータ型をテキスト型に設定します。
パワークエリで年度の列を作成する（おまけ）
ついで年度の列も作成したいと思います。こちらは比較的簡単です。
「日付から3か月マイナスして、『年』の値を取得する」という方法になります。
以下の手順でパワークエリを作成して下さい。
操作手順
- メニューから「列の追加」→ 「カスタム列」
- 図のように「新い列名」には「年度」と入力し、「カスタム列の式」には下記のコードを入力します
Date.Year(Date.AddMonths([日付], -3))
- 作成した「年度」の列のデータ型を整数型に設定します。
サンプルファイル
ご紹介したパワークエリのサンプルファイルは以下のリンクからダウンロード頂けますので良かったら参考にされて下さい。
まとめ
ピボットテーブルで会計年度の下の月を正しくソートする方法 をご紹介しました。
ついでに日付から会計年度を作成する方法もご紹介しました。
これでピボットテーブルによる動的なデータ分析をスムーズに行えるようになると思います。
パワークエリに数ステップ追加するだけで使えるようになりますので、是非皆様の仕事でも使ってみてください。
