前回はデータモデルをピボットテーブルに変換し、それを数式化して使うって話を書きました。
で、今回はさらにスライサーも使えるんだよって紹介です。
Akira Takaoさん、ありがとうございます！
Contents
この記事でやったこと
数式化して表の様式を整えるところは一緒ですが、今回はスライサーを使えるようにしてみました。
もとのデータはこんな感じに部門別のPL科目があって、これをパワークエリで読み込みピボットテーブルで読み込んで数式化するってのは同じです。
この、スライサーが追加されたことを除いては。
これ、本当に便利で、部門を選択すれば自動的に真ん中の損益計算書が更新されるのです。
しかも、スライサー右上の複数選択をOKにすれば、見たい部門を複数選ぶことだって可能です。
ね？面白いでしょ？
今回はこの方法について見ていきましょう。
スライサーを組み込む処理手順
ピボットテーブルの作成
まずはデータを取り込みピボットテーブルを作成します。
データを選択してピボットテーブルを作成してもいいし、パワークエリでデータモデルを作って読み込んでもいいし、ここは状況に応じて使い分けてください。
パワークエリを使う場合は前回の記事を参考にしてください。
で、このように行項目・合計項目を選びます。
スライサーの挿入
そして、数式化する前に、スライサーを挿入します。
ピボットテーブルの分析タブから「スライサーの挿入」を選択し、スライサーを使う項目（ここでは部門）を選択します。
すると、このようにスライサーが挿入されました。
ピボットテーブルの数式化
スライサーの挿入ができれば、ピボットテーブルを数式化します。
「OLAPツール」内に「数式に変換」があります。
これで、ピボットテーブルは数式化されました。
この状態でスライサーをいじってもちゃんと更新されるんやから、すごいですよね。
元ピボットテーブルの整形ほか
まず、金額を呼び出すCUBEVALU関数の中身を修正します。
集計方法として合計列のタイトルを指定しているのですが（F$1となっているセルのこと）、これ列名を変えるとリンクが切れてしますので、参照しないように書き換えておく必要があります。
このように、F$1と指定していた内容が
”[mesures],[合計 / 金額]”となっています。
これで列名が変わっても大丈夫ですね。
この書き換えができれば、この数式を他のセルにもコピー＆ペーストします。
そして並べ替えたり縦列集計行を作ったり、こんな風に仕上げてみました。
これでスライサーを使えば好きなように部門を選択できるわけです。
気を付けること
ただしこれ、2点注意点があります。
読み込んだ値がNull（空欄）の時
読み込んだ値が存在しない場合、その値を使って計算している場合エラーになります。
こんな感じです。
数式バーをご覧下さい。
この引き算、CUBE関数相手では使えなくなるのです。
なにもこれは引き算だけに限った話ではなく、四則演算している場合に、値無を含めているとエラーになってしまいます。
今回の回避方ですが、SUMで集計するとエラーを回避できます。
例えばこんな感じです。
- 売上総利益を例に見ていくと、SUM関数で集計すると問題無し。
- SUM関数でも要素がすべて空欄ならエラー
- ならばIFERROR関数でエラーの時（要素がすべて空欄）は0を返すように指定
これで各段階損益を計算しつつ、エラーを回避します。
集計項目を網羅的に配置すること
あともう一点は、ピボットテーブルのように集計項目はスライサーの選択によっても変動しないということです。
数式化している以上当然と言えば当然ですが、スライサーの選択を変えたからと言って項目が変わるわけではありません。
ですので、まだ値が無いぐらいはいいのですが、項目が無かったらそもそも集計されないということになります。
ですので、表を作る段階で集計項目を網羅的にすべて配置するようにしないといけません。
こんな感じで、表の様式を固めつつスライサーで遊びつつエラーもしっかり回避してみました。
最後に
いかがだったでしょうか。
今回はパワークエリはほとんど関係ありませんが、パワークエリの出口としてこんな使い方もあるよって感じでお届けしました。
数式化すると、表の様式を自由自在に編集できて便利ですが、反面気を付けておかないと思わぬエラーに出くわす印象も否めないように感じます。
とはいえ、スライサーが使えるのは便利なことだと思うので、こんなこともできるんだと頭の片隅にでも置いておいてもらえたら嬉しいですね。
今回も最後までおよみいただき、ありがとうございました！
